· 4 years ago · Jun 13, 2021, 05:30 PM
1DROP DATABASE IF EXISTS LIBRARY;
2CREATE DATABASE library;
3USE library;
4
5CREATE TABLE publisher (
6 name VARCHAR(50) NOT NULL,
7 address VARCHAR(100),
8 phone CHAR(11),
9
10 PRIMARY KEY(name)
11);
12
13CREATE TABLE books (
14 ISBN CHAR(13) NOT NULL,
15 title VARCHAR(50),
16 publisher VARCHAR(50),
17 year INT(4),
18 price INT(255),
19 category VARCHAR(10),
20 threshold INT(255),
21 quantity INT(255),
22
23 PRIMARY KEY(ISBN),
24 FOREIGN KEY(publisher) REFERENCES publisher(name),
25 CHECK (quantity >= 0),
26 CHECK (year >= 0 AND year <= 2022),
27 CHECK (category IN ("Science", "Art", "Religion", "History", "Geography"))
28);
29
30CREATE TABLE sales (
31 saleDate DATE NOT NULL,
32 ISBN CHAR(13) NOT NULL,
33 quantity INT(255),
34
35 FOREIGN KEY(ISBN) REFERENCES books(ISBN)
36);
37
38CREATE TABLE orders (
39 order_id INT(255) NOT NULL auto_increment,
40 ISBN CHAR(13),
41 quantity INT(255),
42
43 PRIMARY KEY(order_id),
44 FOREIGN KEY(ISBN) REFERENCES books(ISBN)
45);
46
47CREATE TABLE authors (
48 ISBN CHAR(13),
49 name VARCHAR(50),
50 FOREIGN KEY(ISBN) REFERENCES books(ISBN),
51 PRIMARY KEY(ISBN, name)
52);
53
54CREATE TABLE USER(
55 username VARCHAR(50),
56 credentials BIT NOT NULL,
57 password VARCHAR(50) NOT NULL,
58 first_name VARCHAR(50),
59 last_name VARCHAR(50),
60 email VARCHAR(300),
61 address VARCHAR(100),
62 phone CHAR(11),
63 purchase INT DEFAULT 0,
64 PRIMARY KEY(username)
65);
66
67CREATE TABLE CART(
68 username VARCHAR(50),
69 ISBN CHAR(13),
70 quantity INT,
71
72 PRIMARY KEY(username,ISBN),
73 FOREIGN KEY(ISBN) REFERENCES books(ISBN),
74 FOREIGN KEY (username) REFERENCES USER(username),
75
76 CHECK (quantity >= 0)
77);
78
79DELIMITER #
80CREATE TRIGGER BooksSold
81 AFTER UPDATE ON books
82 FOR EACH ROW
83BEGIN
84 IF NEW.quantity < OLD.threshold THEN
85 INSERT INTO orders(ISBN, quantity) VALUES(OLD.ISBN, OLD.threshold-NEW.quantity);
86 END IF;
87END;#
88
89CREATE PROCEDURE InsertBook (
90 IN ISBN CHAR(13),
91 IN title VARCHAR(50),
92 IN author VARCHAR(50),
93 IN publisher VARCHAR(50),
94 IN year INT(4),
95 IN price INT(255),
96 IN category VARCHAR(10),
97 IN threshold INT(255),
98 IN quantity INT(255)
99)
100BEGIN
101 INSERT INTO books VALUES(ISBN, title, publisher, year, price, category, threshold, quantity);
102 INSERT INTO authors VALUES(ISBN, author);
103END;#
104
105CREATE PROCEDURE AlterBook (
106 IN ISBN CHAR(13),
107 IN title VARCHAR(50),
108 IN author VARCHAR(50),
109 IN publisher VARCHAR(50),
110 IN year INT(4),
111 IN price INT(255),
112 IN category VARCHAR(10),
113 IN threshold INT(255),
114 IN quantity INT(255)
115)
116BEGIN
117 SELECT * FROM books WHERE books.ISBN = ISBN;
118 UPDATE books SET books.title = title , books.publisher = publisher, books.year = year, books.price = price,
119 books.category = category, books.threshold = threshold, books.quantity = quantity
120 WHERE books.ISBN = ISBN;
121END;#
122
123
124CREATE PROCEDURE AddAuthor (IN ISBN CHAR(13), IN author VARCHAR(50))
125BEGIN
126 INSERT INTO authors VALUES(ISBN, author);
127END;#
128
129CREATE PROCEDURE InsertPublisher (IN name VARCHAR(50), IN address VARCHAR(100), IN phone CHAR(11))
130BEGIN
131 INSERT INTO publisher VALUES(name, address, phone);
132END;#
133
134CREATE PROCEDURE SellBooks (IN isbn CHAR(13), IN quan INT(255))
135BEGIN
136 UPDATE books SET books.quantity=books.quantity-quan WHERE books.ISBN=isbn;
137
138 INSERT INTO sales
139 SELECT d.saleDate, b.ISBN, q.quan
140 FROM (SELECT CURDATE() as saleDate) as d
141 CROSS JOIN books as b
142 CROSS JOIN (SELECT quan) as q
143 WHERE b.ISBN=isbn;
144END;#
145
146CREATE PROCEDURE SalesSinceDate (IN d DATE)
147BEGIN
148 SELECT * FROM sales WHERE saleDate >= d;
149END;#
150
151CREATE PROCEDURE AddOrder (IN isbn CHAR(13), IN quantity INT(255))
152BEGIN
153 INSERT INTO orders (ISBN, quantity) VALUES(isbn, quantity);
154END;#
155
156CREATE PROCEDURE ConfirmOrder (IN id CHAR(13))
157BEGIN
158 UPDATE books AS b JOIN
159 ( select * from orders where order_id=id) as o
160 ON b.ISBN = o.ISBN
161 SET b.quantity=b.quantity+o.quantity;
162
163 DELETE FROM orders WHERE order_id=id;
164END;#
165
166CREATE PROCEDURE SearchForISBN (IN isbn CHAR(13))
167BEGIN
168 SELECT * FROM books WHERE books.ISBN=isbn;
169END;#
170
171CREATE PROCEDURE SearchForTitle (IN title VARCHAR(50))
172BEGIN
173 SELECT * FROM books WHERE books.title=title;
174END;#
175
176CREATE PROCEDURE SearchForAuthor (IN author VARCHAR(50))
177BEGIN
178 SELECT * FROM books WHERE books.ISBN IN (
179 SELECT ISBN FROM authors WHERE authors.name=author
180 );
181END;#
182
183CREATE PROCEDURE SearchForPublisher (IN publisher VARCHAR(50))
184BEGIN
185 SELECT * FROM books WHERE books.publisher=publisher;
186END;#
187
188CREATE PROCEDURE SearchForYear (IN year INT(4))
189BEGIN
190 SELECT * FROM books WHERE books.year=year;
191END;#
192
193CREATE PROCEDURE SearchForPrice (IN price INT(255))
194BEGIN
195 SELECT * FROM books WHERE books.price=price;
196END;#
197
198CREATE PROCEDURE SearchForCategory (IN category VARCHAR(10))
199BEGIN
200 SELECT * FROM books WHERE books.category=category;
201END;#
202
203CREATE procedure LogIn(IN username VARCHAR(50), IN password VARCHAR(50))
204BEGIN
205 SELECT *
206 FROM USER
207 WHERE USER.username = username AND USER.password = password;
208END;#
209
210CREATE procedure Logout(IN username VARCHAR(50))
211BEGIN
212 DELETE FROM CART
213 WHERE CART.username = username;
214END;#
215
216CREATE procedure AddUser(
217 IN username VARCHAR(50),
218 IN credentials BIT,
219 IN password VARCHAR(50),
220 IN first_name VARCHAR(50),
221 IN last_name VARCHAR(50),
222 IN email VARCHAR(300),
223 IN address VARCHAR(100),
224 IN phone CHAR(11)
225)
226BEGIN
227 INSERT INTO USER VALUES(username, credentials, password, first_name, last_name, email, address, phone, 0);
228END;#
229
230CREATE procedure UpdateUser(
231 IN username VARCHAR(50),
232 IN password VARCHAR(50),
233 IN first_name VARCHAR(50),
234 IN last_name VARCHAR(50),
235 IN email VARCHAR(300),
236 IN address VARCHAR(100),
237 IN phone CHAR(11)
238)
239BEGIN
240 UPDATE USER
241 SET USER.password = password, USER.first_name = first_name, USER.last_name = last_name,
242 USER.email = email, USER.address = address, USER.phone = phone
243 WHERE USER.username = username;
244END;#
245
246CREATE procedure AddItem(IN username VARCHAR(50), IN isbn CHAR(13), IN quantity INT)
247BEGIN
248 IF EXISTS (SELECT * FROM CART WHERE CART.username = username AND CART.isbn = isbn) THEN
249 UPDATE CART
250 SET CART.quantity = CART.quantity + quantity
251 WHERE CART.username = username AND CART.isbn = isbn;
252 ELSE
253 INSERT INTO CART
254 VALUES (username, isbn, quantity);
255 END IF;
256END;#
257
258CREATE procedure UpdateItem(IN username VARCHAR(50), IN isbn CHAR(13), IN quant INT)
259BEGIN
260 IF quant <> 0 THEN
261 UPDATE CART
262 SET CART.quantity = quant
263 WHERE CART.username = username AND CART.ISBN = isbn;
264 ELSE
265 DELETE FROM CART
266 WHERE CART.username = username AND CART.ISBN = isbn;
267 END IF;
268END;#
269
270CREATE procedure RemoveItem(IN username VARCHAR(50), IN isbn CHAR(13), IN quantity INT)
271BEGIN
272 UPDATE CART
273 SET CART.quantity = CART.quantity - quantity
274 WHERE CART.username = username AND CART.isbn = isbn;
275
276 DELETE FROM CART
277 WHERE CART.quantity = 0;
278END;#
279
280CREATE procedure viewItems(IN username VARCHAR(50))
281BEGIN
282 SELECT CART.ISBN, title, publisher, year , price, category, threshold, CART.quantity AS CartQuantity, books.quantity AS quantity
283 FROM CART JOIN books ON CART.ISBN = books.ISBN
284 WHERE CART.username = username;
285END;#
286
287CREATE procedure viewItemsPrices(IN username VARCHAR(50))
288BEGIN
289 SELECT CART.ISBN, CART.quantity, CART.quantity * books.price
290 FROM (CART JOIN books ON CART.ISBN = books.ISBN)
291 WHERE CART.username = username;
292END;#
293
294CREATE procedure totalPrice(IN username VARCHAR(50))
295BEGIN
296 SELECT COALESCE(SUM(CART.quantity * books.price), 0) AS totalPrice
297 FROM (CART JOIN books ON CART.ISBN = books.ISBN)
298 WHERE CART.username = username;
299END;#
300
301CREATE procedure Promote(IN username VARCHAR(50))
302BEGIN
303 UPDATE USER
304 SET USER.credentials = 1
305 WHERE USER.username = username;
306END;#
307
308CREATE procedure Checkout(IN username VARCHAR(50))
309BEGIN
310 START TRANSACTION;
311 update books
312 set books.quantity = books.quantity - (
313 SELECT COALESCE(CART.quantity, 0)
314 FROM CART
315 where CART.isbn =books.isbn AND CART.username = username
316 )
317 WHERE exists (SELECT * FROM CART WHERE CART.isbn =books.isbn AND CART.username = username);
318
319 Insert into sales
320 SELECT d.saleDate, b.isbn, b.quantity
321 FROM (SELECT CURDATE() as saleDate) as d
322 CROSS JOIN (
323 SELECT isbn ,quantity
324 FROM CART
325 where CART.username = username) as b;
326
327 Update USER
328 SET USER.purchase = USER.purchase + (
329 SELECT COALESCE(SUM(CART.quantity * books.price), 0)
330 FROM (CART JOIN books ON CART.ISBN = books.ISBN)
331 WHERE CART.username = username
332 )
333 WHERE USER.username = username;
334
335 DELETE FROM CART
336 WHERE CART.username = username;
337 COMMIT;
338END;#
339
340CREATE procedure TopCustomers()
341BEGIN
342 SELECT USER.username
343 FROM USER
344 ORDER BY USER.purchase DESC
345 LIMIT 5;
346END;#
347
348DELIMITER ;
349
350call AddUser(
351 "Kareem",
352 1,
353 "12345",
354 "Kareem",
355 "Elhawaty",
356 "ka@gmail.com",
357 "address",
358 "0122"
359 );
360
361call AddUser(
362 "Hazem",
363 0,
364 "12345",
365 "Hazem",
366 "Shawkey",
367 "ha@gmail.com",
368 "address",
369 "0111"
370 );
371
372call InsertPublisher ("HBD.com", "Alexandria University", "999");
373call InsertPublisher ("D7k", "Alexandria University", "988");
374call InsertPublisher ("Penguin", "Alexandria University", "988");
375call InsertPublisher ("Random", "Alexandria University", "988");