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

Musician Number has been chosen to be the primary key. It seems that there is
an important application that requires the fast and frequent retrieval of musician
names together with their college-degree data, but without their musician numbers.
As currently structured, this would clearly require repeated joins of the MUSICIAN
and DEGREE tables, which might cause unacceptable performance problems. Since
the Musician Name attribute is unique and is a candidate key of the MUSICIAN
table, a solution to this problem is to replace the Musician Number foreign-key
attribute in the DEGREE table with Musician Name:

Musician

−−−−−−

Name Degree University Year

−−−−

With Musician Name already in the DEGREE table, the retrieval situation
described does not require a join. Plus, the DEGREE table can still tie degrees
uniquely to musicians, since Musican Name is unique.
Another possible solution to the more general problem of retrieving both
detailed data about musicians and their degrees at the same time involves the
concept of repeating groups. We know that there is a one-to-many relationship
between musicians and degrees since a musician can have several degrees but a
degree is associated with only one musician. Suppose we assume that a musician
can have at most three degrees. We can then eliminate the DEGREE table entirely
by merging its data into the MUSICIAN table:

Musician Musician Annual Orchestra Degree University Year Degree University Year Degree University Year

−−−−−−

Number Name Instrument Salary Name #1 #1 #1 #2 #2 #2 #3 #3 #3

−−−−

This is possible because of the small fixed maximum number of degrees and
because of the ability to distinguish among them, in this case in a time sequence
based on when they were awarded or by level, say bachelor's degree first, master's
degree second. Clearly, in this case, there will be null attribute values since not every
musician has three degrees. Further, there may be more programmer involvement
since inserting new degree data or even retrieving degree data may require more
informed and careful operations. But it certainly eliminates the join between the
MUSICIAN table and the now defunct DEGREE table, and may be the modification
necessary for acceptable performance.

EXAMPLE: LUCKY RENT-A-CAR

Consider the Lucky Rent-A-Car database of Figure 5.18. One issue with this
company is the privacy of their customers' data. Some of their employees may
need to access the entire CUSTOMER table, while others may need, for example,
customer number and customer name data but not the more personal data, such as
customer address and customer telephone. A restriction can be set up to accomplish
this using views. One view can be created that includes the entire table; another
can be created that includes only the Customer Number and Customer Name
attributes. Using these two views in the SQL GRANT command (discussed in
Chapter 11), different employees or groups of employees can be given full access
to the CUSTOMER table or restricted access to only part of it.