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

Exercise No. 46: Lecture at the Boston University (dfm)

The following E/R schema describes a lecture at the Boston University.

Lectures are hold by a lecturer. This person is of a certain type (professor, assistant) and belongs to a certain department (parallel hierarchy). Students who are of a certain type attend a lecture. A lecture has a certain subject which belongs to a certain subject group and takes place in a certain lecture hall. This hall is part of a university building. The time dimension consists of day and week.

Figure D.E.30.1 - a lecture at the Boston University

Please identify the fact of interest and build the attribute tree. Some attributes cannot be used for aggregation in a meaningful way. Those 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 and build a glossary for the fact attributes if there are any.

Please include an additional fact attribute counting the number of lectures.

Solution

Entity LECTURE 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.30.2 - The attribute tree

Lecturer, subject, student and lecture-hall are defined as dimensions, dimension day is introduced as a range of the date attribute. No. of lectures is added as the only fact attribute.

The description of a subject cannot be used for aggregation and is therefore tagged as non-dimension attribute.

Figure D.E.30.3 - The fact schema

no. of lectures = COUNT(LECTURE)

Exercises List