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