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

Exercise No. 30: Miami University Enrollment (log)

This cube stores data about the Miami University information system.

We have four dimensions with the following hierarchical structure (the dimension "student" features a parallel hierarchy at level 1):

Table L.E.61.1 - Miami University Enrollment

"Number of enrollments" is the only key figure in this exercise. Include the following attributes:

  • each course has an additional attribute "C_co_descr", the description of the course

  • each dorm has an additional attribute "S_do_addr", the address of the dorm

  • each professor has an additional attribute "P_pr_res", the research area of the professor

Please draw the Classic Star and the Fact Constellation Schema. Then draw the dimension tables partitioned and normalized!

Solution

This case's Classic Star Schema has five tables, a central fact table and four 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:

Figure L.E.61.1 - The Classic Star 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.61.2 - 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.61.2.

Exercises List