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

Customer 0121

Customer 0933

Customer 1047

Salesperson 137
Mr. Baker

FIGURE 5.5
A salesperson and his four customers

Customer 1826

For example, the Salesperson Number attribute of the CUSTOMER relation
shows that salesperson 137 is responsible for customers 0121, 0933, 1047, and 1826.
Looking at it from the point of view of the customer, the same relation shows that
the only salesperson associated with customer 0121 is salesperson 137, Figure 5.5.
This last point has to be true. After all, there is only one record for each customer in
the CUSTOMER relation (the Customer Number attribute is unique since it is the
relation's primary key) and there is only one place to put a salesperson number in
it. The bottom line is that the Salesperson Number foreign key in the CUSTOMER
relation effectively establishes the one-to-many relationship between salespersons
and customers.
By the way, notice that, in this case, the primary key of the SALESPERSON
relation and the corresponding foreign key in the CUSTOMER relation both have
the same attribute name , Salesperson Number. This will often be the case but it
does not have to be. What is necessary is that both attributes have the same domain
of values ; that is, they must both have values of the same type, such as (in this case)
three-digit whole numbers that are the identifiers for salespersons.
It is the presence of a salesperson number in a customer record that indicates
which salesperson the customer is associated with. Fundamentally, that is why
the Salesperson Number attribute is in the CUSTOMER relation and that is the
essence of its being a foreign key in that relation. In Chapter 7, we will discuss
database design issues in detail. But, for now, note that when building a one-to-many
relationship into a relational database, it will always be the case that the unique
identifier of the entity on the '' one side'' of the relationship (Salesperson Number,
in this example) will be placed as a foreign key in the relation representing the entity
on the '' many side'' of the relationship (the CUSTOMER relation, in this example).
Here's something else about foreign keys. There are situations in which a
relation doesn't have a single, unique attribute to serve as its primary key. Then, it