SILS iSchool

07 Nov 2018

Value Added | daily

Class Schedule

Basics | sessions 01-05

22 AUG | intro
27 AUG | clients
29 AUG | servers
05 Sep | networks
10 Sep | basics lab

Web Development | sessions 06-11

12 Sep | structural layer
17 Sep | presentational layer
19 Sep | working with layers
24 Sep | behavior layer
26 Sep | images & design
01 Oct | website lab

Document Markup | sessions 12-14

03 Oct | object layers
08 Oct | graphics
10 Oct | document markup lab

Spreadsheets | sessions 15-19

15 Oct | spreadsheets
17 Oct | formulas & functions
22 Oct | thoughts about data display
 18 Oct  | Fall Break 
24 Oct | database tools
29 Oct | spreadsheets lab

Relational Database | sessions 20-26

31 Oct | relational databases
05 Nov | tables

 07 Nov  | relationships |  05.02  | next session

12 Nov | input & output
14 Nov | SQL
19 Nov | complex queries
26 Nov | databases lab
 21 Nov | Thanksgiving 

Presentation | sessions 27-30

28 Nov | presentation design
03 Dec | presentation delivery
05 Dec | presentation lab
12 Dec | 0800-1100 | final in class presentation

Relate your tables by ensuring referential integrity between Primary Keys and Foreign Keys.
Use the lookup wizard on the Foreign Keys to allow the program to create the relationships for you.
These do not have to be turned in, but they will be needed during the subsequent sessions.

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

back to top

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

back to 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
    • Connection 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

back to 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

back to top