DAD $220$ Module Five Activity Template

Complete these steps as you work through the directions for this activity. Refer to the guidelines and rubric for help with how to complete these steps. Rename this template by adding your last name to the file name. Replace the bracketed text in the template with your responses and supporting screenshots as you complete the activity. Size each screenshot and its explanation to fit approximately one$-$quarter of the page. Review the Template Screenshot Example linked in the guidelines and rubric for this assignment to see how screenshots for your assignment should look. Submit the completed template for grading and feedback.

$1.$ Analyze the data provided in FleetMaintenanceRecords to identify themes.

a$.$ Review part$-$replacement frequencies and types. Then create a hypothesis that the fleet management team can use to better handle maintenance.

i$.$ Create a table called Parts Maintenance. Put this table in the database named after yourself.

ii$.$ Load the data set from the $/$home$/$codio$/$workspace$$ path and run queries to find the results. You should use the following line terminators when importing:

$.$

iii. Answer the following questions and provide supporting screenshots.

a$)$ Which parts are being replaced most often?

b$)$ Which region or regions of the country experience more part failures and replacements than others?

a$.$ Identify the region or regions with more reasons for the replacement of parts.

b$.$ Use the Region Definitions sheet to identify states in each region.

$[$Insert your response and screenshot here.$]$

c$)$ How might the fleet maintenance team use the information to update its maintenance schedule?

$[$Insert your response and screenshot here.$]$

d$)$ Which parts are being replaced most often due to corrosion or rust?

$[$Insert your response here.$]$

e$)$ Which parts are being replaced most often because of mechanical failure or an accident like a flat tire or rock through the windshield?

$[$Insert your response here.$]$

$2.$ Write a brief summary of your analysis that takes the information from step one and presents it in a way that nontechnical stakeholders can understand. Write your response in paragraph form.

$[$Insert your response here.$]$

$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?

$[$Insert your response here.$]$

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?

$[$Insert your response here.$]$

$4.$ Explain how the functions in the analysis tool $($MySQL$)$ allowed you to organize the data and retrieve records quickly.

$[$Insert your response here.$]$

$----------------$

Please help! I have this so far..... not sure if it's correct..Thank you!!

CREATE TABLE PartsMaintenance $($

VehicleID VARCHAR$(30),$

Repair VARCHAR$(40),$

Reason VARCHAR$(40),$

Year INT,

Make VARCHAR$(20),$

BodyType VARCHAR$(50))$;

ALTER PartsMaintenance

ALTER TABLE PartsMaintenance

ADD COLUMN State VARCHAR$(2)$

AFTER VehicleID

$(($The table had to include state$))$

LOAD DATA INFILE $\text{'}/$home$/$codio$/$workspace$/$FleetMaintenanceRecords$.$csv$\text{'}$

INTO TABLE PartsMaintenance

FIELDS TERMINATED BY $\text{'},\text{'}$

LINES TERMINATED BY $\text{'}$

$\text{'}$

IGNORE $1$ ROWS;

SELECT REPLACE$($Repair$,$ 'replacement', $\text{'}\text{'})$ AS Part, COUNT$(*)$ AS PartCount

FROM PartsMaintenance

WHERE Repair LIKE $\text{'}\%$replacement$\%\text{'}$

GROUP BY Part;