Integrity-Related Terms

   

---

Relationship- Related Terms

Relationships

A relationship exists between two tables when you can in some way associate the records of the first table with those of the second. You can establish the relationship via a set of primary and foreign keys (as you learned in the previous section) or through a third table known as a linking table (also known as an associative table ). The manner in which you establish the relationship really depends on the type of relationship that exists between the tables. (You'll learn more about that in a moment.) Figure 3.11 illustrates a relationship established via primary/foreign keys, and Figure 3.12 illustrates a relationship established with a linking table.

Figure 3.12. A relationship established between two tables with the help of a linking table.

A relationship is an important component of a relational database.

• It enables you to create multitable views.

• It is crucial to data integrity because it helps reduce redundant data and eliminate duplicate data.

You can characterize every relationship in three ways: by the type of relationship that exists between the tables, the manner in which each table participates, and the degree to which each table participates.

Types of Relationships

There are three specific types of relationship (traditionally known as a cardinality ) that can exist between a pair of tables: one-to-one , one-to-many , and many-to-many .

One-to-One Relationships

A pair of tables bears a one-to-one relationship when a single record in the first table is related to only one record in the second table, and a single record in the second table is related to only one record in the first table. In this type of relationship, one table serves as a "parent" table and the other serves as a "child" table. You establish the relationship by taking a copy of the parent table's primary key and incorporating it within the structure of the child table, where it becomes a foreign key. This is a special type of relationship because it is the only one in which both tables may actually share the same primary key.

Figure 3.13 shows an example of a typical one-to-one relationship. In this case, EMPLOYEES is the parent table and COMPENSATION is the child table. The relationship between these tables is such that a single record in the EMPLOYEES table can be related to only one record in the COMPENSATION table, and a single record in the COMPENSATION table can be related to only one record in the EMPLOYEES table. Note that E MPLOYEE ID is indeed the primary key in both tables. However, it will also serve the role of a foreign key in the child table.

Figure 3.13. An example of a one-to-one relationship.

One-to-Many Relationships

A one-to-many relationship exists between a pair of tables when a single record in the first table can be related to many records in the second table, but a single record in the second table can be related to only one record in the first table. (The parent/child model I used to describe a one-to-one relationship works here as well. In this case, the table on the "one" side of the relationship is the parent table, and the table on the "many" side is the child table.) You establish a one-to-many relationship by taking a copy of the parent table's primary key and incorporating it within the structure of the child table, where it becomes a foreign key.

The example in Figure 3.14 illustrates a typical one-to-many relationship. A single record in the AGENTS table can be related to one or more records in the ENTERTAINERS table, but a single record in the ENTERTAINERS table is related to only one record in the AGENTS table. As you probably have already guessed, A GENT ID is a foreign key in the ENTERTAINERS table.

Figure 3.14. An example of a one-to-many relationship.

This is by far the most common relationship that exists between a pair of tables in a database. It is crucial from a data-integrity standpoint because it helps to eliminate duplicate data and to keep redundant data to an absolute minimum.

Many-to-Many Relationships

A pair of tables bears a many-to-many relationship when a single record in the first table can be related to many records in the second table and a single record in the second table can be related to many records in the first table. You establish this relationship with a linking table . (You learned a little bit about this type of table at the beginning of this section.) A linking table makes it easy for you to associate records from one table with those of the other and will help to ensure that you have no problems adding, deleting, or modifying related data. You define a linking table by taking copies of the primary key of each table in the relationship and using them to form the structure of the new table. These fields actually serve two distinct roles: Together, they form the composite primary key of the linking table; separately, they each serve as a foreign key.

A many-to-many relationship that is not properly established is "unresolved." Figure 3.15 shows a classic and clear example of an unresolved many-to-many relationship. In this instance, a single record in the STUDENTS table can be related to many records in the CLASSES table and a single record in the CLASSES table can be related to many records in the STUDENTS table.

Figure 3.15. An example of an unresolved many-to-many relationship.

This relationship is unresolved due to the inherent peculiarity of the many-to-many relationship. The main issue is this: How do you easily associate records from the first table with records in the second table? To reframe the question in terms of the tables shown in Figure 3.15, how do you associate a single student with several classes or a specific class with several students? Do you insert a few S TUDENT fields into the CLASSES table? Or do you add several C LASS fields to the STUDENTS table? Either of these approaches will make it difficult for you to work with the data and will affect data integrity adversely. The best approach for you to take is to create and use a linking table, which will resolve the many-to-many relationship in the most appropriate and effective manner. Figure 3.16 shows this solution in practice.

Figure 3.16. Resolving the many-to-many relationship with a linking table.

It's important for you to know the type of relationship that exists between a pair of tables because it determines how the tables are related, whether or not records between the tables are interdependent, and the minimum and maximum number of related records that can exist within the relationship. You'll learn much more about relationships in Chapter 10.

Types of Participation

A table's participation within a relationship can be either mandatory or optional . Say there is a relationship between two tables called TABLE_A and TABLE_B.

• TABLE_A's participation is mandatory if you must enter at least one record into TABLE_A before you can enter records into TABLE_B.

• TABLE_A's participation is optional if you are not required to enter any records into TABLE_A before you can enter records into TABLE_B.

Let's take a look at an example using the AGENTS and CLIENTS tables in Figure 3.17. The AGENTS table has a mandatory participation within the relationship if an agent must exist before a new client can be entered into the CLIENTS table. However, the AGENTS table's participation is optional if there is no requirement for an agent to exist in the table before a new client can be entered into the CLIENTS table. You can identify the appropriate type of participation for the AGENTS table by determining the way its data is being used in relation to the data in the CLIENTS table. For example, when you want to ensure that each client is assigned to an available agent, you make the AGENTS table's participation within the relationship mandatory.

Figure 3.17. The AGENTS and CLIENTS tables.

Degree of Participation

The degree of participation determines 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 a given table is allowed to have associated with a single record in the related table.

Consider, once again, a relationship between two tables called TABLE_A and TABLE_B. You establish the degree of participation for TABLE_B by indicating a minimum and maximum number of records in TABLE_B that can be related to a single record in TABLE_A. If a single record in TABLE_A can be related to no fewer than 1 but no more than 10 records in TABLE_B, then the degree of participation for TABLE_B is 1,10 . (The degree of participation is notated with the minimum number on the left and the maximum number on the right, separated by a comma.) You can establish the degree of participation for TABLE_A in the same manner. You can identify the degree of participation for each table in a relationship by determining the way the data in each table is related and how the data is being used.

Let's consider the AGENTS and CLIENTS tables in Figure 3.17 once more. If you require an agent to handle at least one client, but certainly no more than eight, then the degree of participation for the CLIENTS table is 1,8 . When you want to ensure that a client can only be assigned to one agent, then you indicate the degree of participation for the AGENTS table as 1,1 .

---

Top