Perform a functional dependency analysis

Assignment Help Database Management System
Reference no: EM13758284

You are a star consultant from "Databases R US" IT consulting and managing firm. Just graduated from APUS and this is your first critical job. You just got hired by "Home Solutions Inc" a construction and parts company to help them with their customer and parts data management. Unfortunately, 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 of experience with data management is hurting the business as they have been unable to successfully keep track of their inventory system, customers and sales. Joe has put together a spreadsheet, see below, and they have been using it as a database of sorts, to track their customers and inventory.

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.

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

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.

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.

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)

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)

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)

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

 

 

 

You can assume that the quantities reflect packages and the prices are per item

Below you can find an alternative format for the same "database" that Joe has put together with his limited data management knowledge, and your starting point.

Invoice

CompanyName

Acct Balance

Acct Limit

Part

Quantity

Price

87

Mary's IT Services

5000

20000

Bolt
Nut

50
40

10
20

72

Mel's Hotdogs

7200

7200

Bolt
Screw

100
45

10
30

57

Brookings

500

12000

Screw
Bolt

60
65

30
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
Nail

75
120

10
50

19

Al's

7000

7000

Screw

110

30

20

Al's

7000

7000

Nut

90

20

Reference no: EM13758284

Questions Cloud

Identify events that could delay the closing of a project : Identify events that could delay the closing of a project or phase. Evaluate which event you believe would cause the longest delay and explain why. Provide an example to justify your answer
The evidence-based project-paper on diabetes : Based on the summary of research findings identified from the Evidence-Based Project-Paper on Diabetes that describes a new diagnostic tool or intervention for the treatment of diabetes in adults or children
Evaluating project performance : Suggest activities that can be performed by the project manager in order to evaluate individual performance, project planning, and how well the project met the measureable organization value (MOV)
Can six sigma be used for both products and services : According to the American society for quality, customers in the United States consistently rate service quality lower than product quality.  What are the similarities and differences between measuring service quality and product quality?  Do demines..
Perform a functional dependency analysis : 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 - Perform a functional dependency analysis, and include it as part of your deliverables, for every s..
Explain the regulatory compliance law : From the e-Activity, explain the regulatory compliance law that you researched, and ascertain the effect that information security could have on such a law
Deductive logic appear in all business communications : Inductive and deductive logic appear in all business communications that will give the basis of your argument. What other (5) considerations should also be made in the design of your message?
A plan for planning : Case: You are a consultant for a Fabric company that burned down, this company was the only source of income for many of the town people they all worked there, as a consultant to the owner how to rebuild the company to where it will be better than be..
Guidance provided by department of health and human services : Describe the guidance provided by the Department of Health and Human Services, the National Institute of Standards and Technology (NIST), and other agencies for ensuring compliance with these standards and regulatory requirements

Reviews

Write a Review

 

Database Management System Questions & Answers

  Development of an effective entity relationship model

Determine the steps in the development of an effective Entity Relationship Model (ERM) Diagram and determine the possible iterative steps

  Part 1 true or false please explain why 1 sql structured

part 1 true or false please explain why. 1 sql structured query language is both ddl data definition language and dml

  Consider the eer diagram for a car dealer

Consider the EER diagram for a car dealer in the figure below. Map the EER schema into a set of relations. For the VEHICLE to CAR/ TRUCK/ SUV generalization.

  Sketch object-oriented model for private airport database

Sketch an object-oriented model for a small private airport database that is used to keep track of airplanes, their owners, airport employees, and pilots.

  Prepare fully attributed data model

Use the results of the transcripts of an interview with IT consultant. Prepare a Fully Attributed Data Model. Add the data attributes for each entity."

  Question 1a explain the use of facts dimensions and

question 1a explain the use of facts dimensions and attributes in a star schema model.b in relation to a fact table

  If each instance for r listed above is legal what can you

If each instance for R listed above is legal, what can you say about the FD A → B?

  Estimated costs and expected activity

Abel Company uses activity-based costing. The company has two products - estimated costs and expected activity

  Discuss likely data integrity constraints on attributes

Write an SQL query to find out how many Computer Science departments are not in Victoria. Return the number -  Discuss likely data integrity constraints on attributes and specify all candidate keys and the primary key for each relation based on the..

  How does oracle process query

How does Oracle process this query? That is, what does Explain Plan tell you about how the query is processed - how would you recognize that the results were not correct?

  Draw dependency diagram after identifying all dependencies

Draw the dependency diagram after identifying all dependencies in this data structure. See Lecture 6, Slide 27 for dependency diagram and convert this data structure to a set of 3NF relations. Clearly showing each step

  How might the tables be related to one another

What tables would you create, and what would the table components be and How might the (independent) tables be related to one another?

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd