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

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

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.

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).

The DBA divides the database into areas so you can reference the database in sections instead of an entire unit. Areas are physical divisions of the database that are defined in the schema and are used to dump selectively, verify, or recover sections of the database; improve I/O; group logically related record types; and provide protection restrictions. Areas are stored as separate files and can be on separate volumes.

5.11 Realms

A realm is a group of one or more areas. Realms are logical divisions of the database. A realm is the object of the DML READY statement. Figure 5-27 shows the relationship between the schema, areas, subschema, and realms. Even though realms can contain data from more than one area, the type of data they contain is dependent on the subschema. It acts as a filter, allowing access to only specific data items.

Entire realms, as well as individual database records, are locked by the DBCS as they are retrieved by the run unit, and the degree of locking depends on the specific DML command used. For more information, see Section 6.1.

Figure 5-27 Database Relationships

The term run unit and program are not the same. A run unit is an executable image that may access a database, while a program can be used in two or more run units. For example, program SHOW-EMPLOYEE can be run simultaneously by a payroll department employee to obtain employee data, and by an accountant to obtain job cost data. Each person controls his or her own run unit.

5.13 Currency Indicators

When you access database records, the database control system (DBCS) uses pointers called currency indicators to keep track of record storage and retrieval. Compaq COBOL uses currency indicators to remember records and their positions in the database. Currency indicators can be changed by DML statement execution. Thus, they assist in defining the environment of a DML statement and are updated as a result of executing DML statements.

One currency indicator exists for each realm, set type, and record type defined in your subschema. Another currency indicator, called the run-unit currency indicator, also exists for the run unit.

All the currency indicators in a run unit are null prior to execution of the first DML statement. The null value indicates there is neither a current record nor a current position. Execution of certain DML statements can change the value of currency indicators. However, currency indicators do not change if statement execution fails.

The DBCS also uses currency indicators as place markers to control its sequence of access to the database. For example, if VENDOR is the name of the vendor records in Figure 5-2, then the current of VENDOR is normally the vendor record most recently accessed. Likewise, in the set VENDOR_SUPPLY, the current of VENDOR_SUPPLY is normally the most recently accessed record of that set. Note that current of set could be either a member or owner record because both record types are part of the VENDOR_SUPPLY set.

Failure to establish correct currency can produce incorrect or unpredictable results. For example, you might unknowingly modify or delete the wrong record. The following sections describe how the DBCS sets currency indicators and how to use currency status in a DML program.

5.13.1 Current of Realm

Each realm currency indicator can be null or it can identify:

• A record and its position in the realm
• A position in the realm but not a specific record

A record identified by the realm currency is called current of realm. The DBCS updates current of realm only when you reference a different record within the realm. For example:

000100 PROCEDURE DIVISION. 000110 . 000120 . 000130 . 000500 FIND FIRST PART WITHIN BUY. 000510 FIND FIRST PART WITHIN MAKE. 000520 FIND NEXT PART WITHIN BUY. 000600 FIND NEXT SUPPLY WITHIN PART_INFO. 000610 . 000620 . 000630 .

For example, if LABEL and CASSETTE are in the BUY realm, while TAPE is in the MAKE realm, statement 000500 sets the first occurrence of PART record in realm BUY (LABEL) as current of realm BUY. Statement 000510 sets the first occurrence of PART record in realm MAKE (TAPE) as current of realm MAKE. Notice that current of realm BUY is still the record occurrence accessed in statement 000500. Statement 000520 changes the current of realm BUY to the next occurrence PART record in realm BUY (CASSETTE). Current of realm MAKE remains the record accessed in statement 000510. Because the SUPPLY record type is located in the MARKET realm, statement 000600 sets the current of MARKET realm to the first SUPPLY record in the current PART_INFO set.

5.13.2 Current of Set Type

Each set type currency indicator can be null or it can identify:

• A record and its position in the set type
• A position in the set type but not a record

A record identified by a set type currency indicator is the current record for the set type, or current of set type.

If the ordering criterion for a set type is NEXT or PRIOR, the set type's currency indicator specifies the insertion point for member records. Therefore, if the currency indicator points to an empty position, a member record can be inserted in the specified position. If the currency indicator points to a record and NEXT is specified, a member record can be inserted after the current record for the set type. If the currency indicator points to a record and PRIOR is specified, a member record can be inserted before the current record for the set type.

The DBCS updates current of set type only when you reference a record that participates either as an owner or member in a set type occurrence. For example:

000100 PROCEDURE DIVISION. . . . 000500 FIND FIRST PART. 000510 FIND FIRST SUPPLY WITHIN PART_INFO. 000520 FIND OWNER WITHIN VENDOR_SUPPLY. 000600 . . .

Statement 000500 sets the first occurrence of PART (LABEL) as current of set types PART_USES, PART_USED_ON, and PART_INFO. This is because PART records participate in three sets (see Figure 5-2). Because LABEL is current of PART_INFO, statement 000510 sets the first occurrence of SUPPLY (4-DAYS) owned by LABEL as current of set type PART_INFO. Because SUPPLY also participates in the VENDOR_SUPPLY set, this statement also sets the current occurrence of SUPPLY as current of set type VENDOR_SUPPLY. Statement 000520 sets the VENDOR owner record occurrence (SOUND-OFF CO.), which owns the current SUPPLY record, as current of set type VENDOR_SUPPLY.

5.13.3 Current of Record Type

Each record type currency indicator can be null or it can identify:

• A record and its position among other records of the same type
• A position among records of the same type, but not identify a record

Record type currency indicators do not identify a record type's relationship with other record types.

A record identified by a record type currency indicator is called current of record type. The DBCS updates the current of record type only when you reference a different record occurrence of the record type. References to other record types do not affect this currency. For example:

000100 PROCEDURE DIVISION . . . 000500 FIND LAST PART. 000510 FIND FIRST SUPPLY WITHIN PART_INFO. 000520 FIND NEXT WITHIN PART_INFO. 000530 FIND FIRST VENDOR. . . .

Statement 000500 sets the last occurrence of PART (TAPE) as current of record type PART. Statement 000510 sets the SUPPLY record occurrence (2-DAYS) as current of record type SUPPLY. Statement 000520 updates current of record type for SUPPLY to record occurrence (1-WEEK). Statement 000530 sets VENDOR record occurrence (MUSICO INC.) as current of record type VENDOR.

5.13.4 Current of Run Unit

The Database Control System (DBCS) updates the currency indicator for current of run unit each time a run unit refers to a different record occurrence, regardless of realm, set, or record type. For example:

000100 PROCEDURE DIVISION. . . . 000500 FIND FIRST PART. 000510 FIND FIRST SUPPLY. 000520 FIND FIRST VENDOR. 000600 . 000610 . 000620 .

Statement 000500 sets the current of run unit to the first PART record occurrence (LABEL). Statement 000510 then sets the first SUPPLY record occurrence (4-DAYS) as current of run unit. Finally, statement 000520 sets the first VENDOR record (MUSICO INC.) as current of run unit. The first VENDOR record occurrence remains current of run unit until the run unit refers to another record occurrence.

5.14 Currency Indicators in a Compaq COBOL DML Program

Currency indicators are the tools you use to navigate through a database. Because of the many set relationships a database can contain, touching a record with a DML statement often changes more than one currency indicator. For example, a FETCH to a set type record can change currency for the set type, the record type, the realm, and the run unit. Knowing currency indicator status, how currency indicators change, and what statements control them, will help you locate the correct data.

Example 5-3 searches for TAPE vendors with a supply rating equal to A. Assume that record TAPE resides in BUY realm and that the SUPPLY record occurrences 2-DAYS and 5-DAYS have a SUP_RATING equal to A. Figure 5-28 shows how DML statements affect currency status.

Example 5-3 Currency Indicators

000100 PROCEDURE DIVISION . . . 000490 100-FETCH-THE-PART. 000500 MOVE "TAPE" TO PART_DESC. 000510 FETCH FIRST PART USING PART_DESC. 000520 MOVE "A" TO SUP_RATING. 000550 200-FIND-SUPPLY. 000560 FIND NEXT SUPPLY WITHIN PART_INFO 000570 USING SUP_RATING. 000580 AT END 000590 GO TO 500-NO-MORE-SUPPLY. 000600 FETCH OWNER WITHIN VENDOR_SUPPLY. 000610 ************************ 000620 * VENDOR PRINT ROUTINE * 000630 ************************ 000640 GO TO 200-FIND-SUPPLY.

Statement 000500 provides the search argument used by statement 000510. Statement 000510 fetches the first occurrence of PART with a PART_DESC equal to TAPE. Statement 000520 provides the search argument used by statement 000560. Statement 000560 finds each member record occurrence of SUPPLY with a SUP_RATING equal to A owned by the PART with a PART_DESC equal to TAPE.

If, instead of its present structure, statement 000560 read "FIND NEXT SUPPLY USING SUP_RATING," the search for supply records would not be restricted to supply member records in the PART_INFO set owned by TAPE. Instead, the search would extend to all supply records, finding all vendors with a supply rating equal to A, who may or may not be suppliers of TAPE.

Figure 5-28 Currency Status by Executable DML Statement