wnJaGFZb

1-- 01) Criar as tabelas e os relacionamentos.
2
3DROP TABLE IF EXISTS REQ_MAT;
4DROP TABLE IF EXISTS REQUISICAO;
5DROP TABLE IF EXISTS SECAO;
6DROP TABLE IF EXISTS CCUSTO;
7DROP TABLE IF EXISTS MATERIAL;
8DROP TABLE IF EXISTS ALMOXARIFE;
9
10CREATE TABLE CCUSTO (
11    CCusto CHAR(3) NOT NULL,
12    NomeCC VARCHAR(10) NOT NULL,
13    PRIMARY KEY (CCusto)
14);
15
16CREATE TABLE SECAO (
17    Secao DECIMAL(3,0) UNSIGNED ZEROFILL NOT NULL,
18    NomeSec VARCHAR(20) NOT NULL,
19    CCusto CHAR(3) NOT NULL,
20    PRIMARY KEY (Secao),
21    FOREIGN KEY (CCusto) REFERENCES CCUSTO(CCusto)
22);
23
24CREATE TABLE MATERIAL (
25    CdMat DECIMAL(4,0) UNSIGNED ZEROFILL NOT NULL,
26    DescrMatPrima VARCHAR(15) NOT NULL,
27    PRIMARY KEY (CdMat)
28);
29
30CREATE TABLE ALMOXARIFE (
31    NAlmox DECIMAL(4,0) UNSIGNED ZEROFILL NOT NULL,
32    Almoxarife VARCHAR(30) NOT NULL,
33    PRIMARY KEY (NAlmox)
34);
35
36CREATE TABLE REQUISICAO (
37    NumReq DECIMAL(7,0) UNSIGNED ZEROFILL NOT NULL,
38    DtReq DATE NOT NULL,
39    Secao DECIMAL(3,0) UNSIGNED ZEROFILL NOT NULL,
40    NAlmox DECIMAL(4,0) UNSIGNED ZEROFILL NOT NULL,
41    PRIMARY KEY (NumReq),
42    FOREIGN KEY (Secao) REFERENCES SECAO(Secao),
43    FOREIGN KEY (NAlmox) REFERENCES ALMOXARIFE(NAlmox)
44);
45
46CREATE TABLE REQ_MAT (
47    NumReq DECIMAL(7,0) UNSIGNED ZEROFILL NOT NULL,
48    CdMat DECIMAL(4,0) UNSIGNED ZEROFILL NOT NULL,
49    QtdReq DECIMAL(7,0) UNSIGNED NOT NULL,
50    VlrUnit DECIMAL(10,2) UNSIGNED NOT NULL,
51    PRIMARY KEY (NumReq, CdMat),
52    FOREIGN KEY (NumReq) REFERENCES REQUISICAO(NumReq),
53    FOREIGN KEY (cdMat) REFERENCES MATERIAL(CdMat)
54);