Normal Forms Lab

In this lab, you will

  1. Download a database with tables in non-normal form: normalizations.mdb. (Right click and select "Save As...")
  2. Save the database to your U: drive.
  3. You will then work on the database from your U: drive, saving your work there.
  4. The database contains non-normalized tables. Your job is to normalize them. Details below.
  5. Finally, you will write queries on the now-normalized tables.
  6. Hand in your work electronically as usual.

On the next exam, no allowances will be given for incorrectly saved files!

Decomposing into Normal Forms

(80 pts total) For each of the 3 tables in the database, "Teaching", "StudentCredit" and "Orders", do the following tasks:

  1. (15 pts total = 5 pts x 3) Identify which normal form(s) is(are) being violated. Record your finding, including a short description of your reasoning, in the comment boxes for the fields for that table. Be sure to identify which field(s) are violating which normal forms. It is helpful to put the comment in the field that is doing the violation.

  2. (60 pts total = 20 pts x 3) Without disturbing the original table,
    1. create new tables and relationships to put the system back in compliance with the guidelines given by normal forms.
    2. Be sure to set the properties of the relationships correctly in your relationship diagram.

  3. (5 pts) Copy the data from the original table to their correct locations in your new table(s).

The lecture notes on Normal Forms can be found here: NormalForms.htm

Queries on Normalized Data

(20 pts total) Write the following queries on your normalized tables.

Be sure to compare your queries with what it would have taken if you had not normalized your tables.

These types of queries may appear again in this course--hint, hint, nudge, nudge!

  1. (5 pts) Find all the instructors who teach upper-division computer science courses. That is, they teach courses with names greaters than "comp300" and start with "comp". Call your query "UpperDivInstructors".

  2. (5 pts) Find all the credit card numbers where that credit card is owned by only one person and the type of the credit card is input by the user when the query is run. Do not show that person's name. Call your query "SingleOwnerCreditCards".

  3. (10 pts) Find all the customers that made more than one order, displaying the customer's name, and what items they ordered. Call your query "MultipleOrders". Hint: use aggregates and multiple copies of a table in your query and link them together in the query design view using the same drag-and-drop technique used to build the relationship diagram. Start by finding all the customers that made multiple orders, then try to add what items they ordered.