A hotel keeps records about the reservations they have over the years. For each
reservation, the number of nights booked and the total price is recorded. In addition, they
keep information about the guests, rooms and reservation date, including the year, month
and day.
The following tables represent a sample of the information recorded about guest and room
Guest
Name
Address
Wilson Spencer
3720 Hickory Street, New Jersey, NJ
Evangeline Schroeder
4375 Brannon Avenue, Jacksonville, FL
Derek Colley
4839 Creekside Lane, Oakland, CA
Amarah Gates
1624 Werninger Street, Houston, TX
+Room
Room Number RoomType NoOfBeds
105
Queen
1
425
Queen
2
123
Single
1
578
Suite
2
\(
a. (20 pts)
This hotel wants to set up a data warehouse containing various information about the business.
A section of this data warehouse focuses on recording the reservations.
As previously indicated, it is essential to record the data about the rooms, guests and reservation
dates.
For each reservation, it is also important to record the number of nights booked and the total
price.
Based on all these information, draw a star schema for this specific part of data warehouse.
b. (10 pts)
Based on your start schema in a., write a SQL query displaying the guest name, room type,
day, month and number of night stayed for all reservations made in 2021.