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

Exercise No. 55: Opera by Wolfgang Amadeus Mozart (log)

This case deals with opera-singers and musicians performing in an opera by Wolfgang Amadeus Mozart.

The opera-singer sings in a certain register and belongs to the cast. The musician plays a certain instrument which belongs to an instrument group. This group is part of an orchestra. The opera is written by a certain composer. The performance is watched by a spectator and by a critic. The time dimension consists of day and week.

We have six dimensions with the following hierarchical structure:

Table L.E.37.1 - opera

There is only one key figure in this example "no. of performances". Each opera has an additional attribute "O_op_tit", the opera's title.

Please draw the Classic Star and the Fact Constellation Schema. Then draw the example's dimension tables partitioned and normalized and use them to build a Snowflake Schema!

Solution

This case's Classic Star Schema has seven tables, a central fact table and six dimension tables. The latter consist of a generated key as well as a key and a text field for each hierarchy (except the total aggregation) and the level attribute. The fact table uses the generated keys from the dimension tables as a combined key and contains no key figures in this example:

Figure L.E.37.1 - The Classic Star Schema

The Fact Constellation Schema for this case consists of six dimension tables and 36 fact tables (3x3x2x1x1x2):

Figure L.E.37.2 - The Fact Constellation Schema

Partitioning the Classic Star's dimension tables by the level attribute results in one table for each hierarchy level (in each dimension). All tables include the fields of the higher levels, the level attribute itself is omitted:

Figure L.E.37.3 - DT partitioned (left) and normalized (right)

Normalizing the dimension tables completely means that only the key of the next higher level is kept in each table. Please note that the tables for the total aggregation level have been neglected in Figure L.E.37.3

The Snowflake Schema is a combination of the Fact Constellation Schema's fact tables and the dimension tables either partitioned or normalized:

Figure L.E.37.4 - The Snowflake Schema

Exercises List