I need help with this assignment. Anyone available?

Assignment Instructions

Case Study - week 6 - (18%):

You were just hired by a company to help them with their invoice management. The company has been depending on the owner's nephew for their data needs and Joe has done his best to come through for the family business. His lack database experience is hurting the business as they have been unable to successfully keep track of their invoices. Joe put together a spreadsheet (see below) and they have been using it as a database of sorts, to track their customers, invoices, and part sales. Contact the instructor with any questions.

Joe met with company representatives and the following key points were agreed:

1 We can assume each CustomerID is unique - there is one John's customer, one Jim's Hotdogs and so on.
2 One part may be sold to many customers, and one customer can buy many parts.
3 There are no restrictions on how many different parts a customer may order with one invoice.
4 No fields beyond those in the spreadsheet are needed. You can consider the CustomerID and Part values to be unique. (you are NOT being asked to extend or total any pricing)

Your objective is to replace the spreadsheet with a relational database. Your submission will consist of a word compatible document, and an Access DB.

The specific tasks are listed below, the percentage in parenthesis corresponds to the grade weight for each task: Organize your Word compatible document to match the appropriate tasks below. Name your document Last Name_CaseStudy (i.e. Smith_CaseStudy).

SUGGESTION: Review the High Level Dependencies and Normalization, Normalization Terms and Techniques, and Normalization Example Topics in the General Information Forum.

1) Review the existing spreadsheet, and the sample data and document any assumptions you need to make about the content. Explain the approach that you are going to follow. (10%)

2) Submit "one" complete functional dependency analysis (all fields must be included at least once), use the functional notation shown on pages 410-413 Examples 14.2, 14.3, 14.4 and 14.5 to document the dependencies.

The notation is: Field A à Field B, Field C, means that Field A is a determinant for Field B and Field C.
Similarly if a field is dependent on the combination of values of two fields then:
Field X, Field Y à Field Z

There may be one or more rows of functional dependencies. Consider all potential values for each field when determining dependencies (do not rely solely on the values shown in the below spreadsheet.) Explain one of the functional dependencies in plain English (include field names) (25%)

3) Submit a complete set of 2NF, and 3NF tables (ensure you label and identify all 2NF and then all 3NF tables). (25%)

Use normal form "definitions" to ensure that your set of tables are in 2NF and then in 3NF. You may also include the 1NF table(s) if that helps your thought process. DO NOT SUBMIT ANY SPREADSHEET BASED TABLES

Include a plain English description to explain why the 2NF tables are in 2NF and then explain why the set of 3NF tables are in 3NF (you should mix plain English and normal form definitions in your explanation - do not simply copy the definitions). One approach may be to use fields names as examples in your description - i.e. Field A is a determinant for Field B because...
  
You MUST use the shorthand representation shown on page 111 (also shown on page149), which describes the tables found on page 112 of the Connolly text (ensure you underline the primary key fields). The notation is:
TableName (Field1Name, Field2Name, Field3Name, etc.) underline the primary key field(s). The Pratt text also has many examples of this notation.

4) Create an MS Access database, including the data provided, using the 3NF tables. Name your database Last Name_CaseStudy (i.e. Smith_CaseStudy). Upload the database to the assignment area as one of the deliverables (5%).
5) Create a Query (In MS Access) that may include one or more tables, as needed, that provides the answer to the following request: List the Invoice number, Customer Name, and Parts for purchases by Customers that have an Account Balance of $3,000 or higher, and sort them with the highest balance being displayed first (Descending values) (5%)
6) Create one Form: New Customer Input - this form should include any relevant fields that you think might be needed (In MS Access) (5%)
7) Create One Report: Items Price List - this report should include any relevant fields that you think might be needed (In MS Access) (5%)

Below you will find the spreadsheet that Joe has put together for your use.
You can assume that the quantity reflects the number of items, and the price is per item - you are NOT expected to calculate any extended pricing or totals. 

Invoice#	CustomerID	Acct Balance	Acct Limit	PartID	Quantity	Price
87	Martin's IT Services	        3,000	     20,000	Bolt	150	0.10
				Nut	40	0.20
72	Jim's Hotdogs	        7,200	       7,200	Bolt	100	0.10
				Screw	45	0.30
57	Brookings	           500	     12,000	Screw	60	0.30
				Bolt	65	0.10
67	John's	        1,000	       3,000	Nut	70	0.20
3	Action Computer	        1,000	     10,000	Screw	140	0.30
7	Kline's	        8,000	       9,999	Bolt	30	0.10
9	John's	        1,000	       3,000	Nail	25	0.50
17	Parker's	           700	       1,000	Bolt	75	0.10
				Nail	120	0.50
19	Al's	        5,000	       7,000	Screw	110	0.30
20	Al's	        5,000	       7,000	Nut	190	0.20

Here is the notation for the above spreadsheet - which we consider an unnormalized table:

Invoice (Invoice#, CustomerID, AcctBalance, AcctLimit, (PartID, Quantity, Price))

Below you can find an alternative format for the same spreadsheet that Joe has put together - the below also includes a repeating group - by virtual of "repeating the same field name set" and is therefore an unnormalized table (UNF) The notation for this would be the same as for the pervious spreadsheet.

Invoice#	CustomerID	Acct Balance	Acct Limit	Part1	Quantity1	Price1	Part2	Quantity2	Price2
87	Mary's IT Services	       3,000	        20,000	Bolt	50	0.10	Nut	40	0.20
72	Jim's Hotdogs	       7,200	         7,200	Bolt	100	0.10	Screw	45	0.30
57	Brookings	          500	        12,000	Screw	60	0.30	Bolt	65	0.10
67	John's	       1,000	         3,000	Nut	70	0.20	Â	Â	Â
3	Action Computer	       1,000	        10,000	Screw	40	0.30	Â	Â	Â
7	Kline's	       8,000	         9,999	Bolt	30	0.10	Â	Â	Â
9	John's	       1,000	         3,000	Nail	25	0.50	Â	Â	Â
17	Parker's	          700	         1,000	Bolt	75	0.10	Nail	120	0.50
19	Al's	       5,000	         7,000	Screw	110	0.30	Â	Â	Â
20	Al's	       5,000	         7,000	Nut	90	0.20	Â	Â