PbQhTqMK

1SET search_path = University_schema;
2
3DROP SCHEMA IF EXISTS University_schema CASCADE;
4CREATE SCHEMA University_schema;
5
6CREATE TABLE IF NOT EXISTS Etudiant (
7	sid		VARCHAR(10) NOT NULL,
8	snom 	VARCHAR(20)	NOT NULL,
9	sexe	CHAR		NOT NULL CHECK (sexe in ('M', 'F')),
10	age		INT			NOT NULL,
11	moyenne	VARCHAR(3)	NOT NULL,
12	PRIMARY KEY (sid)
13);
14										
15CREATE TABLE IF NOT EXISTS Dept (
16	dId		VARCHAR(10) NOT NULL,
17	nombrephds	INT		NOT NULL,
18	PRIMARY KEY (did)
19);
20										
21CREATE TABLE IF NOT EXISTS Prof (
22	pid		VARCHAR(10) NOT NULL,
23	pnom 	VARCHAR(20)	NOT NULL,
24	dep		VARCHAR(10)	NOT NULL,
25	PRIMARY KEY (pid),
26	FOREIGN KEY (dep) REFERENCES Dept(dId)
27);
28
29CREATE TABLE IF NOT EXISTS Cours (
30	cno		VARCHAR(10) NOT NULL,
31	cnom 	VARCHAR(40)	NOT NULL,
32	dep		VARCHAR(10)	NOT NULL,
33	PRIMARY KEY (cno),
34	FOREIGN KEY (dep) REFERENCES Dept(dId)
35);
36
37CREATE TABLE IF NOT EXISTS Section (
38	cno		VARCHAR(10) NOT NULL,
39	sectno 	VARCHAR(10)	NOT NULL,
40	pid		VARCHAR(10) NOT NULL,
41	PRIMARY KEY (cno, sectno),
42	FOREIGN KEY (cno) REFERENCES Cours(cno)
43		ON DELETE CASCADE,
44	FOREIGN KEY (pid) REFERENCES Prof(pid)
45		ON DELETE SET NULL
46);
47										
48CREATE TABLE IF NOT EXISTS Inscription (
49	sid		VARCHAR(10) NOT NULL,
50	cno		VARCHAR(10) NOT NULL,
51	sectno 	VARCHAR(10)	NOT NULL,
52	note	INT			NOT NULL CHECK (note BETWEEN 0 AND 100),
53	PRIMARY KEY (sid, cno, sectno),
54	FOREIGN KEY (sid) REFERENCES Etudiant(sid),
55	FOREIGN KEY (cno, sectno) REFERENCES Section(cno, sectno)
56);
57
58-- DATA
59
60SET search_path = University_schema;
61
62/*	Q2: Cela ne fonctionne pas, car le numero de cours et le numero de cours reference une section, donc
63*	si le numero de cours est invalide, il ne sera pas possible de trouver la section voulue. L'integrite
64*	referentielle s'assure que la foreign key existe prealablement.
65*	Ex: INSERT INTO Inscription VALUES ('s6', 'test', '1', 90);
66*/
67
68INSERT INTO Etudiant VALUES ('s1', 'Simon Nissan', 'M', 20, '4.2');
69INSERT INTO Etudiant VALUES ('s2', 'Laurent Passepartout', 'M', 30, '3.2');
70INSERT INTO Etudiant VALUES ('s3', 'Alexandra Laplace', 'F', 40, '2.8');
71INSERT INTO Etudiant VALUES ('s5', 'Simon Belanger', 'M', 20, '3.2');
72INSERT INTO Etudiant VALUES ('s6', 'Mark Zuck', 'M', 30, '1.2');
73INSERT INTO Etudiant VALUES ('s7', 'Sophie Yenamarre', 'M', 30, '4.2');
74
75INSERT INTO Dept VALUES ('genChem', 9);
76INSERT INTO Dept VALUES ('gigl', 40);
77INSERT INTO Dept VALUES ('Maths', 5);
78
79INSERT INTO Prof VALUES ('p1', 'AZ', 'gigl');
80INSERT INTO Prof VALUES ('p2', 'MG', 'gigl');
81INSERT INTO Prof VALUES ('p3', 'NZ', 'Maths');
82INSERT INTO Prof VALUES ('p4', 'LH', 'Maths');
83
84INSERT INTO Cours VALUES ('105', 'programmation','gigl');
85INSERT INTO Cours VALUES ('200', 'NLP','gigl');
86INSERT INTO Cours VALUES ('304',	'Geometrie 101', 'Maths');
87INSERT INTO Cours VALUES ('305',	'Theoremes en geometrie', 'Maths');
88INSERT INTO Cours VALUES ('306',	'Geometrie intermediaire', 'Maths');
89INSERT INTO Cours VALUES ('307',	'Geometrie','Maths');
90
91INSERT INTO Section VALUES ('105', '1', 'p1');
92INSERT INTO Section VALUES ('105', '2', 'p1');
93INSERT INTO Section VALUES ('305', '1', 'p3');
94INSERT INTO Section VALUES ('305', '2', 'p4');
95
96INSERT INTO Inscription VALUES ('s3', '105', '1', 90);
97INSERT INTO Inscription VALUES ('s2', '105', '1', 60);
98INSERT INTO Inscription VALUES ('s1', '105', '2', 70);
99INSERT INTO Inscription VALUES ('s5', '105', '2', 70);
100INSERT INTO Inscription VALUES ('s5', '305', '2', 100);
101INSERT INTO Inscription VALUES ('s6', '305', '2', 65);