A relational database is based upon sets, with each table representing a set. However, there are some types of information that are not directly amenable to the set data structure. Think, for example, of an organization chart, a bill of material in a manufacturing and assembly plant, or a family tree. These types of information are hierarchical in nature, and most conveniently represented in a tree structure. In this chapter we discuss how to represent such hierarchical information in a relational table. We also discuss in detail various SQL constructs that you can use to extract hierarchical information from a relational table.
Let’s look at an example to understand how we can represent hierarchical information in a relational database. As a basis for the example, we’ll use an organization chart showing how one employee reports to another within a large organization, as shown in Figure 8-1.
Figure 8-1 represents a hierarchy of employees. The
information regarding an employee, his manager, and the reporting
relationship need to be represented in one table,
employee, as shown in the Entity Relationship
Diagram in Figure 8-2.
In Figure 8-2, the