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