LOGICAL DATABASE DESIGN - Fundamentals of Database Management Systems - page 173

ORCHESTRA

Orchestra Music

Name City Country Director

MUSICIAN

Musician Musician Annual Orchestra

Number Name Instrument Salary Name

DEGREE

Musician
Numbe r Degree University Year

COMPOSER

Composer Date of

Name Country Birth

COMPOSITION

Composition Compose r

Name Name Year

RECORDING

F IGURE 7.23
The World Music Association
relational database

Orchestra Composition Compose r

Name

Nam e Name Year Price

Notice that the primary key of the RECORDING table begins with the Orchestra
Name attribute and then continues with both the Composition Name and Composer
Name attributes. This is because the primary key of one of the two entities in the
many-to-many relationship, composition, is the combination of those two latter
attributes.

Designing the Lucky Rent-A-Car Database
Figure 7.24 shows the Lucky Rent-A-Car E-R diagram. The conversion to a
relational database structure begins with the car entity and its four attributes, as
shown in the CAR table of the database in Figure 7.25. Because car is on the '' many
side'' of a one-to-many relationship with the manufacturer entity, the CAR table
also has the Manufacturer Name attribute as a foreign key. The straightforward one-
to-many relationship from car to maintenance event produces a MAINTENANCE
EVENT table with Car Serial Number as a foreign key. The customer entity converts
to the CUSTOMER table with its four attributes. The many-to-many relationship
between car and customer converts to the RENTAL table. Car Serial Number, the
unique identifier of the car entity, and Customer Number, the unique identifier
of the customer entity, plus the Rental Date intersection data attribute form the
three-attribute primary key of the RENTAL table, with Return Date and Total
Cost as additional intersection data attributes. Rental Date has to be part of the