tZwCPns8

1DROP DATABASE IF EXISTS ElsysHub;
2CREATE DATABASE ElsysHub;
3USE ElsysHub;
4
5CREATE TABLE Users(
6	Id INTEGER AUTO_INCREMENT NOT NULL,
7    Email VARCHAR(25) NOT NULL,
8    Password CHAR(64) NOT NULL,
9    ClassNum INTEGER NOT NULL,
10    ClassLetter CHAR NOT NULL,
11    NumInClass INTEGER NOT NULL,
12    PRIMARY KEY(Id, Email)
13);
14
15INSERT INTO Users VALUES(NULL, 'Django2@gmail.com', 'mimi', 11, 'a', 22);
16
17CREATE TABLE Subjects(
18	Id INTEGER AUTO_INCREMENT NOT NULL,
19    Name VARCHAR(40) NOT NULL,
20    PRIMARY KEY(Id, Name)
21);
22
23INSERT INTO Subjects VALUES(NULL, "OOP");
24
25CREATE TABLE Assignments(
26	Id INTEGER NOT NULL AUTO_INCREMENT,
27    Title VARCHAR(50) NOT NULL,
28    DescriptionURL VARCHAR(100) NOT NULL,
29    PRIMARY KEY(Id, Title)
30);
31
32INSERT INTO Assignments VALUES(NULL, "balls and boxes", "https://github.com/elsys/oop2017-2018/tree/master/practice/05-balls-and-boxes");
33
34CREATE TABLE Solutions(
35	Id INTEGER NOT NULL AUTO_INCREMENT,
36    SubjectId INTEGER NOT NULL,
37    AssignmentId INTEGER NOT NULL,
38    UserId INTEGER NOT NULL,
39    Points INTEGER NOT NULL,
40    SourceCode TEXT NOT NULL,
41    PRIMARY KEY (Id),
42    FOREIGN KEY (SubjectId) REFERENCES Subjects(Id) ON DELETE CASCADE,
43    FOREIGN KEY (AssignmentId) REFERENCES Assignments(Id) ON DELETE CASCADE,
44    FOREIGN KEY (UserId) REFERENCES Users(Id) ON DELETE CASCADE 
45);
46
47INSERT INTO Solutions VALUES (NULL, 1, 1, 1, 0, "here is source code");