D5TNdMcy

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', 'Other'),
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);
71DROP TABLE IF EXISTS PURCHASE_ITEM;
72CREATE TABLE PURCHASE_ITEM (
73  PID        INTEGER,
74  ISBN       INTEGER,
75  QUANTITY   INTEGER UNSIGNED NOT NULL,
76  BOOK_PRICE DOUBLE           NOT NULL,
77  CONSTRAINT PRIMARY KEY (PID, ISBN)
78
79);
80
81ALTER TABLE BOOK
82  ADD FOREIGN KEY BOOK(PUBLISHER_ID) REFERENCES PUBLISHER (PUBLISHER_ID);
83
84ALTER TABLE AUTHOR
85  ADD FOREIGN KEY AUTHOR(ISBN) REFERENCES BOOK (ISBN)
86  ON DELETE CASCADE
87  ON UPDATE CASCADE;
88
89ALTER TABLE PUBLISHER_PHONES
90  ADD FOREIGN KEY PUBLISHER_PHONES(PUBLISHER_ID) REFERENCES PUBLISHER (PUBLISHER_ID);
91
92ALTER TABLE PUBLISHER_ADDRESSES
93  ADD FOREIGN KEY PUBLISHER_ADDRESSES(PUBLISHER_ID) REFERENCES PUBLISHER (PUBLISHER_ID);
94
95
96ALTER TABLE ORDER_INFO
97  ADD FOREIGN KEY ORDER_INFO(ISBN) REFERENCES BOOK (ISBN)
98  ON DELETE SET NULL;
99
100ALTER TABLE PURCHASE
101  ADD FOREIGN KEY PURCHASE(USER_ID) REFERENCES USER_INFO (USER_ID);
102
103ALTER TABLE PURCHASE_ITEM
104ADD CONSTRAINT FIRSTCONSTRAINT
105FOREIGN KEY PURCHASE_ITEM(PID) REFERENCES PURCHASE (PID)
106ON DELETE CASCADE;
107
108ALTER TABLE PURCHASE_ITEM
109ADD CONSTRAINT SECONDCONSTRAINT
110FOREIGN KEY PURCHASE_ITEM(ISBN) REFERENCES BOOK (ISBN);
111
112DELIMITER $$
113CREATE PROCEDURE REMOVE_OLD_PURCHASES()
114  BEGIN
115    DELETE FROM PURCHASE
116    WHERE PURCHASE_DATE < NOW() - INTERVAL 3 MONTH;
117  END $$
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
148DROP TRIGGER IF EXISTS DECREASE_BOOK_QUANTITY;
149DELIMITER $$
150CREATE TRIGGER DECREASE_BOOK_QUANTITY
151  BEFORE INSERT
152  ON PURCHASE_ITEM
153  FOR EACH ROW
154  BEGIN
155    DECLARE TO_BE_TAKEN INTEGER; -- QUANTITY ADDED TO PURCHASE
156	SELECT QUANTITY INTO TO_BE_TAKEN FROM BOOK WHERE ISBN = NEW.ISBN;
157    IF TO_BE_TAKEN < NEW.QUANTITY THEN
158		SIGNAL SQLSTATE '45000' 
159		SET MESSAGE_TEXT = "OUT OF STOCK";
160    ELSE
161	UPDATE BOOK SET QUANTITY = QUANTITY- NEW.QUANTITY WHERE ISBN = NEW.ISBN;
162    END IF;
163  END $$
164DELIMITER ;
165
166DELIMITER $$
167CREATE TRIGGER ADD_ORDER
168  AFTER UPDATE
169  ON BOOK
170  FOR EACH ROW
171  BEGIN
172    IF NEW.QUANTITY < NEW.THRESHOLD
173    THEN
174      INSERT INTO ORDER_INFO (ISBN, QUANTITY) VALUES (NEW.ISBN, 10 * NEW.THRESHOLD);
175    END IF;
176  END $$
177DELIMITER ;