Database Reference
In-Depth Information
RepCustomer
PartRep
RepNum
CustomerNum
PartNum
RepNum
20
148
AT94
20
20
282
AT94
65
35
148
DR93
20
65
282
DR93
35
65
356
DR93
65
212
DW11
35
CustomerPart
CustomerNum
PartNum
148
AT94
148
DR93
148
DW11
282
AT94
282
DR93
356
AT94
FIGURE 6-25
Results obtained by splitting Sales table into three tables
Figure 6-26 shows the result of joining these three tables. Note that it contains inaccurate information.
The second row, for example, indicates that rep 20 sold part DR93 to customer 148. If you look back to Fig-
ure 6-24, you will see that is not the case.
Sales
RepNum
CustomerNum
PartNum
20
148
AT94
20
148
DR93 !!!!
20
282
AT94 !!!!
20
282
DR93
35
148
DR93
35
148
DW11
65
282
AT94
65
282
DR93
65
356
AT94
FIGURE 6-26
Result obtained by joining three tables—the second and third rows are in error!
The second row appears in the join because rep 20 is related to customer 148 in the RepCustomer table
(rep 20 sold a part to customer 148), customer 148 is related to part DR93 in the CustomerPart table (cus-
tomer 148 bought part DR93 from a rep), and part DR93 is related to rep 20 in the RepPart table (rep 20
sold part DR93 to a customer). In other words, rep 20 sold a part to customer 148, customer 148 bought part
DR93, and rep 20 sold part DR93. Of course, these three facts do not imply that rep 20 sold part DR93 to
customer 148. (Customer 148 might have purchased this part from another rep.)
The problem with the preceding relationship is that it involves all three entities
reps, customers, and
parts. Splitting the Sales table shown in Figure 6-26 any further is inappropriate. Such a relationship is called
a many-to-many-to-many relationship.
Remember from the discussion of fourth normal form that there are examples of three-way relationships
in which you must split the tables. In particular, if the relationship between sales reps and customers has
nothing to do with the relationship between sales reps and parts, this table would violate fourth normal form
and would need to be split.
—
