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

Exercise No. 21: Soccer Goal (dfm)

In the following E/R schema a player scores a goal. The player belongs to a certain soccer team and is of a certain nationality (parallel hierarchy); the soccer team plays in a certain national league and the nationality of the player tells us about the continent the player comes from. The goal itself is allowed by a referee and is watched by spectators. The time dimension consists of month, six-month and year.

Figure D.E.33.1 - soccer goal

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 goals.

Solution

Entity GOAL 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.33.2 - The attribute tree

Player, referee and spectator are defined as dimensions, dimension month is introduced as a range of the date attribute. Number of goals is added as a fact attribute.

The date of birth of a player cannot be used for aggregation and is therefore tagged as non-dimension attribute.

Figure D.E.33.3 - The fact schema

Number of goals = COUNT (GOAL)

This exercise is part of a case study: dfm - apa - log

Exercises List