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

Parent Required Action on Parent Action on Child

Insert None. Get a parent.

Prohibit.

Modify key or
foreign key

Change children's foreign
key values to match new
value ( cascade update ).
Prohibit.

OK, if new foreign
key value matches
existing parent.
Prohibit.

Delete Delete children

( cascade delete ).
Prohibit.

None.

(a) Actions When the Parent Is Required

Child Required Action on Parent Action on Child

Insert Get a child.

Prohibit.

None.

Modify key or
foreign key

Update the foreign key of
(at least one) child.
Prohibit.

If not last child, OK.
If last child, prohibit
or find a replacement.

Delete None. If not last child, OK.

If last child, prohibit
or find a replacement.

Figure 6-28
Summary of Actions to
Enforce Minimum Cardinality (b) Actions When the Child Is Required

To discuss these rules, we will use the database design for storing data on several
companies shown in Figure 6-29. In this diagram, we have a 1:N, M-O relationship be-
tween COMPANY and DEPARTMENT and between DEPARTMENT and EMPLOYEE, and a
1:N, M-M relationship between COMPANY and PHONE_CONTACT. In the COMPANY-to-
DEPARTMENT relationship, COMPANY (on the 1 side of the relationship) is the parent entity

COMPANY DEPARTMENT

Figure 6-29
Database Design for Data on
Several Companies

CompanyName DepartmentName

City
Country
Volume

Budgetcode
MailStop
CompanyName (FK)

PHONE_CONTACT
Contact
CompanyName (FK)

EMPLOYEE

EmployeeNumber

EmployeeName
Phone
Email (AK1.1)
HireDate
ReviewDate
EmpCode
DepartmentName (FK)

PhoneNumber