If you have finished task 05.01, your tables should be set up and ready to relate

All the tables in this database are in one-to-many relationships

  • for the one-to-many relationships, you must decide which way the relationship will go and then make a copy of the Primary Key in the one table and place a copy of it as a Foreign Key in the many table
  • the four tables you imported will already have a field in them that will be the FK in a relationship

What about the relationship between the Orders table and some other table in the database?

You will need to decide whether or not you need to create a one-to-many or a many-to-many relationship between the Orders table and another table

  • think of the relationship between Orders and another table in Entity Relationship Diagram terms
  • Orders is a table/noun and it relates to another table/noun through a relationship/verb. What is this relationship/verb?

Let's establish some rules for this database that may assist in deciding the relationships

Mr. Pitt, our client, wants to be involved in every purchase order for his library, but he doesn't like to think about complex things
  • thus, he wants each order to be for a single book title only
  • it can be for more than one copy of a book, but cannot be for more than one title
  • since he likes to give books away, he might well be ordering the same title many times, but each time he orders a book, it will be an order for a single title

However, you only have to do this in regards to the Order table. You must decide what field in the Order table is the FK and what PK in another table it should be related to

  • check the Field Properties for the Foreign Key and ensure that Duplicates are allowed
  • you will have noted that all the tables that you imported already have fields in them that can be Foreign Keys, depending on the relationship

Your task is to decide on the relationship and link the related fields

You have a many-to-many relationship between Book and Author,

but in Access you must create the many-to-many relationship by creating an intervening table and relating the Book and Author tables to it as two separate one-to-many relationships

  • the data you imported in task 05.01 should have allowed you to set up an intervening, or junction, table to sit between the book table and the author table. You don't need to make another many-to-many relationship, but this intervening table shows how it may be done, should the need arise in a future database project.

The intervening table has its own Primary Key for each new record and also contains copies of Primary Keys of the two related tables, inserted in the intervening table as Foreign Keys

  • if the Foreign Key data type was an AutoNumber when it was a Primary Key, change it to a Number when it is a Foreign Key in the intervening table (because it needs to be a copy of and AutoNumber, not a newly generated AutoNumber) and ensure that Duplicates are allowed
  • then use the drag tool to link the three tables together through their respective keys, enforce referential integrity and enable cascades

this intervening table will allow the two original tables to relate to each other in a Many-to-many relationship

[top]

I will be looking for the following in your related tables

  1. Primary Keys (for each table)
    • Orders Table PK
    • Book Table PK
    • Publisher Table PK
    • Author Table PK
    • BookAuthorConnector Table PK
  2. Lookup Tables (to look up Foreign Key values in different tables)
    • use the Lookup Wizard on the Foreign Key field in the appropriate tables in order to create a Lookup Table relationship that you may then use to enter Foreign Key values into tables as you create new records in those tables
    • once you have identified the fields that will be your Foreign Keys, use the lookup wizard to create the relationship to their associated Primary Key in another table
  3. Relationships: enforce referential integrity on all relationships

[top]

This is the second part of your Task 05

  • Create the needed relationships between your tables
  • Plan to have your tables and relationships ready at the start of the next class session
  • The relationships must be healthy to enable the remaining components of the task to work correctly
  • Here's a short summary about creating relationships

[top]

creating relationships | 05.02 | next session