Database Redesign - Database Processing: Fundamentals, Design, and Implementation - page 381

This discussion assumes that the required child constraint is enforced by triggers. If the
required child constraint is enforced by application programs, then all of those programs also
must be changed. Dozens of programs may need to be changed, which is one reason why it is
better to enforce such constraints using triggers rather than application code.

Changing Maximum Cardinalities
The only difficulty when increasing cardinalities from 1:1 to 1:N or from 1:N to N:M is preserv-
ing existing relationships. This can be done, but it requires a bit of manipulation, as you will
see. When reducing cardinalities, relationship data will be lost. In this case, a policy must be
created for deciding which relationships to lose.

Changing a 1:1 Relationship to a 1:N Relationship
Figure 8-4 shows a 1:1 relationship between EMPLOYEE and PARKING_PERMIT. As we dis-
cussed in Chapter 6, the foreign key can be placed in either table for a 1:1 relationship. The
action taken depends on whether EMPLOYEE is to be the parent entity in the 1:N relationship
or whether PARKING_PERMIT is to be the parent.
If EMPLOYEE is to be the parent (employees are to have multiple parking permits), then
the only change necessary is to drop the constraint that PARKING_PERMIT.EmployeeNumber
be unique. The relationship will then be 1:N.
If PARKING_PERMIT is to be the parent (e.g., if parking permits are to be allocated to
many employees, say, for a carpool), then the foreign key and appropriate values must be
moved from PARKING_PERMIT to EMPLOYEE. The following SQL will accomplish this:

/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-ALTER-TABLE-CH08-09 *** */
ALTER TABLE EMPLOYEE
ADD PermitNumber Int NULL;
/* *** SQL-UPDATE-CH08-02 *** */
UPDATE EMPLOYEE

SET EMPLOYEE.PermitNumber =

(SELECT PP.PermitNumber

FROM PARKING_PERMIT AS PP

WHERE PP.EmployeeNumber = EMPLOYEE.EmployeeNumber);

Once the foreign key has been moved over to EMPLOYEE, the EmployeeNumber column
of PARKING_PERMIT should be dropped. Next, create a new foreign key constraint to define
referential integrity. So multiple employees can relate to the same parking permit, the new
foreign key must not have a UNIQUE constraint.

Changing a 1:N Relationship to an N:M Relationship
Suppose that View Ridge Gallery decides that it wants to record multiple purchasers for a
given transaction. It may be that some of its art is co-owned between a customer and a bank or
a trust account, for example; or perhaps it may want to record the names of both owners when
a couple purchases art. For whatever reason, this change will require that the 1:N relationship
between CUSTOMER and TRANS be changed to an N:M relationship.

EMPLOYEE PARKING_PERMIT

Figure 8-4
The Employee-to-Parking_
Permit 1:1 Relationship

EmployeeNumber: NOT NULL PermitNumber: NOT NULL

Name: NOT NULL
Phone: NOT NULL
Email: NOT NULL

DateIssued: NOT NULL
LotNumber: NOT NULL
EmployeeNumber: NOT NULL (FK) (AK1.1)