PHYSICAL DATABASE DESIGN - Fundamentals of Database Management Systems - page 236

The RENTAL table represents the many-to-many relationship among cars and
customers, recording who rented which car on a particular date. The primary key
is thus Car Serial Number, Customer Number, and Rental Date. Recall that Rental
Date must be part of the primary key because a particular customer could have
rented a particular car on more than one occasion. This three-attribute primary key
is clumsy. An index built on it would be long and clumsy too, and if it had to be
used as a foreign key in another table, that would be clumsy, too. A solution is to
add a new Rental Number attribute that will serve as a unique key of the table:

Rental Car Serial Customer Rental Return Total

Number Number Number Date Date Cost

Next, assume that the following table, which has data about the president of
each manufacturer, has been added to the database:

Manufacturer President President President President

Name Name Address Telephone email

Since each company has exactly one president, there is a one-to-one rela-
tionship between manufacturers, represented by the existing MANUFACTURER
table, and presidents, represented by the new PRESIDENT table. As is usually the
case in such situations, it makes sense to represent the two different entities in two
different tables. However, if we ever need to retrieve both detailed manufacturer
data and detailed president data, we will have to execute a join. If we have to do
this frequently and with significant speed, it may make sense to combine the two
tables together:

Manufacturer Manufacturer Sales Rep Sales Rep President President President President

Name Country Name Telephone Name Address Telephone email

After all, since a company has only one president, it also has only one
president name, one president address, and so forth. This arrangement makes for a
bulkier table that will be spread out over a larger disk area than either table alone,
possibly slowing down certain retrievals. But it will avoid the join needed to retrieve
manufacturer and president detailed data together.
Finally, here are examples of the physical design technique of adding new
tables. Lucky Rent-A-Car's CAR table is accessed very frequently—so frequently,
in fact, that it has become a performance bottleneck. The company has decided
to duplicate the table and put each of the two copies on different disk devices
so that some applications can access one disk and other applications the other
disk. This will improve throughput. However, these two duplicate tables must
be kept identical at all times and any changes made to them must be made to
both copies simultaneously. Notice that while the CAR table may have to be read
frequently for Lucky's rental operations, it has to be updated only when new cars
are added to Lucky's inventory or existing cars are taken out of inventory. This
makes the duplicate-table technique practical, since frequent changes that require
the updating of both tables simultaneously would slow down the entire environment
significantly.