-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathProject.sql
263 lines (235 loc) · 12.9 KB
/
Project.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
/*
Kyle Brost
Cameron Gera
CIS4301
Project
We are working in MySQL for this project (MySQL does not support assertions or
checks)
We've taken a slightly different approach to the traditional online store
model - instead of selling individual products, we've instead opted to sell
monthly subscription services, watch bands in particular. As seen in
'Product', there are three levels and four term lenghts for each. Order
quantity shouldn't exceed 1 typically, but it is allowed to.
Our online store sells watch band subscriptions, where each level mails a
different manufacturer's watch band monthly.
*/
/*
+-----------------------+
| Tables_in_OnlineStore |
+-----------------------+
| AnOrder |
| Product |
| Supplier |
| User |
+-----------------------+
-- AnOrder --
+-------+---------------------+-------------+----------+------+----------+---------------------+---------------------+
| id | date_bought | cur_product | cur_user | paid | quantity | createdAt | updatedAt |
+-------+---------------------+-------------+----------+------+----------+---------------------+---------------------+
| 10000 | 2015-11-20 11:18:26 | 20000 | 40000 | 1 | 1 | 2015-11-20 11:18:26 | 0000-00-00 00:00:00 |
| 10001 | 2015-11-20 11:18:26 | 20003 | 40001 | 1 | 4 | 2015-11-20 11:18:26 | 0000-00-00 00:00:00 |
| 10002 | 2015-11-20 11:18:26 | 20007 | 40002 | 1 | 2 | 2015-11-20 11:18:26 | 0000-00-00 00:00:00 |
| 10003 | 2015-11-20 11:18:27 | 20010 | 40003 | 1 | 3 | 2015-11-20 11:18:27 | 0000-00-00 00:00:00 |
+-------+---------------------+-------------+----------+------+----------+---------------------+---------------------+
-- Product --
+-------+----------+-------+-------+-----------------+--------+----------+---------------------+---------------------+
| id | name | price | stock | description | active | supplier | createdAt | updatedAt |
+-------+----------+-------+-------+-----------------+--------+----------+---------------------+---------------------+
| 20000 | Basic 1 | 5 | 99 | 1 month basic | 1 | 30000 | 2015-11-20 11:18:20 | 2015-11-20 11:18:26 |
| 20001 | Basic 2 | 14 | 100 | 3 month basic | 1 | 30000 | 2015-11-20 11:18:20 | 0000-00-00 00:00:00 |
| 20002 | Basic 3 | 28 | 100 | 6 month basic | 1 | 30000 | 2015-11-20 11:18:20 | 0000-00-00 00:00:00 |
| 20003 | Basic 4 | 55 | 96 | 12 month basic | 1 | 30000 | 2015-11-20 11:18:20 | 2015-11-20 11:18:26 |
| 20004 | Silver 1 | 10 | 50 | 1 month silver | 1 | 30001 | 2015-11-20 11:18:20 | 0000-00-00 00:00:00 |
| 20005 | Silver 2 | 28 | 50 | 3 month silver | 1 | 30001 | 2015-11-20 11:18:20 | 0000-00-00 00:00:00 |
| 20006 | Silver 3 | 50 | 50 | 6 month silver | 1 | 30001 | 2015-11-20 11:18:20 | 0000-00-00 00:00:00 |
| 20007 | Silver 4 | 99 | 48 | 12 month silver | 1 | 30001 | 2015-11-20 11:18:20 | 2015-11-20 11:18:26 |
| 20008 | Gold 1 | 20 | 25 | 1 month gold | 1 | 30002 | 2015-11-20 11:18:20 | 0000-00-00 00:00:00 |
| 20009 | Gold 2 | 58 | 25 | 3 month gold | 1 | 30002 | 2015-11-20 11:18:20 | 0000-00-00 00:00:00 |
| 20010 | Gold 3 | 110 | 22 | 6 month gold | 1 | 30002 | 2015-11-20 11:18:20 | 2015-11-20 11:18:27 |
| 20011 | Gold 4 | 210 | 25 | 12 month gold | 1 | 30002 | 2015-11-20 11:18:21 | 0000-00-00 00:00:00 |
+-------+----------+-------+-------+-----------------+--------+----------+---------------------+---------------------+
-- Supplier --
+-------+---------+---------------------+---------------------+
| id | name | createdAt | updatedAt |
+-------+---------+---------------------+---------------------+
| 30000 | Apple | 2015-11-20 11:17:42 | 0000-00-00 00:00:00 |
| 30001 | Samsung | 2015-11-20 11:17:42 | 0000-00-00 00:00:00 |
| 30002 | Fossil | 2015-11-20 11:17:43 | 0000-00-00 00:00:00 |
+-------+---------+---------------------+---------------------+
-- User --
+-------+-------------+-------------------+-------------------+--------------------+----------+---------------------+---------------------+
| id | address | name | password | email | is_staff | createdAt | updatedAt |
+-------+-------------+-------------------+-------------------+--------------------+----------+---------------------+---------------------+
| 40000 | 123 Main St | Kyle Brost | mypassword | broskj@email.com | 1 | 2015-11-20 11:17:37 | 0000-00-00 00:00:00 |
| 40001 | 456 1st Ave | Cameron Gera | hispassword | gerac@email.com | 1 | 2015-11-20 11:17:37 | 0000-00-00 00:00:00 |
| 40002 | 789 2nd Ln | John Doe | johndoe123 | doejohn@email.com | 0 | 2015-11-20 11:17:37 | 0000-00-00 00:00:00 |
| 40003 | 234 3rd Ave | Jane Doe | password123 | doejane@email.com | 0 | 2015-11-20 11:17:37 | 0000-00-00 00:00:00 |
| 40004 | 567 4th St | George Washington | washington456 | gwash@email.com | 0 | 2015-11-20 11:17:37 | 0000-00-00 00:00:00 |
| 40005 | 953 9th Rd | Teddy Roosevelt | teddyteddy2 | teddyr@email.com | 0 | 2015-11-20 11:17:37 | 0000-00-00 00:00:00 |
| 40006 | 815 7th St | Bill Clinton | lewinskylessthan3 | bclinton@email.com | 0 | 2015-11-20 11:17:38 | 0000-00-00 00:00:00 |
+-------+-------------+-------------------+-------------------+--------------------+----------+---------------------+---------------------+
*/
-- create tables --
# order table
CREATE TABLE AnOrder (
id INT NOT NULL AUTO_INCREMENT,
date_bought timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
cur_product INT REFERENCES Product.id,
cur_user INT REFERENCES User.id,
paid BOOLEAN,
quantity INT,
createdAt timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
updatedAt timestamp ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
# product table
CREATE TABLE Product (
id INT NOT NULL AUTO_INCREMENT,
name CHAR(20),
price REAL,
stock INT,
description CHAR(100),
active BOOLEAN,
supplier INT REFERENCES Supplier.id,
createdAt timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
updatedAt timestamp ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
# supplier table
CREATE TABLE Supplier (
id INT NOT NULL AUTO_INCREMENT,
name CHAR(20),
createdAt timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
updatedAt timestamp ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
# user table
CREATE TABLE User (
id INT NOT NULL AUTO_INCREMENT,
address CHAR(20),
name CHAR(20),
password CHAR(20),
email CHAR(40) UNIQUE,
is_staff BOOLEAN,
createdAt timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
updatedAt timestamp ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
# Table alterations that are required
ALTER TABLE AnOrder AUTO_INCREMENT = 10000;
ALTER TABLE Product AUTO_INCREMENT = 20000;
ALTER TABLE Supplier AUTO_INCREMENT = 30000;
ALTER TABLE User AUTO_INCREMENT = 40000;
-- add initial data --
/*
# contains orders placed
INSERT INTO AnOrder (cur_product, cur_user, paid, quantity) VALUES (20000, 40000, TRUE, 1);
INSERT INTO AnOrder (cur_product, cur_user, paid, quantity) VALUES (20003, 40001, TRUE, 4);
INSERT INTO AnOrder (cur_product, cur_user, paid, quantity) VALUES (20007, 40002, TRUE, 2);
INSERT INTO AnOrder (cur_product, cur_user, paid, quantity) VALUES (20010, 40003, TRUE, 3);
# contains all products offered
INSERT INTO Product (name, price, stock, description, active, supplier) VALUES ('Basic 1', 5.00, 100, 'This is a 1 month subscription to our basic model of watch bands. This supplier is Apple.', TRUE, 30000);
INSERT INTO Product (name, price, stock, description, active, supplier) VALUES ('Basic 2', 14.00, 100, 'This is a 3 month subscription to our basic model of watch bands. This supplier is Apple.', TRUE, 30000);
INSERT INTO Product (name, price, stock, description, active, supplier) VALUES ('Basic 3', 28.00, 100, 'This is a 6 month subscription to our basic model of watch bands. This supplier is Apple.', TRUE, 30000);
INSERT INTO Product (name, price, stock, description, active, supplier) VALUES ('Basic 4', 55.00, 100, 'This is a 12 month subscription to our basic model of watch bands. This supplier is Apple.', TRUE, 30000);
INSERT INTO Product (name, price, stock, description, active, supplier) VALUES ('Silver 1', 10.00, 50, 'This is a 1 month subscription to our silver model of watch bands. This supplier is Fossil.', TRUE, 30001);
INSERT INTO Product (name, price, stock, description, active, supplier) VALUES ('Silver 2', 28.00, 50, 'This is a 3 month subscription to our silver model of watch bands. This supplier is Fossil.', TRUE, 30001);
INSERT INTO Product (name, price, stock, description, active, supplier) VALUES ('Silver 3', 50.00, 50, 'This is a 6 month subscription to our silver model of watch bands. This supplier is Fossil.', TRUE, 30001);
INSERT INTO Product (name, price, stock, description, active, supplier) VALUES ('Silver 4', 99.00, 50, 'This is a 12 month subscription to our silver model of watch bands. This supplier is Fossil.', TRUE, 30001);
INSERT INTO Product (name, price, stock, description, active, supplier) VALUES ('Gold 1', 20.00, 25, 'This is a 1 month subscription to our gold model of watch bands. This supplier is Samsung.', TRUE, 30002);
INSERT INTO Product (name, price, stock, description, active, supplier) VALUES ('Gold 2', 58.00, 25, 'This is a 3 month subscription to our gold model of watch bands. This supplier is Samsung.', TRUE, 30002);
INSERT INTO Product (name, price, stock, description, active, supplier) VALUES ('Gold 3', 110.00, 25, 'This is a 6 month subscription to our gold model of watch bands. This supplier is Samsung.', TRUE, 30002);
INSERT INTO Product (name, price, stock, description, active, supplier) VALUES ('Gold 4', 210.00, 25, 'This is a 12 month subscription to our gold model of watch bands. This supplier is Samsung.', TRUE, 30002);
# contains all suppliers
INSERT INTO Supplier (name) VALUES ('Apple');
INSERT INTO Supplier (name) VALUES ('Samsung');
INSERT INTO Supplier (name) VALUES ('Fossil');
# contains all user information
INSERT INTO User (address, name, password, email, is_staff) VALUES ('123 Main St', 'Kyle Brost', 'mypassword', 'broskj@email.com', TRUE);
INSERT INTO User (address, name, password, email, is_staff) VALUES ('456 1st Ave', 'Cameron Gera', 'hispassword', 'gerac@email.com', TRUE);
INSERT INTO User (address, name, password, email, is_staff) VALUES ('789 2nd Ln', 'John Doe', 'johndoe123', 'doejohn@email.com', FALSE);
INSERT INTO User (address, name, password, email, is_staff) VALUES ('234 3rd Ave', 'Jane Doe', 'password123', 'doejane@email.com', FALSE);
INSERT INTO User (address, name, password, email, is_staff) VALUES ('567 4th St', 'George Washington', 'washington456', 'gwash@email.com', FALSE);
INSERT INTO User (address, name, password, email, is_staff) VALUES ('953 9th Rd', 'Teddy Roosevelt', 'teddyteddy2', 'teddyr@email.com', FALSE);
INSERT INTO User (address, name, password, email, is_staff) VALUES ('815 7th St', 'Bill Clinton', 'lewinskylessthan3', 'bclinton@email.com', FALSE);
*/
/*
prevent negative quantity
prevent quantity > stock
prevent ordering inactive product (stock = 0)
*/
DELIMITER //
CREATE TRIGGER beforeNewOrder
BEFORE INSERT ON AnOrder
FOR EACH ROW BEGIN
IF(NEW.quantity) <= 0 THEN SIGNAL SQLSTATE '22003' SET message_text = "ERROR: INVALID QUANTITY";
END IF;
IF(NEW.quantity) > (
SELECT stock
FROM Product
WHERE NEW.cur_product = id
) THEN
SIGNAL SQLSTATE '22003' SET message_text = "ERROR: QUANTITY > STOCK";
END IF;
IF (SELECT active
FROM Product
WHERE NEW.cur_product = id) = FALSE
THEN
SIGNAL SQLSTATE '22000' SET message_text = "ERROR: INACTIVE PRODUCT";
END IF;
END;
//
/*
decrease stock by quantity on order
change active to false on stock = 0
*/
CREATE TRIGGER onNewOrder
AFTER INSERT ON AnOrder
FOR EACH ROW BEGIN
UPDATE Product
SET stock = stock - NEW.quantity
WHERE NEW.cur_product = id
AND active = TRUE;
UPDATE Product
SET active = FALSE
WHERE stock = 0 AND active = TRUE;
END;
//
CREATE TRIGGER beforeUpdateOrder
BEFORE UPDATE ON AnOrder
FOR EACH ROW BEGIN
IF(NEW.quantity) <= 0 THEN
SIGNAL SQLSTATE '22003' SET message_text = "ERROR: INVALID QUANTITY";
END IF;
IF(NEW.quantity) > (
SELECT stock
FROM Product
WHERE NEW.cur_product = id
) THEN
SIGNAL SQLSTATE '22003' SET message_text = "ERROR: QUANTITY > STOCK";
END IF;
IF (SELECT active
FROM Product
WHERE NEW.cur_product = id) = FALSE
THEN
SIGNAL SQLSTATE '22000' SET message_text = "ERROR: INACTIVE PRODUCT";
END IF;
END;
//
/*
decrease stock by quantity on order
change active to false on stock = 0
*/
CREATE TRIGGER onUpdateOrder
AFTER UPDATE ON AnOrder
FOR EACH ROW BEGIN
UPDATE Product
SET stock = stock - NEW.quantity
WHERE NEW.cur_product = id
AND active = TRUE;
UPDATE Product
SET active = FALSE
WHERE stock = 0 AND active = TRUE;
END;
//
DELIMITER ;