Database Reference
In-Depth Information
EMPLOYEE
EMPLOYEE
EmployeeName
EmployeeName
Other Data...
Other_Data_...
EmployeeNameMgr (FK)
Manages
Manages
Figure 6-21
Representing 1:N Recursive
Relationships
(a)
Data Model for a 1:N Recursive
Relationship in Figure 5-41
(b)
Database Design for a 1:N
Recursive Relationship
By The Way
Again, if you find this to be confusing, assume that you have two different
tables, one called PART and a second called CONTAINED_PART. Create
the intersection table between the two tables. Note that CONTAINED_PART duplicates
the attributes in PART and is thus unnecessary. Eliminate the table and you will have the
design in Figure 6-22(b).
Representing Ternary and higher-Order Relationships
As we discussed in Chapter 5, ternary and higher-order relationships can be represented by
multiple binary relationships, and such a representation usually works without any prob-
lems. However, in some cases, there are constraints that add complexity to the situation. For
example, consider the ternary relationship among the entities ORDER, CUSTOMER, and
SALESPERSON. Assume that the relationship from CUSTOMER to ORDER is 1:N and that
the relationship from SALESPERSON to ORDER also is 1:N. We can represent the three-part
relationship among ORDER:CUSTOMER:SALESPERSON as two separate binary relation-
ships: one between ORDER and CUSTOMER and a second between SALESPERSON and
CUSTOMER. The design of the tables will be:
CUSTOMER (
CustomerNumber
, {nonkey data attributes})
SALESPERSON (
SalespersonNumber
, {nonkey data attributes})
ORDER (
OrderNumber
, {nonkey data attributes},
CustomerNumber
,
SalespersonNumber
)
Figure 6-22
Representing N:M Recursive
Relationships
PART
PART
PartName
PartName
Other Data
Other_Data_...
Contains_Part
Is_Contained_In_Part
PART_PART_INT
PartName (FK)
ContainsPartName (FK)
(a)
Database Design for an N:M
Recursive Relationship
in Figure 5-43
(b)
Database Design for an N:M
Recursive Relationship
Search WWH ::
Custom Search