Find Us On: Facebook Twitter Rss Feed Blog
Need To Register? Sign Up Login

Case Study- Database

Musthave
Musthave 


Your specific tasks to help the business are in parenthesis are the percentage that correspond to the per item payment (grade weight): 1) Review the existing spreadsheet, and their sample data and make any assumptions you need about the company. Make any comments about the approach that you are going to follow, as you see fit. (10%) 2) Redesign the spreadsheet into a database with tables, add any fields that you think could be useful, even though it is not absolutely necessary. Explain your actions as you go along. 3) Perform a functional dependency analysis, and include it as part of your deliverables, for every step of the normalization process (25%) 4) Clearly take the existing un - normalized structure through distinct 1NF, 2NF and finally 3NF stages, using the shorthand representation. All three stages of normalization have to be clearly defined and depicted for full payment by the proprietor. Do not forget Primary keys assignment.(25%) 5) Finally, create an MS Access prototype (actual database), including the data provided, with the newly designed tables. Upload the prototype to the assignment area as part of the deliverables (5%). 6) 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 Description for purchases by Customers that have an Account Balance of $3,000 or higher. Short them with the highest balance being displayed first (Descending values) (5%) 7) Create one Form: New Customer Input - this form should include any relevant fields that you think "Home Solutions Inc" might need (In MS Access) (5%) 8) Create One Report: Items Price List - this report should include any relevant fields that you think "Home Solutions Inc" might need (In MS Access) (5%) • Make sure you document your work as you go along for full payment, as the owner of the business, wants to be able to show your work to new hires and they should be able to pick it up and understand why the database was put together the specific way you are suggesting. • Below you can find the "database" that Joe has put together with his limited data management knowledge, and your starting point. Invoice CompanyName Acct Balance Acct Limit Part1 Quantity1 Price1 Part2 Quantity2 Price2 87 Mary’s IT Services 5000 20000 Bolt 50 10 Nut 40 20 72 Mel’s Hotdogs 7200 7200 Bolt 100 10 Screw 45 30 57 Brookings 500 12000 Screw 60 30 Bolt 65 10 67 Parker’s 1000 5000 Nut 70 20 3 Action Computer’s 1000 10000 Screw 40 30 7 Kline’s 8000 9999 Bolt 30 10 9 John’s 10000 30000 Nail 25 50 17 Lee’s 700 1000 Bolt 75 10 Nail 120 50 19 Al’s 7000 7000 Screw 110 30 20 Al’s 7000 7000 Nut 90 20 Term Project and Proposal (17%) Attachments
Tags
Clarifications

Answers

Case Study Access Solution

dickersonka
dickersonka 



Attached is the access solution to the case... Attachments
CaseStudy.zip (43 K) Download

Preview
information fields CompanyName, AcctBalance, AcctLimit was placed into the invoice table. Each LineItem contains only information part information for the specific line. The Invoice table will contain the data for the Company information. 3NF Company(CompanyId,CompanyName,AcctBalance,AcctLimit) Invoice(InvoiceNum,CompanyId) LineItem(InvoiceNum,LineNum,PartId,Quantity,Price) Part(PartId,PartName) The Company and Part data were duplicated across records

Screenshots
Purchase Answer