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

Exercise No. 23: Travelling (dfm)

In this E/R schema a person - who is classified by type and then by class - travels to a destination. This destination belongs to a certain country which again belongs to a certain continent. This travel is done by a certain mode of transport (car, train, plane, etc.) The time dimension consists of quarter, six-month and year.

Figure D.E.47.1 - Travelling

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

Solution

Entity TRAVELS 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.47.2 - The attribute tree

Person, destination and mode of transportation are defined as dimensions, dimension month is introduced as a range of the date attribute. Expenses becomes a fact attribute and number of persons is added. Number of persons is semi-additive, using the average operator along the date dimension.

The address of a person and and the description of a type and of a class cannot be used for aggregation and are tagged as non-dimension attributes.

Figure D.E.47.3 - The fact schema

expenses = SUM (TRAVELS.expenses)

number of persons = COUNT(TRAVELS)

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

Exercises List