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

Exercise No. 35: Hertz Car Rental Service (dfm)

This E/R-schema shows the Hertz car rental service.

Cars are rent to customers who can be classified into customer groups (private, business, etc.). There's an option to rent a driver for the car as well. The car is serviced by a garage and belongs to a branch of the Hertz car rental service. This branch is responsible for a certain "renting" area and also belongs to the nationalwide operating Hertz car rental service (parallel hierarchy). The time dimension consists of day and week.

Figure D.E.20.1 - the Hertz car rental service

Please identify the fact of interest and build the attribute tree. No attributes have to be pruned or grafted.

Then derive the fact schema from the tree by defining dimensions, fact attributes and hierarchies and build a glossary for the fact attributes.

Please include an additional fact attribute counting the number of rents.

Solution

Entity CARS 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.20.2 - The attribute tree

Customer, garage, branch of a car rental service and driver are defined as dimensions, dimension day is introduced as a range of the date attribute. The dimension driver is tagged as optional.

The attributes fee and driven miles are selected as fact attributes and an additional key figure no. of rents is introduced.

The address of a customer and the date of birth of a driver become non-dimension attributes.

Figure D.E.20.3 - The fact schema

fee = SUM(CAR.fee)

driven miles = SUM(CAR.driven miles)

no. of rents = COUNT(CAR)

Exercises List