Table L.E.52.1 - betting

"Amount" is the only key figure. Each customer has an additional attribute "C_cu_addr", the address. Each event has an additional attribute "E_ev_odds", the odds for the event.

Please draw the Classic Star Schema and create the dimension table "event". Then draw the Fact Constellation Schema, the dimension tables partitioned and normalized and use them to build a Snowflake Schema!

Figure L.E.52.1 - The Classic Star Schema

To create dimension table "event" we have to make up some events and event types, each being a record in the table. Events are assigned to event types by entering the categories in the corresponding fields of each "event"-record. The "type of event"-records contain a N/A sign (*) in the "event"-fields, the record representing the total aggregation has this sign in all fields, save the key and the level attribute:

Figure L.E.52.2 - The content of dimension table "event"

The Fact Constellation Schema for this case consists of five dimension tables and 12 fact tables (1x1x2x2x3):

Figure L.E.52.3 - 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.52.4 - 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.52.3.

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

Figure L.E.52.5 - The Snowflake Schema