YJkyJUri

1use SLIT; 
2
3DROP TABLE IF EXISTS Push;
4DROP TABLE IF EXISTS Feedback;
5DROP TABLE IF EXISTS Deliverable;
6DROP TABLE IF EXISTS Module;
7DROP TABLE IF EXISTS Teacher;
8DROP TABLE IF EXISTS Student;
9DROP TABLE IF EXISTS UserList; 
10
11CREATE TABLE UserList(
12User_ID INT AUTO_INCREMENT, 
13User_firstName VARCHAR(128) NOT NULL, 
14User_lastName VARCHAR(128) NOT NULL, 
15User_mail VARCHAR(128) NOT NULL, 
16User_passwords VARCHAR(128) NOT NULL, 
17User_type ENUM('Teacher', 'Student', 'Admin') DEFAULT 'Student',
18CONSTRAINT PK_Users_ID PRIMARY KEY (User_ID));
19
20CREATE TABLE Module(
21Module_ID INT AUTO_INCREMENT,
22Module_title VARCHAR (25) NOT NULL,
23Module_description VARCHAR (400) NOT NULL,
24Module_criteria VARCHAR (50) NOT NULL,
25Module_status ENUM('Publisert', 'Ikke publisert'),
26Module_resources VARCHAR(200),
27Module_deadline DATE NOT NULL,
28CONSTRAINT PK_Module_ID PRIMARY KEY (Module_ID));
29
30CREATE TABLE Deliverable(
31Deliverable_ID INT AUTO_INCREMENT,
32Deliverable_message VARCHAR(200),
33Deliverable_file VARCHAR(50) NOT NULL,
34Deliverable_dateSent DATE NOT NULL,
35FK_Module_ID INT NOT NULL,
36FK_Users_ID INT NOT NULL,
37CONSTRAINT PK_Deliverable_ID PRIMARY KEY (Deliverable_ID),
38CONSTRAINT FOREIGN KEY (FK_Module_ID) REFERENCES Module (Module_ID),
39CONSTRAINT FOREIGN KEY (FK_Users_ID) REFERENCES UserList (User_ID));
40
41CREATE TABLE Feedback(
42Feedback_ID INT AUTO_INCREMENT,
43Feedback_comment VARCHAR(200) NOT NULL,
44Feedback_score INT NOT NULL,
45Feedback_status ENUM('Godkjent', 'Ikke godkjent'),
46Date_received DATE NOT NULL,
47FK_Deliverable_ID INT,
48FK_Users_ID INT,
49CONSTRAINT PK_Feedback_ID PRIMARY KEY (Feedback_ID),
50CONSTRAINT FOREIGN KEY (FK_Deliverable_ID) REFERENCES Deliverable (Deliverable_ID),
51CONSTRAINT FOREIGN KEY (FK_Users_ID) REFERENCES UserList (User_ID));
52
53CREATE TABLE Push (
54Push_ID INT AUTO_INCREMENT,
55Push_text VARCHAR (50),
56Push_date DATE NOT NULL,
57CONSTRAINT PK_Push_ID PRIMARY KEY (Push_ID));
58
59INSERT INTO UserList(User_ID, User_firstName, User_lastName, User_mail, User_passwords)
60VALUES
61(1, 'Haris', 'Saric', 'hariss15@uia.no', 'bakvaske'),
62(2, 'Magnus', 'Hornnes', 'maho15@uia.no', 'frontvaske'),
63(3, 'Tommy', 'Ea', 'toea15@uia.no', 'jotarooo'),
64(4, 'Sebastian', 'Nomme', 'seno15@uia.no', 'javafxbugs'),
65(5, 'Rebecca', 'Iversen', 'reiv15@uia.no', 'opensourceinc');
66
67INSERT INTO Module(Module_ID, Module_title, Module_description, Module_criteria, Module_resources,
68Module_status, Module_deadline)
69VALUES
70(1, 'Klasser og objekter', 'En god start paa faget', 'Kapittel 1', 'www.sedet.com', 'Publisert', '2016-08-28');
71
72INSERT INTO Deliverable(Deliverable_ID, Deliverable_message, Deliverable_file, Deliverable_dateSent, FK_Module_ID, FK_Users_ID)
73VALUES
74(1, 'Heisann sveisann', 'bilde1.jpg', '2016-02-02', 1, 1),
75(2, 'Koser meg', 'bilde2.jpg', '2016-04-06', 1, 2);
76
77INSERT INTO Feedback(Feedback_ID, Feedback_comment, Feedback_score, Feedback_status, 
78Date_received, FK_Deliverable_ID, FK_Users_ID)
79VALUES
80(1, 'Bra jobbet!', 84, 'Godkjent', '2016-01-01', 1, 1),
81(2, 'Flott!', 95, 'Godkjent', '2016-09-07', 2, 3);