· 5 years ago · Apr 03, 2020, 12:01 PM
1DROP DATABASE IF EXISTS Restaurant_booking_system;
2CREATE DATABASE IF NOT EXISTS Restaurant_booking_system;
3
4USE Restaurant_booking_system;
5
6CREATE TABLE IF NOT EXISTS Customers(
7 ID INT NOT NULL AUTO_INCREMENT,
8 Firstname VARCHAR(50) NOT NULL,
9 Lastname VARCHAR(50) NOT NULL,
10 Phone VARCHAR(50) NOT NULL,
11 Email VARCHAR(50),
12 PRIMARY KEY(ID)
13);
14
15CREATE TABLE IF NOT EXISTS Bookings(
16 ID INT NOT NULL AUTO_INCREMENT,
17 Customer_ID INT NOT NULL,
18 Date DATETIME NOT NULL,
19 Seats INT NOT NULL,
20 PRIMARY KEY(ID),
21 FOREIGN KEY(Customer_ID) REFERENCES Customers(ID)
22);
23
24CREATE TABLE IF NOT EXISTS Tables(
25 ID INT NOT NULL AUTO_INCREMENT,
26 Table_number INT NOT NULL,
27 PRIMARY KEY(ID)
28);
29
30CREATE TABLE IF NOT EXISTS Table_bookings(
31 Table_ID INT NOT NULL,
32 Booking_ID INT NOT NULL,
33 FOREIGN key (Table_ID) REFERENCES Tables(ID),
34 FOREIGN key (Booking_ID) REFERENCES Bookings(ID),
35 PRIMARY KEY(Table_ID, Booking_ID)
36);
37
38INSERT INTO Customers (Firstname, Lastname, Phone, Email) VALUES
39('Aguistin', 'Baverstock', '647-565-4249', 'abaverstock0@devhub.com'),
40('Emilie', 'Bentinck', '373-360-2549', 'ebentinck1@theglobeandmail.com'),
41('Merrielle', 'Jiracek', '213-924-2985', 'mjiracek2@utexas.edu'),
42('Bernie', 'Swarbrick', '321-836-9666', 'bswarbrick3@ucoz.ru'),
43('Hildagard', 'Rowney', '807-439-9867', 'hrowney4@shareasale.com'),
44('Jarrid', 'Downs', '731-734-7412', 'jdowns0@ibm.com'),
45('Miof mela', 'Toll', '944-706-7980', 'mtoll1@loc.gov'),
46('Fanni', 'Rushworth', '999-173-4498', 'frushworth2@ovh.net'),
47('Courtnay', 'Bagwell', '908-161-8159', 'cbagwell3@webmd.com'),
48('Bondon', 'Whitesel', '531-193-3077', 'bwhitesel4@mayoclinic.com'),
49('Talya', 'Iannelli', '453-339-4115', 'tiannelli5@springer.com'),
50('Murvyn', 'Parzis', '102-818-4149', 'mparzis6@networkadvertising.org'),
51('Nigel', 'Jouhandeau', '544-331-3826', 'njouhandeau7@theglobeandmail.com'),
52('Iggie', 'Kincaid', '601-277-1007', 'ikincaid8@ca.gov'),
53('Clark', 'Shoreson', '795-412-9808', 'cshoreson9@nymag.com'),
54('Zora', 'Brewers', '338-366-4851', 'zbrewersa@wufoo.com'),
55('Iris', 'Oxberry', '371-408-3406', 'ioxberryb@mail.ru'),
56('Barnaby', 'Elverston', '532-497-9326', 'belverstonc@newyorker.com'),
57('Sherwood', 'Zealander', '534-335-2964', 'szealanderd@dyndns.org'),
58('Wilburt', 'Flucker', '195-181-5173', 'wfluckere@hc360.com'),
59('Kordula', 'Hyatt', '852-376-7308', 'khyattf@google.com'),
60('Jean', 'Ranshaw', '898-479-9672', 'jranshawg@free.fr'),
61('Tiebold', 'Hessay', '106-507-5841', 'thessayh@bloglines.com'),
62('Nisse', 'Tegeller', '225-644-1451', 'ntegelleri@youtube.com'),
63('Nikki', 'Inge', '605-658-9464', 'ningej@harvard.edu');
64
65INSERT INTO Bookings (Customer_ID, Date, Seats) VALUES
66(1, '2020-05-10 17:00:00', 2),
67(2, '2020-05-10 17:00:00', 10),
68(3, '2020-05-10 17:00:00', 5),
69(4, '2020-05-10 17:00:00', 9),
70(5, '2020-05-10 17:00:00', 10),
71(6, '2020-05-10 18:00:00', 2),
72(7, '2020-05-10 18:00:00', 10),
73(8, '2020-05-10 18:00:00', 5),
74(9, '2020-05-10 18:00:00', 9),
75(10, '2020-05-10 18:00:00', 10),
76(11, '2020-05-10 19:00:00', 2),
77(12, '2020-05-10 19:00:00', 10),
78(13, '2020-05-10 19:00:00', 5),
79(14, '2020-05-10 19:00:00', 9),
80(15, '2020-05-10 19:00:00', 10),
81(16, '2020-05-10 20:00:00', 2),
82(17, '2020-05-10 20:00:00', 10),
83(18, '2020-05-10 20:00:00', 5),
84(19, '2020-05-10 20:00:00', 9),
85(20, '2020-05-10 20:00:00', 10),
86(21, '2020-05-10 21:00:00', 2),
87(22, '2020-05-10 21:00:00', 10),
88(23, '2020-05-10 21:00:00', 5),
89(24, '2020-05-10 21:00:00', 9),
90(25, '2020-05-10 21:00:00', 10),
91(1, '2020-05-11 17:00:00', 2),
92(2, '2020-05-11 17:00:00', 10),
93(3, '2020-05-11 17:00:00', 5),
94(4, '2020-05-11 17:00:00', 9),
95(5, '2020-05-11 17:00:00', 10),
96(6, '2020-05-11 18:00:00', 2),
97(7, '2020-05-11 18:00:00', 10),
98(8, '2020-05-11 18:00:00', 5),
99(9, '2020-05-11 18:00:00', 9),
100(10, '2020-05-11 18:00:00', 10),
101(11, '2020-05-11 19:00:00', 2),
102(12, '2020-05-11 19:00:00', 10),
103(13, '2020-05-11 19:00:00', 5),
104(14, '2020-05-11 19:00:00', 9),
105(15, '2020-05-11 19:00:00', 10),
106(16, '2020-05-11 20:00:00', 2),
107(17, '2020-05-11 20:00:00', 10),
108(18, '2020-05-11 20:00:00', 5),
109(19, '2020-05-11 20:00:00', 9),
110(20, '2020-05-11 20:00:00', 10),
111(21, '2020-05-11 21:00:00', 2),
112(22, '2020-05-11 21:00:00', 10),
113(23, '2020-05-11 21:00:00', 5),
114(24, '2020-05-11 21:00:00', 9),
115(25, '2020-05-11 21:00:00', 10);
116
117INSERT INTO Tables (Table_number) VALUES
118(1),
119(2),
120(3),
121(4),
122(5);
123
124INSERT INTO Table_bookings (Table_ID, Booking_ID) VALUES
125(1, 1),(2, 2),(3, 3),(4, 4),(5, 5),
126(1, 6),(2, 7),(3, 8),(4, 9),(5, 10),
127(1, 11),(2, 12),(3, 13),(4, 14),(5, 15),
128(1, 16),(2, 17),(3, 18),(4, 19),(5, 20),
129(1, 21),(2, 22),(3, 23),(4, 24),(5, 25),
130(1, 26),(2, 27),(3, 28),(4, 29),(5, 30),
131(1, 31),(2, 32),(3, 33),(4, 34),(5, 35),
132(1, 36),(2, 37),(3, 38),(4, 39),(5, 40),
133(1, 41),(2, 42),(3, 43),(4, 44),(5, 45),
134(1, 46),(2, 47),(3, 48),(4, 49),(5, 50);
135
136
137/* --------------------------------------------------------ASSIGNMENTS-----------------------------------------------------------------
138(a) - Get a list of all tables in the restaurant (overview for the front-end)
139
140 SELECT Table_number FROM Tables;
141
142
143(b) - Get a list of all bookings for a given customer (when they arrive at the restaurant)ordered by date
144
145 SELECT bookings.*
146 FROM Bookings
147 INNER JOIN customers
148 ON bookings.Customer_ID=Customers.ID
149 WHERE Customer_ID=1
150 ORDER BY Date asc;
151
152
153(c) - Get a list of all bookings for a given tableID, including the customers for a specific date
154
155 SELECT bookings.*, customers.*
156 FROM Table_bookings
157 INNER JOIN bookings
158 ON bookings.id=table_bookings.booking_ID
159 INNER JOIN customers
160 ON customers.id=bookings.customer_id
161 WHERE table_bookings.table_ID=1 AND date(bookings.Date)='2020-05-11';
162*/