Home

Managing Data Relationships

Characteristics of Data Relationships - Referential Integrity

Introduction

A data relationship between two tables allows 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 the records keep their 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. 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.

Practical Learning: Integrating Referential Integrity

  1. Start Microsoft Access
  2. From the list of files, click Ceil Inn1 from the previous lesson
  3. On the Ribbon, click Database Tools
  4. In the Relationships section, click Relationships

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). It is likely that the same record on a parent table can be tied to various records in the child table. This type of relationship is referred to 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, in the Edit Relationships dialog box, click the Enforce Referential Integrity check box. 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. Right-click the line between MaritalsStatus and Employees. Click Edit Relationship...

    Relationships

  2. In the Edit Relationship dialog box, click the Enforce Referential Integrity check box

  3. Click OK.
    Notice the 1 and the ∞ symbol
  4. Double-click the line between FilingsStatus Employees
  5. In the Edit Relationship dialog box, click the Enforce Referential Integrity check box
  6. Click OK
  7. Do the same for the other remaining lines

    Relationships

  8. Close the Relationships window
  9. When asked whether you want to save, 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 Mutual One-To-Many Relationship

Mutual reference is a scenario in which each of two tables references the other. A variant of this feature is where some records of a table A would get their foreign value from a table B but also some records of the table B would get their foreign value from table A. To illustrate this, imagine you have a table of employees and each employee is recorded as belonging to a certain department. Obviously, an employee can (should) belong to only one department. This can be illustrated as follows:

A department for one or many employees

For each department, you may want to specify who the manager is. Obviously, the manager must be an employee, from the table of employees. This can be illustrated as follows:

A manager for each department - the manager is an employee

As another  variant to a one-to-many relationship, some records of a table A may get their foreign value from a table B, then some records of table B may get their foreign value from a table C, and finally some records of table C would get their foreign value from table A.

A Many-To-Many Relationship: Junction Tables

A data relationship is referred to as many-to-many if a record from one table A can be represented by many records from another B, and a record from the table B can have a relationship with many records from the first table A. As an example, if you drive your car on a typical day on one road, your road will intersect with many other roads. Of course, someone else driving on other roads would intersect with the road where you are driving. In other words, a road name I-95 would cross (intersect with) other roads such as Rte 1, MD 198, and I-95, etc. On the other hand, Rte 1 also would consider that it crosses (or intersects with) I-95 and MD 32 among others.

To configure a many-to-many relationship in the Relationships window, you may have to add the table twice.

A Junction Table

As a variance of a many-to-many relationship, instead of just two tables, you can create a junction table that unites two or more tables. You create the junction table the same way you do for two tables: Add a foreign key for each of the tables.

Practical LearningPractical Learning: Creating a Many-To-Many Relationship

  1. On the Ribbon, click File and click New
  2. Click Blank Desktop Database
  3. Set the File Name to Road System1
  4. Click Create
  5. In the default table, double-click ID and type IntersectionID
  6. Close the table
  7. When asked whether you want to save, click Yes
  8. Set the table name as Intersections and press Enter
  9. On the Ribbon, click Create
  10. In the Tables section, click Table Design
  11. Type RoadName and press F6
  12. In the Field Size, type 20
  13. In the top section of the table, right-click RoadName and click Primary Key
  14. Complete the table as follows:
     
    Field Name Field Size
    RoadName  
    RoadType 20
    Distance 12
    Location  
  15. Close the table
  16. When asked whether you want to save, click Yes
  17. Set the name as Roads
  18. Click OK
  19. In the Navigation Pane, double-click Roads
  20. Create the followiong records:
     
    RoadName RoadType Distance Location
    US 322 U.S. Highway 494.00 From Cleveland, Ohio east to Atlantic City, New Jersey
    I-90 Interstate 412.76 I-90 traverses east–west from Seattle (WA) to Boston (MA)
    I-64 Interstate 297.62 From West Virginia to the Hampton Roads
    NE 14 State Highway 203.54 From Superior, KS to SD 37
    I-40 Interstate 2555.10  
    I-80 Interstate 237.48 In Ohio, from I-80 to the north of the state
    US 2 Road 2571.00 Western Segment From Washington Rte 529 to I 75 in Michigan; Eastern Segment From US 11 in NY to I-95 in Maine.
    MD 410 Road 13.920 From East Bethesda to Pennsy Drive in Landover Hills
    I-66 Interstate 76.38 From Middletown, Virginia to US 29 in Washington, DC
    I-476 Interstate 132.10 In Pennsylvania between I-95 near Chester and I-81 near Scranton
    I-29 Interstate 750.58 From Kansas City, MO to Manitoba Highway 75 (in Canada)
    PA 581 Capital Beltway 27.6 Surrounds Harrisburg, PA
    VSR 234 State Highway 33.92 In Virginia, from U.S. Route 1 near Dumfries to U.S. Route 15 near Woolsey
    I-94 Interstate 352.39 From east–west through the central portion of North Dakota
    I-77 Interstate 613.41 Fom West Virginia to North Carolina and Ohio
    US-81 U.S. Highway 229.28 From the Great Plains region (Fort Worth, Texas) to the U.S.–Canadian border at Pembina, North Dakota)
    I-5 Interstate 796.432 From Mexico-United States border to South of Oregon
    I-35 Interstate 1,569.06 From Laredo, TX to Duluth, Minnesota, at Minnesota Highway 61
    US 83 U.S. Highway 1894.00 From Mexico–US border in Brownsville, TX to Westhope, ND, at the Canada–US border
    I-81 Interstate 855.00 From Dandridge, TN to Wellesley Island, NY/Hill Island, ON
    Highway 137      
    I-78 Interstate 75.23 From Union Township in Lebanon County, Pennsylvania to New York City
    US 75      
    I 296 Interstate 3.43 Michigan
    US 85 U.S. Highway 1479 From the US-Mexico border in El Paso, TX to the US-Canada border in Fortuna, ND, to Saskatchewan Highway 35
    I-76 Interstate 434.87 From an interchange with I-71 west of Akron, Ohio, east to I-295 in Bellmawr, New Jersey.
    SR 254 State Highway 25.01 In Virginia, from VSR 42 near Buffalo Gap to U.S. Rte 340 (US 340) in Waynesboro
  21. Close the Roads table
  22. In the Navigation Pane, right-click Intersections and click Design View
  23. Complete the table as follows:
     
    Field Name Field Size
    IntersetionIID  
    Road1 20
    Road2 20
    InNear 120
  24. Close the table
  25. When asked whether you want to save the file, click Yes
  26. In the Navigation Pane, double-click Intersections
  27. Create the followiong records:
     
    Road1 Road2 InNear
    I-29 I-35 In Kansas City, MO
    PA 581 I-81 West of Enola
    I-81 I-77 In Wytheville, VA
    US-81 I-40 In El Reno, Oklahoma
    I-35 I-40 In Oklahoma City, OK
    I-29 I-80 In Council Bluffs, IA
    I-81 I-84 In Scranton, PA
    I-81 I-64 From Lexington, VA to Staunton, VA
    I-94 US 83  
    I-81 Highway 137  
    I-29 I-94 In Fargo, ND
    I-81 I-70  
    I-81 I-66  
    US 322 I-80 In Clarion Township, PA
    I-81 I-80 Near Hazleton, PA
    I-81 I-76 In Penna Turnpike at Carlisle, PA
    I-94 US 85 In Belfield, North Dakota
    I-81 I-40 In Dandridge, TN
    PA 581 I-81 Fom Colonial Park to west of Enola
    I-81 I-476  
    I-29 I-90 Near Sioux Falls, SD
  28. Close the Intersections table
  29. On the Ribbon, click Database Tools
  30. In the Relationships section, click Relationships
  31. In the Show Table dialog box, double-click Roads
  32. Double-click Intersections
  33. Double-click Roads again

    Relationships

  34. Click Close
  35. Drag RoadName from Roads and drop it on Road1 in Intersections
  36. Click Create
  37. Drag RoadName from Roads_1 and drop it on Road2 in Intersections
  38. Click Create

    Relationships

  39. Close the Relationships window
  40. When asked whether you want to save, click Yes
 

A Sub-Datasheet

Introduction

A sub-datasheet is a means of displaying the dependent records of a parent record on a datasheet. There must be a table containing a foreign key so that the child table can be, or has been, connected to the parent table.

Practical Learning: Introducing Subdatasheets

  1. On the Ribbon, click File and click Open
  2. From the resources that accompany these lessons, open the Ceil Inn2 database
  3. From the Navigation Pane, double-click Employees to open its table:

    Lookup and Relationship

  4. From the Navigation Pane, double-click Rooms to open its table:

    Lookup and Relationship

  5. Close both tables
  6. On the Ribbon, click Database Tools
  7. Click the Relationships button
  8. On the Show Table (if it is not displaying, on the Ribbon, click the Show Table button), as the Customers table is selected (if it is not selected, click it to select it), press and hold Shift
  9. Click Rooms to select all tables
  10. Release Shift
  11. On the Show Table, click Add
  12. On the Show Table, click Close
  13. Establish a relationship using the MaritalStatusID from the MaritalsStatus table to the Employees table
  14. Establish a relationship using the FilingStatusID from the FilingStatusID table to the Employees table
  15. Establish a relationship using the EmployeeNumber from the Employees table to the Occupancies table
  16. Establish a relationship using the EmployeeNumber from the Employees table to the Payments table
  17. Establish a relationship using the AccountNumber from the Customers table to the Occupancies table
  18. Establish a relationship using the AccountNumber from the Customers table to the Payments table
  19. Establish a relationship using the RoomNumber from the Rooms table to the Occupancies table

    Lookup and Relationship

  20. Close the window
  21. When asked whether you want to save the relationships, press Enter
  22. Once a relationship has been established between two tables, when you open the parent table in Datasheet View, each record would appear with a + button to its left.
    From the Navigation Pane, double-click Employees to open its table:

    Lookup and Relationship

  23. Close the Employees table:
  24. From the Navigation Pane, double-click Rooms to open its table:

    Sub-Datasheets

  25. This means that Microsoft Access is configured to recognize relationships and apply them to show the sub-datasheet.
    By its definition, a sub-datasheet allows you to view the related records by clicking the + button. This would expand that record and display its related records. You can expand just one record by clicking its + button or a few records by clicking the + buttons of the desired records.
    On the table, click the + button on the 106 record
  26. Also click the + button on the 110 record:

    Sub-Datasheet

  27. When a record is expanded, it displays a - button to its left.
    You can also expand all records.
    If necessary, on the Ribbon, click Home.
    In the Records section of the Home tab of the Ribbon, click More, position the mouse on Subdatasheet, and click Expand All:

    Sub-Datasheet

  28. After viewing a record, to collapse it back, you can click its - button.
    To collapse all records, in the Records section of the Home tab of the Ribbon, click More -> Subdatasheet -> Collapse All
  29. Close the table (if you are asked whether you want to save the table, click No)
  30. To start a new database, press Ctrl + N
  31. Set the File Name of the database to Video Collection1 and click Create
  32. Close the default table without saving it
  33. To create a new table, on the Ribbon, click Create
  34. In the Tables section, click the Table Design
  35. Set the name of the first field to ActorID
  36. Set its Data Type to Number
  37. While the field is still selected, in the Tools section of the Ribbon, click the Primary Key button Primary Key
  38. In the upper section of the table, under ActorID, create a new field as follows:
    Field Name: Actor
    Data Type: Short Text
    Field Size: 50

    Table

  39. To switch the table to the other view, on the Ribbon, click the View button View
  40. When you are asked whether you want to save the table, click Yes
  41. Type Actors and press Enter
  42. Press Enter and enter the following names of actors:
     
    Actor ID Actor
    1 Eddie Murphy
    2 Tom Cruise
    3 Ving Rhames
    4 Mel Gibson
    5 Charlie Sheen
    6 Jaime Pressly
    7 Sheryl Lee Ralph
    8 Daryl Hannah
    9 Joe Don Baker
    10 Victoria Rowell
    11 Grant Shaud
    12 Kevin McCarthy
    13 Charles S. Dutton
    14 Mia Kirshner
    15 Lane Smith
    16 Randy Quaid
    17 Philip Seymour Hoffman
    18 Delroy Lindo
    19 Rene Russo
    20 Gary Sinise
    21 Matthew Broderick
    22 Jean Reno
    23 Michael Douglas
  43. Close the table
  44. Create a new table in Design View with the following fields:
     
    Field Name Data Type Field Size
    DirectorID (Primary Key) Number
    Director Short Text 50
  45. Save the table as Directors and switch to Datasheet View
  46. Enter some directors as follows:
     
    DirectorID Director
    1 Roland Emmerich
    2 Renny Harlin
    3 Tony Scott
    4 Ron Howard
    5 Sanjay Leela Bhansali
    6 Eddie Murphy
    7 J. J. Abrams
    8 Jonathan Lynn
    9 Mel Gibson
    10 Steven Spielberg
    11 Joel Gallen
    12 Oliver Stone
  47. Create a new table in Design View with the following fields:
     
    Field Name Data Type Field Size
    CategoryID (Primary Key) Number  
    Categor Text 50
  48. Save the table as Categories and switch it to Datasheet View
  49. Enter a few categories on the table:
     
    CategorID Category
    1 Drama
    2 Comedy
    3 War
    4 Fitness
    5 Science Fiction
    6 Musical
    7 Adventure
    8 Documentary
    9 Fantasy
    10 Cartoon
    11 Biography
    12 Religious
    13 Hindu
    14 TV Show
  50. Close the table

Creating a Sub-Datasheet

As we have seen so far, to have a relationship between two tables, you must create a primary key in one table and the corresponding foreign key in another table. You can then establish a relationship between both tables in the Relationships window. As stated already, Microsoft Access can take it upon itself to show the related records. You do not have to establish a relationship first in order to take advantage of the sub-datasheet effect. If you have created two tables, one with a primary key and another with a foreign key, you can create the sub-datasheet yourself.

To create a non-existing datasheet, open the table with the primary key in Datasheet View. In the Records section of the Home tab of the Ribbon, click More, position the mouse on Subdatasheet, and click Subdatasheet... This would open the Insert Subdatasheet dialog box. In the list of tables (or queries), you must click the table (or query) that has the foreign key that relates to the primary key of the current table. The names of the primary key and the foreign key would appear in the combo boxes. Once you click OK, Microsoft Access would take care of configuring the subdatasheet.

If a relationship has been established and a subdatasheet exists in a table but you do not want the subdatasheet to show anymore, you can remove it. To delete a subdatasheet, open the table with the primary key in Datasheet View. In the Records section of the Home tab of the Ribbon, click More -> Subdatasheet, and click Remove.

Practical Learning: Creating a Sub-Datasheet

  1. Create a new table in Design View with the following fields:
     
    Field Name Data Type
    VideoID (Primary Key) Number
    Title  
    DirectorID Number
    CopyrightYear Number
    Length  
    CategoryID Number
    Rating  
  2. Save the table as Videos and switch it to Datasheet View 
  3. Enter a few videos:
     
    VideoID Title DirectorID CopyrightYear Length CategoryID Rating
    1 Distinguished Gentleman (The) 8   112 2 R
    2 Wall Street 12 1987 126 1 R
    3 Ransom 4   121 1 R
    4 Not Another Teen Movie 11 2005 100 2  
    5 Harlem Nights 6 1989 116 2 R
    6 M:i:III 7 2006 125 1 PG-13
    7 Devdas 5   175 13  
    8 Passion of the Christ (The) 9 2004   12 R
    9 Platoon 12 1986 120 3 R
    10 Day After Tomorrow (The) 1 2004 123 1 PG-13
    11 Beautiful Mind (A) 4 2001 135 1 PG-13
    12 Godzilla 1 1998 139 1 PG-13
  4. Close the table
  5. From the Navigation Pane, double-click Directors: Table to open it

    Videos

  6. On the Ribbon, click Home
  7. In the Records section, click More -> Subdatasheet -> Subdatasheet...

    Sub-Datasheet

  8. In the Insert Subdatasheet dialog box, make sure the Tables tab is selected. In the list box, click Videos and notice that DirectorID has been selected in the combo boxes:

    Insert Subdatasheet

  9. Click OK

    Relationships

  10. A message box information informs you and asks whether you want to create a relationship between both tables. Read it and click Yes

    Videos

  11. Click a + button and notice the related records. Close the Directors table:

    Videos

  12. Close Microsoft Access
  13. When asked whether you want to save, click Yes
Previous Copyright © 1997-2019, FunctionX, Inc. Next