/
make-tables.sql
92 lines (79 loc) · 3.27 KB
/
make-tables.sql
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
use cms;
drop table if exists subfile cascade;
drop table if exists qfile cascade;
drop table if exists comment cascade;
drop table if exists substring cascade;
drop table if exists gracedays cascade;
drop table if exists file cascade;
drop table if exists grade cascade;
drop table if exists assignment cascade;
drop table if exists role cascade;
drop table if exists course cascade;
drop table if exists sessions cascade;
drop table if exists account cascade;
create table account
(accountid int primary key auto_increment,
firstname varchar(64) not null,
lastname varchar(64) not null,
email varchar(64) not null,
passhash char(64) not null,
salt char(32) not null,
rollno varchar(32),
privilegelevel int not null,
activationkey char(64));
create table sessions
(sessionid char(64) not null,
accountid int not null , foreign key (accountid) references account(accountid));
create table course
(courseid int primary key auto_increment,
coursename varchar(64) not null,
coursecode varchar(16) not null,
creatorid int not null , foreign key (creatorid) references account(accountid),
startdate date not null,
enddate date not null,
maxgracedays int not null);
create table role
(accountid int not null , foreign key (accountid) references account(accountid),
courseid int not null , foreign key (courseid) references course(courseid),
role int not null);
create table assignment
(assignmentid int primary key auto_increment,
courseid int not null , foreign key (courseid) references course(courseid),
creatorid int not null , foreign key (creatorid) references account(accountid),
creationtime datetime not null,
duetime datetime,
maxsubmittime datetime,
titlestring text not null,
submissiontype varchar(4) not null,
maxmarks int not null);
create table grade
(assignmentid int not null , foreign key (assignmentid) references assignment(assignmentid),
submittorid int not null , foreign key (submittorid) references account(accountid),
givenmarks int,
gracedaysused int not null);
create table file
(fileid int primary key auto_increment,
realfname varchar(64) not null,
storedfname varchar(96) not null);
create table subfile
(fileid int not null , foreign key (fileid) references file(fileid),
submittorid int not null , foreign key (submittorid) references account(accountid),
assignmentid int not null , foreign key (assignmentid) references assignment(assignmentid),
submittime datetime not null default CURRENT_TIMESTAMP);
create table qfile
(fileid int not null , foreign key (fileid) references file(fileid),
assignmentid int not null , foreign key (assignmentid) references assignment(assignmentid));
create table comment
(commentstring text not null,
commentorid int not null , foreign key (commentorid) references account(accountid),
assignmentid int not null , foreign key (assignmentid) references assignment(assignmentid),
commenttime datetime not null);
create table substring
(answerstring text not null,
submittorid int not null , foreign key (submittorid) references account(accountid),
assignmentid int not null , foreign key (assignmentid) references assignment(assignmentid),
submittime datetime not null);
create table gracedays
(courseid int not null , foreign key (courseid) references course(courseid),
studentid int not null , foreign key (studentid) references account(accountid),
availabledays int not null);