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

Exercise No. 2: Banking (dfm)

The following E/R schema shows customers asking for a loan at a bank. The time dimension consists of week, month and year.

 

Figure D.E.2.1 - banking

Please identify the fact of interest and build the attribute tree. No attributes have to be pruned or grafted, but some of them cannot be used for aggregation in a meaningful way. Those should be tagged as non-dimension attributes in the fact schema.

Then derive the fact schema from the tree by defining dimensions, fact attributes and hierarchies and build a glossary for the fact attributes if there are any.

Please include an additional fact attribute counting the number of loans.

Solution

Kein Bild hinterlegt

Entity ASKS FOR A LOAN is chosen as the only fact. Its identifier becomes the root of the attribute tree. All other attributes or identifiers become nodes.

Figure D.E.2.2 - The attribute tree

To build the fact schema customer and branch are defined as dimensions, dimension week is introduced as a range of the date attribute. Attribute costs/fee becomes a fact attribute and number of loans is added.

The address of a customer and the address of a branch cannot be used for aggregation and are tagged as non-dimension attributes.

Figure D.E.2.3 - The fact schema

costs/fee = SUM (ASKS FOR A LOAN.costs/fee)

no. of loans = COUNT (ASKS FOR A LOAN)

This exercise is part of a case study: dfm - apa - log

Exercises List