cmiller1137 and Ron McFadyen
In this chapter, we will sharing information about database relationships and how relationships are defined from one table to another. The Relationships Tool is used to define relationships between tables based on common fields. Relationships defined using the Relationships Tool are important as they help ensure integrity of data and they provide us with default join criteria for queries involving more than one table.
In this section, we will use the University and the Library databases in our examples.
Consider the University database that contains a Department table and a Course table. These two tables have the deptCode field in common:
In the Department table, deptCode is the primary key and is used to identify a specific department.
In the Course table, the deptCode field is a part of the primary key and indicates the department to which the course belongs.
To ensure that a row in Course is related to an existing row in Department, we can use the Microsoft Access -Relationships Tool to define a relationship between these two tables based on this common field. Using a diagram, we can illustrate this connection between these two tables:
Figure 5.1 Displaying a relationship between two tables
In this situation, we say that deptCode in Course is a foreign key referencing the deptCode field in Department.
Now, consider the Library database:
The Loan table has a callNo field as well as the Book table; the callNo field identifies a specific book.
The Loan table has an id field as well as the Member table; the id field identifies an individual member.
In the Library database, we can establish a relationship between the Loan table and the Book table based on the callNo field. A second relationship can be established between the Loan table and the Member table based on the id field. Using a diagram, we can illustrate these two relationships:
Figure 5.2 Showing relationships involving three tables
The Loan table has two foreign keys identified as callNo and id:
The callNo field in Loan references the primary key (callNo) in Book.
The id field in Loan references the primary key (id) in Member.
5.1 Database Integrity In Relational Database Design
Primary Key
Recall that a table’s primary key (PK) is a field (possibly composite) that has unique values. Each record row has a PK value different from any other row in the table. Primary key is a field with a unique identifier. If a query were designed to retrieve a row of that table based on a value of the PK, then at most one row of the table will be retrieved.
Foreign Key
A foreign key is a field (or combination of fields) in a table B that is associated with a primary key field in a table A through a relationship (A and B can be the same table). Data redundancy is eliminated by having a foreign key in one table related to a primary key in another table.
Entity Integrity
When we define a primary key for a table, we are enforcing entity integrity. Entity integrity means that each