· 8 years ago · Dec 03, 2017, 06:08 PM
1DROP SCHEMA IF EXISTS university CASCADE;
2CREATE SCHEMA university;
3SET SCHEMA 'university';
4
5DROP TABLE IF EXISTS Department;
6CREATE TABLE Department(
7 name TEXT PRIMARY KEY,
8 abbrv TEXT NOT NULL UNIQUE
9);
10INSERT INTO Department VALUES ('Dept. of Computer Science', 'DCS');
11INSERT INTO Department VALUES ('Dept. of Computer Engineering', 'DCE');
12
13DROP TABLE IF EXISTS Program;
14CREATE TABLE Program(
15 name TEXT PRIMARY KEY,
16 abbrv TEXT NOT NULL
17);
18INSERT INTO Program VALUES ('Computer Science', 'CS');
19INSERT INTO Program VALUES ('Computer Engineering', 'CE');
20INSERT INTO Program VALUES ('Computer Interaction', 'CI');
21INSERT INTO Program VALUES ('Robot Design', 'RD');
22
23DROP TABLE IF EXISTS Hosts;
24CREATE TABLE Hosts(
25 department TEXT,
26 program TEXT,
27 FOREIGN KEY (department) REFERENCES Department(name)
28 ON DELETE CASCADE
29 ON UPDATE CASCADE,
30 FOREIGN KEY (program) REFERENCES Program(name)
31 ON DELETE CASCADE
32 ON UPDATE CASCADE,
33 PRIMARY KEY(department, program)
34);
35INSERT INTO Hosts VALUES ('Dept. of Computer Engineering', 'Computer Engineering');
36INSERT INTO Hosts VALUES ('Dept. of Computer Science', 'Computer Engineering');
37INSERT INTO Hosts VALUES ('Dept. of Computer Science', 'Computer Science');
38INSERT INTO Hosts VALUES ('Dept. of Computer Engineering', 'Robot Design');
39INSERT INTO Hosts VALUES ('Dept. of Computer Science', 'Computer Interaction');
40
41DROP TABLE IF EXISTS Branch;
42CREATE TABLE Branch(
43 name TEXT,
44 program TEXT,
45 FOREIGN KEY (program) REFERENCES Program(name)
46 ON DELETE CASCADE
47 ON UPDATE CASCADE,
48 PRIMARY KEY(name, program)
49);
50
51INSERT INTO Branch VALUES ('Algorithms', 'Computer Science');
52INSERT INTO Branch VALUES ('Digiflisp Programming', 'Computer Engineering');
53INSERT INTO Branch VALUES ('Frontend Design', 'Computer Interaction');
54INSERT INTO Branch VALUES ('Robot Wars History', 'Robot Design');
55
56DROP TABLE IF EXISTS Student;
57CREATE TABLE Student(
58 nid TEXT PRIMARY KEY,
59 name TEXT NOT NULL,
60 uid TEXT NOT NULL,
61 program TEXT NOT NULL,
62 branch TEXT,
63 FOREIGN KEY (program) REFERENCES Program(name)
64 ON DELETE CASCADE
65 ON UPDATE CASCADE,
66 FOREIGN KEY (branch, program) REFERENCES Branch(name, program)
67 ON DELETE CASCADE
68 ON UPDATE CASCADE
69);
70INSERT INTO Student VALUES ('1995-10-24-8484', 'Emil Rosén', 'rosem', 'Robot Design');
71INSERT INTO Student VALUES ('1851-10-24-1337', 'Felix Wennberg', 'wefe', 'Robot Design', 'Robot Wars History');
72INSERT INTO Student VALUES ('0000-12-24-1111', 'Jesus of Nasareth', 'JESUS', 'Computer Interaction', 'Frontend Design');
73INSERT INTO Student VALUES ('1993-12-24-1111', 'Ado Rustig', 'ruad', 'Computer Interaction');
74INSERT INTO Student VALUES ('1723-01-23-2211', 'Charles Darwin', 'Dach', 'Computer Engineering', 'Digiflisp Programming');
75INSERT INTO Student VALUES ('1980-05-10-3313', 'Dick Mahlmström', 'Mahdick', 'Computer Science');
76
77DROP TABLE IF EXISTS Course;
78CREATE TABLE Course(
79 code TEXT PRIMARY KEY,
80 name TEXT NOT NULL,
81 credit FLOAT NOT NULL
82 CHECK (credit > 0)
83);
84INSERT INTO Course VALUES ('1', 'Dator Teknik', '7.5');
85INSERT INTO Course VALUES ('2', 'Automata', '7.5');
86INSERT INTO Course VALUES ('3', 'Robot building', '7.5');
87INSERT INTO Course VALUES ('4', 'Tusenvariables analys', '7.5');
88INSERT INTO Course VALUES ('5', 'Computer Communications', '7.5');
89INSERT INTO Course VALUES ('6', 'MatStat', '7.5');
90
91DROP TABLE IF EXISTS BranchMandatory;
92CREATE TABLE BranchMandatory(
93 branch TEXT NOT NULL,
94 program TEXT NOT NULL,
95 course TEXT NOT NULL,
96 FOREIGN KEY (branch, program) REFERENCES Branch(name, program)
97 ON DELETE CASCADE
98 ON UPDATE CASCADE,
99 FOREIGN KEY (course) REFERENCES Course(code)
100 ON DELETE CASCADE
101 ON UPDATE CASCADE,
102 PRIMARY KEY (branch, program, course)
103);
104INSERT INTO BranchMandatory VALUES ('Algorithms', 'Computer Science', '2');
105INSERT INTO BranchMandatory VALUES ('Robot Wars History', 'Robot Design', '5');
106INSERT INTO BranchMandatory VALUES ('Digiflisp Programming', 'Computer Engineering', '6');
107
108CREATE TABLE ProgramMandatory(
109 program TEXT NOT NULL,
110 course TEXT NOT NULL,
111 FOREIGN KEY (program) REFERENCES Program(name)
112 ON DELETE CASCADE
113 ON UPDATE CASCADE,
114 FOREIGN KEY (course) REFERENCES Course(code)
115 ON DELETE CASCADE
116 ON UPDATE CASCADE,
117 PRIMARY KEY (program, course)
118);
119INSERT INTO ProgramMandatory VALUES ('Computer Science', '4');
120INSERT INTO ProgramMandatory VALUES ('Robot Design', '3');
121INSERT INTO ProgramMandatory VALUES ('Computer Engineering', '1');
122
123DROP TABLE IF EXISTS Recommendation;
124CREATE TABLE Recommendation(
125 branch TEXT NOT NULL,
126 program TEXT NOT NULL,
127 course TEXT NOT NULL,
128 FOREIGN KEY (branch, program) REFERENCES Branch(name, program)
129 ON DELETE CASCADE
130 ON UPDATE CASCADE,
131 FOREIGN KEY (course) REFERENCES Course(code)
132 ON DELETE CASCADE
133 ON UPDATE CASCADE,
134 PRIMARY KEY (branch, program, course)
135);
136INSERT INTO Recommendation VALUES ('Algorithms', 'Computer Science', '3');
137INSERT INTO Recommendation VALUES ('Robot Wars History', 'Robot Design', '1');
138INSERT INTO Recommendation VALUES ('Digiflisp Programming', 'Computer Engineering', '5');
139
140DROP TABLE IF EXISTS Registered;
141CREATE TABLE Registered(
142 nid TEXT NOT NULL,
143 course TEXT NOT NULL,
144 FOREIGN KEY (nid) REFERENCES Student(nid)
145 ON DELETE CASCADE
146 ON UPDATE CASCADE,
147 FOREIGN KEY (course) REFERENCES Course(code)
148 ON DELETE CASCADE
149 ON UPDATE CASCADE,
150 PRIMARY KEY(nid, course)
151);
152INSERT INTO Registered VALUES ('1995-10-24-8484', '1');
153INSERT INTO Registered VALUES ('1851-10-24-1337', '2');
154INSERT INTO Registered VALUES ('0000-12-24-1111', '3');
155INSERT INTO Registered VALUES ('1993-12-24-1111', '4');
156INSERT INTO Registered VALUES ('1723-01-23-2211', '5');
157INSERT INTO Registered VALUES ('1980-05-10-3313', '6');
158
159CREATE TYPE grade AS ENUM('U', '3', '4', '5');
160CREATE TABLE HasRead(
161 nid TEXT NOT NULL,
162 course TEXT NOT NULL,
163 grade grade NOT NULL,
164 FOREIGN KEY (nid) REFERENCES Student(nid)
165 ON DELETE CASCADE
166 ON UPDATE CASCADE,
167 FOREIGN KEY (course) REFERENCES Course(code)
168 ON DELETE CASCADE
169 ON UPDATE CASCADE,
170 PRIMARY KEY (nid)
171);
172
173INSERT INTO HasRead VALUES ('1995-10-24-8484', '3', '5');
174INSERT INTO HasRead VALUES ('1851-10-24-1337', '3', '3');
175INSERT INTO HasRead VALUES ('1723-01-23-2211', '1', 'U');
176INSERT INTO HasRead VALUES ('1980-05-10-3313', '4', '5');
177
178CREATE TABLE LimitedCourse(
179 maxStudent REAL NOT NULL,
180 course TEXT PRIMARY KEY,
181 FOREIGN KEY (course) REFERENCES Course(code)
182 ON DELETE CASCADE
183 ON UPDATE CASCADE
184);
185
186INSERT INTO LimitedCourse VALUES ('10', '5');
187INSERT INTO LimitedCourse VALUES ('13', '6');
188
189CREATE TABLE WaitingList(
190 nid TEXT,
191 course TEXT,
192 place TEXT,
193 FOREIGN KEY (nid) REFERENCES Student(nid)
194 ON DELETE CASCADE
195 ON UPDATE CASCADE,
196 FOREIGN KEY (course) REFERENCES Course(code)
197 ON DELETE CASCADE
198 ON UPDATE CASCADE,
199 PRIMARY KEY (nid, course)
200 --UNIQUE(place, course)
201);
202
203INSERT INTO WaitingList VALUES ('1851-10-24-1337', '5', '3');
204INSERT INTO WaitingList VALUES ('1851-10-24-1337', '6', '3');
205INSERT INTO WaitingList VALUES ('1995-10-24-8484', '5', '3');
206
207CREATE TABLE TypeOfCourse(
208 courseType TEXT PRIMARY KEY
209);
210
211INSERT INTO TypeOfCourse VALUES ('Mathematical');
212INSERT INTO TypeOfCourse VALUES ('Programming');
213INSERT INTO TypeOfCourse VALUES ('Shitcourse');
214
215CREATE TABLE IsA(
216 course TEXT,
217 courseType TEXT,
218 FOREIGN KEY (course) REFERENCES Course(code)
219 ON DELETE CASCADE
220 ON UPDATE CASCADE,
221 FOREIGN KEY (courseType) REFERENCES TypeOfCourse(courseType)
222 ON DELETE CASCADE
223 ON UPDATE CASCADE,
224 PRIMARY KEY (course, courseType)
225);
226
227INSERT INTO IsA VALUES ('1', 'Mathematical');
228INSERT INTO IsA VALUES ('2', 'Programming');
229INSERT INTO IsA VALUES ('5', 'Shitcourse');
230
231CREATE TABLE Gives(
232 department TEXT,
233 course TEXT,
234 FOREIGN KEY (department) REFERENCES Department(name)
235 ON DELETE CASCADE
236 ON UPDATE CASCADE,
237 FOREIGN KEY (course) REFERENCES Course(code)
238 ON DELETE CASCADE
239 ON UPDATE CASCADE,
240 PRIMARY KEY (course, department)
241);
242
243INSERT INTO Gives VALUES ('Dept. of Computer Science', '1');
244INSERT INTO Gives VALUES ('Dept. of Computer Engineering', '2');
245INSERT INTO Gives VALUES ('Dept. of Computer Science', '3');
246INSERT INTO Gives VALUES ('Dept. of Computer Engineering', '4');
247INSERT INTO Gives VALUES ('Dept. of Computer Science', '5');
248INSERT INTO Gives VALUES ('Dept. of Computer Engineering', '6');
249
250
251
252-- VIEWS
253
254-- View: StudentsFollowing
255-- For all students, their basic information (name etc.), and the programme and branch (if any) they are following.
256CREATE VIEW StudentsFollowing AS
257 SELECT *
258 FROM Student;
259
260-- View: FinishedCourses
261-- For all students, all finished courses, along with their names, grades (grade 'U', '3', '4' or '5') and number of credits.
262
263CREATE VIEW FinishedCourses AS
264 SELECT Student.name, Student.nid, HasRead.course, HasRead.grade, Course.credit
265 FROM Student, HasRead, Course
266 WHERE Student.nid = HasRead.nid AND HasRead.course = Course.code;
267
268
269-- View: Registrations
270-- All registered and waiting students for all courses, along with their waiting status ('registered' or 'waiting').
271
272
273CREATE VIEW Registrations AS
274 SELECT Student.name, Student.nid, course, 'waiting'
275 FROM Student, Registered
276 WHERE Student.nid = Registered.nid
277 UNION
278 SELECT Student.name, Student.nid, course, 'registered'
279 FROM Student, WaitingList
280 WHERE Student.nid = WaitingList.nid;
281
282
283-- View: PassedCourses
284-- For all students, all passed courses, i.e. courses finished with a grade other than ‘U’, and the number of credits for those courses.
285-- This view is intended as a helper view towards the PathToGraduation view (and for task 4), and will not be directly used by your application.
286
287CREATE VIEW PassedCourses AS
288 SELECT *
289 FROM FinishedCourses
290 WHERE grade != 'U';
291
292-- View: UnreadMandatory
293-- For all students, the mandatory courses (branch and programme) they have not yet passed.
294-- This view is intended as a helper view towards the PathToGraduation view, and will not be directly used by your application.
295CREATE VIEW UnreadMandatory AS
296 SELECT Student.name, Student.nid, course, credit
297 FROM Student, ProgramMandatory, Course
298 WHERE Student.program = ProgramMandatory.program AND ProgramMandatory.course = Course.code AND NOT EXISTS
299 (SELECT PassedCourses.course
300 FROM PassedCourses
301 WHERE Student.nid = PassedCourses.nid AND PassedCourses.course = ProgramMandatory.course)
302 UNION
303 SELECT Student.name, Student.nid, course, credit
304 FROM Student, BranchMandatory, Course
305 WHERE Student.branch = BranchMandatory.branch AND Course.code = BranchMandatory.course AND NOT EXISTS
306 (SELECT PassedCourses.course
307 FROM PassedCourses
308 WHERE Student.nid = PassedCourses.nid AND PassedCourses.course = BranchMandatory.course);
309
310-- View: PathToGraduation
311-- For all students, their path to graduation, i.e. a view with columns for
312-- the number of credits they have taken.
313-- the number of mandatory courses they have yet to read (branch or programme).
314-- the number of credits they have taken in courses that are classified as math courses.
315-- the number of credits they have taken in courses that are classified as research courses.
316-- the number of seminar courses they have read.
317-- whether or not they qualify for graduation.
318
319CREATE VIEW ReadCredits AS
320 SELECT Student.name, Student.nid, SUM(PassedCourses.credit) AS ReadCredits
321 FROM Student, PassedCourses
322 WHERE Student.nid = PassedCourses.nid
323 GROUP BY Student.name, Student.nid;
324
325CREATE VIEW RemainingCredits AS
326 SELECT Student.name, Student.nid, SUM(UnreadMandatory.credit) AS RemainingCredits
327 FROM Student, UnreadMandatory
328 WHERE Student.nid = UnreadMandatory.nid
329 GROUP BY Student.name, Student.nid;
330
331CREATE VIEW PathToGraduation AS
332 SELECT coalesce (ReadCredits.nid, RemainingCredits.nid), ReadCredits, RemainingCredits
333 FROM ReadCredits FULL OUTER JOIN RemainingCredits ON ReadCredits.nid = RemainingCredits.nid