Design a data mart solution for the SUN hotel chain that has over 300 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. The hotel chain managers would like to know for each hotel the income and the percentage of (reserved, free, and unavailable) according to:
• geographical location of the hotel (country, province, and city)
• hotel category (5 stars, 4 stars, etc)
• room features (number of beds, TV, whirlpool bath, promotion)
The hotel chain managers would like to analyze the daily, holiday, monthly and yearly income. Some frequent queries the managers would like to answer are the following.
1. For each country and month, derive the portion of rooms which are reserved, free, and unavailable.
2. For each country, derive the portion of rooms which are reserved. Associate a rank to each country according to the portion of checkout rooms for that country in a particular year with respect to all reserved rooms for that country. The country with the highest ratio of checkout rooms in a particular year must rank first.
3. For each country and month, produce the cumulative income of 4-star hotels. Design The data mart will store information of 2000 and 2014. The following cardinalities are known:
Room Features: ~4
Hotels ~1800
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).

1. Design a data warehouse model (DFM) according to the DQML specification; in particular, the designed data warehouse must promptly answer to all the frequent queries ‘1-3’.
2. Map the DFM model to a logical model (i.e. relational). Clearly display the main fact table(s) and dimensions.
3. 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.
4. 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 reasons for your choices
5. Provide and implement a materialised view(s) to answer the directors frequent queries ‘i-iii’

i. For each country and month, derive the portion of rooms which are reserved, free, and unavailable.
ii. For each country, derive the portion of rooms which are reserved. Associate a rank to each country according to the portion of checkout rooms for that country in a particular year with respect to all reserved rooms for that country. The country with the highest ratio of checkout
rooms in a particular year must rank first.
iii. For each country and month, produce the cumulative income of 4-star hotels.