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

CUSTOMER
Is Required
Parent

Action on CUSTOMER
(Parent)

Action on
CUSTOMER_ARTITST_INT
(Child)

Insert None. Get a parent.

Modify key or
foreign key

Prohibit—CUSTOMER
uses a surrogate key.

Prohibit—CUSTOMER
uses a surrogate key.

Delete Prohibit if a transaction

related to this CUSTOMER
exists—data related to a
transaction is never deleted
(business rule).
Allow if no transaction
related to this CUSTOMER
exists—(business rule)—
cascade delete children.

None.

(c) For the CUSTOMER-to-CUSTOMER_ARTIST_INT Relationship

ARTIST
Is Required
Parent

Action on ARTIST
(Parent)

Action on
CUSTOMER_ARTITST_INT
(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 a transaction

related to a work by this
ARTIST exists—data related
to a transaction is never
deleted (business rule).
Allow if no transaction
related to a work by this
ARTIST exists—(business
rule)—cascade delete
children.

None.

Figure 6-39
Continued

(d) For the ARTIST-to-CUSTOMER_ARTIST_INT Relationship

Figure 6-40
Actions to Enforce Minimum
Cardinality for Required
Children for the WORK-to-
TRANS Relationship

TRANS
Is Required
Child

Action on WORK
(Parent)

Action on TRANS
(Child)

Insert INSERT trigger on WORK

to create row in TRANS.
TRANS will be given data
for DateAcquired and
AcquisitionPrice. Other
columns will be null.

Will be created by
INSERT trigger on WORK.

Modify key or
foreign key

Prohibit—surrogate key. Prohibit—TRANS must

always refer to the WORK
associated with it.

Delete Prohibit—data related to a

transaction is never deleted
(business rule).

Prohibit—data related to a
transaction is never deleted
(business rule).