Database Basics - Creating Relationships

 

Complete the On-line Intro to Access - Build Relationships for a new Access 2007 Database tutorial.

Answer the following questions as you read through the lessons. Save the database in your database folder by adding your name to the front of the file name.
Do NOT cut and paste the answers, put them in your own words.

  1. What is the function of a primary key?
  2. What is a foreign key?
  3. What are the three types of relationships between tables and which one is the most commonly used?
  4. What is the rule for creating a one to many relationship?
  5. What is referential integrity and why is it important in relational databases?
  6. What is an orphaned record?
  7. Go to Lynda.com and watch the Access 2007 Essential Training  with David Rivers - watch the Creating Key Fields and Creating Table Relationships and Enforcing referential integrity. Go to school loop and find the Creating Relationships assignment, your starting db file is attached to this assignment - Music Library 5a. Download the file and save it in your H: drive. As you watch the video follow the steps and show your completed table relationships to your instructor for teacher check. You will use the same file for all three video lessons. Save the table in your Access folder in your home drive.
  8. Once you have finished creating the relationships save the DB. Once you finish the next exercise you will show both databases to your instructor for teacher check.

Creating Relationships Practice

Copy the CreatingRelationshipsDB database found on school loop (see step 7 above) to your Access folder on your home drive. Open Access and then open the CreatingRelationshipsDB database.

  1. List the tables found in this database.
  2. Click on the relationship view button. Complete the following chart to indicate which tables are related to which tables. The first one has been done for you as an example.

Parent Table – Primary Key

Child Table – Foreign Key

Departments Table – Department_ID

Employees Table – Department_ID

 

 

 

 

 

 

 

 

 

  1. Use this chart to create the relationships between each related table. Remember to start with the PK and drag it to the FK of the related table. Be sure the Enforce Referential Integrity box is selected.
  2. Organize your tables so the relationships are clear and easy to discern. Show your answers and relationships to your instructor for teacher check_________________