h74mNipD

1USE nrobertson;
2
3DROP TABLE IF EXISTS Feedback;
4DROP TABLE IF EXISTS Shift;
5DROP TABLE IF EXISTS Recipe;
6DROP TABLE IF EXISTS Delivery;
7DROP TABLE IF EXISTS Bill_Detail;
8DROP TABLE IF EXISTS Menu_Item;
9DROP TABLE IF EXISTS Instore_Bill;
10DROP TABLE IF EXISTS Seating;
11DROP TABLE IF EXISTS Online_Bill;
12DROP TABLE IF EXISTS Bill;
13DROP TABLE IF EXISTS Stock;
14DROP TABLE IF EXISTS Ingredient;
15DROP TABLE IF EXISTS Employee;
16DROP TABLE IF EXISTS Customer;
17DROP TABLE IF EXISTS Employee_Class;
18DROP TABLE IF EXISTS Branch;
19
20CREATE TABLE Branch (
21	Brch_ID INT(5) NOT NULL UNIQUE AUTO_INCREMENT,
22	Brch_Name VARCHAR(100) NOT NULL,
23	PRIMARY KEY(Brch_ID)
24);
25
26CREATE TABLE Employee_Class (
27	EmpClass_ID INT(5) NOT NULL UNIQUE,
28	EmpClass_Rate INT(5)  NOT NULL,
29	EmpClass_Desc VARCHAR(100) NOT NULL,
30	PRIMARY KEY(EmpClass_ID)
31);
32
33CREATE TABLE Employee (
34	Emp_ID INT(5) NOT NULL UNIQUE AUTO_INCREMENT,
35	Emp_FName VARCHAR(100) NOT NULL,
36 	Emp_LName VARCHAR(100) NOT NULL,
37	EmpClass_ID INT(5) NOT NULL,
38	Brch_ID INT(5) NOT NULL,
39   	PRIMARY KEY(Emp_ID),
40	FOREIGN KEY(EmpClass_ID) REFERENCES Employee_Class(EmpClass_ID),
41	FOREIGN KEY(Brch_ID) REFERENCES Branch(Brch_ID)
42);
43
44CREATE TABLE Feedback (
45	Fdbck_ID INT(5) NOT NULL UNIQUE AUTO_INCREMENT,
46	Fdbck_Content VARCHAR(1000) NOT NULL, 
47	Emp_ID INT(5) NOT NULL,
48	Brch_ID INT(5)NOT NULL,
49	PRIMARY KEY(Fdbck_ID),
50	FOREIGN KEY(Emp_ID) REFERENCES Employee(Emp_ID),
51	FOREIGN KEY(Brch_ID) REFERENCES Branch(Brch_ID)
52);
53
54CREATE TABLE Shift (
55	Shift_ID INT(5) NOT NULL UNIQUE AUTO_INCREMENT,
56	Emp_ID INT(5) NOT NULL,
57	Shift_Date DATETIME NOT NULL,
58	PRIMARY KEY(Shift_ID),
59	FOREIGN KEY(Emp_ID) REFERENCES Employee(Emp_ID)
60);
61
62CREATE TABLE Ingredient (
63	Ingrdt_ID INT(5) NOT NULL UNIQUE AUTO_INCREMENT,
64	Ingrdt_Name VARCHAR(100) NOT NULL,
65	PRIMARY KEY(Ingrdt_ID)
66);
67
68CREATE TABLE Menu_Item (
69	Item_ID INT(5) NOT NULL UNIQUE AUTO_INCREMENT,
70	Item_Name VARCHAR(100) NOT NULL,
71	Item_Cost INT(5) NOT NULL,
72	PRIMARY KEY(Item_ID)
73);
74
75CREATE TABLE Recipe (
76	Rcp_ID INT(5) NOT NULL UNIQUE AUTO_INCREMENT,
77	Ingrdt_ID INT(5) NOT NULL,
78	Item_ID INT(5) NOT NULL,
79	Rcp_AmountReq INT(5)NOT NULL,
80	PRIMARY KEY(Rcp_ID),
81	FOREIGN KEY(Ingrdt_ID) REFERENCES Ingredient(Ingrdt_ID),
82	FOREIGN KEY(Item_ID) REFERENCES Menu_Item(Item_ID)
83);
84
85CREATE TABLE Stock (
86	Stck_ID INT(5) NOT NULL UNIQUE AUTO_INCREMENT,
87	Ingrdt_ID INT(5) NOT NULL,
88	Brch_ID INT(5) NOT NULL,
89	Stck_QOH INT(5)NOT NULL,
90	PRIMARY KEY(Stck_ID),
91	FOREIGN KEY(Ingrdt_ID) REFERENCES Ingredient(Ingrdt_ID),
92	FOREIGN KEY(Brch_ID) REFERENCES Branch(Brch_ID)
93);
94
95CREATE TABLE Delivery (
96	Dlv_ID INT(5) NOT NULL UNIQUE AUTO_INCREMENT,
97	Stck_ID INT(5) NOT NULL,
98	Dlv_Amount INT(5) NOT NULL,
99	Dlv_Received INT(1) NOT NULL,
100	Dlv_Cost INT(5) NOT NULL,
101	PRIMARY KEY(Dlv_ID),
102	FOREIGN KEY(Stck_ID) REFERENCES Stock(Stck_ID)
103);
104
105CREATE TABLE Customer (
106	Cust_ID INT(5) NOT NULL UNIQUE AUTO_INCREMENT,
107	Cust_FName VARCHAR(100)NOT NULL,
108	Cust_LName VARCHAR(100)NOT NULL,
109	Cust_Addr VARCHAR(100)NOT NULL,
110	PRIMARY KEY(Cust_ID)
111);
112
113CREATE TABLE Seating(
114	Seat_ID INT(5) NOT NULL,
115	Brch_ID INT(5) NOT NULL,
116	Seat_Capacity INT(5) NOT NULL,
117	PRIMARY KEY(Seat_ID, Brch_ID),
118	FOREIGN KEY(Brch_ID) REFERENCES Branch(Brch_ID)
119	ON DELETE CASCADE # should delete seatings when the branch is deleted
120);
121
122CREATE TABLE Bill (
123	Bill_ID INT(5) NOT NULL UNIQUE AUTO_INCREMENT,
124	Brch_ID INT(5) NOT NULL,
125	Bill_Total INT(5) NOT NULL,
126	Bill_State VARCHAR(100) NOT NULL,
127	Bill_Date DATETIME NOT NULL,
128	PRIMARY KEY(Bill_ID),
129	FOREIGN KEY(Brch_ID) REFERENCES Branch(Brch_ID)
130);
131
132CREATE TABLE Bill_Detail (
133	BillDet_ID INT(5) NOT NULL UNIQUE AUTO_INCREMENT,
134	Item_ID INT(5) NOT NULL,
135	Bill_ID INT(5) NOT NULL,
136	BillDet_Count INT(5) NOT NULL,
137	BillDet_State VARCHAR(100) NOT NULL,
138	PRIMARY KEY(BillDet_ID),
139	FOREIGN KEY(Item_ID) REFERENCES Menu_Item(Item_ID),
140	FOREIGN KEY(Bill_ID) REFERENCES Bill(Bill_ID)
141);
142
143CREATE TABLE Online_Bill (
144	OnBill_ID INT(5) NOT NULL UNIQUE AUTO_INCREMENT,
145	Cust_ID INT(5) NOT NULL,
146   	Bill_ID INT(5) NOT NULL,
147	PRIMARY KEY(OnBill_ID),
148	FOREIGN KEY(Cust_ID) REFERENCES Customer(Cust_ID),
149   	FOREIGN KEY(Bill_ID) REFERENCES Bill(Bill_ID)
150);
151
152CREATE TABLE Instore_Bill (
153	InBill_ID INT(5) NOT NULL UNIQUE AUTO_INCREMENT,
154	Attend_ID INT(5) NOT NULL,
155	Seat_ID INT(5) NOT NULL,
156	Bill_ID INT(5) NOT NULL,
157	Brch_ID INT(5) NOT NULL,
158	PRIMARY KEY(InBill_ID),
159	FOREIGN KEY(Bill_ID) REFERENCES Bill(Bill_ID),
160	FOREIGN KEY(Attend_ID) REFERENCES Employee(Emp_ID),
161	FOREIGN KEY(Seat_ID,Brch_ID) REFERENCES Seating(Seat_ID,Brch_ID)
162);
163
164
165/*
166#Branch data
167INSERT INTO Branch VALUES (1,"Halifax");
168INSERT INTO Branch VALUES (2,"Toronto");
169
170
171#Employee Classes
172INSERT INTO Employee_Class VALUES (1,20,"Attendant");
173INSERT INTO Employee_Class VALUES (2,20,"Head Cook");
174INSERT INTO Employee_Class VALUES (3,20,"Chef");
175INSERT INTO Employee_Class VALUES (4,20,"Manager");
176INSERT INTO Employee_Class VALUES (5,20,"Branch Manager");
177
178
179#Employee 
180INSERT INTO Employee(Emp_FName,Emp_LName,EmpClass_ID,Brch_ID) VALUES ("JohnHalifax","Attendant1",1,1);
181INSERT INTO Employee(Emp_FName,Emp_LName,EmpClass_ID,Brch_ID) VALUES ("JohnToronto","Attendant1",1,2);
182INSERT INTO Employee(Emp_FName,Emp_LName,EmpClass_ID,Brch_ID) VALUES ("JohnHalifax","Attendant2",1,1);
183INSERT INTO Employee(Emp_FName,Emp_LName,EmpClass_ID,Brch_ID) VALUES ("JohnToronto","Attendant2",1,2);
184
185INSERT INTO Employee(Emp_FName,Emp_LName,EmpClass_ID,Brch_ID) VALUES ("JohnHalifax","HeadCook1",2,1);
186INSERT INTO Employee(Emp_FName,Emp_LName,EmpClass_ID,Brch_ID) VALUES ("JohnToronto","HeadCook1",2,2);
187
188INSERT INTO Employee(Emp_FName,Emp_LName,EmpClass_ID,Brch_ID) VALUES ("JohnHalifax","Chef1",3,1);
189INSERT INTO Employee(Emp_FName,Emp_LName,EmpClass_ID,Brch_ID) VALUES ("JohnToronto","Chef1",3,2);
190INSERT INTO Employee(Emp_FName,Emp_LName,EmpClass_ID,Brch_ID) VALUES ("JohnHalifax","Chef2",3,1);
191INSERT INTO Employee(Emp_FName,Emp_LName,EmpClass_ID,Brch_ID) VALUES ("JohnToronto","Chef2",3,2);
192
193INSERT INTO Employee(Emp_FName,Emp_LName,EmpClass_ID,Brch_ID) VALUES ("JohnHalifax","Manager1",4,1);
194INSERT INTO Employee(Emp_FName,Emp_LName,EmpClass_ID,Brch_ID) VALUES ("JohnToronto","Manager1",4,2);
195
196INSERT INTO Employee(Emp_FName,Emp_LName,EmpClass_ID,Brch_ID) VALUES ("DA BOSS","BranchManny",5,2);
197
198INSERT INTO Menu_Item(Item_Name,Item_Cost) VALUES ("Sandwich",10);
199INSERT INTO Menu_Item(Item_Name,Item_Cost) VALUES ("Steak",15);
200INSERT INTO Menu_Item(Item_Name,Item_Cost) VALUES ("Salad",15);
201
202INSERT INTO Ingredient(Ingrdt_Name) VALUES ("Bread");
203INSERT INTO Ingredient(Ingrdt_Name) VALUES ("Meat");
204INSERT INTO Ingredient(Ingrdt_Name) VALUES ("Vegetables");
205
206INSERT INTO Stock(Ingrdt_ID,Brch_ID,Stck_QOH) VALUE(1,1,10);
207INSERT INTO Stock(Ingrdt_ID,Brch_ID,Stck_QOH) VALUE(2,1,8);
208INSERT INTO Stock(Ingrdt_ID,Brch_ID,Stck_QOH) VALUE(3,1,9);
209
210INSERT INTO Stock(Ingrdt_ID,Brch_ID,Stck_QOH) VALUE(1,2,10);
211INSERT INTO Stock(Ingrdt_ID,Brch_ID,Stck_QOH) VALUE(2,2,8);
212INSERT INTO Stock(Ingrdt_ID,Brch_ID,Stck_QOH) VALUE(3,2,9);
213
214INSERT INTO Recipe(Ingrdt_ID,Item_ID,Rcp_AmountReq) VALUES(1,1,2);
215INSERT INTO Recipe(Ingrdt_ID,Item_ID,Rcp_AmountReq) VALUES(1,2,1);
216INSERT INTO Recipe(Ingrdt_ID,Item_ID,Rcp_AmountReq) VALUES(2,2,2);
217
218
219INSERT INTO Seating(Seat_ID,Brch_ID,Seat_Capacity) VALUES(1,1,5);
220INSERT INTO Seating(Seat_ID,Brch_ID,Seat_Capacity) VALUES(2,1,5);
221INSERT INTO Seating(Seat_ID,Brch_ID,Seat_Capacity) VALUES(3,1,5);
222INSERT INTO Seating(Seat_ID,Brch_ID,Seat_Capacity) VALUES(4,1,5);
223INSERT INTO Seating(Seat_ID,Brch_ID,Seat_Capacity) VALUES(5,1,5);
224
225INSERT INTO Seating(Seat_ID,Brch_ID,Seat_Capacity) VALUES(1,2,5);
226INSERT INTO Seating(Seat_ID,Brch_ID,Seat_Capacity) VALUES(2,2,5);
227INSERT INTO Seating(Seat_ID,Brch_ID,Seat_Capacity) VALUES(3,2,5);
228INSERT INTO Seating(Seat_ID,Brch_ID,Seat_Capacity) VALUES(4,2,5);
229INSERT INTO Seating(Seat_ID,Brch_ID,Seat_Capacity) VALUES(5,2,5);
230
231INSERT INTO Bill(Brch_ID,Bill_Total,Bill_State,Bill_Date) VALUES(1,20,"NOT PAID","2008-11-11 13:23:44");
232
233INSERT INTO Bill_Detail(Item_ID,Bill_ID,BillDet_Count,BillDet_State) VALUES (1,1,1,"NOT PREPARED");
234INSERT INTO Bill_Detail(Item_ID,Bill_ID,BillDet_Count,BillDet_State) VALUES (1,1,2,"NOT PREPARED");
235
236INSERT INTO Bill(Brch_ID,Bill_Total,Bill_State,Bill_Date) VALUES(1,20,"PAID","2008-11-11 13:23:44");
237
238INSERT INTO Bill_Detail(Item_ID,Bill_ID,BillDet_Count,BillDet_State) VALUES (1,2,1,"NOT PREPARED");
239INSERT INTO Bill_Detail(Item_ID,Bill_ID,BillDet_Count,BillDet_State) VALUES (2,2,1,"NOT PREPARED");
240
241
242INSERT INTO Bill(Brch_ID,Bill_Total,Bill_State,Bill_Date) VALUES(1,20,"NOT PAID","2008-11-11 13:23:44");
243
244INSERT INTO Bill_Detail(Item_ID,Bill_ID,BillDet_Count,BillDet_State) VALUES (2,3,1,"NOT PREPARED");
245INSERT INTO Bill_Detail(Item_ID,Bill_ID,BillDet_Count,BillDet_State) VALUES (3,3,2,"NOT PREPARED");
246
247INSERT INTO Instore_Bill(Attend_ID,Seat_ID,Bill_ID,Brch_ID) VALUES (1,4,3,1);
248
249INSERT INTO Instore_Bill(Attend_ID,Seat_ID,Bill_ID,Brch_ID) VALUES (1,2,2,1);
250
251INSERT INTO Instore_Bill(Attend_ID,Seat_ID,Bill_ID,Brch_ID) VALUES (1,1,1,1);
252
253INSERT INTO Delivery(Stck_ID,Dlv_Amount,Dlv_Received,Dlv_Cost) VALUES ();
254*/