· 10 years ago · Oct 20, 2015, 11:21 AM
1USE class_diagram;
2
3DROP TABLE IF EXISTS progress;
4DROP TABLE IF EXISTS module;
5DROP TABLE IF EXISTS teacher;
6DROP TABLE IF EXISTS student;
7
8CREATE TABLE student (
9 student_id INT(11) NOT NULL,
10 name VARCHAR(45),
11 email VARCHAR(45),
12 PRIMARY KEY(student_id)
13);
14
15CREATE TABLE teacher (
16 teacher_id INT(11) NOT NULL,
17 name VARCHAR(45),
18 email VARCHAR(45),
19 PRIMARY KEY(teacher_id)
20);
21
22CREATE TABLE module (
23 module_id INT(11) NOT NULL,
24 name VARCHAR(45),
25 description VARCHAR(252),
26 course VARCHAR(45),
27 PRIMARY KEY(module_id)
28);
29
30CREATE TABLE progress (
31 progress_id INT(11) NOT NULL,
32 delivered DATE,
33 reviewed DATE,
34 approved BOOLEAN,
35 comment VARCHAR(252),
36 s_id INT(11),
37 t_id INT(11),
38 m_id INT(11),
39 PRIMARY KEY(progress_id),
40 FOREIGN KEY(s_id)REFERENCES student(student_id),
41 FOREIGN KEY(t_id)REFERENCES teacher(teacher_id),
42 FOREIGN KEY(m_id)REFERENCES module(module_id)
43);
44
45CREATE TABLE uploadFiles (
46 -- comments
47 -- id
48 -- delivery date
49 -- file name
50
51 -- foreign key to progress
52
53);
54
55
56-- -----------------------------------------------------
57-- Data for table `student`
58-- -----------------------------------------------------
59INSERT INTO `student`
60(`student_id`, `name`, `email`)
61VALUES
62(0001, `Student Studentson`, `student@gmail.com`),
63(0002, `Even Nilsen`, `even@gmail.com`),
64(0003, `Jorgen Gramstad`, `jorgen@gmail.com`),
65(0004, `Jonas Dam`, `jonas@gmail.com`),
66(0005, `Simen Fuglestad`, `simen@gmail.com`);
67
68-- -----------------------------------------------------
69-- Data for table `teacher`
70-- -----------------------------------------------------
71INSERT INTO `teacher`
72(`teacher_id`, `name`, `email`)
73VALUES
74(0001, `Teacher Teacherson `, `teacher@gmail.com`),
75(0002, `Doc Brown`, `brown@gmail.com`),
76(0003, `Benjamin Kloster`, `kloster@gmail.com`),
77(0004, `Coffee Machinicus`, `coffee@gmail.com`);
78
79-- -----------------------------------------------------
80-- Data for table `module`
81-- -----------------------------------------------------
82INSERT INTO `module`
83(`student_id`, `name`, `email`)
84VALUES
85(0001, `module1`, `BlueJ Introduction`, `is109`),
86(0002, `module2`, `Class Definition`, `is109`),
87(0003, `module3`, `Class Definition 2`, `is109`),
88(0004, `module4`, `Objects and Loops`, `is109`),
89(0005, `module5`, `Designing Classes`, `is109`),
90(0006, `module6`, `Documentation`, `is110`),
91(0007, `module7`, `Designing Classes 2`, `is110`),
92(0008, `module8`, `Testing and Debugging`, `is110`),
93(0009, `module9`, `Inheritance`, `is110`),
94(0010, `module10`, `Inheritance 2`, `is110`),
95(0011, `module11`, `Abstract Classes and Interfaces`, `is110`),
96(0012, `module11`, `GUI`, `is110`),
97(0013, `module13`, `Errorhandling`, `is110`),
98(0014, `module14`, `Programmingproject`, `is110`);
99
100-- Obligatory
101-- Prerequisites
102
103-- -----------------------------------------------------
104-- Data for table `progress`
105-- -----------------------------------------------------
106INSERT INTO `progress`
107(`student_id`, `name`, `email`)
108VALUES
109(0001, `Teacher Teacherson `, `teacher@gmail.com`),
110(0002, `Doc Brown`, `brown@gmail.com`),
111(0003, `Benjamin Kloster`, `kloster@gmail.com`),
112(0004, `Coffee Machinicus`, `coffee@gmail.com`);
113
114/*
115CREATE TABLE progress (
116 progress_id INT(11) NOT NULL,
117 delivered DATE,
118 reviewed DATE,
119 approved BOOLEAN,
120 comment VARCHAR(252),
121 s_id INT(11),
122 t_id INT(11),
123 m_id INT(11),
124 PRIMARY KEY(progress_id),
125 FOREIGN KEY(s_id)REFERENCES student(student_id),
126 FOREIGN KEY(t_id)REFERENCES teacher(teacher_id),
127 FOREIGN KEY(m_id)REFERENCES module(module_id)
128);
129
130*/