DAD 220 Analysis and Summary Template (2)

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

Uploaded by AgentHedgehogPerson918

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. Explanation: I used this command to create the Parts Maintenance table. 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)); Command I used: 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? i. Fuel tank is being replaced most with 95 number of repairs. Other top replacements include, tire repair (74) and tire replacement (66).

Explanation: This is the command I used: 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: Midwest has the highest part failures with 260 repairs. ii. Northeast: 208 repairs iii. Southeast: 186 repairs iv. Southwest: 63 repairs v. West: 66 repairs

Command I used: SELECT REGION, reason AS REPLACEMENT_REASON, COUNT(*) AS NUMBER_OF_REASONS FROM ( SELECT CASE WHEN UPPER(state) IN ('AZ','NM','TX','OK') THEN 'SOUTHWEST' WHEN UPPER(state) IN ('AR','LA','MS','AL','GA','FL','KY','TN','SC','NC','VA','WV','DE','MD') THEN 'SOUTHEAST' WHEN

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

UPPER(state) IN ('PA','NJ','NY','CT','RI','MA','VT','NH','ME') THEN 'NORTHEAST' WHEN UPPER(state) IN ('ND','SD','KS','NE','MN','WI','IA','MO','MI','IN','IL','OH') THEN 'MIDWEST' WHEN UPPER(state) IN ('WA','ID','MT','OR','WY','CO','UT','NV','CA') THEN 'WEST' END AS REGION, reason FROM Parts_Maintenance ) AS region_reason_count GROUP BY REGION, REPLACEMENT_REASON ORDER BY REGION, NUMBER_OF_REASONS DESC; vi. How might the fleet maintenance team use the information to update its maintenance schedule? The fleet maintenance can use the data to update its maintenance schedule by knowing that the Midwest has more repairs, so they should have more resources, techs, and spare parts to this region. It will also help them to inspect more and prevent maintenance in regions like the Midwest who have higher repairs than other regions. c. Which parts are being replaced most due to corrosion or rust? Wheel Arch (55). Fender replacement (54). Rocker Panel (53). Command used: 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? Tire repair (74). Tire replacement (66). Windshield replacement (63).

Command I used: 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. After analyzing the data, the fuel tank has been more frequently replaced. The Midwest area has the highest number of part failures and replacements, which means there needs to be more resources and focus in that area, but also in the Northeastern region since it doesn’t fall far behind the Midwest region. Wheel arch and Fender replacement are replaced the most due to corrosion and rust. Tires have been the most repared due to mechanical failures and/or accidents. 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 used SELECT as the main query because it finds the data I need and with COUNT(*), it finds the exact number of whatever information I need to know like number of replacements for each part. UNION was used to get a diversity of the regions. GROUP BY groups the data by the repair column. ORDER BY sorts results in descending order based on replacement number. 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? Because there is so much information, it narrows it down and it helps to find things quicker. If you couldn’t narrow down what you are looking for, you would have to go through tons of data. 4. Explain how the functions in the analysis tool allowed you to organize the data and retrieve records quickly.

Using the functions in the analysis tool helped me to organize data and retrieve records quickly because the SELECT function allows you to specify data fields, such as number of replacements and part names. GROUP BY helps group data by part names, such as number of replacements for each part. COUNT function helped me calculate the total number of replacements for each part. ORDER BY helped me sort data in descending order. UNION helped me combine data from different regions. Overall, all the functions are helpful because they narrow down information you need and help you move quicker.

Your preview ends here

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