Create Relationships

Now that we have made all the tables, it is finally time to create the “Relationships” between them. To create a “Relationship”, first close all tables by clicking the X in the upper right (or left on a Mac) corner of the table.

When all tables are closed, choose the contextual tab “Database Tools” in the “Ribbon” and click the “Relationships” button.

The “Relationships” tab opens in the window.

If it is the first time you’re creating a “Relationship” in this “Database”, a “Show Table” dialog appears :

You can establish “Relationships” between “Tables”, “Queries” or both. You have these tabs at the top of the “Show Table” dialog box . Because we do not have queries as yet, I’ll go with the “Tables” tab.

In the “Show Table” under the “Tables” tab, we see the three tables which we have already created in our “Database”. Because we need to connect these with each other, we must put them under the “Relationships” tab.
To do this, select the “Tables” tab in the dialog box and click the “Add” button. The faster way is by double-clicking on a table in the dialog. We repeat this for each table and click the close button when we’re done.

Now all our tables are shown in the “Relationships” tab. If your tables are not placed together, you can move them by clicking on the title bar of the table, and dragging to the right place. When all fields from a table are not visible, place your mouse pointer over the edge of the table and when it changes into a double arrow, click and drag it to the desired size.

To connect the tables, click and drag the “AlbumID” from the “Album” table, onto the “AlbumID” field of the “Song” (1) table.
This opens the “Edit Relationships” window.

In this window you can see that a “one-to-many” “Relationshipship” is included, which is what we are creating. The “AlbumID” from the two tables, one from the “Album” table connects to the one from the “Song” table. We also have the “Join Type …” button. This button is useful when you create a “Relationship” between two tables that already have data.

When we click it opens a dialog with three options:

1. Only include rows where the joined fields from both tables are equal.
2. Include ALL records from ” Album” and only those records from “Song” where the joined fields are equal.
3. Include ALL records from “Song” and only those records from “Album” where the joined fields are equal.

Explanation:

The first option is comes down to this:
“Show only the Albums with songs.”

The second option corresponds to:
“Show All albums, including those that have no songs.”

The third option we could interpret as:
“Show All the songs, including those for which no there is no album number.”
The “Album” table already has several records, but the “Song” table has no records. So the second option seems the best choice, so click OK.
As the last item in the “Edit Relationships” window, we have the “Referential integrity”. This is quite important.

What is “Referential Integrity”?

Example:
You have a one-to-many “Relationship” between the “Album” table and the “Song” table and you want to delete an album from the “Album”table.
Now if the album that you want to delete has songs in the “Song” table, these songs will be “unconnected songs.”

The songs still have a AlbumID in the “Songs” table but, this AlbumID no longer exists because, you have just removed it from the “Album” table.

This is why we have the “Referential Integrity” checkbox. “Referential integrity” is intended to prevent non-linked records. When this box is checked, “Access” will refuse any operation that would violate the table “Relationship”. For example, removing a AlbumID in the “Album” table.
When the “Enforce Referential Integrity” checkbox is selected, we have the opportunity to select the two underlying boxes.

The first, “Cascade Update Related Fields” where the changes to primary field from one table changes related fields in the other table automatically.
Example: you change “AlbumID” field in the “Album” table, then the “AlbumID” field in the “Song” table changes automatically.

When the “Cascade Delete Related Records” check box is selected, we can delete records from the “Album” table and it then removes related field in the “Song” table automatically. Normally this would not have been possible because the “Enforce Referential Integrity” box was checked.

For this “Relationship”, we select all three checkboxes and click OK.

Do the same for the “SingerID” field in the “Lead Singers” table. Drag it to the “SingerID” field in the “Song” table. Only that for the “Join properties”, choose “only include rows where the joined fields from both tables are equal”, which is the default setting.

Then we have this:

You can always change the settings of the “Relationship” by double-clicking the line between the two tables, or by selecting a line, and clicking on the “Edit Relationships” button in the “Ribbon”.

To remove a “Relationship”, select the line of the “Relationship” and click the “Delete” button on your keyboard. Alternatively, you can right-click (Control+Click on a Mac) a line and select “Delete” in the drop-down menu.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats