Back to the Data Model
Subject: develope a star schema in the health domain.
give keys and non trivial functional dependencies of all tables
Question: Develop a star schema for a data warehouse in the health domain.
Give the keys and (non trivial) functional dependencies of all tables and
state which tables are in which normal form. Which tables are fact tables;
which tables are dimension tables?
What are the measures of the fact tables?
The health domain is characterized as follows:
Patients go to doctors.
Patients have a home address (city, country).
Likewise doctors have a work address.
Patients either have a private or a public insurance.
Furthermore, patients have an age.
Doctors keep records for all their patients.
These records keep information of the disease that a patient
has and when the patient got which kind of treatment.
Furthermore, the patient record keeps information on whether
each application of the treatment helped (score from 1 to 6; 6 is good)
and whether there were any side effects (score from 1 to 6; 6 = no side effects).
A treatment can either be a certain medication (e.g., Aspirin", Placebo", . . . )
or surgery (e.g., heart surgery", arthroscopy", . . . ).
Each treatment has a cost. Your data warehouse should be modeled so that it can
answer the following types of queries efficiently.
Q1 Which fraction of the patients tends to go to doctors in their home town.
Q2 Give the top 10 disease by the cost of their treatment.
Q3 For each disease, give the most effective (on average) treatment.
Q4 For each disease, give the treatment with the highest/lowest side-effect.
This analysis should take the age of the patients into account.
August 21st. 2011