There is only one key figure in this example ("number of flights"). Each captain has an additional attribute "C_c_dob", the captain's date of birth and each airplane has an additional attribute "A_a_age", the airplane's age.

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!

This case's Classic Star Schema has four tables, a central fact table and three 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 in this example only one key figure "no. of flights":

Figure L.E.1.1 - The Classic Star Schema

The Fact Constellation Schema for this case consists of three dimension tables and nine fact tables (1x3x3):

Figure L.E.1.2 - The Fact Constellation Schema

The example's dimension tables drawn partitioned and normalized show the parallel hierarchy in a more obvious way: (the tables for the total aggregation level have been neglected)

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

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.1.4 - The Snowflake Schema