Relationships, Table Look-ups and E.R.D's
|
For this lesson you should be able to:
|
|
Primary Keys
|
Lets imagine for a second that you are building a database for prisoners.
We want height, date of birth, gender, hair colour, tattoos, and to store whether they are violent. Before we create this lets Draw a diagram in Draw.IO The following is a tutorial on how to draw a table in draw.io |
|
Draw.io
|
1.) Browse to draw.io and click "Create New Diagram"
2.) Click on "Flowcharts" and then "Create" 
3.) On the left click on "Entity Relationship"
4.) Click and drag the table that is pointed out on the picture to the middle of your page 
|
|
Prisoner Task
|
Using Draw.io and the information from the prisoner above, create a table.
Try to make yours more correct then others in the class Ignore the Primary Key for now. See if you can figure out how to change it and add more rows. |
|
Primary Keys
|
Have you ever been given a number for something?
A student number, IRD Number, customer number or ID number? Take a look at this bank note 
|
Why use a key?
Think back to a the prison situation... What would happen if we had two prisoners with the same name, same date of birth? Databases need unique entries so how would you tell the difference between the two... Task: 1.) Update your table to have a prisoner ID 2.) Create this database in Access and enter 4 records. 3.) Use an AutoNumber as a prisonerID |
Relationships and Lookups
|
In order to help maintain a safe prison, prisoners need to be separated by violence level. To do this the prison has create 6 different classes:
Tax Evasion Business Fraud Theft Drug Distribution Assault/manslaughter Murder They may come up with more categories in the future. Task: 1.) in Draw.IO Create another table to illustrate the prisoner categories. Use a categoryID as a primary Key. |
2.) Create a database table for the categories in the same one you used to create the prisoners. Ensure that you have an Autonumber as a categoryID
|
Assigning a category to a prisoner
|
Every prisoner can have one Category. Every category could be attached to many prisoners.
This is what we call a one to many relationship. This will be important later on. For now we just want to assign prisoners their crime category. We want to create a "foreign key" in our original prisoners database table Task: 1.) Update the diagram as per the following on the right... |
Don't worry if you don't understand this yet you will over time!
|
5.) Close all of your tables in the background
6.) Drag the categoryID from one table on top of a categoryID from another table. 
|
1.) Now create this table in Access in the same database file
2.) Add a CategoryID to the prisoners table. Setting up relationships 3.) Click the relationships button after ensuring that you still have a table open 
4.) Add the "Categories" and "Prisoners" table
7.) Click "Enforce Referential Integrity", this ensures that the relationships are connected.
8.) Open up the prisoners table and try and enter the number 44. See what happens 
|
Enforced Numbers
|
In the CategoryID field you will only be able to enter values that exist in the Category table.
e.g. you can enter CategoryID = 6 because it relates to the Murder field. However you cannot enter the number 7 because there is no category for it. So you are now forced to enter only numbers in the other table. .... This is going to be quite difficult, So instead we are going to include an excellence level technique and apply a look up. |
|
Performing a lookup
|
Follow these instructions to create a lookup:
1.) Open the students table in design view. 2.) Click the categoryID 3.) Click on the lookup tab at the bottom 4.) Change "display control" to "List Box" 
6.) Enter data into this table and see what happens when you get to the CategoryID
|
5.) Change the rest of the form to look like this
|
Using a list box
|
Previously when we did a look up it looked like the picture on the right.
If you notice it saves it as a number which is very hard to work with as humans. We can change this by using a query. 1.) Open up the prisoners database file that you created 2.) Go to the lookup field for the category ID It should look like the picture on the right. 3.) Click on the "Row Source" and the three dots next to this field. 4.) Add the category Table 5.) Add both fields. Save and close 6.) Change the fields to look like the following picture |
|
Friends Categories
|
Last time we created a friend database for friends
This time we want to create another table and field for friend type:"Besties, Acquaintance, friend of a friend, enemy" Create another field called FriendTypeID and create another table to store the different types. Create a lookup to make entry easier |
|
