Summary

   

---

Relationship-Level Integrity

A relationship attains relationship-level integrity after you've verified that it is properly established and its characteristics are suitably set. Relationship-level integrity warrants the following:

• The connection between the two tables (or key fields) in a relationship is sound. You accomplished this by using primary and foreign key fields to establish a one-to-one or a one-to-many relationship and a linking table to establish a many-to-many relationship.

• You can insert new records into each table in a meaningful manner. You ensured this by designating the appropriate type of participation for each table (or key field) within the relationship.

• You can delete an existing record without producing any adverse effects. You guaranteed this by assigning an appropriate deletion rule for the relationship.

• There is a meaningful limit to the number of records that can be interrelated within the relationship. You implemented this by designating the appropriate degree of participation for each table (or key field) within the relationship.

As you know, relationship-level integrity is the third component of overall data integrity. (The first is table-level integrity and the second is field-level integrity.) You'll establish the final component of overall data integrity in the next chapter when you learn how to establish business rules for the database.

CASE STUDY

It's now time to identify the relationships that exist for the tables that appear on the final table list for Mike's Bikes. You've assigned your assistant, Zachary, to this part of the design process, and he's currently working with these tables:

CUSTOMERS

EMPLOYEES

INVOICES

PRODUCTS

VENDORS

Zachary's first order of business is to identify the relationships that currently exist between the tables. He decides to meet only with Mike because there are few tables in this database, and he figures that Mike should be familiar enough with the tables to help him verify the relationships.

Before Zachary meets with Mike, he creates a table matrix and identifies as many relationships as possible. Figure 10.65 shows his completed matrix.

Figure 10.65. Identifying the relationships among the tables in the Mike's Bikes database.

Zachary then studies the table matrix closely and uses the appropriate formula to determine the true relationship between each pair of tables. Here is what he's discovered so far:

CUSTOMERS and INVOICES bear a one-to-many relationship. (1:1 + 1:N = 1:N)

EMPLOYEES and INVOICES bear a one-to-many relationship. (1:1 + 1:N = 1:N)

PRODUCTS and INVOICES bear a many-to-many relationship. (1:N + 1:N = M:N)

Now he diagrams the relationships, places them in a folder, and heads to Starbucks for his meeting with Mike.

At the meeting, Mike and Zachary work on verifying the relationships. They both determine that the three relationships are indeed correct, and then Zachary brings Mike's attention to the PRODUCTS and VENDORS tables. He's not quite sure about the relationship between them, so he discusses the matter with Mike.

Z ACHARY:	" I wanted to ask you about the relationship between the PRODUCTS and VENDORS tables. Can a single product be associated with one or more vendors?"
M IKE:	" Yes, in a manner of speaking. What I mean is that a single type of productsuch as a bike lock can be associated with one or more vendors. But I give each lock its own product number and treat it as a distinct item, regardless of the vendor who supplies it. Now, if the true meaning of your question is whether a single record in the PRODUCTS table can be associated with one or more records in the VENDORS table, then the answer is no because each record in the PRODUCTS table contains a reference to only one vendor in the VENDORS table."
Z ACHARY:	" I thought as much. In that case, there's a one-to-many relationship between the VENDORS and PRODUCTS tables. I automatically figured that a single vendor could be associated with many products in the PRODUCTS table."

Zachary now diagrams the one-to-many relationship between the VENDORS and PRODUCTS tables and continues with the next step.

He establishes each one-to-many relationship by taking a copy of the primary key from the parent table and incorporating it within the structure of the child table (where it serves as a foreign key) and then revises the relationship diagram accordingly . Figure 10.66 shows one of his revised diagrams.

Figure 10.66. The relationship diagram for the EMPLOYEES and INVOICES tables.

Now Zachary establishes the many-to-many relationship between the INVOICES and PRODUCTS tables by creating a new linking table called INVOICE PRODUCTS. He bases the new table on the I NVOICE N UMBER field from the INVOICES table and the P RODUCT N UMBER field from the PRODUCTS table. Figure 10.67 shows the revised relationship diagram for these tables.

Figure 10.67. Establishing and diagramming the many-to-many relationship between the INVOICES and PRODUCTS tables.

Zachary reviews each table structure to ensure that it conforms to the Elements of the Ideal Table. Fortunately, he doesn't have to make any modifications because all of the table structures are sound. He now refines the foreign keys in each table by making certain that each one complies with the Elements of a Foreign Key. Finally, Zachary modifies the appropriate items in the General Elements and Logical Elements sections of each foreign key's Field Specifications sheet. Figure 10.68 shows the modifications he's made for one of the foreign keys. (I've highlighted the changes so that you can recognize them more easily.)

Figure 10.68. The General Elements and Logical Elements sections of the Field Specifications sheet for the C USTOMER ID foreign key field in the INVOICES table.

Zachary's next task is to establish the appropriate relationship characteristics for each relationship. He begins by defining a deletion rule for each relationship and then identifies both the type of participation and the degree of participation for each table within the relationship. He completes his task by designating these characteristics on the relationship diagram. Figure 10.69 shows one of the completed diagrams.

Figure 10.69. The completed relationship diagram for the EMPLOYEES and INVOICES tables.

Mike and Zachary review and verify all the relationships one last time. They agree that everything is complete, so they celebrate with a couple of Mocha Br ves.

---

Top