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

Exercise No. 49: DVD (dfm)

In the following E/R schema a customer asks a store assistant for a DVD (digital video disc) via different modes of communication (phone, face to face, email, etc.). The DVD is of a certain type and is released by a certain artist (parallel hierarchy). The artist belongs to a certain music company. The time dimension consists of day, week and month.

Figure D.E.38.1 - DVD

Attribute solved is (at the fact instance level) a binary variable (parameter value 0 or 1, depending on whether the inquiry could be solved or not) that may be aggregated using the "average" operator along all dimensions. This allows us to analyze the ratio of inquiries that could be solved.

Please identify the fact of interest and build the attribute tree. 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 if there are any.

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

Solution

Entity ASKS FOR 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.38.2 - The attribute tree

Customer, store assistant, DVD and mode of communication are defined as dimensions, dimension day is introduced as a range of the date attribute. The attribute solved is selected as a fact attribute and an additional key figure no. of inquiries is introduced. Attribute solved is non-additive along all dimensions

The title of a DVD cannot be used for aggregation and is therefore tagged as non-dimension attribute.

Figure D.E.38.3 - The fact schema

solved = AVG(ASKS FOR.solved)

Number of inquiries = COUNT(ASKS FOR)

Exercises List