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

In this text, the actions to enforce required parents are
documented using referential integrity actions on the table
design diagrams. The actions to enforce required children
are documented by using Figure 6-28(b) as a boilerplate
document. An additional complication is that a table can
participate in many relationships. Triggers written to en-
force the minimum cardinality on one relationship may
interfere with triggers written to enforce the minimum

cardinality on another relationship. This problem is beyond
the scope of this text, but be aware that it exists. The prin-
ciples for enforcing minimum cardinality are summarized
in Figure 6-33.
A database design for the View Ridge Gallery is shown
in Figures 6-37, 6-38, 6-39, 6-40, and 6-41. You should un-
derstand this design because it will be used throughout the
remainder of this topic.

Key Terms

action
alternate key (AK)
association table
candidate key
cascading deletion
cascading update
component design
data constraint
database design
DBMS reserved word
default value
domain constraint
interrelation constraint
intersection table
intrarelation constraint
minimum cardinality enforcement action
MUST constraint
MUST COVER constraint

MUST NOT constraint
null status
parent mandatory and child
mandatory (M-M)
parent mandatory and child
optional (M-O)
parent optional and child
mandatory (O-M)
parent optional and child optional (O-O)
range constraint
referential integrity (RI) action
surrogate key
SQL Server IDENTITY ({StartValue},
{Increment}) property
systems analysis and design
systems development life cycle
(SDLC)
trigger

Review Questions

6.1 Identify the three major tasks for transforming a data model into a database design.

6.2 What is the relationship between entities and tables? Between attributes and columns?

6.3 Why is the choice of the primary key important?

6.4 What are the three characteristics of an ideal primary key?

6.5 What is a surrogate key? What are its advantages?

6.6 When should you use a surrogate key?

6.7 Describe two disadvantages of surrogate keys.

6.8 What is the difference between an alternate key and a candidate key?