DAD 220 Analysis and Summary Template Murphy

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 BaronKuduPerson693

DAD 220 Analysis and Summary Template Commands: Use Murphy; - Changes database to database named after my last name 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)); - Creates the table titled Parts_Maintenance SHOW tables; - Verifies creation of Parts_Maintenance table I created the Parts_Maintenance table and included the table in the database named after my last name. Command: LOAD DATA INFILE '/home/codio/workspace/FleetMaintenanceRecords.csv' INTO TABLE Parts_Maintenance FIELDS TERMINATED BY',' LINES TERMINATED BY '\ r\n'; - Loaded the data from the csv file into the Parts_Maintenance table

1. Analyze the data you’ve been provided with to identify themes : a. Which parts are being replaced most? i. Based on my query results, the parts that are being replaced the most are the Fule tank (Fuel tank) with 95 repairs. Tire repair comes in second at 74 repairs and Tire replacement comes in third with 66 repairs. 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 of 260 repairs compared to the other regions. The query retrieves the count of part failures and replacements for each region from the “Parts_Maintenance” table by using the UNION operator to combine the results of the multiple SELECT statements representing each region. ii. How might the fleet maintenance team use the information to update its maintenance schedule?

1. The fleet maintenance team can use the information retrieved to see that it should allocate more resources to the Midwest region since it had the highest number of repairs. Allocating more resources to the region will ensure that repairs are done in a timely manner and that downtime is reduced. The team can use the information to manage their inventory of parts more efficiently by seeing what parts they should keep on hand. c. Which parts are being replaced most due to corrosion or rust? The part being replaced most due to corrosion or rust is the Wheel Arch with 55 repairs. The Fender replacement comes in second at 54 repairs and the Rocker Panel comes in third at 53 repairs. The query retrieves the parts that have experienced repairs due to rust or corrosion. The data is then displayed descending in a table with the columns PART_REPAIR and NUMBER_OF_REPAIRS. d. Which parts are being replaced most because of mechanical failure or accident, like a flat tire or rock through the windshield? The parts being repaired the most because a flat tire or rock through the window are tire repair at 74. Tire replacement comes in second at 66, and windshield replacement comes in third at 63. The query retrieved the parts that are being replaced the most because of mechanical failure or accident where the reason contains the word flat or crack. The data is then grouped by part repair and displayed in descending order based on the count of number of 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

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, we can see that the Midwest region has the most repairs with a total of 260 reported. We can also see that the most common type of repair was the fuel tank with a total of 95 repairs. The wheel arch, fender replacement, and rocker panel are replaced the most when it comes to rust or corrosion. The tire repair, tire replacement, and windshield replacement are replaced the most due to being flat or cracked. This data can help us to improve fleet maintenance by ensuring we are allocating resources appropriately. 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. To identify trends in the regions, I used the UNION operator to combine the results of my multiple SELECT statements into a single table. I used the LIKE operator with the % wild card for specific reasons for repairs to see the trend for repairs. 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. The queries used in this lab allow us to display data in a way that is easy to understand which allows stakeholders to make decisions based on specific data. Knowing the data helps to track trends and make decisions on ways to improve current processes. 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 me to organize and retrieve data quickly. Select function allowed me to retrieve data from specific fields. Group, order, and descending functions allowed for the data to be displayed in a clear and easy to read manner. The count function allowed for the total number to be displayed. The union function allowed for data to be combined from multiple queries. The like function with the % wild card allowed us to be able to search for similar repair names.