Database Reference
In-Depth Information
CUSTOMER
Email
ARTIST
TRANS
WORK
LastName
FirstName
AreaCode
LocalNumber
Street
City
State
ZipPostalCode
Country
LastName
FirstName
PURCHASES/SOLD TO
ACQUIRED
CREATES/CREATED BY
DateAcquired
Title
Copy
AcquisitionPrice
DateSold
SalesPrice
AskingPrice
Nationality
DateOfBirth
DateDeceased
Medium
Description
HAS INTEREST IN/ADMIRED BY
Figure 6-35
View Ridge Gallery Data
Model
Note that we are using the entity name TRANS instead of TRANSACTION. We are doing
this because
transaction
is a
DBMS reserved word
in most (if not all) DBMS products. Using
DBMS reserved words such as
table, column
, or other names can create problems. Similarly,
we cannot use the reserved word
tran.
The word
trans
, however, is not a DBMS reserved word,
and we can use it without problems. We will discuss this problem more when we discuss spe-
cific DBMS products in Chapters 10A, 10B and 10C.
In the View Ridge data model, an artist may be recorded in the database even if none of
his or her works has appeared in the gallery. This is done to record customer preferences for
artists whose works might appear in the future. Thus, an artist may have from zero to many
works.
The identifier of WORK is the composite (Title, Copy) because, in the case of lithographs
and photos, there may be many copies of a given title. Also, the requirements indicate that a
work may appear in the gallery many times, so there is a need for potentially many TRANS
entities for each WORK. Each time a work appears in the gallery, the acquisition date and
price must be recorded. Thus, each WORK must have at least one TRANS row.
A customer may purchase many works; this is recorded in the 1:N relationship from
CUSTOMER to TRANS. Note that this relationship is optional in both directions. Finally, there is
an N:M relationship between CUSTOMERs and ARTISTs. This is an N:M relationship between
strong entities—the team searched in vain for a missing attribute that would indicate an asso-
ciation pattern rather than an N:M relationship.
Database Design with Data Keys
A database design for the data model in Figure 6-35 is shown in Figure 6-36. This design uses
data keys, and every primary key except the composite (ARTIST.LastName, ARTIST.FirstName)
has problems. The keys for WORK and TRANS are huge, and the key for CUSTOMER is doubtful;
Figure 6-36
Initial View Ridge Gallery
Database Design
CUSTOMER
TRANS
WORK
Email
ARTIST
LastName (FK)
FirstName (FK)
DateAcquired
Title (FK)
Copy (FK)
LastName (FK)
FirstName (FK)
Title
Copy
LastName
FirstName
AreaCode
LocalNumber
Street
City
State
ZipPostalCode
Country
LastName
FirstName
PURCHASES/SOLD_TO
ACQUIRED
CREATES/CREATED_BY
Nationality
DateOfBirth
DateDeceased
Medium
AcquisitionPrice
DateSold
SalesPrice
AskingPrice
Email (FK)
CUSTOMER_ARTIST_INT
LastName (FK)
FirstName (FK)
Email (FK)
HAS_INTEREST_IN
ADMIRED_BY
Search WWH ::
Custom Search