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

Exercise No. 25: Cab Ride (dfm)

This simplified E/R schema shows a customer riding in a cab.

Each customer belongs to a customer group and is driven by a driver who may be working for a certain cab association.
The time dimension consists of day, month and year.

Figure D.E.51.1 - cab ride

The attribute has luggage is a binary variable (parameter value 0 or 1, depending on whether the customer has luggage or not) that may be aggregated using the "average" operator along all dimensions.

Please identify the fact of interest and build the attribute tree. The attributes dob (date of birth) and last inspection should be pruned as they are not of interest.
Attributes that cannot be used for aggregation in a meaningful way 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.

Solution

Entity CAB RIDE 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.51.2 - The attribute tree

Driver, Customer and car are defined as dimensions and the dimension day is introduced as a range of the date attribute. The attributes km, price and has luggage become fact attributes. The attribute association is tagged as optional. The attributes last inspection and dob are pruned.

Figure D.E.51.3 - The fact schema

km = SUM (CAB RIDE.km)

price = SUM (CAB RIDE.price)

has luggage = AVG(CAB RIDE.has luggage)

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

Exercises List