DAD 220 Analysis and Summary Template JC

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 SuperFangCobra37

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.  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));  LOAD DATA INFILE '/home/codio/workspace/FleetMaintenanceRecords.csv' INTO TABLE Parts_Maintenance FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';  describe Parts_Maintenance; 1. Analyze the data you’ve been provided with to identify themes :

 SELECT repair AS PART_REPAIR, COUNT(*) AS NUMBER_OF_REPAIRS  FROM Parts_Maintenance  GROUP BY PART_REPAIR  ORDER BY NUMBER_OF_REPAIRS DESC; a. Which parts are being replaced most? i. The part that is being replaced the most is the Fule (fuel) tank. There are 95 repairs. b. Is there a region of the country that experiences more part failures and replacements than others?

 SELECT "Southwest" AS REGION, COUNT(*) AS NumOfRepairs FROM Parts_Maintenance WHERE State IN('AZ','NM','TX','OK');  SELECT "West" AS REGION, COUNT(*) AS NumOfRepairs FROM Parts_Maintenance WHERE State IN('WA','ID','MT','OR','WY','CO','UT','NV','CA');  SELECT "Midwest" AS REGION, COUNT(*) AS NumOfRepairs FROM Parts_Maintenance WHERE State IN('ND','SD','KS','NE','MN','WI','IA','MO','MI','IN','IL','OH');  SELECT "Northeast" AS REGION, COUNT(*) AS NumOfRepairs FROM Parts_Maintenance WHERE State IN('PA','NJ','NY','CT','RI','MA','VT','NH','ME');  SELECT "Southeast" AS REGION, COUNT(*) AS NumOfRepairs FROM Parts_Maintenance WHERE State IN('AR','LA','MS','AL','GA','FL','KY','TN','SC','NC','VA','WV','DE','MD'); i. Identify region: The Midwest region of the country experiences more part failures and replacements than others. ii. How might the fleet maintenance team use the information to update its maintenance schedule? The fleet maintenance team can use this data for employee distribution. They can schedule more employees and appointments for the Midwest because they have the highest part failures. They would not need as many scheduling hours for the Southwest part of the country because the least number of repairs occur there. c. Which parts are being replaced most due to corrosion or rust?  SELECT Repair AS Repaired, COUNT(Repair) AS Occurance FROM Parts_Maintenance WHERE Reason IN ('Corrosion', 'Rust') GROUP BY Repaired ORDER BY Occurance DESC; i. The parts being replaced most due to corrosion and rust are the Wheel Arch, Fenders for fender replacement, and the Rocker panel. d. Which parts are being replaced most because of mechanical failure or accident, like a flat tire or rock through the windshield?

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

 SELECT Repair AS Repaired, COUNT(Repair) AS Occurance FROM Parts_Maintenance WHERE Reason NOT IN (‘Corrosion’, ‘Rust’) GROUP BY Repaired ORDER BY Occurrence DESC; i. The parts that are being replaced the most because of mechanical failure or accident are Tires, Windshields, and Batteries. 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. I created a database using csv files to manage and organize data about various car repair information. I used queries to determine which parts are being replaced more often and also determining the region where the most car repairs took place. The data retrieved can be used to help the fleet maintenance teams. 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? First, I created a csv file. Then the MySQL functions to display the requested data in an organized matter. Then I created a new table to further manage the data so that I was able to present information in this activity. I used various queries to retrieve specific data like which region experiences more car repairs. I used SELECT functions to filter specific information to complete this analysis. 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 benefit of using these queries to retrieve the information in a way that allows you to provide valuable information to my stakeholders is the ability to easily manipulate and manage vast amounts of data. Using these queries to retrieve specific data is vital in business deals and transactions. 4. Explain how the functions in the analysis tool allowed you to organize the data and retrieve records quickly. a. Using MySQL functions like ORDER BY, COUNT(*), SELECT, AND WHERE allowed me to organize the data and retrieve the records quickly. Looking over my activity, I used the SELECT tool the most. SELECT is used when I need to organize and display specific data. WHERE is used to manage and filter data. It extracts specific data. The COUNT function literally counts the values according to the results populated by the query and retrieves the total number. The ORDER BY function returns results in order. This order can be ascending or descending order.