DROP DATABASE IF EXISTS sportGroups;
CREATE DATABASE sportGroups;
USE sportGroups;

CREATE TABLE clubs(
id INT AUTO_INCREMENT PRIMARY KEY,
sportName VARCHAR(100) NOT NULL

);

CREATE TABLE couches(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(55) NOT NULL

);

CREATE TABLE students(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
fNom CHAR(9) NOT NULL unique,
phone VARCHAR(55) NULL

);

CREATE TABLE groups(
id INT AUTO_INCREMENT PRIMARY KEY,
day ENUM('Mon','Tue','Wen','T','Fr','Sat','Sun') NOT NULL,
hour TIME NOT NULL,
location VARCHAR(255) NOT NULL,
club_id INT NOT NULL,
coach_id INT NOT NULL,
CONSTRAINT FOREIGN KEY (club_id) REFERENCES clubs(id),
CONSTRAINT FOREIGN KEY (coach_id) REFERENCES clubs(id),
Unique KEY(day,hour,location)
);

CREATE TABLE student_group(
student_id int not null,
group_id int not null,
constraint FOREIGN key(student_id) REFERENCES students(id),
constraint FOREIGN key(group_id) REFERENCES groups(id),
primary key (student_id,group_id)
);