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

Exercise No. 58: Real Estate I (dfm)

This simplified E/R schema shows an information system of a real estate company. A real estate is sold by the owner to the buyer which is arranged by the real estate agent. A lawyer may be included to adivise the deal.

The time dimension consists of day, month and year.

Figure D.E.62.1 - real estate

Please identify the fact of interest and build the attribute tree.
Attributes that cannot be used for aggregation in a meaningful way should be tagged as non-dimension attributes in the fact schema. All date of birth (dob) attributes should by pruned as they are of no interest.

Then derive the fact schema from the tree by defining dimensions, fact attributes and hierarchies and build the glossary for the fact attributes. Please include an additional fact attribute "no. of sales" counting the number of sales.

Solution

Entity SALE 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.62.2 - The attribute tree

Lawyer, agent, owner, buyer and real estate are defined as dimensions and the dimension day is introduced as a range of the date attribute. The dimension lawyer is tagged as optional.

The address of the real estate cannot be used for aggregation and is tagged as non-dimension attribute.

Figure D.E.62.3 - The fact schema

price = SUM (SALE.price)

no. of sales = COUNT (SALE)

Exercises List