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

requires a combination of two or more attributes to reach uniqueness and serve as
its primary key. Sometimes one or more of the attributes in that combination can be
a foreign key! Yes, when this happens, a foreign key is actually part of the relation's
primary key! This was not the case in the CUSTOMER relation of Figure 5.2b.
In this relation, the primary key only consists of one attribute, Customer Number,
which is unique all by itself. The foreign key, Salesperson Number, is clearly not a
part of the primary key.
Here is an example of a situation in which a foreign key is part of a relation's
primary key. Figure 5.6 adds the CUSTOMER EMPLOYEE relation, Figure 5.6c,
to the General Hardware database. Remember that General Hardware's customers
are the hardware stores, home improvement stores, or chains of such stores that it
supplies. Figure 5.6c, the CUSTOMER EMPLOYEE relation, lists the employees of
each of General Hardware's customers. In fact, there is a one-to-many relationship
between customers and customer employees. A customer (like a hardware store)
has many employees but an employee, a person, works in only one store:

Customer Customer Employee

For example, Figure 5.6c shows that customer 2198 has four employees,
Smith, Jones, Garcia, and Kaplan. Each of those people is assumed to work for
only one customer company, customer 2198. Following the rule we developed for
setting up a one-to-many relationship with a foreign key, the Customer attribute
must appear in the CUSTOMER EMPLOYEE relation as a foreign key, and indeed
it does.
Now, what about finding a legitimate primary key for the CUSTOMER
EMPLOYEE relation? The assumption here is that employee numbers are unique
only within a company ; they are not unique across all of the customer companies.
Thus, as shown in the CUSTOMER EMPLOYEE relation in Figure 5.6c, there can
be an employee of customer number 0121 who is employee number 30441 in that
company's employee numbering system, an employee of customer number 0933
who is employee number 30441 in that company's system, and also an employee of
customer number 2198 who is also employee number 30441. That being the case, the
Employee Number is not a unique attribute in this relation. Neither it nor any other
single attribute of the CUSTOMER EMPLOYEE relation is unique and can serve,
alone, as the relation's primary key. But the combination of Customer Number and
Employee Number is unique. After all, we know that customer numbers are unique
and within each customer company, employee numbers are unique. That means
that, as shown in Figure 5.6c, the combination of Customer Number and Employee
Number can be and is the relation's primary key. Further, that means that Customer
Number is both a foreign key in the CUSTOMER EMPLOYEE relation and apart
of its primary key. As shown in Figure 5.6c, we will start marking attributes that
are both a foreign key and a part of the primary key with an underline consisting of
a dashed line over a solid line.

Many-to-Many Binary Relationship
Storing the Many-to-Many Binary Relationship Figure 5.7 expands the General Hardware
database by adding two more relations, the PRODUCT relation, Figure 5.7d, and the
SALES relation, Figure 5.7e. The PRODUCT relation simply lists the products that
General Hardware sells, one row per product, with Product Number as the unique
identifier and thus the primary key of the relation. Each of General Hardware's