Lesson 4 Relational Databases

1 Relational Databases

Objectives

  • You will be able to demonstrate how to create multiple tables in a databases.
  • You will be able to build knowledge of primary/foreign keys, relational types and referential integrity.
  • You will be able to demonstrate relationships between tables using appropriate relationship methods.
  • You will understand how to read and manipulate a relational map.

Try It

  • Starter - Click on the image below to see what you remember about databases:

Learn It

  • By now, you've set up a table you need for your database and have created fields for the different categories. Next we need to create several more tables to enable us to create customer accounts and customer orders. Then we will link these tables together using the relationships function. Relationships provide Access with the means to bring this information together for you when you need it.

  • This lesson explains how to establish relationships between the tables in an Access database. You will learn how to read and manipulate the relationship map. You will also learn about primary and foreign keys, relationship types, and referential integrity.

Relational-Database-300x300.jpg

2 Activity 1: Creating Tables

Try It

  • Open your *Game Stock Database* Download this file from the last lesson.
  • Today you are going to add two more tables to our database:
  • Your Teacher will demonstrate how to do this or you can watch the video above or view this guide?
    1. Create a Customers Table.
    2. Create an Orders table.
    3. Select appropriate file names for each table.
    4. Select appropriate data types for each field.
    5. Open the Customers table and add some fake names & addresses etc…
    6. Save your two new tables.

AddTable.png

3 Activity 2: Progress Check

Learn It

In the previous lesson you should have created two new tables (Customers & Orders)
Used appropriate field names on each table
Used appropriate data types for each field
Added some fake customer details into the Customers table

4 Activity 3: Establishing relationships between tables

Learn It

Primary and Foreign Keys RelationshipMap.png

  • A primary key is the first field in each table of the database. You may recall that this field auto-numbers by default, so every record in the table has its own unique number to identify it. Access uses this number to quickly pull information together when you run queries or reports, which are covered later.
  • In the example above, the primary key for the Customers table is Customer ID, the primary key for the Orders table is Order ID, and the primary key for the Games Catalogue table is Game ID.
  • A foreign key is a field that is the primary field in its own table but that shows up in another table. If you look closely at the Orders table, the fields Customer ID and Game ID appear there, as well as in their own respective tables. These fields are the primary key in their own tables, but in the Orders table, they are considered foreign keys.

Understanding types of relationships OneToMany.png Access allows for several different types of relationships. These include:

  • One-to-One
  • One-to-Many
  • Many-to-Many

The relationship type you will come across most frequently—and the one created in our bookstore scenario—is the One-to-Many relationship.

  • The One-to-Many relationship means data for that field will show up a single time in one table but many times in the related table.
  • For example, let's look at one of the game titles in our game store. The Game ID for the game should appear only once in the Games catalogue table because this table lists every title stocked in the store. But it will probably appear many times in the Orders table because we hope it gets ordered by many people many times.

Referential Integrity Referential.png

  • Enforcing Referential Integrity ensures that you never have an order for a game that doesn't appear in the Games catalogue table. Selecting this option tells Access to check for these things when someone is working with your data records.
  • In the Edit Relationships dialog box, an option to Enforce Referential Integrity appears.

Try It

  • NOTE: Tables must be closed in order to establish relationships. Click the Relationships command in the Relationships group on the Database Tools tab in the Ribbon.
  • Your Teacher will demonstrate how to do this or you can watch the video above or view this guide?
  • You need to complete the following tasks:
    1. Open the database, and establish a relationship between the Customers table, Orders table and the Games Catalogue table using the drag-and-drop method previously shown.
    2. Explore the options and settings in the Edit Relationships dialog box.
    3. Move the tables around in the relationship map.
    4. Take screenshots to prove you have completed each task.
    5. Save you completed database.

    Relation.png

Badge It - Silver

  • Upload your screenshot evidence of your three completed tables (Games Catalogue, Customers and Orders) to www.bournetolearn.com.

Badge It - Gold

  • Upload your screenshot evidence of your three tables joined together using appropriate relationaship types, to www.bournetolearn.com.

Badge It - Platinum

  • Explain in detail the following terms and how they relate to your Games Catalogue model.
  • Primary Key, Foreign Key, Relationship Type, Referential Integrity.
  • Upload your evidence to www.bournetolearn.com.