· 8 years ago · Apr 25, 2018, 09:20 AM
1DROP DATABASE IF EXISTS school_sport_clubs;
2CREATE DATABASE school_sport_clubs;
3USE school_sport_clubs;
4
5CREATE TABLE school_sport_clubs.sports(
6 id INT AUTO_INCREMENT PRIMARY KEY ,
7 name VARCHAR(255) NOT NULL
8);
9
10CREATE TABLE school_sport_clubs.coaches(
11 id INT AUTO_INCREMENT PRIMARY KEY ,
12 name VARCHAR(255) NOT NULL ,
13 egn VARCHAR(10) NOT NULL UNIQUE
14);
15
16CREATE TABLE school_sport_clubs.students(
17 id INT AUTO_INCREMENT PRIMARY KEY ,
18 name VARCHAR(255) NOT NULL ,
19 egn VARCHAR(10) NOT NULL UNIQUE ,
20 address VARCHAR(255) NOT NULL ,
21 phone VARCHAR(20) NULL DEFAULT NULL ,
22 class VARCHAR(10) NULL DEFAULT NULL
23);
24
25CREATE TABLE school_sport_clubs.sportGroups(
26 id INT AUTO_INCREMENT PRIMARY KEY ,
27 location VARCHAR(255) NOT NULL ,
28 dayOfWeek ENUM('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday') ,
29 hourOfTraining TIME NOT NULL ,
30 sport_id INT NOT NULL ,
31 coach_id INT NOT NULL ,
32 UNIQUE KEY(location,dayOfWeek,hourOfTraining) ,
33 CONSTRAINT FOREIGN KEY(sport_id)
34 REFERENCES sports(id) ,
35 CONSTRAINT FOREIGN KEY (coach_id)
36 REFERENCES coaches(id)
37);
38
39CREATE TABLE school_sport_clubs.student_sport(
40 student_id INT NOT NULL ,
41 sportGroup_id INT NOT NULL ,
42 CONSTRAINT FOREIGN KEY (student_id)
43 REFERENCES students(id) ,
44 CONSTRAINT FOREIGN KEY (sportGroup_id)
45 REFERENCES sportGroups(id) ,
46 PRIMARY KEY(student_id,sportGroup_id)
47);
48
49CREATE TABLE taxesPayments(
50 id INT AUTO_INCREMENT PRIMARY KEY,
51 student_id INT NOT NULL,
52 group_id INT NOT NULL,
53 paymentAmount DOUBLE NOT NULL,
54 month TINYINT,
55 year YEAR,
56 dateOfPayment DATETIME NOT NULL ,
57 CONSTRAINT FOREIGN KEY (student_id)
58 REFERENCES students(id),
59 CONSTRAINT FOREIGN KEY (group_id)
60 REFERENCES sportgroups(id)
61);
62
63CREATE TABLE salaryPayments(
64 id INT AUTO_INCREMENT PRIMARY KEY,
65 coach_id INT NOT NULL,
66 month TINYINT,
67 year YEAR,
68 salaryAmount double,
69 dateOfPayment datetime not null,
70 CONSTRAINT FOREIGN KEY (coach_id)
71 REFERENCES coaches(id),
72 UNIQUE KEY(`coach_id`,`month`,`year`)
73);
74
75INSERT INTO sports
76VALUES (NULL, 'Football') ,
77 (NULL, 'Volleyball'),
78 (NULL, 'Tennis') ,
79 (NULL, 'Karate') ,
80 (NULL, 'Taekwon-do');
81
82INSERT INTO coaches
83VALUES (NULL, 'Ivan Todorov Petkov', '7509041245') ,
84 (NULL, 'georgi Ivanov Todorov', '8010091245') ,
85 (NULL, 'Ilian Todorov Georgiev', '8407106352') ,
86 (NULL, 'Petar Slavkov Yordanov', '7010102045') ,
87 (NULL, 'Todor Ivanov Ivanov', '8302160980') ,
88 (NULL, 'Slavi Petkov Petkov', '7106041278');
89
90INSERT INTO students (name, egn, address, phone, class)
91VALUES ('Iliyan Ivanov', '9401150045', 'Sofia-Mladost 1', '0893452120', '10') ,
92 ('Ivan Iliev Georgiev', '9510104512', 'Sofia-Liylin', '0894123456', '11') ,
93 ('Elena Petrova Petrova', '9505052154', 'Sofia-Mladost 3', '0897852412', '11') ,
94 ('Ivan Iliev Iliev', '9510104542', 'Sofia-Mladost 3', '0894123457', '11') ,
95 ('Maria Hristova Dimova', '9510104547', 'Sofia-Mladost 4', '0894123442', '11') ,
96 ('Antoaneta Ivanova Georgieva', '9411104547', 'Sofia-Krasno selo', '0874526235', '10');
97
98INSERT INTO sportGroups
99VALUES (NULL, 'Sofia-Mladost 1', 'Monday', '08:00:00', 1, 1 ) ,
100 (NULL, 'Sofia-Mladost 1', 'Monday', '09:30:00', 1, 2 ) ,
101 (NULL, 'Sofia-Liylin 7', 'Sunday', '08:00:00', 2, 1) ,
102 (NULL, 'Sofia-Liylin 7', 'Sunday', '09:30:00', 2, 2) ,
103 (NULL, 'Plovdiv', 'Monday', '12:00:00', 1, 1);
104
105INSERT INTO student_sport
106VALUES (1, 1),
107 (2, 1),
108 (3, 1),
109 (4, 2),
110 (5, 2),
111 (6, 2),
112 (1, 3),
113 (2, 3),
114 (3, 3);
115
116INSERT INTO `school_sport_clubs`.`taxespayments`
117VALUES (NULL, '1', '1', '200', '1', 2015, now()),
118 (NULL, '1', '1', '200', '2', 2015, now()),
119 (NULL, '1', '1', '200', '3', 2015, now()),
120 (NULL, '1', '1', '200', '4', 2015, now()),
121 (NULL, '1', '1', '200', '5', 2015, now()),
122 (NULL, '1', '1', '200', '6', 2015, now()),
123 (NULL, '1', '1', '200', '7', 2015, now()),
124 (NULL, '1', '1', '200', '8', 2015, now()),
125 (NULL, '1', '1', '200', '9', 2015, now()),
126 (NULL, '1', '1', '200', '10', 2015, now()),
127 (NULL, '1', '1', '200', '11', 2015, now()),
128 (NULL, '1', '1', '200', '12', 2015, now()),
129 (NULL, '2', '1', '250', '1', 2015, now()),
130 (NULL, '2', '1', '250', '2', 2015, now()),
131 (NULL, '2', '1', '250', '3', 2015, now()),
132 (NULL, '2', '1', '250', '4', 2015, now()),
133 (NULL, '2', '1', '250', '5', 2015, now()),
134 (NULL, '2', '1', '250', '6', 2015, now()),
135 (NULL, '2', '1', '250', '7', 2015, now()),
136 (NULL, '2', '1', '250', '8', 2015, now()),
137 (NULL, '2', '1', '250', '9', 2015, now()),
138 (NULL, '2', '1', '250', '10', 2015, now()),
139 (NULL, '2', '1', '250', '11', 2015, now()),
140 (NULL, '2', '1', '250', '12', 2015, now()),
141 (NULL, '3', '1', '250', '1', 2015, now()),
142 (NULL, '3', '1', '250', '2', 2015, now()),
143 (NULL, '3', '1', '250', '3', 2015, now()),
144 (NULL, '3', '1', '250', '4', 2015, now()),
145 (NULL, '3', '1', '250', '5', 2015, now()),
146 (NULL, '3', '1', '250', '6', 2015, now()),
147 (NULL, '3', '1', '250', '7', 2015, now()),
148 (NULL, '3', '1', '250', '8', 2015, now()),
149 (NULL, '3', '1', '250', '9', 2015, now()),
150 (NULL, '3', '1', '250', '10', 2015, now()),
151 (NULL, '3', '1', '250', '11', 2015, now()),
152 (NULL, '3', '1', '250', '12', 2015, now()),
153 (NULL, '1', '2', '200', '1', 2015, now()),
154 (NULL, '1', '2', '200', '2', 2015, now()),
155 (NULL, '1', '2', '200', '3', 2015, now()),
156 (NULL, '1', '2', '200', '4', 2015, now()),
157 (NULL, '1', '2', '200', '5', 2015, now()),
158 (NULL, '1', '2', '200', '6', 2015, now()),
159 (NULL, '1', '2', '200', '7', 2015, now()),
160 (NULL, '1', '2', '200', '8', 2015, now()),
161 (NULL, '1', '2', '200', '9', 2015, now()),
162 (NULL, '1', '2', '200', '10', 2015, now()),
163 (NULL, '1', '2', '200', '11', 2015, now()),
164 (NULL, '1', '2', '200', '12', 2015, now()),
165 (NULL, '4', '2', '200', '1', 2015, now()),
166 (NULL, '4', '2', '200', '2', 2015, now()),
167 (NULL, '4', '2', '200', '3', 2015, now()),
168 (NULL, '4', '2', '200', '4', 2015, now()),
169 (NULL, '4', '2', '200', '5', 2015, now()),
170 (NULL, '4', '2', '200', '6', 2015, now()),
171 (NULL, '4', '2', '200', '7', 2015, now()),
172 (NULL, '4', '2', '200', '8', 2015, now()),
173 (NULL, '4', '2', '200', '9', 2015, now()),
174 (NULL, '4', '2', '200', '10', 2015, now()),
175 (NULL, '4', '2', '200', '11', 2015, now()),
176 (NULL, '4', '2', '200', '12', 2015, now()),
177 /**2014**/
178 (NULL, '1', '1', '200', '1', 2014, now()),
179 (NULL, '1', '1', '200', '2', 2014, now()),
180 (NULL, '1', '1', '200', '3', 2014, now()),
181 (NULL, '1', '1', '200', '4', 2014, now()),
182 (NULL, '1', '1', '200', '5', 2014, now()),
183 (NULL, '1', '1', '200', '6', 2014, now()),
184 (NULL, '1', '1', '200', '7', 2014, now()),
185 (NULL, '1', '1', '200', '8', 2014, now()),
186 (NULL, '1', '1', '200', '9', 2014, now()),
187 (NULL, '1', '1', '200', '10', 2014, now()),
188 (NULL, '1', '1', '200', '11', 2014, now()),
189 (NULL, '1', '1', '200', '12', 2014, now()),
190 (NULL, '2', '1', '250', '1', 2014, now()),
191 (NULL, '2', '1', '250', '2', 2014, now()),
192 (NULL, '2', '1', '250', '3', 2014, now()),
193 (NULL, '2', '1', '250', '4', 2014, now()),
194 (NULL, '2', '1', '250', '5', 2014, now()),
195 (NULL, '2', '1', '250', '6', 2014, now()),
196 (NULL, '2', '1', '250', '7', 2014, now()),
197 (NULL, '2', '1', '250', '8', 2014, now()),
198 (NULL, '2', '1', '250', '9', 2014, now()),
199 (NULL, '2', '1', '250', '10', 2014, now()),
200 (NULL, '2', '1', '250', '11', 2014, now()),
201 (NULL, '2', '1', '250', '12', 2014, now()),
202 (NULL, '3', '1', '250', '1', 2014, now()),
203 (NULL, '3', '1', '250', '2', 2014, now()),
204 (NULL, '3', '1', '250', '3', 2014, now()),
205 (NULL, '3', '1', '250', '4', 2014, now()),
206 (NULL, '3', '1', '250', '5', 2014, now()),
207 (NULL, '3', '1', '250', '6', 2014, now()),
208 (NULL, '3', '1', '250', '7', 2014, now()),
209 (NULL, '3', '1', '250', '8', 2014, now()),
210 (NULL, '3', '1', '250', '9', 2014, now()),
211 (NULL, '3', '1', '250', '10', 2014, now()),
212 (NULL, '3', '1', '250', '11', 2014, now()),
213 (NULL, '3', '1', '250', '12', 2014, now()),
214 (NULL, '1', '2', '200', '1', 2014, now()),
215 (NULL, '1', '2', '200', '2', 2014, now()),
216 (NULL, '1', '2', '200', '3', 2014, now()),
217 (NULL, '1', '2', '200', '4', 2014, now()),
218 (NULL, '1', '2', '200', '5', 2014, now()),
219 (NULL, '1', '2', '200', '6', 2014, now()),
220 (NULL, '1', '2', '200', '7', 2014, now()),
221 (NULL, '1', '2', '200', '8', 2014, now()),
222 (NULL, '1', '2', '200', '9', 2014, now()),
223 (NULL, '1', '2', '200', '10', 2014, now()),
224 (NULL, '1', '2', '200', '11', 2014, now()),
225 (NULL, '1', '2', '200', '12', 2014, now()),
226 (NULL, '4', '2', '200', '1', 2014, now()),
227 (NULL, '4', '2', '200', '2', 2014, now()),
228 (NULL, '4', '2', '200', '3', 2014, now()),
229 (NULL, '4', '2', '200', '4', 2014, now()),
230 (NULL, '4', '2', '200', '5', 2014, now()),
231 (NULL, '4', '2', '200', '6', 2014, now()),
232 (NULL, '4', '2', '200', '7', 2014, now()),
233 (NULL, '4', '2', '200', '8', 2014, now()),
234 (NULL, '4', '2', '200', '9', 2014, now()),
235 (NULL, '4', '2', '200', '10', 2014, now()),
236 (NULL, '4', '2', '200', '11', 2014, now()),
237 (NULL, '4', '2', '200', '12', 2014, now()),
238 /**2016**/
239 (NULL, '1', '1', '200', '1', 2016, now()),
240 (NULL, '1', '1', '200', '2', 2016, now()),
241 (NULL, '1', '1', '200', '3', 2016, now()),
242 (NULL, '2', '1', '250', '1', 2016, now()),
243 (NULL, '3', '1', '250', '1', 2016, now()),
244 (NULL, '3', '1', '250', '2', 2016, now()),
245 (NULL, '1', '2', '200', '1', 2016, now()),
246 (NULL, '1', '2', '200', '2', 2016, now()),
247 (NULL, '1', '2', '200', '3', 2016, now()),
248 (NULL, '4', '2', '200', '1', 2016, now()),
249 (NULL, '4', '2', '200', '2', 2016, now());