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

Purchases sharing the Time and Product dimension. Constellation schemas
may include both normalized and denormalized dimension tables.
We will discuss further star and snowflake schemas when we study logical
representation of hierarchies later in this chapter.
5.3 Relational Implementation of the Conceptual
Model
In Chap. 2 , we presented a set of rules that can be applied to translate an
ER model to the relational model. Analogously, we can define a set of rules
to translate the conceptual model we use in this topic (the MultiDim model)
into the relational model using either the star or snowflake schema. In this
section and the following one, we study such mapping rules.
Since the MultiDim model is based on the ER model, its mapping to the
relational model is based on the rules described in Sect. 2.4.1 , as follows:
Rule 1: A level L , provided it is not related to a fact with a one-to-one
relationship, is mapped to a table T L that contains all attributes of the
level. A surrogate key may be added to the table; otherwise, the identifier
of the level will be the key of the table. Note that additional attributes will
be added to this table when mapping relationships using Rule 3 below.
Rule 2: Afact F is mapped to a table T F that includes as attributes all
measures of the fact. Further, a surrogate key may be added to the table.
Note that additional attributes will be added to this table when mapping
relationships using Rule 3 below.
Rule 3: A relationship between either a fact F and a dimension level L ,or
between dimension levels L P and L C (standing for the parent and child
levels, respectively), can be mapped in three different ways, depending on
its cardinalities:
Rule 3a: If the relationship is one-to-one, the table corresponding to the
fact ( T F ) or to the child level ( T C ) is extended with all the attributes
of the dimension level or the parent level, respectively.
Rule 3b: If the relationship is one-to-many, the table corresponding to
the fact ( T F ) or to the child level ( T C ) is extended with the surrogate
key of the table corresponding to the dimension level ( T L ) or the parent
level ( T P ), respectively, that is, there is a foreign key in the fact or child
table pointing to the other table.
Rule 3c: If the relationship is many-to-many, a new table T B (standing
for bridge table) is created that contains as attributes the surrogate keys
of the tables corresponding to the fact ( T F ) and the dimension level
( T L ), or the parent ( T P ) and child levels ( T C ), respectively. The key of
the table is the combination of both surrogate keys. If the relationship