zd7ewBPp

1drop database if exists group44;
2create database group44;
3use group44;
4
5CREATE TABLE clubs(
6id INT auto_increment PRIMARY KEY,
7sportType VARCHAR(50) NOT NULL
8);
9
10CREATE TABLE coaches(
11id INT auto_increment PRIMARY KEY,
12coachesName VARCHAR(255) NOT NULL,
13egn CHAR(10) NOT NULL UNIQUE
14);
15
16CREATE TABLE students(
17id INT auto_increment PRIMARY KEY,
18studentName VARCHAR(255) NOT NULL,
19facNum CHAR(10) NOT NULL UNIQUE,
20phone VARCHAR(55) NULL
21);
22
23CREATE TABLE groups(
24id INT auto_increment PRIMARY KEY,
25dayOfWeek ENUM('MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY'),
26hourOfDay TIME NOT NULL,
27location VARCHAR(255) NOT NULL,
28club_id INT NOT NULL,
29coach_id INT NOT NULL,
30CONSTRAINT FOREIGN KEY(club_id) REFERENCES clubs(id),
31CONSTRAINT FOREIGN KEY(coach_id) REFERENCES coaches(id),
32UNIQUE KEY(dayOfWeek, hourOfDay, location)
33);
34
35CREATE TABLE student_group(
36student_id INT NOT NULL,
37group_id INT NOT NULL,
38CONSTRAINT FOREIGN KEY(student_id) REFERENCES students(id),
39CONSTRAINT FOREIGN KEY(group_id) REFERENCES groups(id),
40PRIMARY KEY(student_id, group_id)
41);