Logical Data Warehouse Design - Data Warehouse Systems: Design and Implementation - page 129

provided it exists. The table may also include a discriminating attribute
that indicates the specific aggregation path of each member.

Sector

SectorKey
SectorName
Description
BranchKey
...

Customer

Branch

CustomerKey
CustomerId
CustomerName
Address
SectorKey (0,1)
ProfessionKey (0,1)
BranchKey
CustomerType
...

BranchKey
BranchName
Description
...

Profession

ProfessionKey
ProfessionName
Description
BranchKey
...

Fig. 5.8 Improved relational representation of the generalized hierarchy in Fig. 4.6

An example of the relations for the hierarchy in Fig. 4.6 is given in Fig. 5.8 .
The table Customer includes two kinds of foreign keys: one that indicates
the next specialized hierarchy level ( SectorKey and ProfessionKey ), which is
obtained by applying Rules 1 and 3b in Sect. 5.3 ; the other kind of foreign
key corresponds to the next joining level ( BranchKey ), which is obtained by
applying Rule 4 above. The discriminating attribute CustomerType ,whichcan
take the values Person and Company , indicates the specific aggregation path
of members to facilitate aggregations. Finally, check constraints must be
specified to ensure that only one of the foreign keys for the specialized levels
may have a value, according to the value of the discriminating attribute:
ALTER TABLE Customer ADD CONSTRAINT CustomerTypeCK
CHECK ( CustomerType IN ( ' Person ' , ' Company ' ))
ALTER TABLE Customer ADD CONSTRAINT CustomerPersonFK
CHECK ( (CustomerType != ' Person ' )OR
( ProfessionKey IS NOT NULL AND SectorKey IS NULL ) )
ALTER TABLE Customer ADD CONSTRAINT CustomerCompanyFK
CHECK ( (CustomerType != ' Company ' )OR
( ProfessionKey IS NULL AND SectorKey IS NOT NULL ) )

The schema in Fig. 5.8 allows one to choose alternative paths for analysis.
One possibility is to use the paths that include the specific levels, for example,
Profession or Sector . Another possibility is to only access the levels that are
common to all members, for example, to analyze all customers, whatever
their type, using the hierarchy Customer and Branch . As with the snowflake
structure, one disadvantage of this structure is the necessity to apply join
operations between several tables. However, an important advantage is the
expansion of the analysis possibilities that it offers.