University Hospital Star Schema

Justifications for fields/attributes used:

Table: Doctor
Purpose:
1. To be able to identify which department a particular doctor is from and the
number of positions the doctor is currently holding so to be able to determine
the doctor’s productivity, performance or KPI.

2. To enable the tracking of the number of doctors in the hospital to ensure
there are sufficient numbers to serve each of the departments in the hospital.

Table: Patient
Purpose:
1. To be able to identify common reasons for patient’s admittance so that the
hospital can take the necessary precautions to be better prepared, such as adding
more hospital bed if patients’ usually are admitted for surgeries and require long
length of stay.

2. To be able to track and project estimateions based on the revenues/income generated from the patients' total bills.

Table: Time
Purpose:
1. To be able to analyze the peak time of patient admittance into the hospital based
on Hourly, Daily, Monthly and Yearly basis.

Table: Fact Table
Purpose:
1. To be able to see the analysis from all three of the other dimension tables, i.e.
Doctor, Patient and Time so that a suitable Charge rate could be determined that
will be fair to both doctors and patients. For example, doctors working on peak
hour may have to handle more patients and should be paid a higher rate.

2. To be able to conduct counts for all type of measures from each dimension table
so to be able to assist in determining the charges.