kRt3qPQ1

1DROP DATABASE IF EXISTS TRAB;
2CREATE DATABASE TRAB;
3USE TRAB;
4
5CREATE TABLE CIDADE (
6  CD_CID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
7  NM_CID VARCHAR(40) NOT NULL,
8  SG_UF VARCHAR(2) NOT NULL,
9  PRIMARY KEY (CD_CID)
10);
11
12CREATE TABLE OBRA(
13  CD_OBR BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
14  NM_OBRA VARCHAR(30) NOT NULL,
15  NR_CEP INT(8) NOT NULL,
16  CD_CID BIGINT UNSIGNED NOT NULL,
17  NM_BAIRR VARCHAR(20) NOT NULL,
18  NM_LOGR VARCHAR(40) NOT NULL,
19  NR_LOGR VARCHAR(15),
20  PRIMARY KEY (CD_OBR),
21  CONSTRAINT FOREIGN KEY OBR_CID_FK(CD_CID) REFERENCES CIDADE(CD_CID) ON DELETE CASCADE ON UPDATE RESTRICT
22);
23
24CREATE TABLE EMPREGADO(
25  NR_CPF INT(11) UNSIGNED NOT NULL,
26  NM_EMP VARCHAR(30) NOT NULL,
27  DS_FUN VARCHAR(40) NOT NULL,
28  CD_OBR BIGINT UNSIGNED NOT NULL,
29  PRIMARY KEY (NR_CPF),
30  CONSTRAINT FOREIGN KEY EMP_OBR_FK(CD_OBR) REFERENCES OBRA(CD_OBR) ON DELETE CASCADE ON UPDATE RESTRICT
31);
32
33CREATE TABLE INSUMO (
34  CD_INS BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
35  NM_INS VARCHAR(30) NOT NULL,
36  TP_INS VARCHAR(240) NOT NULL,
37  PRIMARY KEY (CD_INS)
38);
39
40CREATE TABLE FORNECEDOR (
41  NR_CNPJ INT(14) UNSIGNED NOT NULL,
42  NM_FOR VARCHAR(40) NOT NULL,
43  NR_CEP INT(8) UNSIGNED NOT NULL,
44  CD_CID BIGINT UNSIGNED NOT NULL,
45  NM_BAIRR VARCHAR(20) NOT NULL,
46  NM_LOGR VARCHAR(40) NOT NULL,
47  NR_LOGR VARCHAR(15),
48  PRIMARY KEY (NR_CNPJ),
49  CONSTRAINT FOREIGN KEY FOR_CID_FK(CD_CID) REFERENCES CIDADE(CD_CID) ON DELETE CASCADE ON UPDATE RESTRICT
50);
51
52CREATE TABLE INSUMO_FORNECIDO (
53  CD_INS BIGINT UNSIGNED NOT NULL,
54  NR_CNPJ INT(14) UNSIGNED NOT NULL,
55  PRIMARY KEY (CD_INS, NR_CNPJ),
56  CONSTRAINT FOREIGN KEY INS_FOR_INS_FK(CD_INS) REFERENCES INSUMO(CD_INS) ON DELETE CASCADE ON UPDATE RESTRICT,
57  CONSTRAINT FOREIGN KEY INS_FOR_FOR_FK(NR_CNPJ) REFERENCES FORNECEDOR(NR_CNPJ) ON DELETE CASCADE ON UPDATE RESTRICT
58);
59
60CREATE TABLE COMPRA (
61  CD_COM BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
62  NR_CPF INT(14) UNSIGNED NOT NULL,
63  CD_INS BIGINT UNSIGNED NOT NULL,
64  NR_CNPJ INT(14) UNSIGNED NOT NULL,
65  DT_COM DATE NOT NULL,
66  DT_PREV_PAG DATE,
67  VL_COM DOUBLE(9,2) NOT NULL,
68  NR_ITENS bigint NOT NULL,
69  PRIMARY KEY (CD_COM),
70  CONSTRAINT FOREIGN KEY COM_INS_FOR_FK(CD_INS, NR_CNPJ) REFERENCES INSUMO_FORNECIDO(CD_INS, NR_CNPJ) ON DELETE CASCADE ON UPDATE RESTRICT,
71  CONSTRAINT FOREIGN KEY COM_EMP_FK(NR_CPF) REFERENCES EMPREGADO(NR_CPF) ON DELETE CASCADE ON UPDATE RESTRICT
72);
73
74CREATE TABLE PARCELA (
75  CD_COM BIGINT UNSIGNED NOT NULL,
76  CD_PAR SMALLINT UNSIGNED NOT NULL,
77  DT_VCTO DATE NOT NULL,
78  VL_PAR DOUBLE(9,2) NOT NULL,
79  DT_PGTO DATE,
80  VL_PAGO DOUBLE(9,2),
81  PRIMARY KEY (CD_COM, CD_PAR),
82  CONSTRAINT FOREIGN KEY PAR_COM_FK(CD_COM) REFERENCES COMPRA(CD_COM) ON DELETE CASCADE ON UPDATE RESTRICT
83);