-- --------------------------------------------------------------------------- -- -------------------------------------- ====== === === ------------- -- Um caso de estudo. ------------------- == == == = == ------------- -- Um Data Warehouse para a Northwind. -- == == == === == ------------- -- -------------------------------------- ====== === === ------------- -- Criao das tabelas do DW -- Data Mart - Encomendas -- Data Mart - Qualidade de Servio -------------------------------------------------------- -- DDL for Sequence NRSEQ_SEQ -------------------------------------------------------- CREATE SEQUENCE "NRSEQ_SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE -------------------------------------------------------- -- DDL for Table DIM_CALENDARIO -------------------------------------------------------- CREATE TABLE "DIM_CALENDARIO" ("DATA" DATE, "DIASEMANA" VARCHAR2(25), "SEMANA" NUMBER(2,0), "MES" VARCHAR2(25), "TRIMESTRE" NUMBER(1,0), "SEMESTRE" NUMBER(2,0), "ANO" NUMBER(4,0)) -------------------------------------------------------- -- DDL for Table DIM_CATEGORIA -------------------------------------------------------- CREATE TABLE "DIM_CATEGORIA" ("ID" NUMBER(1,0), "DESIGNACAO" VARCHAR2(75), "OBSERVACOES" CLOB, "ETIQUETATMP" DATE DEFAULT sysdate, "REGATIVO" CHAR(1) DEFAULT 'S') -------------------------------------------------------- -- DDL for Table DIM_CLIENTE -------------------------------------------------------- CREATE TABLE "DIM_CLIENTE" ("ID" CHAR(10), "DESIGNACAO" VARCHAR2(75), "TELEFONE" VARCHAR2(25), "CIDADE" VARCHAR2(75), "PAIS" VARCHAR2(75), "OBSERVACOES" CLOB, "ETIQUETATMP" DATE DEFAULT sysdate, "REGATIVO" CHAR(1) DEFAULT 'S') -------------------------------------------------------- -- DDL for Table DIM_CLIENTE_HST -------------------------------------------------------- CREATE TABLE "DIM_CLIENTE_HST" ("ID" CHAR(10), "NRSEQ" NUMBER, "TELEFONE" VARCHAR2(25), "CIDADE" VARCHAR2(75), "PAIS" VARCHAR2(75), "ETIQUETATMP" DATE DEFAULT sysdate) -------------------------------------------------------- -- DDL for Table DIM_FONTE -------------------------------------------------------- CREATE TABLE "DIM_FONTE" ("ID" NUMBER, "DESIGNACAO" VARCHAR2(75), "DESCRICAO" CLOB, "OBSERVACOES" CLOB, "ETIQUETATMP" DATE DEFAULT sysdate, "REGATIVO" CHAR(1) DEFAULT 'S') -------------------------------------------------------- -- DDL for Table DIM_FUNCIONARIO -------------------------------------------------------- CREATE TABLE "DIM_FUNCIONARIO" ("ID" NUMBER(1,0), "NOME" VARCHAR2(75), "ENDERECO" VARCHAR2(150), "CIDADE" VARCHAR2(75), "REGIAO" VARCHAR2(75), "PAIS" VARCHAR2(75), "OBSERVACOES" CLOB, "ETIQUETATMP" DATE DEFAULT sysdate, "REGATIVO" CHAR(1) DEFAULT 'S') -------------------------------------------------------- -- DDL for Table DIM_PRODUTO -------------------------------------------------------- CREATE TABLE "DIM_PRODUTO" ("ID" NUMBER, "DESIGNACAO" VARCHAR2(75), "CATEGORIA" NUMBER(1,0), "OBSERVACOES" CLOB, "ETIQUETATMP" DATE DEFAULT sysdate, "REGATIVO" CHAR(1) DEFAULT 'S') -------------------------------------------------------- -- DDL for Table TF_ENCOMENDAS -------------------------------------------------------- CREATE TABLE "TF_ENCOMENDAS" ("FONTE" NUMBER(1,0), "ENCOMENDA" NUMBER, "DATAENCOMENDA" DATE, "FUNCIONARIO" NUMBER, "CLIENTE" CHAR(10), "PRODUTO" NUMBER, "QUANTIDADE" NUMBER(18,2), "PRECO" NUMBER(18,2), "VALORTOTAL" NUMBER(18,2), "VALORDESCONTO" NUMBER(18,2), "VALORTOTALCOMDESCONTO" NUMBER(18,2), "ETIQUETATMP" DATE DEFAULT sysdate) -------------------------------------------------------- -- DDL for Table TF_QUALIDADESERVICO -------------------------------------------------------- CREATE TABLE "TF_QUALIDADESERVICO" ("ENCOMENDA" NUMBER, "DATAENCOMENDA" DATE, "DATAPARAENTREGAR" DATE, "DATADESPACHO" DATE, "FUNCIONARIO" NUMBER(1,0), "CLIENTE" CHAR(10), "DIASATEPEDIDOENTREGA" NUMBER, "DIASATEDESPACHO" NUMBER, "INDICERELACAO" NUMBER, "ETIQUETATMP" DATE DEFAULT sysdate) -------------------------------------------------------- -- Constraints for Table DIM_CLIENTE -------------------------------------------------------- ALTER TABLE "DIM_CLIENTE" ADD CONSTRAINT "PK_DIM_CLIENTE" PRIMARY KEY ("ID") ENABLE ALTER TABLE "DIM_CLIENTE" MODIFY ("ID" NOT NULL ENABLE) -------------------------------------------------------- -- Constraints for Table DIM_CALENDARIO -------------------------------------------------------- ALTER TABLE "DIM_CALENDARIO" ADD CONSTRAINT "PK_DIM_CALENDARIO" PRIMARY KEY ("DATA") ENABLE ALTER TABLE "DIM_CALENDARIO" MODIFY ("DATA" NOT NULL ENABLE) -------------------------------------------------------- -- Constraints for Table TF_ENCOMENDAS -------------------------------------------------------- ALTER TABLE "TF_ENCOMENDAS" MODIFY ("FUNCIONARIO" NOT NULL ENABLE) ALTER TABLE "TF_ENCOMENDAS" MODIFY ("PRODUTO" NOT NULL ENABLE) ALTER TABLE "TF_ENCOMENDAS" MODIFY ("CLIENTE" NOT NULL ENABLE) ALTER TABLE "TF_ENCOMENDAS" MODIFY ("FONTE" NOT NULL ENABLE) ALTER TABLE "TF_ENCOMENDAS" MODIFY ("DATAENCOMENDA" NOT NULL ENABLE) -------------------------------------------------------- -- Constraints for Table DIM_CATEGORIA -------------------------------------------------------- ALTER TABLE "DIM_CATEGORIA" ADD CONSTRAINT "PK_DIM_CATEGORIA" PRIMARY KEY ("ID") ENABLE ALTER TABLE "DIM_CATEGORIA" MODIFY ("ID" NOT NULL ENABLE) -------------------------------------------------------- -- Constraints for Table DIM_PRODUTO -------------------------------------------------------- ALTER TABLE "DIM_PRODUTO" MODIFY ("CATEGORIA" NOT NULL ENABLE) ALTER TABLE "DIM_PRODUTO" ADD CONSTRAINT "PK_DIM_PRODUTO" PRIMARY KEY ("ID") ENABLE ALTER TABLE "DIM_PRODUTO" MODIFY ("ID" NOT NULL ENABLE) -------------------------------------------------------- -- Constraints for Table DIM_FUNCIONARIO -------------------------------------------------------- ALTER TABLE "DIM_FUNCIONARIO" ADD CONSTRAINT "PK_DIMFUNCIONARIO" PRIMARY KEY ("ID") ENABLE ALTER TABLE "DIM_FUNCIONARIO" MODIFY ("ID" NOT NULL ENABLE) -------------------------------------------------------- -- Constraints for Table DIM_CLIENTE_HST -------------------------------------------------------- ALTER TABLE "DIM_CLIENTE_HST" ADD PRIMARY KEY ("ID", "NRSEQ") ENABLE ALTER TABLE "DIM_CLIENTE_HST" MODIFY ("NRSEQ" NOT NULL ENABLE) ALTER TABLE "DIM_CLIENTE_HST" MODIFY ("ID" NOT NULL ENABLE) -------------------------------------------------------- -- Constraints for Table TF_QUALIDADESERVICO -------------------------------------------------------- ALTER TABLE "TF_QUALIDADESERVICO" MODIFY ("CLIENTE" NOT NULL ENABLE) ALTER TABLE "TF_QUALIDADESERVICO" MODIFY ("FUNCIONARIO" NOT NULL ENABLE) ALTER TABLE "TF_QUALIDADESERVICO" MODIFY ("DATADESPACHO" NOT NULL ENABLE) ALTER TABLE "TF_QUALIDADESERVICO" MODIFY ("DATAPARAENTREGAR" NOT NULL ENABLE) ALTER TABLE "TF_QUALIDADESERVICO" MODIFY ("DATAENCOMENDA" NOT NULL ENABLE) -------------------------------------------------------- -- Constraints for Table DIM_FONTE -------------------------------------------------------- ALTER TABLE "DIM_FONTE" ADD CONSTRAINT "PK_DIM_FONTE" PRIMARY KEY ("ID") ENABLE ALTER TABLE "DIM_FONTE" MODIFY ("ID" NOT NULL ENABLE) -------------------------------------------------------- -- Ref Constraints for Table DIM_PRODUTO -------------------------------------------------------- ALTER TABLE "DIM_PRODUTO" ADD CONSTRAINT "FK_DIM_PRODUTO_DIM_CATEGORIA" FOREIGN KEY ("CATEGORIA") REFERENCES "DIM_CATEGORIA" ("ID") ENABLE -------------------------------------------------------- -- Ref Constraints for Table TF_ENCOMENDAS -------------------------------------------------------- ALTER TABLE "TF_ENCOMENDAS" ADD CONSTRAINT "TF_ENCOMENDAS_FK1" FOREIGN KEY ("DATAENCOMENDA") REFERENCES "DIM_CALENDARIO" ("DATA") ENABLE ALTER TABLE "TF_ENCOMENDAS" ADD CONSTRAINT "TF_ENCOMENDAS_FK2" FOREIGN KEY ("CLIENTE") REFERENCES "DIM_CLIENTE" ("ID") ENABLE ALTER TABLE "TF_ENCOMENDAS" ADD CONSTRAINT "TF_ENCOMENDAS_FK3" FOREIGN KEY ("FONTE") REFERENCES "DIM_FONTE" ("ID") ENABLE ALTER TABLE "TF_ENCOMENDAS" ADD CONSTRAINT "TF_ENCOMENDAS_FK4" FOREIGN KEY ("FUNCIONARIO") REFERENCES "DIM_FUNCIONARIO" ("ID") ENABLE ALTER TABLE "TF_ENCOMENDAS" ADD CONSTRAINT "TF_ENCOMENDAS_FK5" FOREIGN KEY ("PRODUTO") REFERENCES "DIM_PRODUTO" ("ID") ENABLE -------------------------------------------------------- -- Ref Constraints for Table TF_QUALIDADESERVICO -------------------------------------------------------- ALTER TABLE "TF_QUALIDADESERVICO" ADD CONSTRAINT "TF_QUALIDADESERVICO_FK1" FOREIGN KEY ("DATAENCOMENDA") REFERENCES "DIM_CALENDARIO" ("DATA") ENABLE ALTER TABLE "TF_QUALIDADESERVICO" ADD CONSTRAINT "TF_QUALIDADESERVICO_FK2" FOREIGN KEY ("DATAPARAENTREGAR") REFERENCES "DIM_CALENDARIO" ("DATA") ENABLE ALTER TABLE "TF_QUALIDADESERVICO" ADD CONSTRAINT "TF_QUALIDADESERVICO_FK3" FOREIGN KEY ("DATADESPACHO") REFERENCES "DIM_CALENDARIO" ("DATA") ENABLE ALTER TABLE "TF_QUALIDADESERVICO" ADD CONSTRAINT "TF_QUALIDADESERVICO_FK4" FOREIGN KEY ("CLIENTE") REFERENCES "DIM_CLIENTE" ("ID") ENABLE ALTER TABLE "TF_QUALIDADESERVICO" ADD CONSTRAINT "TF_QUALIDADESERVICO_FK5" FOREIGN KEY ("FUNCIONARIO") REFERENCES "DIM_FUNCIONARIO" ("ID") ENABLE -------------------------------------------------------- -- DDL for Trigger DIM_CLIENTE_HST_TRG -------------------------------------------------------- CREATE OR REPLACE TRIGGER "DIM_CLIENTE_HST_TRG" before insert on Dim_Cliente_HST for each row begin select NrSeq_seq.nextval into :new.NrSeq from dual; end; ALTER TRIGGER "DIM_CLIENTE_HST_TRG" ENABLE