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

Exercise No. 8: Toll Road (dfm)

This simplified E/R schema shows a vehicle driving on a toll road.

Each vehicle is of a certain type (e.g. various sizes of trucks, private cars, etc.) and can use different types of roads (interstates, tunnels, bridges, etc.).
The time dimension consists of day, month and year.

 

Figure D.E.54.1 - toll road

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.

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 vehicles and calculate the fact attribute toll.

Solution

Entity TRIP 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.54.2 - The attribute tree

Vehicle and road are defined as dimensions and the dimension day is introduced as a range of the date attribute. The attribute toll has to be calculated and becomes a fact attribute and number of vehicles is added.

The owner cannot be used for aggregation and is tagged as non-dimension attribute.

Figure D.E.54.3 - The fact schema

km = SUM (TRIP.km)

toll = SUM (TRIP.km * TRIP.price)

no. of vehicles = COUNT (TRIP)

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

Exercises List