|
HP OpenVMS Systems Documentation |
|
HP COBOL
|
| Previous | Contents | Index |
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:
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:
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
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
300-CONNECT-COMPONENT-1.
MOVE 1 TO COMP_QUANTITY.
STORE COMPONENT RETAINING PART_USES.
|
Figure 5-19 COMPONENT Is Connected to Both Set Types
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
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:
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:
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:
| Previous | Next | Contents | Index |