Chapter 4 Tutorial

1. Chapter 4 Tutorial

2. Modeling Data Warehouse • A data warehouse is based on a multidimensional data model which views data in the form of a data cube • A data cube allows data to be modeled and viewed in multiple dimensions • Dimension tables • Fact table contains measures and keys to related dimension tables

3. Cont. • Star schema: A fact table in the middle connected to a set of dimension tables. • Snowflake schema: represents dimensional hierarchy by normalizing the dimension tables. • save storage • reduces the effectiveness of browsing • Fact constellations: Multiple fact tables share dimension tables

4. Q3 • Suppose that a data warehouse consists of the three dimensions time, doctor, and patient, and the two measures count and charge, where charge is the fee that a doctor charges a patient for a visit. (a) Enumerate three classes of schemas that are popularly used for modeling data warehouses. • Star schema • Snowflake schema • Fact constellation schema

5. Q3 cont. (b) Draw a schema diagram for the above data warehouse using one of the schema classes listed in (a). Using a star schema.

6. Q3 cont. Star Schema doctor time doctor_id doctor_name phone # address gender time_key day day_of_the_week month quarter year patient patient_id patient_name phone # address gender Fact Table Measures

7. Q5 Suppose that a data warehouse consists of the four dimensions, date, spectator, location, and game, and the two measures, count and charge, where charge is the fare that a spectator pays when watching a game on a given date. Spectators may be students, adults, or seniors, with each category having its own charge rate. Draw a star schema diagram for the data warehouse.

8. Q5 cont. Star Schema spectator location date location_id phone # Street city province country spectator_id spectator_name phone # address Status Charge rate date_id day month quarter year game game_id game_name description producer Sales Fact Table