"

Chapter 11 – Relationship Design

INTROCUCTION

Now that our data is stored properly in our tables, lets connect those tables by establishing what we call relationships. This is one of the key components of an RDMS and will be essential to data structure.

  • Establish relationships in the database to connect tables via similar data “links”
  • Create static lookups to control data entry

Guided Exercies

Go to your IU OneDrive and open your username-HomeGoodsStore database.

tblCustomer table

  1. For the PhoneNumber field, create a template for data entry so that the person entering the data just has to enter the numbers and the rest of the characters will automatically appear. Use the # symbol as the placeholder character. Store the data with the symbols.
  2. For the State field, create a drop-down list that gets its values from the tblRegion table. The drop-down list should display the StateAbbr and StateName fields. Sort the list in ascending order by StateName. Do not hide the key column. Keep the StateAbbr field as the value to store and accept the label that Access gives the field.
    • After the lookup wizard creates the list, modify the appropriate property so that column headings are displayed at the top of the drop-down list.

Save and close the tblCustomer table.

tblOrder table

  • For the PurchaseDate field, create a template for the person entering the data so that they enter the PurchaseDate as a Short Date. Use the _ as the placeholder.

Save and close the tblOrder table.

tblFeedback table

  1. The combination of a CustomerID and a ProductID should always be unique. In this table, that will ensure that a customer does not rate the same product more than once. Set these two fields as the composite key.
  2. The Rating field is the number that the customer rates the product on a scale of 1-7. The data type of this field should be kept as Number so that calculations could be performed such as finding the average rating for a product. However, since the value will always be between 1 and 7, change the Field Size to the smallest one for a Number data type.
  3. For the Rating field, create a drop-down list by typing in the values 1 through 7 in a vertical column to create the list. Accept the label that Access gives the field and limit the values to the list.
  4. Change the data type of the FollowUp? field so that it displays as a check box.
  5. For the ProductID field, create a drop-down list that gets its values from the tblProduct table. The drop-down list should display the ProductID, ProductName, and Color fields from the tblProduct table. Sort the list in ascending order by ProductName, then in ascending order by Color. Do not hide the key column. Keep the ProductID field as the field to store and accept the label that Access gives the field.
    • After the lookup wizard creates the list, modify the appropriate property so that column headings are displayed at the top of the drop-down list.
    • Change the width of the second column to 3 inches.
    • Change the width of the list to 5 inches.

Save and close the tblFeedback table.

tblRegion table

  • For the RegionName field, create a one column vertical drop-down list by typing in the four values: Midwest, Northeast, South, West. Accept the label that Access gives the field, but do not limit the values to the list.

Save and close the tblRegion table.

Make sure all objects are closed. Close the database. Double-check the Last Modified date for your database file.

More Practice

Work you should do before our next class

Go to your IU OneDrive and open your username-HomeGoodsStore database.

tblEmployee table

  1. For the HireDate field, create a Short Date template for data entry using the _ as the placeholder character.
  2. For the Building field, create a one column vertical drop-down list by typing in the values: Main, North, South, Taft, Watson, West. Accept the label that Access gives the field and limit the values to the list.

Save and close the tblEmployee table.

tblEmployeeContact table

  1. The ContactID field will not be used for calculations. Choose the appropriate data type and set the field size to 5.
  2. For the EmployeeID field, create a drop-down list that gets its values from the tblEmployee table. The drop-down list should display the EmployeeID, LastName, and FirstName fields. Sort the list in ascending order by LastName, and then ascending order by FirstName. Do not hide the key column. Keep EmployeeID as the value to store and accept the label that Access gives the field.
    • After the lookup wizard creates the list, modify the appropriate property so that column headings are displayed at the top of the drop-down list.
  3. The PhoneNumber field will never be used in calculations. Change the data type to allow the field size to be changed to the maximum number of characters needed for a United States phone number that is written in standard form, such as (812) 855-4848.
  4. For the PhoneNumber field, create a template for data entry so that the person entering the data just has to enter the numbers and the rest of the characters will automatically appear. Use the # as the placeholder character. Store the data with the symbols.
  5. Identify the longest Type of phone number. Change the field size to allow for that type but nothing longer than it.
  6. For the Type field, create a one column vertical drop-down list by typing in the values: Cell, Emergency, Fax, Home, Office. Accept the label that Access gives the field, but do not limit the values to the list.

Save and close the tblEmployeeContact table.

tblOrder table

  • For the ProductID field, create a drop-down list that gets its values from the tblProduct table. The drop-down list should display the ProductID, ProductName, and Color fields from the tblProduct table. Sort the list in ascending order by ProductName, then in ascending order by Color. Do not hide the key column. Keep the ProductID field as the field to store and accept the label that Access gives the field.
    1. After the lookup wizard creates the list, modify the appropriate property so that column headings are displayed at the top of the drop-down list.
    2. Change the width of the second column to 3 inches.
    3. Change the width of the list to 5 inches.

Save and close the tblOrder table.

Make sure all objects are closed. Close the database. Double-check the Last Modified date for your database file.

Post-Chapter 11 ERD

Your Entity Relationship Diagram (ERD, also known as the “Relationships” tool in Access) should look something like this after the Chapter 11 contents. We will do more work on the other desired relationships in Chapter 12.

License

K160: Introduction to Modeling and Solving Business Problems 1e Copyright © 2024 by Trustees of Indiana University. All Rights Reserved.

Share This Book