Module 5 Activity Claudia Resendez

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 ElderScienceSwan36

DAD 220 Analysis and Summary Template The command I used is: 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)); This command created a table called parts_maintenance and this table is in the database named after me(Resendez). This command loaded data from a CSV file into the ‘’Parts_Maintenance’ table using the LOAD DATA INFILE statement. 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? The command I used was : SELECT repair AS PART_REPAIR, COUNT(*) AS NUMBER_OF_REPAIRS -> FROM Parts_Maintenance -> GROUP BY PART_REPAIR -> ORDER BY NUMBER_OF_REPAIRS DESC; Based on this, the parts that are being replaced the most is Fule Tank because it has 95 replacements. Tire repair has 74 and Tire replacement has 66. Windshield had 63 replacements. Battery had 56 replacements. b. Is there a region of the country that experiences more part failures and replacements than others? i. Identify region:

The command I used for this step was: SELECT "SOUTHWEST" AS REGION, COUNT(*) AS NUMBER_OF_REPAIRS FROM Parks_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 ('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; The midwest has the highest amount of failures and replacements because it has 260 repairs. Followed by the northeast with 208 repairs, Southeast with 186, West with 66 repairs, and the South west with 63 repairs.

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

ii. How might the fleet maintenance team use the information to update its maintenance schedule? The fleet maintenance team uses the data to update the maintenace schedule in multiple ways. Since the Midwest region has the highest number of repairs, the maintenance team should have more technicans and parts to this region. This would help the repairs be fixed quickly instead of having to wait forever for a repair. Since this area has the highest number, they should do routine inspections to maybe reduce the amount of repairs. The team can manage to have more parts readily available in this region. By using this information, the team can better prepare and use their time more wisely. c. Which parts are being replaced most due to corrosion or rust? The command I 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; The wheel arch(55 replacements), Fender replacement(54 replacements), and Rocker panel(53 replacements). d. Which parts are being replaced most because of mechanical failure or accident, like a flat tire or rock through the windshield?

The command I used 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; The parts being replaced the most because of mechanical failure or accident are tire replacement has 74 replacements followed by tire replacement with 66 replacements and windshield replacements with 63 replacements.

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. After analyzing the data, I found some things that can improve maintenance. The fuel tank is the most replaced part but needs further review to understand why there are so many needing to be replaced. I also noticed the Midwest region has the most amount of part failures which means this region needs more maintenance and parts. The wheel arch, fender replacement, and the rocker panel have a lot of replacements due to corrosion and rust which could be prevented with proper maintenance. This information helps the maintenance team know where they are needed more and where spare parts need to be. This information can help prevent longer wait times for spare parts and can also help prevent things from happening with proper prevention. In an area with lots of snow and cold weather, parts are more likely to rust and corrode due to salt. By the team knowing this, they can be readily prepared. The team can keep learning from this data to better improve the amount of repairs needed in each region. 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 SQL queries to retrieve data from the Parts_Maintenance table. The query I used to find the most replaced parts 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; This query calculated the number of replacements that were caused by either rust or corrosion. i. To determine what regions were experiencing the most amount of replacements I used the command: SELECT "SOUTHWEST" AS REGION, COUNT(*) AS NUMBER_OF_REPAIRS ii. FROM Parks_Maintenance iii. WHERE UPPER(state) IN ('AZ', 'NM', 'TX', 'OK') iv. UNION v. SELECT "SOUTHEAST" AS REGION, COUNT(*) AS NUMBER_OF_REPAIRS vi. FROM Parts_Maintenance vii. WHERE UPPER(state) IN ('AR', 'LA', 'MS', 'AL', 'GA', 'FL', 'KY', 'TN', 'SC', 'NC', 'VA', 'WV', 'DE', 'MD') viii. UNION ix. SELECT "NORTHEAST" AS REGION, COUNT(*) AS NUMBER_OF_REPAIRS x. FROM Parts_Maintenance xi. WHERE UPPER(state) IN ('ND', 'SD', 'KS', 'NE', 'MN', 'WI', 'IA', 'MO', 'MI', 'IN', 'IL', 'OH') xii. UNION xiii. SELECT "WEST" AS REGION, COUNT(*) AS NUMBER_OF_REPAIRS xiv. FROM Parts_Maintenance xv. WHERE UPPER(state) IN ('WA', 'ID', 'MT', 'OR', 'WY', 'CO', 'UT', 'NV', 'CA') xvi. 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

This command allowed me to see what regions had the most number of replacements. 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 think there are a lot of benefits using these queries to retrieve information that also provides valuable information to stakeholders because this data helps analyze what parts are needing replaced more often and this can help the fleet team manage their parts and better schedule. This data can also help them take preventative measures to maybe stop so many replacements happening. The team will also be able to see how each region affects the parts differently and that will help them know what regions need more parts and more preventative measures. 4. Explain how the functions in the analysis tool allowed you to organize the data and retrieve records quickly. The functions in the analysis tool helped me retrieve the data quickly and helped me stay organized. These functions helped me get a better understanding of maintenance patterns and made me realize what the maintenance team could do better.