dH3cj3PY

1DROP DATABASE IF EXISTS fish_and_chips;
2
3CREATE DATABASE fish_and_chips;
4
5USE fish_and_chips;
6
7CREATE TABLE category (
8	category_id INT UNSIGNED,
9    category_name VARCHAR(50) NOT NULL,
10    category_desc VARCHAR(255),
11    parent_category_id INT UNSIGNED DEFAULT NULL,
12    PRIMARY KEY (category_id),
13    FOREIGN KEY (parent_category_id) REFERENCES category (category_id)
14		ON DELETE CASCADE 
15        ON UPDATE CASCADE,
16    CONSTRAINT uc_categorty_name UNIQUE (category_name)
17);
18
19CREATE TABLE item (
20	item_id INT AUTO_INCREMENT,
21    item_name VARCHAR(50) NOT NULL,
22    item_price DECIMAL(4,2) NOT NULL,
23    item_desc VARCHAR(255),
24    category_id INT UNSIGNED NOT NULL,
25    PRIMARY KEY (item_id),
26    FOREIGN KEY(category_id) REFERENCES category (category_id) 
27		ON UPDATE RESTRICT 
28        ON DELETE CASCADE,
29	CONSTRAINT uc_name_category UNIQUE (item_name, category_id)
30);
31
32CREATE TABLE opening_hour (
33	open_hr_no INT AUTO_INCREMENT,
34    open_hr_day VARCHAR(9),
35    open_hr_open_time TIME,
36    open_hr_close_time TIME,
37    PRIMARY KEY (open_hr_no),
38    CONSTRAINT open_close_time_chk CHECK(open_hr_open_time < open_hr_close_time)
39);
40
41-- insert categories
42INSERT INTO category VALUES (1, 'Special', NULL, NULL);
43INSERT INTO category VALUES (2, 'Fish', NULL, NULL);
44INSERT INTO category VALUES (3, 'Fish-Extra', NULL, 2);
45INSERT INTO category VALUES (4, 'Chips', NULL, NULL);
46INSERT INTO category VALUES (5, 'Seafood', NULL, NULL);
47INSERT INTO category VALUES (6, 'Souvlaki', '(Homemade Tzatziki Sauce, Lettuce, Tomato, Onion)', NULL);
48INSERT INTO category VALUES (7, 'Steak Sandwiches', NULL, NULL);
49INSERT INTO category VALUES (8, 'Steak Sandwiches-Extra', NULL, 7);
50INSERT INTO category VALUES (9, 'Sandwiches', NULL, NULL);
51INSERT INTO category VALUES (10, 'Sandwiches-Extra', NULL, 9);
52INSERT INTO category VALUES (11, 'Hamburgers', NULL, NULL);
53INSERT INTO category VALUES (12, 'Hamburgers-Extra', NULL, 11);
54INSERT INTO category VALUES (13, 'Burgers', '(Lettuce, Tomato, Onion, Sauce)', NULL);
55INSERT INTO category VALUES (14, 'Snacks', NULL, NULL);
56INSERT INTO category VALUES (15, 'Sweets', NULL, NULL);
57INSERT INTO category VALUES (16,'Sauces', NULL, NULL);
58
59-- insert items
60-- special packs
61INSERT INTO item (item_name, item_price, item_desc, category_id) 
62	VALUES ('Lunch Pack', 11.00, 
63		'One fried flake, one potato cake, one dim sim, some chips and a 375mL soft drink or 600mL water.', 
64		(SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SPECIAL'));
65INSERT INTO item (item_name, item_price, item_desc, category_id) 
66	VALUES ('Dinner Pack', 23.00, 
67		'Two fried flakes, two potato cakes, two dim sims, four chicken nuggets, two seafood/crab sticks and minimum chips.', 
68		(SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SPECIAL'));
69INSERT INTO item (item_name, item_price, item_desc, category_id) 
70	VALUES ('Seafood Pack', 30.00, 
71		'Two fried flakes, two crumbed prawns, two crab sticks, two scallops, four calamari rings and minimum chips.', 
72		(SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SPECIAL'));
73INSERT INTO item (item_name, item_price, item_desc, category_id) 
74	VALUES ('Family Pack', 37.00, 
75		'Four fried flakes, four potato cakes, four dim sims, four seafood/crab sticks and minimum chips.', 
76		(SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SPECIAL'));
77
78-- fish
79INSERT INTO item (item_name, item_price, item_desc, category_id) 
80	VALUES ('Flake', 5.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'FISH'));
81INSERT INTO item (item_name, item_price, item_desc, category_id) 
82	VALUES ('Barramundi', 6.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'FISH'));
83INSERT INTO item (item_name, item_price, item_desc, category_id) 
84	VALUES ('Barracuda', 4.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'FISH'));
85INSERT INTO item (item_name, item_price, item_desc, category_id) 
86	VALUES ('Blue Grenadier', 6.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'FISH'));
87INSERT INTO item (item_name, item_price, item_desc, category_id) 
88	VALUES ('Snapper', 6.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'FISH'));
89INSERT INTO item (item_name, item_price, item_desc, category_id) 
90	VALUES ('Flounder', 6.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'FISH'));
91INSERT INTO item (item_name, item_price, item_desc, category_id) 
92	VALUES ('Whiting (King George)', 7.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'FISH'));
93INSERT INTO item (item_name, item_price, item_desc, category_id) 
94	VALUES ('Fish Bite', 3.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'FISH'));
95
96-- extras for fish
97INSERT INTO item (item_name, item_price, item_desc, category_id) 
98	VALUES ('Grilled', 0.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'FISH-EXTRA'));
99INSERT INTO item (item_name, item_price, item_desc, category_id) 
100	VALUES ('Grilled No Batter', 0.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'FISH-EXTRA'));
101INSERT INTO item (item_name, item_price, item_desc, category_id) 
102	VALUES ('Grilled Crumbed', 1.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'FISH-EXTRA'));
103    
104-- chips
105INSERT INTO item (item_name, item_price, item_desc, category_id) 
106	VALUES ('Minimum', 4.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'CHIPS'));
107INSERT INTO item (item_name, item_price, item_desc, category_id) 
108	VALUES ('Medium', 5.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'CHIPS'));
109INSERT INTO item (item_name, item_price, item_desc, category_id) 
110	VALUES ('Large', 7.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'CHIPS'));
111
112-- seafood
113INSERT INTO item (item_name, item_price, item_desc, category_id) 
114	VALUES ('Fish Cake', 2.20, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SEAFOOD'));
115INSERT INTO item (item_name, item_price, item_desc, category_id) 
116	VALUES ('Scallop', 3.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SEAFOOD'));
117INSERT INTO item (item_name, item_price, item_desc, category_id) 
118	VALUES ('Crab Stick', 1.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SEAFOOD'));
119INSERT INTO item (item_name, item_price, item_desc, category_id) 
120	VALUES ('Calamari Ring', 1.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SEAFOOD'));
121INSERT INTO item (item_name, item_price, item_desc, category_id) 
122	VALUES ('Crumbed Prawn', 1.80, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SEAFOOD'));
123INSERT INTO item (item_name, item_price, item_desc, category_id) 
124	VALUES ('Mussel in Batter', 0.90, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SEAFOOD'));
125    
126-- souvlaki
127INSERT INTO item (item_name, item_price, item_desc, category_id) 
128	VALUES ('Lamb', 9.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SOUVLAKI'));
129INSERT INTO item (item_name, item_price, item_desc, category_id) 
130	VALUES ('Chicken', 9.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SOUVLAKI'));
131INSERT INTO item (item_name, item_price, item_desc, category_id) 
132	VALUES ('Fish', 9.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SOUVLAKI'));
133INSERT INTO item (item_name, item_price, item_desc, category_id) 
134	VALUES ('Vegetarian', 4.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SOUVLAKI'));
135INSERT INTO item (item_name, item_price, item_desc, category_id) 
136	VALUES ('Plain Pita Bread', 2.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SOUVLAKI'));
137
138-- steak sandwiches
139INSERT INTO item (item_name, item_price, item_desc, category_id) 
140	VALUES ('Plain', 6.50, '(Steak, Lettuce, Sauce)', (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'STEAK SANDWICHES'));
141INSERT INTO item (item_name, item_price, item_desc, category_id) 
142	VALUES ('The Lot', 8.50, '(Steak, Lettuce, Egg, Bacon, Onion, Tomato, Cheese, Sauce)', (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'STEAK SANDWICHES'));
143
144-- extras for steak sandwiches
145INSERT INTO item (item_name, item_price, item_desc, category_id) 
146	VALUES ('Egg', 0.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'STEAK SANDWICHES-EXTRA'));
147INSERT INTO item (item_name, item_price, item_desc, category_id) 
148	VALUES ('Bacon', 0.70, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'STEAK SANDWICHES-EXTRA'));
149INSERT INTO item (item_name, item_price, item_desc, category_id) 
150	VALUES ('Onion', 0.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'STEAK SANDWICHES-EXTRA'));
151INSERT INTO item (item_name, item_price, item_desc, category_id) 
152	VALUES ('Cheese', 0.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'STEAK SANDWICHES-EXTRA'));
153INSERT INTO item (item_name, item_price, item_desc, category_id) 
154	VALUES ('Pineapple', 0.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'STEAK SANDWICHES-EXTRA'));
155INSERT INTO item (item_name, item_price, item_desc, category_id) 
156	VALUES ('Beetroot', 0.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'STEAK SANDWICHES-EXTRA'));
157
158-- sandwiches
159INSERT INTO item (item_name, item_price, item_desc, category_id) 
160	VALUES ('Cheese', 2.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SANDWICHES'));
161INSERT INTO item (item_name, item_price, item_desc, category_id) 
162	VALUES ('Cheese + Lettuce', 3.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SANDWICHES'));
163INSERT INTO item (item_name, item_price, item_desc, category_id) 
164	VALUES ('Cheese + Lettuce + Tomato', 3.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SANDWICHES'));
165INSERT INTO item (item_name, item_price, item_desc, category_id) 
166	VALUES ('Bacon + Egg + Cheese', 4.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SANDWICHES'));
167INSERT INTO item (item_name, item_price, item_desc, category_id) 
168	VALUES ('Chicken', 4.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SANDWICHES'));
169
170-- extras for sandwiches
171INSERT INTO item (item_name, item_price, item_desc, category_id) 
172	VALUES ('Toasted', 0.30, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SANDWICHES-EXTRA'));
173INSERT INTO item (item_name, item_price, item_desc, category_id) 
174	VALUES ('Rolls', 0.40, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SANDWICHES-EXTRA'));
175
176-- hamburgers
177INSERT INTO item (item_name, item_price, item_desc, category_id) 
178	VALUES ('Plain', 4.50, '(Beef Patty, Lettuce, Sauce)', (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'HAMBURGERS'));
179INSERT INTO item (item_name, item_price, item_desc, category_id) 
180	VALUES ('The Lot', 7.00, '(Beef Patty, Lettuce, Egg, Bacon, Onion, Tomato, Cheese, Sauce)', (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'HAMBURGERS'));
181
182-- extras for hamburgers
183INSERT INTO item (item_name, item_price, item_desc, category_id) 
184	VALUES ('Egg', 0.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'HAMBURGERS-EXTRA'));
185INSERT INTO item (item_name, item_price, item_desc, category_id) 
186	VALUES ('Bacon', 0.70, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'HAMBURGERS-EXTRA'));
187INSERT INTO item (item_name, item_price, item_desc, category_id) 
188	VALUES ('Onion', 0.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'HAMBURGERS-EXTRA'));
189INSERT INTO item (item_name, item_price, item_desc, category_id) 
190	VALUES ('Cheese', 0.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'HAMBURGERS-EXTRA'));
191INSERT INTO item (item_name, item_price, item_desc, category_id) 
192	VALUES ('Pineapple', 0.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'HAMBURGERS-EXTRA'));
193INSERT INTO item (item_name, item_price, item_desc, category_id) 
194	VALUES ('Beetroot', 0.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'HAMBURGERS-EXTRA'));
195
196-- burgers
197INSERT INTO item (item_name, item_price, item_desc, category_id) 
198	VALUES ('Chicken Schnitzel Burger', 8.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'BURGERS'));
199INSERT INTO item (item_name, item_price, item_desc, category_id) 
200	VALUES ('Lamb Roll', 6.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'BURGERS'));
201INSERT INTO item (item_name, item_price, item_desc, category_id) 
202	VALUES ('Fish Burger', 8.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'BURGERS'));
203INSERT INTO item (item_name, item_price, item_desc, category_id) 
204	VALUES ('Roast Chicken Burger', 5.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'BURGERS'));
205INSERT INTO item (item_name, item_price, item_desc, category_id) 
206	VALUES ('Chicken Burger', 5.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'BURGERS'));
207
208-- snacks
209INSERT INTO item (item_name, item_price, item_desc, category_id) 
210	VALUES ('Hot Dog', 3.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SNACKS'));
211INSERT INTO item (item_name, item_price, item_desc, category_id) 
212	VALUES ('Dim Sim', 1.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SNACKS'));
213INSERT INTO item (item_name, item_price, item_desc, category_id) 
214	VALUES ('Potato Cake', 1.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SNACKS'));
215INSERT INTO item (item_name, item_price, item_desc, category_id) 
216	VALUES ('Fried Homemade Dim Sim', 1.80, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SNACKS'));
217INSERT INTO item (item_name, item_price, item_desc, category_id) 
218	VALUES ('Spring Roll', 2.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SNACKS'));
219INSERT INTO item (item_name, item_price, item_desc, category_id) 
220	VALUES ('Chiko Roll', 2.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SNACKS'));
221INSERT INTO item (item_name, item_price, item_desc, category_id) 
222	VALUES ('Hamburger in Batter', 3.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SNACKS'));
223INSERT INTO item (item_name, item_price, item_desc, category_id) 
224	VALUES ('Sausage in Batter', 2.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SNACKS'));
225INSERT INTO item (item_name, item_price, item_desc, category_id) 
226	VALUES ('Beef Croquette', 1.80, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SNACKS'));
227INSERT INTO item (item_name, item_price, item_desc, category_id) 
228	VALUES ('Chicken Croquette', 1.80, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SNACKS'));
229INSERT INTO item (item_name, item_price, item_desc, category_id) 
230	VALUES ('Chicken Schnitzel', 5.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SNACKS'));
231INSERT INTO item (item_name, item_price, item_desc, category_id) 
232	VALUES ('Corn Jack', 2.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SNACKS'));
233INSERT INTO item (item_name, item_price, item_desc, category_id) 
234	VALUES ('Chicken Wing', 1.30, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SNACKS'));
235INSERT INTO item (item_name, item_price, item_desc, category_id) 
236	VALUES ('Pickled Onion', 1.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SNACKS'));
237INSERT INTO item (item_name, item_price, item_desc, category_id) 
238	VALUES ('Chicken Nugget', 0.70, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SNACKS'));
239INSERT INTO item (item_name, item_price, item_desc, category_id) 
240	VALUES ('Sweet Chilli Tender', 1.40, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SNACKS'));
241INSERT INTO item (item_name, item_price, item_desc, category_id) 
242	VALUES ('Chicken Garlic Ball', 1.00, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SNACKS'));
243
244-- sweets
245INSERT INTO item (item_name, item_price, item_desc, category_id) 
246	VALUES ('Pineapple Fritter', 2.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SWEETS'));
247INSERT INTO item (item_name, item_price, item_desc, category_id) 
248	VALUES ('Banana Fritter', 2.50, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SWEETS'));
249
250-- sauces
251INSERT INTO item (item_name, item_price, item_desc, category_id) 
252	VALUES ('Tomato Sauce', 0.40, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SAUCES'));
253INSERT INTO item (item_name, item_price, item_desc, category_id) 
254	VALUES ('Tartare Sauce', 0.40, NULL, (SELECT DISTINCT category_id FROM category WHERE UPPER(category_name) = 'SAUCES'));
255    
256    
257-- opening hours
258INSERT INTO opening_hour (open_hr_day, open_hr_open_time, open_hr_close_time)
259	VALUES ('Monday', CONVERT("16:30", TIME), CONVERT("20:30", TIME));
260INSERT INTO opening_hour (open_hr_day, open_hr_open_time, open_hr_close_time)
261	VALUES ('Tuesday', CONVERT("11:00", TIME), CONVERT("20:30", TIME));
262INSERT INTO opening_hour (open_hr_day, open_hr_open_time, open_hr_close_time)
263	VALUES ('Wednesday', CONVERT("11:00", TIME), CONVERT("20:30", TIME));
264INSERT INTO opening_hour (open_hr_day, open_hr_open_time, open_hr_close_time)
265	VALUES ('Thursday', CONVERT("11:00", TIME), CONVERT("20:30", TIME));
266INSERT INTO opening_hour (open_hr_day, open_hr_open_time, open_hr_close_time)
267	VALUES ('Friday', CONVERT("11:00", TIME), CONVERT("21:00", TIME));
268INSERT INTO opening_hour (open_hr_day, open_hr_open_time, open_hr_close_time)
269	VALUES ('Saturday', CONVERT("11:00", TIME), CONVERT("21:00", TIME));
270INSERT INTO opening_hour (open_hr_day, open_hr_open_time, open_hr_close_time)
271	VALUES ('Sunday', CONVERT("16:30", TIME), CONVERT("20:30", TIME));