etunyyW4

1DROP TABLE IF EXISTS Themes CASCADE;
2DROP TABLE IF EXISTS Chercheurs CASCADE;
3DROP TABLE IF EXISTS Equipes CASCADE;
4DROP TABLE IF EXISTS Laboratoire CASCADE;
5DROP TABLE IF EXISTS Travailler CASCADE;
6DROP TABLE IF EXISTS Definir CASCADE;
7DROP TYPE IF EXISTS typeLabo;
8
9--Création de la table Theme
10CREATE TABLE Themes(
11	codeTh INT CONSTRAINT pk_codeTh PRIMARY KEY,
12	libelle VARCHAR(55) NOT NULL
13);
14
15--Création de la table Chercheurs
16CREATE TABLE Chercheurs(
17	codeCh INT CONSTRAINT pk_codeCh PRIMARY KEY,
18	nomCh VARCHAR(50) NOT NULL,
19	codeEq INT 
20);
21
22--Création d'un type 
23CREATE TYPE typeLabo AS ENUM ('Universitaire','CNRS','INRIA');
24
25--Création de la table 
26CREATE TABLE Laboratoire(
27	codeLabo INT CONSTRAINT pk_codeLabo PRIMARY KEY,
28	type typeLabo,
29	nomLabo VARCHAR(20),
30	URL VARCHAR(100),
31	codeDir INT
32);
33
34CREATE TABLE Equipes(
35	codeEq INT CONSTRAINT pk_codeEquipe PRIMARY KEY,
36	nomEq Varchar(55),
37	codeResp INT,
38	codeLabo INT
39);
40
41CREATE TABLE Definir(
42	codeEq INT,
43	codeTh INT
44);
45
46
47CREATE TABLE Travailler(
48	codeCh INT,
49	codeTh INT
50);
51
52
53--Definition des contraintes pour chaques tables
54ALTER TABLE Chercheurs ADD CONSTRAINT fk_codeEq FOREIGN KEY (codeEq) REFERENCES  Equipes(codeEq) DEFERRABLE;
55
56ALTER TABLE Laboratoire ADD CONSTRAINT fk_codeDir FOREIGN KEY (codeDir) REFERENCES Chercheurs(codeCh) DEFERRABLE;
57
58
59ALTER TABLE Equipes ADD CONSTRAINT fk_codeResp FOREIGN KEY (codeResp) REFERENCES Chercheurs(codeCh) DEFERRABLE;
60ALTER TABLE Equipes ADD CONSTRAINT fk_codeLabo FOREIGN KEY (codeLabo) REFERENCES Laboratoire(codeLabo) DEFERRABLE;
61
62ALTER TABLE Definir ADD CONSTRAINT fk_codeEq FOREIGN KEY (codeEq) REFERENCES Equipes(codeEq) DEFERRABLE;
63ALTER TABLE Definir ADD CONSTRAINT fk_codeTh FOREIGN KEY (codeTh) REFERENCES Themes(codeTh) DEFERRABLE;
64
65
66ALTER TABLE Travailler ADD CONSTRAINT fk_codeCh FOREIGN KEY (codeCh) REFERENCES Chercheurs(codeCh) DEFERRABLE;
67ALTER TABLE Travailler ADD CONSTRAINT fk_codeTh FOREIGN KEY (codeTh) REFERENCES Themes(codeTh) DEFERRABLE;
68
69
70
71--Insertion des données dans les tables
72
73
74
75
76--On creer 16 chercheur repartient dans 4 equipes
77BEGIN;
78SET CONSTRAINTS fk_codeEq DEFERRED;
79INSERT INTO Chercheurs (codeCh, nomCh, codeEq)
80	VALUES
81	('1','Laouni','001'), -- Responsable équipe 1
82	('2','Uber','001'),
83	('3','Celestin','001'),
84	('4','Pétunia','001'),
85	('5','Jean-Mi','002'), -- Responsable équipe 2
86	('6','Brendon','002'),
87	('7','Karim','002'),
88	('8','Clitorine','002'),
89	('9','Frooti','003'), -- Responsable équipe 3
90	('10','Lucifer','003'),
91	('11','Lorenzo','003'),
92	('12','Taufoou','003'),
93	('13','Julien-Marie','004'), -- Responsable équipe 4
94	('14','Merdive','004'),
95	('15','Jean-Bryan','004'),
96	('16','Dylan','004');
97COMMIT;