Transforming Data Models into Database Designs - Database Processing: Fundamentals, Design, and Implementation - page 240

the parent tables are required—COMPANY_PART_INT requires both a COMPANY and PART.
The parents may or may not require an intersection table row, depending on application re-
quirements. In Figure 6-10(b), a COMPANY need not supply a PART, but a PART must be sup-
plied by at least one COMPANY.

By The Way The problem for the data models of N:M relationships between strong enti-
ties is that they have no direct representation. An N:M relationship must
always be decomposed into two 1:N relationships using an intersection table in the
database design. This is why products like MySQL Workbench are unable to represent
N:M relationships in a data model. These products force you to make the transformation
to two 1:N relationships ahead of time, during modeling. As stated in Chapter 5, how-
ever, most data modelers consider this requirement to be a nuisance because it adds
complexity to data modeling when the whole purpose of data modeling is to reduce
complexity to the logical essentials.

Relationships Using ID-Dependent entities
Figure 6-11 summarizes the four uses for ID-dependent entities. We have already described
the first use shown in Figure 6-11: the representation of N:M relationships. As shown in Figure
6-10, an ID-dependent intersection table is created to hold the foreign keys of the two tables
participating in the relationship, and identifying 1:N relationships are created between each
table and the intersection table.
The other three uses shown in Figure 6-11 were discussed in Chapter 5, and here we will
describe the creation of tables and relationships for each of these three uses.

association Relationships
As we discussed in Chapter 5, an association relationship is subtly close to an N:M relationship
between two strong entities. The only difference between the two types of relationships is that
an association relationship has one or more attributes that pertain to the relationship and not
to either of the entities. These attributes must be added to what would otherwise be the inter-
section table in the N:M relationship. Figure 6-12(a) shows the association relationship data
model created in Figure 5-22. In this example, the association of a company and a part carries
an attribute named Price.
The representation of such a relationship is straightforward: start by creating an intersec-
tion table that is ID-dependent on both of its parents, and then convert it to an association
table by adding the Price attribute to that table. The result for the example in Figure 6-12(a) is
the association table:

QUOTATION ( CompanyName , PartNumber , Price)

This table appears in the database design in Figure 6-12(b). Like all ID-dependent
relationships, the parents of an association table are required. The parents may or may

Figure 6-11
Four Uses for ID-Dependent
Entities

Four Uses for ID-Dependent Entities

Representing N:M relationships

Representing association relationships

Storing multivalued attributes

Representing archetype/instance relationships