- Parent Category: Programming Assignments' Solutions
We Helped With This ACCESS Database Homework: Have A Similar One?
|More Info||Microsoft Access Help|
Short Assignment Requirements
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
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.