Exercise #6 - MS Access


Requirements


We have been asked to implement an order tracking system for a mail-order supplier of office goods. We are required to store information on each customer (Name, two-line address, parish and phone number), items that we stock (item code, description and unit price), and on each order (order number, date, customer, items ordered and quantity ordered).

The client has proposed that the following table structure be used:

Field Name
 Type
Order Number
 Integer
Order Date
 Date
Customer Name
 String
Customer Address1
 String
Customer Address2
 String
Parish
 String
Phone Number
 Phone Num
Item Code
 Integer
Item Description
 String
Unit Price
 Number

An example of the resulting table is shown below:

Example Table Structure

Exercise

  1. Is the table normalised? What normal form is it in?
  2. Identify at least one error in the example shown - and state how normalisation have stopped this error from ocurring.
  3. Redesign the table so that it is in 3NF (third normal form), showing the steps along the way.
  4. Implement the resulting tables (HINT!) in Access.