udJe8WPN

1DROP DATABASE IF EXISTS sportGroups;
2CREATE DATABASE sportGroups;
3USE sportGroups;
4
5CREATE TABLE clubs(
6id INT AUTO_INCREMENT PRIMARY KEY,
7sportName VARCHAR(100) NOT NULL
8
9);
10
11CREATE TABLE couches(
12id INT AUTO_INCREMENT PRIMARY KEY,
13name VARCHAR(255) NOT NULL,
14email VARCHAR(55) NOT NULL
15
16);
17
18CREATE TABLE students(
19id INT AUTO_INCREMENT PRIMARY KEY,
20name VARCHAR(255) NOT NULL,
21fNom CHAR(9) NOT NULL unique,
22phone VARCHAR(55) NULL
23
24);
25
26CREATE TABLE groups(
27id INT AUTO_INCREMENT PRIMARY KEY,
28day ENUM('Mon','Tue','Wen','T','Fr','Sat','Sun') NOT NULL,
29hour TIME NOT NULL,
30location VARCHAR(255) NOT NULL,
31club_id INT NOT NULL,
32coach_id INT NOT NULL,
33CONSTRAINT FOREIGN KEY (club_id) REFERENCES clubs(id),
34CONSTRAINT FOREIGN KEY (coach_id) REFERENCES clubs(id),
35Unique KEY(day,hour,location)
36);
37
38CREATE TABLE student_group(
39student_id int not null,
40group_id int not null,
41constraint FOREIGN key(student_id) REFERENCES students(id),
42constraint FOREIGN key(group_id) REFERENCES groups(id),
43primary key (student_id,group_id)
44);