· 8 years ago · Jun 08, 2018, 01:16 PM
1DROP SCHEMA IF EXISTS BOOKSTORE;
2CREATE SCHEMA BOOKSTORE;
3USE BOOKSTORE;
4
5CREATE TABLE BOOK (
6 ISBN INTEGER,
7 TITLE VARCHAR(30) UNIQUE NOT NULL,
8 PUBLISHER_ID INT NOT NULL,
9 PRICE DOUBLE NOT NULL,
10 CATEGORY ENUM ('Science', 'Art', 'Religion', 'History', 'Geography'),
11 PUBLICATION_YEAR SMALLINT,
12 QUANTITY INTEGER UNSIGNED NOT NULL,
13 THRESHOLD INTEGER NOT NULL,
14
15 CONSTRAINT PRIMARY KEY (ISBN)
16);
17
18CREATE TABLE AUTHOR (
19 ISBN INTEGER,
20 AUTHOR_NAME VARCHAR(30),
21
22 CONSTRAINT PRIMARY KEY (ISBN, AUTHOR_NAME)
23);
24
25CREATE TABLE PUBLISHER (
26 PUBLISHER_ID INTEGER AUTO_INCREMENT,
27 PUBLISHER_NAME VARCHAR(30) UNIQUE NOT NULL,
28
29 CONSTRAINT PRIMARY KEY (PUBLISHER_ID)
30);
31
32CREATE TABLE PUBLISHER_PHONES (
33 PUBLISHER_ID INTEGER,
34 PHONE VARCHAR(11) UNIQUE NOT NULL,
35
36 CONSTRAINT PRIMARY KEY (PUBLISHER_ID, PHONE)
37);
38
39CREATE TABLE PUBLISHER_ADDRESSES (
40 PUBLISHER_ID INTEGER,
41 ADDRESS VARCHAR(30) NOT NULL,
42
43 CONSTRAINT PRIMARY KEY (PUBLISHER_ID, ADDRESS)
44);
45
46CREATE TABLE USER_INFO (
47 USER_ID INTEGER AUTO_INCREMENT,
48 USER_NAME VARCHAR(30) UNIQUE NOT NULL,
49 FNAME VARCHAR(30) NOT NULL,
50 LNAME VARCHAR(30) NOT NULL,
51 EMAIL VARCHAR(30) UNIQUE NOT NULL,
52 PHONE VARCHAR(11) UNIQUE NOT NULL,
53 SHIPPING_ADDRESS VARCHAR(30) NOT NULL,
54 IS_MANAGER BOOLEAN DEFAULT FALSE,
55 CONSTRAINT PRIMARY KEY (USER_ID)
56);
57
58CREATE TABLE ORDER_INFO (
59 ID INTEGER AUTO_INCREMENT,
60 ISBN INTEGER,
61 QUANTITY INTEGER NOT NULL,
62 CONSTRAINT PRIMARY KEY (ID)
63);
64
65CREATE TABLE PURCHASE (
66 PID INTEGER AUTO_INCREMENT,
67 USER_ID INTEGER NOT NULL,
68 PURCHASE_DATE TIMESTAMP NOT NULL,
69 CONSTRAINT PRIMARY KEY (PID)
70);
71
72CREATE TABLE PURCHASE_ITEM (
73 PID INTEGER,
74 ISBN INTEGER,
75 QUANTITY INTEGER UNSIGNED NOT NULL CHECK ( QUANTITY <= (SELECT BOOK.QUANTITY
76 FROM
77 BOOK
78 WHERE BOOK.ISBN = ISBN)),
79 BOOK_PRICE DOUBLE NOT NULL,
80 CONSTRAINT PRIMARY KEY (PID, ISBN)
81);
82
83ALTER TABLE BOOK
84 ADD FOREIGN KEY BOOK(PUBLISHER_ID) REFERENCES PUBLISHER (PUBLISHER_ID);
85
86ALTER TABLE AUTHOR
87 ADD FOREIGN KEY AUTHOR(ISBN) REFERENCES BOOK (ISBN)
88 ON DELETE CASCADE
89 ON UPDATE CASCADE;
90
91ALTER TABLE PUBLISHER_PHONES
92 ADD FOREIGN KEY PUBLISHER_PHONES(PUBLISHER_ID) REFERENCES PUBLISHER (PUBLISHER_ID);
93
94ALTER TABLE PUBLISHER_ADDRESSES
95 ADD FOREIGN KEY PUBLISHER_ADDRESSES(PUBLISHER_ID) REFERENCES PUBLISHER (PUBLISHER_ID);
96
97
98ALTER TABLE ORDER_INFO
99 ADD FOREIGN KEY ORDER_INFO(ISBN) REFERENCES BOOK (ISBN)
100 ON DELETE SET NULL;
101
102ALTER TABLE PURCHASE
103 ADD FOREIGN KEY PURCHASE(USER_ID) REFERENCES USER_INFO (USER_ID);
104
105ALTER TABLE PURCHASE
106 ADD FOREIGN KEY PURCHASE(USER_ID) REFERENCES USER_INFO (USER_ID);
107
108ALTER TABLE PURCHASE_ITEM
109 ADD FOREIGN KEY PURCHASE_ITEM(PID) REFERENCES PURCHASE (PID) ON DELETE CASCADE;
110
111ALTER TABLE PURCHASE_ITEM
112 ADD FOREIGN KEY PURCHASE_ITEM(ISBN) REFERENCES BOOK (ISBN);
113
114DELIMITER $$
115CREATE PROCEDURE REMOVE_OLD_PURCHASES()
116 BEGIN
117 DELETE FROM PURCHASE WHERE PURCHASE_DATE >= NOW() - INTERVAL 3 MONTH;
118DELIMITER ;
119
120
121DELIMITER $$
122CREATE PROCEDURE CONFIRM_ORDERS(IN ORDER_ID INTEGER)
123 BEGIN
124 DECLARE IMPORTS INTEGER;
125 DECLARE IMPORTS_ISBN INTEGER;
126 SELECT
127 QUANTITY,
128 ISBN
129 INTO IMPORTS, IMPORTS_ISBN
130 FROM ORDER_INFO
131 WHERE ID = ORDER_ID;
132 DELETE FROM ORDER_INFO
133 WHERE ID = ORDER_ID;
134 UPDATE BOOK
135 SET QUANTITY = QUANTITY + IMPORTS
136 WHERE ISBN = IMPORTS_ISBN;
137 END $$
138DELIMITER ;
139
140DELIMITER $$
141CREATE PROCEDURE WITHDRAWAL(IN ORDER_ID INTEGER)
142 BEGIN
143 DELETE FROM ORDER_INFO
144 WHERE ID = ORDER_ID;
145 END $$
146DELIMITER ;
147
148
149DELIMITER $$
150CREATE TRIGGER DECREASE_BOOK_QUANTITY
151 AFTER INSERT
152 ON PURCHASE_ITEM
153 FOR EACH ROW
154 BEGIN
155 DECLARE TO_BE_TAKEN INTEGER; -- QUANTITY ADDED TO PURCHASE
156 DECLARE BOOK_ISBN INTEGER; -- THE BOOK ADDED TO THE PURCHASE_ITEM
157 SELECT QUANTITY
158 INTO TO_BE_TAKEN
159 FROM DUAL;
160 SELECT ISBN
161 INTO BOOK_ISBN
162 FROM DUAL;
163 UPDATE BOOK
164 SET QUANTITY = QUANTITY - TO_BE_TAKEN
165 WHERE ISBN = BOOK_ISBN;
166 END $$
167
168
169
170-- DELIMITER $$
171-- CREATE TRIGGER ADD_PURCHASE AFTER UPDATE ON BOOK FOR EACH ROW
172-- BEGIN
173-- DECLARE PUR_NUM INT;
174-- SELECT `AUTO_INCREMENT` INTO PUR_NUM
175-- FROM INFORMATION_SCHEMA.TABLES
176-- WHERE TABLE_SCHEMA = 'BOOKSTORE'
177-- AND TABLE_NAME = 'PURCHASE';
178-- INSERT INTO PURCHASE_ITEM VALUES(PUR_NUM,NEW.ISBN,OLD.ISBN-NEW.ISBN,NEW.BOOK_PRICE);
179-- END $$
180-- DELIMITER ;
181
182-- CREATE TABLE PURCHASE_ITEM (
183-- PID INTEGER,
184-- ISBN INTEGER,
185-- QUANTITY INTEGER NOT NULL,
186-- BOOK_PRICE DOUBLE NOT NULL,
187--
188-- CONSTRAINT PRIMARY KEY (PID, ISBN)
189
190-- DELIMITER $$
191-- CREATE TRIGGER REJECT_PURCHASE
192-- BEFORE UPDATE
193-- ON BOOK
194-- FOR EACH ROW
195-- BEGIN
196-- DECLARE MESSAGE_TEXT VARCHAR(30);
197-- IF NEW.QUANTITY < 0
198-- THEN
199-- SET MESSAGE_TEXT = "BOOKS AMOUNT EXCEEDED";
200-- SIGNAL SQLSTATE '45000';
201-- END IF;
202-- END $$
203-- DELIMITER ;
204
205-- DELIMITER $$
206-- CREATE TRIGGER ADD_PURCHASE
207-- BEFORE INSERT
208-- ON PURCHASE_ITEM
209-- FOR EACH ROW
210-- BEGIN
211-- UPDATE BOOK
212-- SET QUANTITY = QUANTITY - NEW.QUANTITY
213-- WHERE BOOK.ISBN = NEW.ISBN;
214-- END $$
215-- DELIMITER ;