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

Exercise No. 53: Opera by Giuseppe Verdi (dfm)

In this simplified E/R schema an opera-singer and a musician perform in an opera by Giuseppe Verdi. The opera-singer sings in a certain register and belongs to the cast. The musician plays a certain instrument which belongs to an instrument group. This group is part of an orchestra. The performance is watched by a spectator - who is of a certain type (subscriber, adult, student, etc.) - and by a critic. The time dimension consists of day and week.

Figure D.E.27.1 - an opera by Giuseppe Verdi

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 if there are any.

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

Solution

Entity PERFORMS 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.27.2 - The attribute tree

Opera-singer, spectator, musician, opera and critic are defined as dimensions, dimension day is introduced as a range of the date attribute. No. of performances is added as the only fact attribute.

The title of an opera cannot be used for aggregation and is therefore tagged as a non-dimension attribute.

Figure D.E.27.3 - The fact schema

no. of performances = COUNT(PERFORMS)

Exercises List