DAD 220 Analysis and Summary Template BROWN

docx

School

Southern New Hampshire University *

*We aren’t endorsed by this school

Course

220

Subject

Mechanical Engineering

Date

Feb 20, 2024

Type

docx

Pages

8

Uploaded by ChancellorKangaroo151

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. Table Creation: CREATE TABLE Parts_Maintenance (vehicle_id VARCHAR(20), state VARCHAR(2), repair VARCHAR(50), reason VARCHAR(50), year INT, make VARCHAR(20), body_type VARCHAR(50)); Data Into File: LOAD DATA INFILE '/home/codio/workspace/FleetMaintenanceRecords.csv' INTO Table Parts_Maintenance FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'; 1. Analyze the data you’ve been provided with to identify themes : a. Which parts are being replaced most?
From the information in the table below, it would appear as though the Fuel Tank is being replaced the most, with 95 replacements. This is followed by Tire Repair at 74 and Tire Replacement at 66. Command: 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 results of my query show that the Midwest region experiences more part failures and replacements than any other region with 260 total
Command: 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','MS','AL','GA','FL','KY','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','MN','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?
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
1. The Fleet Maintenance team can utilize the information to, firstly, discover where the most amount of failures, repairs, and replacements are concentrated. They can then look into the causes of those issues and address what issues occur most frequently and use that to develop a preventative maintenance plan. Knowing what parts fail most frequently would also give the team the ability to have a proper inventory based on the data provided. c. Which parts are being replaced most due to corrosion or rust? i. From my query, I was able to find that wheel arches, fenders, and rocker panels are the most commonly replaced items with 55, 54, and 53 respectively. Command 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; d. Which parts are being replaced most because of mechanical failure or accident, like a flat tire or rock through the windshield? i. The most common parts that are replaced by a mechanical failure of accident are Tires with a total of 66 and Windshields with a total of 63. General Tire repairs account for 74 of the total number of instances with this issue.
Command 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; 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. From the results of my query, I was able to identify various causes of costly repairs and replacements as well as to get an idea of how the company may use this data to better prepare for and mitigate these instances. First, it was identified that fuel tank repairs are the most common type of repair. We can use the data to identify the overall cause of the repair and work towards locating a root cause which would then allow the team to put in place a preventative maintenance plan to ensure the longevity of the fuel tanks. Second, it is shown that the Midwest seems to have the greatest occurrence of repairs and replacements with 260 total, compared to the 208 in the second place spot. This would be an indicator that more resources may need to be utilized in the region and perhaps more targeted preventative maintenance be performed as well. Third, we were able to identify the parts that failed due to corrosion. Wheel arches, fenders and rocker panels seem to take the brunt of the damage when it comes to rust and corrosion. With this information, the maintenance team can take a deeper look at what is causing that and perhaps provide some sort of solution, like a rust resistant coating, to mitigate the repairs. Last, we can see that it appears to be Tires and Windshields which fail the most frequently due to things like flats and rocks. While these things are inevitable, the team may be able to use this to look into more durable tire options, which could help lower the repair and replacement rate over time. 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 order to identify trends or themes in the data presented, I was able to use several SQL queries to target desired attributes within the dataset. Some of the queries I used are as follows: Command SELECT repair AS PART_REPAIR, COUNT(*) AS NUMBER_OF_REPAIRS FROM Parts_Maintenance GROUP BY PART_REPAIR ORDER BY NUMBER_OF_REPAIRS DESC; Explanation
This code calculates the total number of parts replaced within the Parts_Maintenance table and groups them by part repaired. It’s presented to us in descending order, making it easy to determine which parts are replaced the most and which the least. Command 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','MS','AL','GA','FL','KY','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','MN','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; Explanation This code allowed me to view all of the repairs by region. Command 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; Explanation This code allowed me to select within the Parts_Maintenance table the parts that were repaired or replaced by rust and corrosion specifically. Command 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;
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 This code allowed me to select certain attributes within the Parts_Maintenance table that contained references to flat or crack, which was useful in identifying the repairs that resulted from flat tires and cracked windshields. 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 allow us to present targeted data to stakeholders to make informed decisions about the maintenance of vehicles within their fleet. This will allow the company to determine what causes the most failures, where the most failures occur, and what parts are needed most as far as on hand inventory. If the fuel tank is the most prone to failure and the most likely to require replacement, for instance, the data can be used to pinpoint where it happens most and to provide a starting point for developing a root cause analysis. This could help to mitigate the repairs and replacements by allowing the team to develop more aggressive preventative maintenance programs for that and the other issues presented within the data. Overall, this can represent a cost savings to the company by allocating more resources where they are needed and cutting back on resources which aren’t needed so much and in regions where they aren’t so much needed. 4. Explain how the functions in the analysis tool allowed you to organize the data and retrieve records quickly. The SELECT function in MySQL allowed me to specify the data that I wanted to retrieve. This included part name, number of replacements, and regions. This allowed me to create a data set that was concise and clear. Using the GROUP BY function allowed me to take the data retrieved with the SELECT function and choose how I wanted to display it. This made it easy to read and understand. The COUNT function allowed me to calculate the total number of replacements for a given part of parts. Included with the other functions, this made for an easy to understand snapshot of how many parts were repaired in what regions and for what reasons. Using the ORDER BY function allowed me to choose to present the data in descending order, which made it easy to determine which parts were repaired or replaced the most, what regions were most affected, and what parts were replaced the most due to certain factors.
Overall, the functions within the program allowed for quick and easy representation of datasets based on specific criteria. This would be beneficial not only to someone presenting the data, but to the people the data is being presented to.