2hC9VKsc

1CREATE DATABASE IF NOT EXISTS `juicd` DEFAULT CHARSET utf8;
2
3USE `juicd`;
4
5/* Drop existing tables if they exist, ignoring foreign keys while dropping */
6SET FOREIGN_KEY_CHECKS = 0;
7DROP TABLE IF EXISTS `customers`;
8DROP TABLE IF EXISTS `outlets`;
9DROP TABLE IF EXISTS `employees`;
10DROP TABLE IF EXISTS `orders`;
11DROP TABLE IF EXISTS `products`;
12DROP TABLE IF EXISTS `orderitems`;
13DROP TABLE IF EXISTS `juice`;
14/* Re-enable foreign keys */
15SET FOREIGN_KEY_CHECKS = 1;
16
17
18/*Table structure for table `customers` */
19
20CREATE TABLE `customers` (
21  `customerID` SERIAL,
22  `customerName` VARCHAR(255) NOT NULL,
23  `address` VARCHAR(255) NOT NULL,
24  `email` VARCHAR(255) NOT NULL,
25  `points` BIGINT UNSIGNED NOT NULL,
26  PRIMARY KEY (`customerID`)
27) ENGINE=InnoDB;
28
29/*Table structure for table `outlets` */
30
31CREATE TABLE `outlets` (
32  `outletID` SERIAL,
33  `location` VARCHAR(255) NOT NULL,
34  `managerID` BIGINT UNSIGNED NOT NULL,
35  PRIMARY KEY (`outletID`)
36) ENGINE=InnoDB;
37
38/*Table structure for table `employees` */
39
40CREATE TABLE `employees` (
41  `employeeID` SERIAL,
42  `employeeName` VARCHAR(255) NOT NULL,
43  `outletID` BIGINT UNSIGNED NOT NULL,
44  `timeatoutlet` INT(3) UNSIGNED NOT NULL,
45  `linemanagerID` BIGINT UNSIGNED DEFAULT NULL,
46  PRIMARY KEY (`employeeID`),
47  CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`linemanagerID`) REFERENCES `employees` (`employeeID`),
48  CONSTRAINT `employees_ibfk_2` FOREIGN KEY (`outletID`) REFERENCES `outlets` (`outletID`)
49) ENGINE=InnoDB;
50 /*if the employee is a manager, then linemnanagerID will be null */
51
52/* Alter outlets and add a foreign key for the employee who is the manager of the outlet */
53ALTER TABLE `outlets` ADD CONSTRAINT `outlets_ibfk_1` FOREIGN KEY (`managerID`) REFERENCES `employees` (`employeeID`);
54
55/*Table structure for table `products` */
56
57CREATE TABLE `products` (
58  `productID` SERIAL,
59  `productName` VARCHAR(255) NOT NULL,
60  `cost` DECIMAL(10,2) NOT NULL,
61  `outletID` BIGINT UNSIGNED NOT NULL,
62  `isJuice` BOOL NOT NULL,
63  PRIMARY KEY (`productID`),
64  CONSTRAINT `products_ibfk_1` FOREIGN KEY (`outletID`) REFERENCES `outlets` (`outletID`)
65) ENGINE=InnoDB;
66/* if isJuice is false then cost would be given for a whole unit, otherwise it'd be per 100ml*/
67
68/*Table structure for table `orders` */
69
70CREATE TABLE `orders` (
71  `orderID` SERIAL,
72  `orderTime` TIMESTAMP NOT NULL,
73  `outletID` BIGINT UNSIGNED NOT NULL,
74  `customerID` BIGINT UNSIGNED NOT NULL,
75  `employeeID` BIGINT UNSIGNED NOT NULL,
76  PRIMARY KEY (`orderID`),
77  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customerID`) REFERENCES `customers` (`customerID`),
78  CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`outletID`) REFERENCES `outlets` (`outletID`),
79  CONSTRAINT `orders_ibfk_3` FOREIGN KEY (`employeeID`) REFERENCES `employees` (`employeeID`)
80) ENGINE=InnoDB;
81
82
83/*Table structure for table `orderitems` */
84
85CREATE TABLE `orderitems` (
86  `orderitemID` SERIAL
87  `orderID` BIGINT UNSIGNED NOT NULL,
88  `productID` BIGINT UNSIGNED NOT NULL,
89  `quantity` int(10) NOT NULL,
90  `cost` DECIMAL(10,2) NOT NULL,
91  CONSTRAINT `orderitems_ibfk_1` FOREIGN KEY (`orderID`) REFERENCES `orders` (`orderID`),
92  CONSTRAINT `orderitems_ibfk_2` FOREIGN KEY (`productID`) REFERENCES `products` (`productID`)
93) ENGINE=InnoDB;
94/*orderitems being a seperate table allows an order containing multiple different items to be easily recorded*/
95
96/*Table structure for table `juice` */
97
98CREATE TABLE `juice` (
99  `orderitemID` BIGINT UNSIGNED NOT NULL,
100  `juicename` varchar(255),
101  `juicequantity` int(10) NOT NULL,
102  CONSTRAINT `juice_ibfk_1` FOREIGN KEY (`orderitemID`) REFERENCES `orderitems` (`orderitemID`),
103  CONSTRAINT `juice_ibfk_2` FOREIGN KEY (`cupsize`) REFERENCES `products` (`productID`)
104) ENGINE=InnoDB;
105/*if a person orders multiple cups of juice, this table allows it to be easily differentiated which cup contains what juice*/