Oregon State University
Oregon State University Home Page

Up


Database Assignment 2: Relationships

Overview

In this assignment, you continue working on building a relational database for the Wedgewood Pacific Corporation.

  •  Learn how to create relationships between Access tables

  • Learn how to create an Access form based on a multiple tables

  • Learn how to create an Access report based on a multiple tables

Deliverable

In this assignment, you will put your work in the same WPC.mdb file that you created for Assignment 1.

For all Corvallis-based students: I will harvest your WPC.mdb database from your p:\classwork\BA370 folder which is located on the college's file://cob-storage.bus.oregonstate.edu/ file server. If the last sentence doesn't make sense to you, I recommend reading about the college's Servers and Storage ... or asking for assistance from the Help Desk in the Bexell Hall basement computer lab. So if you complete the assignment at home, move a copy of your completed WPC.mdb database to the specified folder by the due date and time.

For all Cascades Campus (Bend) students: I'd like Bend students to use Blackboard to upload their WPC.mdb files. Go to my.oregonstate.edu, go to the BA 370 course, and use the Digital Dropbox feature to upload your WPC.mdb file.

Training Exercises

As a training exercise, I recommend that you start by working through The Access Workbench activities on pages 268 - 281 of the textbook.

Near the top of page 268, you will be asked to open the WMDRM.mdb database that you created in Chapter 1. If you did the training exercises for Chapter 1, you can open the file you created for that Chapter. If you want to begin afresh, you can open this WMCRM.mdb file.

From the middle of page 268 to the end of page 272, you will use this WMCRM-Combined-Data.mdb file.

Starting with page 273, you will once again begin working on the WMCRM.mdb file. You will create a CONTACT table, build a relationship between the CUSTOMER and CONTACT tables, build a form that shows data from both tables, and build a report that includes data from both tables. If you complete this exercises successfully, you should have a database similar to this WMCRM-Done.mdb file.

I think the textbook illustrations of the form on page 280 and the report on page 281 look rather ugly ... and they show how Access 2003 wizards would create these objects. Here is how I laid out these objects with Access 2007 in WMCRM-Done.mdb. It took me a while to adjust the Access 2007 wizard's results to make these objects look reasonable:
 

In class, I will use this WMCRM-Classroom-Example.mdb file as an example database.

Instructions

For this assignment, you are to complete the Access Workbench Exercises on pages 284-288 of the textbook.

Steps B, F, and J: For each of these steps, you will create a quick data entry form. Feel free to use a wizard to create these forms ... you do not need to format them carefully. Thus, they are likely to look like this:
   

Step I: When you have completed Step I, your Relationship view should look like:
   

To create a compound primary key for the Assignment table, drag across the ProjectID and EmployeeNumber fields before you click on the yellow primary key button. That should put a yellow key indicator next to both fields.

Several people have asked me how to create the foriegn keys. You create a foreign key when you build a one-to-many relationship between tables. The one-side of the relationship points to the primary key of the parent table and the many-side of the relationship points to the foreign key in the child table. Foreign keys are not created in an explicit way similar to the way you create a primary key.

Step K: This step asks you to create a WPC Department Employee Data Form. Depending on how your build this form, you are likely to create subform for it. If so, I'd like you to name the subform as WPC Department Employee Data Subform. I want you to format this form nicely, so it should look no worse than this:

If you have problems making your form look as good as the example shown above, you may want to play the appropriate instructional video in the Video Tutorials section below.

Step L: This step asks you to create a Wedgewood Pacific Corporation Department Employee Report. If you accept the default choices from the Access wizard, you will have a truly ugly report. I'd like you to adjust the report's layout and formatting so it looks no worse than this:

If you have problems making your report look as good as the example shown above, you may want to play the appropriate instructional video in the Video Tutorials section below.

Video Tutorials

Hints for playing these videos in the Bexell computer labs: First, if you don't have a headphone set, check one out from a lab assistant. Next, try right-clicking on the video and save the video file on disk somewhere. You should find each video is a .WMV (Window Media Vide) file -- a very common video file format. Then, use My Computer to find the file on disk. Right-click on the file name and ask to Open the file with Window Media Player. That should let you see the video and hear the sound.

Hints for playing these videos in Bend or elsewhere: Be patient ... these files are large and they are not located on a streaming server -- which means you probably will have to download the entire video file before it begins playing.

Building relationships in a database

This video answers a student's question about why he couldn't build relationships between two tables in a database.
(6 minutes)

Building reasonable forms

This video shows techniques for building forms that look good.
(12 minutes)

Building reasonable reports

This video shows techniques for building reports that look good.
(14 minutes)


Test your work

Make sure your work is in the proper location for grading.

Since this assignment asks you to put your work in the same file that you used for the first Access assignment, you should go to the bottom of that page and verify that your Username link opens the WPC.mdb file in your \classwork\ba370 folder. You also should make sure the WPC.mdb file which that link opens is the file which contains the new tables and relationships that you constructed above.

This page was last updated on 3/30/2008


This page is maintained by Dave Sullivan, 541-737-6059. [College of Business Home Page] [OSU Home Page] [OSU Disclaimer]