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

Exercise No. 4: Courses (dfm)

This simplified E/R-schema contains data about employees attending courses.

Each employee belongs to a certain department and each course is of a certain type. The time dimension consists of month and year.

Figure D.E.13.1 - Employees attend courses

Please identify the fact of interest and build the attribute tree. No attributes have to be pruned or grafted.

Please also 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 attendances.

Figure D.E.13.1 - Employees attend courses

Please identify the fact of interest and build the attribute tree. No attributes have to be pruned or grafted.

Please also 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 attendances.

Solution

Entity ATTENDS 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.13.2 - The attribute tree

Employee and course are defined as dimensions, dimension month is introduced as a range of the date attribute. No. of attendances is added as the only fact attribute.

The date of birth of an employee and the title of a course cannot be used for aggregation and are tagged as non-dimension attributes.

Figure D.E.13.3 - The fact schema

no. of attendances = COUNT(ATTENDS)

Exercises List