CXvi2gTy

1DROP TABLE IF EXISTS Programs CASCADE;
2CREATE TABLE Programs(
3name TEXT NOT NULL,
4abbreviation TEXT NOT NULL,
5PRIMARY KEY(name)
6); 
7
8DROP TABLE IF EXISTS Branches CASCADE;
9CREATE TABLE Branches(
10name TEXT NOT NULL,
11program TEXT NOT NULL,
12PRIMARY KEY(name, program),
13FOREIGN KEY (program) REFERENCES Programs(name)
14); 
15
16DROP TABLE IF EXISTS Students CASCADE;
17CREATE TABLE Students(
18idnr CHAR(10) PRIMARY KEY NOT NULL,
19name TEXT NOT NULL,
20login TEXT NOT NULL,
21program TEXT NOT NULL,
22FOREIGN KEY (program) REFERENCES Programs(name),
23UNIQUE(login)
24);
25
26DROP TABLE IF EXISTS StudentBranches CASCADE;
27CREATE TABLE StudentBranches(
28student TEXT PRIMARY KEY NOT NULL,
29branch CHAR(2) NOT NULL,
30program TEXT NOT NULL,
31FOREIGN KEY (student) REFERENCES Students(idnr),
32FOREIGN KEY (branch, program) REFERENCES Branches(name, program)
33);
34
35DROP TABLE IF EXISTS Departments CASCADE;
36CREATE TABLE Departments(
37name TEXT NOT NULL,
38abbreviation TEXT NOT NULL,
39PRIMARY KEY(name),
40UNIQUE(abbreviation)
41); 
42
43DROP TABLE IF EXISTS Courses CASCADE;
44CREATE TABLE Courses(
45code CHAR(6) PRIMARY KEY NOT NULL, 
46name TEXT NOT NULL, 
47credits REAL NOT NULL, 
48department TEXT NOT NULL,
49FOREIGN KEY (department) REFERENCES Departments(name)
50);
51
52DROP TABLE IF EXISTS Classifications CASCADE;
53CREATE TABLE Classifications(
54name TEXT PRIMARY KEY NOT NULL
55);
56
57DROP TABLE IF EXISTS Classified CASCADE;
58CREATE TABLE Classified(
59course CHAR(6) NOT NULL,
60classification TEXT NOT NULL,
61PRIMARY KEY(course, classification),
62FOREIGN KEY (course) REFERENCES Courses(code),
63FOREIGN KEY (classification) REFERENCES Classifications(name)
64);
65
66DROP TABLE IF EXISTS LimitedCourses CASCADE;
67CREATE TABLE LimitedCourses(
68code CHAR(6) PRIMARY KEY NOT NULL,
69capacity INT NOT NULL,
70FOREIGN KEY (code) REFERENCES Courses(code)
71);
72 
73DROP TABLE IF EXISTS MandatoryProgram CASCADE;
74CREATE TABLE MandatoryProgram(
75course CHAR(6) NOT NULL,
76program TEXT NOT NULL,
77PRIMARY KEY(course, program),
78FOREIGN KEY (program) REFERENCES Programs(name),
79FOREIGN KEY (course) REFERENCES Courses(code)
80
81);
82 
83DROP TABLE IF EXISTS MandatoryBranch CASCADE;
84CREATE TABLE MandatoryBranch(
85course CHAR(6) NOT NULL,
86branch CHAR(2) NOT NULL,
87program TEXT NOT NULL,
88PRIMARY KEY(course, branch, program),
89FOREIGN KEY (course) REFERENCES Courses(code),
90FOREIGN KEY (branch, program) REFERENCES Branches(name, program)
91);
92 
93DROP TABLE IF EXISTS RecommendedBranch CASCADE;
94CREATE TABLE RecommendedBranch(
95course CHAR(6) NOT NULL,
96branch CHAR(2) NOT NULL,
97program TEXT NOT NULL,
98PRIMARY KEY(course, branch, program),
99FOREIGN KEY (course) REFERENCES Courses(code),
100FOREIGN KEY (branch, program) REFERENCES Branches(name, program)
101);
102 
103DROP TABLE IF EXISTS Registered CASCADE;
104CREATE TABLE Registered(
105student TEXT NOT NULL,
106course CHAR(6) NOT NULL,
107PRIMARY KEY(student, course),
108FOREIGN KEY (student) REFERENCES Students(idnr),
109FOREIGN KEY (course) REFERENCES Courses(code)
110);
111 
112DROP TABLE IF EXISTS Taken CASCADE;
113CREATE TABLE Taken(
114student TEXT NOT NULL,
115course CHAR(6) NOT NULL,
116grade TEXT NOT NULL,
117PRIMARY KEY(student, course),
118FOREIGN KEY (student) REFERENCES Students(idnr),
119FOREIGN KEY (course) REFERENCES Courses(code)
120);
121 
122-- position is either a SERIAL, a TIMESTAMP or the actual position
123DROP TABLE IF EXISTS WaitingList CASCADE;
124CREATE TABLE WaitingList(
125student TEXT NOT NULL,
126course CHAR(6) NOT NULL,
127position TEXT NOT NULL,
128PRIMARY KEY(student, course),
129UNIQUE(course, position),
130FOREIGN KEY (student) REFERENCES Students(idnr),
131FOREIGN KEY (course) REFERENCES Courses(code)
132);