Guest House

Data for this assessment is available:

• guesthouse data in MySQL format
• guesthouse data in Microsoft SQL Server format

Background

• Guests stay at a small hotel.
• Each booking is recorded in the table booking, the date of the first night of the booking is stored here (we do not record the date the booking was made)
• At the time of booking the room to be used is decided
• There are several room types (single, double..)
• The amount charged depends on the room type and the number of people staying and the number of nights
• Guests may be charged extras (for breakfast or using the minibar)
• Database Description | Easy Problems | Medium Problems | Hard Problems
• Guest House Assessment Sample Queries

Table booking

The table booking contains an entry for every booking made at the hotel. A booking is made by one guest - even though more than one person may be staying we do not record the details of other guests in the same room. In normal operation the table includes both past and future bookings.

+------------+--------------+---------+----------+-----------+---------------------+--------+--------------+
| booking_id | booking_date | room_no | guest_id | occupants | room_type_requested | nights | arrival_time |
+------------+--------------+---------+----------+-----------+---------------------+--------+--------------+
|       5001 | 2016-11-03   |     101 |     1027 |         1 | single              |      7 | 13:00        |
|       5002 | 2016-11-03   |     102 |     1179 |         1 | double              |      2 | 18:00        |
|       5003 | 2016-11-03   |     103 |     1106 |         2 | double              |      2 | 21:00        |
|       5004 | 2016-11-03   |     104 |     1238 |         1 | double              |      3 | 22:00        |
+------------+--------------+---------+----------+-----------+---------------------+--------+--------------+

Table room

Rooms are either single, double, twin or family.

+-----+-----------+---------------+
| id  | room_type | max_occupancy |
+-----+-----------+---------------+
| 101 | single    |             1 |
| 102 | double    |             2 |
| 103 | double    |             2 |
| 104 | double    |             2 |
| 105 | family    |             3 |
+-----+-----------+---------------+

Table rate

Rooms are charged per night, the amount charged depends on the "room type requested" value of the booking and the number of people staying:

+-----------+-----------+--------+
| room_type | occupancy | amount |
+-----------+-----------+--------+
| double    |         1 |  56.00 |
| double    |         2 |  72.00 |
| family    |         1 |  56.00 |
| family    |         2 |  72.00 |
| family    |         3 |  84.00 |
| single    |         1 |  48.00 |
| twin      |         1 |  50.00 |
| twin      |         2 |  72.00 |
+-----------+-----------+--------+

You can see that a double room with one person staying costs £56 while a double room with 2 people staying costs £72 per night

Note that the actual room assigned to the booking might not match the room required (a customer may ask for a single room but we actually assign her a double). In this case we charge at the "requirement rate".