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
154
155
156
157
|
-- We need a users database to do authorization, manage groups, &c
-- Here's a first cut.
DROP TABLE roles;
DROP TABLE enrolment;
DROP TABLE group_members;
DROP TABLE users;
DROP TABLE groups;
CREATE TABLE users (
login varchar(80) PRIMARY KEY, -- login id
nick varchar(80),
fullname varchar(80),
studentid varchar(80) or NULL ****
);
CREATE TABLE groups (
groupid varchar(18) PRIMARY KEY, -- group name Y^4-S^9-G^3 **** use offering-id + group number (compound key)
nick varchar(80), -- group nickname
subject varchar(9), -- subject code **** use "offerings" table from CASMAS
year varchar(4) -- when
);
CREATE TABLE group_members (
login varchar(80) REFERENCES users (login),
groupid varchar(18) REFERENCES groups (groupid)
);
CREATE TABLE enrolment (
login varchar(80) REFERENCES users (login),
subject varchar(9),
result
supp_result
year varchar(4)
);
CREATE TABLE roles (
login varchar(80) REFERENCES users (login),
role varchar(8)
);
CREATE TABLE project (
projectid
synopsis
url
subject
deadline
);
CREATE TABLE extension (
login or groupid
projectid
deadline
approver
comment
);
CREATE TABLE mark (
login or groupid
projectid
componentid
marker
mark
timestamp
feedback
comment
KEY: (login/groupid, projectid, componentid)
);
################
CREATE TABLE problem (
problemid
specification
test (xml)
);
CREATE TABLE problem_tags (
problemid
tag
added_by
timestamp
);
CREATE TABLE problem_attempt (
problemid
login
datetime
submission
complete (boolean)
KEY: (problemid, login, datetime)
);
CREATE INDEX indexname ON problem_attempt (problemid, login);
CREATE TABLE problem_attempt_breakdown (
problemid
testcaseid
login
datetime
result (boolean) **** doesnt tell academic which concepts students are struggling with
);
CREATE TABLE problem_test_case (
problemid
testcaseid
testcase (sourced from xml)
description
visibility
tags (xref to tag table; break out)
);
# concept, curriculum, difficulty
CREATE TABLE tag (
tag
documentation
added_by
timestamp
);
# for multipart problems
CREATE TABLE prerequisite_problem (
parent_problemid
child_problemid
);
INSERT INTO users (login,nick) values ('conway', 'Tom');
INSERT INTO roles (login,role) values ('conway', 'student');
INSERT INTO users (login,nick) values ('apeel', 'Andrew');
INSERT INTO roles (login,role) values ('apeel', 'student');
INSERT INTO users (login,nick) values ('mgiuca', 'Matt');
INSERT INTO roles (login,role) values ('mgiuca', 'tutor');
INSERT INTO users (login,nick) values ('sb', 'Steven');
INSERT INTO roles (login,role) values ('sb', 'lecturer');
INSERT INTO users (login,nick) values ('mpp', 'Mike');
INSERT INTO roles (login,role) values ('mpp', 'student');
INSERT INTO users (login,nick) values ('ivo', 'Ivo');
INSERT INTO roles (login,role) values ('ivo', 'admin');
INSERT INTO groups (groupid, nick, subject, year) values ('2007-INFO10001-321', 'Purple Alert', 'INFO10001', '2008');
INSERT INTO groups (groupid, nick, subject, year) values ('2007-INFO10001-322', 'Blind Illuminati', 'INFO10001', '2008');
INSERT INTO group_members (login,groupid) values ('conway', '2007-INFO10001-321');
INSERT INTO group_members (login,groupid) values ('apeel', '2007-INFO10001-321');
INSERT INTO group_members (login,groupid) values ('mgiuca', '2007-INFO10001-321');
INSERT INTO group_members (login,groupid) values ('sb', '2007-INFO10001-321');
INSERT INTO group_members (login,groupid) values ('mpp', '2007-INFO10001-322');
INSERT INTO group_members (login,groupid) values ('ivo', '2007-INFO10001-322');
INSERT INTO enrolment (login,subject,year) values ('conway' , 'INFO10001', '2008');
INSERT INTO enrolment (login,subject,year) values ('apeel' , 'INFO10001', '2008');
INSERT INTO enrolment (login,subject,year) values ('mgiuca' , 'INFO10001', '2008');
INSERT INTO enrolment (login,subject,year) values ('sb' , 'INFO10001', '2008');
INSERT INTO enrolment (login,subject,year) values ('mpp' , 'INFO10001', '2008');
INSERT INTO enrolment (login,subject,year) values ('ivo' , 'INFO10001', '2008');
|