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

Exercise No. 42: Betting (dfm)

This simplified E/R schema shows an information system of a betting office.

A customer places a bet on a certain event using various modes of communication (internet, phone, etc.) with a company that has a number of branches in different cities.
The time dimension consists of day, month and year.

Figure D.E.52.1 - betting

Please identify the fact of interest and build the attribute tree. The attribute type of customer has to be pruned as this information is not needed in the data warehouse. Attributes that cannot be used for aggregation in a meaningful way 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 BET 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.52.2 - The attribute tree

Customer, branch, event and mode of comm. are defined as dimensions and the dimension day is introduced as a range of the date attribute. The attribute amount becomes a fact attribute.

The address of the customer and the odds of the event cannot be used for aggregation and is tagged as non-dimension attribute.

Figure D.E.52.3 - The fact schema

amount = SUM (BET.amount)

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

Exercises List