[an error occurred while processing this directive]

HP OpenVMS Systems Documentation

Content starts here

HP COBOL
DBMS Database Programming Manual


Previous Contents Index

5.9.2 Multiset Relationships

A set cannot contain an owner record and a member record of the same type. Nor can a simple set represent a many-to-many relationship. To simulate such relationships, Oracle CODASYL DBMS uses the concept of multiset relationships. Multiset relationships occur when two set types share a common record type called a junction record. The junction record can contain information specific to the relationship. An empty junction record contains only pointer information used by the DBCS to establish the multiset relationship. This section discusses three kinds of multiset relationships:

  • Many-to-many relationships between two types of records
  • Many-to-many relationships between records of the same type
  • One-to-many relationships between records of the same type

5.9.2.1 Many-to-Many Relationships Between Two Types of Records

To build a many-to-many relationship between two types of records, the DBA uses a junction record. For example, a part can be supplied by many vendors, and one vendor can supply many parts. The SUPPLY record type in Figure 5-14 links or joins PART records with VENDOR records.

Figure 5-14 Bachman Diagram of a Many-to-Many Relationship Between Two Types of Records


Figure 5-15 is an occurrence diagram of a many-to-many relationship between two types of records. This diagram typifies a many-to-many relationship because it shows a part (TAPE) being supplied by more than one vendor and a vendor (SOUND-OFF CO.) supplying more than one part. You could add additional vendors for a part by joining new supply records to a part and its new vendors. You could also add additional parts supplied by one vendor by joining supply records to the vendor and the new parts.

Figure 5-15 Many-to-Many Relationship Between Two Types of Records


5.9.2.2 Many-to-Many Relationships Between Records of the Same Type

To represent a relationship between record occurrences of the same type, the DBA builds a many-to-many relationship using member records to create the necessary links. Figure 5-16 shows a many-to-many relationship between records of the same type, where PART is the owner of both PART_USES and PART_USED_ON set types and COMPONENT is the junction record.

PART_USES is a bill of materials set type that links a PART owner record through its COMPONENT member records to the part's subassemblies. The link to the subassemblies is from COMPONENT member records up to the PART_USED_ON set type and back to PART owner records.

Figure 5-16 Bachman Diagram of a Many-to-Many Relationship Between Records of the Same Type


For example, assume you are creating a bill of materials and you have a finished part, a stool, made from one stool seat and four stool legs. Figure 5-17, Figure 5-18, Figure 5-19, and Figure 5-20 show occurrence diagrams of the bill of materials you would need to build a stool.

To complete the bill of materials you have to link the stool seat and stool legs to the finished part, the stool. You would:

  1. Use the FIND statement to locate the stool.


    PROCEDURE DIVISION.
    100-FIND-STOOL.
        MOVE "STOOL" TO PART_DESC.
        FIND FIRST PART USING PART_DESC.
    

    Figure 5-17 Current of PART_USES and PART_USED_ON


  2. Use the FIND statement to locate the stool seat retaining PART_USES currency. Because PART usually owns both sets, using a FIND or FETCH statement to locate PART changes both set currency indicators. Retaining PART_USES currency keeps a pointer at STOOL; otherwise, STOOL SEAT would be current for both sets. Section 5.13 discusses currency indicators in more detail.


    200-FIND-STOOL-SEAT.
        MOVE "STOOL SEAT" TO PART_DESC.
        FIND FIRST PART USING PART_DESC
                   RETAINING PART_USES.
    

    Figure 5-18 Retain PART_USES Currency


  3. Build a COMPONENT record (component 1), and store it retaining PART_USES currency. Because COMPONENT participates in the PART_USES set, storing it normally changes the set's currency. Therefore, executing a STORE statement with the retaining clause keeps STOOL as current of PART_USES. At this point, STOOL is the PART_USES owner of component 1, and STOOL SEAT is the PART_USED_ON owner of component 1.
    Since the insertion mode for COMPONENT is automatic in both set types, a STORE COMPONENT automatically connects COMPONENT to both set types.


    300-CONNECT-COMPONENT-1.
        MOVE 1 TO COMP_QUANTITY.
        STORE COMPONENT RETAINING PART_USES.
    

    Figure 5-19 COMPONENT Is Connected to Both Set Types


  4. Use the FIND statement to locate the stool legs, again retaining PART_USES currency, thus keeping STOOL current of PART_USES.


    400-FIND-STOOL-LEGS.
        MOVE "STOOL LEGS" TO PART_DESC.
        FIND FIRST PART USING PART_DESC
                   RETAINING PART_USES.
    

    Figure 5-20 Finding the Stool Legs While Keeping STOOL Current of PART_USES


  5. Build a second COMPONENT record (component 4) and store it. This links both PART_USES owner STOOL and PART_USED_ON owner STOOL LEGS to component 4. This completes all the necessary relationships you need to create the bill of materials shown in Figure 5-21.


    500-CONNECT-COMPONENT-4.
        MOVE 4 TO COMP_QUANTITY.
        STORE COMPONENT.
    

    Figure 5-21 Completed Bill of Materials


Figure 5-22 shows the relationship between PART records and COMPONENT records. The solid lines connect PART_USES owners to their members and the dotted lines connect PART_USED_ON owners to their members.

Figure 5-22 Occurrence Diagram of a Many-to-Many Relationship Between Records of the Same Type


The STOOL program in Example 8-5 loads and connects the parts for the STOOL bill of materials presented earlier in this section. It uses the relationship represented in Figure 5-16 to print its parts breakdown report in Section 8.6. Figure 5-23 explains how to read the parts breakdown report.

Figure 5-23 Sample Parts Breakdown Report


The sample parts breakdown report shows that:

  • PART A is built using two subassemblies: PART B and PART D.
  • PART B is built using PART C and PART D.
  • PART C is built using PART D.

5.9.2.3 One-to-Many Relationships Between Records of the Same Type

To build a one-to-many relationship between records of the same type, the DBA uses junction records. In a one-to-many relationship between records of the same type, either record type can be the junction record. However, in Figure 5-24 the WK_GROUP record type serves as the junction record because the EMPLOYEE record type has most of the relationship's data.

The record type EMPLOYEE includes all employees---supervisors, managers, and so forth. A manager can have many supervisors and a supervisor can have many employees. Conversely, an employee can have only one supervisor, and a supervisor can have only one manager.

Figure 5-24 One-to-Many Relationship Between Records of the Same Type


To show a relationship between employees (that is, who works for whom), Figure 5-24 uses the record type WK_GROUP as a link to establish an owner-to-member relationship. For example, a manager or supervisor would own a WK_GROUP record occurrence in the MANAGES set, and the same WK_GROUP occurrence owns any number of EMPLOYEE records in the CONSISTS_OF set. The relationship would be as follows: one occurrence of EMPLOYEE owns a WK_GROUP record occurrence, which in turn owns zero or more occurrences of the EMPLOYEE record type.

A one-to-many relationship between records of the same type is different from a many-to-many relationship between records of the same type because:

  • An employee can have only one manager, while a part can be used on many subassemblies.
  • The EMPLOYEE record type can participate both as an owner and a member in its relationship with WK_GROUP.
  • The PART record type can participate only as an owner in its relationship with COMPONENT.

Example 8-6 shows how to use DML for hierarchical relationships. The example uses the diagram in Figure 5-24.

The data in Figure 5-25 shows sample EMPLOYEE records and the connecting WK_GROUP links (Groups A, B1, and B2). For example, employee Howell manages a group that consists of employees Noyce and Moore.

Figure 5-25 Sample Data Prior to Update


Assume that employee Klein is promoted to supervisor with Neils and Riley reassigned to work for him. Figure 5-25 shows the relationship between EMPLOYEE and WK_GROUP record types prior to the update, and Figure 5-26 shows the relationship after the update.

Figure 5-26 Sample Data After Update


Example 8-6 (PERSONNEL-UPDATE program) uses the data in Figure 5-25 and shows you how to:

  1. Load the database (PERSONNEL-UPDATE).
  2. Display the contents of the database on your terminal using the Report Writer before changing relationships (PERSONNEL-REPORT) (see Figure 5-25 and Example 8-7).
  3. Create new relationships (PROMOTION-UPDATE).
  4. Display the contents of the database on your terminal using the Report Writer after changing relationships (PERSONNEL-REPORT) (see Figure 5-26 and Example 8-8).


Previous Next Contents Index