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

may appear in the database design of the tables and the relationship, and this is illustrated in
Figure 6-8(a). A similar technique is used on the foreign key LockerNumber in Figure 6-8(b).
Figure 6.8 shows the minimum cardinalities of the relationship as optional-optional (O-O),
and in this case either of the designs in Figure 6.8 will work, though the design team many prefer
one over the other. However, if the minimum cardinalities of the relationship are either manda-
tory-optional (M-O) or optional-mandatory (O-M), then one design will be greatly preferred, as
you will learn in the section on minimum cardinality design later in this chapter. Also, applica-
tion requirements may mean that one design is faster than the other.
To summarize, to represent a 1:1 strong entity relationship, place the key of one table in
the other table. Enforce the maximum cardinality by defining the foreign key as unique (or as
an alternate key).

1:N Relationships Between Strong entities
After the tables corresponding to the strong entities have been designed, a 1:N relationship
between the entities is represented by placing the primary key of the table on the one side into
the table on the many side as a foreign key. Recall from Chapter 5 that the term parent is used to
refer to the table on the one side, and the term child is used to refer to the table on the many side.
Using this terminology, you can summarize the design of 1:N relationships by saying, “Place the
primary key of the parent in the child as a foreign key.” This is illustrated in Figure 6-9.
Figure 6-9(a) shows an E-R diagram for the 1:N relationship between the COMPANY and
DEPARTMENT entities. The relationship is represented in the database design in Figure 6-9(b)
by placing the primary key of the parent (CompanyName) in the child (DEPARTMENT) as a
foreign key. Because parents have many children (the relationship is 1:N), there is no need to
make the foreign key unique.
For 1:N relationships between strong entities, that's all there is to it. Just remember: “Place
the primary key of the parent in the child as a foreign key.”

N:M Relationships Between Strong entities
Again, we must first create the database design tables from the data model entities and then
create the relationship. However, the situation for N:M relationships is more complicated.
The problem is that there is no place in either table in an N:M relationship in which to place
the foreign key. Consider the example in Figure 6-10(a), which shows a relationship between
COMPANY and PART that specifies which companies can supply which parts. A COMPANY
may supply many PARTs, and a PART may be supplied by many different COMPANY (ies).
Suppose we try to represent this relationship by placing the primary key of one table as a
foreign key in the second table, as we did for 1:N relationships. Say we place the primary key of
PART in COMPANY as follows:

C OMPANY ( CompanyName , City, Country, Volume, PartNumber )
PART ( PartNumber , PartName, SalesPrice, ReOrderQuantity, QuantityOnHand)

COMPANY DEPARTMENT

Figure 6-9
Transformation of a 1:N
Relationship Between Strong
Entities

CompanyName DepartmentName

City
Country
Volume

BudgetCode
MailStop

(a) 1:N Relationship Between Strong Entities

COMPANY DEPARTMENT

CompanyName DepartmentName

City
Country
Volume

BudgetCode
MailStop
CompanyName (FK)

(b) Placing the Primary Key of the Parent in the Child as a Foreign Key