· 7 years ago · Dec 10, 2018, 07:34 AM
1/**
2 * TABLES
3 */
4
5DROP OWNED BY max;
6
7CREATE TABLE Programs (
8 name TEXT,
9 abbrev VARCHAR(10) NOT NULL,
10 PRIMARY KEY (name)
11);
12
13CREATE TABLE Students (
14 idnr NUMERIC(10),
15 name TEXT NOT NULL,
16 login VARCHAR(10) NOT NULL,
17 program TEXT NOT NULL,
18 PRIMARY KEY (idnr),
19 UNIQUE (login),
20 FOREIGN KEY (program) REFERENCES Programs(name)
21);
22
23CREATE TABLE Departments (
24 name TEXT,
25 abbrev VARCHAR(10) NOT NULL,
26 PRIMARY KEY (name),
27 UNIQUE (abbrev)
28);
29
30CREATE TABLE Courses (
31 code VARCHAR(10),
32 name TEXT NOT NULL,
33 credits NUMERIC(2) NOT NULL,
34 department TEXT NOT NULL,
35 PRIMARY KEY (code),
36 FOREIGN KEY (department) REFERENCES Departments(name)
37);
38
39CREATE TABLE Classifications (
40 name TEXT,
41 PRIMARY KEY (name)
42);
43
44CREATE TABLE Branches (
45 name TEXT,
46 program TEXT,
47 PRIMARY KEY (name, program),
48 FOREIGN KEY (program) REFERENCES Programs(name)
49);
50
51CREATE TABLE LimitedCourses (
52 code VARCHAR(10),
53 seats NUMERIC(3) NOT NULL,
54 PRIMARY KEY (code),
55 FOREIGN KEY (code) REFERENCES Courses(code)
56);
57
58CREATE TABLE Classified (
59 course VARCHAR(10),
60 classification TEXT,
61 PRIMARY KEY (course, classification),
62 FOREIGN KEY (course) REFERENCES Courses(code),
63 FOREIGN KEY (classification) REFERENCES Classifications(name)
64);
65
66CREATE TABLE StudentBranches (
67 student NUMERIC(10),
68 branch TEXT NOT NULL,
69 program TEXT NOT NULL,
70 PRIMARY KEY (student),
71 FOREIGN KEY (student) REFERENCES Students(idnr),
72 FOREIGN KEY (branch, program) REFERENCES Branches(name, program)
73);
74
75CREATE TABLE Registered (
76 student NUMERIC(10),
77 course VARCHAR(10),
78 PRIMARY KEY (student, course),
79 FOREIGN KEY (student) REFERENCES Students(idnr),
80 FOREIGN KEY (course) REFERENCES Courses(code)
81);
82
83CREATE TABLE MandatoryProgram (
84 course VARCHAR(10),
85 program TEXT,
86 PRIMARY KEY (course, program),
87 FOREIGN KEY (course) REFERENCES Courses(code),
88 FOREIGN KEY (program) REFERENCES Programs(name)
89);
90
91CREATE TABLE TakenCourses (
92 student NUMERIC(10),
93 course VARCHAR(10),
94 grade CHAR(1) NOT NULL,
95 PRIMARY KEY (student, course),
96 FOREIGN KEY (student) REFERENCES Students(idnr),
97 FOREIGN KEY (course) REFERENCES Courses(code)
98);
99
100CREATE TABLE WaitingList (
101 student NUMERIC(10),
102 course VARCHAR(10),
103 position NUMERIC(3) NOT NULL,
104 PRIMARY KEY (student, course),
105 FOREIGN KEY (student) REFERENCES Students(idnr),
106 FOREIGN KEY (course) REFERENCES Courses(code),
107 UNIQUE (course, position)
108);
109
110CREATE TABLE MandatoryBranch (
111 course VARCHAR(10),
112 branch TEXT,
113 program TEXT,
114 PRIMARY KEY (course, branch, program),
115 FOREIGN KEY (course) REFERENCES Courses(code),
116 FOREIGN KEY (branch, program) REFERENCES Branches(name, program)
117);
118
119CREATE TABLE RecommendedBranch (
120 course VARCHAR(10),
121 branch TEXT,
122 program TEXT,
123 PRIMARY KEY (course, branch, program),
124 FOREIGN KEY (course) REFERENCES Courses(code),
125 FOREIGN KEY (branch, program) REFERENCES Branches(name, program)
126);
127
128CREATE TABLE ProgramDepartments (
129 department TEXT,
130 program TEXT,
131 PRIMARY KEY (department, program),
132 FOREIGN KEY (department) REFERENCES Departments(name),
133 FOREIGN KEY (program) REFERENCES Programs(name)
134);
135
136/**
137 * Views
138 */
139
140DROP VIEW IF EXISTS
141 BasicInformation,
142 FinishedCourses,
143 PassedCourses,
144 Registrations,
145 UnreadMandatory,
146 PathToGraduation;
147
148CREATE VIEW BasicInformation AS
149SELECT idnr, name, login, Students.program, branch
150FROM Students LEFT JOIN StudentBranches ON (idnr = student);
151
152CREATE VIEW FinishedCourses AS
153WITH grades AS (SELECT * FROM Courses INNER JOIN TakenCourses ON (course = code))
154SELECT student, course, grade, credits
155FROM Students JOIN grades ON (idnr = student);
156
157CREATE VIEW PassedCourses AS
158SELECT student, course, credits
159FROM FinishedCourses
160WHERE grade != 'U';
161
162CREATE VIEW Registrations AS
163SELECT * FROM (SELECT student, course, 'waiting' AS status FROM WaitingList) AS waiting
164UNION ALL
165SELECT * FROM (SELECT student, course, 'registered' AS status FROM Registered) AS registered;
166
167CREATE VIEW UnreadMandatory AS
168WITH studentinbranch AS (SELECT idnr, Students.program, StudentBranches.branch FROM Students LEFT JOIN StudentBranches
169 ON (idnr = student)),
170 branchcourses AS (SELECT idnr, course
171 FROM studentinbranch LEFT JOIN MandatoryBranch
172 ON (studentinbranch.branch, studentinbranch.program) = (MandatoryBranch.branch, MandatoryBranch.program)
173 WHERE course IS NOT NULL),
174 programcourses AS (SELECT idnr, course
175 FROM Students LEFT JOIN MandatoryProgram
176 ON (Students.program = MandatoryProgram.program)
177 WHERE course IS NOT NULL),
178 requiredcourses AS (SELECT * FROM programcourses
179 UNION ALL
180 SELECT * FROM branchcourses)
181SELECT idnr AS student, requiredcourses.course
182FROM requiredcourses LEFT JOIN PassedCourses
183ON (idnr, requiredcourses.course) = (student, PassedCourses.course)
184WHERE PassedCourses.course IS NULL;
185
186CREATE VIEW PathToGraduation AS
187WITH
188 takencourses AS (SELECT idnr, course
189 FROM Students LEFT JOIN PassedCourses
190 ON idnr = student),
191 studentinbranch AS (SELECT idnr, branch
192 FROM Students LEFT JOIN studentbranches
193 ON idnr = student),
194 classifiedcredits AS (SELECT *
195 FROM PassedCourses LEFT JOIN Classified
196 ON PassedCourses.course = Classified.course),
197 totcredits AS (SELECT idnr, SUM(COALESCE(credits, 0)) as totalCredits
198 FROM takencourses LEFT JOIN Courses
199 ON course = code
200 GROUP BY idnr),
201 mandleft AS (SELECT idnr, COUNT(course) AS mandatoryLeft
202 FROM Students LEFT JOIN UnreadMandatory
203 ON idnr = student
204 GROUP BY idnr),
205 mathcreds AS (SELECT student, SUM(credits) as mathCredits
206 FROM classifiedcredits
207 WHERE classification = 'math'
208 GROUP BY student),
209 rescreds AS (SELECT student, SUM(credits) as researchCredits
210 FROM classifiedcredits
211 WHERE classification = 'research'
212 GROUP BY (student)),
213 semcourses AS (SELECT student, COUNT(classification) AS seminarsCourses
214 FROM classifiedcredits
215 WHERE classification = 'seminar'
216 GROUP BY student),
217 hasreccreds AS (SELECT idnr, CASE WHEN (SUM(Courses.credits) >= 10) THEN TRUE ELSE FALSE END AS hasrecommended
218 FROM studentinbranch LEFT JOIN recommendedbranch
219 ON studentinbranch.branch = recommendedbranch.branch
220 LEFT JOIN Courses
221 ON recommendedbranch.course = Courses.code
222 LEFT JOIN PassedCourses
223 ON (idnr, recommendedbranch.course) = (PassedCourses.student, PassedCourses.course)
224 GROUP BY idnr),
225 pathtograd AS (SELECT totcredits.idnr AS student,
226 totalCredits,
227 mandatoryleft,
228 COALESCE(mathcredits, 0) AS mathCredits,
229 COALESCE(researchcredits, 0) AS researchCredits,
230 COALESCE(seminarscourses, 0) AS seminarsCourses,
231 hasreccreds.hasrecommended
232 FROM totcredits
233 LEFT JOIN mandleft ON totcredits.idnr = mandleft.idnr
234 LEFT JOIN mathcreds ON totcredits.idnr = mathcreds.student
235 LEFT JOIN rescreds ON totcredits.idnr = rescreds.student
236 LEFT JOIN semcourses ON totcredits.idnr = semcourses.student
237 LEFT JOIN hasreccreds ON totcredits.idnr = hasreccreds.idnr)
238-- join everything and add the 'qualified' field
239SELECT student, totalcredits, mandatoryleft, mathcredits, researchcredits, seminarscourses,
240 CASE WHEN (mandatoryleft = 0
241 AND hasrecommended
242 AND mathcredits >= 20
243 AND researchcredits >= 10
244 AND seminarscourses > 0)
245 THEN TRUE ELSE FALSE
246 END AS qualified
247FROM pathtograd;
248
249CREATE VIEW CourseQueuePositions AS
250SELECT course, student, position AS place FROM WaitingList;
251
252/**
253 * Inserts
254 */
255
256 INSERT INTO Programs VALUES ('Program1', 'P1');
257INSERT INTO Programs VALUES ('Program2', 'P2');
258
259INSERT INTO Branches VALUES ('B1','Program1');
260INSERT INTO Branches VALUES ('B2','Program1');
261INSERT INTO Branches VALUES ('B1','Program2');
262
263INSERT INTO Students VALUES (1111111111,'S1','ls1','Program1');
264INSERT INTO Students VALUES (2222222222,'S2','ls2','Program1');
265INSERT INTO Students VALUES (3333333333,'S3','ls3','Program2');
266INSERT INTO Students VALUES (4444444444,'S4','ls4','Program1');
267
268INSERT INTO Departments VALUES ('Dep1', 'D1');
269
270INSERT INTO Courses VALUES ('CCC111','C1',10,'Dep1');
271INSERT INTO Courses VALUES ('CCC222','C2',20,'Dep1');
272INSERT INTO Courses VALUES ('CCC333','C3',30,'Dep1');
273INSERT INTO Courses VALUES ('CCC444','C4',40,'Dep1');
274INSERT INTO Courses VALUES ('CCC555','C5',50,'Dep1');
275
276INSERT INTO LimitedCourses VALUES ('CCC222',1);
277INSERT INTO LimitedCourses VALUES ('CCC333',2);
278
279INSERT INTO Classifications VALUES ('math');
280INSERT INTO Classifications VALUES ('research');
281INSERT INTO Classifications VALUES ('seminar');
282
283INSERT INTO Classified VALUES ('CCC333','math');
284INSERT INTO Classified VALUES ('CCC444','research');
285INSERT INTO Classified VALUES ('CCC444','seminar');
286
287INSERT INTO StudentBranches VALUES (2222222222,'B1','Program1');
288INSERT INTO StudentBranches VALUES (3333333333,'B1','Program2');
289INSERT INTO StudentBranches VALUES (4444444444,'B1','Program1');
290
291INSERT INTO MandatoryProgram VALUES ('CCC111','Program1');
292
293INSERT INTO MandatoryBranch VALUES ('CCC333', 'B1', 'Program1');
294INSERT INTO MandatoryBranch VALUES ('CCC555', 'B1', 'Program2');
295
296INSERT INTO RecommendedBranch VALUES ('CCC222', 'B1', 'Program1');
297
298INSERT INTO Registered VALUES (1111111111,'CCC111');
299INSERT INTO Registered VALUES (1111111111,'CCC222');
300INSERT INTO Registered VALUES (1111111111,'CCC333');
301
302INSERT INTO Registered VALUES (2222222222,'CCC222');
303
304INSERT INTO TakenCourses VALUES(4444444444,'CCC111','5');
305INSERT INTO TakenCourses VALUES(4444444444,'CCC222','5');
306INSERT INTO TakenCourses VALUES(4444444444,'CCC333','5');
307INSERT INTO TakenCourses VALUES(4444444444,'CCC444','5');
308
309INSERT INTO TakenCourses VALUES(1111111111,'CCC111','3');
310INSERT INTO TakenCourses VALUES(1111111111,'CCC222','3');
311INSERT INTO TakenCourses VALUES(1111111111,'CCC333','3');
312INSERT INTO TakenCourses VALUES(1111111111,'CCC444','3');
313
314INSERT INTO TakenCourses VALUES(2222222222,'CCC111','U');
315INSERT INTO TakenCourses VALUES(2222222222,'CCC222','U');
316INSERT INTO TakenCourses VALUES(2222222222,'CCC444','U');
317
318INSERT INTO WaitingList VALUES(3333333333,'CCC222',1);
319INSERT INTO WaitingList VALUES(3333333333,'CCC333',1);
320INSERT INTO WaitingList VALUES(2222222222,'CCC333',2);