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

Because there is no consistent means of documenting these actions in commercial data-
base design products, we will use the templates in Figure 6-28 to document our decisions.
Figure 6-38 summarizes the relationships in the View Ridge database design.
Because all tables have surrogate keys, there is no need for any update cascade behavior for
any parent. However, some update actions on child tables must be restricted. For example, once a
WORK (child) is assigned to an ARTIST (parent), it is never to change to another parent. Because
this database is used to record purchases and sales, View Ridge management never wants to
delete any data that are related to a transaction. From time to time, they may remove prior year's
data in bulk, but they will do that using bulk data transfer and not as part of any application.
Hence, any CUSTOMER, WORK, or ARTIST row that is related to a TRANS row is never
to be deleted. Note, however, that rows of CUSTOMERs who have never made a purchase and
rows of ARTISTs whose works have never been carried in the gallery can be deleted. If either
a CUSTOMER or ARTIST is deleted under these circumstances, the deletion will cascade to
rows in the intersection table CUSTOMER_ARTIST_INT.
Finally, referential integrity actions are necessary for obtaining a parent WORK when a
TRANS record is created and a parent ARTIST when a WORK record is created. In both cases,
the policy will be for the application program to provide the ID of the required parent at the
time the WORK or TRANS record is created.
All these actions are documented in Figure 6-39, where each part is based on the template for
required children shown in Figure 6-28(a). Note that there is no diagram for the CUSTOMER-to-
TRANS relationship, because that is an O-O relationship without a required parent (or child).

Minimum Cardinality enforcement for the Required Child
As shown in the summary in Figure 6-38, TRANS is the only required child in the database de-
sign in Figure 6-37. The actions to enforce that required child are documented in Figure 6-40,
which is based on the template in Figure 6-28(b).

Figure 6-39
Actions to Enforce Minimum
Cardinality for Required
Parents

ARTIST
Is Required
Parent

Action on ARTIST
(Parent)

Action on WORK
(Child)

Insert None. Get a parent.

Modify key or
foreign key

Prohibit—ARTIST uses a
surrogate key.

Prohibit—ARTIST uses a
surrogate key.

Delete Prohibit if WORK exists—

data about a work and its
related transaction is never
deleted (business rule).
Allow if no WORK exists
(business rule).

None.

(a) For the ARTIST-to-WORK Relationship

WORK
Is Required
Parent

Action on WORK
(Parent)

Action on TRANS
(Child)

Insert None. Get a parent.

Modify key or
foreign key

Prohibit—WORK uses a
surrogate key.

Prohibit—WORK uses a
surrogate key.

Delete Prohibit—data about a work

and its related transaction is
never deleted (business rule).

None.

(b) For the WORK-to-TRANS Relationship