PART: A Database Design and Implementation

Eagle Hotel International is a large hotel chain that is revamping the company web site and online reservation system. Your team has been hired to create the database back end for the site. Customers will visit the web site to search for hotels and make online reservations for rooms. If they are rewards customers, they can also make changes to their profile (name, address, email, etc.) and review account status, including recent stays, current reservations and point summary.

Eagle Hotel would like to store customer information, hotel information, and reservation information in the database as well as any specials or packages the hotels are offering. After an initial meeting with the representatives from Eagle Hotel, you have developed a list of business rules and specifications to begin the design of an ER model and data dictionary.

Instructions:

In this assignment, you will model the entities and attributes for Eagle Hotel taking care to model special attributes correctly using the business rules below. You do and should NOT model any relationships.

Business Rules

Eagle Hotel would like to store the following:

1.) Customers: customer ID, first and last name, address, phone, email, loyalty number (if they are in the rewards program) and credit card number.

2.) Hotels: hotel ID, name, address, phone, description, amenities.

3.) Room type: type, description

4.) Specials: name of special, a description of what it includes, the start date of the special and the end date.

5.) Rewards: There are four levels of rewards categories for hotels of varying level of classification (for example, a 4-star hotel provides more points per night than a 3-star). For each reward category, Eagle Hotel International would like to store the name of the category, the number of points earned per night stay and a description of the category.

6.) Reservations: confirmation number, check-in date, length of stay, price per day and total price.

7.) A customer can have multiple credit cards.

8.) Each hotel has multiple amenities.

9.) Names have a first name and last name while addresses have a street, city, state, and zip.

10.) The total price for a reservation is the length of stay multiplied by the price per day.

label PK for primary keys

-----------------------------------------------------------------------------------------------------------------------------------------------------------

PART: B

Eagle Hotel International is a large hotel chain that is revamping the company web site and online reservation system as described in the last assignment.

Instructions

In this assignment, you will model some of the relationships described by the business rules below for Eagle Hotel. The six entities involved in these relationships are: hotel, reservation, room type, customer, special and rewards category. As this assignment is about the relationships, the details regarding the attributes in each relationship can be omitted.

Business Rules

1. Each hotel has multiple room types (for example, 2 double beds, 1 king bed, king suite). A room type can be offered at multiple hotels.

2. A reservation corresponds to exactly one hotel. A hotel can have multiple reservations.

3. A customer can have multiple reservations. Each reservation corresponds to exactly one customer.

4. An individual special can be offered at multiple hotels. Hotels can offer multiple specials.

5. Each hotel must have exactly one rewards category and a rewards category corresponds to at least one hotel.