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

Exercise No. 45: Pizza Service (dfm)

This E/R-schema is about a pizza service.

A customer who belongs to a certain customer group orders a pizza at a branch of a pizza service; this branch belongs to a certain pizza service. The pizza is made by a cook and then delivered by an employee. This person is responsible for a certain city district which belongs to a certain city. The time dimension consists of day and week.

Figure D.E.26.1 - pizza service

Please identify the fact of interest and build the attribute tree. No attributes have to be pruned or grafted. 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.

Please include an additional fact attribute "revenue".

Solution

Entity PIZZA 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.26.2 - The attribute tree

Customer, cook, branch of pizza service and employee are defined as dimensions, dimension day is introduced as a range of the date attribute.

The attributes price and quantity are selected as fact attributes and an additional key figure revenue is introduced.

The address of a customer becomes a non-dimension attribute.

Figure D.E.26.3 - The fact schema

price = SUM(PIZZA.price)

quantity = SUM(PIZZA.quantity)

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

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

Exercises List