Difference between revisions of "Guest House/zh"

From SQLZOO
Jump to: navigation, search
(Created page with "*旅客住在一間小旅店。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 (w...")
 
Line 1: Line 1:
*旅客住在一間小旅店。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)
+
*每個預訂都被記錄在表格 '''booking''' 中,入住第一晚的日期會錄在此表格中。
*At the time of booking the room to be used is decided
+
*訂房時要決定房間的類別。
*There are several room types (single, double..)
+
*旅店有不同的房間類別,如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)
+
*可能有額外的收費,如早餐、享用房間的迷李酒吧等。
 
*[[Guest House|Database Description]] | [[Guest House Assessment Easy|Easy Problems]] | [[Guest House Assessment Medium|Medium Problems]] | [[Guest House Assessment Hard|Hard Problems]]
 
*[[Guest House|Database Description]] | [[Guest House Assessment Easy|Easy Problems]] | [[Guest House Assessment Medium|Medium Problems]] | [[Guest House Assessment Hard|Hard Problems]]
 
*[[Guest House Assessment Sample Queries]]  
 
*[[Guest House Assessment Sample Queries]]  

Revision as of 06:13, 4 May 2017

Hotel.png

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".