ERD

DBProblemDescriptionHiltonResorts-1 xAssignment-1 xAssignment1-Template.vsdx

Business Situation
Hilton Resort Hotels

Hilton Resort Hotels has grown rapidly over the past several years with the acquisition of Club Med chain and several very high luxury hotels worldwide. They have been adding more rooms, more extension wings on buildings, and whole new buildings creating large complexes of facilities serving a wide variety of needs. They desperately need support to keep track of rooms, facilities, reservations, guests, the usage of allied services, and billings. Such support would enable them to better serve their customers and guests and make more efficient use of their facilities and staff. The success of Hilton Resort Hotels is due in large measure to the team effort felt by all employees. They are collectively motivated by a desire to best serve the customer. However, as the operation grows this objective becomes threatened. The staff needs rapid access to complete, and current information to adequately serve the needs of their guests and hosts, and fairly respond to their demands.

There are several different kinds of rooms in a hotel complex. Most are set up for sleeping with one or two double beds (which may be regular size, extra long, queen size, or king size). Any given room could have two different types of beds. Some sleeping rooms have a minimal amount of extra space, some have an extra large open area with chairs and tables for meeting, etc., or for extra rollaway beds. All sleeping rooms have toilet and bath facilities, telephone, television, closets, and drawers. All sleeping rooms are designated as smoking or nonsmoking. A sleeping room is rated according to the number of sleeping guests it can accommodate (assuming adults, with adjustments made for small children).

A suite consists of a sleeping room with an additional, separated room for meeting or working. However, there is only a single hallway access door to the suite. If there are separate hallway access doors to adjacent rooms (whether sleeping rooms or meeting-only rooms), the rooms are considered two separate rooms and can be allocated separately.

Some rooms are meeting rooms only without sleeping facilities. Some meeting rooms have toilet facilities and some do not. Meeting rooms are rated according to their seating capacity assuming the guests would be seated around tables. Since an outside courtyard or a pool patio could also be the site of an event, they are treated as meeting rooms. A sleeping room may be adjacent to at most one meeting room. A meeting room may have one or two adjacent sleeping rooms. In addition, some sleeping rooms are adjacent to another sleeping room with a private-access door between them. Some meeting rooms are very large, such as a ballroom seating up to 10,000 persons. Some have large movable walls to divide them into multiple smaller rooms. All movable walls have a door. Each of the smaller rooms has a separate designation along with an indication of which rooms it is adjacent to. Two rooms are considered adjacent if there is a door between them.

Many of the rooms can serve multiple functions. For example, a suite could be assigned as a sleeping room or as a meeting room only. In a pinch, a meeting room could be used as a sleeping room (with rollaway beds), but only if it has toilet facilities. Some rooms also have a bed which folds up into the wall, turning it into a meeting room. A room with permanent beds cannot be assigned as a meeting room.

There are many more rooms in a hotel complex than is possible for any one person to remember. Moreover, at any given time a room may be undergoing renovation or reconstruction, or not be made up and cleaned. The computer must keep track of all room relationships and availabilities. Ideally and eventually, the computer system should enable the staff to see a graphical representation of the layout of a room and its facilities, and to zoom out to include adjacent rooms.

Each room has its own base rental rate per day. Sleeping rooms are allocated on a daily basis from 4 P.M. until 12 noon the next day. Earlier or later extensions are granted depending on when housekeeping gets the room ready or is available to get the room ready. Longer extensions entail a surcharge (a flat fee) on the daily rate. Meeting rooms are scheduled on an hourly basis throughout the day and evening. The usage times are generally designated as: breakfast, morning, lunch, afternoon, supper, evening, and sometimes night. Each noneating usage is charged at the half-day rate with discounts for multiple noneating usages. One noneating usage slot is granted free of charge for each paid eating usage of the meeting room. A paid bar is considered an “eating” usage of the room. Meeting room charges may also be reduced or waived based on the number of guests staying in a hotel as a result of the event being held in the meeting room.

Designating the location of the various facilities follows some pattern. A hotel complex consists of multiple buildings, each with multiple floors and multiple wings. Room numbers are assigned uniquely only within wings which have a variety of alphanumeric designations. Wings are designated uniquely within buildings, which are also named. A room number consists of one or two digits designating the floor or level, followed by two digits designating the room number on the floor. Wings differ by proximity to indoor or outdoor swimming pools, proximity to parking garage, and handicapped access. Often a whole floor of a wing is designated as nonsmoking. These factors are often important to parties making reservations.

Customers of a hotel include: guests, who stay overnight in sleeping rooms, and hosts, organizations or individuals who host meetings or meals in meeting rooms. It is sometimes necessary to distinguish the billing party who is responsible for making payment, from the party using the facilities. For a meeting room there is only one party responsible for making payment. However, if multiple guests stay in a sleeping room, the billing can be split up any way the guests agree, as long as there is some responsible party. Furthermore, a guest may switch from one room to another during the visit, and still receive a single (composite) bill for the visit. In some cases of a split billing, the room may be separately charged to a billing party, while any phone calls and other usages are charged to the guest occupying the sleeping room.

Much of the information to be retained in the computer system surrounds events. An event is a meeting or other gathering of persons requiring the use of one or more rooms. Often guests are at a hotel to attend an event and are to be so affiliated. Each event will have a host who is either a guest or a billed party (or both). An event has a duration, and several facilities will be used in conjunction with the event. A scheduled event may also record estimated attendance and an estimated number of guests.

Reservations are made up to two years in advance (or more for major functions) for meeting and sleeping rooms. When making a reservation, the customers express their needs and desires in terms of: bed type and size, number of guests, location, proximities, smoking or nonsmoking, etc.

Specific room assignments may not be made until the date draws close in time. (This is necessary because of the unpredictability of specific room availability due to breakdown, repair, and renovation. Even then, last minute changes are necessary due to unexpected breakdowns and the desire to accommodate changes in guest plans). Rooms are assigned based on a unique, sequential number assigned to each wing. Reservations are filled with the lowest numbered wing(s) and the lowest available room number(s) that meets the guests’ stated requirements. In making or changing reservations, the hotel staff must have accurate and current information on the actual and projected usage of all facilities.

Advance deposits are sometimes required depending on the qualifications of the customer. The qualifications are a function of their past history with the hotel chains, their cooperativeness, their flexibility in negotiating the usage of facilities, their promptness in making payment, etc. In an effort to be responsive to customer needs and demands, hotel staff is given considerable latitude in making decisions and assessing charges. This must be done in the light of the total relationship the customer has with the hotel chain – including past visits or events, and the complete spectrum or usage on the current visit. For example, when a customer checks into one of the public restaurants or the health club with a couple of guests, it is desirable for the staff to know that they are responsible for renting the ballroom for three days and hosting 200 guests who are paying to stay at the Hilton Hotel.

There must be a responsible party to be billed for all usage of facilities and services in a hotel. Charged services include sleeping room usage, meeting room usage, meals (or drinks) in meeting rooms or delivered to sleeping rooms or served in a public restaurant, telephone calls, general extra charges for room service (delivery and set up), or business services (photocopying, computer time or equipment rental, printing, fax), charges from retail shops in a complex or for services (such as a masseuse in the health club or racquetball court fees).

Each charged service is recorded on a transaction transmittal form (and should eventually be entered directly into the computer system). All charges must be recorded in a timely fashion so that an up-to-date rendering of a guest’s total bill is always available. For example, if a guest ate in the restaurant and made a phone call just before leaving, those charges should be reflected on the final billing when they check out just a few minutes later. Billed charges must be recorded with sufficient detailed information to enable the responsible party to verify the charges. The hotel also records expected or authorized charges, and ordered services in an effort to anticipate the level of accumulated charges and avoid any surprises. This information may be gathered as part of the reservations process.

The billed party is generally a hotel guest staying in one of the sleeping rooms. A billed party may also be an outsider, a local host living at home, or some organization. It is possible for a billed party to be responsible for the charges of multiple guests. Even if the responsible party is an organization, it is still necessary to have the name of an individual who is acting on behalf of (under the authority of) the organization.

In making reservations and during actual usage, it is important to keep track of who is assigned where and who is using what facilities. At all times it is important for the hotel staff to know how to contact a guest or host at the hotel, at least to the extent possible with all the information which is available to or voluntarily given to the hotel. This means keeping track of the sleeping room they are currently assigned to (if any), the organizational event (meeting, conference, sponsor, etc.) they are attending (if any), their own organizational affiliation, that is, the organization which they work for. Each guest receives a plastic card with a PIN. As they move about within the hotel, they have the option of running the card through readers indicating their current location. The card is used to gain access to sleeping and meeting rooms. As such, the hotel always knows when someone uses the card to gain access to a room. Readers are placed in various facilities such as every meeting room, restaurants, and health clubs. In this way, the guest may, at their discretion, use the card to inform the computer of their current location. Each card reader has two slots coming (entering) and going (leaving the facility). Cleaning and repair personnel also have magnetic cards to record when a particular room becomes available.

When it is important to guests that they can be contacted, the hotel can be kept informed through several facilities. Guests can leave outgoing messages on their telephone (as with a home answering machine) informing callers and the hotel of their whereabouts or when they will be available. At their option, a guest may request that information concerning their whereabouts be kept confidential – not to be revealed to any callers. Of course, hotel staff can always take a message and relay it to the guest, who can then decide whether or not to respond to the call.

Overview

The concepts of primary key, foreign key, and table representations are the foundation for understanding the newer data management techniques you will be learning in this course.

The best way to review is to create an entity-relationship model for a sample problem, then implement the resulting model in data-definition language of SQL. Thus, a sample problem is provided and you should review entity-relationship modeling techniques, listen to the recorded review of ER modeling, carefully analyze the Hilton hotel problem description, and begin to develop your ER model.

 You should complete the following activities:

1) Describe the key requirements and data required to support them for the Hilton Hotel presented in the situation described in the attached file: DBProblemDescriptionHiltonResorts-1 x

2) Create an entity-relationship model for the Hilton Hotel business situation using UML class notation. This Visio file contains a template that you can use: Assignment1-Template.vsdx

3) Explain why your entity-relationship model is in third-normal-form (or justify if not 3NF).

4) Create the SQL DDL that creates the 10 most important tables represented in your UML Class diagram.

5) Write the SQL Procedure that demonstrates that your design is capable of reporting all of the charges a guest has generated during a stay, i.e., the total amount and list of charges provided at checkout. (Note you do not have to implement your design in a DBMS to show that your SQL satisfies this requirement.)

6) Write the SQL to find the hotel that generates the most revenue.

7) Write the SQL to find the service that generates the most revenue for each hotel.

8) Write another interesting question your design can answer.

9) Write the SQL to answer your question from #8.

Directions
: Please begin this assignment soon, as it will take longer than you expect to work carefully through the problem description and create your initial ER model. You are to create a MS Word document that contains your description of the data needed, ER model (which should be created with Visio or other drawing tool with UML class diagram template), relational model associating attributes with classes, and your explanation of your database normal form.

You should create your DDL file using Notepad++, which will help you to check syntax and aids our grading process. When completed, please upload your Word document and DDL text file under the “Assignments” tab.  Please use a document naming convention that allows us to determine your name.  EXAMPLE: “LastName_FirstName_Assn1 x or .txt .sql”.

Submission Requirements 

 

· Briefly describe the data for the Hilton Hotel, 1 page. (5 points)

· Using UML Class Diagramming notation, create a complete model for all of the data needed for this business situation. Use MS Visio or other tool to draw your diagram. Do not add attributes to the diagram. Record your attributes as a relational model, i.e., class name and list of attribute names, e.g., Patient(C_Patient_PA, T_PatientFirstName_PA, d_BirthDate_PA). Use the following naming convention for your attributes: c_ indicates a key or foreign key attribute; d_ is a date variable; i_ indicates an integer, n_ indicates some numeric value with decimal places, and t_ indicates an attribute contains a text or string value. Also, add a two character class abbreviation, e.g., _pa, indicating the class the attribute is in the Patient table. Note any assumptions. (20 points)

· Evaluate your model to determine its normal form. Briefly, 1 paragraph, explain what normal form your model is in and why this is the appropriate normal form. Note any assumptions. (5 points)

· Write the create table SQL statements needed to implement the ten most important tables in your relational model. Be sure to include appropriate constraints for keys and foreign keys. Use Notepad++ to create a file with .sql extension for this part of the assignment and also submit that file to the assignment 1 folder. (20 points)

· Write the SQL Procedure that demonstrates that your design is capable of reporting all of the charges a guest has generated during a stay, i.e., the total amount and list of charges provided at checkout. (Note you do not have to implement your design in a DBMS to show that your SQL satisfies this requirement.) (10 points)

· Write the SQL to find the hotel that generates the most revenue. (10 points)

· Write the SQL to find the service that generates the most revenue for each hotel. (10 points)

· Write another interesting question your design can answer. (10 points)

· Write the SQL to answer your question from #8. (10 points)

 

Assessment

Your ER diagram, normal form explanation, and DDL will be evaluated for correctness and completeness. Correctness is how well your model represents the problem, e.g., the model includes the correct entities and relationships among them, along with appropriate attributes. Completeness is how well your model satisfies all of the requirements of the problem situation.

Customer
<>
parameter

Invoice
<>
parameter

Vendor
<>
parameter

Line
<>
parameter

supplies/isSuppliedBy
1..1
0..*
M1
M3
Product
<>
parameter

places/isPlacedBy
1..1
1..*
M1
M3
sells/isSoldBy
0..*
1..1
M1
M3
has/isOn
1..1
1..*
M1
M3