Database Concepts - Data Warehouse Systems: Design and Implementation - page 23

Orders OrderDetails

OrderID
OrderDate
RequiredDate
ShippedDate
...

OrderID
LineNo
UnitPrice
Quantity
Discount
SalesAmount

Fig. 2.5 Relationship translation of the schema in Fig. 2.2

Note that, in general, we can embed the key of T 1 in T 2 ,orconversely,
the key of T 2 in T 1 . The choice depends on the cardinality of the roles of
R . In Fig. 2.1 , assume the relationship Supplies has cardinalities (1,1) with
Products and (0,1) with Suppliers . Embedding ProductID in table Suppliers
may result in several tuples of the Suppliers relation containing null values
in the ProductID column, since there can be suppliers that do not supply
any product. Thus, to avoid null values, it would be preferable to embed
SupplierID in table Products .
Rule 4: Consider a regular binary one-to-many relationship type R relating
entity types E 1 and E 2 ,where T 1 and T 2 are the tables resulting from
the mapping of these entities. R is mapped embedding the key of T 2 in
table T 1 as a foreign key. In addition, the simple monovalued attributes
and the simple components of the monovalued complex attributes of R
are included in T 1 , defining the corresponding non-null constraints for the
mandatory attributes.
As an example, in Fig. 2.1 , the one-to-many relationship type Supplies
between Products and Suppliers is mapped by including the attribute
SupplierID in table Products ,asaforeignkey,asshowninFig. 2.4 .
Rule 5: Consider a regular binary many-to-many relationship type R
between entity types E 1 and E 2 , such that T 1 and T 2 are the tables
resulting from the mapping of the former entities. R is mapped to a table T
containing the keys of T 1 and T 2 , as foreign keys. The key of T is the union
of these keys. Alternatively, the relationship identifier, if any, may define
the key of the table. T also contains the simple monovalued attributes and
the simple components of the monovalued complex attributes of R and
also defines non-null constraints for the mandatory attributes.
In Fig. 2.1 , the many-to-many relationship type EmployeeTerritories
between Employees and Territories is mapped to a table with the same
name containing the identifiers of the two tables involved, as shown in
Fig. 2.4 .
Rule 6: A multivalued attribute of an entity or relationship type E is
mapped to a table T , which also includes the identifier of the entity or
relationship type. A referential integrity constraint relates this identifier