设计并实现酒店预订系统的数据库，并且编写一个简易的交互端程序。
Purpose of this project
Analyze, design, implement, and document a database system application. You
will use the methodology for database development learned in class. The system
must be implemented on a DBMS with any language as a host-language for the
application. The system must be menu-driven and include the basic
functionality described below.
The Online Hotel Reservation System
The following specifications are intended as a guide; they are not the
complete specifications. These are intended to be a basis for you to get
started in the right direction in designing your system. You as the designer
must analyze and decide what other details or features should be specified for
your system. Thus, individual group implementations will differ in terms of
design and implementation styles. Every group has to mention clearly in its
report what other specifications are assumed. However: (a) they should not
contradict the assumptions described below, and (b) they have to be clearly
stated in your report.
Database Requirements
Hulton is a chain of hotels that has many hotels all over the world. Its
reservation system is web-based. All reservations are managed via a website.
Hulton wants to build a database system for customers, rooms, reservation
transactions and customer reviews. You have been approached to help Hulton
with the database design and application development.
- The hotel chain has several hotels, which are identified by a number. For each hotel we need to store its location. Location comprises information about street address, country, state and zip. Each hotel can have multiple phone numbers.
- Each hotel has a number of rooms (at least 10). For each room we need to store information about the type of the room, number of people who can stay in the room, a textual description about the room, room number, floor number, and price per night. Examples of room types are standard, double, deluxe, and suite. The room description depends on the type of the room. Prices and descriptions of rooms may vary from one hotel to another for the same type of room. A room of a specific hotel is identified by its room number.
- Some of the rooms are offered for reservation at a discounted price for a continuous period of time at most once per year. We have to store the discount as a percentage of the regular price for those rooms and the start and end date for the discount period.
- Each hotel also offers different types of breakfasts (for example, continental, English, Italian, American, French). Different types of breakfast may have different prices. We need to store information about the type of breakfast, a textual description about the breakfast, and price per serving. Prices and descriptions of breakfasts may vary from one hotel to another for the same type of breakfast. A breakfast offered by a specific hotel is identified by its type.
- Every hotel provides some services (for example, parking, laundry, airport dropoff/pick up etc.). The database should store information about the service types offered and the cost for availing those services. The price for services may vary in different hotels. A service provided by a specific hotel is identified by its type.
- A customer has an ID, name, address, email, and phone number.
- A customer makes an online reservation using the hotel website. A reservation includes at least one room reservation in one or different hotels for the same or different checkin and check-out dates. Every reservation is identified by an invoice number. We also need to know the date on which the reservation was made, and the total amount paid by the customer for that reservation.
- Customers make online reservations using credit cards. A customer can make multiple reservations but can use only one credit card for each reservation. A credit card may or may not be in the name of the customer who makes the reservation. For every credit card, we need to store the card number, name on the card, expiration date, security code, billing address and the type of the card (for example: Visa, Master, Discover, Amex).
- A room reservation is done for a particular room by specifying the check-in date and check-out date for the room. The number of days for staying in that room is also of interest. Multiple room reservations can be done for a particular room in the same reservation (with different check-in dates, of course). The reservation system does not allow reservations for the same room with overlapping reservation periods.
- A room reservation may include breakfast. It can include multiple breakfast types and can have multiple orders for each breakfast type. We need to store the number of orders for each breakfast type included with a room reservation. The total number of orders for different breakfast types for a room reservation should not exceed the number of persons that can stay in that room.
- A room reservation may also contain one or more service types. Only services and breakfasts from the hotel where a room is reserved can be reserved.
- The total amount of a reservation is calculated by adding the sum of the costs of all breakfast orders, the sum of the costs for all the services ordered and the sum of the cost of the different rooms included in the reservation. Discounted prices for the rooms have to be taken into account.
- Customers write reviews for the hotels for which they made reservations. A review can be identified by a review id. For each review we also store a numeric rating (in the range of 10), and a textual comment. A review can only be one of the three categories: room review, service review and breakfast review. A customer can write multiple reviews of one or more categories. However each review is written by one customer. Depending on its category, a review rates either a particular room, or a particular service type or a particular breakfast type of a particular hotel. The system must ensure that customers can only write reviews for those rooms, breakfast types and service types they reserved.
Functional Requirements
The Hulton hotel chain is interested in an online application program
interface that is easy to use so that they do not have to spend time
unnecessarily to train their employees. Hulton would also like the program
interface to be easy for the customers. In particular it is interested in the
next three applications: Customer registration and management, Online
reservation, and Collection of statistics. The last application is crucial in
decision making by the analysts and managers. For instance, it will allow the
introduction of new room types, service types and breakfast types or
modification of certain services, room and/or breakfast types, the suggestion
of new offers to increase the profit of the hotels. As database and
application program designers you are expected to write a menu-based main
program which consists of the following three application programs for the
daily transactions of the hotels and is open to the inclusion of additional
functionality in the future. Note that many functions are left out in order to
reduce the size and the complexity of the project.
Registration and Management
Every customer has to register before being able to make a reservation. This
interface will help the customer register and manage their registration
information.
Online Reservation and Reviewing
The online reservation program will allow the customers to search a hotel by
country and state and then reserve rooms in that hotel depending on their
choice of room type. They can also avail discounted price for rooms if their
intended date of check-in to a room is within the discounted period of that
room. The customers can specify the number of breakfast orders for each
breakfast type they ordered in a room reservation. Different types of services
can be availed by the customers for every room reservation they make. The
system should allow a customer to write reviews for each room he reserved, for
each breakfast type he ordered and for each service type he availed in a
hotel.
Reservation statistics
This program provides statistics which are used by Hulton to analyze customer
satisfaction and need. In particular, this program should be able to make the
following computations:
- For a given time period (begin date and end date) compute the highest rated room type for each hotel.
- For a given time period (begin date and end date) compute the 5 best customers (in terms of money spent in reservations).
- For a given time period (begin date and end date) compute the highest rated breakfast type across all hotels.
- For a given time period (begin date and end date) compute the highest rated service type across all hotels.
