We Helped With This ACCESS Database Homework: Have A Similar One?

Assignment 3 (25 points)

Due Date: October 4, 2016 (at the beginning of the class)

You are to implement a relational database using a standard Excel file (dataset3.xlsx) available on BeachBoard.  The database is intended to support online order taking and fulfillment operations that:  

1.      Enable customers to look up product related information, create customer profiles, place orders, and make payments;

2.      Allow order & shipping confirmation emails, shipping labels, and daily/periodic reports to be generated; and

3.      Support internal operations, such as recording and tracking of products, customers, as well as payment and shipping information.

Part A. Normalization to Minimize Data Redundancy

Examine the Order worksheet and identify redundant data.  Normalize the Order worksheet by breaking it up into two smaller ones.  Specifically, you shall create a new worksheet to keep track of all orders received; on that new worksheet each order will get one and only one record. The other worksheet, to be named Order Details, shall contain attributes to keep track of the products and quantity required for each order.  

The resulting Excel file shall contain six worksheets, Product, Customer, Payment Method, Shipping Address, Order and Order Details.

Part B. Creating a Relational Database Using Microsoft Access

Use Microsoft Access to import all worksheets you come up with in Part A.  For each of the tables, be certain that you have designated a primary key (or have Access choose one for you, if applicable).  Primary key shall be indexed and unique.  To ensure data integrity, specify an appropriate data type for each of the attributes. 

Edit a relationship diagram to enforce relational integrity by linking tables together properly. 

Be sure relational integrity constraints are enforced, and where appropriate, choose the option Cascade Update Related Fields.  We typically relate two tables by linking the foreign key from one table to the primary key of the other.  However, if the two attributes (fields) are not compatible (i.e., having different data types), or if there are any existing data inconsistency, Access will not allow you to establish the relationship.  Provide a screen print of the relationship diagram showing all tables, primary keys, and non-key attributes.  

Design and print out a Relationship Report.  Note that Relationship Report is a formal report documenting the database you are implementing.  It shall have your relationship diagram on one single page, plus a report title and date.  Name it “Relationship Report for Assignment 3” and save it accordingly.  Make sure there is no truncation of tables or attribute names.  You may find landscape orientation desirable when printing out the relationship report. 

C. Inserting Records

Insert a new product record:

Product No: 600312

Product Name: PNY Elite 64GB USB 3.0 Flash Drive

Product Category: Storage

Quantity on Hand: 20000

Safety Stock: 2000

Unit Cost: $12.00

Unit Price: $19.95

Insert a new shipping address for Brad Pitt:

Shipping ID: S-619

Customer No: 1005

Recipient L_Name: Jolie

Recipient F_Name: Angelina

Shipping Street Address: 205 Broadway

Shipping City: New York

Shipping State: NY

Shipping Zip Code:10012

Insert a new customer record:

Customer No: 1018

Customer L_Name: Wang

Customer F_Name: Stan

Email: ...

Password: GoodGuy

A new customer, James Bond, just placed an order for two units of 200415 and two units of 400258. Using below data related to customer, order, payment and shipping, insert the needed records into the database to capture the transaction:

•      Order No: 300-20; Order Date: 9/20/2016

•      Customer No: 1020; Email: ...; Password: Bond007

•      Payment No: P-422; Name on Card: James Bond; Card Type: Discover; Card No:

6011002020524567; Expiration Date: 12-19; Security Code: 888

•      Shipping ID: S-620; Recipient L_Name: Wang, Recipient F_Name: Stan; Shipping Street Address: 1250 Bellflower Blvd. CBA 432; Shipping City: Long Beach; Shipping State: CA; Shipping ZIP Code: 90840

D. Deleting Records

Lionel Messi (Customer No 1013) decided to cancel the order he placed (Order No 300-17) that has not been processed. Delete the needed records so the order will not be fulfilled.  Because the payment card is not charged until the order is shipped, there is no need to process credit back to the payment card.   

Submit a hardcopy report that includes a cover page, plus the following:

1.      a screen print of the relationship diagram you come up with;

2.      Relationship Report for Assignment 3; and

3.      all database tables in datasheet view (not screen print) in the order of Product, Customer, Payment Method, Shipping Address, Order and Order Details.  Be sure there is no truncation of attribute names or data.