Database Concepts › Representing Relationships as Sets › Hierarchies › Two-level Hierarchies

Two-level Hierarchies

A single set represents a two-level hierarchy. The owner record is the first level and the member records comprise the second level. This represents a one-to-n (or one-to-many) relationship between the owner record and the member records. Hierarchical relationships can be represented as repeating elements within a record or multiple record types. For example, in the DEPT-EMPLOYEE set described earlier in this chapter, each employee's information is stored as a separate record. The data could alternatively be expressed as a single record type by making EMPLOYEE a repeating element in DEPARTMENT. In COBOL, the record would appear as follows:

01 DEPARTMENT.
   02 DEPT-ID                       PIC 9(4)
   02 DEPT-NAME                     PIC X(45)
   02 DEPT-HEAD-ID                  PIC 9(4)
   02 EMP-CNT                       PIC S9(4) COMP SYNC.
   02 EMPLOYEE                      OCCURS 0 TO 100 TIMES
                                           DEPENDING ON EMP-CNT
      03 EMP-ID                              PIC 9(4).
      03 EMP-NAME.
         04 EMP-FIRST-NAME                   PIC X(10).
         04 EMP-LAST-NAME                    PIC X(15).

      03 EMP-ADDRESS.
         . . .
      03 BIRTH-DATE.
         04 BIRTH-YEAR                       PIC 99.
         04 BIRTH-MONTH                      PIC 99.
         04 BIRTH-DAY                        PIC 9(4).

The main reasons for expressing relationships as sets rather than as repeating elements within records are as follows:

• A record can be accessed directly; an element cannot.
• Records can participate in many sets, thereby permitting a combination of relationships that would be impossible among individual elements.
• It is often difficult to know how many occurrences to allow for and over-estimation wastes storage since programs must reserve enough space to hold the maximum number.

On the other hand, maintaining information as elements means fewer records and more information per record access. Whether to break out repeating elements as member records of a set is a basic question in designing a database and one which is best approached on a case-by-case basis.

Consider, for example, dental claims made by an employee of Commonweather Corporation. Each procedure for which a claim is made must be described and recorded on the database. While multiple procedures can be claimed together, the number is limited and the amount of information recorded about each is small. For these reasons, dental procedure information is stored as repeating items within the DENTAL-CLAIM record occurrence with which they are associated rather than as separate records.