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

Exercise No. 7: Car Sales (log)

We will now build a lo­gi­cal model for the "car sale" case.

We have three di­men­si­ons with the fol­lo­wing hier­ar­chi­cal struc­tu­re:

Table L.E.23.1 - car sale

"Price", "quan­ti­ty" and "re­ve­nue" are the key fi­gu­res in this examp­le. There are no ad­di­tio­nal at­tri­bu­tes.

Plea­se draw the Clas­sic Star and the Fact Con­stel­la­ti­on Sche­ma. Then draw the examp­le's di­men­si­on ta­bles nor­ma­li­zed and use them to build a Snowfla­ke Sche­ma!

So­lu­ti­on

This case's Clas­sic Star Sche­ma has four ta­bles, a cen­tral fact table and three di­men­si­on ta­bles. The lat­ter con­sist of a ge­ne­ra­ted key as well as a key and a text field for each hier­ar­chy (ex­cept the total ag­gre­ga­ti­on) and the level at­tri­bu­te. The fact table uses the ge­ne­ra­ted keys from the di­men­si­on ta­bles as a com­bi­ned key and con­tains three key fi­gu­res ("price", "quan­ti­ty" and "re­ve­nue") in this examp­le:

Fi­gu­re L.E.23.1 - The Clas­sic Star Sche­ma

The Fact Con­stel­la­ti­on Sche­ma for this case con­sists of three di­men­si­on ta­bles and 18 fact ta­bles (2x3x3):

Fi­gu­re L.E.23.2 - The Fact Con­stel­la­ti­on Sche­ma

Nor­ma­li­zing the di­men­si­on ta­bles com­ple­te­ly means that only the key of the next hig­her level is kept in each table. Plea­se note that the ta­bles for the total ag­gre­ga­ti­on level have been ne­glec­ted in Fi­gu­re L.E.23.3

Fi­gu­re L.E.23.3 - DT nor­ma­li­zed

The Snowfla­ke Sche­ma is a com­bi­na­ti­on of the Fact Con­stel­la­ti­on Sche­ma's fact ta­bles and the di­men­si­on ta­bles eit­her par­ti­tio­ned or nor­ma­li­zed: (in this case nor­ma­li­zed)

Fi­gu­re L.E.23.4 - The Snowfla­ke Sche­ma

This ex­er­ci­se is part of a case study: dfm - apa - log

Ex­er­ci­s­es List