|
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
1 |
CREATE TABLE login ( |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
2 |
login VARCHAR UNIQUE NOT NULL, |
3 |
loginid SERIAL PRIMARY KEY NOT NULL, |
|
4 |
nick VARCHAR, |
|
5 |
fullname VARCHAR, |
|
|
366
by drtomc
Somehow a couple of changed that address issues sb raised didn't get committed previously. Here they are. |
6 |
rolenm VARCHAR, |
|
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
7 |
studentid VARCHAR -- may be null |
|
25
by drtomc
A bit more work on the userdb stuff. |
8 |
);
|
9 |
||
|
353
by drtomc
Start addressing sb's suggestions. |
10 |
CREATE TABLE offering ( |
|
324
by drtomc
Should all be okay now. |
11 |
offeringid SERIAL PRIMARY KEY NOT NULL, |
12 |
subj_name VARCHAR NOT NULL, |
|
13 |
subj_code VARCHAR NOT NULL, |
|
14 |
year CHAR(4) NOT NULL, |
|
|
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
15 |
semester CHAR(1) NOT NULL, |
16 |
url VARCHAR |
|
|
324
by drtomc
Should all be okay now. |
17 |
);
|
18 |
||
|
355
by drtomc
Fix a few typos and glitches to actually create the ivle database. |
19 |
CREATE TABLE project ( |
20 |
projectid SERIAL PRIMARY KEY NOT NULL, |
|
21 |
synopsis VARCHAR, |
|
22 |
url VARCHAR, |
|
23 |
offeringid INT4 REFERENCES offering (offeringid) NOT NULL, |
|
24 |
deadline TIMESTAMP |
|
25 |
);
|
|
26 |
||
27 |
CREATE TABLE project_group ( |
|
|
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
28 |
groupnm VARCHAR NOT NULL, |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
29 |
groupid SERIAL PRIMARY KEY NOT NULL, |
|
353
by drtomc
Start addressing sb's suggestions. |
30 |
offeringid INT4 REFERENCES offering (offeringid), |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
31 |
nick VARCHAR, |
|
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
32 |
createdby INT4 REFERENCES login (loginid) NOT NULL, |
33 |
epoch TIMESTAMP NOT NULL, |
|
|
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
34 |
UNIQUE (offeringid, groupnm) |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
35 |
);
|
36 |
||
|
353
by drtomc
Start addressing sb's suggestions. |
37 |
CREATE TABLE group_invitation ( |
|
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
38 |
loginid INT4 REFERENCES login (loginid) NOT NULL, |
|
355
by drtomc
Fix a few typos and glitches to actually create the ivle database. |
39 |
groupid INT4 REFERENCES project_group (groupid) NOT NULL, |
|
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
40 |
inviter INT4 REFERENCES login (loginid) NOT NULL, |
41 |
invited TIMESTAMP NOT NULL, |
|
42 |
accepted TIMESTAMP, |
|
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
43 |
UNIQUE (loginid,groupid) |
|
25
by drtomc
A bit more work on the userdb stuff. |
44 |
);
|
45 |
||
|
353
by drtomc
Start addressing sb's suggestions. |
46 |
CREATE TABLE group_member ( |
|
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
47 |
loginid INT4 REFERENCES login (loginid), |
|
355
by drtomc
Fix a few typos and glitches to actually create the ivle database. |
48 |
groupid INT4 REFERENCES project_group (groupid), |
|
353
by drtomc
Start addressing sb's suggestions. |
49 |
projectid INT4 REFERENCES project (projectid), |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
50 |
UNIQUE (loginid,projectid), |
51 |
PRIMARY KEY (loginid,groupid) |
|
|
25
by drtomc
A bit more work on the userdb stuff. |
52 |
);
|
53 |
||
54 |
CREATE TABLE enrolment ( |
|
|
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
55 |
loginid INT4 REFERENCES login (loginid), |
|
353
by drtomc
Start addressing sb's suggestions. |
56 |
offeringid INT4 REFERENCES offering (offeringid), |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
57 |
result INT, |
|
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
58 |
special_result VARCHAR, |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
59 |
supp_result INT, |
|
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
60 |
special_supp_result VARCHAR, |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
61 |
notes VARCHAR, |
62 |
PRIMARY KEY (loginid,offeringid) |
|
|
25
by drtomc
A bit more work on the userdb stuff. |
63 |
);
|
64 |
||
|
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
65 |
CREATE TABLE assessed ( |
66 |
assessedid SERIAL PRIMARY KEY NOT NULL, |
|
67 |
loginid INT4 REFERENCES login (loginid), |
|
|
355
by drtomc
Fix a few typos and glitches to actually create the ivle database. |
68 |
groupid INT4 REFERENCES project_group (groupid), |
|
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
69 |
-- exactly one of loginid and groupid must be non-null
|
70 |
CHECK ((loginid IS NOT NULL AND groupid IS NULL) |
|
71 |
OR (loginid IS NULL AND groupid IS NOT NULL)) |
|
72 |
);
|
|
73 |
||
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
74 |
CREATE TABLE project_extension ( |
|
355
by drtomc
Fix a few typos and glitches to actually create the ivle database. |
75 |
assessedid INT4 REFERENCES assessed (assesedid) NOT NULL, |
|
353
by drtomc
Start addressing sb's suggestions. |
76 |
projectid INT4 REFERENCES project (projectid) NOT NULL, |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
77 |
deadline TIMESTAMP NOT NULL, |
|
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
78 |
approver INT4 REFERENCES login (loginid) NOT NULL, |
79 |
notes VARCHAR |
|
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
80 |
);
|
81 |
||
82 |
CREATE TABLE project_mark ( |
|
|
355
by drtomc
Fix a few typos and glitches to actually create the ivle database. |
83 |
assessedid INT4 REFERENCES assessed (assesedid) NOT NULL, |
|
353
by drtomc
Start addressing sb's suggestions. |
84 |
projectid INT4 REFERENCES project (projectid) NOT NULL, |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
85 |
componentid INT4, |
|
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
86 |
marker INT4 REFERENCES login (loginid) NOT NULL, |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
87 |
mark INT, |
88 |
marked TIMESTAMP, |
|
89 |
feedback VARCHAR, |
|
|
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
90 |
notes VARCHAR |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
91 |
);
|
|
281
by stevenbird
extensive updates based on data modelling discussion with Tom Conway |
92 |
|
93 |
CREATE TABLE problem ( |
|
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
94 |
problemid SERIAL PRIMARY KEY NOT NULL, |
95 |
spec VARCHAR |
|
|
281
by stevenbird
extensive updates based on data modelling discussion with Tom Conway |
96 |
);
|
97 |
||
|
353
by drtomc
Start addressing sb's suggestions. |
98 |
CREATE TABLE problem_tag ( |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
99 |
problemid INT4 REFERENCES tutorial_problem (problemid), |
100 |
tag VARCHAR NOT NULL, |
|
|
366
by drtomc
Somehow a couple of changed that address issues sb raised didn't get committed previously. Here they are. |
101 |
description VARCHAR, |
102 |
standard BOOLEAN NOT NULL, |
|
|
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
103 |
added_by INT4 REFERENCES login (loginid) NOT NULL, |
|
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
104 |
date TIMESTAMP NOT NULL, |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
105 |
PRIMARY KEY (problemid,added_by,tag) |
106 |
);
|
|
107 |
||
108 |
CREATE TABLE problem_test_case ( |
|
109 |
problemid INT4 REFERENCES problem (problemid) NOT NULL, |
|
110 |
testcaseid SERIAL UNIQUE NOT NULL, |
|
111 |
testcase VARCHAR, |
|
112 |
description VARCHAR, |
|
113 |
visibility VARCHAR CHECK (visibility in ('public', 'protected', 'private')) |
|
114 |
);
|
|
115 |
||
|
353
by drtomc
Start addressing sb's suggestions. |
116 |
CREATE TABLE problem_test_case_tag ( |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
117 |
testcaseid INT4 REFERENCES problem_test_case (testcaseid) NOT NULL, |
118 |
tag VARCHAR NOT NULL, |
|
119 |
description VARCHAR, |
|
|
366
by drtomc
Somehow a couple of changed that address issues sb raised didn't get committed previously. Here they are. |
120 |
standard BOOLEAN NOT NULL, |
|
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
121 |
added_by INT4 REFERENCES login (loginid) NOT NULL, |
|
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
122 |
date TIMESTAMP NOT NULL, |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
123 |
PRIMARY KEY (testcaseid,added_by,tag) |
|
281
by stevenbird
extensive updates based on data modelling discussion with Tom Conway |
124 |
);
|
125 |
||
126 |
CREATE TABLE problem_attempt ( |
|
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
127 |
problemid INT4 REFERENCES problem (problemid) NOT NULL, |
|
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
128 |
loginid INT4 REFERENCES login (loginid) NOT NULL, |
|
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
129 |
date TIMESTAMP NOT NULL, |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
130 |
attempt VARCHAR NOT NULL, |
131 |
complete BOOLEAN NOT NULL, |
|
|
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
132 |
PRIMARY KEY (problemid,loginid,date) |
|
281
by stevenbird
extensive updates based on data modelling discussion with Tom Conway |
133 |
);
|
134 |
||
|
324
by drtomc
Should all be okay now. |
135 |
CREATE INDEX problem_attempt_index ON problem_attempt (problemid, loginid); |
|
281
by stevenbird
extensive updates based on data modelling discussion with Tom Conway |
136 |
|
137 |
CREATE TABLE problem_attempt_breakdown ( |
|
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
138 |
problemid INT4 REFERENCES problem (problemid) NOT NULL, |
139 |
testcaseid INT4 REFERENCES problem_test_case (testcaseid) NOT NULL, |
|
|
354
by drtomc
Addressed the rest of sb's suggestions. No doubt this will lead to more suggestions. :-) |
140 |
loginid INT4 REFERENCES login (loginid) NOT NULL, |
|
319
by drtomc
Partial correctness for the user database schema. A few bits and pieces to go. |
141 |
date TIMESTAMP NOT NULL, |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
142 |
result BOOLEAN |
143 |
);
|
|
144 |
||
|
353
by drtomc
Start addressing sb's suggestions. |
145 |
CREATE TABLE problem_prerequisite ( |
|
296
by drtomc
Almost SQLized the database stuff from yesterday's discussion with sb. |
146 |
parent INT4 REFERENCES problem (problemid) NOT NULL, |
147 |
child INT4 REFERENCES problem (problemid) NOT NULL, |
|
148 |
PRIMARY KEY (parent,child) |
|
149 |
);
|
|
150 |