1U3LHwFn

1Team Name: Earth Programmers
2Members: Dhruv Soni, Achraf Derdak
3
4Project: Hotel Reservation System
5
6
7
8		 
9I- Database Schema (DDL to create database and relations along with constraint) 
10	 	 		
11# this relation will be archived when checkoutdate = to currentdate			
12CREATE TABLE `Reservation` (
13  `userid` int(11) NOT NULL,
14  `reservationid` int(11) NOT NULL AUTO_INCREMENT,
15  `checkindate` date DEFAULT NULL,
16  `checkoutdate` date DEFAULT NULL,
17  `roomnumber` int(11) NOT NULL,
18  PRIMARY KEY (`userid`,`reservationid`),
19  UNIQUE KEY `Res` (`reservationid`),
20  KEY `roomnumber` (`roomnumber`),
21  CONSTRAINT `reservation_ibfk_1` FOREIGN KEY (`roomnumber`) REFERENCES `Room` (`roomnumber`)
22) 
23
24
25----------------------------------------------------------------------------------------------------------------------------------------------------------
26					
27
28CREATE TABLE `User` (
29  `userid` int(11) NOT NULL,
30  `name` varchar(100) DEFAULT NULL,
31  `age` int(11) NOT NULL,
32  `admin` tinyint(1) DEFAULT '0',
33  PRIMARY KEY (`userid`),
34  CONSTRAINT `user_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `Reservation` (`userid`) ON DELETE CASCADE
35) 
36
37
38----------------------------------------------------------------------------------------------------------------------------------------------------------
39
40CREATE TABLE `Room` (
41  `roomnumber` int(11) NOT NULL AUTO_INCREMENT,
42  `roomtype` varchar(50) DEFAULT NULL,
43  `costPerNight` int(11) DEFAULT NULL,
44  PRIMARY KEY (`roomnumber`)
45) 
46
47
48------------------------------------------------------------------------------------------------------------------------------------------------------------
49
50
51
52CREATE TABLE `Invoices` (
53  `reservationid` int(11) DEFAULT NULL,
54  `staycost` double DEFAULT NULL,
55  `paid` tinyint(1) DEFAULT NULL,
56  KEY `reservationid` (`reservationid`),
57  CONSTRAINT `invoices_ibfk_1` FOREIGN KEY (`reservationid`) REFERENCES `Reservation` (`reservationid`) ON DELETE CASCADE
58) 
59
60------------------------------------------------------------------------------------------------------------------------------------------------------------
61
62CREATE TABLE `Parking` (
63  `parkingspot` int(11) NOT NULL,
64  PRIMARY KEY (`parkingspot`),
65  CONSTRAINT `parking_ibfk_1` FOREIGN KEY (`parkingspot`) REFERENCES `Room` (`roomnumber`)
66) 
67------------------------------------------------------------------------------------------------------------------------------------------------------------
68# This relation is the archive relation
69CREATE TABLE `Archive` (
70  `userid` int(11) NOT NULL,
71  `reservationid` int(11) NOT NULL,
72  `checkindate` date DEFAULT NULL,
73  `checkoutdate` date DEFAULT NULL,
74  `roomnumber` int(11) NOT NULL,
75  PRIMARY KEY (`userid`,`reservationid`)
76) 
77
78	
79
80II- 15 functional requirements and associated SQL programs
81
82-1) Check for vacancies 
83SELECT roomnumber from Room
84WHERE roomnumber NOT IN (
85SELECT roomnumber 
86FROM reservation
87Where checkinDate = 'YYYY-MM-DD' and checkoutDate = 'YYYY-MM-DD’
88)
89
90
91
92- 2) user and admin can reserve a room (using a procedure addreservation, please refer to the procedure section)
93
94call addreservation ('dhruv', '2017-10-01','2017-11-01',2);
95DROP procedure if exists addreservation									
96CREATE PROCEDURE addreservation (IN username VARCHAR(50), checkin DATE, checkout DATE, room INT)	BEGIN										
97INSERT  INTO  Reservation (userid, checkindate,checkoutdate,roomnumber) 
98select userid,checkin, checkout, room
99from User
100where User.name = username;
101END//
102DELIMITER ;
103
104
105- 3) cancel reservation
106
107DELETE FROM Reservation WHERE reservationid = “userinput”; 
108
109
110- 4) modify reservation
111update reservation
112set checkindate =”userinput” , checkoutdate = “userinput”
113
114
115- 5) list all the reserved rooms by specific user
116
117SELECT room 
118FROM reservation 
119Where userid = (select userid from user where name = “dhruv”);
120
121- 6) admin can check all users
122SELECT * FROM User
123
124-7)  admin can see all the reservations
125SELECT * FROM Reservation
126 	
127
128- 8) admin can delete any reservation
129DELETE FROM Reservation WHERE reservationid = “userinput”; 
130
131- 9) admin can modify any reservation
132update reservation
133Set,  checkindate =”admininput” , checkoutdate = “admininput”
134Where userid = (select userid from User where name = “adminprovidedname”)
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
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
153
154
155- 13) admin can see the number of reservations made in a single check in day ( aggregation operation)
156
157Select count(*)
158from reservation
159Where checkinday = “dateprovidedbyadmin”
160
161
162- 14) number of reservations made by all users that made a specific number of reservations  ( groupby and having operation)
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
173- 15) admin can see the number of reservations made in a single check in day (aggregation operation)
174
175select name 
176from User, Reservation 
177where User.userid = reservation.userid 
178group by name
179Having 
180
181having max(age) <(select avg(age) from User);
182
183- admin can change room price
184
185- admin can check for overdue invoices.
186
187- users can pay outstanding invoices.
188
189		 	 	 		
190III- Trigger
191							
192create trigger insertintogarage
193after insert on Room
194for each row
195begin
196Insert into Parking 
197values (new.roomnumber) ;
198End
199-----------------------------------------------------------------------------------------------------------------------------------------
200create trigger fireprocedure
201after insert on Reservation
202for each row
203begin
204Call archiveprocedure();
205End;
206
207IV- Procedure 
208	
209DROP procedure if exists addreservation									
210CREATE PROCEDURE addreservation (IN username VARCHAR(50), checkin DATE, checkout DATE, room INT)	BEGIN										
211INSERT  INTO  Reservation (userid, checkindate,checkoutdate,roomnumber) 
212select userid,checkin, checkout, room
213from User
214where User.name = username;
215END//
216DELIMITER ;
217
218-----------------------------------------------------------------------------------------------------------------------------------------
219
220CREATE DEFINER=`root`@`localhost` PROCEDURE `archiveprocedure`()
221BEGIN
222INSERT
223INTO    Archive
224SELECT  *
225FROM    Reservation
226WHERE   checkoutdate  = CURDATE()
227FOR UPDATE;
228DELETE
229FROM    Reservation
230WHERE   checkoutdate = CURDATE() ;
231END
232
233
234
235
236IV- Screenshots
237-1) user request checking vacancies
238      ---> Note customer object in main method will call its method checkforvacancies which will perform the query as shown in the second screenshot
239
240
241
242
243
244-2) user request insert User
245      ---> Note name DR.Kim is taken as input and it’s successfully inserted as shown in screenshot 2
246
247
248
249
250
251
252
253
254-3) user request number with specific amount of reservation
255      ---> Note: in this example the number entered is 2
256
257
258
259
260
261
262
263
264
265
266SIDE NOTES: 
267
268AUTO INCREMENT RESERVATION ID
269FOR THE RESERVATION TABLE WE NEED A FORIEGN KEY TO REFERENCE USERID UnDER TABLE
270
271SET collation_connection = 'utf8_general_ci';
272then for your databases
273
274
275ALTER DATABASE HotelReservation CHARACTER SET utf8 COLLATE utf8_general_ci;
276  
277  ALTER TABLE Invoices  CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
278    ALTER TABLE Users  CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
279      ALTER TABLE Reservations  CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
280        ALTER TABLE Room  CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
281
282ALTER TABLE Room ADD costPerNight Int;
283
284https://stackoverflow.com/questions/7724824/move-rows-from-tablea-into-table-archive
285
286
287
28898down voteaccepted
289SELECT users.id, DATE_FORMAT(users.signup_date, '%Y-%m-%d') FROM users WHERE DATE(signup_date) = CURDATE()
290SET SQL_SAFE_UPDATES = 0;