· 9 years ago · Jan 20, 2017, 11:48 PM
1CREATE DATABASE IF NOT EXISTS EMPRESA;
2
3USE EMPRESA;
4
5CREATE TABLE IF NOT EXISTS RA94518_RA95372_EMPREGADO (
6 SSN CHAR(9) NOT NULL,
7 PNOME VARCHAR(15) NOT NULL,
8 UNOME VARCHAR(15) NOT NULL,
9 SEXO CHAR,
10 DNASC DATE,
11 ECIDADE VARCHAR(30),
12 SUPERSSN CHAR(9),
13 DNO INT NOT NULL,
14PRIMARY KEY(SSN)
15);
16
17CREATE TABLE IF NOT EXISTS RA94518_RA95372_DEPARTAMENTO (
18 DNUMERO INT NOT NULL,
19 DNOME VARCHAR(15) NOT NULL,
20 DCIDADE VARCHAR(15) NOT NULL,
21 GERSSN CHAR(9) NOT NULL,
22 GERDATAINICIO DATE,
23PRIMARY KEY(DNUMERO),
24UNIQUE(DNOME)
25);
26
27CREATE TABLE IF NOT EXISTS RA94518_RA95372_PROJETO(
28 PNUMERO INT NOT NULL,
29 PNOME VARCHAR(15) NOT NULL,
30 PCIDADE VARCHAR(15),
31 DNUM INT NOT NULL,
32PRIMARY KEY(PNUMERO)
33);
34
35CREATE TABLE IF NOT EXISTS RA94518_RA95372_TRABALHA_EM (
36 ESSN CHAR(9) NOT NULL,
37 PNO INT NOT NULL,
38 HORAS DECIMAL(3,1) NOT NULL,
39PRIMARY KEY(ESSN,PNO)
40);
41
42CREATE TABLE IF NOT EXISTS RA94518_RA95372_DEPENDENTE (
43 ESSN CHAR(9) NOT NULL,
44 NUMDEP INT NOT NULL,
45 NOMEDEP VARCHAR(15) NOT NULL,
46 SEXO CHAR(1),
47 DATANASC DATE,
48 PARENTESCO VARCHAR(6),
49PRIMARY KEY(ESSN,NOMEDEP)
50);
51
52
53ALTER TABLE RA94518_RA95372_EMPREGADO
54ADD FOREIGN KEY(SUPERSSN) REFERENCES RA94518_RA95372_EMPREGADO(SSN);
55
56ALTER TABLE RA94518_RA95372_EMPREGADO
57ADD FOREIGN KEY(DNO) REFERENCES RA94518_RA95372_DEPARTAMENTO(DNUMERO);
58
59ALTER TABLE RA94518_RA95372_DEPARTAMENTO
60ADD FOREIGN KEY(GERSSN) REFERENCES RA94518_RA95372_EMPREGADO (SSN);
61
62ALTER TABLE RA94518_RA95372_PROJETO
63ADD FOREIGN KEY(DNUM) REFERENCES RA94518_RA95372_DEPARTAMENTO(DNUMERO);
64
65ALTER TABLE RA94518_RA95372_TRABALHA_EM
66ADD FOREIGN KEY(ESSN) REFERENCES RA94518_RA95372_EMPREGADO(SSN);
67
68ALTER TABLE RA94518_RA95372_TRABALHA_EM
69ADD FOREIGN KEY(PNO) REFERENCES RA94518_RA95372_PROJETO(PNUMERO);
70
71ALTER TABLE RA94518_RA95372_DEPENDENTE
72ADD FOREIGN KEY(ESSN) REFERENCES RA94518_RA95372_EMPREGADO(SSN);
73
74
75INSERT INTO RA94518_RA95372_DEPARTAMENTO VALUES
76(1,'DIN','MARINGA','12345ABCD','2015-01-01'),
77(2,'DINF','LONDRINA','12349ABCD','2015-01-01'),
78(3,'INF','CURITIBA','12350ABCD','2015-01-01'),
79(4,'ENG.SOFT','MARINGA','12346ABCD','2015-01-01'),
80(5,'ENG.COMP','LONDRINA','12347ABCD','2015-01-01'),
81(6,'PROC.DADOS','CURITIBA','12348ABCD','2015-01-01');
82
83
84INSERT INTO RA94518_RA95372_EMPREGADO VALUES
85('12345ABCD','PEDRO','ANTUNES','M','1980-01-01','MARINGA',NULL,1),
86('12346ABCD','MARCOS','SANTOS','M','1980-07-03','MARINGA','12345ABCD',2),
87('12347ABCD','MARGARETE','SANTOS','F','1977-07-02','MARINGA','12345ABCD',1),
88('12348ABCD','LUCAS','SOARES','M','1974-07-02','LONDRINA','12345ABCD',2),
89('12349ABCD','R.CENI','SPFC','M','1970-07-02','MARINGA',NULL,3),
90('12350ABCD','LUCAS','SPFC','M','1970-07-02','MARINGA','12349ABCD',4),
91('12351ABCD','KAKA','SPFC','M','1970-07-02','MARINGA','12349ABCD',5),
92('12352ABCD','JOSUE','SPFC','M','1970-07-02','MARINGA','12349ABCD',6),
93('12353ABCD','LUGANO','SPFC','M','1970-07-02','MARINGA','12349ABCD',1),
94('12354ABCD','RAI','SPFC','M','1970-07-02','LONDRINA',NULL,2),
95('12355ABCD','CEREZZO','SPFC','M','1970-07-02','LONDRINA','12354ABCD',3),
96('12356ABCD','PALINHA','SPFC','M','1970-07-02','LONDRINA','12354ABCD',4),
97('12357ABCD','S.CHULAPA','SANTOS','M','1970-07-02','LONDRINA','12354ABCD',5),
98('12358ABCD','T.SANTANA','SANTOS','M','1970-07-02','LONDRINA','12354ABCD',6),
99('12359ABCD','MURICY','SANTOS','M','1970-07-02','CURITIBA',NULL,1),
100('123603ABC','ALOISIO','SPFC','M','1970-07-02','CURITIBA','12359ABCD',5),
101('12360ABCD','ADRIANO','SPFC','M','1970-07-02','CURITIBA','12359ABCD',2),
102('12361ABCD','AMOROSO','SPFC','M','1970-07-02','CURITIBA','12359ABCD',3),
103('12362ABCD','MINEIRO','SPFC','M','1970-07-02','CURITIBA','12359ABCD',4),
104('12363ABCD','ALOISIO','SPFC','M','1970-07-02','CURITIBA','12359ABCD',5);
105
106INSERT INTO RA94518_RA95372_PROJETO VALUES (1,'Produto Y','MARINGA',1),
107(2,'LIBERTADORE05','MARINGA',2),(3,'LIBERTADORE92','MARINGA',3),
108(4,'LIBERTADORE93','LONDRINA',4),(5,'SULAMERIC12','LONDRINA',5),
109(6,'SULAMERIC12','LONDRINA',6),(7,'SULAMERIC12','CURITIBA',1),
110(8,'BRASILEIRO08','CURITIBA',2),(9,'BRASILEIRO07','CURITIBA',3),
111(10,'BRASILEIRO06','MARINGA',4);
112
113
114INSERT INTO RA94518_RA95372_TRABALHA_EM VALUES ('12345ABCD',1,10.0),('12345ABCD',2,10.0),
115('12346ABCD',2,10.0),('12346ABCD',3,10.0),
116('12347ABCD',3,10.0),('12347ABCD',4,10.0),
117('12348ABCD',4,10.0),('12348ABCD',5,10.0),
118('12349ABCD',5,10.0),('12349ABCD',6,10.0),
119('12350ABCD',1,10.0),('12350ABCD',6,10.0),
120('12351ABCD',1,10.0),('12351ABCD',2,10.0),
121('12352ABCD',2,10.0),('12352ABCD',3,10.0),
122('12353ABCD',3,10.0),('12353ABCD',4,10.0),
123('12354ABCD',4,10.0),('12354ABCD',5,10.0),
124('12355ABCD',5,10.0),('12356ABCD',6,10.0),('12357ABCD',1,10.0),('12358ABCD',2,10.0),
125('12359ABCD',3,10.0),('12360ABCD',4,10.0),('12361ABCD',5,10.0),('12362ABCD',6,10.0),('12363ABCD',1,10.0);
126
127INSERT INTO RA94518_RA95372_DEPENDENTE VALUES ('12345ABCD',3,'ALEX.SILVA','M','1980-01-01','FILHO'),
128('12345ABCD',3,'R.CAIO','M','1980-01-01','FILHO'),('12345ABCD',3,'R.OLIVEIRA','M','1980-01-01','FILHO'),
129('12346ABCD',2,'MIRANDA','M','1980-01-01','FILHO'),('12346ABCD',2,'WASHINGTON','M','1980-01-01','FILHO'),
130('12347ABCD',2,'JUNIOR','M','1980-01-01','FILHO'),('12347ABCD',2,'WASHINGTON','M','1980-01-01','FILHO'),
131('12348ABCD',1,'CHICHA','M','1980-01-01','FILHO'),('12349ABCD',1,'EVANS','M','1980-01-01','FILHO'),
132('12350ABCD',1,'GUILHERMINO','M','1980-01-01','FILHO');