· 5 years ago · Feb 02, 2020, 09:04 AM
1/*
2Project: HomeWork 2, Restaurants
3Author: Tejas Krishna Reddy
4
5*/
6
7/* Create the Database */
8CREATE DATABASE IF NOT EXISTS `hw2_restaurants`;
9USE `hw2_restaurants`;
10
11
12
13/* Start Creating tables */
14DROP TABLE IF EXISTS `reservations`;
15DROP TABLE IF EXISTS `sitdownrestaurant`;
16DROP TABLE IF EXISTS `foodcartrestaurant`;
17DROP TABLE IF EXISTS `takeoutrestaurant`;
18DROP TABLE IF EXISTS `recommendations`;
19DROP TABLE IF EXISTS `reviews`;
20DROP TABLE IF EXISTS `restaurants`;
21DROP TABLE IF EXISTS `companies`;
22DROP TABLE IF EXISTS `creditcards`;
23DROP TABLE IF EXISTS `users`;
24
25
26CREATE TABLE `companies` (
27 `CompanyName` varchar(255) NOT NULL,
28 `About` longtext,
29 PRIMARY KEY (`CompanyName`)
30);
31
32INSERT INTO companies(CompanyName, About) VALUES
33 ('TKR', 'A Company that focuses on making Indian Food super tasty and healthy!'),
34 ('Wellys', 'We look forward to offer cheap and affordable food to everyone');
35
36
37
38DROP TABLE IF EXISTS `restaurants`;
39CREATE TABLE `restaurants` (
40 `RestaurantId` int NOT NULL AUTO_INCREMENT,
41 `Name` varchar(255) NOT NULL,
42 `Description` longtext,
43 `Menu` varchar(255) DEFAULT NULL,
44 `Hours` timestamp(4) NULL DEFAULT NULL,
45 `Active` tinyint DEFAULT NULL,
46 `Cuisine` enum('African','American','Asian','European','Hispanic') DEFAULT NULL,
47 `Street1` longtext,
48 `Street2` longtext,
49 `City` varchar(255) DEFAULT NULL,
50 `State` varchar(255) DEFAULT NULL,
51 `Zip` int DEFAULT NULL,
52 `CompanyName` varchar(255) NOT NULL,
53 PRIMARY KEY (`RestaurantId`),
54 FOREIGN KEY (`CompanyName`) REFERENCES `companies`(`CompanyName`)
55);
56
57INSERT INTO restaurants(RestaurantId, Name, Description, Menu,
58 Hours, Active, Cuisine, Street1, Street2, City, State, Zip, CompanyName) VALUES
59 (1, 'Tejas', 'Awesome Indian Food', 'Dosa', "1999-01-01 01:01:01.0100", 0, 'African', 'ksks', 'ffd',
60 'ss', 'ff', 45556, 'TKR'),
61 (2, 'WENDYS', 'Cheap Food', 'chicken nuggets', "2019-01-15 11:11:11.0100", 1,
62 'Hispanic', 'Mass Ave', '3rd Building', 'Boston', 'MA', 02021, 'Wellys');
63/*select * from restaurants */
64
65
66
67DROP TABLE IF EXISTS `users`;
68CREATE TABLE `users` (
69 `UserName` varchar(255) NOT NULL,
70 `Password` varchar(255) DEFAULT NULL,
71 `FirstName` varchar(255) DEFAULT NULL,
72 `LastName` varchar(255) DEFAULT NULL,
73 `Email` varchar(255) DEFAULT NULL,
74 `Phone` varchar(255) DEFAULT NULL,
75 PRIMARY KEY (`UserName`),
76 UNIQUE(`UserName`)
77);
78
79INSERT INTO users(UserName, Password, FirstName, LastName, Email, Phone) VALUES
80 ('TejasKReddy', 'Tejtkreddy@33', 'Tejas', "KrishnaReddy", 'tejas.krishnareddy1415@gmail.com', '6692039691'),
81 ('Samridhi', 'Sam@1234', 'Samridhi', "Yash", 'sam@gmail.com', '1800-666-666');
82
83
84DROP TABLE IF EXISTS `creditcards`;
85CREATE TABLE `creditcards` (
86 `CardNumber` bigint(16) NOT NULL,
87 `ExpirationDate` date DEFAULT NULL,
88 `UserName` varchar(255) DEFAULT NULL,
89 PRIMARY KEY (`CardNumber`),
90 FOREIGN KEY(UserName) REFERENCES users (UserName) ON DELETE CASCADE
91);
92
93/*
94INSERT INTO creditcards(CardNumber, ExpirationDate, UserName) VALUES
95 ('1234567887654321', '2019-11-11', 'TejasKReddy'),
96 ('9876543212345678', '2020-11-12', 'Samridhi');
97*/
98
99
100DROP TABLE IF EXISTS `recommendations`;
101CREATE TABLE `recommendations` (
102 `RecommendationId` int NOT NULL AUTO_INCREMENT,
103 `UserName` varchar(255) DEFAULT NULL,
104 `RestaurantId` int DEFAULT NULL,
105 PRIMARY KEY (`RecommendationId`),
106 FOREIGN KEY(RestaurantId) REFERENCES restaurants (RestaurantId),
107 FOREIGN KEY(UserName) REFERENCES users (UserName)
108);
109
110INSERT INTO recommendations(RecommendationId, UserName, RestaurantId) VALUES
111 ('1', 'TejasKReddy', '1'),
112 ('2', 'Samridhi', '2');
113
114DROP TABLE IF EXISTS `reviews`;
115CREATE TABLE `reviews` (
116 `ReviewID` int NOT NULL AUTO_INCREMENT,
117 `Created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
118 `Content` longtext,
119 `Rating` decimal(5,0) DEFAULT NULL,
120 `UserName` varchar(255) DEFAULT NULL,
121 `RestaurantId` int DEFAULT NULL,
122 PRIMARY KEY (`ReviewID`),
123 FOREIGN KEY(RestaurantId) REFERENCES restaurants (RestaurantId),
124 FOREIGN KEY(UserName) REFERENCES users (UserName)
125);
126
127
128INSERT INTO reviews(ReviewID, Created, Content, Rating, UserName, RestaurantId) VALUES
129 ('1', '1999-01-01 01:01:01.0100', 'Awesome Awesome food, mind blowing', '1.3', 'TejasKReddy', '1'),
130 ('2', '2020-12-12 01:01:01.0100', 'Waiters are so rude!', '4.9', 'Samridhi', '2');
131
132
133DROP TABLE IF EXISTS `sitdownrestaurant`;
134CREATE TABLE `sitdownrestaurant` (
135 `RestaurantId` int NOT NULL,
136 `Capacity` int DEFAULT NULL,
137 PRIMARY KEY (`RestaurantId`),
138 FOREIGN KEY (RestaurantId) REFERENCES restaurants (RestaurantId) ON DELETE CASCADE
139);
140
141INSERT INTO sitdownrestaurant(RestaurantId, Capacity) VALUES
142 ('1', '47'),
143 ('2', '74');
144
145DROP TABLE IF EXISTS `foodcartrestaurant`;
146CREATE TABLE `foodcartrestaurant` (
147 `RestaurantId` int NOT NULL,
148 `Licensed` tinyint DEFAULT NULL,
149 PRIMARY KEY (`RestaurantId`),
150 FOREIGN KEY (RestaurantId) REFERENCES restaurants (RestaurantId) ON DELETE CASCADE
151);
152
153INSERT INTO foodcartrestaurant(RestaurantId, Licensed) VALUES
154 ('1', '0'),
155 ('2', '1');
156
157DROP TABLE IF EXISTS `takeoutrestaurant`;
158CREATE TABLE `takeoutrestaurant` (
159 `RestaurantId` int NOT NULL,
160 `MaxWaitTime` int DEFAULT NULL,
161 PRIMARY KEY (`RestaurantId`),
162 FOREIGN KEY (RestaurantId) REFERENCES restaurants (RestaurantId) ON DELETE CASCADE
163);
164
165INSERT INTO takeoutrestaurant(RestaurantId, MaxWaitTime) VALUES
166 ('1', '30'),
167 ('2', '40');
168
169DROP TABLE IF EXISTS `reservations`;
170CREATE TABLE `reservations` (
171 `ReservationId` int NOT NULL AUTO_INCREMENT,
172 `Start` timestamp(4) NULL DEFAULT NULL,
173 `End` timestamp(4) NULL DEFAULT NULL,
174 `Size` int DEFAULT NULL,
175 `UserName` varchar(255) DEFAULT NULL,
176 `RestaurantId` int DEFAULT NULL,
177 PRIMARY KEY (ReservationId),
178 FOREIGN KEY(UserName) REFERENCES users (UserName) ON DELETE CASCADE,
179 FOREIGN KEY(RestaurantId) REFERENCES sitdownrestaurant (RestaurantId) ON DELETE CASCADE
180);
181
182INSERT INTO reservations(ReservationId, Start, End, Size, UserName, RestaurantId) VALUES
183 ('1','1999-01-01 01:01:01.0100', '1999-01-01 01:01:01.0100', '60', 'TejasKReddy', '1'),
184 ('2', '2019-04-03 01:01:01.0100','2009-11-11 01:01:01.0100', '50', 'Samridhi','2');