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

Exercise No. 20: Freight Bookings (dfm)

In this E/R schema a customer - who belongs to a certain customer group (private, business, etc.) - orders a freight booking. It consists of certain freight which belongs to a certain type of freight. The freight booking is carried out by an employee who works for a branch of a forwarding agent. This branch belongs to a certain forwarding agent. Shipping documents which include the date and the costs of the freight booking are attached. The time dimension consists of week, month and quarter.

Figure D.E.28.1 - freight bookings

Please identify the fact of interest and build the attribute tree. Let us assume that shipping documents shall not be part of the date warehouse. Its attributes date and costs are of interest and shall be included! 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.

Solution

Entity FREIGHT BOOKINGS 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.28.2 - The attribute tree

Customer, employee and freight are defined as dimensions, dimension week is introduced as a range of the date attribute.Costs becomes the only fact attribute.

The shipping documents sub-tree is grafted. The address of a customer and of a branch of a forwarding agent and the description of a freight cannot be used for aggregation and are tagged as a non-dimension attributes.

Figure D.E.28.3 - The fact schema

costs = SUM (FREIGHT BOOKINGS.costs)

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

Exercises List