Home

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...
     
    Relationships
  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
     
    Relationships
  13. Save and close the Relationships window

The Many-to-Many Relationship

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

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.

Data Joins

 

Introduction

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

Genders

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

Persons

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:

Persons

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.

Inner Joins

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:

Query

You would create the query as an inner join:

Query

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)
     
    Relationships
  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:
     
    Relationships
  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):
     
    Bethesda Car Rental - Cars
  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

Outer Joins

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:

Query

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

And click OK.

A Relationship Report

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 Relationship Report, which would highlight it Relationship Report. 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 Relationships
  3. In the Tools section of the Ribbon, click the Relationship Report button Relationship Report
  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
 
   
 

Fundamentals of Indexes

 

Introduction

An index is a list of words that makes it easy to locate a section in a document. For a table of a database, an index is one column or a list of columns that can make it easily to locate one or more records.

If you create a table that has a primary key, the database engine automatically creates an index. If you want, you can accept that index, delete that default index, or create a new one. Before creating an index, you must have document on which the index will be based. Before creating an index for a database, you must first create a table. With the table ready, before creating the index, you must identify the column(s) you want to use.

Practical LearningPractical Learning: Introducing Indexes

  1. To create a Blank Database, press Ctrol + N
  2. Set the File Name to CPAP1 (which stands for College Park Auto parts 1)
  3. Click Create
  4. Close the default table without saving it
  5. On the Ribbon, click Create
  6. To create a new table in Design View, in the Tables section, click Table Design
  7. Complete the table with the following fields:
     
    Field Name Data Type Field Size Caption Format
    StoreItemCode Text 10 Store Item Code  
    CarYear Number Integer Car Year  
    Make Text 40    
    Model Text 40    
    Category Text 32    
    ItemName Text 50 Item Name  
    UnitPrice Number Double Unit Price Fixed
  8. To save the table, press Ctrl + S
  9. Type StoreItems as the name of the table
  10. Click OK
  11. When a message box asks you whether you want to create a primary key, click No
  12. Switch the table to Datasheet View and create the store items
  13. Switch the table back to Design View

Creating an Index

Once you have the table, you can create an index. To visually create an index, open the table in Design View. Then, in the Show/Hide section of the Ribbon, click Indexes. This would display the Indexes window. To create an index, under the Index Name column, type a name. Then, click the arrow of the corresponding Field Name combo box and select the column used as the index. Once you are ready, close the Indexes window.

Practical LearningPractical Learning: Creating an Index

  1. Make sure the Design tab of the Ribbon is selected.
    In the Show/Hide section, click the Indexes button Indexes
  2. Under Index Name, replace the name with IDX_ItemCode and accept the other options

Deleting an Index

To remove an existing index, display the table in Design View. In the Show/Hide section, click Indexes. In the Indexes window, right-click the index and click Delete Rows.

Characteristics of an Index

 

A Primary Key as an Index

If a table has a primary key, that primary key’s column(s) is used as the index. If the primary key doesn’t exist, to transform your index into a primary key, under the Index Name column, select the index. Then, in the lower part of the window, change the value of Primary from No (the default for a non-primary key) to Yes.

Sorting the Records in an Index

The best way for an index to keep track of its records is to sort them:

  • If the index contains only one column, the database engine would use a logical algorithm to sort the records
  • If the index contains more than one column, the first column would be sorted, followed by the second column, and so on

If the column is text-based, the records would be sorted in alphabetical order. This is referred to as ascending. You can also sort the records in reverse alphabetical order. This is referred to as descending.

If you are visually creating an index, to specify the sorting order, click the combo box in the third column under Sort Order and select either Ascending or Descending.

Practical LearningPractical Learning: Sorting the Records of an Index

  • On the right side of the index name, under Sort Order, notice that it is marked as Ascending

Indexes

Restricting Unique Values

Like a primary key, an index can be used to control how records are created on its table. For example, an index can check that each new record has a unique value in the indexed column. To do this visually, in the top section of the Indexes window, select the index name. In the bottom section, set the Unique column to Yes. When this is set, no new record can use a value that was already set on a previous record.

If the records were already created and when data analysis is made, if two records have the same value, only the first record would be considered.

Practical LearningPractical Learning: Restricting Unique Values on an Index

  • In the lower section of the window, set Unique value to Yes

Allowing or Disallowing Null Values

In the absence of a primary key, an index is a valuable helper to controlling data entry. In the absence of a primary key, you can ask an index to make sure that the value of its column is not left empty when a new record is created. To set this rule, in the top section of the Indexes window, select the index. In the bottom section, set the Ignore Null column to No, which is the default already. When this is set, the user cannot move to the next record if no value was given to the indexed column.

If the records were already created, during data analysis, the records with a null value would be ignored.

Practical LearningPractical Learning: Disallowing Null Values on an Index

  1. In the lower section of the window, make sure the Ignore Nulls column is set to No.
    Close the Indexes window
  2. Switch the table to Datasheet view
  3. When asked whether you want to save, click Yes
  4. Complete the table with new values.
    You will enter your own items codes
    • If you receive an error message box because you try typing an item code that exists already, enter another item code
    • If you receive an error message because you try leaving an item code empty, type a unique number
  5. Close the table

Lesson Summary

 
 

MCAS: Using Microsoft Office Access 2007 Topics

 
 
S2 Define and print table relationships
 

Exercises

 

Yugo National Bank

  1. Open the Yugo National Bank2 database
  2. Open its Relationships window and configure the relationships for referential integrity
  3. Save the Relationships report as Yugo National Bank and print the report
  4. Create a form for the Customers table. Save the form as Customers and design it as you see fit. Here is an example:
     
    Yugo National Bank - Customers
  5. Close the form
  6. Create a form for the Transactions table. Save the form as Transactions and design it as you see fit. Here is an example:
     
    Yugo National Bank - Customers
  7. Close the form

Watts A loan

  1. Open the Watts A Loan database.
    Open the CustomersTransactions form in Design View. Add a Text Box below the subform. Set its Name to txtTotalPayments then set its Format to Currency with 2 Decimal Places. Make it get its value from the txtTransactions text box of the sbfAccountTransactions form
    Add another Text Box below the subform and change its properties as follows:
    Name: txtCurrentBalance
    Control Source: =DLookUp("LoanAmount", "LoanProcessing", "CustomerID = " & CustomerID) - Nz(txtTotalPayments)
    Format: Currency
    Decimal Places: 2

    Save and close the form
  2. Create a new table in Design View with the following fields:
     
    Field Name Data Type Additional Information
    LoanEvaluationID AutoNumber Primary Key
    Caption: Loan Evaluation ID
    LoanAmount Currency Caption: Loan Amount
    Default Value: 0
    InterestRate Number Field Size: Double
    Format: Percent
    Caption: Interest Rate
    Default Value: 0.0875
    NumberOfPeriods Number Field Size: Integer
    Caption: NumberOfPeriods
    Default Value: 12

    Save the table as LoanEvaluation and close it

  3. Create a new form based on the LoanEvaluation table
    Save the form as LoanEvaluation
    Add a Text Box in its Detail section and set its properties as follows:
    Name: txtPeriodicPayment
    Control Source:
    =Abs(Pmt(Nz(CDbl([InterestRate]))/12,
    Nz(CInt([NumberOfPeriods])),Nz(CDbl([LoanAmount])),0,0))

    Format: Currency
    Decimal Places: 2

    Use the Command Button Wizard to add a button that can be used to close the form
    Design the form as follows:
     


    Disable the Maximize button and make the form Pop Up. Don't make it automatically center itself. Before saving the form, position it slightly to the middle-right side of the screen so Microsoft Access would remember that position
    Save and close the form
  4. Open the LoanProcessing form in Design View. Using the Command Button Wizard to add a button that, when clicked, would open the LoanEvaluation form (remember that there is no relationship between both forms; therefore, you will Open The Form And Show All The Records). Set the button's Text to Loan Evaluation and its Name to cmdLoanEval.
    Save and close the form
  5. Open the LoanProcessing form and use its Loan Evaluation button to open the LoanEvaluation form. In the Loan Evaluation form, evaluate a $1500.00 amount of at 12.50% paid in 28 months. After evaluating it, manually create a new personal loan in the Loan Processing form for the amount of $1500 at 12.50% for 28 payments. The loan is processed by the owner, for the 83-457-8 account on April 10th, 2002. Make the 1st payment due on May 20th of the same year and put a reminder that the payments are due every 22th of the month
    Evaluate other amounts and create loans for the other customers.
    Close both forms
  6. Open the CustomersTransactions form to see the results
 
 
   
 

Previous Copyright © 2010-2012 FunctionX Next