· 9 years ago · Dec 21, 2016, 08:00 AM
1DROP TABLE IF EXISTS students;
2DROP TABLE IF EXISTS sportGroups;
3DROP TABLE IF EXISTS student_sport;
4DROP TABLE IF EXISTS sports;
5DROP TABLE IF EXISTS coaches;
6
7CREATE TABLE students(
8id INT AUTO_INCREMENT PRIMARY KEY,
9name VARCHAR(255) NOT NULL,
10egn VARCHAR(10) NOT NULL UNIQUE,
11address VARCHAR(255) NOT NULL,
12phone VARCHAR(20) NULL DEFAULT NULL,
13class VARCHAR(10) NULL DEFAULT NULL
14);
15
16CREATE TABLE sportGroups(
17id INT AUTO_INCREMENT PRIMARY KEY,
18location VARCHAR(255) NOT NULL,
19dayOfWeek ENUM('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday'),
20hourOfTraining TIME NOT NULL
21);
22
23CREATE TABLE student_sport(
24student_id int not null,
25sportGroup_id int not null,
26PRIMARY KEY(student_id,sportGroup_id)
27);
28
29CREATE TABLE sports(
30id INT AUTO_INCREMENT PRIMARY KEY,
31name VARCHAR(255) NOT NULL
32);
33
34ALTER TABLE sportGroups
35ADD sport_id INT NOT NULL;
36
37
38CREATE TABLE coaches(
39id INT AUTO_INCREMENT PRIMARY KEY,
40name VARCHAR(255) NOT NULL,
41egn VARCHAR(10) NOT NULL UNIQUE
42);
43
44ALTER TABLE sportGroups
45ADD coach_id INT;
46
47INSERT INTO `sports` (`name`) VALUES ('Football');
48INSERT INTO `sports` (`name`) VALUES ('Volleyball');
49
50INSERT INTO `coaches` (`name`, `egn`)
51VALUES ('Иван Петков', '7509041245'),
52('Георги Иванов Тодоров', '8010091245'),
53('ИлиÑн Тодоров Георгиев', '8407106352');
54
55INSERT INTO `coaches` (`name`, `egn`) VALUES ('Петър Славков Йорданов', '7010102045');
56INSERT INTO `coaches` (`name`, `egn`) VALUES ('Слави Петков Петков', '7106041278');
57
58INSERT INTO students (name, egn, address, phone, class)
59VALUES ('Iliyan Ivanov', '9401150045', 'Sofia-Mladost 1', '0893452120', '10'),
60('Ivan Iliev Georgiev', '9510104512', 'Sofia-Liylin', '0894123456', '11'),
61('Elena Petrova Petrova', '9505052154', 'Sofia-Mladost 3', '0897852412', '11'),
62('Ivan Iliev Iliev', '9510104542', 'Sofia-Mladost 3', '0894123457', '11'),
63('Maria Hristova Dimova', '9510104547', 'Sofia-Mladost 4', '0894123442', '11'),
64('Antoaneta Ivanova Georgieva', '9411104547', 'Sofia-Krasno selo', '0874526235', '10');
65
66INSERT INTO `student_sport` (`student_id`, `sportGroup_id`) VALUES (1, 1),
67 (2, 1),
68 (3, 1),
69 (4, 2),
70 (5, 2),
71 (6, 2);
72
73 INSERT INTO sportgroups (location, dayOfWeek, hourOfTraining, sport_id, coach_id)
74 VALUES ('Sofia-Mladost 1', 'Monday', '8:00:00', 1, 1),
75 ('Sofia-Mladost 1', 'Monday', '9:30:00', 1, 1),
76 ('Sofia- Liylin 7','Sunday','09:00:00',2,2),
77 ('Sofia-Nadezhda', 'Sunday', '08:00', 1,NULL);