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

The database design of the E-R model for the data model in Figure 6-17(a) is shown
in Figure 6-17(b). Notice that EmployeeNumber is both a part of the primary key of
EMPLOYEE_SKILL and also a foreign key to EMPLOYEE. The 1:N nonidentifying relation-
ship between SKILL and EMPLOYEE_SKILL is represented by placing the key of SKILL,
which is Name, in EMPLOYEE_SKILL. Note that EMPLOYEE_SKILL.Name is a foreign key
but not part of the primary key of EMPLOYEE_SKILL.
A similar strategy is used to transform the SALES_ORDER data model in Figure 6-18.
Figure 6-18(a) is a copy of the SALES_ORDER data model originally shown in Figure 5-33. In
Figure 6-18(b), the ID-dependent table, ORDER_LINE_ITEM, has SalesOrderNumber as part
of its primary key and as a foreign key. It has ItemNumber as a foreign key only.

By The Way The design transformation process for all HAS-A relationships can be sum-
marized by the phrase, “Place the primary key of the parent in the child
as a foreign key.” For strong entities, a 1:1 relationship can have either entity as the
parent, and therefore the foreign key can go in either table. For 1:N relationships, the
primary key of the parent goes in the child as the foreign key. For N:M relationships,
decompose the model into two 1:N relationships by defining an intersection table and
place the parent key of the parent in the child as a foreign key for each.
For identifying relationships, the primary key of the parent is already in the child, so
there is nothing more to do. For mixed relationships, on the identifying side, the primary
key of the parent is already in the child. On the nonidentifying side, place the primary
key of the parent in the child. In short, if you're going to memorize just a few rules for
creating relationships, the first one is “HAS-A: Place the primary key of the parent in the
child as the foreign key.”

Figure 6-18
Transformation of the
SALES_ORDER Pattern

CUSTOMER

CUSTOMER

CustomerID

SALESPERSON

CustomerID

SALESPERSON

LastName
FirstName
Address
City
State
Zip
Phone

SalespersonID

LastName
FirstName
Address
City
State
Zip
Phone

SalespersonID

SalespersonLastName
SalespersonFirstName
SalespersonCode

SalespersonLastName
SalespersonFirstName
SalespersonCode

SALES_ORDER

SALES_ORDER

SalesOrderNumber

SalesOrderNumber

Date
Subtotal
Tax
Total
CustomerID (FK)
SalespersonID (FK)

Date
Subtotal
Tax
Total

ITEM

ITEM

ItemNumber

ItemNumber

UnitPrice
Description

UnitPrice
Description

ORDER_LINE_ITEM
SalesOrderNumber
LineNumber

ORDER_LINE_ITEM
SalesOrderNumber (FK)
LineNumber

Quantity
UnitPrice
ExtendedPrice

Quantity
UnitPrice
ExtendedPrice
ItemNumber (FK)

(a) Data Model of SALES_ORDER
Pattern from Figure 5-33

(b) Database Design for the
SALES_ORDER Pattern