Question

The purpose of this assignment is to analyze data and use it to provide stakeholders with potential answers to a previously identified business problem.

For this assignment, continue to assume the role of a data analyst at Adventure Works Cycling Company. Evaluate the data associated with the drop in sales for the popular model "LL Road Frame-Black 60." Provide a hypothesis on what could be contributing to the falling sales identified in the initial business problem presented by your manager.

In 250-500 words, share these recommendations in a Word document that addresses the following.

1. Summary of the business problem including the requestor who initially brought the problem to you.
2. Summary of the data that were requested and how they was obtained.
3. Discussion of the limitations of the available data and ethical concerns related to those limitations.
4. Hypothesis of why sales of the popular model have dropped based upon data analysis. Reference the Excel file that summarizes the data findings that resulted from your queries.
5. Recommendations for addressing the business problem.
6. In addition to the report, the manager has requested that you submit the Excel files summarizing the data findings that resulted from your queries.
7. The manager has also requested that you update the ERD you created in the Topic 5 assignment to include the tables generated as a result of the joins completed in the Topic 6 assignment. The ERD should clearly document the work stream and relationships.

Answer 1

According to the case scenario here, we can say that initially the problem is caused by the drop in sales for the popular model "LL road Frame- Black 60". Let's first of all know about the company and the product.

So, Adventure Works Cycles, the fictitious company on which the AdventureWorks sample databases are based, is a large, multinational manufacturing company. The company manufactures and sells metal and composite bicycles to North American, European, and Asian commercial markets. While its base operation is located in Bothell, Washington, with 290 employees, several regional sales teams are located throughout its market base.

In 2000, Adventure Works Cycles bought a small manufacturing plant, Importadores Neptuno, located in Mexico. Importadores Neptuno manufactures several critical subcomponents for the Adventure Works Cycles product line. These subcomponents are shipped to the Bothell location for final product assembly. In 2001, Importadores Neptuno, became the sole manufacturer and distributor of the touring bicycle product group.

Coming off a successful fiscal year, Adventure Works Cycles is looking to broaden its market share by targeting its sales to its best customers, extending its product availability through an external website, and reducing cost of sales through lower production costs.

As a bicycle manufacturing company, Adventure Works Cycles has four product lines:

· Bicycles that are manufactured at the Adventure Works Cycles company.

· Bicycle components that are replacement parts, such as wheels, pedals, or brake assemblies.

· Bicycle apparel that is purchased from vendors for resale to Adventure Works Cycles customers.

· Bicycle accessories that are purchased from vendors for resale to Adventure Works Cycles customers.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

So, After analysing the question here,  manager recently informed that by law, all employees must have an emergency phone number on file. so I submitted a proposal to the IT Department detailing the request to have it added and after analysing the AdventureWorks 2014 OLTP Schema. I understood that the main flaw or the main reason for which this is happening is nothing but the support issues that the customer is facing.

As we have not compromised in the quality of the product. As we have to analyze the structure of a relational database and demonstrate the ability to correctly document and explain additions to the structure of a relational database.

In order to address this business problem, we must deconstruct it. we have to Study the entity relationship diagram (ERD) titled "AdventureWorks 2014 OLTP Schema," and use it determine the specific questions that must be asked and answered to address the problem.

We can also design and implement queries that will assist in developing answers to business problems. Write queries for the "AdventureWorks 2016" database using SQL Server 2016 Developer Edition.

To go more deep in identifying it, what we can do is, we can :-

1. Find the product ID for the LL Road Frame – Black 60.

2. Find the listing price of the LL Road Frame – Black 60.

3. How would you rewrite the query used in question 2 to exclude NULL values?

4. How many orders have been placed for LL Road Frame – Black 60?

5. Rename the OrderQty to Quantity in your results.

we can also create temporary tables and document them using an entity relationship diagram (ERD).

we can write a query to determine the dates of each sales orders from a table you have not looked at yet.

As we know, Karen Berge, a document control assistant at the company, comes to you with a request. Karen wants the titles and file names of all of the documents she has produced and has asked you to generate this information for her. Using what we  have learned, we can produce a query in one statement to give her the information she needs.

So finally we can  build a stored procedure that allows for the compression of data and the ability to run a process repeatedly over time.

Hope it helps.