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

Exercise No. 14: Bus Maintenance (dfm)

This E/R schema is about bus maintenance. A mechanic - who works for a certain garage - maintains a bus which is of a certain type. The driver of the bus is also included in this E/R schema because he/she plays an important role. The time dimension consists of month, quarter and year.

Figure D.E.29.1 - bus maintenance

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.

Solution

Entity MAINTAINS 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.29.2 - The attribute tree

Mechanic, bus and driver are defined as dimensions, dimension month is introduced as a range of the date attribute.Costs becomes a fact attribute, and number of services is added.

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

Figure D.E.29.3 - The fact schema

costs = SUM (MAINTAINS.costs)

number of services = COUNT (MAINTAINS)

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

Exercises List