Dimensional Modelling

Assignment

Mark: 10

Designing a Dimensional Database

The Hightel Hotel chain is a small chain with properties throughout Greater Accra,

Ashanti, Northern region, and Volta . They have a central database to store and

track guest reservations. In 2015, they put cafes in many of their hotels, called

“Café in the Hotel.” They have an order-tracking system that relays customer

orders from the wait staff to the kitchen.

Hightel Hotels would like to use the data they have collected to better understand

the performance of their hotels and cafes. They also have access to a database from

the online review site “HotelComplainer.com.”

Your task is to design two dimensional data marts using data from those three

databases. You will plan the star schema for each data mart by choosing the

dimensions, facts, and attributes from the data contained in those databases. The

relational schemas of those databases are on the following page.

NOTE: You will not actually come up with answers to these questions since you

have no data. You’ll just be designing the star schemas that can address these

questions about the business.

The questions to be addressed by each data mart are listed in the table below. You

should include only the data fields you need in each data mart, but the data marts

can contain some of the same data (i.e., the same fields can appear in both data

marts).

To complete the exercise, you’ll need to perform the following steps:

1) Identify the main business event for each data mart. This will be the fact.

Ask yourself, “What is the basic business event that generates the performance

metric (revenue)?”

2) Identify the attributes associated with the fact.

Ask yourself, “How is the business event quantified (measured)?”

3) Identify the dimensions and their attributes.

Ask yourself, “What data characterizes the various aspects of that business

event?”

4) Either sketch the star schema or make a list of the dimensions and the fact and

their attributes. You can use the examples in the slides as a guide.

Data Mart 1: Hotel Performance Data Mart 2: Restaurant

Performance

• During which month are the most rooms rented?

o Identify the “off season” (if any) for our hotels in Greater

Accra, Ashanti, Northern

region, and Volta region.

• Which hotel generates the most (non-restaurant) revenue?

• What is the average length of stay in hotels with 4.5 or more

stars?

• Do smokers stay longer than non-smokers?

• For a given hotel, how many customers come from outside

the regions of our hotels?

• Which hotel restaurant generates the most revenue?

• Do the best rated hotels generate more restaurant revenue?

• What is the most frequently ordered item in the Kumasi

metropolitan area?

Hotels Hotel_id Country_code Hotel_name Hotel_address Hotel_city Hotel_zipcode

Countries Country_code Country_currency Country_name

Hotel rooms Room_number Hotel_id Room_type Room_floor

Room types Room_type_code Room_standard_rate Room_description Smoking_YN

Room Bookings Booking_id Room_type_code Hotel_id Checkin_date Number_of_days Room_count

Guest Bookings Booking_id Guest_number

Guests Guest_number Guest_firstname Guest_lastname Guest_address Guest_city Guest_zipcode Guest_email

Hotel Amenities Lookup Characteristic_id Characteristic_description

Hotel Amenities Characteristic_id Hotel_id

Hotel Reservation Database

Ratings Rating_id Hotel_id Date_of_stay Star_rating Review_text

Hotels Hotel_id Country_code Hotel_name Hotel_address Hotel_city Hotel_zipcode

HotelComplainer Ratings Database

(totally external company)

Customer Customer_number Customer_name Customer_address Customer_city Customer_zipcode Customer_email

Hotel Rating Rating_id Customer_id Date_of_rating

Café in the Hotel Database (same company but database is not

connected to the hotel)

Customer Customer_number Customer_name Customer_address Customer_city Customer_zipcode

Order Order_number Customer_number Hotel_id Food_item_id Order_date Order_time Table_number

Food item Order number Food_item_id Order_date Order_time

Hotels Hotel_id Country_code Hotel_name Hotel_address Hotel_city Hotel_zipcode