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

SALESPERSON

F IGURE 7.11
Conversion of the E-R diagram in Figure
7.10 with a one-to-one unary relationship
into a relational table

Salesperson Salesperson Commission Year Backup

Number Name Percentage of Hire Numbe r

SALESPERSON
PK Salesperson

Number
Salesperson
Name
Commission
Percentage
Year of Hire

F IGURE 7.12
The one-to-many (1-M) unary relationship
from Figure 2.7b

Manages
Reports to

One-to-Many Unary Relationship The one-to-many unary relationship situation is
very similar to the one-to-one unary case. Figure 7.12 repeats the E-R diagram
from Figure 2.7b. Figure 7.13 shows the conversion of this diagram into a relational
database. Some employees manage other employees. An employee's manager
is recorded in the Manager Number attribute in the table in Figure 7.13. The
manager numbers are actually salesperson numbers since some salespersons are
sales managers who manage other salespersons. This arrangement works because
each employee has only one manager. For any particular SALESPERSON record,
there can only be one value for the Manager Number attribute. However, if you
scan down the Manager Number column, you will see that a particular value may
appear several times because a person can manage several other salespersons.

Many-to-Many Unary Relationship Figure 7.14 shows the E-R diagram for the
many-to-many unary relationship of Figure 2.7c. As Figure 7.15 indicates, this
relationship requires two tables in the conversion. The PRODUCT table has no
foreign keys. The COMPONENT table indicates which items go into making up
which other items, as was described in the bill-of-materials discussion in Chapter 6.
This table also contains any intersection data that may exist in the many-to-many
relationship. In this example, the Quantity attribute indicates how many of a
particular item go into making up another item.
The fact that we wind up with two tables in this conversion is really not
surprising. The general rule is that in the conversion of a many-to-many relationship
of any degree (unary, binary, or ternary), the number of tables will be equal to the
number of entity types (one, two, or three, respectively) plus one more table for
the many-to-many relationship. Thus, the conversion of the many-to-many unary
relationship required two tables, the many-to-many binary relationship three tables,
and, as will be shown next, the many-to-many ternary relationship four tables.

F IGURE 7.13
Conversion of the E-R diagram in Figure
7.12 with a one-to-many unary
relationship into a relational table

SALESPERSON

Salesperson Salesperson Commission Year

Number Name Percentage of Hire Manage r