Studierende stehen vor dem LC und blicken lächelnd einer Kollegin mit einer Mappe in der Hand nach.

Exercise No. 27: General Hospital (dfm)

This simplified E/R schema shows a patient who gets examined by a doctor in the General Hospital.

The patient has an insurance which is underwritten by an insurance company. He may or may not (out-patient) lie in a hospital bed which is situated in a ward. The doctor making the examination works for a department which belongs to a hospital. If necessary a nurse can assist the doctor.
The time dimension consists of day, week and month.

Figure D.E.56.1 - General Hospital

Please identify the fact of interest and build the attribute tree.
Attributes that cannot be used for aggregation in a meaningful way should be tagged as non-dimension attributes in the fact schema (note: dob is short for date of birth).

Then derive the fact schema from the tree by defining dimensions, fact attributes and hierarchies and build a glossary for the fact attributes if there are any.

Solution

Entity EXAMINATION is chosen as the only fact. Its identifier becomes the root of the attribute tree. All other attributes or identifiers become nodes.

Figure D.E.56.2 - The attribute tree

Patient, doctor and nurse are defined as dimensions and the dimension day is introduced as a range of the date attribute. The dimension nurse and the attribute bed of the dimension patient are tagged as optional. The attribute costs becomes the only fact attribute.

The dob of the patient cannot be used for aggregation and is tagged as non-dimension attribute.

Figure D.E.56.3 - The fact schema

costs = SUM (EXAMINATION.costs)

Exercises List