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

Relationships with a Weak Non-ID-Dependent entity
As you learned in Chapter 5, a relationship between a strong entity and a weak but non-ID-
dependent entity behaves just the same as a relationship between two strong entities. The
relationship is a nonidentifying relationship, and, again, these relationships are character-
ized by their maximum cardinality. The previous discussion of 1:1, 1:N, and N:M relationships
between strong entities also applies to these types of relationships between a strong entity
and a weak but non-ID-dependent entity.
For example, what happens when the identifier of the parent of an ID-dependent entity
is replaced with a surrogate key? Consider the example of BUILDING and APARTMENT, in
which the identifier of APARTMENT is the composite of an apartment number and a building
identifier.
Suppose that the identifier of BUILDING is (Street, City, State/Province, Country). In this
case, the identifier of APARTMENT is (Street, City, State/Province, Country, ApartmentNumber).
This design can be improved by replacing the long BUILDING identifier with a surrogate key.
Suppose that we replace the key of BUILDING with BuildingID, a surrogate.
Now, with a surrogate key for BUILDING, what is the key of APARTMENT? When we
place the key of the parent in the child, we obtain (BuildingID, ApartmentNumber). But this
combination has no meaning to the user. What does an identifier of (10045898, '5C') mean to
a user? Nothing! The key became meaningless when Street, City, State/Province, and Country
were replaced by BuildingID in BUILDING.
We can improve the design by using the following principle: When replacing the identi-
fier of the parent of an ID-dependent entity with a surrogate key, replace the identifier of the
ID-dependent entity with its own surrogate key. The resulting table will be weak, but not
ID-dependent.

Relationships in Mixed entity Designs
As you might guess, the design of mixed entity patterns is a combination of strong entity and
ID-dependent entity designs. Consider the example of employees and skills in Figure 6-17.
Figure 6-17(a) is a copy of Figure 5-35. Here the entity EMPLOYEE_SKILL is ID dependent on
EMPLOYEE, but it has a nonidentifying relationship to SKILL.

EMPLOYEE SKILL EMPLOYEE SKILL

Figure 6-17
Transformation of the
Mixed Entity Pattern

EmployeeNumber Name EmployeeNumber Name

EmployeeName
Phone
Email
HireDate
ReviewDate
EmpCode

Description
SalaryRange

EmployeeName
Phone
Email
HireDate
ReviewDate
EmpCode

Description
SalaryRange

EMPLOYEE_SKILL
EmployeeNumber
SkillNumber

EMPLOYEE_SKILL
EmployeeNumber (FK)
SkillNumber

ProficiencyLevel
CourseTaken

ProficiencyLevel
CourseTaken
Name (FK)

(a) Data Model with Mixed Entity
Pattern from Figure 5-35

(b) Database Design for
Mixed Entity Pattern