gMsACRJ6

1drop database if exists examSession;
2create database examSession;
3use examSession;
4
5create table exams (
6id int auto_increment primary key,
7examName varchar(255) not null,
8dateHeld datetime not null,
9semester enum('summer','winter'),
10averageGrade decimal not null
11);
12
13create table subjects (
14id int auto_increment primary key,
15subjectName varchar(255) not null,
16dateIntroduced date not null
17);
18
19create table students (
20id int auto_increment primary key,
21studentName varchar(255) not null,
22facultyNumber varchar(9) not null unique,
23groupNumber int not null,
24academicYear int not null
25);
26
27create table teachers (
28id int auto_increment primary key,
29teacherName varchar(255) not null,
30phone varchar(20) default null,
31cabinet int default null
32);
33
34create table student_exam (
35student_id int not null, 
36CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(id),
37exam_id int not null,
38CONSTRAINT FOREIGN KEY (exam_id) REFERENCES exams(id),
39PRIMARY KEY(student_id,exam_id)
40);
41
42create table student_subject (
43student_id int not null, 
44CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(id),
45subject_id int not null,
46CONSTRAINT FOREIGN KEY (subject_id) REFERENCES subjects(id),
47PRIMARY KEY(student_id,subject_id)
48);
49
50create table teacher_subject (
51teacher_id int not null,
52CONSTRAINT FOREIGN KEY (teacher_id) REFERENCES teachers(id),
53subject_id int not null,
54CONSTRAINT FOREIGN KEY (subject_id) REFERENCES subjects(id),
55PRIMARY KEY(teacher_id,subject_id)
56);
57
58insert into exams (examName, dateHeld, semester, averageGrade)
59values
60('Databases 2nd exam', '2018-05-19T08:00:00', 'summer', 6.00),
61('Semiconductors final', '2017-11-21T14:30:00', 'winter', 3.75),
62('Algorithms', '2018-06-06T11:30:00', 'summer', 4.60),
63('Amplitude Modulation', '2018-06-01T09:00:00', 'summer', 3.60),
64('Databases 1st exam', '2018-03-20T14:00:00', 'summer', 5.00);
65
66insert into subjects (subjectName, dateIntroduced)
67values
68('Databases', '1989-01-01'),
69('Semiconductor materials', '1975-01-01'),
70('Design and analysis of algorithms', '2001-01-01'),
71('Digital Signal Processing', '1999-01-01');
72
73insert into students (studentName, facultyNumber, groupNumber , academicYear)
74values
75('Yasen Ivov Stoilov', '121216081', 44, 2),
76('Neyasen Ivov Stoilov', '121216082', 44, 2),
77('Ivan Ivanov Ivanov', '121217341', 45, 1),
78('Georgi Georgiev Georgiev', '121217186', 48, 1),
79('Yordan Yordanov Yordanov', '121217333', 50, 1),
80('Stefan Stefanov Stefanov', '121215105', 41, 3);
81
82insert into teachers (teacherName, phone, cabinet)
83values
84('Ivan Teacherov', '0888888888', 2315),
85('Georgi Teacherov', '099999999', 2437),
86('Yordan Teacherov', '077777777', 2619),
87('Plamen Teacherov', '06555555555', 3109),
88('Vasil Teacherov', '0173658719', 1442);
89
90/*
91select * from exams;
92select * from subjects;
93select * from teachers;
94select * from students;
95*/
96
97insert into student_exam 
98values
99(1, 1),
100(2, 1),
101(1, 2),
102(2, 2),
103(3, 2),
104(4, 2),
105(5, 2),
106(2, 3),
107(3, 4),
108(4, 4),
109(5, 4),
110(6, 4),
111(1, 5),
112(2, 5),
113(6, 5);
114
115
116insert into student_subject
117values
118(1, 1),
119(2, 1),
120(3, 1),
121(4, 1),
122(5, 1),
123(6, 1),
124(1, 2),
125(2, 2),
126(3, 2),
127(4, 2),
128(5, 2),
129(2, 3),
130(3, 4),
131(4, 4),
132(5, 4),
133(6, 4);
134
135insert into teacher_subject 
136values 
137(1, 1),
138(1, 3),
139(2, 1),
140(2, 3),
141(3, 2),
142(4, 4),
143(5, 4);