1Dg0uG1v

1/*
2Autorzy:
3Jacek Gawron
4Mirosław Kuźniar
5
6Baza wykonana na serwerze MySQL z wykorzysatniem programu MySQL Workbench  
7*/
8/*Stworz baze*/
9DROP DATABASE IF EXISTS projekt_jg_mk;
10CREATE DATABASE projekt_jg_mk;
11USE projekt_jg_mk;
12
13/*Tworzenie tabel*/
14CREATE TABLE Reservation_Status ( 
15    ID INT AUTO_INCREMENT, 
16    Name VARCHAR(4000) NOT NULL, 
17    PRIMARY KEY (ID) 
18);
19
20CREATE TABLE Rent_Status ( 
21    ID INT AUTO_INCREMENT, 
22    Name VARCHAR(4000), 
23    PRIMARY KEY(ID) 
24);
25
26CREATE TABLE Library_Cards ( 
27    ID INT AUTO_INCREMENT, 
28    Card_Number VARCHAR(30), 
29    Owner_Last_Name VARCHAR(50), 
30    Owner_Phone_Number BIGINT, 
31    PRIMARY KEY(ID) 
32);
33
34CREATE TABLE Authors ( 
35    ID INT AUTO_INCREMENT, 
36    First_Name VARCHAR(4000), 
37    Last_Name VARCHAR(4000), 
38    PRIMARY KEY(ID) 
39);
40
41CREATE TABLE Genres(
42    ID int NOT NULL AUTO_INCREMENT,
43    Name varchar(4000),
44    PRIMARY KEY (ID)
45);
46 
47CREATE TABLE Branches(
48    ID int NOT NULL AUTO_INCREMENT,
49    Name varchar(4000),
50    Address varchar(4000),
51    PhoneNumber bigint,
52    OpenTime time,
53    CloseTime time,
54    PRIMARY KEY (ID)
55);
56 
57CREATE TABLE Publishers(
58    ID int NOT NULL AUTO_INCREMENT,
59    Name varchar(4000),
60    PRIMARY KEY (ID)
61);
62 
63CREATE TABLE Books(
64    ID int NOT NULL AUTO_INCREMENT,
65    Title varchar(4000),
66    YearOfRelease int,
67    Publishers_ID int,
68    Branches_ID int,
69    PRIMARY KEY (ID),
70    FOREIGN KEY(branches_id)
71        REFERENCES Branches(ID),
72    FOREIGN KEY(publishers_id)
73        REFERENCES Publishers(ID)
74);
75 
76CREATE TABLE genres_books(
77    Genres_ID int,
78    Books_ID int,
79    PRIMARY KEY(Genres_ID, Books_ID),
80    FOREIGN KEY(genres_id)
81        REFERENCES Genres(ID),
82    FOREIGN KEY(books_id)
83        REFERENCES Books(ID)
84);
85
86CREATE TABLE Positions(
87    ID int NOT NULL AUTO_INCREMENT,
88    PositionName varchar(4000),
89    Salary float,
90    PRIMARY KEY (ID)
91);
92
93CREATE TABLE Employees(
94    ID int NOT NULL AUTO_INCREMENT,
95    FirstName varchar(4000),
96    LastName varchar(4000),
97    Branches_ID int,
98    Positions_ID int,
99    PRIMARY KEY (ID),
100    FOREIGN KEY(branches_id)
101        REFERENCES Branches(ID),
102    FOREIGN KEY(positions_id)
103        REFERENCES Positions(ID)
104);
105
106CREATE TABLE authors_books ( 
107    authors_id INT NOT NULL, 
108    books_id INT NOT NULL,
109    FOREIGN KEY(authors_id) REFERENCES Authors(ID), 
110    FOREIGN KEY(books_id) REFERENCES Books(ID) 
111);
112
113CREATE TABLE Reservations ( 
114    ID INT AUTO_INCREMENT, 
115    Start_Date DATE, 
116    End_Date DATE, 
117    reservation_status_id INT NOT NULL, 
118    library_cards_id INT NOT NULL, 
119    books_id INT NOT NULL, 
120    PRIMARY KEY(ID), 
121    FOREIGN KEY(reservation_status_id) REFERENCES reservation_status(ID), 
122    FOREIGN KEY(library_cards_id) REFERENCES Library_cards(ID), 
123    FOREIGN KEY(books_id) REFERENCES books(ID) 
124);
125
126CREATE TABLE Rents ( 
127    ID INT AUTO_INCREMENT, 
128    Start_Date DATE, End_Date DATE, 
129    rent_status_id INT NOT NULL, 
130    library_cards_id INT NOT NULL, 
131    books_id INT NOT NULL, 
132    PRIMARY KEY(ID), 
133    FOREIGN KEY(rent_status_id) REFERENCES Rent_Status(ID), 
134    FOREIGN KEY(library_cards_id) REFERENCES Library_Cards(ID), 
135    FOREIGN KEY(books_id) REFERENCES Books(ID) 
136);
137/*Tworzenie wyzwalacza szukającego pasującej rezerwacji i ustawiający jej status na zakończoną*/
138DROP TRIGGER IF EXISTS setReservationStatus;
139CREATE TRIGGER setReservationStatus 
140AFTER INSERT
141ON rents
142FOR EACH ROW
143UPDATE reservations SET reservation_status_id = 3 
144WHERE library_cards_id = NEW.library_cards_id AND books_id = NEW.books_id 
145AND DATEDIFF(start_date, CURDATE()) <= 0 AND DATEDIFF(end_date,CURDATE()) >= 0;
146
147/*uzupelnij tabelę reservation_status*/
148INSERT INTO Reservation_Status(ID,Name) VALUES (NULL,"Waiting");
149INSERT INTO Reservation_Status(ID,Name) VALUES (NULL,"Avaiable");
150INSERT INTO Reservation_Status(ID,Name) VALUES (NULL,"Completed");
151/*uzupelnij tabelę rent_status*/ 
152INSERT INTO rent_status(ID,Name) VALUES (NULL,"rented");
153INSERT INTO rent_status(ID,Name) VALUES (NULL,"returned");
154INSERT INTO rent_status(ID,Name) VALUES (NULL,"not returned");
155/*Uzupelnij tabelę authors*/
156INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Adam', 'Mickiewicz');
157INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Jan', 'Kochanowski');
158INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Juliusz', 'Slowacki');
159INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Stephen', 'King');
160INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Cortney', 'Brokenbrow');
161INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Ianthe', 'Chettle');
162INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Hershel', 'Druery');
163INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Xaviera', 'Parres');
164INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Rupert', 'Friend');
165INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Joanne', 'Mailey');
166INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Levon', 'Gudger');
167INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Far', 'Walcar');
168INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Noll', 'Hassen');
169INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Olwen', 'Shotton');
170INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Hynda', 'Madison');
171INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Tatum', 'Flippen');
172INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Hildegarde', 'Suscens');
173INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Clem', 'Danbrook');
174INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Adriana', 'Dubois');
175INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Shelden', 'Davydochkin');
176INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Celine', 'Colloff');
177INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Hillel', 'McDonough');
178INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Dee dee', 'Camier');
179INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Zach', 'Guidotti');
180INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Rolando', 'Silcocks');
181INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Jenilee', 'Friedank');
182INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Ezechiel', 'Origin');
183INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Imojean', 'de Banke');
184INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Fraze', 'Alkin');
185INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Darwin', 'Jacox');
186INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Hatty', 'Dafydd');
187INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Damita', 'Knight');
188INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'April', 'Hollyman');
189INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Micky', 'Froude');
190INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Diarmid', 'Herculson');
191INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Rhodia', 'Sandland');
192INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Petey', 'Jarmyn');
193INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Duncan', 'Anthon');
194INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Launce', 'Nutbeem');
195INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Saxe', 'Blumire');
196INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Ursala', 'Collumbell');
197INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Marge', 'Martignon');
198INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Skipper', 'Tatlowe');
199INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Collette', 'Ilbert');
200INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Norine', 'Cappleman');
201INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Elonore', 'Doul');
202INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Zaneta', 'Von Oertzen');
203INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Idelle', 'Bispham');
204INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Stephenie', 'Marsie');
205INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Nadean', 'Noraway');
206INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Ofelia', 'Worms');
207INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Gabriellia', 'Tallis');
208INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Leigh', 'Pavese');
209INSERT INTO authors (ID, first_name, last_name) VALUES (NULL, 'Addie', 'Tschierasche');
210/*Uzupelnij tabelę publishers*/
211INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Huels-Steuber');
212INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Ondricka and Sons');
213INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Bode, Morissette and Kertzmann');
214INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Bogan, Auer and Kutch');
215INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Murphy, Rolfson and Okuneva');
216INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Wiza, Mertz and Huels');
217INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Dibbert-Haag');
218INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Rippin-Koelpin');
219INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Wehner Group');
220INSERT INTO Publishers (ID, Name) VALUES (NULL, 'Feest LLC');
221/*Uzupelnij tabelę genres*/
222INSERT INTO Genres (ID, Name) VALUES (NULL, 'Drama');
223INSERT INTO Genres (ID, Name) VALUES (NULL, 'Action');
224INSERT INTO Genres (ID, Name) VALUES (NULL, 'Action|Drama');
225INSERT INTO Genres (ID, Name) VALUES (NULL, 'Adventure|Fantasy');
226INSERT INTO Genres (ID, Name) VALUES (NULL, 'Drama|Mystery|Thriller');
227INSERT INTO Genres (ID, Name) VALUES (NULL, 'Comedy');
228INSERT INTO Genres (ID, Name) VALUES (NULL, 'Comedy');
229INSERT INTO Genres (ID, Name) VALUES (NULL, 'Adventure');
230INSERT INTO Genres (ID, Name) VALUES (NULL, 'Comedy|Crime|Drama|Mystery');
231INSERT INTO Genres (ID, Name) VALUES (NULL, 'Comedy');
232/*Uzupelnij tabelę branches*/
233INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Trailing Windmills', '95912 Kropf Center', '6977585824', '8:20', '19:21');
234INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Softleaf Sedge', '3 Derek Street', '8751849441', '9:25', '19:59');
235INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Coastal Serviceberry', '2993 Namekagon Pass', '8308464841', '9:14', '19:21');
236INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Cup Lichen', '479 Merrick Junction', '4805405334', '8:40', '17:19');
237INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Spanish Thyme', '227 Lien Circle', '5832028409', '9:51', '19:08');
238INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Redmahogany', '35 Arrowood Point', '6645419434', '8:19', '19:07');
239INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Corzo''s Wild Petunia', '811 Vermont Road', '7047380890', '8:38', '18:17');
240INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Flowers'' Beardtongue', '8572 Ohio Pass', '2433824110', '9:56', '18:58');
241INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'California Black Oak', '82 Mandrake Park', '7062696094', '8:43', '18:24');
242INSERT INTO Branches (ID, Name, Address, PhoneNumber, OpenTime, CloseTime) VALUES (NULL, 'Clamshell Orchid', '7918 Hermina Plaza', '8041386382', '8:17', '17:53');
243 /*Uzupelnij tabelę positions*/ 
244INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'Software Test Engineer I', '1685.52');
245INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'VP Quality Control', '3101.35');
246INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'Community Outreach Specialist', '5986.27');
247INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'Product Engineer', '3800.84');
248INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'Assistant Professor', '2702.78');
249INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'Cost Accountant', '3618.35');
250INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'Health Coach III', '3604.60');
251INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'VP Marketing', '3178.73');
252INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'Executive Secretary', '6300.33');
253INSERT INTO Positions (ID, PositionName, Salary) VALUES (NULL, 'Research Associate', '5712.31');
254/*Uzupelnij tabelę employees*/
255INSERT INTO Employees (ID, FirstName, LastName, Branches_id, Positions_id) VALUES (NULL, 'Reube', 'Bertenshaw', 1, 2);
256INSERT INTO Employees (ID, FirstName, LastName, Branches_id, Positions_id) VALUES (NULL, 'Joannes', 'Riditch', 1, 4);
257INSERT INTO Employees (ID, FirstName, LastName, Branches_id, Positions_id) VALUES (NULL, 'Clea', 'Caddell', 1, 9);
258INSERT INTO Employees (ID, FirstName, LastName, Branches_id, Positions_id) VALUES (NULL, 'Loralee', 'Phinnis', 2, 5);
259INSERT INTO Employees (ID, FirstName, LastName, Branches_id, Positions_id) VALUES (NULL, 'Natasha', 'Crawforth', 3, 5);
260INSERT INTO Employees (ID, FirstName, LastName, Branches_id, Positions_id) VALUES (NULL, 'Engracia', 'Mordin',4 ,5);
261INSERT INTO Employees (ID, FirstName, LastName, Branches_id, Positions_id) VALUES (NULL, 'Vassily', 'Skillett', 5, 7);
262INSERT INTO Employees (ID, FirstName, LastName, Branches_id, Positions_id) VALUES (NULL, 'Bianca', 'Justun', 6, 8);
263INSERT INTO Employees (ID, FirstName, LastName, Branches_id, Positions_id) VALUES (NULL, 'Peyton', 'Riccelli', 8, 1);
264INSERT INTO Employees (ID, FirstName, LastName, Branches_id, Positions_id) VALUES (NULL, 'Cly', 'Owbrick', 10, 6);
265/*Uzupelnij tabelę books*/
266INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Concussion', 1995, 3, 1);
267INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Human Trafficking', 2012, 6, 1);
268INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Bonjour tristesse', 1992, 3, 1);
269INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'City Island', 1998, 2, 2);
270INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Sir Arne''s Treasure', 2002, 2, 6);
271INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Thomas Crown Affair, The', 1991, 5, 6);
272INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Territories ', 2012, 4, 7);
273INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Larry the Cable Guy: Health Inspector', 1998, 4, 8);
274INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Day of the Falcon', 2001, 4, 9);
275INSERT INTO Books (ID, Title, YearOfRelease, Publishers_id, Branches_id) VALUES (NULL, 'Caesar (Julius Caesar)', 1986, 6, 3);
276/*Uzupelnij tabelę library_cards*/
277INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'kRJH-CzZi-J4aa', 'Bendel', '1723115335');
278INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'zTb1-kvE8-zOSY', 'McEllen', '4238682589');
279INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, '9DJm-cdAy-y8Bk', 'Kelledy', '5589959021');
280INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, '3EFt-Igg8-Xim5', 'Scrowby', '2969494584');
281INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'Lnj0-ojUw-rFI6', 'Baggaley', '2382664387');
282INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'DAF0-XhEk-KSW8', 'Retallack', '8416886734');
283INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'w4Jc-gojI-B54j', 'Kigelman', '7859945478');
284INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'Hd0I-lvyG-CBe9', 'Affuso', '8181971986');
285INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'fp8F-xLEA-3ARH', 'Tabbernor', '5025235234');
286INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'e7PC-KweV-jkZy', 'Calcraft', '6459852554');
287INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'FmB3-5QRu-eIBr', 'Janiak', '4081744606');
288INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, '6kEq-ZmlU-gopQ', 'Kimberley', '2656671222');
289INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'G7zm-EB5G-kjrW', 'Rutigliano', '3047256848');
290INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'AbdT-CaJZ-0j5s', 'Kleeborn', '8157272467');
291INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'nmwG-MQKD-37ML', 'Burnapp', '5192217949');
292INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, '0GdF-thZ7-TeSQ', 'Blagden', '5501849359');
293INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'WOCO-DT18-5yhD', 'Caulier', '4222965267');
294INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, '6vKo-7vjA-2d4r', 'Perren', '2933766466');
295INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'NDtl-FwLh-pcrT', 'Brugden', '7555154982');
296INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, '5Opp-BACv-NVG2', 'Foat', '9602934014');
297INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, '0udC-bLRE-npDy', 'Halladey', '5845724399');
298INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'aLBl-971J-AJ2q', 'Dietsche', '7049331297');
299INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'UueW-PZW2-SQpB', 'De Bischof', '6348152598');
300INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'LUNo-sOUp-yWPF', 'Hegges', '1765496197');
301INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, '2n8y-GqQb-udEC', 'Isenor', '2147434845');
302INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, '3I7B-sYf5-CP7X', 'Rickert', '2638160105');
303INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'tPvT-fUQU-nzkv', 'Bessey', '6867330055');
304INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'r3mt-H1hs-a0Ji', 'Ismirnioglou', '8808013610');
305INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, '0uz3-53mL-Jefv', 'Durrans', '2961741616');
306INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'QR3M-QFlk-CGt1', 'Aldgate', '2392374465');
307INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'nJDG-0bRK-8DyC', 'Catcherside', '2565830742');
308INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'brPw-6ShG-Eis5', 'Belasco', '6013413554');
309INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 's2en-hi9Z-r7Yl', 'Clawley', '9575631113');
310INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'k41o-qKgQ-L8Qi', 'Whereat', '1934727371');
311INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'qJVv-xsDC-xgwa', 'Bertholin', '6536349578');
312INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'zSWv-ZsoR-sfqn', 'Gypps', '8737099761');
313INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'n3ld-LBGO-5LB6', 'Dericot', '7332328816');
314INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'q8vO-nRze-GIfx', 'Crimin', '9583617056');
315INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'eDkc-8dHP-Cioa', 'Pendrigh', '9853280818');
316INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'K9aN-dsbX-EA6V', 'Peasey', '9716206115');
317INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'HNGb-Rbjr-oHYG', 'Grishankov', '1784309857');
318INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, '7tMo-KUlf-BRlS', 'Brearley', '1105250054');
319INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'dXMJ-leGP-mg01', 'Whiteoak', '6997653858');
320INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'gY1M-lxMz-VOXZ', 'Sworder', '6606458688');
321INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'MHCl-VL6m-zvyV', 'Lomath', '5244750576');
322INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'Ruzy-m7Of-D1f3', 'Shore', '1098033036');
323INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'OGc9-Ywus-Rgke', 'Burree', '2103919293');
324INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'sPWx-xpl8-TbrN', 'Lenard', '5734700231');
325INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, 'SYVc-HrUj-3XDd', 'Maffeo', '5058614244');
326INSERT INTO library_cards (ID, card_number, owner_last_name, owner_phone_number) VALUES (NULL, '7ZnK-DV4Y-n806', 'Alldis', '3128625724');
327/*Uzupelnij tabelę authors_books*/
328INSERT INTO authors_books (authors_id, books_id) VALUES (2, 8);
329INSERT INTO authors_books (authors_id, books_id) VALUES (10, 2);
330INSERT INTO authors_books (authors_id, books_id) VALUES (9, 7);
331INSERT INTO authors_books (authors_id, books_id) VALUES (6, 6);
332INSERT INTO authors_books (authors_id, books_id) VALUES (9, 8);
333INSERT INTO authors_books (authors_id, books_id) VALUES (5, 10);
334INSERT INTO authors_books (authors_id, books_id) VALUES (10, 5);
335INSERT INTO authors_books (authors_id, books_id) VALUES (5, 3);
336INSERT INTO authors_books (authors_id, books_id) VALUES (3, 3);
337INSERT INTO authors_books (authors_id, books_id) VALUES (10, 1);
338INSERT INTO authors_books (authors_id, books_id) VALUES (5, 9);
339INSERT INTO authors_books (authors_id, books_id) VALUES (8, 10);
340INSERT INTO authors_books (authors_id, books_id) VALUES (5, 4);
341INSERT INTO authors_books (authors_id, books_id) VALUES (1, 5);
342INSERT INTO authors_books (authors_id, books_id) VALUES (1, 2);
343/*Uzupelnij tabelę genres_books*/
344INSERT INTO genres_books (genres_id, books_id) VALUES (7, 10);
345INSERT INTO genres_books (genres_id, books_id) VALUES (8, 7);
346INSERT INTO genres_books (genres_id, books_id) VALUES (10, 7);
347INSERT INTO genres_books (genres_id, books_id) VALUES (9, 4);
348INSERT INTO genres_books (genres_id, books_id) VALUES (6, 8);
349INSERT INTO genres_books (genres_id, books_id) VALUES (9, 9);
350INSERT INTO genres_books (genres_id, books_id) VALUES (9, 1);
351INSERT INTO genres_books (genres_id, books_id) VALUES (4, 7);
352INSERT INTO genres_books (genres_id, books_id) VALUES (1, 3);
353INSERT INTO genres_books (genres_id, books_id) VALUES (1, 6);
354INSERT INTO genres_books (genres_id, books_id) VALUES (1, 5);
355INSERT INTO genres_books (genres_id, books_id) VALUES (4, 2);
356/*Uzupelnij tabelę reservations*/
357insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-06-05', '2019-07-05', 1, 3, 5);
358insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-08-29', '2019-09-29', 3, 1, 1);
359insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-08-08', '2019-09-08', 1, 3, 4);
360insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-08-21', '2019-09-21', 2, 1, 3);
361insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-07-02', '2019-08-02', 3, 2, 6);
362insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-10-05', '2019-11-05', 3, 3, 3);
363insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-05-04', '2019-06-04', 1, 3, 6);
364insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-07-16', '2019-08-16', 1, 1, 2);
365insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-05-02', '2019-06-02', 2, 1, 7);
366insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-09-11', '2019-10-11', 3, 3, 8);
367insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-11-10', '2019-12-10', 3, 1, 3);
368insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-08-23', '2019-09-23', 2, 2, 9);
369insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-10-08', '2019-11-08', 3, 3, 4);
370insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-10-26', '2019-11-26', 2, 1, 2);
371insert into reservations (id, start_date, end_date, reservation_status_id, library_cards_id, books_id) values (null, '2019-05-22', '2019-06-22', 1, 1, 3);
372/*Uzupelnij tabelę rents*/
373insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-07-01', '2019-08-01', 3, 2, 6);
374insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-08-04', '2019-09-04', 3, 2, 8);
375insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-07-01', '2019-08-01', 2, 1, 10);
376insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-05-11', '2019-06-11', 3, 1, 5);
377insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-05-17', '2019-06-17', 1, 3, 6);
378insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-03-06', '2019-04-06', 3, 3, 4);
379insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-05-13', '2019-06-13', 3, 1, 6);
380insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-07-16', '2019-08-16', 2, 1, 2);
381insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-06-30', '2019-07-30', 1, 2, 1);
382insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-06-29', '2019-07-29', 2, 3, 9);
383insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-04-05', '2019-05-05', 1, 2, 9);
384insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-04-29', '2019-05-29', 2, 1, 9);
385insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-05-06', '2019-06-06', 1, 2, 5);
386insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-07-30', '2019-08-30', 3, 3, 3);
387insert into rents (id, start_date, end_date, rent_status_id, library_cards_id, books_id) values (null, '2019-07-05', '2019-08-05', 1, 3, 9);
388
389CREATE OR REPLACE VIEW Catalogue_Trailing_WIndmills AS
390SELECT books.Title, books.YearOfRelease, GROUP_CONCAT(authors.first_name,' ', authors.last_name) AS Authors, publishers.Name AS Publisher, GROUP_CONCAT(genres.Name) AS Genres FROM Books
391JOIN authors_books ON authors_books.books_id = books.id
392JOIN authors ON authors_books.authors_id  = authors.id 
393JOIN publishers ON books.publishers_id = publishers.id
394JOIN genres_books ON genres_books.books_id = books.id
395JOIN genres ON genres_books.genres_id = genres.id
396WHERE books.branches_id = 1
397GROUP BY books.Title;
398
399CREATE OR REPLACE VIEW Catalogue_Softleaf_Sedge AS
400SELECT books.Title, books.YearOfRelease, GROUP_CONCAT(authors.first_name,' ', authors.last_name) AS Authors, publishers.Name AS Publisher, GROUP_CONCAT(genres.Name) AS Genres FROM Books
401JOIN authors_books ON authors_books.books_id = books.id
402JOIN authors ON authors_books.authors_id  = authors.id 
403JOIN publishers ON books.publishers_id = publishers.id
404JOIN genres_books ON genres_books.books_id = books.id
405JOIN genres ON genres_books.genres_id = genres.id
406WHERE books.branches_id = 2
407GROUP BY books.Title;
408
409CREATE OR REPLACE VIEW Catalogue_Coastal_Serviceberry AS
410SELECT books.Title, books.YearOfRelease, GROUP_CONCAT(authors.first_name,' ', authors.last_name) AS Authors, publishers.Name AS Publisher, GROUP_CONCAT(genres.Name) AS Genres FROM Books
411JOIN authors_books ON authors_books.books_id = books.id
412JOIN authors ON authors_books.authors_id  = authors.id 
413JOIN publishers ON books.publishers_id = publishers.id
414JOIN genres_books ON genres_books.books_id = books.id
415JOIN genres ON genres_books.genres_id = genres.id
416WHERE books.branches_id = 3
417GROUP BY books.Title;
418
419CREATE OR REPLACE VIEW Catalogue_Cup_Lichen AS
420SELECT books.Title, books.YearOfRelease, GROUP_CONCAT(authors.first_name,' ', authors.last_name) AS Authors, publishers.Name AS Publisher, GROUP_CONCAT(genres.Name) AS Genres FROM Books
421JOIN authors_books ON authors_books.books_id = books.id
422JOIN authors ON authors_books.authors_id  = authors.id 
423JOIN publishers ON books.publishers_id = publishers.id
424JOIN genres_books ON genres_books.books_id = books.id
425JOIN genres ON genres_books.genres_id = genres.id
426WHERE books.branches_id = 4
427GROUP BY books.Title;
428
429CREATE OR REPLACE VIEW Catalogue_Spanish_Thyme AS
430SELECT books.Title, books.YearOfRelease, GROUP_CONCAT(authors.first_name,' ', authors.last_name) AS Authors, publishers.Name AS Publisher, GROUP_CONCAT(genres.Name) AS Genres FROM Books
431JOIN authors_books ON authors_books.books_id = books.id
432JOIN authors ON authors_books.authors_id  = authors.id 
433JOIN publishers ON books.publishers_id = publishers.id
434JOIN genres_books ON genres_books.books_id = books.id
435JOIN genres ON genres_books.genres_id = genres.id
436WHERE books.branches_id = 5
437GROUP BY books.Title;
438
439CREATE OR REPLACE VIEW Catalogue_Redmahogany AS
440SELECT books.Title, books.YearOfRelease, GROUP_CONCAT(authors.first_name,' ', authors.last_name) AS Authors, publishers.Name AS Publisher, GROUP_CONCAT(genres.Name) AS Genres FROM Books
441JOIN authors_books ON authors_books.books_id = books.id
442JOIN authors ON authors_books.authors_id  = authors.id 
443JOIN publishers ON books.publishers_id = publishers.id
444JOIN genres_books ON genres_books.books_id = books.id
445JOIN genres ON genres_books.genres_id = genres.id
446WHERE books.branches_id = 6
447GROUP BY books.Title;
448
449CREATE OR REPLACE VIEW Catalogue_Corzos_Wild AS
450SELECT books.Title, books.YearOfRelease, GROUP_CONCAT(authors.first_name,' ', authors.last_name) AS Authors, publishers.Name AS Publisher, GROUP_CONCAT(genres.Name) AS Genres FROM Books
451JOIN authors_books ON authors_books.books_id = books.id
452JOIN authors ON authors_books.authors_id  = authors.id 
453JOIN publishers ON books.publishers_id = publishers.id
454JOIN genres_books ON genres_books.books_id = books.id
455JOIN genres ON genres_books.genres_id = genres.id
456WHERE books.branches_id = 7
457GROUP BY books.Title;
458
459CREATE OR REPLACE VIEW Catalogue_Flowers_Beardtongue AS
460SELECT books.Title, books.YearOfRelease, GROUP_CONCAT(authors.first_name,' ', authors.last_name) AS Authors, publishers.Name AS Publisher, GROUP_CONCAT(genres.Name) AS Genres FROM Books
461JOIN authors_books ON authors_books.books_id = books.id
462JOIN authors ON authors_books.authors_id  = authors.id 
463JOIN publishers ON books.publishers_id = publishers.id
464JOIN genres_books ON genres_books.books_id = books.id
465JOIN genres ON genres_books.genres_id = genres.id
466WHERE books.branches_id = 8
467GROUP BY books.Title;
468
469CREATE OR REPLACE VIEW Catalogue_California_Black AS
470SELECT books.Title, books.YearOfRelease, GROUP_CONCAT(authors.first_name,' ', authors.last_name) AS Authors, publishers.Name AS Publisher, GROUP_CONCAT(genres.Name) AS Genres FROM Books
471JOIN authors_books ON authors_books.books_id = books.id
472JOIN authors ON authors_books.authors_id  = authors.id 
473JOIN publishers ON books.publishers_id = publishers.id
474JOIN genres_books ON genres_books.books_id = books.id
475JOIN genres ON genres_books.genres_id = genres.id
476WHERE books.branches_id = 9
477GROUP BY books.Title;
478
479CREATE OR REPLACE VIEW Catalogue_Clamshell_Orchid AS
480SELECT books.Title, books.YearOfRelease, GROUP_CONCAT(authors.first_name,' ', authors.last_name) AS Authors, publishers.Name AS Publisher, GROUP_CONCAT(genres.Name) AS Genres FROM Books
481JOIN authors_books ON authors_books.books_id = books.id
482JOIN authors ON authors_books.authors_id  = authors.id 
483JOIN publishers ON books.publishers_id = publishers.id
484JOIN genres_books ON genres_books.books_id = books.id
485JOIN genres ON genres_books.genres_id = genres.id
486WHERE books.branches_id = 10
487GROUP BY books.Title;
488
489/*Tworzenie funkcji zliczającej ilość pracowników na danej pozycji*/
490DROP FUNCTION IF EXISTS employeeCounter;
491DELIMITER $$
492CREATE FUNCTION employeeCounter(position INT)
493RETURNS INT DETERMINISTIC
494RETURN (SELECT COUNT(employees.Positions_ID) as Number_of_employees
495        FROM employees
496        WHERE employees.Positions_ID = position
497        )
498$$
499DELIMITER ;
500/*Test funkcji*/
501SELECT employeeCounter(5) as 'Number of employees' ;
502
503/*Tworzenie procedury zwracającej książki dostępne w danej filli*/
504DROP PROCEDURE IF EXISTS availableBooks;
505DELIMITER $$ 
506CREATE PROCEDURE availableBooks(branch INT)
507BEGIN
508    SELECT DISTINCT books.ID, books.Title, books.YearOfRelease
509    FROM books
510    JOIN rents ON (rents.books_id = books.ID)
511    WHERE books.Branches_ID = branch
512    AND rents.rent_status_id <> 1 AND rents.rent_status_id <> 3 ;
513END
514$$
515DELIMITER ;     
516call availableBooks(1);
517 
518/*Tworzenie funkcji zwracającej ilość aktywnych rezerwacji*/
519DROP FUNCTION IF EXISTS activeReservationCounter;
520DELIMITER $$
521CREATE FUNCTION activeReservationCounter(liblaryCard INT)
522RETURNS INT DETERMINISTIC
523RETURN (SELECT COUNT(reservations.ID)
524        FROM reservations
525        WHERE reservations.reservation_status_id IN (1,2)
526        AND reservations.library_cards_id = liblaryCard
527        )
528$$
529DELIMITER ;   
530SELECT activeReservationCounter(4) as 'Number of active reservations' ;
531
532/*Przykładowa transakcja, usunięcie książki wymaga usunięcia wszystkich powiązań z nią w innych tabelach*/
533START TRANSACTION;
534SELECT * FROM reservations;
535DELETE FROM Rents WHERE books_id = 1;
536DELETE FROM Reservations WHERE books_id = 1;
537DELETE FROM Authors_Books WHERE books_id = 1;
538DELETE FROM Genres_Books WHERE books_id = 1;
539DELETE FROM Books WHERE id = 1;
540COMMIT;
541SELECT * FROM books;