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

Exercise No. 14: Bus Maintenance (log)

We will now build a lo­gi­cal model for the "bus main­ten­an­ce" case.

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

Table L.E.28.1 - bus main­ten­an­ce

"Costs" and "no. of ser­vices" are the key fi­gu­res in this examp­le. Each bus has an ad­di­tio­nal at­tri­bu­te "B_bu_a­ge", the age of the bus and each dri­ver has an ad­di­tio­nal at­tri­bu­te "D_dr_­exp", the dri­ver's ex­pe­ri­ence.

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 five ta­bles, a cen­tral fact table and four 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 two key fi­gu­res ("costs" and "no. of ser­vices") in this examp­le:

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

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

Fi­gu­re L.E.29.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.29.3

Fi­gu­re L.E.29.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 (nor­ma­li­zed in this case):

Fi­gu­re L.E.29.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