DAD 220_Analysis and Summary_Rob Roome

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

Uploaded by DrCoyote2479

Professor: Torre Roenne Student: Robert Roome Due date: 10/1/2023 DAD 220 Analysis and Summary 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. 1. Analyze the data you’ve been provided with to identify themes : a. Which parts are being replaced most? i. When using the query below you can see that the part that is being replaced the most is the Fuel Tank that has been repaired 95 times. This is followed by Tire repair with 75 repairs and Tire replacement with 66 repairs. SELECT Repair AS PART_REPAIR, COUNT(*) AS NUMBER_OF_REPAIRS FROM Parts_Maintenance GROUP BY PART_REPAIR ORDER BY NUMBER_OF_REPAIRS DESC; b. Is there a region of the country that experiences more part failures and replacements than others? i. Identify region:

1. The region that seems to be experiencing more part replacements and repairs then the others would be in the Midwest region with 234 repairs. This is followed by the Northeast with 208 repairs, Southeast with 163 repairs, West with 66 repairs, and the Southwest with 63 repairs. The SQL query that I used to find this information was: SELECT 'Southwest' AS Region, COUNT(*) AS Number_of_Repairs FROM Parts_Maintenance -> WHERE UPPER(State) IN ('AZ', 'NM', 'TX', 'OK') -> UNION -> SELECT 'Southeast' AS Region, COUNT(*) AS Number_of_Repairs FROM Parts_Maintenance -> WHERE UPPER(State) IN ('AR', 'LA', 'AL', 'GA', 'TN', 'SC', 'NC', 'VA', 'WV', 'DE', 'MD') -> UNION -> SELECT 'Northeast' AS Region, COUNT(*) AS Number_of_Repairs FROM Parts_Maintenance -> WHERE UPPER(State) IN ('PA', 'NJ','NY','CT','RI','MA','VT','ME','NH') -> UNION -> SELECT 'Midwest' AS Region, COUNT(*) AS Number_of_Repairs FROM Parts_Maintenance -> WHERE UPPER(State) IN ('ND','SD','KS','NE','WI','IA','MO','MI','IN','IL','OH') -> UNION -> SELECT 'West' AS Region, COUNT(*) AS Number_of_Repairs FROM Parts_Maintenance -> WHERE UPPER(State) IN ('WA','ID','MT','OR','WY','CO','UT','NV','CA') -> ORDER BY Number_of_Repairs DESC; ii. How might the fleet maintenance team use the information to update its maintenance schedule?

1. The maintenance team would be able to use this information to identify trends within each region. Using the identified trend would allow them to adjust their staffing needs based on the region’s needs. So, they would be able to justify having more maintenance staff in the Midwest region as it is shown to be the region with the most repairs needed so a larger maintenance staff would be needed. The information is also helpful to identify what parts need to be in stock more than others. Another way that this data can be used is that the team could schedule more vehicle inspections and put into action more preventive maintenance measures in the regions with the higher repair numbers. c. Which parts are being replaced most due to corrosion or rust? i. When running this query to find what parts are being replaced due to corrosion or rust it is shown that the Wheel Arch is being repaired the most with 55 repairs. This is followed by Fender Replacements and Rocker Pannels with 54 and 53 repairs. d. Which parts are being replaced most because of mechanical failure or accident, like a flat tire or rock through the windshield? i. There are three parts that are being replaced most because of mechanical failure or accidents. In first place is tire repairs with 74 repairs followed by tire replacement with 66 repairs. The last one that was brought up by this query is

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

windshield replacement with 63 repairs 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. When analyzing the data above some important trends were found. The trends that were found could be useful to the company to improve the maintenance of the fleet of vehicles. First it was found that the most frequently replaced part in the fleet is the fuel tank, this would need to be looked into more to find out why this is. The next information found was that the Midwest region of the company requires the most repairs in the company. As that this region is having the greatest number of repairs more a higher number of resources and maintenance personal are needed in that region. Next, it was found that are 8 main parts that are being replace due to corrosion or rust. The top three parts were found to be the Wheel Arch at 55 repairs, Fenders at 54 repairs, and Rocker Panels at 53 repairs. This shows that some research should be put in to see if there is a way to avoid or limit this issue. The last piece of information that was found is the amounts of repairs that are being done due to mechanical failures or accidents. The main part that is being repaired or replaced in this category is Tires with 74 repairs and 66 replacements. As those tires are shown to be prone to be repaired or replace the most would indicate that a higher stock count would be needed. 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? i. In the first question I used the query SELECT Repair AS PART_REPAIR, COUNT(*) AS NUMBER_OF_REPAIRS FROM Parts_Maintenance GROUP BY PART_REPAIR ORDER BY NUMBER_OF_REPAIRS DESC;. This resulted in creating a table that showed the parts that were being replace most often. The SELECT Repair part groups the data by the Repair Coolum. The COUNT(*) counts the number of repairs for each part and ORDER BY DESC organizes the data in descending or within the table. ii. The second question used the query SELECT 'Southwest' AS Region, COUNT(*) AS Number_of_Repairs FROM Parts_Maintenance -> WHERE UPPER(State) IN ('AZ', 'NM', 'TX', 'OK')-> UNION-> SELECT 'Southeast' AS Region, COUNT(*) AS Number_of_Repairs FROM Parts_Maintenance -> WHERE UPPER(State) IN ('AR', 'LA', 'AL', 'GA', 'TN', 'SC', 'NC', 'VA', 'WV', 'DE', 'MD')-> UNION -> SELECT 'Northeast' AS Region, COUNT(*) AS Number_of_Repairs FROM

Parts_Maintenance-> WHERE UPPER(State) IN ('PA', 'NJ','NY','CT','RI','MA','VT','ME','NH') -> UNION -> SELECT 'Midwest' AS Region, COUNT(*) AS Number_of_Repairs FROM Parts_Maintenance -> WHERE UPPER(State) IN ('ND','SD','KS','NE','WI','IA','MO','MI','IN','IL','OH')-> UNION-> SELECT 'West' AS Region, COUNT(*) AS Number_of_Repairs FROM Parts_Maintenance -> WHERE UPPER(State) IN ('WA','ID','MT','OR','WY','CO','UT','NV','CA')-> ORDER BY Number_of_Repairs DESC;. This was a very long one to enter in as that it used multiple commands First the SELECT command was used to select each region of the company. This was followed by the COUNT(*) which counted the number of repairs that were done in each state in each region. Next was the WHERE UPPER(State) IN part identifies each state in the region to be counted. Lastly the ORDER BY DESC puts the results in the table being put in to order by the number of repairs done in descending order. The UNION pulls the data from each region and puts all the data into one table. iii. To identify the number of parts that are affected by corrosion or rust the query that was used was SELECT Repair AS Part_Repair, COUNT(*) AS Number_of_Repairs FROM Parts_Maintenance-> WHERE UPPER(Reason) IN ('CORROSION','RUST') -> GROUP BY Part_Repair -> ORDER BY Number_of_Repairs DESC;. SELECT identifies what column is to be used to create the new table the WHERE statement indicates to look for the reason and using IN identifies the specific reason. GROUP BY says to group the new data found under a part repair column and ORDER BY the number of repairs in descending order. iv. To identify the parts that are being replace most often to accidents or mechanical failure was SELECT Repair AS Part_Repair, COUNT(*) AS Number_of_Repairs FROM Parts_Maintenance -> WHERE UPPER(Reason) LIKE '%FLAT%' OR UPPER(Reason) LIKE '%CRACK%' -> GROUP BY Part_Repair -> ORDER BY Number_of_Repairs DESC;. SELECT identifies the column that is being used to find the data followed by AS to create the name of the new column as part_repaired. WHERE identifies the Reason column will also be used and adding the LIKE %flat% and %crack% so that these specific words were to be searched for. Then the data was grouped by the part repaired and ordered by the number of repairs in descending order. 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. Using these queries to retrieve the information had many benefits. The main benefit is that it allowed for the information to be organized into an easy format to read and understand for the stakeholders. It also showed them in what order their resources should be focused by what region needs them. Another benefit it tells them what parts are needed to be kept in stock to avoid delays in repairs and keep the maintenance up to date. 4. Explain how the functions in the analysis tool allowed you to organize the data and retrieve records quickly. a. These functions helped in organizing and retrieving the records quickly. The SELECT function allowed me to identify what columns to use which and parts were being looked

for in the Part_Maintenance table. The COUNT function was used to count the number of times that the specific data appeared in the Part_Maintenance table giving a clear single number that the reason or part that was being looked for. The GROUP BY function allowed me to group the data by the part names. The ORDER BY and DESC allowed for the data to be put in order of the number of times the specific data was found in descending order.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version