Reviewing Database Relationships

Before we dive into setting up a relationship, let's be sure we understand the different types of relationships that can be created in Access:

• One-to-many
• One-to-one
• Many-to-many

Let's start with one-to-many.

Reviewing One-to-Many Relationships

The most commonly used type of relationship in relational databases is the one-to-many relationship. The relationship you see now between tblDepartments and tblFaculty is an example of this. It shows that one department can have many faculty members, but a faculty member can only belong to one department.

The key structure of the two tables shown the following diagram further illustrates this concept:

In our database, we can see that the DeptCode field is the primary key in tblDepartments, but it is not a unique field in the tblFaculty table since multiple faculty members are assigned the same department. The DeptCode field in the tblFaculty table is referred to as a foreign key. Recall from Access: The Basics, a foreign key is used in conjunction with a primary key to relate a record in one table to a record in another.

NOTE: It is also important to keep in mind that every DeptCode field value in the tblDepartments table does not have to be used in the tblFaculty table. For example, a department might exist at the university and have faculty, but the faculty data may not exist in the database yet.

Let's add tblCourses to the relationship scheme.

Step1. To open the Show Table dialog box, on the Ribbon,

Click the Relationships Design tab, Click 

You see the Add Tables pane.

Step2. To add tblCourses to the Relationships window,

Double-Click tblCourses

NOTE: You can also Press & Drag tblCourses from the Navigation pane into the Relationships window.

You may find it useful to rearrange the tables at this point. Recall from Access: The Basics that the layout of the tables here doesn't impact the relationships, simply how easy it is for us to see and manipulate them.

Step3. To rearrange the tables in the Relationships window, if desired,

Press & Drag the tables into the following order: tblFaculty, tblDepartments, and tblCourses

While this step isn't necessary, it might help visualize the relationships in a more linear fashion. Ultimately, you can arrange them however you would like.

Let's create a relationship between tblCourses and tblDepartments on DeptCode.

Step4. To relate tblCourses to tblDepartments,

Press & Drag DeptCode from tblCourses to DeptCode in tblDepartments

You see the Edit Relationships dialog box:

Access already recognizes that this is a one-to-many relationship since it can look at the underlying table structure. We just need to work with the relationship properties just a bit.

Reviewing Relationship Properties

When you start to build a relationship, you are presented with several options. Let's take a look at them as they relate to the DeptCode relationship.

Enforcing Referential Integrity

Referential integrity is system of rules that Access uses to ensure that you don't have orphaned records. In this case, that means you won't have a course with a DeptCode that does not exist in tblDepartments. It can also be used to make sure that records are not accidentally deleted or changed. When two tables are related, the records in the table containing the primary key are called the parent records, while the records in the table containing foreign keys are called child records. In our database, in the relationship between tblFaculty and tblDepartments, the Department record in tblDepartments would be the parent record and the related faculty in tblFaculty would be the child records.

If a database does not enforce the rules of referential integrity, it would be very easy for a child record to become an orphaned record. An orphaned record is a child record that has no record with a matching key in the parent table. In other words, referential integrity keeps a course from being assigned to a "phantom" department.

Referential integrity applies to primary and foreign key fields only. When referential integrity is enabled, Access will enforce the following rules to prevent orphaned records:

• A value may not be entered in the foreign key field of the related table that does not also exist in the primary key field in the primary table. For example, you cannot have a DeptCode in tblCourses that does not exist in tblDepartments.
• The value of the primary key in the primary table may not be changed if matching records exist in a related table. For example, you cannot change a DeptCode value in tblDepartments if there is one or more associated courses in tblCourses.
• A record from the primary table may not be deleted if matching records exist in a related table. For example, you cannot delete a department from tblDepartments if there are associated courses in tblCourses.

If allowed, all of the previous situations would create orphaned records, which would deteriorate the integrity of the data in the database. For this reason, referential integrity should be enforced in nearly all situations.

NOTE: Even though referential integrity is enforced, null values in the foreign key field do not violate referential integrity. For example, you may leave the DeptCode foreign key field blank when creating a new record in tblCourses if we hadn't set the Required property for the field to Yes earlier.

We will follow best practices and instruct Access to enforce referential integrity.

Step1. To enforce referential integrity,

Click the "Enforce Referential Integrity" checkbox

NOTE: If there are already existing records in the related table that violate the referential integrity rule, Access will display an error message and require that you fix the problem before attempting to enable referential integrity.

Enabling referential integrity is an important step in maintaining consistent data in a database; however, depending upon the requirements of a business or institution, it may be beneficial to override the record updating rule of referential integrity.

Next we will discuss a couple of options on how to override the underlying rules of referential integrity when it is enabled.

Cascade Update Related Fields

By enabling the Cascade Update Related Fields option, Access will automatically change foreign key values in related tables when a primary key value is changed. In this case, we will assume that we want our data entry personnel to be able to change DeptCode values. That way, when a DeptCode value in the parent table tblDepartments is changed, the related values in the child table tblCourses would be automatically updated to the new values. Cascading updates also preserves the relationship between the records. If we did not want to allow users to change a primary key value for any reason, then we would leave this option unchecked.

NOTE: If the primary key is an AutoNumber field, enabling Cascade Update Related Fields will have no effect because you can't change a value in an AutoNumber field.

Step1. To turn on Cascade Update Related Fields,

Click the "Cascade Update Related Fields" checkbox

Let's explore the last checkbox in this dialog box.

Cascade Delete Related Records

The Cascade Delete Related Records option tells Access to delete any related child records if the associated parent record is deleted. Enabling this option is almost never a good practice. For example, if this option were checked, and if we deleted a department from the tblDepartments table, then all of the courses in tblCourses offered by that department would also be deleted. This would not make sense because universities need to maintain historical data to keep track of courses taught in the past. Since deleting records has potentially disastrous results, it is important to think carefully about whether or not to enable this option.

Remember that the Cascade Update and Cascade Delete options apply only to related primary and foreign key values between parent and child records.

This relationship is ready to go. Let's create it.

Step1. To create the relationship,

Click

You are returned to the Relationships window. Next we will look at some other types of relationships.

Introducing One-to-One Relationships

A one-to-one relationship can be another useful way to join tables, but it is not as commonly used. A one-to-one relationship between two tables is where two tables are related by their respective primary keys rather than a primary and foreign key.

Whenever you create a relationship between two tables on their primary key fields, you are creating a one-to-one because the record has to be unique in each table. For every record in the first table, only one record can exist in the second table, as shown here between the tblFaculty and tblGovID tables:

NOTE: While this is a common real-world example of a one-to-one relationship, Access does not provide sufficient security measures to securely store government identification numbers like social security numbers, according to IU's data management policies. For more information about data security, visit the IU Data Management website.

In this one-to-one relationship example, a record in the tblFaculty table can have only one matching record in the tblGovID table. Also, just like in a one-to-many relationship, there need not be a matching record in the tblGovID table for every record in the tblFaculty table.

Conceptualizing Many-to-Many Relationships

The concept of a many-to-many relationship is where a record in the primary table may have many related records in the related table and a record in the related table may have many related records in the primary table. This type of relationship can be somewhat confusing and cannot be directly defined in relational databases, including Access.

Let's look at a simple diagram illustrating the concept of a many-to-many relationship between tblFaculty and tblCourses.

In this example, a single faculty member can teach many courses. This essentially creates a one-to-many relationship from tblFaculty (one side) and tblCourses (many side). Taking this one more step, since popular courses are offered more than once each semester, each course can have several instructors. This means a course would need to either appear multiple times in tblCourses, or we would have to modify the record structure of tblCourses to include an arbitrary number of instructors. Both solutions will violate referential integrity.

In this situation, violating referential integrity poses the problem of determining who is teaching the course, where, and at what time. Since tblCourses does not have date and time or location fields, we have no way to answer the question, "Who is teaching MATH-R118 on Mondays at 4 p.m.?" This problem can't be solved by simply adding fields to tblCourses because we would be unable to distinguish between courses that are taught by three instructors or ones being taught by a single instructor. Nor can we tell if that one instructor is teaching four sessions or just one.

Here's a graphical representation of the many-to-many relationship between tblFaculty and tblCourses:

A many-to-many relationship must be broken down to create two one-to-many relationships. The most common way to facilitate this type of relationship is to create a junction table, which works as a bridge between the two tables, as shown in the following diagram:

NOTE: You may see junction tables also called join tables. The two terms are interchangeable. These materials will refer to only junction tables.

In our example today, the junction table must contain the primary key from both tables joined by the relationship, which makes up the composite primary key. A composite primary key (or composite key) is multiple fields that together identify a unique record. For example, CourseCode can repeat in the junction table to represent multiple instructors teaching a particular course, but a particular faculty member cannot be assigned to the same course twice.

NOTE: This problem can also be solved by creating an autonumber field that would serve as the primary key. The only difference between the two possible key solutions is that with the autonumber field, an instructor could be assigned to teach the same section twice. We will look into how to prevent this kind of behavior later in the workshop.

As seen in this example, the junction table may also contain other information regarding the joined data. None of the fields in a composite key can be empty. While values for each of these fields individually can be duplicated within the table, the combination of the composite key fields cannot be duplicated. Specifically, section 1901 can be taught by instructor 1000552386, but once that instructor is assigned to section 1901, they cannot be assigned to that same section again. In this example, the junction table will allow various faculty members to teach the same course without violating primary key integrity.