Database Reference
In-Depth Information
CUSTOMER
CustomerID
ARTIST
TRANS
WORK
LastName
FirstName
AreaCode
LocalNumber
Street
City
State
ZipPostalCode
Country
Email (AK1.1)
ArtistID
PURCHASES/SOLD_TO
ACQUIRED
CREATES/CREATED_BY
TransactionID
WorkID
LastName (AK1.1)
FirstName (AK1.2)
Nationality
DateOfBirth
DateDeceased
DateAcquired
AcquisitionPrice
DateSold
SalesPrice
AskingPrice
WorkID (FK)
CustomerID (FK)
Title (AK1.1)
Copy (AK1.2)
Medium
Description
ArtistID (FK)
CUSTOMER_ARTIST_INT
CustomerID (FK)
ArtistID (FK)
HAS_INTEREST_IN
ADMIRED_BY
Figure 6-37
Final View Ridge Gallery
Database Design
many customers may not have an e-mail address. Because of these problems, this design cries
out for surrogate keys.
Surrogate Key Database Design
The database design for the View Ridge database using surrogate keys is shown in Figure 6-37.
Notice that two identifying relationships (TRANS-to-WORK) and (WORK-to-ARTIST) have
been changed to nonidentifying relationships represented by dashed lines. This was done be-
cause once ARTIST has a surrogate key, there is no need to keep ID-dependent keys in WORK
and TRANS. Realize that WORK and TRANS are both weak entities even though they are no
longer ID-dependent.
Notice that (LastName, FirstName) in ARTIST has been defined as an alternate key. This
notation indicates that (LastName, FirstName) has a UNIQUE constraint, which ensures that
artists are not duplicated in the database. Similarly, (Title, Copy) in WORK is defined as an
alternate key so that a given work cannot appear more than once.
The foreign key placement is a straightforward application of the techniques described
in this chapter, but note that the foreign key CustomerID in TRANS can have null values. This
specification allows the creation of a TRANS row when a work is acquired, before any cus-
tomer has purchased the work. All other foreign keys are required.
Minimum Cardinality enforcement for Required Parents
According to Figure 6-28(a), for each relationship that involves a required parent, we need to
decide:
Whether to cascade or prohibit updates of the parent's primary key
●
Whether to cascade or prohibit deletions of the parent
●
How to obtain a parent when a new child is created
●
Figure 6-38
Summary of View
Ridge Database Design
Relationships
Relationship
Cardinality
Parent
Child
Type
Nonidentifying
MAX
1:N
MIN
M-O
ARTIST
WORK
CUSTOMER
CUSTOMER
WORK
TRANS
TRANS
CUSTOMER_ARTIST_INT
Nonidentifying
Nonidentifying
Identifying
1:N
1:N
1:N
M-M
O-O
M-O
ARTIST
CUSTOMER_ARTIST_INT
Identifying
1:N
M-O
Search WWH ::
Custom Search