· 6 years ago · Mar 28, 2019, 01:00 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
80INSERT INTO coaches
81VALUES (NULL, 'Ivan Todorov Petkov', '7509041245') ,
82 (NULL, 'georgi Ivanov Todorov', '8010091245') ,
83 (NULL, 'Ilian Todorov Georgiev', '8407106352') ,
84 (NULL, 'Petar Slavkov Yordanov', '7010102045') ,
85 (NULL, 'Todor Ivanov Ivanov', '8302160980') ,
86 (NULL, 'Slavi Petkov Petkov', '7106041278');
87
88INSERT INTO students (name, egn, address, phone, class)
89VALUES ('Iliyan Ivanov', '9401150045', 'Sofia-Mladost 1', '0893452120', '10') ,
90 ('Ivan Iliev Georgiev', '9510104512', 'Sofia-Liylin', '0894123456', '11') ,
91 ('Elena Petrova Petrova', '9505052154', 'Sofia-Mladost 3', '0897852412', '11') ,
92 ('Ivan Iliev Iliev', '9510104542', 'Sofia-Mladost 3', '0894123457', '11') ,
93 ('Maria Hristova Dimova', '9510104547', 'Sofia-Mladost 4', '0894123442', '11') ,
94 ('Antoaneta Ivanova Georgieva', '9411104547', 'Sofia-Krasno selo', '0874526235', '10');
95
96INSERT INTO sportGroups
97VALUES (NULL, 'Sofia-Mladost 1', 'Monday', '08:00:00', 1, 1 ) ,
98 (NULL, 'Sofia-Mladost 1', 'Monday', '09:30:00', 1, 2 ) ,
99 (NULL, 'Sofia-Liylin 7', 'Sunday', '08:00:00', 2, 1) ,
100 (NULL, 'Sofia-Liylin 7', 'Sunday', '09:30:00', 2, 2) ,
101 (NULL, 'Plovdiv', 'Monday', '12:00:00', '1', '1');
102
103INSERT INTO student_sport
104VALUES (1, 1),
105 (2, 1),
106 (3, 1),
107 (4, 2),
108 (5, 2),
109 (6, 2),
110 (1, 3),
111 (2, 3),
112 (3, 3);
113
114INSERT INTO `school_sport_clubs`.`taxespayments`
115VALUES (NULL, '1', '1', '200', '1', 2015, now()),
116 (NULL, '1', '1', '200', '2', 2015, now()),
117 (NULL, '1', '1', '200', '3', 2015, now()),
118 (NULL, '1', '1', '200', '4', 2015, now()),
119 (NULL, '1', '1', '200', '5', 2015, now()),
120 (NULL, '1', '1', '200', '6', 2015, now()),
121 (NULL, '1', '1', '200', '7', 2015, now()),
122 (NULL, '1', '1', '200', '8', 2015, now()),
123 (NULL, '1', '1', '200', '9', 2015, now()),
124 (NULL, '1', '1', '200', '10', 2015, now()),
125 (NULL, '1', '1', '200', '11', 2015, now()),
126 (NULL, '1', '1', '200', '12', 2015, now()),
127 (NULL, '2', '1', '250', '1', 2015, now()),
128 (NULL, '2', '1', '250', '2', 2015, now()),
129 (NULL, '2', '1', '250', '3', 2015, now()),
130 (NULL, '2', '1', '250', '4', 2015, now()),
131 (NULL, '2', '1', '250', '5', 2015, now()),
132 (NULL, '2', '1', '250', '6', 2015, now()),
133 (NULL, '2', '1', '250', '7', 2015, now()),
134 (NULL, '2', '1', '250', '8', 2015, now()),
135 (NULL, '2', '1', '250', '9', 2015, now()),
136 (NULL, '2', '1', '250', '10', 2015, now()),
137 (NULL, '2', '1', '250', '11', 2015, now()),
138 (NULL, '2', '1', '250', '12', 2015, now()),
139 (NULL, '3', '1', '250', '1', 2015, now()),
140 (NULL, '3', '1', '250', '2', 2015, now()),
141 (NULL, '3', '1', '250', '3', 2015, now()),
142 (NULL, '3', '1', '250', '4', 2015, now()),
143 (NULL, '3', '1', '250', '5', 2015, now()),
144 (NULL, '3', '1', '250', '6', 2015, now()),
145 (NULL, '3', '1', '250', '7', 2015, now()),
146 (NULL, '3', '1', '250', '8', 2015, now()),
147 (NULL, '3', '1', '250', '9', 2015, now()),
148 (NULL, '3', '1', '250', '10', 2015, now()),
149 (NULL, '3', '1', '250', '11', 2015, now()),
150 (NULL, '3', '1', '250', '12', 2015, now()),
151 (NULL, '1', '2', '200', '1', 2015, now()),
152 (NULL, '1', '2', '200', '2', 2015, now()),
153 (NULL, '1', '2', '200', '3', 2015, now()),
154 (NULL, '1', '2', '200', '4', 2015, now()),
155 (NULL, '1', '2', '200', '5', 2015, now()),
156 (NULL, '1', '2', '200', '6', 2015, now()),
157 (NULL, '1', '2', '200', '7', 2015, now()),
158 (NULL, '1', '2', '200', '8', 2015, now()),
159 (NULL, '1', '2', '200', '9', 2015, now()),
160 (NULL, '1', '2', '200', '10', 2015, now()),
161 (NULL, '1', '2', '200', '11', 2015, now()),
162 (NULL, '1', '2', '200', '12', 2015, now()),
163 (NULL, '4', '2', '200', '1', 2015, now()),
164 (NULL, '4', '2', '200', '2', 2015, now()),
165 (NULL, '4', '2', '200', '3', 2015, now()),
166 (NULL, '4', '2', '200', '4', 2015, now()),
167 (NULL, '4', '2', '200', '5', 2015, now()),
168 (NULL, '4', '2', '200', '6', 2015, now()),
169 (NULL, '4', '2', '200', '7', 2015, now()),
170 (NULL, '4', '2', '200', '8', 2015, now()),
171 (NULL, '4', '2', '200', '9', 2015, now()),
172 (NULL, '4', '2', '200', '10', 2015, now()),
173 (NULL, '4', '2', '200', '11', 2015, now()),
174 (NULL, '4', '2', '200', '12', 2015, now()),
175 /**2014**/
176 (NULL, '1', '1', '200', '1', 2014, now()),
177 (NULL, '1', '1', '200', '2', 2014, now()),
178 (NULL, '1', '1', '200', '3', 2014, now()),
179 (NULL, '1', '1', '200', '4', 2014, now()),
180 (NULL, '1', '1', '200', '5', 2014, now()),
181 (NULL, '1', '1', '200', '6', 2014, now()),
182 (NULL, '1', '1', '200', '7', 2014, now()),
183 (NULL, '1', '1', '200', '8', 2014, now()),
184 (NULL, '1', '1', '200', '9', 2014, now()),
185 (NULL, '1', '1', '200', '10', 2014, now()),
186 (NULL, '1', '1', '200', '11', 2014, now()),
187 (NULL, '1', '1', '200', '12', 2014, now()),
188 (NULL, '2', '1', '250', '1', 2014, now()),
189 (NULL, '2', '1', '250', '2', 2014, now()),
190 (NULL, '2', '1', '250', '3', 2014, now()),
191 (NULL, '2', '1', '250', '4', 2014, now()),
192 (NULL, '2', '1', '250', '5', 2014, now()),
193 (NULL, '2', '1', '250', '6', 2014, now()),
194 (NULL, '2', '1', '250', '7', 2014, now()),
195 (NULL, '2', '1', '250', '8', 2014, now()),
196 (NULL, '2', '1', '250', '9', 2014, now()),
197 (NULL, '2', '1', '250', '10', 2014, now()),
198 (NULL, '2', '1', '250', '11', 2014, now()),
199 (NULL, '2', '1', '250', '12', 2014, now()),
200 (NULL, '3', '1', '250', '1', 2014, now()),
201 (NULL, '3', '1', '250', '2', 2014, now()),
202 (NULL, '3', '1', '250', '3', 2014, now()),
203 (NULL, '3', '1', '250', '4', 2014, now()),
204 (NULL, '3', '1', '250', '5', 2014, now()),
205 (NULL, '3', '1', '250', '6', 2014, now()),
206 (NULL, '3', '1', '250', '7', 2014, now()),
207 (NULL, '3', '1', '250', '8', 2014, now()),
208 (NULL, '3', '1', '250', '9', 2014, now()),
209 (NULL, '3', '1', '250', '10', 2014, now()),
210 (NULL, '3', '1', '250', '11', 2014, now()),
211 (NULL, '3', '1', '250', '12', 2014, now()),
212 (NULL, '1', '2', '200', '1', 2014, now()),
213 (NULL, '1', '2', '200', '2', 2014, now()),
214 (NULL, '1', '2', '200', '3', 2014, now()),
215 (NULL, '1', '2', '200', '4', 2014, now()),
216 (NULL, '1', '2', '200', '5', 2014, now()),
217 (NULL, '1', '2', '200', '6', 2014, now()),
218 (NULL, '1', '2', '200', '7', 2014, now()),
219 (NULL, '1', '2', '200', '8', 2014, now()),
220 (NULL, '1', '2', '200', '9', 2014, now()),
221 (NULL, '1', '2', '200', '10', 2014, now()),
222 (NULL, '1', '2', '200', '11', 2014, now()),
223 (NULL, '1', '2', '200', '12', 2014, now()),
224 (NULL, '4', '2', '200', '1', 2014, now()),
225 (NULL, '4', '2', '200', '2', 2014, now()),
226 (NULL, '4', '2', '200', '3', 2014, now()),
227 (NULL, '4', '2', '200', '4', 2014, now()),
228 (NULL, '4', '2', '200', '5', 2014, now()),
229 (NULL, '4', '2', '200', '6', 2014, now()),
230 (NULL, '4', '2', '200', '7', 2014, now()),
231 (NULL, '4', '2', '200', '8', 2014, now()),
232 (NULL, '4', '2', '200', '9', 2014, now()),
233 (NULL, '4', '2', '200', '10', 2014, now()),
234 (NULL, '4', '2', '200', '11', 2014, now()),
235 (NULL, '4', '2', '200', '12', 2014, now()),
236 /**2016**/
237 (NULL, '1', '1', '200', '1', 2016, now()),
238 (NULL, '1', '1', '200', '2', 2016, now()),
239 (NULL, '1', '1', '200', '3', 2016, now()),
240 (NULL, '2', '1', '250', '1', 2016, now()),
241 (NULL, '3', '1', '250', '1', 2016, now()),
242 (NULL, '3', '1', '250', '2', 2016, now()),
243 (NULL, '1', '2', '200', '1', 2016, now()),
244 (NULL, '1', '2', '200', '2', 2016, now()),
245 (NULL, '1', '2', '200', '3', 2016, now()),
246 (NULL, '4', '2', '200', '1', 2016, now()),
247 (NULL, '4', '2', '200', '2', 2016, now());