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

Exercise No. 16: Workout (dfm)

This simplified E/R schema shows an athlete doing a workout.

The athlete works out for a certain type of sport by doing different exercises which are specified in various types (weight lifting, endurance, etc.). The athlete may work out on his own or be advised by a personal coach. Each athlete concentrates on working out for only one type of sport.
The time dimension consists of day, week and month.

Figure D.E.55.1 - workout

Please identify the fact of interest and build the attribute tree.
Attributes that cannot be used for aggregation in a meaningful way should be tagged as non-dimension attributes in the fact schema (note: dob is short for date of birth).

Then derive the fact schema from the tree by defining dimensions, fact attributes and hierarchies. Please include an additional fact attribute counting the number of workouts.

Solution

Entity WORKOUT 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.55.2 - The attribute tree

Athlete, exercise and personal coach are defined as dimensions and the dimension day is introduced as a range of the date attribute. The dimension personal coach is tagged as optional and the fact attribute no. of workouts is added.

The address of the coach and the dob of the athlete cannot be used for aggregation and are tagged as non-dimension attribute.

Figure D.E.55.3 - The fact schema

no. of workouts = COUNT (WORKOUT)

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

Exercises List