hXGayAmu

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);
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
168DELIMITER $$
169CREATE TRIGGER ADD_ORDER
170    AFTER UPDATE
171    ON BOOK
172    FOR EACH ROW
173    BEGIN
174      IF NEW.QUANTITY < NEW.THRESHOLD
175        THEN
176           INSERT INTO ORDER_INFO (ISBN,QUANTITY) VALUES (NEW.ISBN,10*NEW.THRESHOLD);
177        END IF;
178END $$