Database Reference
In-Depth Information
is an ID-dependent weak entity, it must have a COMPANY parent. In addition, assume that
application requirements indicate that each COMPANY row must have at least one row in
PHONE_CONTACT. Hence, the relationship is M-M.
However, transactions are almost always initiated from the side of the strong entity. A
data entry form will begin with a COMPANY and then, somewhere in the body of the form,
the data from the PHONE_CONTACT table will appear. Hence, all insert, update, and dele-
tion activity on PHONE_CONTACT will come as a result of some action on COMPANY. Given
this situation, we can ignore the Action on Child columns in Figure 6-28(a) and Figure 6-28(b)
because no one will ever try to insert, modify, or delete a new PHONE_CONTACT except in
the context of inserting, modifying, or deleting a COMPANY.
Because the relationship is M-M, however, we must take all of the actions in the Action on
Parent columns of both Figure 6-28(a) and Figure 6-28(b). With regards to inserts on parents,
we must always create a child. We can meet this need by writing a COMPANY INSERT trigger
that automatically creates a new row of PHONE_CONTACT with null values for Contact and
PhoneNumber.
With regard to updates and deletions, all we need to do is to cascade all of the remaining
actions in Figure 6-28(a) and Figure 6-28(b). Changes to COMPANY.CompanyName will be
propagated to PHONE_CONTACT.CompanyName. The deletion of a COMPANY will automat-
ically delete that company's PHONE_CONTACT rows. This makes sense; if we no longer want
data about a company, we certainly no longer want its contact and phone data.
By The Way Because of the difficulty of enforcing M-M relationships, developers look
for special circumstances to ease the task. Such circumstances usually
exist for relationships between strong and weak entities, as described. For relation-
ships between strong entities, such special circumstances may not exist. In this case,
the M-M cardinality is sometimes just ignored. Of course, this cannot be done for
applications such as financial management or operations that require careful records
management, but for an application such as airline reservations, where seats are over-
booked anyway, it might be better to redefine the relationship as M-O.
Documenting the Minimum Cardinality Design
Because enforcing minimum cardinality can be complicated, and because it often involves the
creation of triggers or other procedures, clear documentation is essential. Because the design
for the enforcement of required parents is easier than that for required children, we will use
different techniques for each.
Documenting Required Parents
Database modeling and design tools such as CA Technologies' ERwin and Oracle's MySQL
Workbench allow you to define referential integrity (RI) actions on each table. These defi-
nitions are useful for documenting the actions necessary for a required parent. According to
Figure 6-28(a), three design decisions are necessary for a required parents: (1) determining
whether updates to the parent's primary key should cascade or be prohibited; (2) determin-
ing whether deletions of the parent should cascade or be prohibited; and (3) identifying how
a parent row is to be selected on the insert of a child.
By The Way In theory, referential integrity actions can be used to document the actions
to be taken to enforce required children as well as required parents. When
they are used for both purposes, however, they become confusing and ambiguous. In an
M-M relationship, for example, a child may have one set of rules for insert because of its
required parent and another set of rules for insert because it is a required child. The insert
 
Search WWH ::




Custom Search