Cascades on Related Records
After creating a legitimate relationship between two tables, you must then 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 allow 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: Editing Relationships
- Open the Music Collection1 database you were working on in the
- To display the Relationships window, on the main menu, click Tools -> Relationships…
- To control the relationships, on the Relationships window, click the joining line between
MusicCategories and MusicAlbums. Notice that it becomes thicker than the others
- On the main menu, click Relationships -> Edit Relationship…
- Click the Enforce Referential Integrity check box.
Now, the database would like to know how you would handle data updating and
- Check the other two check boxes:
- Click OK
- In the Relationships window, right-click the joining line between the Artists and MusicAlbums tables:
- Click Edit Relationship... from the popup menu
- Click all the three check boxes and click OK
- In the Relationships window, double-click the joining line between MusicAlbums and AlbumTracks
- In the Edit Relationship dialog box, click all three check boxes and click OK
- Using one of the above three techniques, configure the joining line between the MusicAlbums and the Formats tables for a one-to-many relationship that performs both cascade update and cascade deletes
- Save the relationships window and close it
The Many-to-Many Relationship
Although one-to-many is the most common type of relationship applied on tables, 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
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 can be very helpful for data analysis and
A junction table is usually made of three or four fields (usually not less than three and usually not more than four; a classic junction table has only 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
- Open the Video Collection database you started in lesson
- To create a new table, on the main menu, click Insert -> Table
- From the New Table dialog box, double-click Design View
- Type VideoActorID as the name of the first field
- Set its Data Type to AutoNumber
- Right-click the VideoActorID name and click Primary Key
- Save the table as VideosAndActors
- Click the empty field under VideoActorID, type ActorID then press Tab, type
L and press F6
- In the first page of the Lookup Wizard, make sure the first radio button is selected and click Next.
- On the second page of the wizard, make sure Actors is selected and click Next
- In the Available Fields list, double-click Actor and click Next twice
- Change the field's label to Actor/Actress and press Enter
- When asked to save the table, click Yes
- Switch the table to Datasheet View
- In Datasheet View, right-click Actor/Actress and click Lookup Column…
- In the first page of the Lookup Wizard, make sure the first radio button is selected and
- From the list of tables, click Videos and click Next
- From the Available Fields, double-click Title to select it and click Next
- Make sure the Hide Key Column check box is marked and click Next
- Change the label to Video Title and click Finish
- Switch the table to Design View
- Set the Description of VideoID to The title of a video
- Set the Description of ActorID to An actor or actress who participated in this video
- Save the table and switch it to Datasheet View
- To perform data entry, on the Video Title column, click the arrow of the combo box and select A Few Good Men
- Then, on the Actor/Actress column, click the arrow of the combo box and select Jack Nicholson
- In the same way, complete the table as follows:
- Close (and if necessary save) the table.
- To open the Relationships window, on the main menu, click Tools -> Relationship...
- Observe the names of tables on the window
Right-click an empty area of the Relationships window and click Show Table...
- On the Show Table dialog box, double-click Directors table. Also, if there is a table on the Show Table property sheet that is not displaying in the Relationships window, add it
- To close the Show Table property sheet, click the Close button
- Position the VideosAndActors table between the Actors and the Videos tables
- Drag DirectorID from Directors and drop it on top of DirectorID in Videos
- Check the Enforce Referential Integrity check followed by the Cascade Update Related Fields and Cascade Delete Related Fields check boxes
- Click Create
- As done previously, double-click each joining line and enforce its reference integrity to both update and delete records in the Edit Relationship dialog box
- If you are using Microsoft Access 2000 and later, on the main menu, click File -> Print Relationships...
- Click the close button. When asked to save the report, click Yes. Change the name of the report to Video Collection Layout and click
- Close the Relationships window (in Microsoft Access 97, if you are asked to save the window, click Yes).
The One-to-One Relationship
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.
A sub datasheet provides a technique of creating or displaying relationships in a table in a Datasheet View. It can help you or the user see information that is stored in another table. For example, when using a music collection database, it can should the music tracks that are part of an album by displaying an album in a table as long as a relationship has been created and configured.
To use a sub datasheet, open a table or query in Datasheet View and click the + button on the first column of the desired record. Here is an example:
|The subdatasheet is not available in
Microsoft Access 97
Practical Learning: Using a Sub Datasheet
- Open the Music Collection database
- On the Database Window, from the Tables section, double-click the MusicAlbums table
- Click the + button on the left of Tribute
- Notice that the tracks of the music album display
- To perform data entry, click the + button of the One Bright Day record
- Click the empty field under # then type 1 and press Enter
- Complete the list of tracks as follows:
|| Track Title
|| Black My Story (Not History)
|| One Bright Day
|| Who Will Be There
|| When The Lights Gone Out
|| All Love
|| Look Who’s Dancing
|| Love Is The Only Law
|| Pains Of Life
|| Urban Music
|| All You Got
|| When The Lights Gone Out (Jamaican Stylee)
- After viewing the table, close it
Display related records in a subdatasheet
Enforce referential integrity
Print database relationships
- Open the Watts A Loan database and configure the cascading of
relationships as follows:
Save and close the Relationships window
- Open the Yugo National Bank database and open its Relationships
window. Enforce the cascades on relationships as follows and save it:
If you are using Microsoft Access >= 2000, print the Relationships
dialog box and save its report as YNB Main Diagram