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

Exercise No. 15: Restaurant Order (dfm)

This E/R-schema is about a restaurant order.

A customer orders a meal. The meal is of a certain type and can be part of the dish of the day. A waiter takes the order and a bill which contains the sum, the tax and the date of the order is attached. The time dimension consists of day, week and month.

 

Figure D.E.41.1 - restaurant order

Please identify the fact of interest and build the attribute tree. In this case we only need the following information of the bill: sum (fact attribute) and date. The tax and the bill itself is not of interest. We only need a classification of the "meal" by "dish of the day". However please bare in mind that the dish of the day is optional. 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, the numer of customers.

Solution

Entity ORDERS 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.24.2 - The attribute tree

Customer, waiter and meal are defined as dimensions, dimension day is introduced as a range of the date attribute. The attribute dish of the day is tagged as optional.

The bill sub-tree is grafted which remains us the attributes sum and date. Attribute tax and the classification of a meal by type is pruned. The attribute sum is selected as a fact attribute and an additional key figure number of customers is introduced. Attribute number of customers is semi-additive; that means it is not additive along all dimensions.

The description of a meal becomes a non-dimension attribute.

Figure D.E.24.3 - The fact schema

sum = SUM(ORDERS.sum)

no. of customers = COUNT(ORDERS)

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

Exercises List