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

Exercise No. 3: Library (dfm)

The following E/R-schema shows students borrowing books.

Each book belongs to a certain subject group. The time dimension consists of week, month and year.

 

Figure D.E.12.1 - A library

Please identify the fact of interest and build the attribute tree. No attributes have to be pruned or grafted, but some of them 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.

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

Solution

Entity BORROWS 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.12.2 - The attribute tree

To build the fact schema student and book are defined as dimensions, dimension week is introduced as a range of the date attribute. Attribute fee becomes a fact attribute and no. of loans is added.

The address of a student and the title of a book cannot be used for aggregation and are therefore tagged as non-dimension attributes in the fact schema.

Figure D.E.12.3 - The fact schema

fee = SUM(BORROWS.fee)
no. of loans = COUNT(BORROWS)

Exercises List