XQ2K4eGU

1CREATE DATABASE IF NOT EXISTS PorkShop;
2USE PorkShop;
3
4create table if not exists customer (
5  customer_id INT NOT NULL AUTO_INCREMENT,
6  fname VARCHAR(30) NOT NULL,
7  lname VARCHAR(30) NOT NULL,
8  email VARCHAR(45) NOT NULL UNIQUE,
9  username VARCHAR(50) NOT NULL UNIQUE,
10  password VARCHAR(255) NOT NULL,
11  primary key (customer_id)
12);
13
14create table if not exists address (
15  address_id INT NOT NULL AUTO_INCREMENT,
16  customer_id INT NOT NULL,
17  address_line_1 VARCHAR(20) NOT NULL,
18  address_line_2 VARCHAR(20),
19  city VARCHAR(15) NOT NULL,
20  state VARCHAR(2) NOT NULL,
21  zipcode VARCHAR(5) NOT NULL,
22  primary key (address_id),
23  foreign key (customer_id) references customer(customer_id)
24);
25
26create table if not exists card (
27  payment_id INT NOT NULL AUTO_INCREMENT,
28  customer_id INT NOT NULL,
29  cc_holder_name VARCHAR(60) NOT NULL,
30  cc_num VARCHAR(16) NOT NULL,
31  cc_exp_month VARCHAR (2) NOT NULL,
32  cc_exp_year VARCHAR (2) NOT NULL,
33  cc_cvc VARCHAR(3) NOT NULL,
34  billing_addr_id INT,
35  primary key (payment_id),
36  foreign key (customer_id) references customer(customer_id),
37  foreign key (billing_addr_id) references address(address_id) ON DELETE SET NULL
38);
39
40create table if not exists orders (
41  order_id INT NOT NULL AUTO_INCREMENT,
42  customer_id INT NOT NULL,
43  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
44  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
45  sub_total DECIMAL(19, 2),
46  tax_amount DECIMAL(19, 2),
47  grand_total DECIMAL(19, 2),
48  status VARCHAR(10),
49  shipping_addr_id INT,
50  primary key (order_id),
51  foreign key (customer_id) references customer(customer_id),
52  foreign key (shipping_addr_id) references address(address_id)
53  ON DELETE SET NULL ON UPDATE CASCADE
54);
55
56
57create table if not exists payment_info (
58  payment_info_id INT NOT NULL AUTO_INCREMENT,
59  order_id INT NOT NULL,
60  cc_holder_name VARCHAR (60) NOT NULL,
61  cc_last_four VARCHAR(4) NOT NULL,
62  cc_exp_month VARCHAR(2) NOT NULL,
63  cc_exp_yr VARCHAR(2),
64  approval_status VARCHAR(10),
65  primary key (payment_info_id),
66  foreign key (order_id) references orders(order_id)
67);
68
69create table if not exists brand (
70  brand_id INT NOT NULL AUTO_INCREMENT,
71  brand_name VARCHAR(45) NOT NULL,
72  phone VARCHAR(11) NOT NULL,
73  primary key (brand_id)
74);
75
76create table if not exists category (
77  cat_id INT NOT NULL AUTO_INCREMENT,
78  cat_name VARCHAR(45) NOT NULL,
79  description VARCHAR(255) NOT NULL,
80  primary key (cat_id)
81);
82
83create table if not exists product (
84  product_id INT NOT NULL AUTO_INCREMENT,
85  product_name VARCHAR(100) NOT NULL,
86  product_desc VARCHAR(255),
87  product_price DECIMAL(19, 2) NOT NULL,
88  product_image VARCHAR(100),
89  cat_id INT NOT NULL,
90  brand_id INT NOT NULL,
91  primary key (product_id),
92  foreign key(cat_id) references category(cat_id),
93  foreign key(brand_id) references brand(brand_id)
94);
95
96create table if not exists order_product (
97  order_id INT NOT NULL,
98  product_id INT NOT NULL,
99  qty INT NOT NULL,
100  price DECIMAL (19, 2),
101  primary key (order_id, product_id)
102);
103
104-- add a condition, if qty > 0 in_stock == true
105create table if not exists inventory (
106  product_id INT NOT NULL,
107  qty INT NOT NULL,
108  in_stock BOOLEAN NOT NULL,
109  max_sale_qty INT NOT NULL,
110  min_sale_qty INT NOT NULL,
111  min_stock_threshold INT NOT NULL,
112  primary key (product_id)
113);
114
115DELIMITER $$ 
116CREATE TRIGGER `before_inventory_update`
117BEFORE UPDATE ON `inventory`
118 FOR EACH ROW BEGIN
119  declare msg varchar(128);
120  IF new.qty < 0 THEN
121    SET msg = concat('QuantityConstraintError: Trying to insert a negative value in qty: ', cast(new.qty as char));
122    signal sqlstate '45000' SET message_text = msg;
123   end IF
124END$$
125DELIMITER; 
126
127
128create table if not exists employee (
129  emp_id INT NOT NULL AUTO_INCREMENT,
130  fname VARCHAR(30) NOT NULL,
131  lname VARCHAR(30) NOT NULL,
132  email VARCHAR(255) NOT NULL,
133  type VARCHAR(10) NOT NULL,
134  username VARCHAR(50) NOT NULL UNIQUE,
135  password VARCHAR(255) NOT NULL,
136  primary key (emp_id)
137);
138
139create table if not exists order_change_log (
140  change_log_id INT NOT NULL AUTO_INCREMENT,
141  emp_id INT NOT NULL,
142  order_id INT NOT NULL,
143  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
144  description VARCHAR(255),
145  primary key (change_log_id),
146  foreign key (order_id) references orders(order_id),
147  foreign key (emp_id) references employee(emp_id)
148);
149
150-- Creating a view for the sales
151CREATE VIEW sales_info AS
152SELECT o.order_id, o.created_at AS order_date,
153p.product_id, p.qty, p.price, c.cat_name
154FROM orders AS o, order_product AS p, product AS pd, category AS c
155WHERE o.order_id = p.order_id AND p.product_id = pd.product_id
156AND pd.cat_id = c.cat_id ORDER BY order_id;
157
158
159-- insert into customer values(1, "Daniel", "Lawson", "daniellawson@mail.com", "dlawson87", "password");
160-- insert into customer values(2, "May", "Moody", "maymoody@ymail.com", "maymoody67", "password");
161-- insert into customer values(3, "Guy", "Bishop", "guybishop@mail.com", "guybishop88", "password");
162-- insert into customer values(4, "Alicia", "Sandoval", "aliciasando@mail.com", "lizzysandoval", "password");
163--
164-- insert into address values(1, 1, "1345 Stone St", "APT 657", "Minneapolis", "MN", "12345");
165-- insert into address values(2, 1, "2345 Henepin Ave", "Suite 345", "Minneapolis", "MN", "12354");
166-- insert into address values(3, 2, "12 SW Garcia Ave", "", "San Antonio", "TX", "78234");
167-- insert into address values(4, 3, "1234 Wayward Pines", "APT 324", "Mountain View", "CA", "98674");
168-- insert into address values(5, 4, "865 Heatherstone", "", "Orlando", "FL", "34567");
169-- insert into address values(6, 4, "1254 Canterbury Rd", "", "Austin", "TX", "75465");
170--
171-- insert into card values(1, 1, "Daniel Lawson", "1234567891011234", "09", "19", "123", 2);
172-- insert into card values(2, 1, "Daniel Lawson", "4952886449112142", "12", "20", "456", 2);
173-- insert into card values(3, 2, "May Moody", "5486572352459909", "01", "18", "765", 3);
174-- insert into card values(4, 3, "Guy Bishop", "1386194809291102", "10", "21", "342", 4);
175-- insert into card values(5, 4, "Alicia Sandoval", "4187632373022016", "11", "20", "787", 5);
176--
177-- -- insertion of brands
178-- insert into brand values(1, "Puppy Apparel Co.", "2025551234");
179-- insert into brand values(2, "Good Boy Food Factory", "2025559999");
180-- insert into brand values(3, "Pooch Accessories", "2025555678");
181--
182-- -- insertion into categories
183-- insert into category values(1, "Apparel", "Clothes and Costumes");
184-- insert into category values(2, "Toys", "Chewing Toys, bones, rubber balls");
185-- insert into category values(3, "Beds", "Beds of different sizes");
186-- insert into category values(4, "Food", "dry food, wet food, training snacks");
187-- insert into category values(5, "Accessories", "hats, leashes, collars, miscellaneous");
188--
189-- -- insert into products
190-- insert into product values(1, "Ewok Costume", "front paw makes your pup look like they're walking on two feet. Has Velcro.", 12.99, "ewok_costume.jpeg", 1, 1);
191-- insert into product values(2, "Reversible Pink Raincoat", "Has Velcro closure, water-resistant, pink color", 14.99, "reversible_pink.jpeg", 1, 1);
192-- insert into product values(3, "Yoda costume", "Has Velcro closure, water-resistant, leash port in back allows access to dog's collar", 19.99, "yoda_costume.jpeg", 1, 1);
193-- insert into product values(4, "Reversible Blue Raincoat", "Has Velcro closure, water-resistant, blue color", 14.99, "reversible_blue.jpeg", 1, 1);
194-- insert into product values(5, "Split Hood Parka", "Has Velcro closure, pleated t-body construction for improved fit", 29.99, "red_parka.jpeg", 1, 1);
195-- insert into product values(6, "Chewbacca Hoodie", "Easily slips over your dog's head. Features Chewbacca fur with hood and bandolier strap", 14.99, "chewie_costume.jpeg", 1, 1);
196-- insert into product values(7, "Beef Dry Food", "Grain-Free nutrition with no gluten ingredients for easier digestibility", 53.99, "dryfood1.jpeg", 4, 2);
197-- insert into product values(8,	"Salmon Dry Food", "Fresh salmon is always first for great taste. Made with whole foods for gentle digestion", 59.99, "dryfood2.jpeg", 4, 2);
198-- insert into product values(9,	"Chicken Dry Food", "Grain Free, Puppy Recipe Dry Dog Food from WholeHearted. Real chicken is the first ingredient", 24.99, "dryfood3.jpeg", 4, 2);
199-- insert into product values(10, "Beef Wet Food", "Case of 12, Ensures optimal nutrient absorption to keep your dog fit and healthy", 18.99, "wetfood1.jpeg", 4, 2);
200-- insert into product values(11, "Wet Food Pack", "Case of 12", 8.99, "wetfood2.jpeg", 4, 2);
201-- insert into product values(12, "Beef Canned Food", "Case of 12", 23.99, "wetfood3.jpeg", 4, 2);
202-- insert into product values(13, "Monster Ball", "Squeaks when played with. Made of latex", 6.99, "toy1.jpeg", 2, 3);
203-- insert into product values(14, "Teddy Bear", "Nontoxic, No messy filling", 10.99, "toy2.jpeg", 2, 3);
204-- insert into product values(15, "Chewie Bone", "Authentic STAR WARS themed dog toy. Soft plush material", 9.99, "toy3.jpeg", 2, 3);
205-- insert into product values(16, "SqueakAir Tennis Ball", "Non-abrasive tennis material will not wear down your dog's teeth", 10.99, "toy4.jpeg", 2, 3);
206-- insert into product values(17, "Yoda Plushie", "Encourages active play through crinkling and shaking", 13.99, "toy5.jpeg", 2, 3);
207-- insert into product values(18, "Knot Bone", "Nylabone Big Chews are so big and so strong that even your big dog cannot destroy them", 12.99, "toy6.jpeg", 2, 3);
208-- insert into product values(19, "Grey Orthopedic Bed", "Large Orthopedic Foam Filled Dog Bed from the Harmony Manhattan Lofts Collection", 44.99, "bed1.jpeg", 3, 3);
209-- insert into product values(20, "Medium Orthopedic Bed", "For medium breeds. Orthopedic foam helps ease joint pain", 49.99, "bed2.jpeg",	3, 3);
210-- insert into product values(21, "Small Gold Bed", "For small breeds. Cushion is filled with an orthopedic foam fill to provide hip and joint support and added comfort", 34.99, "bed3.jpeg", 3, 3);
211-- insert into product values(22, "Adjustable Leash", "Great for hands-free use in walking or hiking", 10.99, "leash1.jpeg", 5, 3);
212-- insert into product values(23, "Dapper Dan Bows", "One bowtie is dapper and modern in a burgundy and white polka dot print", 12.99, "bows.jpeg", 5, 3);
213-- insert into product values(24, "Adjustable Harness", "Built-in slide allows you to adjust size for a customized fit", 12.99, "harness1.jpeg", 5, 3);
214-- insert into product values(25, "Chewbacca Leash", "Max Length 7.5 inches. Accommodates waists up to 6 inches", 13.99, "leash2.jpeg", 5, 3);
215-- insert into product values(26, "Rose Gold Collar", "Rose Gold Bling Dog Collar from the Modern Luxe Collection", 12.99, "collar1.jpeg",	5, 3);
216-- insert into product values(27, "Retractable Leash", "Perfectly designed two-color tape leash for dogs with color-coordinated brake button", 23.99, "leash3.jpeg", 5, 3);
217--
218-- -- insertion into INVENTORY
219--
220-- insert into inventory values(1, 25, TRUE, 10, 1, 10); -- clothes
221-- insert into inventory values(2, 25, TRUE, 10, 1, 10); -- clothes
222-- insert into inventory values(3, 25, TRUE, 10, 1, 10); -- clothes
223-- insert into inventory values(4, 25, TRUE, 10, 1, 10); -- clothes
224-- insert into inventory values(5, 25, TRUE, 10, 1, 10); -- clothes
225-- insert into inventory values(6, 25, TRUE, 10, 1, 10); -- clothes
226-- insert into inventory values(7, 100, TRUE, 10, 1, 10); -- food
227-- insert into inventory values(8, 100, TRUE, 10, 1, 10); -- food
228-- insert into inventory values(9, 100, TRUE, 10, 1, 10); -- food
229-- insert into inventory values(10, 100, TRUE, 10, 1, 10); -- food
230-- insert into inventory values(11, 100, TRUE, 10, 1, 10); -- food
231-- insert into inventory values(12, 100, TRUE, 10, 1, 10); -- food
232-- insert into inventory values(13, 100, TRUE, 10, 1, 10); -- toys
233-- insert into inventory values(14, 100, TRUE, 10, 1, 10); -- toys
234-- insert into inventory values(15, 50, TRUE, 10, 1, 10); -- toys
235-- insert into inventory values(16, 100, TRUE, 10, 1, 10); -- toys
236-- insert into inventory values(17, 100, TRUE, 10, 1, 10); -- toys
237-- insert into inventory values(18, 50, TRUE, 10, 1, 10); -- toys
238-- insert into inventory values(19, 30, TRUE, 2, 1, 2); -- beds
239-- insert into inventory values(20, 30, TRUE, 2, 1, 2); -- beds
240-- insert into inventory values(21, 30, TRUE, 2, 1, 2); -- beds
241-- insert into inventory values(22, 50, TRUE, 10, 1, 10); -- accessories
242-- insert into inventory values(23, 50, TRUE, 10, 1, 10); -- accessories
243-- insert into inventory values(24, 50, TRUE, 10, 1, 10); -- accessories
244-- insert into inventory values(25, 50, TRUE, 10, 1, 10); -- accessories
245-- insert into inventory values(26, 50, TRUE, 10, 1, 10); -- accessories
246-- insert into inventory values(27, 50, TRUE, 10, 1, 10); -- accessories
247--
248-- insert into employee values(1, "Ernesto", "Sims", "ernestosims@borkshop.com", "admin", "ernestosims67", "password");
249-- insert into employee values(2, "Bonnie", "Hopper", "bonniehopper@borkshop.com", "service", "bonniehopper13", "password");
250--
251-- -- Inserting preliminary Orders for username dlawson87
252-- insert into orders (customer_id, sub_total, tax_amount, grand_total, status, shipping_addr_id)
253-- VALUES(1, 139.93, 11.55, 151.48, "PROCESSING", 1);
254--
255-- insert into order_product VALUES(1, 3, 1, 19.99);
256-- insert into order_product VALUES(1, 9 , 4, 24.99);
257-- insert into order_product VALUES(1, 13, 1, 6.99);
258-- insert into order_product VALUES(1, 26, 1, 12.99);