RPQ30Aq8

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,
107    course TEXT,
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);
116
117INSERT INTO ProgramMandatory VALUES ('Computer Science', '4');
118INSERT INTO ProgramMandatory VALUES ('Robot Design', '3');
119INSERT INTO ProgramMandatory VALUES ('Computer Engineering', '1');
120
121CREATE TABLE Recommendation(
122    branch TEXT,
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', 'Tusenvariables analys');
134INSERT INTO Recommendation VALUES ('Robot Design', 'Robot building');
135INSERT INTO Recommendation VALUES ('Digiflisp Programming', 'Computer Engineering', 'Dator Teknik');
136
137
138CREATE TABLE Registered(
139    nid TEXT,
140    course TEXT,
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);
149
150CREATE TYPE grade AS ENUM('U', '3', '4', '5')
151CREATE TABLE HasRead(
152    nid TEXT NOT NULL,
153    course TEXT NOT NULL,
154    grade grade NOT NULL,
155    FOREIGN KEY (nid) REFERENCES Student(nid)
156        ON DELETE CASCADE
157        ON UPDATE CASCADE,
158    FOREIGN KEY (course) REFERENCES Course(code)
159        ON DELETE CASCADE
160        ON UPDATE CASCADE,
161    PRIMARY KEY (nid)
162);
163
164CREATE TABLE WaitingList(
165    nid TEXT,
166    course TEXT,
167    place TEXT,
168    FOREIGN KEY (nid) REFERENCES Student(nid)
169        ON DELETE CASCADE
170        ON UPDATE CASCADE,
171    FOREIGN KEY (course) REFERENCES Course(code)
172        ON DELETE CASCADE
173        ON UPDATE CASCADE,
174    PRIMARY KEY (place, course),
175    UNIQUE(place, course)
176);
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
186CREATE TABLE TypeOfCourse(
187    courseType TEXT PRIMARY KEY
188);
189
190CREATE TABLE IsA(
191    course TEXT,
192    courseType TEXT,
193    FOREIGN KEY (course) REFERENCES Course(code)
194        ON DELETE CASCADE
195        ON UPDATE CASCADE,
196    FOREIGN KEY (courseType) REFERENCES TypeOfCourse(courseType)
197        ON DELETE CASCADE
198        ON UPDATE CASCADE,
199    PRIMARY KEY (course, courseType)
200);
201
202CREATE TABLE Gives(
203    department TEXT,
204    course TEXT,
205    FOREIGN KEY (department) REFERENCES Department(name)
206        ON DELETE CASCADE
207        ON UPDATE CASCADE,
208    FOREIGN KEY (course) REFERENCES Course(code)
209        ON DELETE CASCADE
210        ON UPDATE CASCADE,
211    PRIMARY KEY (course, department)
212);
213
214--  VIEWS
215
216-- View: StudentsFollowing
217-- For all students, their basic information (name etc.), and the programme and branch (if any) they are following.
218CREATE VIEW StudentsFollowing AS 
219    SELECT *
220    FROM Student;
221
222-- View: FinishedCourses
223-- For all students, all finished courses, along with their names, grades (grade 'U', '3', '4' or '5') and number of credits.
224/*
225CREATE VIEW FinishedCourses AS
226    SELECT Student.nid, HasRead.*, Course.credit
227    FROM HasRead
228    INNER JOIN 
229*/
230    
231-- View: Registrations
232-- All registered and waiting students for all courses, along with their waiting status ('registered' or 'waiting').
233/*
234CREATE VIEW Registrations AS
235    SELECT Student.nid, Registered.*, WaitingList.*
236    FROM Student, Registered, WaitingList
237    WHERE (Student.nid = Registered.nid) OR (Student.nid = WaitingList.nid);
238*/
239
240-- View: PassedCourses
241-- For all students, all passed courses, i.e. courses finished with a grade other than ‘U’, and the number of credits for those courses. 
242-- 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.
243--CREATE VIEW PassedCourses AS
244
245-- View: UnreadMandatory
246-- For all students, the mandatory courses (branch and programme) they have not yet passed. 
247-- This view is intended as a helper view towards the PathToGraduation view, and will not be directly used by your application.
248--CREATE VIEW UnreadMandatory AS
249
250-- View: PathToGraduation
251-- For all students, their path to graduation, i.e. a view with columns for
252-- the number of credits they have taken.
253-- the number of mandatory courses they have yet to read (branch or programme).
254-- the number of credits they have taken in courses that are classified as math courses.
255-- the number of credits they have taken in courses that are classified as research courses.
256-- the number of seminar courses they have read.
257-- whether or not they qualify for graduation.
258--CREATE VIEW PathToGraduation AS