Defining Relations

Relations define how records are shared between files—that is, how records from one file are associated with records in another file. You can enforce deletion schemes for data in related files. The first file is called the primary ("From") file, and the second file is called the secondary ("To") file.

Before you can define relations for a file, you must first define indexes for the file. To define a relation, you must use an index to the secondary file. To create a relation between two files, use values or fields from the primary file to access the related records in the secondary file.

For the procedure to define a relation, see Defining and Removing Relations.

Relation Types

The two types of relations are one-to-one and one-to-many. A one-to-one relation connects one record from the primary file to one record in the secondary file. A one-to-many relation connects one record from the primary file to many records in the secondary file. The maximum number of relations (mRELS) allowed is 125.

For example, you can define a one-to-one relation from the Employee file to the Department file, stating that one employee belongs to one department. The Department (Dept#) field, which is in both files, relates, or connects, the files. Using the Department field, you can also define a one-to-many relation from the Department file to the Employee file, stating that one department has many employees. The following figure illustrates the basic structures of these relations.

In the one-to-one example, the Employee file is the primary file and the Department file is the secondary file. In the one-to-many example, the Department file is the primary file and the Employee file is the secondary file.

Relation Integrity

Two schemes maintain the integrity between relations: how the relation is required and how related records are deleted.

Requirement Schemes

Relations can be required, not required, or conditionally valid. Some requirement schemes do not let you delete related records, others do. A relation that is not required must have a deletion scheme. A relation that is required (either required or conditionally valid) does not need a deletion scheme.

Required If you define a one-to-one relation as required, the record in the secondary file must exist before you can add a related record to the primary file (for example, you cannot add an employee to a non-existent department).

Not Required If you define a one-to-one relation as not required, adding records to the primary file is not dependent on the prior existence of related records in the secondary file. For example, an employee does not need to have a resume record. Therefore, records in the Resume file do not have to exist before you can add employees to the Employee file.

Conditionally Valid

You can also define a relation in which a record must satisfy certain conditions for the relation to be required or not required. For example, an employee does not have to belong to a department. When an employee does belong to a department, the department must first exist in the Department file. The condition states that if the Dept# is not empty, the one-to-one relation of Employee to Department is required.

Deletion Schemes

Some requirement schemes do not let you delete related records, others do. A relation that is not required must have a deletion scheme. A relation that is required, or conditionally valid, does not need a deletion scheme.

If you use deletion schemes, you must also use the "full delete" APIs: , , and . These APIs will enforce the deletion schemes defined for the database file.

The deletion scheme is the method of managing the deletion of records. If you perform record deletion improperly, you can compromise database integrity. The three kinds of deletion schemes are delete ignored, delete cascades, and delete restricted.

  • Delete Ignored: If you delete a record from the primary file, its related record in the secondary file remains intact. Defining a relation as delete ignored is the same as defining no deletion scheme, because the impact on the secondary file is the same.

  • Delete Cascades: If you delete a record from the primary file, the system deletes its related record in the secondary file.

  • Delete Restricted: If you try to delete a record from the primary file, the system requires that you first delete or move its related record in the secondary file.

The following is an example of a one-to-many, not-required relation from the Employee file to the Dependents file. Two employees have dependents (Emp#1 and Emp#3) and one does not (Emp#2). If the relation does not have a deletion scheme (delete ignored), and you delete an Employee record, Dependent records remain in the database. Eventually, the Dependents file becomes cluttered with records that are no longer valid. If you delete Emp#1 and add another Employee record with the same employee number (Emp#1), the dependents for the old Emp#1 (John, Susan, and Molly) now appear as dependents of the new Emp#1 because the files are related. In this example, you should choose delete cascades or delete restricted as the deletion scheme.

The following is an example of a one-to-one, required relation from the Dependents file to the Employee file. Because the Employee record must exist before you can add the Dependents record, the Employee record is independent of the Dependents record. If you delete a Dependents record, the Employee record should be left intact; therefore, no deletion scheme is necessary.

Dependent Relations

To relate two files that do not have all key fields in common, you must use a third file that has key fields in common with the first two files. The third file associates the first two files with each other. A relation in which two files are tied together by a third file is called a dependent relation. There are two relations that make up the dependent relation. These are called supporting relations. To define a dependent relation, you must first define the supporting one-to-one relation.

For example, the Invoice file has key fields Company (Co#) and Vendor (Ven#). The Vendor file has key fields Vendor Group (Vgrp#) and Vendor (Ven#). You cannot access a Vendor record directly from the Invoice file, because the Vendor Group key field is not in the Invoice file. Therefore, you need a third file that has the Company and Vendor Group fields. You can use the Company file, which has these fields, to associate the Invoice file with the Vendor file.

Self-Referential Relation

A self-referential relation is a one-to-one relation in which the primary and the secondary files are the same.

For example, an Employee file record has the employee number (Emp#), employee name (EmpName), and the employee number of the supervisor (Sup#) to whom the employee reports. If Jim Johnson (Emp#1) reports to John Smith (Emp#3), Jim's supervisor number (Sup#) is 3.