2
login VARCHAR UNIQUE NOT NULL,
3
loginid SERIAL PRIMARY KEY NOT NULL,
6
studentid VARCHAR -- may be null
9
CREATE TABLE offering (
10
offeringid SERIAL PRIMARY KEY NOT NULL,
11
subj_name VARCHAR NOT NULL,
12
subj_code VARCHAR NOT NULL,
13
year CHAR(4) NOT NULL,
14
semester CHAR(1) NOT NULL,
18
CREATE TABLE project (
19
projectid SERIAL PRIMARY KEY NOT NULL,
22
offeringid INT4 REFERENCES offering (offeringid) NOT NULL,
26
CREATE TABLE project_group (
27
groupnm VARCHAR NOT NULL,
28
groupid SERIAL PRIMARY KEY NOT NULL,
29
offeringid INT4 REFERENCES offering (offeringid),
31
createdby INT4 REFERENCES login (loginid) NOT NULL,
32
epoch TIMESTAMP NOT NULL,
33
UNIQUE (offeringid, groupnm)
36
CREATE TABLE group_invitation (
37
loginid INT4 REFERENCES login (loginid) NOT NULL,
38
groupid INT4 REFERENCES project_group (groupid) NOT NULL,
39
inviter INT4 REFERENCES login (loginid) NOT NULL,
40
invited TIMESTAMP NOT NULL,
42
UNIQUE (loginid,groupid)
45
CREATE TABLE group_member (
46
loginid INT4 REFERENCES login (loginid),
47
groupid INT4 REFERENCES project_group (groupid),
48
projectid INT4 REFERENCES project (projectid),
49
UNIQUE (loginid,projectid),
50
PRIMARY KEY (loginid,groupid)
1
-- We need a users database to do authorization, manage groups, &c
6
DROP TABLE group_members;
11
login varchar(80) PRIMARY KEY, -- login id
16
groupid varchar(18) PRIMARY KEY, -- group name Y^4-S^9-G^3
17
nick varchar(80), -- group nickname
18
subject varchar(9), -- subject code
19
year varchar(4) -- when
22
CREATE TABLE group_members (
23
login varchar(80) REFERENCES users (login),
24
groupid varchar(18) REFERENCES groups (groupid)
53
27
CREATE TABLE enrolment (
54
loginid INT4 REFERENCES login (loginid),
55
offeringid INT4 REFERENCES offering (offeringid),
57
special_result VARCHAR,
59
special_supp_result VARCHAR,
61
PRIMARY KEY (loginid,offeringid)
64
CREATE TABLE ivle_role (
65
loginid INT4 PRIMARY KEY REFERENCES login (loginid),
69
CREATE TABLE assessed (
70
assessedid SERIAL PRIMARY KEY NOT NULL,
71
loginid INT4 REFERENCES login (loginid),
72
groupid INT4 REFERENCES project_group (groupid),
73
-- exactly one of loginid and groupid must be non-null
74
CHECK ((loginid IS NOT NULL AND groupid IS NULL)
75
OR (loginid IS NULL AND groupid IS NOT NULL))
78
CREATE TABLE project_extension (
79
assessedid INT4 REFERENCES assessed (assesedid) NOT NULL,
80
projectid INT4 REFERENCES project (projectid) NOT NULL,
81
deadline TIMESTAMP NOT NULL,
82
approver INT4 REFERENCES login (loginid) NOT NULL,
86
CREATE TABLE project_mark (
87
assessedid INT4 REFERENCES assessed (assesedid) NOT NULL,
88
projectid INT4 REFERENCES project (projectid) NOT NULL,
90
marker INT4 REFERENCES login (loginid) NOT NULL,
97
CREATE TABLE problem (
98
problemid SERIAL PRIMARY KEY NOT NULL,
102
CREATE TABLE problem_tag (
103
problemid INT4 REFERENCES tutorial_problem (problemid),
104
tag VARCHAR NOT NULL,
105
added_by INT4 REFERENCES login (loginid) NOT NULL,
106
date TIMESTAMP NOT NULL,
107
PRIMARY KEY (problemid,added_by,tag)
110
CREATE TABLE problem_test_case (
111
problemid INT4 REFERENCES problem (problemid) NOT NULL,
112
testcaseid SERIAL UNIQUE NOT NULL,
115
visibility VARCHAR CHECK (visibility in ('public', 'protected', 'private'))
118
CREATE TABLE problem_test_case_tag (
119
testcaseid INT4 REFERENCES problem_test_case (testcaseid) NOT NULL,
120
tag VARCHAR NOT NULL,
122
added_by INT4 REFERENCES login (loginid) NOT NULL,
123
date TIMESTAMP NOT NULL,
124
PRIMARY KEY (testcaseid,added_by,tag)
127
CREATE TABLE problem_attempt (
128
problemid INT4 REFERENCES problem (problemid) NOT NULL,
129
loginid INT4 REFERENCES login (loginid) NOT NULL,
130
date TIMESTAMP NOT NULL,
131
attempt VARCHAR NOT NULL,
132
complete BOOLEAN NOT NULL,
133
PRIMARY KEY (problemid,loginid,date)
136
CREATE INDEX problem_attempt_index ON problem_attempt (problemid, loginid);
138
CREATE TABLE problem_attempt_breakdown (
139
problemid INT4 REFERENCES problem (problemid) NOT NULL,
140
testcaseid INT4 REFERENCES problem_test_case (testcaseid) NOT NULL,
141
loginid INT4 REFERENCES login (loginid) NOT NULL,
142
date TIMESTAMP NOT NULL,
146
CREATE TABLE problem_prerequisite (
147
parent INT4 REFERENCES problem (problemid) NOT NULL,
148
child INT4 REFERENCES problem (problemid) NOT NULL,
149
PRIMARY KEY (parent,child)
28
login varchar(80) REFERENCES users (login),
34
login varchar(80) REFERENCES users (login),
38
INSERT INTO users (login,nick) values ('conway', 'Tom');
39
INSERT INTO roles (login,role) values ('conway', 'student');
40
INSERT INTO users (login,nick) values ('apeel', 'Andrew');
41
INSERT INTO roles (login,role) values ('apeel', 'student');
42
INSERT INTO users (login,nick) values ('mgiuca', 'Matt');
43
INSERT INTO roles (login,role) values ('mgiuca', 'tutor');
44
INSERT INTO users (login,nick) values ('sb', 'Steven');
45
INSERT INTO roles (login,role) values ('sb', 'lecturer');
46
INSERT INTO users (login,nick) values ('mpp', 'Mike');
47
INSERT INTO roles (login,role) values ('mpp', 'student');
48
INSERT INTO users (login,nick) values ('ivo', 'Ivo');
49
INSERT INTO roles (login,role) values ('ivo', 'admin');
51
INSERT INTO groups (groupid, nick, subject, year) values ('2007-INFO10001-321', 'Purple Alert', 'INFO10001', '2008');
52
INSERT INTO groups (groupid, nick, subject, year) values ('2007-INFO10001-322', 'Blind Illuminati', 'INFO10001', '2008');
54
INSERT INTO group_members (login,groupid) values ('conway', '2007-INFO10001-321');
55
INSERT INTO group_members (login,groupid) values ('apeel', '2007-INFO10001-321');
56
INSERT INTO group_members (login,groupid) values ('mgiuca', '2007-INFO10001-321');
57
INSERT INTO group_members (login,groupid) values ('sb', '2007-INFO10001-321');
58
INSERT INTO group_members (login,groupid) values ('mpp', '2007-INFO10001-322');
59
INSERT INTO group_members (login,groupid) values ('ivo', '2007-INFO10001-322');
61
INSERT INTO enrolment (login,subject,year) values ('conway' , 'INFO10001', '2008');
62
INSERT INTO enrolment (login,subject,year) values ('apeel' , 'INFO10001', '2008');
63
INSERT INTO enrolment (login,subject,year) values ('mgiuca' , 'INFO10001', '2008');
64
INSERT INTO enrolment (login,subject,year) values ('sb' , 'INFO10001', '2008');
65
INSERT INTO enrolment (login,subject,year) values ('mpp' , 'INFO10001', '2008');
66
INSERT INTO enrolment (login,subject,year) values ('ivo' , 'INFO10001', '2008');