QVayTzPN

1DROP DATABASE IF EXISTS TRAB;
2CREATE DATABASE TRAB;
3USE TRAB;
4
5-- Modelo Operacional (Fonte de dados)
6
7CREATE TABLE CIDADE (
8  CD_CID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
9  NM_CID VARCHAR(40) NOT NULL,
10  SG_UF VARCHAR(2) NOT NULL,
11  PRIMARY KEY (CD_CID)
12);
13
14CREATE TABLE OBRA(
15  CD_OBR BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
16  NM_OBRA VARCHAR(30) NOT NULL,
17  NR_CEP INT(8) NOT NULL,
18  CD_CID BIGINT UNSIGNED NOT NULL,
19  NM_BAIRR VARCHAR(20) NOT NULL,
20  NM_LOGR VARCHAR(40) NOT NULL,
21  NR_LOGR VARCHAR(15),
22  PRIMARY KEY (CD_OBR),
23  CONSTRAINT FOREIGN KEY OBR_CID_FK(CD_CID) REFERENCES CIDADE(CD_CID) ON DELETE CASCADE ON UPDATE RESTRICT
24);
25
26CREATE TABLE EMPREGADO(
27  NR_CPF INT(11) UNSIGNED NOT NULL,
28  NM_EMP VARCHAR(30) NOT NULL,
29  DS_FUN VARCHAR(40) NOT NULL,
30  CD_OBR BIGINT UNSIGNED NOT NULL,
31  PRIMARY KEY (NR_CPF),
32  CONSTRAINT FOREIGN KEY EMP_OBR_FK(CD_OBR) REFERENCES OBRA(CD_OBR) ON DELETE CASCADE ON UPDATE RESTRICT
33);
34
35CREATE TABLE INSUMO (
36  CD_INS BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
37  NM_INS VARCHAR(30) NOT NULL,
38  TP_INS VARCHAR(240) NOT NULL,
39  PRIMARY KEY (CD_INS)
40);
41
42CREATE TABLE FORNECEDOR (
43  NR_CNPJ INT(14) UNSIGNED NOT NULL,
44  NM_FOR VARCHAR(40) NOT NULL,
45  NR_CEP INT(8) UNSIGNED NOT NULL,
46  CD_CID BIGINT UNSIGNED NOT NULL,
47  NM_BAIRR VARCHAR(20) NOT NULL,
48  NM_LOGR VARCHAR(40) NOT NULL,
49  NR_LOGR VARCHAR(15),
50  PRIMARY KEY (NR_CNPJ),
51  CONSTRAINT FOREIGN KEY FOR_CID_FK(CD_CID) REFERENCES CIDADE(CD_CID) ON DELETE CASCADE ON UPDATE RESTRICT
52);
53
54CREATE TABLE INSUMO_FORNECIDO (
55  CD_INS BIGINT UNSIGNED NOT NULL,
56  NR_CNPJ INT(14) UNSIGNED NOT NULL,
57  PRIMARY KEY (CD_INS, NR_CNPJ),
58  CONSTRAINT FOREIGN KEY INS_FOR_INS_FK(CD_INS) REFERENCES INSUMO(CD_INS) ON DELETE CASCADE ON UPDATE RESTRICT,
59  CONSTRAINT FOREIGN KEY INS_FOR_FOR_FK(NR_CNPJ) REFERENCES FORNECEDOR(NR_CNPJ) ON DELETE CASCADE ON UPDATE RESTRICT
60);
61
62CREATE TABLE COMPRA (
63  CD_COM BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
64  NR_CPF INT(14) UNSIGNED NOT NULL,
65  CD_INS BIGINT UNSIGNED NOT NULL,
66  NR_CNPJ INT(14) UNSIGNED NOT NULL,
67  DT_COM DATE NOT NULL,
68  DT_PREV_PAG DATE,
69  VL_COM DOUBLE(9,2) NOT NULL,
70  NR_ITENS bigint NOT NULL,
71  PRIMARY KEY (CD_COM),
72  CONSTRAINT FOREIGN KEY COM_INS_FOR_FK(CD_INS, NR_CNPJ) REFERENCES INSUMO_FORNECIDO(CD_INS, NR_CNPJ) ON DELETE CASCADE ON UPDATE RESTRICT,
73  CONSTRAINT FOREIGN KEY COM_EMP_FK(NR_CPF) REFERENCES EMPREGADO(NR_CPF) ON DELETE CASCADE ON UPDATE RESTRICT
74);
75
76CREATE TABLE PARCELA (
77  CD_COM BIGINT UNSIGNED NOT NULL,
78  CD_PAR SMALLINT UNSIGNED NOT NULL,
79  DT_VCTO DATE NOT NULL,
80  VL_PAR DOUBLE(9,2) NOT NULL,
81  DT_PGTO DATE,
82  VL_PAGO DOUBLE(9,2),
83  PRIMARY KEY (CD_COM, CD_PAR),
84  CONSTRAINT FOREIGN KEY PAR_COM_FK(CD_COM) REFERENCES COMPRA(CD_COM) ON DELETE CASCADE ON UPDATE RESTRICT
85);
86
87-- Modelo Dimensional
88
89CREATE TABLE D_OBRA(
90  CD_OBR BIGINT UNSIGNED NOT NULL,
91  NM_OBR VARCHAR(40) NOT NULL,
92  PRIMARY KEY (CD_OBR)
93);
94
95CREATE TABLE D_EMPREGADO(
96  NR_CPF INT(11) UNSIGNED NOT NULL,
97  NM_EMP VARCHAR(40) NOT NULL,
98  PRIMARY KEY (NR_CPF)
99);
100
101CREATE TABLE D_INSUMO (
102  CD_INS BIGINT UNSIGNED NOT NULL,
103  NM_INS VARCHAR(40) NOT NULL,
104  TP_INS VARCHAR(40),
105  PRIMARY KEY (CD_INS)
106);
107
108CREATE TABLE D_FORNECEDOR(
109  NR_CNPJ INT(14) UNSIGNED NOT NULL,
110  NM_FOR VARCHAR(40) NOT NULL,
111  PRIMARY KEY (NR_CNPJ)
112);
113
114CREATE TABLE D_TEMPO (
115  CD_TEMPO BIGINT UNSIGNED NOT NULL,
116  NR_ANO SMALLINT(4) NOT NULL,
117  NR_MES SMALLINT(2) NOT NULL,
118  PRIMARY KEY (CD_TEMPO)
119);
120
121CREATE TABLE F_COMPRA (
122  CD_OBR BIGINT UNSIGNED NOT NULL,
123  NR_CPF INT(11) UNSIGNED NOT NULL,
124  CD_INS BIGINT UNSIGNED NOT NULL,
125  NR_CNPJ INT(14) UNSIGNED NOT NULL,
126  CD_TEMPO BIGINT UNSIGNED NOT NULL,
127  VL_COM DOUBLE(9,2) NOT NULL,
128  NR_ITENS INT NOT NULL,
129  PRIMARY KEY (NR_CPF, CD_INS, NR_CNPJ, CD_OBR, CD_TEMPO),
130  CONSTRAINT FOREIGN KEY MD_COM_OBR_FK(CD_OBR) REFERENCES D_OBRA(CD_OBR) ON DELETE CASCADE ON UPDATE RESTRICT,
131  CONSTRAINT FOREIGN KEY MD_COM_EMP_FK(NR_CPF) REFERENCES D_EMPREGADO(NR_CPF) ON DELETE CASCADE ON UPDATE RESTRICT,
132  CONSTRAINT FOREIGN KEY MD_COM_INS_FK(CD_INS) REFERENCES D_INSUMO(CD_INS) ON DELETE CASCADE ON UPDATE RESTRICT,
133  CONSTRAINT FOREIGN KEY MD_COM_FOR_FK(NR_CNPJ) REFERENCES D_FORNECEDOR(NR_CNPJ) ON DELETE CASCADE ON UPDATE RESTRICT,
134  CONSTRAINT FOREIGN KEY MD_COM_TEM_FK(CD_TEMPO) REFERENCES D_TEMPO(CD_TEMPO) ON DELETE CASCADE ON UPDATE RESTRICT
135);