GffXkWLH

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 sports(
12id INT AUTO_INCREMENT PRIMARY KEY,
13name VARCHAR(255) NOT NULL
14 );
15 
16CREATE TABLE couches(
17id INT AUTO_INCREMENT PRIMARY KEY,
18name VARCHAR(255) NOT NULL,
19email VARCHAR(55) NOT NULL
20 
21);
22 
23CREATE TABLE students(
24id INT AUTO_INCREMENT PRIMARY KEY,
25name VARCHAR(255) NOT NULL,
26fNom CHAR(9) NOT NULL unique,
27phone VARCHAR(55) NULL
28 
29);
30 
31CREATE TABLE groupss(
32id INT AUTO_INCREMENT PRIMARY KEY,
33day ENUM('Mon','Tue','Wen','T','Fr','Sat','Sun') NOT NULL,
34hour TIME NOT NULL,
35location VARCHAR(255) NOT NULL,
36club_id INT NOT NULL,
37coach_id INT NOT NULL,
38CONSTRAINT FOREIGN KEY (club_id) REFERENCES clubs(id),
39CONSTRAINT FOREIGN KEY (coach_id) REFERENCES clubs(id),
40Unique KEY(day,hour,location)
41);
42 
43CREATE TABLE student_group(
44student_id int not null,
45group_id int not null,
46constraint FOREIGN key(student_id) REFERENCES students(id),
47constraint FOREIGN key(group_id) REFERENCES groupss(id),
48primary key (student_id,group_id)
49);