Transcript Chapter 3 Tutorial

Chapter 4 Tutorial
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
o Dimension tables
o Fact table contains measures and keys to related
dimension tables
Cont.
1. Star schema: A fact table in the middle connected to a
set of dimension tables.
2. Snowflake schema: represents dimensional hierarchy
by normalizing the dimension tables.
•
save storage
•
reduces the effectiveness of browsing
3. Fact constellations: Multiple fact tables share
dimension tables
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.
1.
2.
3.
Star schema
Snowflake schema
Fact constellation schema
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.
Q3 cont.
Star Schema
time
time_key
day
day_of_the_week
month
quarter
year
doctor
Fact Table
time_key
doctor_id
doctor_id
doctor_name
phone #
address
gender
patient_id
patient
Charge
patient_id
patient_name
phone #
address
gender
Count
Measures
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.
Q5 cont.
Star Schema
date
date_id
day
month
quarter
year
spectator
Sales
Fact Table
date_id
spectator_id
location_id
game
game_id
game_name
description
producer
game_id
Charge
Count
spectator_id
spectator_name
phone #
address
Status
Charge rate
location
location_id
phone #
Street
city
province
country