Configuring Data Relationships

Referential Integrity

Introduction

At this time, we know that there is useful functionality in creating relationships between tables as they allow the user to select existing information instead of typing it. This flow of information brings up issues about what happens if data that exists in a parent table gets deleted while such information has been made available to another table.

When manipulating data that is in a relationship, it is very important to make sure that data keeps its accuracy from one table or source to the other. To accomplish that goal, some rules must be established to "watch" or monitor the flow of information between two tables. Data or referential integrity is used to check that two tables are related through one (sometimes more than one) field on each table used as the primary key and the foreign key, data entered in the foreign key of a child table must exist in the parent table, otherwise it would be rejected. Only two fields of the same data type are used to establish a relationship between two tables, the tables involved in the relationship belong to the same database.

Cascades on Related Records

After creating a legitimate relationship between two tables, you must make sure that when data changes in the parent table, this change is reflected in the child table. For example, if a bank customer changes her last name after getting married or after a divorce, you should be able to change her name in one object (table) and the related objects, such as the one used to process her transactions would receive the changes without your having to make the change on each object (table). In the same way, when data is deleted, the objects that are related to it must also have that data deleted.

To enforce the rules of data integrity, Microsoft Access provides three check boxes in the Edit Relationship dialog box. First, if you want Microsoft Access to monitor data flow, you can click the Enforce Referential Integrity check box. This would make available two check boxes.

The Direction of a Relationship

The One-To-Many Relationship

As mentioned already, a relationship between two tables allows one table, the parent, to make its information available to another table (the child). Because in this case the user is asked to select information, it is likely that the same record on a parent table can be tied to various records in the child table. For example, one customer at a bank can deposit an amount of money today. The same customer can make another deposit tomorrow and even another deposit next month. In such a case, the relationship between the tables would show various entries of the same customer's account number in the object (table) used to deposit money but with different transactions. This type of relationship is known as one-to-many because one entry in the parent table can result in many entries in the child table.

To create a one-to-many relationship, check all three referential integrity check boxes and click Create. The parent table would have a 1 sign on its side of the joining line. The child table would have the infinity symbol on its side of the joining line.

Practical Learning: Managing Referential Integrity

1. Start Microsoft Access
2. Open the Bethesda Car Rental2 database from the previous lesson
3. On the Ribbon, click Database Tools
4. In the Relationships section, click Relationships
5. Right-click the line between Categories and Cars. Click Edit Relationship...
    
   
6. In the Edit Relationship dialog box, click the Enforce Referential Integrity check box
7. Click the two check boxes under it
    
   
8. Click OK.
   Notice the 1 and the ∞ symbol
9. Double-click the line between Cars and Rental Orders
10. In the Edit Relationship dialog box, click the Enforce Referential Integrity check box and click the two check boxes under it
11. Click OK
12. Do the same for the other two remaining lines
     
    
13. Save and close the Relationships window

Although one-to-many is the most common type of relationship applied to records of a table, in some databases, you may need to create a relationship in which many records from one table A can have many related records in another table B and vice versa. This type of relationship is known as many-to-many. For example, in our Video Collection database:

• It is possible to have one video that has many actors. In this case, if we had created a field to receive actors in the Videos table, we would enter many names of actors in that one field but this type of database would not be professional
• At the same time, one actor can have participated in many videos. In this case, if we had created a field to enter the titles of videos in the Actors table, the field would have too many entries, making it unprofessional

To implement this type of relationship, you can create what is called a junction table. A junction table is a table whose main purpose is to bring together fields from other tables, creating a type of cross relationship for the necessary fields:

A junction table contains at least three fields. The first field, almost less engaged, is used as the primary key, the same type of field almost every table has. The other fields hold data that would originate from other tables.

You can create a junction table either in Datasheet View or in Design View:

• To create a junction table in Datasheet View, you use the Lookup Column menu that would call the Lookup Wizard. From the Lookup Wizard, select the table that holds the desired field, then select the field itself, and click Finish. After adding the first column, repeat the same steps for each needed field
• To create a junction table in Design View, set the field's data type to Lookup Wizard and proceed the same way you would for the Lookup Column

You can also switch to either view to create a junction table. In other words, you can create one field in one view and create the other field in the other view.

Practical Learning: Creating Junction Tables

1. Open the Video Collection3 database you started earlier
2. To create a new table, on the Ribbon, click Create and, in the Table section, click Table Design
3. Type VideoActorID as the name of the first field
4. Set its Data Type to AutoNumber
5. Right-click the VideoActorID name and click Primary Key
6. Complete the table with the following two fields:
    

   Field Name	Data Type	Caption	Field Size
   VideoActorID (Primary Key)	AutoNumber	Video Actor ID
   VideoID	Number	Video	Long Integer
   ActorID	Number	Actor	Long Integer

7. Save the table as VideosAndActors
8. Switch it to Datasheet View
9. Enter the records as follows:
    

   VideoActorID	VideoID	ActorID
   1	1	1
   2	3	4
   3	2	23
   4	4	6
   5	1	7
   6	9	5
   7	6	2
   8	4	14
   9	1	9
   10	2	8
   11	3	19
   12	1	10
   13	6	17
   14	3	20
   15	1	11
   16	4	16
   17	3	18
   18	1	15
   19	12	21
   20	6	3
   21	2	5
   22	1	13
   23	5	1
   24	12	22
   25	1	12

10. Close the table

A one-to-one relationship is the type of junction between two tables A and B so that one record in table A can have only one corresponding entry in table B and vice versa. Because this is similar to one table of records, this type of relationship is hardly used since you can as well simply create one table.

Data Joins

When creating relationships among tables, we were selecting the primary key of one table. Here is an example of such a table:

We also know how to create a foreign key of a dependent table to join them. Here is an example named GenderID:

Once such a relationship is created, you can create a query that combines both tables to create a set of records, also called a record set, that would include either all records or isolate only the records that have entries. For example, imagine you have created a Persons table as follows where the Gender of a record is selected from a lookup field:

Suppose you want to create a query that includes the persons of this table and their genders. A question that comes is mind is: Do you want to create a list of only people who can be recognized by their gender, or do you want the list to include everybody? This concept leads to what are referred to as joins of queries. There are two ways you can get such queries: you can prepare the relationship between two tables to be aware of this type of relationship or you can directly create it when designing a query.

When building a query, you select fields and ask Microsoft Access to isolate them as being part of the query. Most of the time, you will want only fields that include a type of validation of your choice (a criterion). An inner join is the kind of query that presents only fields that have matching entries in both tables of a relationship. For example, from the above Persons table, you may want to create a query that includes only persons whose records contain the gender:

You would create the query as an inner join:

To specify that a relationship is inner join on tables, after creating the relationship, while in the Relationships, you can display its Edit Relationship dialog box and click Join Type. The Join Properties dialog box presents three options that allow you to define the direction of the relationship between the tables.

To specify an inner join in a SQL statement, you include INNER JOIN in the statement. For example, the code of the above query is:

SELECT Persons.FirstName,
       Persons.LastName,
       Genders.Gender
FROM Genders INNER JOIN Persons ON Genders.GenderID = Persons.GenderID;

Practical Learning: Creating Inner Join Relationships

1. The Video Collection3 database should still be opened.
   On the Ribbon, click Database Tools. In Relationships section, click the Relationships button
2. If either of the Videos or the Directors table is not displaying, right-click an area in the Relationships window and click Show Tables...
   In the list of tables, double-click the missing table(s) (Directors and/or Videos) and click Close.
   Click the joining line between the Directors and Videos
3. In the Tools section of the Ribbon, click Edit Relationships
4. After making sure that DirectorID is selected in each combo box, click the Enforce Referential Integrity check box
5. Click the two check boxes under Enforce Referential Integrity
6. Click the Join Type button
7. In the Join Properties dialog box, read all options then click the second radio button
   
    
8. Click OK and OK
9. In the same way, complete the relationships of the Video Collection database (when the relationship does not exist, you must create it)
    
   
10. Save and close the Relationships window
11. Open the Bethesda Car Rental2 database
12. Open the Relationships window and configure all relationships as follows:
     
    
13. Save and close the Relationships window
14. On the Ribbon, click Create and, in the Forms section, click Form Design
15. Save the form as Cars and change the following characteristics in the Property Sheet:
    Record Source: Cars
    Caption: Bethesda Car Rental - Cars
16. Design the form as you see fit. Here is an example (no need to match the fonts; use whatever font you have on your computer):
     
    
17. Save the form and switch it to Form View
18. Create the cars records and make sure you add the pictures from the resources of these lessons
19. Close the form

The queries we have used so far and that were based on related tables allowed us to get only the fields that had entries based on the established relationships. Fields that did not follow the rules were excluded. Instead of excluding fields, the SQL allows you to create a query that includes all fields, not just those that follow rules, as long as the records are part of either table. Such a query is referred to as outer join. To manage the result of this type of query, the SQL considers the direction of a relationship.

When creating relationships, we learned to drag a primary key from one table, the parent, to a dependent table, the child. In this type of relationship, the table (or query) that holds the origin of the relationship is referred to as the Left table. The other table is referred to as the right table. Based on this, there are two types of outer joins: the left join represented in SQL as LEFT JOIN and the right join represented by RIGHT JOIN.

As done with the inner join relationship, the left and right joins can be prepared in the Relationships window on tables. If the query has already been created and you want to change its direction, you can change it in the Design View of the query where you would first right-click the joining line and click:

In the Join Properties dialog box, click the radio button that has 2:

And click OK.

After creating a relationship or while working on relationships, you can create a visual report of the result and be able to print it when necessary. To create the report, in the Tools section of the Ribbon, click the Relationship Report button , which would highlight it . This action would automatically generate a report with the relationships designed on it. To keep the report, you should save it and give it a name. You can then print it as you would print any other report.

Practical Learning: Creating a Relationship Report

1. The Bethesda Car Rental2 database should still be opened.
   On the Ribbon, click Database Tools
2. In the Relationships section, click the Relationships button
3. In the Tools section of the Ribbon, click the Relationship Report button
4. When the report has been created, close it
5. When asked whether you want to save, click Yes
6. Type Bethesda Car Rental Relationships as the name of the report
7. Click OK
8. In the Navigation Pane, right-click the Bethesda Car Rental Relationships report and click Print...
9. Close the database