school by me

CREATE TABLE sports (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE sportGroups (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    location VARCHAR(150) NOT NULL,
    dayOfWeek ENUM("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"),
    timeOfDay TIME NOT NULL,

    sport_id INT NOT NULL,
    CONSTRAINT FOREIGN KEY(sport_id) REFERENCES sports(id)
);

CREATE TABLE coaches (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR (100),
    egn VARCHAR(10) UNIQUE NOT NULL
);

ALTER TABLE sportGroups
    ADD coach_id INT,
    ADD CONSTRAINT FOREIGN KEY(coach_id) REFERENCES coaches(id);

CREATE TABLE students (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    egn VARCHAR(10) UNIQUE NOT NULL,
    phone VARCHAR(10) UNIQUE NOT NULL,
    address VARCHAR(150) NOT NULL,
    class INT NOT NULL
);

CREATE TABLE studentSport (
    sport_id INT NOT NULL,
    student_id INT NOT NULL,

    CONSTRAINT FOREIGN KEY(sport_id) REFERENCES sportGroups(id),
    CONSTRAINT FOREIGN KEY(student_id) REFERENCES students(id),

    PRIMARY KEY(sport_id, student_id)
);