1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
|
CREATE TABLE login (
loginid SERIAL PRIMARY KEY NOT NULL,
login VARCHAR UNIQUE NOT NULL,
passhash VARCHAR,
state VARCHAR NOT NULL CHECK (state in ('no_agreement', 'enabled', 'disabled')),
unixid INT UNIQUE NOT NULL, -- unix user id
nick VARCHAR NOT NULL,
fullname VARCHAR NOT NULL,
rolenm VARCHAR NOT NULL CHECK (rolenm in ('anyone', 'student', 'tutor', 'lecturer', 'admin')),
studentid VARCHAR -- may be null
);
CREATE TABLE offering (
offeringid SERIAL PRIMARY KEY NOT NULL,
subj_name VARCHAR NOT NULL,
subj_code VARCHAR NOT NULL,
year CHAR(4) NOT NULL,
semester CHAR(1) NOT NULL,
url VARCHAR
);
CREATE TABLE project (
projectid SERIAL PRIMARY KEY NOT NULL,
synopsis VARCHAR,
url VARCHAR,
offeringid INT4 REFERENCES offering (offeringid) NOT NULL,
deadline TIMESTAMP
);
CREATE TABLE project_group (
groupnm VARCHAR NOT NULL,
groupid SERIAL PRIMARY KEY NOT NULL,
offeringid INT4 REFERENCES offering (offeringid),
nick VARCHAR,
createdby INT4 REFERENCES login (loginid) NOT NULL,
epoch TIMESTAMP NOT NULL,
UNIQUE (offeringid, groupnm)
);
CREATE TABLE group_invitation (
loginid INT4 REFERENCES login (loginid) NOT NULL,
groupid INT4 REFERENCES project_group (groupid) NOT NULL,
inviter INT4 REFERENCES login (loginid) NOT NULL,
invited TIMESTAMP NOT NULL,
accepted TIMESTAMP,
UNIQUE (loginid,groupid)
);
CREATE TABLE group_member (
loginid INT4 REFERENCES login (loginid),
groupid INT4 REFERENCES project_group (groupid),
projectid INT4 REFERENCES project (projectid),
UNIQUE (loginid,projectid),
PRIMARY KEY (loginid,groupid)
);
CREATE TABLE enrolment (
loginid INT4 REFERENCES login (loginid),
offeringid INT4 REFERENCES offering (offeringid),
result INT,
special_result VARCHAR,
supp_result INT,
special_supp_result VARCHAR,
notes VARCHAR,
PRIMARY KEY (loginid,offeringid)
);
CREATE TABLE assessed (
assessedid SERIAL PRIMARY KEY NOT NULL,
loginid INT4 REFERENCES login (loginid),
groupid INT4 REFERENCES project_group (groupid),
-- exactly one of loginid and groupid must be non-null
CHECK ((loginid IS NOT NULL AND groupid IS NULL)
OR (loginid IS NULL AND groupid IS NOT NULL))
);
CREATE TABLE project_extension (
assessedid INT4 REFERENCES assessed (assessedid) NOT NULL,
projectid INT4 REFERENCES project (projectid) NOT NULL,
deadline TIMESTAMP NOT NULL,
approver INT4 REFERENCES login (loginid) NOT NULL,
notes VARCHAR
);
CREATE TABLE project_mark (
assessedid INT4 REFERENCES assessed (assessedid) NOT NULL,
projectid INT4 REFERENCES project (projectid) NOT NULL,
componentid INT4,
marker INT4 REFERENCES login (loginid) NOT NULL,
mark INT,
marked TIMESTAMP,
feedback VARCHAR,
notes VARCHAR
);
CREATE TABLE problem (
problemid SERIAL PRIMARY KEY NOT NULL,
spec VARCHAR
);
CREATE TABLE problem_tag (
problemid INT4 REFERENCES problem (problemid),
tag VARCHAR NOT NULL,
description VARCHAR,
standard BOOLEAN NOT NULL,
added_by INT4 REFERENCES login (loginid) NOT NULL,
date TIMESTAMP NOT NULL,
PRIMARY KEY (problemid,added_by,tag)
);
CREATE TABLE problem_test_case (
problemid INT4 REFERENCES problem (problemid) NOT NULL,
testcaseid SERIAL UNIQUE NOT NULL,
testcase VARCHAR,
description VARCHAR,
visibility VARCHAR CHECK (visibility in ('public', 'protected', 'private'))
);
CREATE TABLE problem_test_case_tag (
testcaseid INT4 REFERENCES problem_test_case (testcaseid) NOT NULL,
tag VARCHAR NOT NULL,
description VARCHAR,
standard BOOLEAN NOT NULL,
added_by INT4 REFERENCES login (loginid) NOT NULL,
date TIMESTAMP NOT NULL,
PRIMARY KEY (testcaseid,added_by,tag)
);
CREATE TABLE problem_attempt (
problemid INT4 REFERENCES problem (problemid) NOT NULL,
loginid INT4 REFERENCES login (loginid) NOT NULL,
date TIMESTAMP NOT NULL,
attempt VARCHAR NOT NULL,
complete BOOLEAN NOT NULL,
PRIMARY KEY (problemid,loginid,date)
);
CREATE INDEX problem_attempt_index ON problem_attempt (problemid, loginid);
CREATE TABLE problem_attempt_breakdown (
problemid INT4 REFERENCES problem (problemid) NOT NULL,
testcaseid INT4 REFERENCES problem_test_case (testcaseid) NOT NULL,
loginid INT4 REFERENCES login (loginid) NOT NULL,
date TIMESTAMP NOT NULL,
result BOOLEAN
);
CREATE TABLE problem_prerequisite (
parent INT4 REFERENCES problem (problemid) NOT NULL,
child INT4 REFERENCES problem (problemid) NOT NULL,
PRIMARY KEY (parent,child)
);
|