The Data Warehouse Development Life Cycle
Oracle Data Warehouse Design
Dealing With Recursive Data Relationships
Unfortunately, the recursive many-to-many relationship is very
confusing and almost impossible to understand without the aid of a
graphical representation. Visualize the recursive many-to-many
relationship as an ordinary many-to-many relationship with the owner
entity "pulled apart" into owner1 and owner2. Figure
4.10 shows how the junction entity establishes the relationship.
Figure 4.10 Viewing a recursive many-to-many relationship as a
many-to-many relationship.
The best way to conceptualize a
recursive many-to-many relationship in a CODASYL model is through
set occurrence diagrams. These diagrams show the pointer chains
that link the relationships (see Figure 4.11). Table sketches show
junction tables contain both implosion and explosion columns in
relational databases.
Figure 4.11 A set occurrence diagram for a recursive
relationship.
In Figure 4.11, we can navigate the
database, determining the components for a Big_Meal. To
navigate this diagram, start at the object Big_Meal and
follow the Has_parts link to the bubble containing the number
one. This is the quantity for the item. We now follow these bubbles
to the Is_a_part link, which shows that one order of fries is
included in a Big_Meal. We return to the Has_parts
link for Big_Meal and find the next bubble. The Is_a_part
link shows that one soda is included in a Big_Meal. We then
continue this process until no further entities can be found in the
Has_parts relationship. In sum, the Has_parts
relationships indicates that a Big_Meal consists of one order
of fries, one soda, and one hamburger. In addition, the hamburger
consists of two meat patties and one bun.