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

Exercise No. 11: Supply (dfm)

This E/R schema describes a supply process.

An employee who may or may not belong to the procurement department orders a supply from a certain supplier. This supply consists of items which are of a certain type. Each supply has a supply ticket added. The time dimension consists of month, quarter and year.

Figure D.E.6.1 - Supply

Please identify the fact of interest and build the attribute tree. Only the date of the supply is needed, any other information of the ticket can be neglected. Some attributes 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 supplies.

Solution

Entity SUPPLY 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.6.2 - The attribute tree

Supplier, item and employee are defined as dimensions, dimension month is introduced as a range of the date attribute.The attribute procurement department of the dimension employee is tagged as optional. The attribute quantity and unit price become fact attributes and number of supplies is added.

The ticket sub-tree is grafted. The date of birth of an employee, the size of an item and the address of a supplier cannot be used for aggregation and are therefore tagged as non-dimension attributes.

Figure D.E.6.3 - The fact schema

quantity = SUM(SUPPLY.quantity)

unit price = SUM(SUPPLY.unit price)

no. of supplies = COUNT(SUPPLY)

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

Exercises List