We opened this chapter with a discussion of the three types of relationships that can exist between a particular pair of tablesone-to-one, one-to-many, and many-to-many. You now know that the one-to-many relationship is the most common type of dual-table relationship and that the many-to-many relationship gives rise to problems that must be resolved. You then learned about a self-referencing relationship, which is a type of relationship that occurs between the records within a given table. It is similar to a dual-table relationship in that it can be one-to-one, one-to-many, or many-to-many.
Next, we discussed how to identify the relationships that exist among the tables in a database. First you learned how to construct and use a table matrix, and then you learned how to use associative and contextual questions to help you identify a given relationship. We then discussed three formulas you could use to determine the true relationship that exists between the tables in a dual-table relationship or between the records in a self-referencing relationship.
The chapter continued with a discussion of how relationships are established. You learned that one-to-one and one-to-many relationships are established by using primary keys and foreign keys, and that many-to-many relationships are established using linking tables. We then briefly revisited multivalued fields, and you learned how to use a proper one-to-many relationship to resolve a multivalued field more efficiently. Next, we discussed self-referencing relationships, and you now know that you establish them in a very similar manner to dual-table relationships. You then learned that you must review all of the table structures and ensure that they still conform to the Elements of the Ideal Table.
Foreign keys were the next topic of discussion, and you learned that every foreign key must comply with the Elements of a Foreign Key. You now know that it can be very important for a foreign key to share the same name as its parent primary key, that you must modify certain elements of a field specification for a field that serves as a foreign key, and that a foreign key must draw its values from the parent primary key.
We then discussed relationship characteristics. You learned how to define a deletion rule for a relationship and that there are four ways you can define it. Next, you learned how to identify the type of participation and degree of participation for each table within a dual-table relationship and for each key field in a self-referencing relationship. As you now know, you can designate the type of participation as Mandatory or Optional. You also know that the degree of participation gauges the minimum and maximum number of interrelated records that can exist within a given relationship. Finally, you learned that you must verify the relationships with users and management and that you can use a checklist to accomplish this task.
The chapter closed with a look at relationship-level integrity. You learned that a relationship attains this type of integrity after you've verified that it is properly established and its characteristics are suitably set.