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

and DEPARTMENT (on the N side of the relationship) is the child entity. In the DEPARMENT-
to-EMPLOYEE relationship, DEPARTMENT (on the 1 side of the relationship) is the parent
entity and EMPLOYEE (on the N side of the relationship) is the child entity. In the COMPANY-
to-PHONE_CONTACT relationship, COMPANY (on the 1 side of the relationship) is the par-
ent entity and PHONE_CONTACT (on the N side of the relationship) is the child entity.
actions When the Parent Is Required
When the parent is required, we need to ensure that every row of the child table has a valid,
non-null value of the foreign key. To accomplish this, we must restrict actions to update or
delete the parent's primary key and actions to create or modify the child's foreign key. Consider
actions on the parent first.
actions on the Parent Row When the Parent Is Required
According to Figure 6-28(a), when a new parent is created, nothing needs to be done. No
child row can yet be dependent upon the new row. In our example, we can create a new
DEPARTMENT and not worry about minimum cardinality enforcement in EMPLOYEE.
However, consider what happens if we attempt to change the value of an existing parent
row's primary key. If that row has children, then those children have a foreign key value that
matches the current primary key value. If the primary key of the parent changes, then any
existing children will become orphans; their foreign key values will no longer match a par-
ent row. To prevent the creation of orphans, either the foreign key values must be changed to
match the new value of the parent's primary key or the modification to the parent's primary
key must be prohibited.
In our example, if a DEPARTMENT attempts to change its DepartmentName from 'Info
Sys' to 'Information Systems', then any child rows in EMPLOYEE that have a foreign key value
of 'Info Sys' will no longer match a parent and will be orphans. To prevent orphans, either the
values of the foreign key in EMPLOYEE must also be changed to 'Information Systems' or the
update to the primary key in DEPARTMENT must be prohibited. The policy of propagating a
change from the parent's primary key to the children's foreign key is called cascading updates .
Now consider what happens when there is an attempt to delete a parent. If that row has
children, and if the deletion is allowed, then the children will become orphans. Hence, when
such a delete attempt is made, either the children must be deleted as well or the deletion must
be prohibited. Deleting the children along with the parent is called cascading deletions . In
our example, when an attempt is made to delete a DEPARTMENT, either all related rows in
EMPLOYEE must be deleted as well or the deletion must be disallowed.
By The Way Generally, cascading deletions are not chosen for relationships between
strong entities. The deletion of a DEPARTMENT row should not force the
deletion of EMPLOYEE rows. Instead, the deletion should be disallowed. To remove a
DEPARTMENT row, the EMPLOYEE rows would be reassigned to a new DEPARTMENT
and then the DEPARTMENT row would be deleted.
However, cascading deletions are almost always chosen for weak child entities.
For example, when you delete a COMPANY, you should always delete all of the weak
PHONE_NUMBER rows that depend on that COMPANY.
actions on the Child Row When the Parent Is Required
Now consider actions on the child row. If the parent is required, then when a new child row is
created, the new row must have a valid foreign key value. When we create a new EMPLOYEE,
for example, if DEPARTMENT is required, then the new EMPLOYEE row must have a valid
value for DepartmentName. If not, the insert must be disallowed. Usually there is a default
policy for assigning parents to a new row. In our example, when a new row is added to
EMPLOYEE, the default policy could be to add the new employee to the department named
'Human Resources'.
With regards to modifications to the foreign key, the new value must match a value of the
primary key in the parent. In EMPLOYEE, if we change DepartmentName from 'Accounting'