NhZScKe6

1DROP DATABASE IF EXISTS `school_sport_clubs`;
2CREATE DATABASE school_sport_clubs;
3USE school_sport_clubs;
4
5
6CREATE TABLE groups(
7	id INT AUTO_INCREMENT PRIMARY KEY, 
8	`day` ENUM('monday','tuesday','wednesday','thursday','friday','saturday','sunday') NOT NULL,
9	`hour` TIME NOT NULL,
10    sport_id INT NOT NULL,
11    UNIQUE KEY (`day`, `hour`)
12);
13
14CREATE TABLE sports(
15	id INT AUTO_INCREMENT PRIMARY KEY, 
16    `name` VARCHAR(30) NOT NULL
17);
18
19ALTER TABLE groups
20ADD CONSTRAINT FOREIGN KEY (sport_id) REFERENCES sports (id);
21
22CREATE TABLE students (
23	id INT AUTO_INCREMENT PRIMARY KEY,
24    first_name VARCHAR(30) NOT NULL,
25    last_name VARCHAR(30) NOT NULL,
26    egn VARCHAR(10) NOT NULL,
27    email VARCHAR(40),
28    grade VARCHAR(10) NOT NULL
29);
30CREATE TABLE students_groups (
31	student_id INT NOT NULL,
32    group_id INT NOT NULL,
33    PRIMARY KEY (student_id, group_id),
34    CONSTRAINT FOREIGN KEY (student_id) REFERENCES students (id),
35    CONSTRAINT FOREIGN KEY (group_id) REFERENCES groups (id) 
36);
37
38CREATE TABLE coaches (
39	id INT AUTO_INCREMENT PRIMARY KEY,
40    first_name VARCHAR(30) NOT NULL,
41    last_name VARCHAR(30) NOT NULL,
42    egn VARCHAR(10) NOT NULL,
43    email VARCHAR(40),
44    group_id INT NOT NULL UNIQUE,
45    CONSTRAINT FOREIGN KEY (group_id) REFERENCES groups (id)
46);