4NkpiJNs

1Team Name: Earth Programmers
2Members: Dhruv Soni, Achraf Derdak
3Project: Hotel Reservation System
4		 
5
6
7I- Database Schema (DDL to create database and relations along with constraint) 
8	 	 		
9# this relation will be archived when checkoutdate = to currentdate			
10CREATE TABLE `Reservation` (
11  `userid` int(11) NOT NULL,
12  `reservationid` int(11) NOT NULL AUTO_INCREMENT,
13  `checkindate` date DEFAULT NULL,
14  `checkoutdate` date DEFAULT NULL,
15  `roomnumber` int(11) NOT NULL,
16  PRIMARY KEY (`userid`,`reservationid`),
17  UNIQUE KEY `Res` (`reservationid`),
18  KEY `roomnumber` (`roomnumber`),
19  CONSTRAINT `reservation_ibfk_1` FOREIGN KEY (`roomnumber`) REFERENCES `Room` (`roomnumber`)
20) 
21
22
23----------------------------------------------------------------------------------------------------------------------------------------------------------
24					
25
26CREATE TABLE `User` (
27  `userid` int(11) NOT NULL,
28  `name` varchar(100) DEFAULT NULL,
29  `age` int(11) NOT NULL,
30  `admin` tinyint(1) DEFAULT '0',
31  PRIMARY KEY (`userid`),
32  CONSTRAINT `user_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `Reservation` (`userid`) ON DELETE CASCADE
33) 
34
35
36----------------------------------------------------------------------------------------------------------------------------------------------------------
37
38CREATE TABLE `Room` (
39  `roomnumber` int(11) NOT NULL AUTO_INCREMENT,
40  `roomtype` varchar(50) DEFAULT NULL,
41  `costPerNight` int(11) DEFAULT NULL,
42  PRIMARY KEY (`roomnumber`)
43) 
44
45
46------------------------------------------------------------------------------------------------------------------------------------------------------------
47
48
49
50CREATE TABLE `Invoices` (
51  `reservationid` int(11) DEFAULT NULL,
52  `staycost` double DEFAULT NULL,
53  `paid` tinyint(1) DEFAULT NULL,
54  KEY `reservationid` (`reservationid`),
55  CONSTRAINT `invoices_ibfk_1` FOREIGN KEY (`reservationid`) REFERENCES `Reservation` (`reservationid`) ON DELETE CASCADE
56) 
57
58------------------------------------------------------------------------------------------------------------------------------------------------------------
59
60CREATE TABLE `Parking` (
61  `parkingspot` int(11) NOT NULL,
62  PRIMARY KEY (`parkingspot`),
63  CONSTRAINT `parking_ibfk_1` FOREIGN KEY (`parkingspot`) REFERENCES `Room` (`roomnumber`)
64) 
65------------------------------------------------------------------------------------------------------------------------------------------------------------
66# This relation is the archive relation
67CREATE TABLE `Archive` (
68  `userid` int(11) NOT NULL,
69  `reservationid` int(11) NOT NULL,
70  `checkindate` date DEFAULT NULL,
71  `checkoutdate` date DEFAULT NULL,
72  `roomnumber` int(11) NOT NULL,
73  PRIMARY KEY (`userid`,`reservationid`)
74) 
75
76	
77
78II- 15 functional requirements and associated SQL programs
79
80-1) Check for vacancies (subquery)  DONE checkforvacancies
81SELECT roomnumber from Room
82WHERE roomnumber NOT IN (
83SELECT roomnumber 
84FROM reservation
85Where checkinDate = 'YYYY-MM-DD' and checkoutDate = 'YYYY-MM-DD’
86)
87
88
89
90- 2) user and admin can reserve a room (using a procedure addreservation, please refer to the procedure section)
91
92call addreservation ('dhruv', '2017-10-01','2017-11-01',2);
93DROP procedure if exists addreservation									
94CREATE PROCEDURE addreservation (IN username VARCHAR(50), checkin DATE, checkout DATE, room INT)	BEGIN										
95INSERT  INTO  Reservation (userid, checkindate,checkoutdate,roomnumber) 
96select userid,checkin, checkout, room
97from User
98where User.name = username;
99END//
100DELIMITER ;
101
102
103- 3) cancel reservation DOING THIS NOW - DHRUV
104
105DELETE FROM Reservation WHERE reservationid = “userinput”; 
106
107
108- 4) modify reservation
109update reservation
110set checkindate =”userinput” , checkoutdate = “userinput”
111
112
113- 5) list all the reserved rooms by specific user
114
115SELECT room 
116FROM reservation 
117Where userid = (select userid from user where name = “dhruv”);
118
119- 6) admin can check all users public void showAllUsers() {
120SELECT * FROM User
121
122-7)  admin can see all the reservations (ADMIN)
123SELECT * FROM Reservation
124 	
125
126- 8) admin can delete any reservation
127DELETE FROM Reservation WHERE reservationid = “userinput”; 
128
129- 9) admin can modify any reservation (ADMIN)
130update reservation
131Set,  checkindate =”admininput” , checkoutdate = “admininput”
132Where userid = (select userid from User where name = “adminprovidedname”)
133
134
135
136
137
138- 10) user can see room price
139Select costpernight 
140From Room 
141Where room = “userinput”
142
143- 11) admin can see name of users along reservation (Join operation)
144SELECT name, reservationid
145FROM  Reservation Join User on reservation.userid = user.userid;
146(ADMIN)
147
148- 12) admin can for check the number of guests on a given day
149Select * From reservation
150where “dateprovidedbyadmin” >= checkindate
151and “dateprovidedbyadmin” <= checkoutdate;
152(ADMIN)
153
154- 13) admin can see the number of reservations made in a single check in day ( aggregation operation) (ADMIN)
155
156Select count(*)
157from reservation
158Where checkinday = “dateprovidedbyadmin”
159
160
161- 14) number of reservations made by all users that made a specific number of reservations  ( groupby and having operation) void checkfornumberofreservations
162
163# in this example it’s 2
164select name 
165from user 
166where userid = (
167Select userid 
168From Reservation
169Group by userid
170Having count(*) >2);
171
172-15) Rooms that are more/less expensive than the selected room (correlated subquery) 
173# in this example we are using less
174
175select roomnumber 
176from room r1
177where costpernight in 
178(select costpernight 
179from room r2
180where r2.costpernight < r1.costpernight);
181
182
183
184
185
186
187
188- 16) admin can see the poeple that will check out after a certain admin provided date(aggregation operation)
189
190select name 
191from User, Reservation 
192where User.userid = reservation.userid 
193group by name
194Having count(*) > 1 and “serprovideddate” >= checkoutdateu
195
196- 17) Admin can see which users have made a reservation (Set Operation, intersection) void whoreserved()
197
198SELECT User.name
199FROM User INNER JOIN Reservation
200ON User.userid = Reservation.userid
201
202-18) Admin can check all the user with specific amount who haven’t paid (Set Operation, except) need to be done
203SELECT *
204FROM user 
205WHERE userId In
206((SELECT userid
207From reservation
208Where userid in
209((SELECT reservationid
210From Invoices
211Where staycost = “amount entred” 
212except
213(SELECT staycost 
214FROM invoices
215WHERE paid = true))));
216
217
218		 	 	 		
219III- Trigger
220							
221create trigger insertintogarage
222after insert on Room
223for each row
224begin
225Insert into Parking 
226values (new.roomnumber) ;
227End
228-----------------------------------------------------------------------------------------------------------------------------------------
229create trigger fireprocedure
230after insert on Reservation
231for each row
232begin
233Call archiveprocedure();
234End;
235-------------------------------------------------------------------------------------------------------------------------------
236create trigger insertintoinvoice
237after insert on reservation
238for each row
239begin
240Insert into invoices 
241values (new.rid,(SELECT DATEDIFF(day,new.checkindate,new.checkoutdate))*(select costpernight from Room where roomnumber = new.roomnuber),0) ;
242End
243
244IV- Procedure 
245	
246DROP procedure if exists addreservation									
247CREATE PROCEDURE addreservation (IN username VARCHAR(50), checkin DATE, checkout DATE, room INT)	BEGIN										
248INSERT  INTO  Reservation (userid, checkindate,checkoutdate,roomnumber) 
249select userid,checkin, checkout, room
250from User
251where User.name = username;
252END//
253DELIMITER ;
254
255-----------------------------------------------------------------------------------------------------------------------------------------
256
257CREATE DEFINER=`root`@`localhost` PROCEDURE `archiveprocedure`()
258BEGIN
259INSERT
260INTO    Archive
261SELECT  *
262FROM    Reservation
263WHERE   checkoutdate  = CURDATE()
264FOR UPDATE;
265DELETE
266FROM    Reservation
267WHERE   checkoutdate = CURDATE() ;
268END
269
270IV- Screenshots
271-1) user request checking vacancies
272      ---> Note customer object in main method will call its method checkforvacancies which will perform the query as shown in the second screenshot
273
274
275
276
277-2) user request insert User
278      ---> Note name DR.Kim is taken as input and it’s successfully inserted as shown in screenshot 2
279
280
281
282
283
284
285
286
287-3) user request number with specific amount of reservation
288      ---> Note: in this example the number entered is 2
289
290
291
292
293
294
295
296
297
298
299
300
301
302DROP PROCEDURE IF EXISTS archiveprocedure;
303 delimiter //
304CREATE DEFINER=`root`@`localhost` PROCEDURE `archiveprocedure`()
305BEGIN
306
307DECLARE _now DATETIME;
308SET _now := NOW();
309
310INSERT
311INTO    Archive
312SELECT  *
313FROM    Reservation
314WHERE   checkoutdate  = CURDATE()
315FOR UPDATE;
316
317DELETE
318FROM    Reservation
319WHERE   checkoutdate <= CURDATE() ;
320
321
322END
323//
324    delimiter ;