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

Exercise No. 17: Western College Enrollment (dfm)

This simplified E/R schema shows a student enrolling in a course at the Western College. The student may live on-campus in a dorm and he may be a member of a greek organisation. The professor who teaches the class is part of a department which is part of a school.

The time dimension consists of term and year.

Figure D.E.59.1 - Western College Enrollment

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.

Then derive the fact schema from the tree by defining dimensions, fact attributes and hierarchies. Please include an additional fact attribute counting the number of enrollments.

Solution

Entity ENROLLMENT 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.59.2 - The attribute tree

Student, professor and course are defined as dimensions and the dimension term is introduced as a range of the date attribute. The attribute room and the attribute greek organisation of the dimension student are tagged as optional.

The address of the dorm, the research area of the professor and the course description cannot be used for aggregation and are tagged as non-dimension attribute.

Figure D.E.59.3 - The fact schema

no. of enrollments = COUNT (ENROLLMENT)

Exercises List