vCW0WADj

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);