Database Reference
In-Depth Information
Relationship
Minimum
Cardinality
Design Decisions to Be Made
Design Documentation
M-O
• Update cascade or prohibit?
• Delete cascade or prohibit?
• Policy for obtaining parent on insert of child
Referential integrity (RI) actions plus documentation for
policy on obtaining parent for child insert.
O-M
• Policy for obtaining child on insert of parent
• Primary key update cascade or prohibit?
• Policy for update of child foreign key
• Policy for deletion of child
Use Figure 6-28(b) as a boilerplate.
M-M
All decisions for M-O and O-M above, plus how
to process trigger conflict on insertion of first
instance of parent/child and deletion of last
instance of parent/child.
For mandatory parent, RI actions plus documentation for
policy on obtaining parent for child insert. For mandatory
child, use Figure 6-28(b) as a boilerplate. Add
documentation on how to process trigger conflict.
Figure 6-33
Summary of Design
Decisions for Minimum
Cardinality
Because the DEPARTMENT-to-EMPLOYEE relationship has a required child, we will fill out the
table in Figure 6-28(b). Figure 6-32 shows the result. Here triggers are described for DEPARTMENT
insert, EMPLOYEE modification (update), and EMPLOYEE deletion. DEPARTMENT modification
(update) actions are unneeded because DEPARTMENT has a surrogate key.
an additional Complication
You should be aware of an additional complication that is beyond the scope of this text. A table can
participate in many relationships. In fact, there can be multiple relationships between the same
two tables. You need to specify a design for the minimum cardinality of every relationship. The
minimum cardinality of each relationship will vary. Some will be O-M, some will be M-O, and some
will be M-M. Some of the relationships will require triggers, which may mean that you have several
sets of insert, update, and delete triggers per table. This array of triggers is not only complicated to
write and test, the actions of different triggers may interfere with one another during execution.
You will need more experience and knowledge to design, implement, and test such complex arrays
of trigger code and DBMS constraints. For now, just be aware that these problems exist.
Summary of Minimum Cardinality Design
Figure 6-33 summarizes the design for relationship minimum cardinality. It shows each type of
relationship, the design decisions that need to be made, and the documentation that should
be created. Use this figure as a guide.
The View Ridge Gallery Database
We conclude this chapter with an example database design problem. This design will be used
throughout the rest of the text, so take the time to understand it. This particular problem was
chosen because it has typical relationships and moderate complexity. It has enough challenges
to make it interesting, but not so many as to make it overwhelming.
Summary of Requirements
The View Ridge Gallery (View Ridge or VRG) is a small art gallery that sells contemporary
European and North American fine art, including lithographs, high-quality reproduction
prints, original paintings and other artwork, and photographs. All of the lithographs, prints,
and photos are signed and numbered, and the original art is usually signed. View Ridge also
provides art framing services. It creates a custom frame for each artwork (rather than selling
standardized, premade frames) and is known for its excellent collection of frame stock.
View Ridge emphasizes reproduction artworks of European Impressionist, Abstractionist,
and Modernist artists such as Wassily Kandinsky and Henri Matisse. For original art, View
 
 
 
Search WWH ::




Custom Search