LOGICAL DATABASE DESIGN - Fundamentals of Database Management Systems - page 169

SALESPERSON

Salesperson Salesperson Commission Year Office

Number Name Percentage of Hire Numbe r

CUSTOMER

Customer Customer Salesperson

Number Name Numbe r HQ City

CUSTOMER EMPLOYEE

Custome r Employee Employee

Numbe r Number Name Title

PRODUCT

Product Product

Number Name Unit Price

SALES

Salesperson Produc t

Numbe r Numbe r Quantity

OFFICE

F IGURE 7.19
The General Hardware Company
relational database

Office
Number Telephone Size

which we will return shortly). To the right of the salesperson entity box in the E-R
diagram, there is a one-to-many relationship (''Sells To'') between salespersons and
customers. The database then includes a CUSTOMER table with the Salesperson
Number attribute as a foreign key, because salesperson is on the '' one side'' of the
one-to-many relationship and customer is on the '' many side'' of the one-to-many
relationship.
Customer employee is a dependent entity of customer and there is a one-to-
many relationship between them. Because of this relationship, the CUSTOMER
EMPLOYEE table in the database includes the Customer Number attribute as a
foreign key. Furthermore, the Customer Number attribute is part of the primary key
of the CUSTOMER EMPLOYEE table because customer employee is a dependent
entity and we're told that employee numbers are unique only within a customer.
The PRODUCT table contains the three attributes of the product entity.
The many-to-many relationship between the salesperson and product entities is
represented by the SALES table in the database. Notice that the combination
of the unique identifiers (Salesperson Number and Product Number) of the two
entities in the many-to-many relationship is the primary key of the SALES table.
Finally, the office entity has its table in the database with its three attributes, which
brings us to the presence of the Office Number attribute as a foreign key in the