Database Modeling and Design - Database Systems - page 86

Associative Entities: Associative entities represent M:M relationships among two or
more entities. For example, from Figure 5-2b , the relationships ProjWork , SuppSched ,
SuppItems , and ItemStruct would be implemented as associative entities. In a college
database with kernel entities Program and Course (among others of course), and a M:M
relationship between them, the associative entity representing the relationship could
be ProgramStructure , which would include foreign keys referencing Program and
Course respectively. Note that the associative entity is the intersecting relation in the
implementation of a M:M relationship (review chapter 3, section 3.5.4).
Subtype/Super-type Entities: If we have two entities E1 and E2, such that a record
of E1 is always a record of E2, and a record of E2 is sometimes a record of E1, then E1 is
said to be a subtype of E2. The existence of a subtype implies the existence of a super-
type: To say that E1 is a subtype of E2, is equivalent to saying that E2 is the super-type of
E1. For illustration, review Figure 5-3 .

5.4.2 Surrogates
Recall that the concept of a surrogate was first introduced in chapter 3 (section 3.5.4).
In understanding the X-R model, the role of surrogates is very important; we therefore
revisit the concept here. Surrogates are system controlled primary keys, defined to avoid
identification of records (tuples) by user-controlled primary keys. They are also often
used to replace composite primary keys. Two consequences of surrogates arise (both of
which can be relaxed with a slight deviation from the XR model which does not enforce
surrogates, E-relations and P-relations as mandatory):

• Primary and foreign keys can be made to always be

non-composite.

• Foreign keys always reference corresponding E-relations

(more on this shortly).

Surrogates provide two significant benefits:

• In some traditional DBMS suites, composite primary keys are not

allowed; surrogates are therefore imperative.

• Even if allowed by the DBMS, composite primary keys are

sometimes cumbersome; surrogates are useful replacements in
these circumstances.

To demonstrate the usefulness of surrogates in simplifying database model and
ultimate design (with respect to avoiding cumbersome composite primary keys), let us
suppose that we want to track purchase orders and their related invoices. The related
entities that we would need to track are Supplier , InventoryItem , PurchaseOrder and
PurchaseInvoice . These are not all included in Figure 5-2 ; however they are represented
in figure 3-4b of chapter 3 (please review). By following through on the E-R model, or
by applying normalization principles of chapter 4, we may construct a tentative set of
normalized relations as illustrated in Figure 5-5a . Notice how potentially cumbersome
the composite keys would be, particularly on relations PurchaseOrdDetail and
PurchaseInvDetail . However, by introducing surrogates as illustrated in Figure 5-5b , we
minimize the need to use complex composite keys.