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

COLLEGE DEPARTMENT PROFESSOR

Chairs/Chaired By

CollegeName DepartmentName ProfessorFirstName

ProfessorLastName

DeanFirstName
DeanLastName
Phone
Building
Room

Phone
TotalMajors
Room
CollegeName (FK)
ProfessorFirstName (FK) (AK1.1)
ProfessorLastName (FK) (AK1.2)

Building
OfficeNumber
Phone

Major APPOINTMENT

DepartmentName (FK)
ProfessorFirstName (FK)
ProfessorLastName (FK)

STUDENT

StudentNumber

Title
Terms

Title
DepartmentName (FK)
StudentFirstName
StudentLastName
HomeStreet
HomeCity
HomeState
HomeZip
Phone
ProfessorDepartment (FK)
ProfessorFirstName (FK)
ProfessorLastName (FK)

Adviser

Figure 6-27
Database Design for Highline
University

In addition to the two referential integrity constraints above our database design, we will
also have the following:

CollegeName in DEPARTMENT must exist in CollegeName in COLLEGE
(ProfessorFirstName, ProfessorLastName) in DEPARTMENT
must exist in (ProfessorFirstName, ProfessorLastName) in PROFESSOR
DepartmentName in APPOINTMENT must exist in DepartmentName in DEPARTMENT
(ProfessorFirstName, ProfessorLastName) in APPOINTMENT
must exist in (ProfessorFirstName, ProfessorLastName) in PROFESSOR

Design for Minimum Cardinality

The third and last step of transforming data models into database designs is to create a plan
for enforcing minimum cardinality. Unfortunately, this step can be considerably more com-
plicated than the first two design steps. Relationships that have required children entities are
particularly problematic because we cannot enforce such constraints with database struc-
tures. Instead, as you will see, we must design procedures for execution by the DBMS or by
applications.
Relationships can have one of four minimum cardinalities: parent optional and child
optional (O-O) , parent mandatory and child optional (M-O) , parent optional and child
mandatory (O-M) , or parent mandatory and child mandatory (M-M) . As far as enforcing
minimum cardinality is concerned, no action needs to be taken for O-O relationships, and we
need not consider them further. The remaining three relationships pose restrictions on insert,
update, and delete activities.
Figure 6-28 summarizes the actions needed to enforce minimum cardinality. Figure 6-28(a)
shows needed actions when the parent row is required (M-O and M-M relationships), and
Figure 6-28(b) shows needed actions when the child row is required (O-M and M-M relation-
ships). In these figures and the accompanying discussion, the term action means minimum
cardinality enforcement action . We use the shorter term action for ease of discussion.