DAD 220 Analysis and Summary Medeiros

docx

School

Southern New Hampshire University *

*We aren’t endorsed by this school

Course

220

Subject

Mechanical Engineering

Date

Jan 9, 2024

Type

docx

Pages

8

Uploaded by rwmedeiros

Report
DAD 220 Analysis and Summary Template Replace the bracketed text in this template with your responses and any supporting screenshots. Then submit it to the Module Five Activity for grading and feedback. Rename this document by adding your last name to the file name before you submit. Explanation: Created a table names PartsMaintenance using the following command: CREATE TABLE PartsMaintenance (vehicle_id VARCHAR(20), state VARCHAR(2), repair VARCHAR(50), reason VARCHAR(50), year INT, make VARCHAR(20), body_type VARCHAR(50)); I was then able to show the command worked using the following show table command: show tables; 1. Analyze the data you’ve been provided with to identify themes :
a. Which parts are being replaced most? Explanation: Using the commands below: SELECT repair AS PART_REPAIR, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance GROUP BY PART_REPAIR ORDER BY NUMBER_OF_REPAIRS DESC; We can identify that the “ Fule” tank (Fuel tank) is the most replaced part coming in at 95 replacements . b. Is there a region of the country that experiences more part failures and replacements than others? i. Identify region: Explanation: We can identify which region of the country that experiences more part failures and replacements using the command below: SELECT 'SOUTHWEST' AS REGION, COUNT(*) AS NUMBER_OF_REPAIRS
FROM PartsMaintenance WHERE UPPER(state) IN ('AZ','NM','TX','OK') UNION SELECT 'NORTHEAST' AS REGION, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance WHERE UPPER(state) IN ('PA','NJ','NY','CT','RI','MA','VT','ME','NH') UNION SELECT 'SOUTHEAST' AS REGION, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance WHERE UPPER(state) IN ('AR','LA','AL','GA','TN','SC','NC','VA','WV','DE','MD') UNION SELECT 'MIDWEST' AS REGION, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance WHERE UPPER(state) IN ('ND','SD','KS','NE','MN','WI','IA','MO','MI','IN','IL','OH') UNION SELECT 'WEST' AS REGION, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance WHERE UPPER(state) IN ('WA','ID','MT','OR','WY','CO','UT','NV','CA') ORDER BY NUMBER_OF_REPAIRS DESC; The result of this query identifies the Midwest as experiencing the most part failures with 260 repairs while the nest closest region is the Northeast with 208 repairs. Using the UNION command to combine the multiple results that were received from each SELECT statement that represented their own specific region. ii. How might the fleet maintenance team use the information to update its maintenance schedule? 1. Fleet Maintenance can use this information to alter their maintenance schedule. 2. More resources can be allotted to the regions with the highest number of repairs. 3. The team can from this point schedule and do more preventative maintenance in those higher regions. 4. Combining the previous tables with this information can also allow the inventory and logistics departments to use more warehouse space for the more pertinent parts that are needed. c. Which parts are being replaced most due to corrosion or rust? i.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Explanation: Using the below command allows us to see which parts are being replaced most due to corrosion or rust: SELECT repair AS PART_REPAIR,COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance WHERE UPPER(reason) IN ('CORROSION','RUST') GROUP BY PART_REPAIR ORDER BY NUMBER_OF_REPAIRS DESC; Using the above code to disseminate the information that we need. We can clearly see that Wheel Arch and Fender replacement are the top 2 parts that are replaced due to rust or corrosion. The maintenance team can use this information as part of a preventative maintenance plan. d. Which parts are being replaced most because of mechanical failure or accident, like a flat tire or rock through the windshield? i.
Explanation: We can retrieve the information on which parts are being replaced most because of mechanical failure or accident (flat tire or rock through the windshield) using the command below: SELECT repair AS PART_REPAIR,COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance WHERE UPPER(reason) LIKE '%FLAT%' OR UPPER(reason) LIKE '%CRACK%' GROUP BY PART_REPAIR ORDER BY NUMBER_OF_REPAIRS DESC; The result of this query shows us the parts that are being replaced most because of mechanical failure or accident (flat tire or rock through the windshield) are Tires . Having this information readily accessible to the maintenance team will allow them to keep the needed tires in stock, and make sure that every vehicle has the tools/equipment to deal with slow leaks or blow outs. 2. Write a brief summary of your analysis that takes the information from Step 1 and presents it in a way that nontechnical stakeholders can understand.
a. With the data that we retrieved from Step 1 there are some important trends to pay special attention to the “ Fule Tank” (Fuel Tank) to get down to the reason why it is the most replaced part. On top of the issue with the “ Fule Tank” (Fuel Tank), the “ Midwest” has the highest number of part failures, which is a cause for a diversion of resources from the areas that don’t need as much resources as the rest of the areas (think of a tiered resource system) to fortify the Midwest. In closing, using this data and applying it to the fortification of resources in the Midwest and the replacement of Wheel Arch and Fenders due to rust and corrosion and prioritize preventative measures for these issues. 3. Outline the approach that you took to conduct the analysis. a. What queries did you use to identify trends or themes in the data? SELECT repair AS PART_REPAIR, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance GROUP BY PART_REPAIR ORDER BY NUMBER_OF_REPAIRS DESC; This was the first query we ran that allowed us to identify the most replaced part in the entire fleet. SELECT 'SOUTHWEST' AS REGION, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance WHERE UPPER(state) IN ('AZ','NM','TX','OK') UNION SELECT 'NORTHEAST' AS REGION, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance WHERE UPPER(state) IN ('PA','NJ','NY','CT','RI','MA','VT','ME','NH') UNION SELECT 'SOUTHEAST' AS REGION, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance WHERE UPPER(state) IN ('AR','LA','AL','GA','TN','SC','NC','VA','WV','DE','MD') UNION SELECT 'MIDWEST' AS REGION, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance WHERE UPPER(state) IN ('ND','SD','KS','NE','MN','WI','IA','MO','MI','IN','IL','OH') UNION SELECT 'WEST' AS REGION, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance WHERE UPPER(state) IN ('WA','ID','MT','OR','WY','CO','UT','NV','CA') ORDER BY NUMBER_OF_REPAIRS DESC; This was the second query that we ran that allowed us to split the entire country up into regions and identify which region had the most part failures and it returned that the Midwest was experiencing the most part failures. By using the UNION command to combine the multiple results that were received from each SELECT statement that represented their own specific region. SELECT repair AS PART_REPAIR,COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance WHERE UPPER(reason) IN ('CORROSION','RUST')
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
GROUP BY PART_REPAIR ORDER BY NUMBER_OF_REPAIRS DESC; This was our third query and it allowed us to see what parts were the most replaced due to damage caused by rust or corrosion. The “WHERE” clause in the statement acts as the filter for the data and is based on specified reasons such as “RUST” and/or “CORROSION” in the Reason” column of the table. “COUNT” calculates the number of parts replacements, using the combination of these commands in the coding allows us to identify the parts that are the most susceptible to rust or corrosion related issues. b. What are the benefits of using these queries to retrieve the information in a way that allows you to provide valuable information to your stakeholders? i. These queries are beneficial and provide insights to the stakeholders because they help to identify the most replaced parts, doing so allows feet maintenance to prioritize and institute preventative measures on the parts that need extra attention. These queries also break the country down to regions and doing so allows the company to analyze the regions differently and “rank” them I n terms of a “worst to first” method that would allow some resources to be diverted from the lesser needed areas to the “worst” or “most needed” areas. They also can prevent much of the damage done by rust and corrosion. The company can also identify most parts replaced due to mechanical failures or accidents which will allow them to take measures targeted at either preventing these incidents or limiting them and improving the overall safety and effectiveness of the fleet. When the effectiveness of the fleet is improved and more reliable it allows us to offer a more efficient service to everyone, including the stakeholders. 4. Explain how the functions in the analysis tool allowed you to organize the data and retrieve records quickly. a. The functions in the analysis tool helped greatly in the organization of the data and quickly retrieving records. This allowed our process of analysis to be quick and efficient. “SELECT” -- Allowed me to apply specification to the data that needed to be retrieved. (Part name, # of replacements…. etc.) this made the results clear, relevant, and easy to read/understand. “GROUP BY” – Allowed the grouping of data based on part names, this made it easy to identify the most replaced parts and overall impact on the fleet. “COUNT” – Allowed me to calculate the total number of replacements for each part. Having this knowledge makes it easy to know what you want to prioritize and direct resources. “ORDER BY” – Allowed me to sort the data in either ascending or descending order. Being able to use this command made it easy when I needed to identify the parts that had the highest replacement counts. “UNION” – Allowed us to combine data from multiple queries. Using this function helped with returning relevant and accurate records quickly.
By using these functions, it helped me to get a better understanding of parts patterns and gave me the data to make data-driven decisions and formulate a plan to enhance the fleet’s reliability and safety.