YEPpNkRZ

1MySQL
2workbench
3
4code:
5
6DROP DATABASE IF EXISTS group45;
7CREATE DATABASE group45;
8USE group45;
9
10CREATE TABLE clubs(
11 id INT AUTO_INCREMENT PRIMARY KEY,
12 sportType VARCHAR(50) NOT NULL
13);
14
15CREATE TABLE coaches(
16 id INT AUTO_INCREMENT PRIMARY KEY,
17 coachName VARCHAR(255) NOT NULL,
18 eng VARCHAR(10) NOT NULL UNIQUE
19);
20
21CREATE TABLE students(
22 id INT AUTO_INCREMENT PRIMARY KEY,
23 studentName VARCHAR(255) NOT NULL,
24 fNum VARCHAR(10) NOT NULL UNIQUE,
25 phone VARCHAR(50) NULL
26);
27
28CREATE TABLE groups(
29 id INT AUTO_INCREMENT PRIMARY KEY,
30 dayOfWeek ENUM('Monday','Sunday'),
31 timeOfDay TIME NOT NULL,
32 location VARCHAR(255) NOT NULL,
33 club_id INT NOT NULL,
34 coach_id INT NOT NULL,
35 CONSTRAINT FOREIGN KEY(club_id) REFERENCES clubs(id),
36 CONSTRAINT FOREIGN KEY(coach_id) REFERENCES coaches(id),
37 UNIQUE  KEY(dayOfWeek,timeOfDay,location)
38);
39
40CREATE TABLE student_group(
41 student_id INT NOT NULL,
42 group_id INT NOT NULL,
43 CONSTRAINT FOREIGN KEY(student_id) REFERENCES students(id),
44 CONSTRAINT FOREIGN KEY(group_Id) REFERENCES groups(id),
45 PRIMARY KEY(student_id,group_id)
46);