Relationship-Level Integrity

   

---

Establishing Relationship Characteristics

Now you'll establish the characteristics of each relationship. These characteristics indicate what will occur when you delete a record, the type of participation each table bears within the relationship, and to what degree each table participates in the relationship.

Defining a Deletion Rule for Each Relationship

The first characteristic you'll establish for the relationship is a deletion rule . This rule determines what your RDBMS should do when you place a request to delete a given record in the parent table of the relationship. Deletion rules are crucial to relationship-level integrity because they help guard against orphaned records, which are records in the child table that have no relationship whatsoever to any records in the parent table.

These are the five types of deletion rules you can define and the actions the RDBMS should take when a given rule is in force:

1. Deny . The RDBMS will not delete the record in the parent table, but will instead keep the record and designate it as "inactive."

2. Restrict . The RDBMS will not delete the record in the parent table if related records exist in the child table. You must have the RDBMS delete all of the related records in the child table before you can have it delete the record in the parent table.

3. Cascade . The RDBMS will take two specific actions: It will delete the record in the parent table, and it will also automatically delete all related records in the child table.

4. Nullify . The RDBMS will delete the record in the parent table and will then update the foreign key values of related records in the child table to null . If you are going to use this deletion rule, you must modify the foreign key's field specifications and set the Null Support logical element to "Nulls Allowed."

5. Set Default. The RDBMS will delete the record in the parent table and will then update the foreign key values of related records in the child table to the current Default Value logical element setting in the foreign key's field specifications. Obviously, you must have a setting for the Default Value element in order to use this rule.

Use a Restrict deletion rule as a matter of course and the other rules as appropriate. The best way to determine which deletion rule is appropriate for a given relationship is to examine the relationship diagram. Consider the diagram in Figure 10.56.

Figure 10.56. What deletion rule is appropriate for a given relationship?

Select a relationship, look at the diagram, and pose the following question:

When a record in the ( name of parent table ) table is deleted, what should happen to related records in the ( name of child table ) table?

Here the question is framed in a generic manner so that you can understand the premise behind it. When you pose this question for a pair of tables in a particular relationship, substitute the phrases within the parentheses with the appropriate table names . If you're working with the relationship between the EMPLOYEES and ORDERS table, you could pose the question in this manner:

When a record in the EMPLOYEES table is deleted, what should happen to related records in the ORDERS table?

The answer you receive depends on how the organization is using the data within the tables and will usually indicate which deletion rule you should use for the relationship.

You can't delete an employee record; you have to designate the employee as inactive. (Use a Deny rule.)

You can't delete an employee record if there are related order records. (Use a Restrict rule.)

You must first delete the orders associated with the employee from the ORDERS table and then delete the employee from the EMPLOYEES table. (Use the Restrict rule.)

All orders associated with the employee must be deleted from the ORDERS table as well. (Use the Cascade rule.)

The employee number for all orders associated with the employee must be deleted. (Use a Nullify rule.)

The employee number for all orders associated with the employee must be reset to the lead salesperson's employee number. (Use a Set Default rule.)

If you (or the people you're working with) cannot easily provide an answer, make note of the relationship and continue with another relationship. You'll revisit all of these relationships when you establish business rules for the database later in Chapter 11. For now, let's assume you received the first reply and you're going to use a Deny rule for the relationship.

Once you've identified the type of deletion rule you want to use for the relationship, designate the rule on the relationship diagram. Use (D) for Deny, (R) for Restrict, (C) for Cascade, (N) for Nullify, and (S) for Set Default. Place the designation under the connection line of the parent table. Figure 10.57 shows the revised relationship diagram for the EMPLOYEES and ORDERS tables.

Figure 10.57. Designating a Restrict deletion rule for the relationship between the EMPLOYEES and ORDERS tables.

You always set the deletion rule from the perspective of the parent table because it is the more important of the two tables within the relationship. Deleting a record in the parent table will always have some effect on related records in the child table, but deleting a record in the child table will have no effect on the related record in the parent table. (There is a specific circumstance in which you might want to establish a Restrict deletion rule for the child table, and you'll learn about it in Chapter 11.)

The question you use to determine the deletion rule for a self-referencing relationship is just slightly different from the one you just used for a dual-table relationship.

When a record in the ( name of parent table ) table is deleted, what should happen to the foreign key values of the other records that were related to it?

If you're working with the self-referencing relationship for the EMPLOYEES table, you could pose the question in this manner:

When a record in the EMPLOYEES table is deleted, what should happen to the foreign key values of the other records that were related to it?

Once again, the reply will usually indicate which deletion rule you should use for the relationship.

You can't delete a record for an employee who's currently managing other employees. (Use a Restrict rule.)

If the employee you want to delete is a manager, you cannot delete his record until you assign the employees he manages to a different manager. (Use the Restrict rule.)

If the employee whose record you want to delete is a manager, the M ANAGER ID must be deleted from the record of every employee he currently manages. (Use a Nullify rule.)

If the employee whose record you want to delete is a manager, the M ANAGER ID must be reset to the senior manager's employee number in the record of every employee he currently manages. (Use a Set Default rule.)

Note

The Cascade rule is notably absent from this example because it doesn't apply to the relationship at all; you don't want to fire employees just because their manager is leaving the organization. This rule is still a viable option in some instances, so do keep it in mind when you're establishing deletion rules for other self-referencing relationships.

Say that you received the fourth reply and have determined that you're going to use a Set Default deletion rule for the relationship. You now complete the process by designating the rule on the relationship diagram. Figure 10.58 shows the results of your work.

Figure 10.58. Designating a Set Default deletion rule for the EMPLOYEES table self-referencing relationship.

Identifying the Type of Participation for Each Table

When you establish a relationship between a pair of tables, each table participates in a particular manner. The type of participation you assign to a given table determines whether a record must exist in that table be fore you can enter records into the related table. There are two types of participation:

1. Mandatory . There must be at least one record in this table before you can enter any records into the related table.

2. Optional. There is no requirement for any records to exist in this table before you can enter records into the related table.

You'll commonly determine the type of participation for most tables later when you're defining business rules, although you can quite often establish the type of participation for tables in relationships where the type of participation for each table is obvious, is a result of common sense, or is in accordance with some particular set of standards. For example, consider the one-to-many relationship between the EMPLOYEES and CUSTOMERS tables in Figure 10.59. (These are slightly different versions of the tables in Figure 10.56.)

Figure 10.59. What type of participation should you assign to each table?

Assume that each customer must be assigned to a particular employee. This employee acts as the customer's account representative and takes care of all transactions and communications between the organization and that customer. Although each customer must be associated with a particular employee, a given employee does not have to be associated any customer at all. Many employees perform other functions within the organization that do not require customer interaction.

This scenario neither implies nor defines any special circumstances, but does indicate the manner in which the organization conducts this part of its business. As such, you can infer the following:

• You should designate a Mandatory type of participation for the EMPLOYEES table. This ensures that there is at least one employee for you to assign to a given customer.

• You should designate an optional type of participation for the CUSTOMERS table. This allows you to enter any person employed by the organization.

Once you've determined the type of participation for each table within the relationship, designate each table's participation on the relationship diagram. Use a vertical line to represent a Mandatory type of participation and a circle to represent an optional type of participation. Figure 10.60 shows the revised relationship diagram for the EMPLOYEES and CUSTOMERS tables and also demonstrates how you indicate each type of participation. Note that you place the symbol representing the type of participation outside of the symbol that represents the type of relationship.

Figure 10.60. Designating the type of participation for the EMPLOYEES and CUSTOMERS tables.

The type of participation also applies to a self-referencing relationship, although in a slightly different manner. Because of the nature of a self-referencing relationship, you designate the type of participation for the primary key and foreign key fields in the table. Figure 10.61 shows a revised relationship diagram for the STAFF table you worked with earlier in this chapter.

Figure 10.61. Designating the type of particpation for the primary and foreign keys of the STAFF table.

In this case, you must have at least one staff member with a valid staff identification number (the primary key) who can serve as a manager. Conversely, you need not provide a manager identification number (the foreign key) for a brand-new staff member; this person may have just been hired earlier today and has not yet been assigned to a particular department or project.

Identifying the Degree of Participation for Each Table

Now that you've determined how each table will participate within the relationship, you must determine the degree to which each table will participate. The degree of participation indicates the minimum number of records that a given table must have associated with a single record in the related table and the maximum number of records that the table is allowed to have associated with a single record in the related table. The factors you use to determine the degree of participationobvious circumstances, common sense, or conformance to some set of standardsare the same as those you used to determine the type of participation. You'll commonly identify the degree of participation for some tables now and revisit the remaining tables when you define business rules for the database.

You use two numbers separated by a comma and enclosed within parentheses to represent the degree of participation for a given table. The first number indicates the required minimum number of related records and the second number indicates the allowable maximum number of related records. For example, a degree of participation such as (2,11) indicates that the table must have at least 2 but no more than 11 of its records related to a single record in the other table.

Consider the EMPLOYEES and CUSTOMERS tables once again. There is a one-to-many relationship between these tables, which means that a given customer can be associated with only one employee and a given employee can be associated with any number of customers. (Yes, I know; this is the obvious part.) Assume, however, that your organization has just instituted a new policy that focuses sharply on quality customer service. In order to ensure that each account representative can deliver the level of service the organization requires, the policy stipulates that he cannot be assigned to more than 15 customers at the same time. Based on this scenario, you can infer that the degree of participation for the EMPLOYEES table is (1,1) and the degree of participation for the CUSTOMERS table is (0,15).

Once you've identified the degree of participation for a particular table, add the information to the relationship diagram. Designate the degree of participation over the connection line of the appropriate table. Figure 10.62 shows the revised relationship diagram for the EMPLOYEES and CUSTOMERS tables.

Figure 10.62. Designating the degree of participation for the EMPLOYEES and CUSTOMERS tables.

The degree of participation also applies to a self-referencing relationship, although you designate it for the primary key and foreign key fields in the table, just as you did with the type of participation. Figure 10.63 shows an updated version of the relationship diagram for the STAFF table that includes the degree of participation information.

Figure 10.63. Designating the degree of participation for the primary and foreign keys of the STAFF table.

S TAFF ID has a degree of participation of (0,12) because a manager can manage up to 12 staff members; a new manager who hasn't yet been assigned to a department or project will have no (or 0) staff members to manage. The degree of participation for M ANAGER ID is (1,1) because a given staff member is managed by only one manager.

You can designate an unlimited degree of participation for any table in a dual-table relationship or key field in a self-referencing relationship by using an "N" in place of the second number. For example, the ORDERS table in Figure 10.64 has an unlimited degree of participation. Although a new customer may have not yet placed an order, you will allow him to place as many orders as he wishes. Imagine the impact on your organization's business if you limited each customer to 35 orders! Your organization would soon be out of business, unless it could continually and consistently acquire new customers.

Figure 10.64. Designating an unlimited degree of participation for the ORDERS table.

Your task now is to set the relationship characteristics for every relationship you've established thus far. As you complete work on a given relationship, be sure to update the relationship diagram so that it reflects the results of your work.

Verifying Table Relationships with Users and Management

The very last order of business is to verify the relationships. You can perform this task relatively easily by using the following checklist:

1. Make sure that you've properly identified each relationship.

2. Make certain that you've properly established each relationship.

3. Make certain that each foreign key complies with the Elements of a Foreign Key.

4. Make sure that you've established an appropriate deletion rule for each relationship.

5. Make certain that you've identified the proper type of participation for each table within a dual-table relationship and for the appropriate key fields in a self-referencing relationship.

6. Make certain that you've identified the proper degree of participation for each table within a dual-table relationship and for the appropriate key fields in a self-referencing relationship.

If all the relationships check out and everyone you're working with agrees to this assessment, you can be confident that the relationships are sound and ready to be incorporated into views.

A Final Note

The degree to which you can easily implement these three relationship characteristics depends greatly upon your RDBMS. Most RDBMSs do not fully or inherently support all of the characteristics, but they do provide some basic support for the deletion rule and type of participation. In most cases, however, you can use SQL and programming code to implement these characteristics for any relationship in your database.

---

Top