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.
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:
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.
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.
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.
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 INVOICE NUMBER field from the INVOICES table and the PRODUCT NUMBER field from the PRODUCTS table. Figure 10.67 shows the revised relationship diagram for these 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.)
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.
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.