API tools faq
paste
Advertisement
didito33

Lab Project

didito33
May 14th, 2022
27
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 12.76 KB | None | 0 0
raw download clone embed print report
  1. DROP DATABASE IF EXISTS school_sport_clubs;
  2. CREATE DATABASE school_sport_clubs;
  3. USE school_sport_clubs;
  4.  
  5. CREATE TABLE school_sport_clubs.sports(
  6. id INT AUTO_INCREMENT PRIMARY KEY ,
  7. name VARCHAR(255) NOT NULL
  8. );
  9.  
  10. CREATE 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 CONSTRAINT EGN CHECK(CHAR_LENGTH(egn) = 10),
  14. month_salary DECIMAL ,
  15. hour_salary DECIMAL
  16. );
  17.  
  18. CREATE TABLE school_sport_clubs.students(
  19. id INT AUTO_INCREMENT PRIMARY KEY ,
  20. name VARCHAR(255) NOT NULL ,
  21. egn VARCHAR(10) NOT NULL UNIQUE ,
  22. address VARCHAR(255) NOT NULL ,
  23. phone VARCHAR(20) NULL DEFAULT NULL ,
  24. class VARCHAR(10) NULL DEFAULT NULL
  25. );
  26.  
  27. CREATE TABLE school_sport_clubs.sportGroups(
  28. id INT AUTO_INCREMENT PRIMARY KEY ,
  29. location VARCHAR(255) NOT NULL ,
  30. dayOfWeek ENUM('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday') ,
  31. hourOfTraining TIME NOT NULL ,
  32. sport_id INT NOT NULL ,
  33. coach_id INT NOT NULL ,
  34. UNIQUE KEY(location,dayOfWeek,hourOfTraining) ,
  35. CONSTRAINT FOREIGN KEY(sport_id)
  36. REFERENCES sports(id) ,
  37. CONSTRAINT FOREIGN KEY (coach_id)
  38. REFERENCES coaches(id)
  39. );
  40.  
  41. CREATE TABLE school_sport_clubs.student_sport(
  42. student_id INT NOT NULL ,
  43. sportGroup_id INT NOT NULL ,
  44. CONSTRAINT FOREIGN KEY (student_id)
  45. REFERENCES students(id) ,
  46. CONSTRAINT FOREIGN KEY (sportGroup_id)
  47. REFERENCES sportGroups(id) ,
  48. PRIMARY KEY(student_id,sportGroup_id)
  49. );
  50.  
  51. CREATE TABLE taxesPayments(
  52. id INT AUTO_INCREMENT PRIMARY KEY,
  53. student_id INT NOT NULL,
  54. group_id INT NOT NULL,
  55. paymentAmount DOUBLE NOT NULL,
  56. month TINYINT,
  57. year YEAR,
  58. dateOfPayment DATETIME NOT NULL ,
  59. CONSTRAINT FOREIGN KEY (student_id)
  60. REFERENCES students(id),
  61. CONSTRAINT FOREIGN KEY (group_id)
  62. REFERENCES sportgroups(id)
  63. );
  64.  
  65. CREATE TABLE salaryPayments(
  66. id INT AUTO_INCREMENT PRIMARY KEY,
  67. coach_id INT NOT NULL,
  68. month TINYINT,
  69. year YEAR,
  70. salaryAmount double CONSTRAINT salaryCantBeNegative CHECK(salaryAmount >= 0),
  71. dateOfPayment datetime not null,
  72. CONSTRAINT FOREIGN KEY (coach_id)
  73. REFERENCES coaches(id),
  74. UNIQUE KEY(`coach_id`,`month`,`year`)
  75. );
  76.  
  77. create table coach_work(
  78. id int auto_increment primary key,
  79. coach_id int not null,
  80. group_id int not null,
  81. number_of_hours int not null default 1,
  82. date Datetime not null,
  83. isPayed BOOLEAN NOT NULL DEFAULT 0,
  84. foreign key (coach_id) references coaches(id),
  85. foreign key (group_id) references sportgroups(id)
  86. );
  87.  
  88. create table salarypayments_log(
  89. id int auto_increment primary key,
  90. operation ENUM('INSERT','UPDATE','DELETE') not null,
  91. old_coach_id int,
  92. new_coach_id int,
  93. old_month int,
  94. new_month int,
  95. old_year int,
  96. new_year int,
  97. old_salaryAmount decimal,
  98. new_salaryAmount decimal,
  99. old_dateOfPayment datetime,
  100. new_dateOfPayment datetime,
  101. dateOfLog datetime
  102. )Engine = Innodb;
  103.  
  104. INSERT INTO sports
  105. VALUES (NULL, 'Football') ,
  106. (NULL, 'Volleyball'),
  107. (NULL, 'Tennis');
  108.  
  109. INSERT INTO coaches (name, egn)
  110. VALUES ('Ivan Todorov Petkov', '7509041245') ,
  111. ('georgi Ivanov Todorov', '8010091245') ,
  112. ('Ilian Todorov Georgiev', '8407106352') ,
  113. ('Petar Slavkov Yordanov', '7010102045') ,
  114. ('Todor Ivanov Ivanov', '8302160980') ,
  115. ('Slavi Petkov Petkov', '7106041278');
  116.  
  117. INSERT INTO students (name, egn, address, phone, class)
  118. VALUES ('Iliyan Ivanov', '9401150045', 'Sofia-Mladost 1', '0893452120', '10') ,
  119. ('Ivan Iliev Georgiev', '9510104512', 'Sofia-Liylin', '0894123456', '11') ,
  120. ('Elena Petrova Petrova', '9505052154', 'Sofia-Mladost 3', '0897852412', '11') ,
  121. ('Ivan Iliev Iliev', '9510104542', 'Sofia-Mladost 3', '0894123457', '11') ,
  122. ('Maria Hristova Dimova', '9510104547', 'Sofia-Mladost 4', '0894123442', '11') ,
  123. ('Antoaneta Ivanova Georgieva', '9411104547', 'Sofia-Krasno selo', '0874526235', '10');
  124.  
  125. INSERT INTO sportGroups
  126. VALUES (NULL, 'Sofia-Mladost 1', 'Monday', '08:00:00', 1, 1 ) ,
  127. (NULL, 'Sofia-Mladost 1', 'Monday', '09:30:00', 1, 2 ) ,
  128. (NULL, 'Sofia-Liylin 7', 'Sunday', '08:00:00', 2, 1) ,
  129. (NULL, 'Sofia-Liylin 7', 'Sunday', '09:30:00', 2, 2) ,
  130. (NULL, 'Plovdiv', 'Monday', '12:00:00', '1', '1');
  131.  
  132. INSERT INTO student_sport
  133. VALUES (1, 1),
  134. (2, 1),
  135. (3, 1),
  136. (4, 2),
  137. (5, 2),
  138. (6, 2),
  139. (1, 3),
  140. (2, 3),
  141. (3, 3);
  142.  
  143. INSERT INTO `school_sport_clubs`.`taxespayments`
  144. VALUES (NULL, '1', '1', '200', '1', 2022, now()),
  145. (NULL, '1', '1', '200', '2', 2022, now()),
  146. (NULL, '1', '1', '200', '3', 2022, now()),
  147. (NULL, '1', '1', '200', '4', 2022, now()),
  148. (NULL, '1', '1', '200', '5', 2022, now()),
  149. (NULL, '1', '1', '200', '6', 2022, now()),
  150. (NULL, '1', '1', '200', '7', 2022, now()),
  151. (NULL, '1', '1', '200', '8', 2022, now()),
  152. (NULL, '1', '1', '200', '9', 2022, now()),
  153. (NULL, '1', '1', '200', '10', 2022, now()),
  154. (NULL, '1', '1', '200', '11', 2022, now()),
  155. (NULL, '1', '1', '200', '12', 2022, now()),
  156. (NULL, '2', '1', '250', '1', 2022, now()),
  157. (NULL, '2', '1', '250', '2', 2022, now()),
  158. (NULL, '2', '1', '250', '3', 2022, now()),
  159. (NULL, '2', '1', '250', '4', 2022, now()),
  160. (NULL, '2', '1', '250', '5', 2022, now()),
  161. (NULL, '2', '1', '250', '6', 2022, now()),
  162. (NULL, '2', '1', '250', '7', 2022, now()),
  163. (NULL, '2', '1', '250', '8', 2022, now()),
  164. (NULL, '2', '1', '250', '9', 2022, now()),
  165. (NULL, '2', '1', '250', '10', 2022, now()),
  166. (NULL, '2', '1', '250', '11', 2022, now()),
  167. (NULL, '2', '1', '250', '12', 2022, now()),
  168. (NULL, '3', '1', '250', '1', 2022, now()),
  169. (NULL, '3', '1', '250', '2', 2022, now()),
  170. (NULL, '3', '1', '250', '3', 2022, now()),
  171. (NULL, '3', '1', '250', '4', 2022, now()),
  172. (NULL, '3', '1', '250', '5', 2022, now()),
  173. (NULL, '3', '1', '250', '6', 2022, now()),
  174. (NULL, '3', '1', '250', '7', 2022, now()),
  175. (NULL, '3', '1', '250', '8', 2022, now()),
  176. (NULL, '3', '1', '250', '9', 2022, now()),
  177. (NULL, '3', '1', '250', '10', 2022, now()),
  178. (NULL, '3', '1', '250', '11', 2022, now()),
  179. (NULL, '3', '1', '250', '12', 2022, now()),
  180. (NULL, '1', '2', '200', '1', 2022, now()),
  181. (NULL, '1', '2', '200', '2', 2022, now()),
  182. (NULL, '1', '2', '200', '3', 2022, now()),
  183. (NULL, '1', '2', '200', '4', 2022, now()),
  184. (NULL, '1', '2', '200', '5', 2022, now()),
  185. (NULL, '1', '2', '200', '6', 2022, now()),
  186. (NULL, '1', '2', '200', '7', 2022, now()),
  187. (NULL, '1', '2', '200', '8', 2022, now()),
  188. (NULL, '1', '2', '200', '9', 2022, now()),
  189. (NULL, '1', '2', '200', '10', 2022, now()),
  190. (NULL, '1', '2', '200', '11', 2022, now()),
  191. (NULL, '1', '2', '200', '12', 2022, now()),
  192. (NULL, '4', '2', '200', '1', 2022, now()),
  193. (NULL, '4', '2', '200', '2', 2022, now()),
  194. (NULL, '4', '2', '200', '3', 2022, now()),
  195. (NULL, '4', '2', '200', '4', 2022, now()),
  196. (NULL, '4', '2', '200', '5', 2022, now()),
  197. (NULL, '4', '2', '200', '6', 2022, now()),
  198. (NULL, '4', '2', '200', '7', 2022, now()),
  199. (NULL, '4', '2', '200', '8', 2022, now()),
  200. (NULL, '4', '2', '200', '9', 2022, now()),
  201. (NULL, '4', '2', '200', '10', 2022, now()),
  202. (NULL, '4', '2', '200', '11', 2022, now()),
  203. (NULL, '4', '2', '200', '12', 2022, now()),
  204. /**2021**/
  205. (NULL, '1', '1', '200', '1', 2021, now()),
  206. (NULL, '1', '1', '200', '2', 2021, now()),
  207. (NULL, '1', '1', '200', '3', 2021, now()),
  208. (NULL, '1', '1', '200', '4', 2021, now()),
  209. (NULL, '1', '1', '200', '5', 2021, now()),
  210. (NULL, '1', '1', '200', '6', 2021, now()),
  211. (NULL, '1', '1', '200', '7', 2021, now()),
  212. (NULL, '1', '1', '200', '8', 2021, now()),
  213. (NULL, '1', '1', '200', '9', 2021, now()),
  214. (NULL, '1', '1', '200', '10', 2021, now()),
  215. (NULL, '1', '1', '200', '11', 2021, now()),
  216. (NULL, '1', '1', '200', '12', 2021, now()),
  217. (NULL, '2', '1', '250', '1', 2021, now()),
  218. (NULL, '2', '1', '250', '2', 2021, now()),
  219. (NULL, '2', '1', '250', '3', 2021, now()),
  220. (NULL, '2', '1', '250', '4', 2021, now()),
  221. (NULL, '2', '1', '250', '5', 2021, now()),
  222. (NULL, '2', '1', '250', '6', 2021, now()),
  223. (NULL, '2', '1', '250', '7', 2021, now()),
  224. (NULL, '2', '1', '250', '8', 2021, now()),
  225. (NULL, '2', '1', '250', '9', 2021, now()),
  226. (NULL, '2', '1', '250', '10', 2021, now()),
  227. (NULL, '2', '1', '250', '11', 2021, now()),
  228. (NULL, '2', '1', '250', '12', 2021, now()),
  229. (NULL, '3', '1', '250', '1', 2021, now()),
  230. (NULL, '3', '1', '250', '2', 2021, now()),
  231. (NULL, '3', '1', '250', '3', 2021, now()),
  232. (NULL, '3', '1', '250', '4', 2021, now()),
  233. (NULL, '3', '1', '250', '5', 2021, now()),
  234. (NULL, '3', '1', '250', '6', 2021, now()),
  235. (NULL, '3', '1', '250', '7', 2021, now()),
  236. (NULL, '3', '1', '250', '8', 2021, now()),
  237. (NULL, '3', '1', '250', '9', 2021, now()),
  238. (NULL, '3', '1', '250', '10', 2021, now()),
  239. (NULL, '3', '1', '250', '11', 2021, now()),
  240. (NULL, '3', '1', '250', '12', 2021, now()),
  241. (NULL, '1', '2', '200', '1', 2021, now()),
  242. (NULL, '1', '2', '200', '2', 2021, now()),
  243. (NULL, '1', '2', '200', '3', 2021, now()),
  244. (NULL, '1', '2', '200', '4', 2021, now()),
  245. (NULL, '1', '2', '200', '5', 2021, now()),
  246. (NULL, '1', '2', '200', '6', 2021, now()),
  247. (NULL, '1', '2', '200', '7', 2021, now()),
  248. (NULL, '1', '2', '200', '8', 2021, now()),
  249. (NULL, '1', '2', '200', '9', 2021, now()),
  250. (NULL, '1', '2', '200', '10', 2021, now()),
  251. (NULL, '1', '2', '200', '11', 2021, now()),
  252. (NULL, '1', '2', '200', '12', 2021, now()),
  253. (NULL, '4', '2', '200', '1', 2021, now()),
  254. (NULL, '4', '2', '200', '2', 2021, now()),
  255. (NULL, '4', '2', '200', '3', 2021, now()),
  256. (NULL, '4', '2', '200', '4', 2021, now()),
  257. (NULL, '4', '2', '200', '5', 2021, now()),
  258. (NULL, '4', '2', '200', '6', 2021, now()),
  259. (NULL, '4', '2', '200', '7', 2021, now()),
  260. (NULL, '4', '2', '200', '8', 2021, now()),
  261. (NULL, '4', '2', '200', '9', 2021, now()),
  262. (NULL, '4', '2', '200', '10', 2021, now()),
  263. (NULL, '4', '2', '200', '11', 2021, now()),
  264. (NULL, '4', '2', '200', '12', 2021, now()),
  265. /**2020**/
  266. (NULL, '1', '1', '200', '1', 2020, now()),
  267. (NULL, '1', '1', '200', '2', 2020, now()),
  268. (NULL, '1', '1', '200', '3', 2020, now()),
  269. (NULL, '2', '1', '250', '1', 2020, now()),
  270. (NULL, '3', '1', '250', '1', 2020, now()),
  271. (NULL, '3', '1', '250', '2', 2020, now()),
  272. (NULL, '1', '2', '200', '1', 2020, now()),
  273. (NULL, '1', '2', '200', '2', 2020, now()),
  274. (NULL, '1', '2', '200', '3', 2020, now()),
  275. (NULL, '4', '2', '200', '1', 2020, now()),
  276. (NULL, '4', '2', '200', '2', 2020, now());
  277.  
  278. UPDATE `school_sport_clubs`.`coaches` SET `month_salary`='2200', `hour_salary`='24' WHERE `id`='1';
  279. UPDATE `school_sport_clubs`.`coaches` SET `month_salary`='2300', `hour_salary`='25' WHERE `id`='2';
  280. UPDATE `school_sport_clubs`.`coaches` SET `month_salary`='2800', `hour_salary`='28' WHERE `id`='3';
  281. UPDATE `school_sport_clubs`.`coaches` SET `month_salary`='3000', `hour_salary`='30' WHERE `id`='4';
  282. UPDATE `school_sport_clubs`.`coaches` SET `month_salary`='2450', `hour_salary`='26' WHERE `id`='5';
  283.  
  284. INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('1', '1', '2', '2021-03-07 08:45:55');
  285. INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('1', '1', '2', '2021-03-14 08:45:55');
  286. INSERT INTO `school_sport_clubs`.`coach_\work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('1', '1', '2', '2021-03-21 08:45:55');
  287. INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('1', '1', '2', '2021-03-28 08:45:55');
  288. INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('1', '1', '2', '2021-04-04 08:45:55');
  289. INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('1', '1', '2', '2021-04-11 08:45:55');
  290. INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('2', '2', '2', '2021-03-07 08:45:55');
  291. INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('2', '2', '2', '2021-03-14 08:45:55');
  292. INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('2', '2', '2', '2021-03-21 08:45:55');
  293. INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('2', '2', '2', '2021-03-28 08:45:55');
  294. INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('2', '2', '2', '2021-04-04 08:45:55');
  295. INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('2', '2', '2', '2021-04-11 08:45:55');
  296. INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('2', '3', '2', '2021-04-02 08:45:55');
  297. INSERT INTO `school_sport_clubs`.`coach_work` (`coach_id`, `group_id`, `number_of_hours`, `date`) VALUES ('2', '3', '2', '2021-04-09 08:45:55');
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!