Home

Introduction to Relationships

 

Overview of Relationships

 

Introduction

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: 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 as a form or a table:

Customer Table Illustration
Introduction to Relationships

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. This exchange of information is made possible by creating relationships 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, the bank account, the date of the transaction, and the amount of the deposit. Perhaps you would structure 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 (all) other objects involved in transactions. The table that holds 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 needed 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 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 the 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 money deposit 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 table 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. Based on this, you can set this field’s Data Type to AutoNumber. Of course, you can let the user type the value of the primary key. For example, most companies make sure that two employees don't use the same employee number. In other words, such a number must be unique for each employee. In the same way, in the US, every person must have a unique Social Security Number. Therefore, in some cases, you can let the data entry person provide the value of the primary key. In practicality, you must strongly refrain from letting the user tamper with the primary key. The role of the primary key is to have a unique number that identifies a record in a table. This means that two records should never have the same value of the primary key. For this reason, you should also set it as AutoNumber and let Microsoft Access deal with it.

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 Primary Key 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
    If you had already started Microsoft Access, press Ctrl + N. From the New dialog box, click the General property page and click the Database or the Blank Database icon
     

    Then click OK

  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 selected, on the Table Design toolbar, click the Primary Key button Primary Key
  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, and then close the table:
     
    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, check the font is set to Arial, type (c) Year, select, copy and 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
 

Previous Copyright © 2002-2007 FunctionX, Inc. Next