-
Notifications
You must be signed in to change notification settings - Fork 2
/
Copy pathdump.sql
419 lines (369 loc) · 13.8 KB
/
dump.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
DROP TABLE IF EXISTS customer_payment_method;
DROP TABLE IF EXISTS store_order;
DROP TABLE IF EXISTS adds;
DROP TABLE IF EXISTS product_in_shopping_cart;
DROP TABLE IF EXISTS shopping_cart;
DROP TABLE IF EXISTS product;
DROP TABLE IF EXISTS category;
DROP TABLE IF EXISTS customer;
DROP TABLE IF EXISTS seller;
DROP TABLE IF EXISTS user;
CREATE TABLE user (
userid VARCHAR(20),
user_password CHAR(100) NOT NULL,
house_number INT,
street_name VARCHAR(100),
postal_code VARCHAR(20),
province VARCHAR(100),
city VARCHAR(100),
email VARCHAR(100),
PRIMARY KEY (userid)
);
CREATE TABLE customer (
id VARCHAR(20),
first_name VARCHAR(100),
last_name VARCHAR(100),
PRIMARY KEY (id),
FOREIGN KEY (id) REFERENCES user(userid)
ON DELETE CASCADE
ON UPDATE CASCADE
);
CREATE TABLE seller (
id VARCHAR(20),
company_name VARCHAR(100),
PRIMARY KEY (id),
FOREIGN KEY (id) REFERENCES user(userid)
ON DELETE CASCADE
ON UPDATE CASCADE
);
CREATE TABLE category (
category_name VARCHAR(100),
PRIMARY KEY (category_name)
);
CREATE TABLE product (
productid INT AUTO_INCREMENT,
stock INT,
product_description VARCHAR(1000),
best_before_date DATE NOT NULL,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2),
sellerid VARCHAR(20),
category_name CHAR(100),
PRIMARY KEY (productid),
FOREIGN KEY (sellerid) REFERENCES seller(id)
ON DELETE SET NULL
ON UPDATE CASCADE,
FOREIGN KEY (category_name) REFERENCES category(category_name)
ON DELETE SET NULL
ON UPDATE CASCADE
);
CREATE TABLE store_order (
customerid VARCHAR(20),
orderid INT,
cost DECIMAL(10, 2),
order_time DATE,
payment_method_used VARCHAR(100),
PRIMARY KEY (customerid, orderid),
FOREIGN KEY (customerid) REFERENCES customer(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
CREATE TABLE shopping_cart (
customerid VARCHAR(20),
cartid INT,
PRIMARY KEY (customerid, cartid),
FOREIGN KEY (customerid) REFERENCES customer(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
CREATE TABLE product_in_shopping_cart (
productid INT,
customerid VARCHAR(20),
cartid INT,
quantity INT,
PRIMARY KEY (productid, customerid, cartid),
FOREIGN KEY (productid) REFERENCES product(productid)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (customerid, cartid) REFERENCES shopping_cart(customerid, cartid)
ON DELETE CASCADE
ON UPDATE CASCADE
);
CREATE TABLE adds (
customerid VARCHAR(20),
productid INT,
PRIMARY KEY (customerid, productid),
FOREIGN KEY (customerid) REFERENCES customer(id)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (productid) REFERENCES product(productid)
ON DELETE CASCADE
ON UPDATE CASCADE
);
CREATE TABLE customer_payment_method (
customerid VARCHAR(20),
payment_method VARCHAR(100),
PRIMARY KEY (customerid, payment_method),
FOREIGN KEY (customerid) REFERENCES customer(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
DELIMITER $$
CREATE TRIGGER OrderIdGeneration BEFORE INSERT ON store_order
FOR EACH ROW
IF NEW.orderid IS NULL
THEN SET NEW.orderid = (
SELECT MAX(orderid)
FROM store_order) + 1;
END IF;$$
CREATE TRIGGER CartIdGeneration BEFORE INSERT ON shopping_cart
FOR EACH ROW
IF NEW.cartid IS NULL
THEN SET NEW.cartid = (
SELECT MAX(cartid)
FROM shopping_cart) + 1;
END IF;$$
CREATE TRIGGER `user_insert_constraints` BEFORE INSERT ON `user`
FOR EACH ROW
IF LENGTH(NEW.userid) < 1 OR LENGTH(NEW.userid) > 20
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'User ID must be between 1 and 20 characters!';
ELSEIF NEW.house_number < 0
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'House number cannot be less than zero!';
ELSEIF LENGTH(NEW.user_password) < 1 OR LENGTH(NEW.user_password) > 100
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Password must be between 1 and 100 characters!';
ELSEIF NEW.email NOT LIKE '%_@__%.__%'
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Invalid email format!';
END IF;$$
CREATE TRIGGER `user_update_constraints` BEFORE UPDATE ON `user`
FOR EACH ROW IF LENGTH(NEW.userid) < 1 OR LENGTH(NEW.userid) > 20
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'User ID must be between 1 and 20 characters!';
ELSEIF NEW.house_number < 0
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'House number cannot be less than zero!';
ELSEIF LENGTH(NEW.user_password) < 1 OR LENGTH(NEW.userid) > 20
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Password must be between 1 and 100 characters!';
ELSEIF NEW.email NOT LIKE '%_@__%.__%'
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Invalid email format!';
END IF;$$
CREATE TRIGGER `product_insert_constraints` BEFORE INSERT ON `product`
FOR EACH ROW IF NEW.productid < 0
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Product ID cannot be less than zero!';
ELSEIF NEW.stock < 0
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Stock cannot be less than zero!';
ELSEIF NEW.best_before_date <= CURRENT_DATE
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Product is expired!';
ELSEIF NEW.price < 0.0
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Price cannot be less than zero!';
END IF;$$
CREATE TRIGGER `product_update_constraints` BEFORE UPDATE ON `product`
FOR EACH ROW IF NEW.productid < 0
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Product ID cannot be less than zero!';
ELSEIF NEW.stock < 0
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Stock cannot be less than zero!';
ELSEIF NEW.best_before_date <= CURRENT_DATE
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Product is expired!';
ELSEIF NEW.price < 0.0
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Price cannot be less than zero!';
END IF;$$
CREATE TRIGGER `store_order_insert_constraints` BEFORE INSERT ON `store_order`
FOR EACH ROW IF NEW.orderid < 0
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Order ID cannot be less than zero!';
ELSEIF NEW.cost < 0.0
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Cost cannot be less than zero!';
ELSEIF NEW.order_time > CURRENT_DATE
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Date of order is later than today!';
END IF;$$
CREATE TRIGGER `store_order_update_constraints` BEFORE UPDATE ON `store_order`
FOR EACH ROW IF NEW.orderid < 0
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Order ID cannot be less than zero!';
ELSEIF NEW.cost < 0.0
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Cost cannot be less than zero!';
ELSEIF NEW.order_time > CURRENT_DATE
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Date of order is later than today!';
END IF;$$
CREATE TRIGGER `shopping_cart_update_constraint` BEFORE UPDATE ON `shopping_cart`
FOR EACH ROW IF NEW.cartid < 0
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Cart ID cannot be less than zero!';
END IF;$$
CREATE TRIGGER `shopping_cart_insert_constraint` BEFORE INSERT ON `shopping_cart`
FOR EACH ROW IF NEW.cartid < 0
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Cart ID cannot be less than zero!';
END IF;$$
CREATE TRIGGER `product_in_shopping_cart_insert_constraints` BEFORE INSERT ON `product_in_shopping_cart`
FOR EACH ROW IF NEW.quantity < 0
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Quantity cannot be less than zero!';
END IF;$$
CREATE TRIGGER `product_in_shopping_cart_update_constraints` BEFORE UPDATE ON `product_in_shopping_cart`
FOR EACH ROW IF NEW.quantity < 0
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Quantity cannot be less than zero!';
END IF;$$
CREATE TRIGGER `customer_payment_method_insert_constraints` BEFORE INSERT ON `customer_payment_method`
FOR EACH ROW IF LENGTH(NEW.payment_method) <= 0
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Payment method cannot be empty!';
END IF;$$
CREATE TRIGGER `customer_payment_method_update_constraints` BEFORE UPDATE ON `customer_payment_method`
FOR EACH ROW IF LENGTH(NEW.payment_method) <= 0
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Payment method cannot be empty!';
END IF;$$
DELIMITER ;
/*Populating the tables with tuples*/
/*User Table Entries*/
/* Y123, A123, H123, J123, and B123's passwords are the same as their usernames*/
/* do not log into sellers (all their passwords are "password")*/
INSERT INTO user VALUES
('Y123','sha256$lYs8Fdwv$9e7828f2e6ddd18714b9cfd4098fc3990aee3d36c32dcc31a646b09b07b86a1b', 1,'152 Street','V3X123','BC', 'Surrey', '[email protected]'),
('A123','sha256$R1CTQ4w4$4542c56c2a05c4acf8ce43666648d547859e27e795e5a8331b57186ce2e2248b', 2,'156 Street','V3X456','BC', 'Surrey', '[email protected]'),
('H123','sha256$39BV4fBA$3fdd20f8e4c31a391b13aa9b3edec5a464d31e8e0dfc6946e345b734a440adbe', 3,'158 Street','V3X789','BC', 'Surrey', '[email protected]'),
('J123','sha256$MCw1gqXD$1eafaa15d9310f6d9c43068a5f7ab71fc7a3a8ed902b963772779b2b661ba44d', 4,'160 Street','V3X101','BC', 'Surrey', '[email protected]'),
('B123','sha256$oUmFWhhQ$811206646627b55b2af00ece0f62218e8c2803f350605a7e4c5f28899257353e', 5,'162 Street','V3X111','BC', 'Surrey', '[email protected]'),
('Micheal101','sha256$oPFc4lyT$b55f31a01fd903698e30bd91d97ba65a8e00fb9944d6d895dde796f9b37b70c7', 6,'160 Street','V3X112','BC', 'Surrey', '[email protected]'),
('Jim123','sha256$oPFc4lyT$b55f31a01fd903698e30bd91d97ba65a8e00fb9944d6d895dde796f9b37b70c7', 7,'162 Street','V3X113','BC', 'Surrey', '[email protected]'),
('Dwight456','sha256$oPFc4lyT$b55f31a01fd903698e30bd91d97ba65a8e00fb9944d6d895dde796f9b37b70c7', 8,'164 Street','V3X114','BC', 'Surrey', '[email protected]'),
('Pam789','sha256$oPFc4lyT$b55f31a01fd903698e30bd91d97ba65a8e00fb9944d6d895dde796f9b37b70c7', 9,'166 Street','V3X115','BC', 'Surrey', '[email protected]'),
('Mose112','sha256$oPFc4lyT$b55f31a01fd903698e30bd91d97ba65a8e00fb9944d6d895dde796f9b37b70c7', 10,'168 Street','V3X116','BC', 'Surrey', '[email protected]');
/*User Table Entries*/
INSERT INTO customer VALUES
('Y123','Yogesh','Sonik'),
('A123','Andy','Lu'),
('H123','Hareet','Dhillon'),
('J123','Bob','HealthyGuy'),
('B123','Brendan','Saw');
/*Seller Table Entries*/
INSERT INTO seller VALUES
('Jim123','Company 1'),
('Dwight456','Dunder Mifflin'),
('Pam789','Company 2'),
('Micheal101','Company 3'),
('Mose112','Company 4');
/*Category Table Entries*/
INSERT INTO category VALUES
('Fruits'),
('Vegetables'),
('Dairy'),
('Meat'),
('Bread');
/*Product table entries*/
INSERT INTO product VALUES
(101,5,'A fresh bunch of Bananas.','2021-04-24','Banana',2.99,'Jim123','Fruits'),
(102,5,'A dozen fresh Apples.','2021-04-24','Apple',3.99,'Jim123','Fruits'),
(103,5,'A bunch of fresh Grapes.','2021-04-24','Grape',4.99,'Jim123','Fruits'),
(104,5,'A box of fresh Oranges.','2021-04-24','Orange',5.99,'Jim123','Fruits'),
(105,5,'A box of fresh Peaches.','2021-04-24','Peach',6.99,'Jim123','Fruits'),
(106,5,'A dozen fresh Beetroots.','2021-4-25','Beet',7.99,'Dwight456','Vegetables'),
(107,5,'A dozen fresh Carrots.','2021-4-25','Carrot',8.99,'Dwight456','Vegetables'),
(108,5,'A fresh bunch of Lettuce.','2021-4-25','Lettuce',9.99,'Dwight456','Vegetables'),
(109,5,'A fresh Cauliflower.','2021-4-25','Cauliflower',10.99,'Dwight456','Vegetables'),
(110,5,'Half-Dozen fresh Eggplant.','2021-4-25','Eggplant',11.99,'Dwight456','Vegetables'),
(111,5,'A fresh jug of Milk.','2021-4-26','Milk',12.99,'Pam789','Dairy'),
(112,5,'A fresh tub of Yogurt.','2021-4-26','Yogurt',13.99,'Pam789','Dairy'),
(113,5,'A fresh wheel of Cheese.','2021-4-26','Cheese',13.99,'Pam789','Dairy'),
(114,5,'A fresh tub of Ice Cream.','2021-4-26','Ice Cream',15.99,'Pam789','Dairy'),
(115,5,'A fresh jug of Skim Milk.','2021-4-26','Skim Milk',16.99,'Pam789','Dairy'),
(116,5,'A whole Chicken.','2021-4-27','Chicken',17.99,'Micheal101','Meat'),
(117,5,'A whole Turkey.','2021-4-27','Turkey',18.99,'Micheal101','Meat'),
(118,5,'A whole Salmon.','2021-4-27','Salmon',19.99,'Micheal101','Meat'),
(119,5,'A whole Steak.','2021-4-27','Steak',20.99,'Micheal101','Meat'),
(120,5,'A whole Goat.','2021-4-27','Goat',21.99,'Micheal101','Meat'),
(121,5,'A fresh loaf of Bread.','2021-4-28','Bread',2.99,'Mose112','Bread'),
(122,5,'A dozen fresh Cheese Buns.','2021-4-28','Cheese Buns',3.99,'Mose112','Bread'),
(123,5,'A dozen fresh Croissants.','2021-4-28','Croissant',4.99,'Mose112','Bread'),
(124,5,'A fresh loaf of Whole Wheat Bread.','2021-4-28','Whole Wheat Bread',5.99,'Mose112','Bread'),
(125,5,'A fresh loaf of Gluten Free Bread.','2021-4-28','Gluten Free Bread',6.99,'Mose112','Bread');
/*Store_order entries*/
INSERT INTO store_order VALUES
('Y123',100,2.99,'2021-2-20','Credit Card'),
('Y123',101,4.99,'2021-2-25','Credit Card'),
('Y123',102,3.99,'2021-2-26','Credit Card'),
('Y123',103,2.99,'2021-2-27','Credit Card'),
('A123',104,2.99,'2021-2-20','Credit Card'),
('A123',105,3.99,'2021-2-21','Credit Card'),
('A123',106,5.99,'2021-2-28','Credit Card'),
('H123',107,4.99,'2021-2-22','Debit Card'),
('H123',108,2.99,'2021-2-25','Debit Card'),
('J123',109,2.99,'2021-2-23','Debit Card'),
('J123',110,2.99,'2021-2-24','Debit Card'),
('B123',111,5.99,'2021-2-24','Debit Card'),
('B123',112,1.99,'2021-2-25','Debit Card');
/*Shopping_cart entries*/
INSERT INTO shopping_cart VALUES
('Y123',200),
('A123',201),
('H123',202),
('J123',203),
('B123',204);
/*product_in_shopping_cart*/
INSERT INTO product_in_shopping_cart VALUES
(101,'Y123',200,2),
(102,'A123',201,2),
(103,'H123',202,2),
(104,'J123',203,2),
(105,'B123',204,2);
/*Adds entries*/
INSERT INTO adds VALUES
('Y123', 101),
('A123',102),
('H123',103),
('J123',104),
('B123',105);
/*customer_payment_method entries*/
INSERT INTO customer_payment_method VAlUES
('Y123','Credit Card'),
('A123','Credit Card'),
('H123', 'Debit Card'),
('J123','Debit Card'),
('B123', 'Debit Card');