1
23kviews
Design star & snowflake schema for "Hotel Occupancy" considering dimensions like Time, Hotel, Room, etc.
1 Answer
3
2.8kviews

Star scheme = For hotel occupancy.

Facts : No. of occupied rooms

No. of vacant rooms revenue

Dimensions : Time, Hotel, Room, Customer,

Dimension hierarchy :

Time : Data, day of week, day of month, week, month, quarter, half, year, holiday.

Hotel : Hotel name, hotel type, start rating, regime, city, state, zone, country.

Room : Room ID, room type, max occupant, No of beds, Room side, A/C non a/c, renovation year.

Customer : Customer ID, customer name, address, type of stay, check in, checkout, amount paid.

Star scheme contains fact table and dimension tables. All the facts are recorded in the fact table. All the hierarchies are grouped in dimension tables.

enter image description here

Snowflake schema has one or more normalized dimensions.

The hotel dimension in the above star schema can be normalized.

enter image description here

Please log in to add an answer.