Home

Introduction to Relationships

 

Overview of Relationships

 

Introduction

Introduction to Relationships

Data entry as we have performed it so far consisted of typing the necessary value in each field. Because a regular database is made of various tables, these objects work as an ensemble to create a product as complete and efficient as possible. As there can be so many objects, one of the rules a database developer should follow is to avoid duplicate entry of data. That is, the same piece of information should not be entered in two different objects. For example, when a person is opening a bank account at a bank, a clerk enters the customer’s information in an object such a form or table:

Customer Table Illustration

When the same customer comes back for a transaction such as to deposit money, her information should not be entered again but that information should be selected from the form used to perform transactions:

Field Relationship Illustration

This is the basis of relational database. Relational database is based on the idea that objects of a database are connected or related so they can exchange information. When this is done, relations are created among objects of a database.

The Primary Key

The Primary Key

If you were asked to create a bank database for the above illustration, the form used when a customer wants to make a deposit probably would need to contain such information as the customer’s name, her bank account, the date of the transaction, and the amount of the deposit. Per haps you would create the form as follows:

Customer Money Deposit

Customer Name
Bank Account
Transaction Date
Amount of Deposit

Based on this, the clerk can simply type the name of the customer and perform the transaction. When the same customer comes back for another type of transaction such as money withdrawal or transfer, the clerk would use another form and enter the customer’s name. The likelihood of typing the name of the customer differently on various forms is really high. For example, when performing a deposit, the clerk may enter the name of the customer as Josiane Tobolowski. On a form used to withdraw money, the clerk may enter the customer’s name as Jozian Toblowsky. Obviously, these are two completely different names. To avoid this type of mistake, the solution is to create an object (table) that would hold the customer’s name and make that name available to other objects involved in transactions. The table that objects the customer’s information may have the following fields:

Customer Information
Full Name
Account Number
Address
Phone Number
Email Address

When a customer needs a bank transaction, the clerk can simply select the customer’s name on a form and continue the transaction. To make this possible, there must be a relationship between the table that holds the customer’s information and the table on which the transaction is performed:

Customer Information Customer Money Deposit
Full Name
Account Number
Address
Phone Number
Email Address
Double Fleche
Customer Name
Bank Account
Transaction Date
Amount of Deposit

When creating this type of relationship, you would be thinking that, although the customer’s information is need by various objects of the database, different objects may need different types of information. For example, when performing money deposit, the clerk would need the customer’s account number and her name. If the customer signed to receive newsletters from the bank, when another clerk is preparing these newsletters to customers, he would need the customer’s name and email address but not her account number. For this reason, relational database provides you with the ability to create a special field in the table that holds the customer’s information. This field would be used to communicate with other objects of the database and provide them with the information they need about the customer. The name of this field usually ends with Number, Nbr, or No. Because this field is referred to an identifier, it sometimes ends with ID. The table that holds a customer’s information may become:

Customer Information
Full Name
Account Number
Address
Phone Number
Email Address
Customer ID

On the objects that need information stored in the customer’s information table, you must create a field that would hold the relationship. For example, you can create such a field on the table used for money deposit. This field does not directly belong to the table: it acts as an “ambassador”. For this reason, it is called a foreign key. Because such a field belongs to the originating table, it should have the same name as the identifier of the original table. On the other hand, the field identifier will be used to manage the relationship between its table and those tables that need information stored in its table. It is called the primary key. After creating these two fields, they are used to “link” both tables:

Customer Information Customer Money Deposit
Full Name
Account Number
Address
Phone Number
Email Address
Customer ID
Fleche
Customer Name
Bank Account
Transaction Date
Amount of Deposit
Customer ID

Although in this example of the customer’s information table we illustrated only one field as the primary key, you can use more than one field as the primary key.

In this scenario, what you need the primary key field to do is to keep track of the exchange of information between its tables and the tables related to it. In most circumstances, you will not need to enter information in the primary key field. For this reason, you can ask Microsoft Access to provide an automatic number to the field. For this reason, you can set this field’s Data Type to AutoNumber.

There are three main ways you get a primary key on a table. If you create a table using the wizard, you will be asked whether you want Microsoft Access to assign a primary key for you. If you are creating a table in Design View, you can click the Primary Key button on the toolbar while the designated field is selected, you can also click Edit -> Primary Key from the main menu. If you try to save a table created in Datasheet View, since you will not have set a primary key, you will be prompted accordingly.

Practical Learning: Introducing Relationships 

  1. Start Microsoft Access and create a Blank Database
  2. Set the name of the database as Video Collection and click Create
  3. To create your new table, from the Tables section, click the New button
  4. From the New Table dialog box, click Design View, and click OK
  5. Set the name of the first field to ActorID and set its Data Type to AutoNumber
  6. While the field is still selection, on the Table Design toolbar, click the Primary Key button
  7. Set its Caption to Actor ID 
  8. In the upper section of the table, under ActorID, set the name of the second field to Actor with the Data Type as Text
  9. Set the Caption of the Actor field to Actor/Actress 
  10. Set the last field as Notes with a Memo as Data Type
     
  11. To switch the table to the other view, on the Table Design toolbar, click the View button.
  12. When you are asked whether you want to save the table, click Yes. 
  13. Type Actors and press Enter. 
  14. Press Enter and enter the following names of actors:
     
    ActorID Actor
      Eddie Murphy
      Michael Douglas
      Glenn Close
      Bruce Willis
      Isaac de Bankole
      Jack Nicholson
      Laura Linney
      Demi Moore
      Tom Cruise
      Djimon Hounsou
      Anne Archer
      Bonnie Bedelia
      Tom Selleck
      Paulina Porizkova
      Lane Smith
  15. Close the table
  16. Create a new table in Design View with the following fields:
     
    Field Name Data Type Description
    DirectorID
    (Primary Key)
    AutoNumber Automatic number
    Director Text Name of a director such as “Mark Lynn” or directors as a group of such as “The Hughes Brothers”
    Notes Memo Observations about the director or group of directors
  17. Save the table as Directors and switch to Datasheet View
  18. Enter some directors as follows:
     
    DirectorID Director
      Renny Harlin
      Tony Scott
      Tim Burton
      Eddie Murphy
      Rob Reiner
      Jonathan Lynn
      Spike Lee
      Steven Spielberg
  19. Create a new table in Design View with the following fields:
     
    Field Name Data Type Caption
    VideoCategoryID
    (Primary Key)
    AutoNumber Video Category ID
    VideoCategory  Text Video Category
    Notes Memo  
  20. Save the table as VideoCategories and switch it to Datasheet View
  21. Enter a few categories on the table:
     
    Video Category ID Video Category
    Biography
    Documentary
    Fitness – Aerobic
    Comedy – General
    Comedy – Police
    Comedy – Politic
    Comedy – War
    Drama – General
    Drama – Police
    Drama – Politic
    Drama – War
    Sci-Fi – General
    Sci-Fi – Comedy
    Sci-Fi – Police
    Sci-Fi – Politic
    Sci-Fi – War
    Cartoon
  22. Close the table
  23. Create a new table in Design View with the following fields:
     
    Field Name Data Type Caption Field Size
    VideoID
    (Primary Key)
    AutoNumber Video ID
    Title Text 120
    CopyrightYear Number © Year Integer
    Length Text 20
    Notes Memo

    (To get the © character, you can open Microsoft Word, type (c) then select and copy it. Then paste it in the Caption property of the table field)

  24. Save the table as Videos and switch it to Datasheet View 
  25. Enter a few videos:
     
    Title © Year Length
    A Few Good Men 1993 138 Mins
    The Last Boy Scout 1991 105 Mins
    The Distinguished Gentleman   112 Mins
    Batman 1989 126 Mins
    Die Hard 2 – Die Harder 1990 124 Mins
    Beverly Hills Cop II 1987 103 Mins
    Her Alibi 1998 94 Mins
  26. Save the table

Lookup Fields

 

Unbound Lookup Fields

The fields we have been creating so far were classic text box fields that allow the user to simply type to create a value. Instead of letting the user type the values of a field, if you want, you can establish a list of values that are valid for a certain field. This would allow the user to simply select from that list. Such a field is called lookup field.

The simplest type of lookup field consists of creating a list whose items you know at the time you are creating the database. For example, if a clerk must specify the gender of persons whose records she is creating, since there are only two genders, you can create a list that contains only male and female items. Since there would not be suspicion of a new value, you can safely create such a list. The lookup list is provided through a combo box. To set or change a value in the field, the user can expand the combo box and select an item. The item selected in stored in the field and is stored as a string (as text).

There are three main ways you can create a simple lookup field:

  • In Datasheet View, create or select a field whose value should be selected from a list. Then, on the main menu, click Insert -> Lookup Column…
  • In Datasheet View, create or select a field whose value should be selected from a list. Then right-click the column header and click Lookup Column
  • In Design View, locate the field whose values should be selected from the list and set its Data Type to Lookup Wizard

Any of these actions would launch the Lookup Wizard. Since in this case you would be creating a known list, you should select the second radio button and proceed.

Practical Learning: Setting Preset List Of Items

  1. The Videos table of the Video Collection database should still be opened
    Right-click the Length column header and click Lookup Column...
  2. On the First Page of the Lookup Wizard, click the second radio button
     
  3. Click Next
  4. Click the first empty field under Col1 and type G 
  5. Press the down-arrow key and type PG 
  6. Press the down-arrow key and type PG-13 
  7. Type the other ratings you are aware of, if any:
     
  8. Click Next
  9. For the Column Label, type Rating and press Enter 
  10. Set the ratings of the movies by selecting from the Rating combo box:
     
  11. Close the Videos table
  12. Open the Music Category1 database
  13. Open the Artists table in Design View
  14. Click the Structure field
  15. For its Data Type, choose Lookup Wizard...
  16. When the first page of the Lookup Wizard comes up, choose the second radio button telling the wizard that you will type values for the field, then click Next
  17. Click the empty field under Col1 and type Solo
  18. Press Tab and type Duo
  19. Press Tab and type Band
  20. Press the down arrow key and type Soundtrack
  21. Press Tab and type Orchestra
  22. Press the down arrow key and type Compilation
  23. Press the down arrow key and type Other
     
  24. Click Next. Accept the column label as Structure and click Finish
  25. Click the Lookup tab in the lower part of the Design View
    To make sure that the user can only choose a preset value in the list, set the Limit To List field to Yes
    On the other hand, if you wanted to allow the user to type new entries, you would set the Limit To List field to No (keep in mind that even if you restrict the user to choose only from the list, you can eventually modify that list)
  26. Save, and then close the table

Bound Lookup Fields

As done with the simple lookup, you can create a field whose data would be selected from a list. As opposed to an unbound lookup fields whose values you can predict at the time you are creating a database, a bound lookup field is one whose values are not known in advance. The values for such a field become available as the database is growing.

We mentioned earlier that tables could be linked to exchange information. Based on our illustration, a foreign key field from a table can be used to provide information to its table by retrieving it from the originating table. When creating such a relationship, you should know what table would be providing such information and how the relationship would be managed.

To create a bound lookup field, you select a field and proceed as we saw for an unbound lookup field to open the Lookup Wizard. This time, you would select the first radio button and click Next. This would give you the opportunity to select the table that will supply the necessary information. In this case also, you can specify more than one column to be presented in the list.

Practical Learning: Getting Fields Data From External Source

  1. Open the Video Collection database and open the Videos table in Datasheet View
  2. Click any field under the Notes column.
  3. On the main menu, click Insert -> Lookup Column...
  4. In the first page of the Lookup Wizard dialog, make sure the first radio button is selected and click Next
  5. On the second page, click VideoCategories:
     
  6. Click Next
  7. From the Available Fields list box, double-click VideoCategory
  8. Click Next and Next
  9. For the label, type Category and click Finish
  10. Set the appropriate category for each movie
     
  11. Close the table

Lesson Summary

 

MOUS Topics

S3 Establish table relationships
S10 Set primary keys
S13 Modify tables using Design View
S14 Use the Lookup Wizard
S33 Establish relationships
 

Exercises

 

Watts A Loan

  1. Open the Watts A Loan database. Create a new table in Design View with the following fields:
     
    Field Name Data Type Additional Information
    TypeOfLoanID AutoNumber Primary Key
    Caption: Type of Loan ID
    TypeOfLoan   Caption: Type of Loan
    Description Memo  

    Save it as TypesOfLoan and close it

  2. Using AutoForm, generate a form based on the TypesOfLoan table. Save it as TypesOfLoan and design it as follows before saving and closing it
     
  3. Open the TypesOfLoan form and create the following types of loan:
     
    Type of Loan ID Type of Loan Description
    1 Personal This is loan given as a cashier check to a customer who wants a cash loan
    2 Car This loan will be processed by our partners as car dealers
    3 Boat  
    4 Furniture  
    5 Musical Instrument We have some partnerships in musical instruments stores. This is the type of loan we will make available to the customers they find for us
  4. Open the Transactions table. In the Datasheet View, create a new column using the Lookup (Column) Wizard. Include the AccountNumber and the LastName fields of the Customers table. Set its label to Account # then save and close the table
  5. Open the Transactions table in Design View. Insert a new field just under TransactionID. Name it Processed By and start the Lookup Wizard. Include the LastName, the FirstName, and the Title fields from the Employees table. Save and close the table
  6. Using AutoForm, generate a form based on the Transactions table. Save it as Transactions and design it as follows:
     

    Close the form

Tenley Associates

  1. Open the Tenley Associates database and open the Employees table in Design View. Using the Lookup Wizard, configure the DepartmentName field so its value would origin from the DepartmentName Column of the Departments table. Set its label to Department. Save and close the table
  2. Open the Employees form in Design View and delete the DeparmentName text box. Using the Field List, add the DepartmentID field to where the other was. Adjust the design of the form as follows:
     


    Save and close it

Yugo National Bank

  1. Open the Yugo National Bank and, in Design View, create a new table with the following fields:
     
    Field Name Data Type Additional Information
    ChargeReasonID AutoNumber Primary Key
    Caption: Charge Reason ID
    ChargeReason   Caption: Charge Reason
    Description Memo  

    Save it as ChargeReasons and close it

  2. Using AutoForm, generate a form based on the ChargeReasons table and save it as ChargeReasons before closing it
     
  3. Open the ChargeReasons form and create the following records
     
    Charge Reason ID Charge Reason Description
    1 Monthly Charge Applied every month to all accounts
    2 Overdraft Applied if a customer's account remains negative for 72 hours

    Close the form

 

 


Previous Copyright © 2002-2007 Yevol Next