Task

$1\mathrm{.}1$ Task Description

Problem specification:

Design a data mart solution for the SUN hotel chain that has over $200$ hotels of different categories all over the world. On a daily basis in the OLTP system of each hotel, information on free, reserved, and unavailable rooms, booking agents and corresponding customers is stored. The hotel chain managers would like to build a data mart to analyse bookings versus checkouts and potential versus net revenue.

Information regarding bookings and payments are held in the OLTP booking system shown in Figure$-1,$ and the e$-$Ticket Data Source in Figure$-2.$ The primary keys are underlined and the foreign keys are followed by the sharp sign $($#$)$ and the name of the referenced table.

Figure$-1.$ Relational database schema for hotel room booking OLTP system

Figure$-2.$ Relational database schema derived from e$-$Ticket Data Source

The hotel chain managers would like integrate the two data sources $($hotel room booking OLTP system and e$-$Ticket Data Source $)$depicted in Figure$-1,$ and Figure$-2$ into a single data repository and critically analyze the daily, monthly and yearly income. Some frequent queries the managers would like to answer are the following.

$1.$ For each room band and month, derive the portion of rooms which are reserved, free, and unavailable.

$2.$ For each room band, derive the portion of rooms which are reserved. Associate a rank to each county according to the portion of checkout rooms for that county in a particular year with respect to all reserved rooms for that band. The band with the highest ratio of checkout rooms in a particular year must rank first.

$3.$ For each room band and concert, produce the cumulative income of $4-$star rooms

Design

The data mart will store information from $2015$ and $2019.$ The following cardinalities are known:

$$ Room Types: ~$3$

$$ Hotels ~$200$

$$ Band: $[1.\mathrm{.}5]$

$$ Concerts: $2000$

$$ Cities: ~$500$

$$ Counties: ~$50$

Considering the designed data mart and its cardinality, decide whether and which materialized views are convenient to improve response time of the frequent queries $($consider all the frequent queries $1-3).$

Task Deliverables

$1.$ Merge the database schemas depicted in Figure$-1$ and Figure$-2$ into a single schema $($integrated schema$)$ so that can store data from both the original databases. State any assumptions you may have considered while developing the integrated schema

$[12$ Marks$]$

$2.$ Based on the integrated relational schema, design a data warehouse model $($DFM$)$; in particular, the designed data mart must promptly answer to all the frequent queries $1-3.$

i$.$ Build the Attribute Tree from the integrated relational schema

ii$.$ Build the Fact Schema from Attribute Tree

$[12$ Marks$]$

$3.$ Map the DFM model to a logical model $($i$.$e$.$ relational$).$ Clearly display the main fact table$($s$)$ and dimensions.

$[6$ Marks$]$

$4.$ Implement the above logical as a working data warehouse schema, under MySQL$/$R$,$ or any other suitable DBMS$.$ Provide the DDL statements to create the proposed data$-$warehouse schema.

$[3$ Marks$]$

$5.$ Considering the designed data warehouse and its cardinalities, decide whether and which materialized views are convenient to improve response time of the frequent queries $($consider all the frequent queries$).$ Explain the reasons for your choices $[4$ Marks$]$

$6.$ Provide and implement a and implement a materialised view$($s$)$ to answer the directors frequent queries $1-3$

$1.$ For each room band and month, derive the portion of rooms which are reserved, free, and unavailable. $[4$ Marks$]$

$2.$ For each room band, derive the portion of rooms which are reserved. Associate a rank to each county according to the portion of checkout rooms for that county in a particular year with respect to all reserved rooms for that county. The county with the highest ratio of checkout rooms in a particular year must rank first. $[5$ Marks$]$

$3.$ For each room band and concert, produce the cumulative income of $4-$star rooms $[4$ Marks$]$

$$ Marks for $6\mathrm{.}1-6\mathrm{.}3,$ will be awarded as follows: $60\%$ for correct query formulation and $40\%$ for appropriate display of results

RoomTypes $($RoomTyplID$,$ TypeDesc$)$

RoomFacilities $($RoomFaciliyID$.$ FacilityDesc$)$

County $($CountyID$,$ CountyName$)$

Singer $($SingerID$,$ SingerForenames, SingerSurnames$)$

City $($CityID$,$ CityName, StateID II: State$)$

State $($StateID$,$ StateName, CountyID$)$: County$)$

Customer $($CustomerID$,$ CustomerForenames, CustomerSumames, CustomerEmail,

CityID II: City$).$

Room $($RoomID$,$ RoomTypeID#: RoomTypes, RoomFaciliticsID #: RoomFacilities$)$

Bookings $($CustomerID i: Customer, RoomID i: Room, DateBookingMade,

TimebookingMade, BookedStartDate, BookedEndDate, TotalPayementDueAmount$)$

Concert $($ConcertID$,$ ConcertName, CityID I: City, SingerID: Singer$)$

Buy $($CustomerID:: Customer, ConcertiD : : Concert, BuyDate, ConcertDate,

TotalPayementB$)$