Adventure Works Cycles Company Story Business Adventure Works Cycles, the fictitious company on which the AdventureWorks sample databases are based, is a large, multinational manufacturing company. -

Adventure Works Cycles Company Story

Business

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.

Product Overview

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.

Manufacturing Overview

· Bill of materials: List of the products used or contained in another product.

· Work orders: Manufacturing orders by work center.

· Locations: Major manufacturing and inventory areas, such as frame forming, paint, subassembly, and so on.

· Manufacturing and product assembly instructions by work center.

· Product inventory: The physical location of a product in the warehouse or manufacturing area, and the quantity available in that area.

· Engineering documentation: Technical specifications and maintenance documentation for bicycles or bicycle components.

Assignment 1

The purpose of this assignment is to practice navigating and identifying database tables and related fields.

For this assignment, assume you have just been hired by Adventure Works Cycles company. As part of the orientation process, you have been asked to learn a little about the company by reading the “Adventure Works Cycles Company Story.”

Your supervisor also wants you to gain an understanding of the content included in the company database. To do this, you must utilize SELECT and FROM statements like the example provided below.

EXAMPLE: SELECT * FROM [Database].[Schema_Name].[Table_Name]

Access the “AdventureWorks 2016 and Scripts for SQL Server 2016 CTP3” database and use SELECT and FROM statements to find the answer to each of the questions below. Create a Word document that includes the SQL queries used to explore the database tables and answer the following questions using the queries completed in steps 1-5.

Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to an Excel file for a visual check of accuracy.

1. Locate the “Person” table and run a basic SELECT query as listed in the example. List all the available fields and how many records exist in that table.

2. On what table and schema would you locate an applicant’s resume data?

3. When is the CEO’s Birthday?

4. What is the list “ListPrice” of the product “HL Touring Seat Assembly?”

5. Is “Holiday Skate & Cycle” a preferred vendor?

Compile the Excel data file and Word document containing the SQL queries and answers to the questions into a .zip file and submit to your instructor.

APA style is not required, but solid academic writing is expected.

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

You are not required to submit this assignment to Turnitin.

For part 2 of assignment 1

Proposal for Process Improvement

Requestor:

Business Problem:

Proposed Solution:

Required Resources:

Implementation Steps:

Benefits:

Potential Obstacles:

1. The purpose of this assignment is to practice making proposals to communicate database needs to relevant stakeholders.

1. For this assignment, assume you work at Adventure Works Cycles. Your manager recently informed you that by law, all employees must have an emergency phone number on file. This information is not currently included in the company database, so you must submit a proposal to the IT Department detailing your request to have it added.

1. Using the “Proposal for Process Improvement” template, construct a Word document that outlines the requester, business problem, proposed solution, resources, implementation steps, benefits, and potential obstacles of the request.

1. APA style is not required, but solid academic writing is expected.

1. This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

1. You are not required to submit this assignment to Turnitin.

Assignment 2

The purpose of this assignment is 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.

Part 1:

For this part of the assignment, you will use the “AdventureWorks 2014 OLTP Schema.” Open the schema and follow the tree/chain to answer the questions below in a Word document.

1. How does the Sales.SalesTerritory table relate to the Person.StateProvince table? Which table holds the Primary and the Foreign Key? On what field do they join together?

2. If you wanted to know the Planned Cost of a Product, what route would you take to the data? Identify the tables and fields used to join on each.

3. Imagine a bonus was paid to an employee using a different currency. Identify the fields you would need so you could access the information. List all the tables involved and the fields used to join.

Part 2:

For this part of the assignment, you will continue to assume you work at Adventure Works Cycles company. In the Topic 1 assignment, you made a request to add a field to the company database. That request has been approved, and now the database diagram needs to be accurately updated.

Create a simple OpenOffice Draw document demonstrating how your new table will connect to the Human Resources.Person table on the diagram. Since this is a new table, add new fields to create the proper relationships as you see fit.

In a one or two paragraph Word document, explain how these fields relate to the rest of the database content.

Compile the OpenOffice Draw file and Word document into a .zip file and submit to your instructor.

APA style is not required, but solid academic writing is expected.

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

You are not required to submit this assignment to Turnitin.

Assignment 3

The purpose of this assignment is to identify business problems that can be addressed through analyzing database content.

For this assignment, assume the role of a data analyst at Adventure Works Cycles company. Your manager recently approached you with a problem. The company is losing money on its popular model “LL Road Frame-Black 60” but cannot determine why sales are down. You have been tasked to research potential reasons why this product, which your manager believes is solid, is not selling.

In order to address this business problem, you must deconstruct it. 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.

In a 250-word document, address the following. Include basic information related to the problem along with specific information about the tables that should be researched.

1. Explain what methods will be used to set the parameters for the query.

2. Define the specific questions that need to be asked and answered in order to address the specified business problem.

3. Explain how the entity relationship diagram (ERD) was used in addressing questions 1-2 above.

4. Describe ethical dilemmas that could be encountered as a result of the research being done to address the business problem.

5. Explain what resources you will use to assist with addressing ethical dilemmas.

APA style is not required, but solid academic writing is expected.

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

You are not required to submit this assignment to Turnitin.

Assignment 4

The purpose of this assignment is to design and implement queries that will assist in developing answers to business problems.

For this assignment, continue to operate in the role of a data analyst at Adventure Works Cycles company. Based upon “LL Road Frame-Black 60” scenario and questions formulated for the Topic 3 assignment, write queries for the “AdventureWorks 2016” database using SQL Server 2016 Developer Edition.

Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to Excel for a visual check of accuracy.

Create a Word document that includes the SQL queries used to explore the database tables, and answer the following questions.

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.

Compile the Excel data file and Word document containing the SQL queries and answers to the questions into a .zip file and submit to your instructor.

APA style is not required, but solid academic writing is expected.

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

You are not required to submit this assignment to Turnitin.

Assignment 5

The purpose of this assignment is to design and implement queries that will assist in developing answers to business problems.

Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to Excel for a visual check of accuracy.

Create a Word document that includes the SQL queries used to explore the database tables, and answer the following questions.

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.

Compile the Excel data file and Word document containing the SQL queries and answers to the questions into a .zip file and submit to your instructor.

APA style is not required, but solid academic writing is expected.

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

You are not required to submit this assignment to Turnitin.

Part 2

The purpose of this assignment is to create temporary tables and document them using an entity relationship diagram (ERD).

For this assignment, continue to play the role of the data analyst for Adventure Works Cycling Company.

Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to Excel for a visual check of accuracy.

Create a Word document that includes the SQL query code for the temporary table that includes the dates of each sales order.

1. Create a temporary table for each of the Topic 4 assignment data components.

2. Use the skills you have learned and practiced to write a query to determine the dates of each sales orders from a table you have not looked at yet. Create a temporary table for this information.

3. Use OpenOffice Draw to create a new entity relationship diagram (ERD) to document the addition of the temporary tables and the work stream. Show the relationship between the tables.

Compile the OpenOffice Draw, Excel data file, and Word document containing the SQL queries and answers to the questions into a .zip file and submit to your instructor.

APA format is not required, but solid academic writing is expected.

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

You are not required to submit this assignment to Turnitin.

Assignment 6

The purpose of this assignment is to complete two complex queries, join them, and validate the expected results of the join.

For this assignment, continue to assume the role of a data analyst at Adventure Works Cycling Company. As you work to address the business problem surrounding sales of the “LL Road Frame-Black 60,” you must continue to refine the data by condensing them into two tables. This requires you to join the table data sets together. While this is an important process, you must be aware of the data integrity issues that can occur as a result of completing joins.

Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to Excel for a visual check of accuracy.

Create a Word document that includes the SQL query code used to explore the database tables and answer the following questions.

Complete the steps below to practice joins.

1. Combine MyProduct and MyPriceHistory displaying the fields into a new temporary table called MyProductPriceHistory. Display ProductID, Name, EndDate, and List Price.

2. Combine MyProduct and MySalesOrderDetail displaying the fields into a new temporary table called MySalesOrderDetailbyName. Display ProductID, Name, SalesOrderID, and Quantity.

3. Combine MySalesOrderDetailbyName and ALLSalesOrderDates displaying the fields into a new temporary table called MySalesHistory. Display ProductID, Name, OrderDate, and SumOfQuantity. This will require you using the SUM and GROUP BY statements.

Create a Word document that includes the SQL query code used for each of the joins listed. Additionally, identify and analyze the data integrity issues you encountered in SQL.

1. Discuss bad queries and bad table data in your analysis.

2. Explain the impact of the data integrity issues.

3. Indicate what needs to be revised in the code.

4. Discuss specifically what needs to be done to fix the data integrity issues created by the join. Please note that as part of your final project for the course you will be adding these new tables to the ERD you created in Topic 5.

Compile the Excel data file and Word document containing the SQL queries and answers to the questions into a .zip file and submit to your instructor.

APA style is not required, but solid academic writing is expected.

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

You are not required to submit this assignment to Turnitin.

Assignment 7

he purpose of this assignment is to update a previous query and present it in an easily readable format.

For this assignment, continue to assume the role of a data analyst at Adventure Works Cycles company.

Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to Excel for a visual check of accuracy.

Create a Word document that includes the SQL query code used to explore the database tables and answer the following questions.

Part 1:

1. Using what you have learned, replicate the data found in #MySalesOrderDetailbyDate and #MyProductPriceHistory using a single line of SQL code. Refer back to the Topic 5 assignment for this information.

2. Modify the query completed in the Topic 6 assignment, and present it in an easy-to-read format. To do this, write the query in two single statements without temporary tables, using abbreviations and line spacing.

Part 2:

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 you have learned, produce a query in one statement to give her the information she needs. Note that constructing the query will require some thinking outside the box since the relationships are not well documented.

Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to Excel for a visual check of accuracy. This Excel file should include the names of all documents Karen has produced.

Add to the Word document you created in Part 1 and include the SQL query code associated with the query you wrote to locate Karen’s documents.

Compile the Excel data file and Word document containing the SQL queries and answers to the questions into a .zip file and submit to your instructor.

APA style is not required, but solid academic writing is expected.

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

You are not required to submit this assignment to Turnitin.

Assignment 8

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.

Compile the updated ERD, Excel data file, and Word document containing the SQL queries and answers to the questions into a .zip file and submit to your instructor.

APA style is not required, but solid academic writing is expected.

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

You are not required to submit this assignment to Turnitin.

Part 2

The purpose of this assignment is to build a stored procedure that allows for the compression of data and the ability to run a process repeatedly over time.

For this assignment, continue to assume the role of a data analyst at Adventure Works Cycling Company. Your manager is waiting for you to deliver your analysis of why the company is losing money on its popular model “LL Road Frame-Black 60.” As you are preparing your final report, your manager asks you to save your final queries from Topic 6 (the two nontemporary queries) as a stored procedure so you can periodically check on them from time to time. To do this, you will need to complete the steps below.

Please note that when SQL queries are run, results are generated in the form of data. This data should be exported and saved to Excel for a visual check of accuracy.

Create a Word document that includes the SQL query code used to explore the database tables.

1. Drop all ORDER_BY statements from the query.

2. Label one as PriceHistory723.

3. Label the other as SalesOrderDetailbyDate723.

Compile the Excel data file and Word document containing the SQL queries into a .zip file and submit to your instructor.

APA style is not required, but solid academic writing is expected.

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

You are not required to submit this assignment to Turnitin.