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

Exercise No. 52: At The Movies (dfm)

In this simplified E/R schema a customer - who belongs to a certain group - watches a movie in a movie-hall. The movie and the movie-hall can be classified according to their type. During this period of time an employee can be present. The time dimension consists of day, week and month.

Figure D.E.49.1 - at the movies

Please identify the fact of interest and build the attribute tree. Some attributes are not of interest for this data warehouse: We are not interested in the date of birth of an employee. Other 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 customers.

Solution

Entity WATCHES 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.49.2 - The attribute tree

Customer, movie, movie-hall and employee are defined as dimensions, dimension day is introduced as a range of the date attribute. Number of customers is added as a fact attribute; remember number of customers is semi-additive!

The dimension employee is tagged as optional.

The title of a movie and the description of a customer group and a movie-hall type cannot be used for aggregation and are therefore tagged as non-dimension attributes.

Figure D.E.49.3 - The fact schema

number of customers = COUNT(WATCHES)

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

Exercises List