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

Exercise No. 33: Bus Tour (dfm)

The following E/R schema describes a bus tour.

A customer books a tour to a certain country. On this tour a bus - which is of a certain type - is used and an employee works on it. The time dimension consists of week, month and year.

Figure D.E.5.1 - Bus tour

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

Solution

Entity TOUR is chosen as the only fact. Its identifier becomes the root of the attribute tree. All other attributes or identifiers become nodes.

Kein Bild hinterlegt

Figure D.E.5.2 - The attribute tree

Bus, country, employee and customer are defined as dimensions, dimension week is introduced as a range of the date attribute. The attribute costs becomes a fact attribute and no. of tours is added.

The age of a bus and the address of a customer cannot be used for aggregation and are therefore tagged as non-dimension attributes.

Figure D.E.5.3 - The fact schema

fee = SUM(TOUR.fee)

no. of tours = COUNT(TOUR)

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

Exercises List