ACIS5504- Project 2 Milestone 1 Management Questions and DW Design Solved

In this milestone you will initiate the project and create your snow-flake schema for the problem situation. This assignment provides experience working with concepts of the problem domain from the users perspective.  The first steps in the project are presented below.

This project will require more than 24 hours of work time to complete.

 

1  Write the three most important questions that management must answer to achieve a competitive advantage in the hotel market.

Submit the questions and a brief (1-2 pages) explanation/justification of why these are the most important questions.

2  Design a Data Warehouse Snowflake schema that is sufficient for addressing all three questions.

Submit an ER Model and data dictionary for your data warehouser Snowflake schema. You can also do a schema for each question that would be useful for explaining your design to users, but this is not required.

See this example of a star schema from the Hokie Hospital problem: HospitalDWSchema.vsdx

Example as pdf: Visio-HospitalDWSchemav2.pdf

3  Implement the fact and dimension tables from your data warehouse star or snowflake schema in MySQL or other DBMS.

Submit the MySQL, or other DBMS, DDL, i.e., the create table statements with proper key and foreign key constraints indicated, that are needed to implement your data warehouse star or snowflake schema.

You should also create the tables in the database, in Milestone 2 you will add data to the tables in Milestone 2.

 

Hokie Resorts Business Situation Description:

You work for a large corporation, Hokie Resort Hotels, that has just purchased 2 hotel and resort corporations each consisting of over 100 hotels.

Management would like you to design a data warehouse that allows them to achieve the most competitive advantage possible from these acquisitions. The two corporate databases and data dictionaries for them are available under milestone 2. You should also review them to understand the problem situation.

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