Difference between revisions of "Guest House/zh"

From SQLZOO
Jump to: navigation, search
Line 3: Line 3:
 
*訂房時要決定房間的類別。
 
*訂房時要決定房間的類別。
 
*旅店有不同的房間類別,如single單人房、double雙人房等。
 
*旅店有不同的房間類別,如single單人房、double雙人房等。
*住宿費按房間類別、人數及天數決定。
+
*住宿費按房間類別、人數及夜數決定。
 
*可能有額外的收費,如早餐、享用房間的迷李酒吧等。
 
*可能有額外的收費,如早餐、享用房間的迷李酒吧等。
 
*[[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]]  
 
[[File:hotel.png]]
 
[[File:hotel.png]]
===Table booking===
+
===表格 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''' 貯存了旅店的每一個預訂。 每個預訂只記錄一個客人的資料,就算有多於一個客人入住同一晚同一房間,我們都不會記錄其他住客的資料。表格會貯存過去和未來的預訂資料。
  
 
  +------------+--------------+---------+----------+-----------+---------------------+--------+--------------+
 
  +------------+--------------+---------+----------+-----------+---------------------+--------+--------------+
 
  | booking_id | booking_date | room_no | guest_id | occupants | room_type_requested | nights | arrival_time |
 
  | 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        |
 
  |      5001 | 2016-11-03  |    101 |    1027 |        1 | single              |      7 | 13:00        |

Revision as of 06:26, 4 May 2017

Hotel.png

表格 booking

表格 booking 貯存了旅店的每一個預訂。 每個預訂只記錄一個客人的資料,就算有多於一個客人入住同一晚同一房間,我們都不會記錄其他住客的資料。表格會貯存過去和未來的預訂資料。

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