script.sql

/* Oracle DDL Generated by BIM 1.0 */

CREATE TABLE "DIMENSIONAL_MODEL"."D_TIMESTAMP"
(
	"ID_TIMESTAMP" INT NOT NULL,
	"TIMESTAMP" CHAR(16) NOT NULL,
	"HOUR" CHAR(16) NOT NULL,
	"PART_OF_THE_DAY" CHAR(16) NOT NULL
)
;

CREATE UNIQUE INDEX "DIMENSIONAL_MODEL"."PK_D_TIMESTAMP" ON "DIMENSIONAL_MODEL"."D_TIMESTAMP"("ID_TIMESTAMP")
;


ALTER TABLE "DIMENSIONAL_MODEL"."D_TIMESTAMP" ADD CONSTRAINT "PK_D_TIMESTAMP"  PRIMARY KEY ("ID_TIMESTAMP")
USING INDEX
;

CREATE TABLE "DIMENSIONAL_MODEL"."F_RECHARGES"
(
	"ID_DATE" INT NOT NULL,
	"ID_CARD" INT NOT NULL,
	"ID_PAYMENT_TYPE" INT NOT NULL,
	"RECHARGED_MONEY" INT
)
;

CREATE UNIQUE INDEX "DIMENSIONAL_MODEL"."PK_F_RECHARGES" ON "DIMENSIONAL_MODEL"."F_RECHARGES"("ID_DATE", "ID_CARD", "ID_PAYMENT_TYPE")
;


ALTER TABLE "DIMENSIONAL_MODEL"."F_RECHARGES" ADD CONSTRAINT "PK_F_RECHARGES"  PRIMARY KEY ("ID_DATE", "ID_CARD", "ID_PAYMENT_TYPE")
USING INDEX
;

CREATE TABLE "DIMENSIONAL_MODEL"."F_TRAFFIC_EVENT"
(
	"ID_DATE" INT NOT NULL,
	"ID_TIMESTAMP" INT NOT NULL,
	"ID_CLIENT" INT NOT NULL,
	"ID_DESTIN" INT NOT NULL,
	"ID_SOURCE_LOCATION" INT NOT NULL,
	"ID_EVENT_TYPE" INT NOT NULL,
	"ID_SERVICE_TYPE" INT NOT NULL,
	"CONSUMED_TIME" INT,
	"CONSUMED_VOL" INT,
	"CONSUMED_EVENTS" INT,
	"BILLED_TIME" INT,
	"BILLED_VOLUME" INT,
	"BILLED_EVENTS" INT
)
;

CREATE UNIQUE INDEX "DIMENSIONAL_MODEL"."PK_F_TRAFFIC_EVENT" ON "DIMENSIONAL_MODEL"."F_TRAFFIC_EVENT"("ID_DATE", "ID_TIMESTAMP", "ID_CLIENT", "ID_DESTIN", "ID_SOURCE_LOCATION", "ID_EVENT_TYPE", "ID_SERVICE_TYPE")
;


ALTER TABLE "DIMENSIONAL_MODEL"."F_TRAFFIC_EVENT" ADD CONSTRAINT "PK_F_TRAFFIC_EVENT"  PRIMARY KEY ("ID_DATE", "ID_TIMESTAMP", "ID_CLIENT", "ID_DESTIN", "ID_SOURCE_LOCATION", "ID_EVENT_TYPE", "ID_SERVICE_TYPE")
USING INDEX
;

CREATE TABLE "DIMENSIONAL_MODEL"."D_DATE"
(
	"ID_DATE" INT NOT NULL,
	"DAY" CHAR(16) NOT NULL,
	"DAY_OF_WEEK" CHAR(16) NOT NULL,
	"MONTH" CHAR(16) NOT NULL,
	"QUARTER" CHAR(16) NOT NULL,
	"YEAR" CHAR(16) NOT NULL,
	"WEEK" CHAR(16) NOT NULL
)
;

CREATE UNIQUE INDEX "DIMENSIONAL_MODEL"."PK_D_DATE" ON "DIMENSIONAL_MODEL"."D_DATE"("ID_DATE")
;


ALTER TABLE "DIMENSIONAL_MODEL"."D_DATE" ADD CONSTRAINT "PK_D_DATE"  PRIMARY KEY ("ID_DATE")
USING INDEX
;

CREATE TABLE "DIMENSIONAL_MODEL"."F_STATE_TRANSITION"
(
	"ID_DATE" INT NOT NULL,
	"ID_CARD" INT NOT NULL,
	"ID_TRANSITION_TYPE" INT NOT NULL
)
;

CREATE UNIQUE INDEX "DIMENSIONAL_MODEL"."PK_F_STATE_TRANSITION" ON "DIMENSIONAL_MODEL"."F_STATE_TRANSITION"("ID_DATE", "ID_CARD", "ID_TRANSITION_TYPE")
;


ALTER TABLE "DIMENSIONAL_MODEL"."F_STATE_TRANSITION" ADD CONSTRAINT "PK_F_STATE_TRANSITION"  PRIMARY KEY ("ID_DATE", "ID_CARD", "ID_TRANSITION_TYPE")
USING INDEX
;

CREATE TABLE "DIMENSIONAL_MODEL"."D_EVENT_TYPE"
(
	"ID_EVENT_TYPE" INT NOT NULL,
	"EVENT_TYPE" CHAR(16) NOT NULL
)
;

CREATE UNIQUE INDEX "DIMENSIONAL_MODEL"."PK_D_EVENT_TYPE" ON "DIMENSIONAL_MODEL"."D_EVENT_TYPE"("ID_EVENT_TYPE")
;


ALTER TABLE "DIMENSIONAL_MODEL"."D_EVENT_TYPE" ADD CONSTRAINT "PK_D_EVENT_TYPE"  PRIMARY KEY ("ID_EVENT_TYPE")
USING INDEX
;

CREATE TABLE "DIMENSIONAL_MODEL"."D_CLIENT"
(
	"ID_CLIENT" INT NOT NULL,
	"CARD_ID" CHAR(16) NOT NULL
)
;

CREATE UNIQUE INDEX "DIMENSIONAL_MODEL"."PK_D_CLIENT" ON "DIMENSIONAL_MODEL"."D_CLIENT"("ID_CLIENT")
;


ALTER TABLE "DIMENSIONAL_MODEL"."D_CLIENT" ADD CONSTRAINT "PK_D_CLIENT"  PRIMARY KEY ("ID_CLIENT")
USING INDEX
;

CREATE TABLE "DIMENSIONAL_MODEL"."D_SOURCE_LOCATION"
(
	"ID_SOURCE_LOCATION" INT NOT NULL,
	"CELL" CHAR(16) NOT NULL,
	"CITY" CHAR(16) NOT NULL,
	"DISTRICT" CHAR(16) NOT NULL,
	"MODEL" CHAR(16),
	"LOCATION_LAT/LON" CHAR(16),
	"COUNTRY" CHAR(16) NOT NULL
)
;

CREATE UNIQUE INDEX "DIMENSIONAL_MODEL"."PK_D_SOURCE_LOCATION" ON "DIMENSIONAL_MODEL"."D_SOURCE_LOCATION"("ID_SOURCE_LOCATION")
;


ALTER TABLE "DIMENSIONAL_MODEL"."D_SOURCE_LOCATION" ADD CONSTRAINT "PK_D_SOURCE_LOCATION"  PRIMARY KEY ("ID_SOURCE_LOCATION")
USING INDEX
;

CREATE TABLE "DIMENSIONAL_MODEL"."D_SERVICE_TYPE"
(
	"ID_SERVICE_TYPE" INT NOT NULL,
	"SERVICE_TYPE" CHAR(16) NOT NULL
)
;

CREATE UNIQUE INDEX "DIMENSIONAL_MODEL"."PK_D_SERVICE_TYPE" ON "DIMENSIONAL_MODEL"."D_SERVICE_TYPE"("ID_SERVICE_TYPE")
;


ALTER TABLE "DIMENSIONAL_MODEL"."D_SERVICE_TYPE" ADD CONSTRAINT "PK_D_SERVICE_TYPE"  PRIMARY KEY ("ID_SERVICE_TYPE")
USING INDEX
;

CREATE TABLE "DIMENSIONAL_MODEL"."D_TYPE"
(
	"ID_TYPE" NUMERIC NOT NULL,
	"TYPE" CHAR(16) NOT NULL
)
;

CREATE UNIQUE INDEX "DIMENSIONAL_MODEL"."PK_D_TYPE" ON "DIMENSIONAL_MODEL"."D_TYPE"("ID_TYPE")
;


ALTER TABLE "DIMENSIONAL_MODEL"."D_TYPE" ADD CONSTRAINT "PK_D_TYPE"  PRIMARY KEY ("ID_TYPE")
USING INDEX
;

CREATE TABLE "DIMENSIONAL_MODEL"."D_TRANSITION_TYPE"
(
	"ID_TRANSITION_TYPE" INT NOT NULL,
	"TRANSITION_TYPE" CHAR(16) NOT NULL
)
;

CREATE UNIQUE INDEX "DIMENSIONAL_MODEL"."PK_D_TRANSITION_TYPE" ON "DIMENSIONAL_MODEL"."D_TRANSITION_TYPE"("ID_TRANSITION_TYPE")
;


ALTER TABLE "DIMENSIONAL_MODEL"."D_TRANSITION_TYPE" ADD CONSTRAINT "PK_D_TRANSITION_TYPE"  PRIMARY KEY ("ID_TRANSITION_TYPE")
USING INDEX
;

CREATE TABLE "DIMENSIONAL_MODEL"."D_PAYMENT_TYPE"
(
	"ID_PAYMENT_TYPE" INT NOT NULL,
	"PAYMENT_TYPE" CHAR(16) NOT NULL
)
;

CREATE UNIQUE INDEX "DIMENSIONAL_MODEL"."PK_D_PAYMENT_TYPE" ON "DIMENSIONAL_MODEL"."D_PAYMENT_TYPE"("ID_PAYMENT_TYPE")
;


ALTER TABLE "DIMENSIONAL_MODEL"."D_PAYMENT_TYPE" ADD CONSTRAINT "PK_D_PAYMENT_TYPE"  PRIMARY KEY ("ID_PAYMENT_TYPE")
USING INDEX
;

CREATE TABLE "DIMENSIONAL_MODEL"."D_CARD"
(
	"ID_CARD" INT NOT NULL,
	"CARD_ID" CHAR(16) NOT NULL
)
;

CREATE UNIQUE INDEX "DIMENSIONAL_MODEL"."PK_D_CARD" ON "DIMENSIONAL_MODEL"."D_CARD"("ID_CARD")
;


ALTER TABLE "DIMENSIONAL_MODEL"."D_CARD" ADD CONSTRAINT "PK_D_CARD"  PRIMARY KEY ("ID_CARD")
USING INDEX
;

CREATE TABLE "DIMENSIONAL_MODEL"."D_DESTIN"
(
	"ID_DESTIN" INT NOT NULL,
	"DESTIN_PREFIX" CHAR(16) NOT NULL,
	"DETIN_LABEL" CHAR(16)
)
;

CREATE UNIQUE INDEX "DIMENSIONAL_MODEL"."PK_D_DESTIN" ON "DIMENSIONAL_MODEL"."D_DESTIN"("ID_DESTIN")
;


ALTER TABLE "DIMENSIONAL_MODEL"."D_DESTIN" ADD CONSTRAINT "PK_D_DESTIN"  PRIMARY KEY ("ID_DESTIN")
USING INDEX
;

CREATE TABLE "DIMENSIONAL_MODEL"."D_PROVIDER"
(
	"ID_PROVIDER" INT NOT NULL,
	"PROVIDER" CHAR(16) NOT NULL,
	"ABC" CHAR(16) NOT NULL
)
;

CREATE UNIQUE INDEX "DIMENSIONAL_MODEL"."PK_D_PROVIDER" ON "DIMENSIONAL_MODEL"."D_PROVIDER"("ID_PROVIDER")
;


ALTER TABLE "DIMENSIONAL_MODEL"."D_PROVIDER" ADD CONSTRAINT "PK_D_PROVIDER"  PRIMARY KEY ("ID_PROVIDER")
USING INDEX
;

ALTER TABLE "DIMENSIONAL_MODEL"."F_RECHARGES" ADD CONSTRAINT "FK_RECHARGES_DATE" FOREIGN KEY ("ID_DATE")
REFERENCES "DIMENSIONAL_MODEL"."D_DATE" ("ID_DATE")
;

ALTER TABLE "DIMENSIONAL_MODEL"."F_RECHARGES" ADD CONSTRAINT "FK_RECHARGES_CARD" FOREIGN KEY ("ID_CARD")
REFERENCES "DIMENSIONAL_MODEL"."D_CARD" ("ID_CARD")
;

ALTER TABLE "DIMENSIONAL_MODEL"."F_RECHARGES" ADD CONSTRAINT "FK_RECHARGES_PAYMENT_TYPE" FOREIGN KEY ("ID_PAYMENT_TYPE")
REFERENCES "DIMENSIONAL_MODEL"."D_PAYMENT_TYPE" ("ID_PAYMENT_TYPE")
;

ALTER TABLE "DIMENSIONAL_MODEL"."F_TRAFFIC_EVENT" ADD CONSTRAINT "FK_TRAFFIC_EVENT_DATE" FOREIGN KEY ("ID_DATE")
REFERENCES "DIMENSIONAL_MODEL"."D_DATE" ("ID_DATE")
;

ALTER TABLE "DIMENSIONAL_MODEL"."F_TRAFFIC_EVENT" ADD CONSTRAINT "FK_TRAFFIC_EVENT_TIMESTAMP" FOREIGN KEY ("ID_TIMESTAMP")
REFERENCES "DIMENSIONAL_MODEL"."D_TIMESTAMP" ("ID_TIMESTAMP")
;

ALTER TABLE "DIMENSIONAL_MODEL"."F_TRAFFIC_EVENT" ADD CONSTRAINT "FK_TRAFFIC_EVENT_CLIENT" FOREIGN KEY ("ID_CLIENT")
REFERENCES "DIMENSIONAL_MODEL"."D_CLIENT" ("ID_CLIENT")
;

ALTER TABLE "DIMENSIONAL_MODEL"."F_TRAFFIC_EVENT" ADD CONSTRAINT "FK_TRAFFIC_EVENT_DESTIN" FOREIGN KEY ("ID_DESTIN")
REFERENCES "DIMENSIONAL_MODEL"."D_DESTIN" ("ID_DESTIN")
;

ALTER TABLE "DIMENSIONAL_MODEL"."F_TRAFFIC_EVENT" ADD CONSTRAINT "FK_TRAFFIC_LOCATION" FOREIGN KEY ("ID_SOURCE_LOCATION")
REFERENCES "DIMENSIONAL_MODEL"."D_SOURCE_LOCATION" ("ID_SOURCE_LOCATION")
;

ALTER TABLE "DIMENSIONAL_MODEL"."F_TRAFFIC_EVENT" ADD CONSTRAINT "FK_TRAFFIC_EVENT_EVENT_TYPE" FOREIGN KEY ("ID_EVENT_TYPE")
REFERENCES "DIMENSIONAL_MODEL"."D_EVENT_TYPE" ("ID_EVENT_TYPE")
;

ALTER TABLE "DIMENSIONAL_MODEL"."F_TRAFFIC_EVENT" ADD CONSTRAINT "FK_TRAFFIC_EVENT_SERVICE_TYPE" FOREIGN KEY ("ID_SERVICE_TYPE")
REFERENCES "DIMENSIONAL_MODEL"."D_SERVICE_TYPE" ("ID_SERVICE_TYPE")
;

ALTER TABLE "DIMENSIONAL_MODEL"."F_STATE_TRANSITION" ADD CONSTRAINT "FK_STATE_TRANSITION_DATE" FOREIGN KEY ("ID_DATE")
REFERENCES "DIMENSIONAL_MODEL"."D_DATE" ("ID_DATE")
;

ALTER TABLE "DIMENSIONAL_MODEL"."F_STATE_TRANSITION" ADD CONSTRAINT "FK_STATE_TRANSITION_CARD" FOREIGN KEY ("ID_CARD")
REFERENCES "DIMENSIONAL_MODEL"."D_CARD" ("ID_CARD")
;

ALTER TABLE "DIMENSIONAL_MODEL"."F_STATE_TRANSITION" ADD CONSTRAINT "FK_STATE_TRANSITION_TYPE" FOREIGN KEY ("ID_TRANSITION_TYPE")
REFERENCES "DIMENSIONAL_MODEL"."D_TRANSITION_TYPE" ("ID_TRANSITION_TYPE")
;