· 7 years ago · Oct 20, 2018, 04:44 PM
1DROP DATABASE IF EXISTS restaurant;
2CREATE DATABASE restaurant;
3USE restaurant;
4SET AUTOCOMMIT=0;
5
6DROP TABLE IF EXISTS employee;
7CREATE TABLE employee (
8 `Order_Info_ID` INT(8) NOT NULL,
9 `Starting_Salary` INT(7) NOT NULL,
10 `Employee_Address` VARCHAR(64) NOT NULL,
11 `Employee_Phone_Number` VARCHAR(15) NOT NULL,
12 `Employee_ID` INT(8) NOT NULL,
13 `Employee_Last_Name` VARCHAR(15) NOT NULL,
14 `Employee_First_Name` VARCHAR(15) NOT NULL,
15 PRIMARY KEY (`Employee_ID`))
16ENGINE = InnoDB
17DEFAULT CHARACTER SET = latin1;
18
19DROP TABLE IF EXISTS customer;
20CREATE TABLE customer (
21 `Customer_Phone_Number` VARCHAR(15) NOT NULL,
22 `Customer_First_Name` VARCHAR(15) NOT NULL,
23 `Customer_Last_Name` VARCHAR(15) NOT NULL,
24 `Customer_Payment_Method` ENUM('Credit Card', 'Cheque', 'Cash') NOT NULL,
25 `Customer_Street_Address` VARCHAR(64) NOT NULL,
26 PRIMARY KEY (`Customer_Phone_Number`))
27ENGINE = InnoDB
28DEFAULT CHARACTER SET = latin1;
29
30DROP TABLE IF EXISTS orders;
31CREATE TABLE orders (
32 `Order_ID` INT(8) NOT NULL,
33 `Order_Date_Time` DATETIME NOT NULL,
34 `Customer_Phone_Number` VARCHAR(15) NOT NULL,
35 `customer_Customer_Phone_Number` VARCHAR(15) NOT NULL,
36 PRIMARY KEY (`Order_ID`, `customer_Customer_Phone_Number`),
37 INDEX `phone_link_idx` (`customer_Customer_Phone_Number` ASC) VISIBLE,
38 CONSTRAINT `phone_link`
39 FOREIGN KEY (`customer_Customer_Phone_Number`)
40 REFERENCES `customer` (`Customer_Phone_Number`)
41 ON DELETE NO ACTION
42 ON UPDATE NO ACTION)
43ENGINE = InnoDB
44DEFAULT CHARACTER SET = latin1;
45
46DROP TABLE IF EXISTS `orderinfo`;
47CREATE TABLE IF NOT EXISTS `orderinfo` (
48 `Order_ID` INT(8) NOT NULL,
49 `Pizza_ID` INT(8) NOT NULL,
50 `Employee_ID` INT(8) NOT NULL,
51 `Product_Code` INT(8) NOT NULL,
52 `Order_Info_ID` INT(8) NOT NULL,
53 `orders_Order_ID` INT(8) NOT NULL,
54 `orders_customer_Customer_Phone_Number` VARCHAR(15) NOT NULL,
55 PRIMARY KEY (`Order_Info_ID`, `orders_Order_ID`, `orders_customer_Customer_Phone_Number`),
56 INDEX `fk_orderinfo_orders1_idx` (`orders_Order_ID` ASC, `orders_customer_Customer_Phone_Number` ASC) VISIBLE,
57 CONSTRAINT `fk_orderinfo_orders1`
58 FOREIGN KEY (`orders_Order_ID` , `orders_customer_Customer_Phone_Number`)
59 REFERENCES `restaurant`.`orders` (`Order_ID` , `customer_Customer_Phone_Number`)
60 ON DELETE NO ACTION
61 ON UPDATE NO ACTION)
62ENGINE = InnoDB
63DEFAULT CHARACTER SET = latin1;
64
65DROP TABLE IF EXISTS `chef`;
66CREATE TABLE IF NOT EXISTS `chef` (
67 `Employee_ID` INT(8) NOT NULL,
68 `Order_Info_ID` INT(8) NOT NULL,
69 PRIMARY KEY (`Employee_ID`),
70 INDEX `Order_Info_ID` (`Order_Info_ID` ASC) VISIBLE,
71 CONSTRAINT `chef_ibfk_1`
72 FOREIGN KEY (`Employee_ID`)
73 REFERENCES `restaurant`.`employee` (`Employee_ID`),
74 CONSTRAINT `chef_ibfk_2`
75 FOREIGN KEY (`Order_Info_ID`)
76 REFERENCES `restaurant`.`orderinfo` (`Order_Info_ID`))
77ENGINE = InnoDB
78DEFAULT CHARACTER SET = latin1;
79
80DROP TABLE IF EXISTS `driver`;
81CREATE TABLE IF NOT EXISTS `driver` (
82 `Employee_ID` INT(8) NOT NULL,
83 `Order_Info_ID` INT(8) NOT NULL,
84 PRIMARY KEY (`Employee_ID`),
85 INDEX `Order_Info_ID` (`Order_Info_ID` ASC) VISIBLE,
86 CONSTRAINT `driver_ibfk_1`
87 FOREIGN KEY (`Employee_ID`)
88 REFERENCES `restaurant`.`employee` (`Employee_ID`),
89 CONSTRAINT `driver_ibfk_2`
90 FOREIGN KEY (`Order_Info_ID`)
91 REFERENCES `restaurant`.`orderinfo` (`Order_Info_ID`))
92ENGINE = InnoDB
93DEFAULT CHARACTER SET = latin1;
94
95DROP TABLE IF EXISTS `other_items`;
96CREATE TABLE IF NOT EXISTS `other_items` (
97 `Product_Code` INT(8) NOT NULL,
98 `Items_Description` VARCHAR(64) NULL DEFAULT NULL,
99 `Items_Price` INT(6) NULL DEFAULT NULL,
100 `Items_Supplier_Name` VARCHAR(32) NULL DEFAULT NULL,
101 `Items_Manufacturer_Name` VARCHAR(32) NULL DEFAULT NULL,
102 `orderinfo_Order_Info_ID` INT(8) NOT NULL,
103 `orderinfo_orders_Order_ID` INT(8) NOT NULL,
104 `orderinfo_orders_customer_Customer_Phone_Number` VARCHAR(15) NOT NULL,
105 PRIMARY KEY (`Product_Code`, `orderinfo_Order_Info_ID`, `orderinfo_orders_Order_ID`, `orderinfo_orders_customer_Customer_Phone_Number`),
106 INDEX `fk_other_items_orderinfo1_idx` (`orderinfo_Order_Info_ID` ASC, `orderinfo_orders_Order_ID` ASC, `orderinfo_orders_customer_Customer_Phone_Number` ASC) VISIBLE,
107 CONSTRAINT `fk_other_items_orderinfo1`
108 FOREIGN KEY (`orderinfo_Order_Info_ID` , `orderinfo_orders_Order_ID` , `orderinfo_orders_customer_Customer_Phone_Number`)
109 REFERENCES `restaurant`.`orderinfo` (`Order_Info_ID` , `orders_Order_ID` , `orders_customer_Customer_Phone_Number`)
110 ON DELETE NO ACTION
111 ON UPDATE NO ACTION)
112ENGINE = InnoDB
113DEFAULT CHARACTER SET = latin1;
114
115DROP TABLE IF EXISTS `pizza` ;
116CREATE TABLE IF NOT EXISTS `pizza` (
117 `Pizza_Base_Price` INT(6) NOT NULL,
118 `Pizza_ID` INT(8) NOT NULL,
119 `Pizza_Crust_Type` ENUM('Deep Pan', 'Thin', 'Thick') NOT NULL,
120 `Pizza_Size` ENUM('Small', 'Medium', 'Large') NOT NULL,
121 `Pizza_Discount_Amount` INT(6) NOT NULL,
122 `orderinfo_Order_Info_ID` INT(8) NOT NULL,
123 `orderinfo_orders_Order_ID` INT(8) NOT NULL,
124 `orderinfo_orders_customer_Customer_Phone_Number` VARCHAR(15) NOT NULL,
125 PRIMARY KEY (`Pizza_ID`, `orderinfo_Order_Info_ID`, `orderinfo_orders_Order_ID`, `orderinfo_orders_customer_Customer_Phone_Number`),
126 INDEX `fk_pizza_orderinfo1_idx` (`orderinfo_Order_Info_ID` ASC, `orderinfo_orders_Order_ID` ASC, `orderinfo_orders_customer_Customer_Phone_Number` ASC) VISIBLE,
127 CONSTRAINT `fk_pizza_orderinfo1`
128 FOREIGN KEY (`orderinfo_Order_Info_ID` , `orderinfo_orders_Order_ID` , `orderinfo_orders_customer_Customer_Phone_Number`)
129 REFERENCES `restaurant`.`orderinfo` (`Order_Info_ID` , `orders_Order_ID` , `orders_customer_Customer_Phone_Number`)
130 ON DELETE NO ACTION
131 ON UPDATE NO ACTION)
132ENGINE = InnoDB
133DEFAULT CHARACTER SET = latin1;
134
135DROP TABLE IF EXISTS `toppings` ;
136CREATE TABLE IF NOT EXISTS `toppings` (
137 `Toppings_Name_Type` VARCHAR(32) NOT NULL,
138 `Toppings_ID` INT(8) NOT NULL,
139 `Toppings_Price` INT(6) NOT NULL,
140 `Pizza_ID` INT(8) NOT NULL,
141 `Toppings_Quantity` INT(6) NOT NULL,
142 `Toppings_Cost` INT(6) NOT NULL,
143 PRIMARY KEY (`Toppings_ID`))
144ENGINE = InnoDB
145DEFAULT CHARACTER SET = latin1;
146
147DROP TABLE IF EXISTS `orderinfo_has_employee`;
148CREATE TABLE IF NOT EXISTS `orderinfo_has_employee` (
149 `orderinfo_Order_Info_ID` INT(8) NOT NULL,
150 `orderinfo_orders_Order_ID` INT(8) NOT NULL,
151 `orderinfo_orders_customer_Customer_Phone_Number` VARCHAR(15) NOT NULL,
152 `employee_Employee_ID` INT(8) NOT NULL,
153 PRIMARY KEY (`orderinfo_Order_Info_ID`, `orderinfo_orders_Order_ID`, `orderinfo_orders_customer_Customer_Phone_Number`, `employee_Employee_ID`),
154 INDEX `fk_orderinfo_has_employee_employee1_idx` (`employee_Employee_ID` ASC) VISIBLE,
155 INDEX `fk_orderinfo_has_employee_orderinfo1_idx` (`orderinfo_Order_Info_ID` ASC, `orderinfo_orders_Order_ID` ASC, `orderinfo_orders_customer_Customer_Phone_Number` ASC) VISIBLE,
156 CONSTRAINT `fk_orderinfo_has_employee_orderinfo1`
157 FOREIGN KEY (`orderinfo_Order_Info_ID` , `orderinfo_orders_Order_ID` , `orderinfo_orders_customer_Customer_Phone_Number`)
158 REFERENCES `restaurant`.`orderinfo` (`Order_Info_ID` , `orders_Order_ID` , `orders_customer_Customer_Phone_Number`)
159 ON DELETE NO ACTION
160 ON UPDATE NO ACTION,
161 CONSTRAINT `fk_orderinfo_has_employee_employee1`
162 FOREIGN KEY (`employee_Employee_ID`)
163 REFERENCES `restaurant`.`employee` (`Employee_ID`)
164 ON DELETE NO ACTION
165 ON UPDATE NO ACTION)
166ENGINE = InnoDB
167DEFAULT CHARACTER SET = latin1;
168
169DROP TABLE IF EXISTS `pizza_has_toppings`;
170CREATE TABLE IF NOT EXISTS `pizza_has_toppings` (
171 `pizza_Pizza_ID` INT(8) NOT NULL,
172 `pizza_orderinfo_Order_Info_ID` INT(8) NOT NULL,
173 `pizza_orderinfo_orders_Order_ID` INT(8) NOT NULL,
174 `pizza_orderinfo_orders_customer_Customer_Phone_Number` VARCHAR(15) NOT NULL,
175 `toppings_Toppings_ID` INT(8) NOT NULL,
176 PRIMARY KEY (`pizza_Pizza_ID`, `pizza_orderinfo_Order_Info_ID`, `pizza_orderinfo_orders_Order_ID`, `pizza_orderinfo_orders_customer_Customer_Phone_Number`, `toppings_Toppings_ID`),
177 INDEX `fk_pizza_has_toppings_toppings1_idx` (`toppings_Toppings_ID` ASC) VISIBLE,
178 INDEX `fk_pizza_has_toppings_pizza1_idx` (`pizza_Pizza_ID` ASC, `pizza_orderinfo_Order_Info_ID` ASC, `pizza_orderinfo_orders_Order_ID` ASC, `pizza_orderinfo_orders_customer_Customer_Phone_Number` ASC) VISIBLE,
179 CONSTRAINT `fk_pizza_has_toppings_pizza1`
180 FOREIGN KEY (`pizza_Pizza_ID` , `pizza_orderinfo_Order_Info_ID` , `pizza_orderinfo_orders_Order_ID` , `pizza_orderinfo_orders_customer_Customer_Phone_Number`)
181 REFERENCES `restaurant`.`pizza` (`Pizza_ID` , `orderinfo_Order_Info_ID` , `orderinfo_orders_Order_ID` , `orderinfo_orders_customer_Customer_Phone_Number`)
182 ON DELETE NO ACTION
183 ON UPDATE NO ACTION,
184 CONSTRAINT `fk_pizza_has_toppings_toppings1`
185 FOREIGN KEY (`toppings_Toppings_ID`)
186 REFERENCES `restaurant`.`toppings` (`Toppings_ID`)
187 ON DELETE NO ACTION
188 ON UPDATE NO ACTION)
189ENGINE = InnoDB
190DEFAULT CHARACTER SET = latin1;