· 9 years ago · Dec 02, 2016, 04:24 AM
1CREATE SCHEMA IF NOT EXISTS `CECSProject` DEFAULT CHARACTER SET utf8
2CREATE TABLE IF NOT EXISTS `CECSProject`.`Customers` (
3 `cid` INT NOT NULL,
4 `cname` VARCHAR(45) NULL,
5 `address` VARCHAR(45) NULL,
6 `age` INT NULL,
7 `income-level` VARCHAR(45) NULL,
8 `username` VARCHAR(20) NULL,
9 `password` VARCHAR(45) NULL,
10 `Customerscol` VARCHAR(45) NULL,
11 PRIMARY KEY (`cid`))
12ENGINE = InnoDB
13CREATE TABLE IF NOT EXISTS `CECSProject`.`Publisher` (
14 `publisherid` INT NOT NULL,
15 `name` VARCHAR(45) NULL,
16 `address` VARCHAR(45) NULL,
17 `discount` VARCHAR(45) NULL,
18 PRIMARY KEY (`publisherid`))
19ENGINE = InnoDB
20CREATE TABLE IF NOT EXISTS `CECSProject`.`Books` (
21 `isbn` INT NOT NULL,
22 `author` VARCHAR(45) NULL,
23 `qty_in_stock` INT NULL,
24 `price` INT NULL,
25 `cost` INT NULL,
26 `year_published` INT NULL,
27 `publisherid` INT NULL,
28 PRIMARY KEY (`isbn`),
29 INDEX `publisherid_idx` (`publisherid` ASC),
30 CONSTRAINT `publisherid`
31 FOREIGN KEY (`publisherid`)
32 REFERENCES `CECSProject`.`Publisher` (`publisherid`)
33 ON DELETE NO ACTION
34 ON UPDATE NO ACTION)
35ENGINE = InnoDB
36CREATE TABLE IF NOT EXISTS `CECSProject`.`Orders` (
37 `ordernum` INT GENERATED ALWAYS AS () VIRTUAL,
38 `cid` INT NULL,
39 `cardnum` INT NULL,
40 `cardmonth` INT NULL,
41 `cardyear` INT NULL,
42 `order_date` DATE NULL,
43 `ship_date` DATE NULL,
44 PRIMARY KEY (`ordernum`),
45 INDEX `cid_idx` (`cid` ASC),
46 CONSTRAINT `cid`
47 FOREIGN KEY (`cid`)
48 REFERENCES `CECSProject`.`Customers` (`cid`)
49 ON DELETE NO ACTION
50 ON UPDATE NO ACTION)
51ENGINE = InnoDB
52CREATE TABLE IF NOT EXISTS `CECSProject`.`OrderList` (
53 `ordernum` INT NOT NULL,
54 `isbn` INT NOT NULL,
55 `quantity` INT NULL,
56 PRIMARY KEY (`ordernum`, `isbn`),
57 INDEX `isbn_idx` (`isbn` ASC),
58 CONSTRAINT `ordernum`
59 FOREIGN KEY (`ordernum`)
60 REFERENCES `CECSProject`.`Orders` (`ordernum`)
61 ON DELETE NO ACTION
62 ON UPDATE NO ACTION,
63 CONSTRAINT `isbn`
64 FOREIGN KEY (`isbn`)
65 REFERENCES `CECSProject`.`Books` (`isbn`)
66 ON DELETE NO ACTION
67 ON UPDATE NO ACTION)
68ENGINE = InnoDB
69CREATE TABLE IF NOT EXISTS `CECSProject`.`StockManager` (
70 `isbn` INT NOT NULL,
71 `quantity` INT NULL,
72 PRIMARY KEY (`isbn`),
73 CONSTRAINT `isbn`
74 FOREIGN KEY (`isbn`)
75 REFERENCES `CECSProject`.`Books` (`isbn`)
76 ON DELETE NO ACTION
77 ON UPDATE NO ACTION)
78ENGINE = InnoDB
79INSERT INTO `CECSProject`.`Books` (`isbn`, `author`, `qty_in_stock`, `price`, `cost`, `year_published`, `publisherid`) VALUES (100001, 'JK Rowling', 20, 10, 10, 1990, 1000);
80INSERT INTO `CECSProject`.`Books` (`isbn`, `author`, `qty_in_stock`, `price`, `cost`, `year_published`, `publisherid`) VALUES (100002, 'John Doe', 100, 20, 15, 1995, 1001);
81INSERT INTO `CECSProject`.`Books` (`isbn`, `author`, `qty_in_stock`, `price`, `cost`, `year_published`, `publisherid`) VALUES (100003, 'Luke Noel', 50, 25, 9, 1996, 1002);
82INSERT INTO `CECSProject`.`Books` (`isbn`, `author`, `qty_in_stock`, `price`, `cost`, `year_published`, `publisherid`) VALUES (100004, 'John Foster', 75, 40, 30, 2015, 1003);
83INSERT INTO `CECSProject`.`Books` (`isbn`, `author`, `qty_in_stock`, `price`, `cost`, `year_published`, `publisherid`) VALUES (100005, 'Lamar Jackson', 200, 30, 12, 2016, 1004);
84INSERT INTO `CECSProject`.`Customers` (`cid`, `cname`, `address`, `age`, `income-level`, `username`, `password`) VALUES (1, 'Tim Jones', '100 Test Dr', 40, 'Low', 'tjones', 'password1');
85INSERT INTO `CECSProject`.`Customers` (`cid`, `cname`, `address`, `age`, `income-level`, `username`, `password`) VALUES (2, 'Luke Noel', '200 Port Road', 20, 'Low', 'lnoel', 'password2');
86INSERT INTO `CECSProject`.`Customers` (`cid`, `cname`, `address`, `age`, `income-level`, `username`, `password`) VALUES (3, 'James Ramsey', '50 Red Lane', 65, 'High', 'jramsey', 'password3');
87INSERT INTO `CECSProject`.`Customers` (`cid`, `cname`, `address`, `age`, `income-level`, `username`, `password`) VALUES (4, 'Mark Zuckerburg', '519 1st St', 29, 'High', 'mzuckerburg', 'password4');
88INSERT INTO `CECSProject`.`Customers` (`cid`, `cname`, `address`, `age`, `income-level`, `username`, `password`) VALUES (5, 'Bill Gates', '30 West Lane', 67, 'High', 'bgates', 'microsoft');
89INSERT INTO `CECSProject`.`OrderList` (`ordernum`, `isbn`, `quantity`) VALUES (1, 100001, 1);
90INSERT INTO `CECSProject`.`OrderList` (`ordernum`, `isbn`, `quantity`) VALUES (2, 100001, 12);
91INSERT INTO `CECSProject`.`OrderList` (`ordernum`, `isbn`, `quantity`) VALUES (3, 100003, 9);
92INSERT INTO `CECSProject`.`OrderList` (`ordernum`, `isbn`, `quantity`) VALUES (4, 100002, 2);
93INSERT INTO `CECSProject`.`OrderList` (`ordernum`, `isbn`, `quantity`) VALUES (5, 100003, 300);
94INSERT INTO `CECSProject`.`Orders` (`ordernum`, `cid`, `cardnum`, `cardmonth`, `cardyear`, `order_date`, `ship_date`) VALUES (1, 1, 123456789, 2, 2019, '2016-11-27', '2016-11-30');
95INSERT INTO `CECSProject`.`Orders` (`ordernum`, `cid`, `cardnum`, `cardmonth`, `cardyear`, `order_date`, `ship_date`) VALUES (2, 2, 222222222, 5, 2018, '2016-11-05', '2016-11-10');
96INSERT INTO `CECSProject`.`Orders` (`ordernum`, `cid`, `cardnum`, `cardmonth`, `cardyear`, `order_date`, `ship_date`) VALUES (3, 3, 333333333, 6, 2019, '2016-11-22', '2016-11-25');
97INSERT INTO `CECSProject`.`Orders` (`ordernum`, `cid`, `cardnum`, `cardmonth`, `cardyear`, `order_date`, `ship_date`) VALUES (4, 4, 444444444, 1, 2020, '2016-11-01', '2016-11-04');
98INSERT INTO `CECSProject`.`Orders` (`ordernum`, `cid`, `cardnum`, `cardmonth`, `cardyear`, `order_date`, `ship_date`) VALUES (5, 5, 999000111, 2, 2017, '2016-11-03', '2016-11-07');
99INSERT INTO `CECSProject`.`Publisher` (`publisherid`, `name`, `address`, `discount`) VALUES (1000, 'Penguin', '1000 Field Way', '5');
100INSERT INTO `CECSProject`.`Publisher` (`publisherid`, `name`, `address`, `discount`) VALUES (1001, 'Reading Rainbow', '1776 Patriotic Drive', '6');
101INSERT INTO `CECSProject`.`Publisher` (`publisherid`, `name`, `address`, `discount`) VALUES (1002, 'Times', '718 Adair Ave', '0');
102INSERT INTO `CECSProject`.`Publisher` (`publisherid`, `name`, `address`, `discount`) VALUES (1003, 'CIA', '200 Cardinal Road', '2');
103INSERT INTO `CECSProject`.`Publisher` (`publisherid`, `name`, `address`, `discount`) VALUES (1004, 'Apple', '51 Bird Lane', '10');
104INSERT INTO `CECSProject`.`StockManager` (`isbn`, `quantity`) VALUES (100001, 100);
105INSERT INTO `CECSProject`.`StockManager` (`isbn`, `quantity`) VALUES (100002, 532);
106INSERT INTO `CECSProject`.`StockManager` (`isbn`, `quantity`) VALUES (100003, 700);
107INSERT INTO `CECSProject`.`StockManager` (`isbn`, `quantity`) VALUES (100004, 90);
108INSERT INTO `CECSProject`.`StockManager` (`isbn`, `quantity`) VALUES (100005, 85);
109CREATE DEFINER = CURRENT_USER TRIGGER `CECSProject`.`OrderList_AFTER_INSERT` AFTER INSERT ON `OrderList` FOR EACH ROW
110
111BEGIN
112
113IF
114(
115(SELECT Books.quantity
116
117FROM Books
118
119WHERE Books.isbn = NEW.isbn) -
120(SELECT NEW.quantity
121FROM NEW)
122) > 0
123
124THEN
125
126BEGIN
127
128UPDATE Books
129
130SET Books.quantity =
131(SELECT Books.quantity
132
133FROM Books
134
135WHERE Books.isbn = NEW.isbn) -
136(SELECT NEW.quantity
137
138FROM NEW);
139
140END;
141
142ELSEIF NOT EXISTS
143(SELECT 1
144FROM StockManager
145
146WHERE StockManager.isbn = NEW.isbn)
147
148THEN
149
150INSERT INTO StockManager
151
152VALUES (NEW.isbn,
153(
154(SELECT Books.quantity
155
156FROM Books
157
158WHERE Books.isbn = NEW.isbn) -
159(SELECT NEW.quantity
160
161FROM NEW)
162) + 10);
163
164UPDATE Books
165
166SET
167Books.quantity = 0;
168
169ELSE
170
171UPDATE StockManager
172
173SET StockManager.quantity = (
174(SELECT StockManager.quantity
175
176FROM StockManager) +
177(SELECT NEW.quantity
178
179FROM NEW))
180
181WHERE StockManager.isbn = NEW.isbn;
182
183UPDATE Books
184
185SET
186Books.quantity = 0;
187
188End if;
189END
190
191
192
193INSERT INTO CustomerExpense(cid, total)
194SELECT Customer.cid
195FROM Customer
196UPDATE ce
197SET ce.total = ce2.summedTotal
198FROM CustomerExpense ce
199INNER JOIN (select Books.isbn, SUM(Books.price*OrderList.quantity) 'summedTotal'
200FROM Books, OrderList
201WHERE Books.isbn = OrderList.isbn and Order.ordernum = OrderList.ordernum
202GROUP BY Books.isbn) ce2 ON Order.cid = ce.cid
203CREATE DEFINER = CURRENT_USER TRIGGER `CECSProject`.`Orders_AFTER_INSERT` AFTER INSERT ON `Orders` FOR EACH ROW
204
205BEGIN
206
207
208IF EXISTS (
209SELECT 1 FROM CustomerExpense
210
211WHERE CustomerExpense.cid = NEW.cid
212) THEN
213
214
215UPDATE CustomerExpense ce
216
217INNER JOIN (select Books.isbn, SUM(Books.price*OrderList.quantity) 'summedTotal'
218
219FROM Books, OrderList
220
221WHERE Books.isbn = OrderList.isbn and NEW.ordernum = OrderList.ordernum
222
223GROUP BY Books.isbn) ce2 ON NEW.cid = ce.cid
224
225SET ce.total = ce2.summedTotal;
226
227ELSE
228
229INSERT INTO CustomerExpense
230
231VALUES ( (SELECT cid
232FROM Customer
233WHERE Customer.cid = NEW.cid));
234
235UPDATE CustomerExpense ce
236
237INNER JOIN
238
239(SELECT
240
241Books.isbn,
242
243SUM(Books.price * OrderList.quantity) 'summedTotal'
244
245FROM
246 Books, OrderList
247
248WHERE
249 Books.isbn = OrderList.isbn AND NEW.ordernum = OrderList.ordernum
250
251GROUP BY Books.isbn) ce2 ON NEW.cid = ce.cid
252SET
253
254ce.total = ce2.summedTotal;
255
256END if;
257
258END