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

Exercise No. 7: Car Sales (dfm)

The following E/R schema is about selling cars. A car dealer who belongs to a car dealer association sells cars in a certain sales area which belongs to a certain city. The cars are imported by an exclusive importer and are of a certain type. The time dimension consists of week, month and quarter.

Figure D.E.23.1 - car sale

Please identify the fact of interest and build the attribute tree. The car dealer association and the exclusive importer are not of interest for this case. .

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 "revenue".

Solution

Entity SELLS 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.23.2 - The attribute tree

Car dealer and car are defined as dimensions, dimension week is introduced as a range of the date attribute. The attribute price and quantity become the fact attributes; revenue is added as an additional one.

The car dealer association and the exclusive importer sub-trees are pruned.

Figure D.E.23.3 - The fact schema

price = SUM (SELLS.price)

quantity = SUM (SELLS.quantity)

revenue = SUM (SELLS.quantity*SELLS.price)

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

Exercises List