Databases Reference
In-Depth Information
PUBLISHER relation
Publisher
Year
Name
City
Country
Telephone
Founded
AUTHOR relation
Author
Author
Year
Year
Number
Name
Born
Died
BOOK relation
Book
Book
Publication
Publishe
r
Number
Name
Year
Pages
Nam
e
CUSTOMER relation
Customer
Customer
Number
Name
Street
City
State
Country
WRITING relation
Book
Autho
r
Number
Numbe
r
SALE relation
F IGURE 5.16
Good Reading Bookstores relational
database
Book
Custome
r
Numbe
r
Numbe
r
Date
Price
Quantity
book on different days. If the assumption is that a customer can only buy copies
of a particular book on one single day, then the combination of Book Number and
Customer Number is fine as the primary key. If the assumption is that a customer
may indeed buy copies of a given book on different days, then the Date attribute
must be part of the primary key to achieve uniqueness.
EXAMPLE: WORLD MUSIC ASSOCIATION
Figure 5.17 shows the relational database for the World Music Association example
described earlier. There is a one-to-many relationship from orchestras to musicians
and, in turn, a one-to-many relationship from musicians to degrees. Thus, the primary
key of the ORCHESTRA relation, Orchestra Name, appears in the MUSICIAN
relation as a foreign key. In turn, the primary key of the MUSICIAN relation,
Musician Number, appears in the DEGREE relation as a foreign key. In fact, since
the DEGREE attribute is unique only within a musician, the Musician Number
attribute and the Degree attribute together serve as the compound primary key of the
DEGREE relation. A similar situation exists between composers and compositions.
The one-to-many relationship from composers to compositions requires that the
Search WWH ::
Custom Search