THE RELATIONAL DATABASE MODEL: INTRODUCTION - Fundamentals of Database Management Systems - page 129

Continuing with the command sequence to eventually find the name of the
salesperson responsible for customer number 1525, the next part of the command
issued is:
Select rows from that result (the relation that resulted from the join) in which
Customer Number = 1525.
This produces:

SalesPerson SalesPerson Commission Year of Customer Customer SalesPerson

Number Name Percentage Hire Number Name Number HQ City

361 Carlyle 20 2001 1525 Fred's Tool Stores 361 Atlanta

Finally, we issue the third command
Project the Salesperson Name over that last result.
and get:

SalesPerson Name

Carlyle
Notice that the process could have been streamlined considerably if the relational
DBMS had more ''intelligence'' built into it. The query dealt with only a single
customer, customer 1525, and there is only one row for each customer in the
CUSTOMER relation, since Customer Number is the unique key attribute. Therefore,
the query needed to look at only one row in the CUSTOMER relation, the one for
customer 1525. Since this row references only one salesperson, salesperson 361,
it follows that, in turn, it needed to look at only one row in the SALESPERSON
relation, the one for salesperson 1525. This type of performance issue in relational
query processing will be covered later in this topic in Chapter 8.

EXAMPLE: GOOD READING BOOK STORES

Figure 5.16 shows the relational database for the Good Reading Book Stores
example described earlier. Since publishers are in a one-to-many relationship to
books, the primary key of the PUBLISHER Relation, Publisher Name, is inserted
into the topic relation as a foreign key. There are two many-to-many relationships.
One, between books and authors, keeps track of which authors wrote which books.
Recall that a book can have multiple authors and a particular author may have written
or partly written many books. The other many-to-many relationship, between books
and customers, records which customers bought which books.
The WRITING relation handles the many-to-many relationship between
books and authors. The primary key is the combination of Book Number and
Author Number. There is no intersection data! Could there be a reason for having
intersection data in this relation? If, for example, this database belonged to a
publisher instead of a bookstore chain, an intersection data attribute might be
Royalty Percentage, i.e. the percentage of the royalties to which a particular author
is entitled for a particular book. The SALE relation takes care of the many-to-many
relationship between books and customers. Certainly Book Number and Customer
Number are part of the primary key of the SALE relation, but is the combination
of the two the entire primary key? The answer is that this depends on whether the
assumption is made that a given customer can or cannot buy copies of a given