Assignment #4: Modify and Query a Relational Database

IMPORTANT REMINDER: This assignment must be completed exactly as specified in the requirements. Deviations will be substantially penalized.

Objectives

In this assignment, you'll work with an existing relational DB using Microsoft Access. The primary objectives of the assignment are:

• Modify a relational DB using MS Access.



• Use Access's Query By Example (QBE) feature to construct queries.
  
  

Requirements

For this assignment you are going to modify the DB that I discussed in class by adding two new tables, modify some existing tables, populate the DB with some test data and construct two queries. The MallWart database is in this zip file, along with some DB lab exercises from the textbook.

You are going to add the following tables: TaxCategory and Shipper

You are going to add the following relationships: A product has a tax category but a tax category can apply to many products. A shipper ships many orders but an order is shipped by one shipper.

The tables should contain the following attributes. Use appropriate attribute formats where not otherwise specified:

TaxCategory Table
CategoryID (numeric, must be unique)
CategoryName (Food | Regular | Tobacco | Alcohol)
TaxRate (in decimal form, e.g., 5% entered as .05, Food = 0.0, Regular = .06,    
 Tobacco = .35, Alcohol = .25)

Shipper Table
ShipperID (numeric, must be unique)
ShipperName (Text)
PhoneNumber(phone number format)
ContactLastName
ContactFirstName

You are going to add these attributes to the following tables:

Order Table
OrderDate

LineItem Table
Quantity  

Customer Table
FirstName
PhoneNumber
StreetAddress
City
State
Zip

You need to modify the Products and Orders tables to account for the new relationships and you need to modify the Relationships view of the MallWart DB in MS Access.

You must add additional records to the tables (new and pre-existing) so that the following queries yield meaningful results.

• Add at least four records to the Shipper table
• Add at least four records to the Orders table (from at least 3 Customers)
• Add at least four records to the LineItems table FOR EACH Order
• Add at least four records to the Customers table

Query 1: All Products (with all product info) that are in either the Alcohol or Tobacco Tax categories (be sure to display the tax category, too). The query must return at least three Alcohol records and three Tobacco records.

Query 2: All Orders that included tobacco products. Include the tax category name and tax rate, the product name, order ID, quantity purchased and date, all customer info, and the Shipper's name. This query must return at least three records.

Submission

The completed assignment is due by 10 pm Friday, April 14th. After that deadline, it is deemed late without penalty and may be submitted until 10 pm Sunday, April 16th. No assignment submitted after that deadline will be graded and a zero will be assigned.

BONUS!! Earn 7.5 bonus points if you (1) submit your assignment prior to 10 p.m. Wednesday, April 12th and your grade is no less than 90.

You must submit your entire website as a zip file ('LastNameWeb.zip') via CourseWeb. Your database must be in a file named MallWartYourLastName.mdb. It must be in the IS10 folder. There must be a link to the database on the assignments.html page in your website.

This assignment is worth 7 points out of 30 total homework points.