Table L.E.33.1 - soccer goal

There is only one key figure in this example, "no. of goals". Each player has an additional attribute "P_pl_dob", the player's date of birth.

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!

Figure L.E.33.1 - The Classic Star Schema

The Fact Constellation Schema for this case consists of four dimension tables and 15 fact tables (1x5x1x3):

Figure L.E.33.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.33.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.33.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.33.4 - The Snowflake Schema