· 8 years ago · Dec 02, 2017, 06:32 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 PRIMARY KEY (branch, program, course)
100);
101INSERT INTO BranchMandatory VALUES ('Algorithms', 'Computer Science', 'Dator Teknik');
102INSERT INTO BranchMandatory VALUES ('Robot Wars History', 'Robot Design', 'MatStat');
103INSERT INTO BranchMandatory VALUES ('Digiflisp Programming', 'Computer Engineering', 'Tusenvariables analys');
104
105CREATE TABLE ProgramMandatory(
106 program TEXT NOT NULL,
107 course TEXT NOT NULL,
108 FOREIGN KEY (program) REFERENCES Program(name)
109 ON DELETE CASCADE
110 ON UPDATE CASCADE,
111 FOREIGN KEY (course) REFERENCES Course(code)
112 ON DELETE CASCADE
113 ON UPDATE CASCADE,
114 PRIMARY KEY (program, course)
115);
116INSERT INTO ProgramMandatory VALUES ('Computer Science', '4');
117INSERT INTO ProgramMandatory VALUES ('Robot Design', '3');
118INSERT INTO ProgramMandatory VALUES ('Computer Engineering', '1');
119
120DROP TABLE IF EXISTS Recommendation;
121CREATE TABLE Recommendation(
122 branch TEXT NOT NULL,
123 program TEXT NOT NULL,
124 course TEXT NOT NULL,
125 FOREIGN KEY (branch, program) REFERENCES Branch(name, program)
126 ON DELETE CASCADE
127 ON UPDATE CASCADE,
128 FOREIGN KEY (course) REFERENCES Course(code)
129 ON DELETE CASCADE
130 ON UPDATE CASCADE,
131 PRIMARY KEY (branch, program, course)
132);
133INSERT INTO Recommendation VALUES ('Algorithms', 'Computer Science', '3');
134INSERT INTO Recommendation VALUES ('Robot Wars History', 'Robot Design', '1');
135INSERT INTO Recommendation VALUES ('Digiflisp Programming', 'Computer Engineering', '5');
136
137DROP TABLE IF EXISTS Registered;
138CREATE TABLE Registered(
139 nid TEXT NOT NULL,
140 course TEXT NOT NULL,
141 FOREIGN KEY (nid) REFERENCES Student(nid)
142 ON DELETE CASCADE
143 ON UPDATE CASCADE,
144 FOREIGN KEY (course) REFERENCES Course(code)
145 ON DELETE CASCADE
146 ON UPDATE CASCADE,
147 PRIMARY KEY(nid, course)
148);
149INSERT INTO Registered VALUES ('1995-10-24-8484', '1');
150INSERT INTO Registered VALUES ('1851-10-24-1337', '2');
151INSERT INTO Registered VALUES ('0000-12-24-1111', '3');
152INSERT INTO Registered VALUES ('1993-12-24-1111', '4');
153INSERT INTO Registered VALUES ('1723-01-23-2211', '5');
154INSERT INTO Registered VALUES ('1980-05-10-3313', '6');
155
156CREATE TYPE grade AS ENUM('U', '3', '4', '5');
157CREATE TABLE HasRead(
158 nid TEXT NOT NULL,
159 course TEXT NOT NULL,
160 grade grade NOT NULL,
161 FOREIGN KEY (nid) REFERENCES Student(nid)
162 ON DELETE CASCADE
163 ON UPDATE CASCADE,
164 FOREIGN KEY (course) REFERENCES Course(code)
165 ON DELETE CASCADE
166 ON UPDATE CASCADE,
167 PRIMARY KEY (nid)
168);
169
170INSERT INTO HasRead VALUES ('1995-10-24-8484', '3', '5');
171INSERT INTO HasRead VALUES ('1851-10-24-1337', '3', '3');
172INSERT INTO HasRead VALUES ('1723-01-23-2211', '1', 'U');
173INSERT INTO HasRead VALUES ('1980-05-10-3313', '2', '5');
174
175CREATE TABLE LimitedCourse(
176 maxStudent REAL NOT NULL,
177 course TEXT PRIMARY KEY,
178 FOREIGN KEY (course) REFERENCES Course(code)
179 ON DELETE CASCADE
180 ON UPDATE CASCADE
181);
182
183INSERT INTO LimitedCourse VALUES ('10', '5');
184INSERT INTO LimitedCourse VALUES ('13', '6');
185
186CREATE TABLE WaitingList(
187 nid TEXT,
188 course TEXT,
189 place TEXT,
190 FOREIGN KEY (nid) REFERENCES Student(nid)
191 ON DELETE CASCADE
192 ON UPDATE CASCADE,
193 FOREIGN KEY (course) REFERENCES Course(code)
194 ON DELETE CASCADE
195 ON UPDATE CASCADE,
196 PRIMARY KEY (nid, course)
197 --UNIQUE(place, course)
198);
199
200INSERT INTO WaitingList VALUES ('1851-10-24-1337', '5', '3');
201INSERT INTO WaitingList VALUES ('1851-10-24-1337', '6', '3');
202INSERT INTO WaitingList VALUES ('1995-10-24-8484', '5', '3');
203
204CREATE TABLE TypeOfCourse(
205 courseType TEXT PRIMARY KEY
206);
207
208INSERT INTO TypeOfCourse VALUES ('Mathematical');
209INSERT INTO TypeOfCourse VALUES ('Programming');
210INSERT INTO TypeOfCourse VALUES ('Shitcourse');
211
212CREATE TABLE IsA(
213 course TEXT,
214 courseType TEXT,
215 FOREIGN KEY (course) REFERENCES Course(code)
216 ON DELETE CASCADE
217 ON UPDATE CASCADE,
218 FOREIGN KEY (courseType) REFERENCES TypeOfCourse(courseType)
219 ON DELETE CASCADE
220 ON UPDATE CASCADE,
221 PRIMARY KEY (course, courseType)
222);
223
224INSERT INTO IsA VALUES ('1', 'Mathematical');
225INSERT INTO IsA VALUES ('2', 'Programming');
226INSERT INTO IsA VALUES ('5', 'Shitcourse');
227
228CREATE TABLE Gives(
229 department TEXT,
230 course TEXT,
231 FOREIGN KEY (department) REFERENCES Department(name)
232 ON DELETE CASCADE
233 ON UPDATE CASCADE,
234 FOREIGN KEY (course) REFERENCES Course(code)
235 ON DELETE CASCADE
236 ON UPDATE CASCADE,
237 PRIMARY KEY (course, department)
238);
239
240INSERT INTO Gives VALUES ('Dept. of Computer Science', '1');
241INSERT INTO Gives VALUES ('Dept. of Computer Engineering', '2');
242INSERT INTO Gives VALUES ('Dept. of Computer Science', '3');
243INSERT INTO Gives VALUES ('Dept. of Computer Engineering', '4');
244INSERT INTO Gives VALUES ('Dept. of Computer Science', '5');
245INSERT INTO Gives VALUES ('Dept. of Computer Engineering', '6');
246
247
248
249-- VIEWS
250
251-- View: StudentsFollowing
252-- For all students, their basic information (name etc.), and the programme and branch (if any) they are following.
253CREATE VIEW StudentsFollowing AS
254 SELECT *
255 FROM Student;
256
257-- View: FinishedCourses
258-- For all students, all finished courses, along with their names, grades (grade 'U', '3', '4' or '5') and number of credits.
259
260CREATE VIEW FinishedCourses AS
261 SELECT Student.name, HasRead.course, HasRead.grade, Course.credit
262 FROM Student, HasRead, Course
263 WHERE Student.nid = HasRead.nid AND HasRead.course = Course.code;
264
265
266-- View: Registrations
267-- All registered and waiting students for all courses, along with their waiting status ('registered' or 'waiting').
268
269
270CREATE VIEW Registrations AS
271 SELECT Student.name, course, 'waiting'
272 FROM Student, Registered
273 WHERE Student.nid = Registered.nid
274 UNION
275 SELECT Student.name, course, 'registered'
276 FROM Student, WaitingList
277 WHERE Student.nid = WaitingList.nid;
278
279
280-- View: PassedCourses
281-- For all students, all passed courses, i.e. courses finished with a grade other than ‘U’, and the number of credits for those courses.
282-- 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.
283
284CREATE VIEW PassedCourses AS
285 SELECT *
286 FROM FinishedCourses
287 WHERE grade != 'U';
288
289-- View: UnreadMandatory
290-- For all students, the mandatory courses (branch and programme) they have not yet passed.
291-- This view is intended as a helper view towards the PathToGraduation view, and will not be directly used by your application.
292CREATE VIEW UnreadMandatory AS
293 SELECT Student.name, course
294 FROM Student, ProgramMandatory
295 WHERE Student.program = ProgramMandatory.program AND NOT EXISTS
296 (SELECT PassedCourses.course
297 FROM PassedCourses
298 WHERE Student.name = PassedCourses.name AND PassedCourses.course = ProgramMandatory.course)
299 UNION
300 SELECT Student.name, course
301 FROM Student, BranchMandatory
302 WHERE Student.branch = BranchMandatory.branch AND NOT EXISTS
303 (SELECT PassedCourses.course
304 FROM PassedCourses
305 WHERE Student.name = PassedCourses.name AND PassedCourses.course = BranchMandatory.course);
306
307-- View: PathToGraduation
308-- For all students, their path to graduation, i.e. a view with columns for
309-- the number of credits they have taken.
310-- the number of mandatory courses they have yet to read (branch or programme).
311-- the number of credits they have taken in courses that are classified as math courses.
312-- the number of credits they have taken in courses that are classified as research courses.
313-- the number of seminar courses they have read.
314-- whether or not they qualify for graduation.
315--CREATE VIEW PathToGraduation AS