INLS261/461 Tools for Information Literacy

If you have finished task 05.1 ...

your tables should be set up and ready to relate.

• All the tables in this database are in either one-to-many or many-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
  • 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 either Primary or 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.1 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]

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 and 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

[top]

I will be looking for the following in your related tables

• Primary and Foreign Keys (to link together the tables)
  • Orders Table PK
  • Book Table PK
  • Publisher Table PK
  • Author Table PK
  • PKs are inserted as appropriate as Foreign Keys in relevant tables
  • Build a junction table to account for the Many to Many relationship
• Lookup Tables (to look up Foreign Key values in different tables)
  • use the Lookup Wizard to create a Lookup Table to enter Foreign Keys into tables
  • you should have at least two foreign keys to enter into another table
• Relationships: Create the relationships between the tables

[top]

This is the second part of your Task 05

• Create the needed relationships between your tables
• Plan to show me your tables and relationships at the start of the next class
• 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]