DAD 220 Analysis and Summary Template - Taylor

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 haleytaylor624

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. Updated permissions in Codio using chmod +x change_perm.sh and ./change_perm.sh from Module 3. Created table names PartsMaintence with the columns named VehicleID, State, Repair ,Reason, YEAR , Make and BodyType using the database taylor. CREATE TABLE PartsMaintence (VehicleID VARCHAR(30), State VARCHAR(2), Repair VARCHAR(50), Reason VARCHAR(50), YEAR INT, Make VARCHAR(30), BodyType VARCHAR(50));

Corrected Spelling of the table name. Loaded data from FleetMaintenanceRecords.csv into the table PartsMaintenance. LOAD DATA INFILE '/home/codio/workspace/FleetMaintenanceRecords.csv' INTO TABLE PartsMaintenance FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 ROWS;

1. Analyze the data you’ve been provided with to identify themes : a. Which parts are being replaced most? The part that is being replaced the most is the Fuel Tank with a total of 95 total repairs. The second highest replaced part is Tire replacement at 66 and then Windshield replacement at 63. The list goes on in descending order to show the highest number of repairs first and the lowest as the last. Located information using the command: SELECT Repair AS PART_REPAIR, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance 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: The region with the most part failures and replacements is the Midwest with a total of 234. This information was located by using the UNION function to join several SELECT statements specific for each region. The list goes on in descending order to show the highest number of repairs per region first and the lowest as the last. Located information using the command: SELECT 'Southwest' AS REGION, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance WHERE State IN ('AZ', 'NM', 'TX', 'OK') UNION SELECT 'Southeast' AS REGION, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance WHERE State IN ('AK', 'LA', 'MS', 'AL', 'GA', 'FL', 'KY', 'TN', 'SC', 'NC', 'VA', 'WV', 'DE', 'MD') UNION SELECT 'Northeast' AS REGION, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance WHERE State IN ('PA', 'NJ', 'NY', 'CT', 'RI', 'MA', 'VT', 'NH', 'ME') UNION SELECT 'Midwest' AS

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

REGION, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance WHERE State IN ('ND', 'SD', 'KS', 'NE', 'WI', 'IA', 'MO', 'MI', 'IN', 'IL', 'OH') UNION SELECT 'West' AS REGION, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance WHERE State IN ('WA', 'ID', 'MT', 'OR', 'WY', 'CO', 'UT', 'NV', 'CA') ORDER BY NUMBER_OF_REPAIRS DESC; ii. How might the fleet maintenance team use the information to update its maintenance schedule? The fleet maintenance team can use this information to be more organized, increase efficiency and improve their time management. Since the Midwest region has the highest number of repairs needed, the team can properly staff this area as well as inventory more parts for the replacements. They can use the data from the other regions as well for those regions that may have a number of lower repairs. c. Which parts are being replaced most due to corrosion or rust? The parts that need replaced due to corrosion or rust are the Wheel Arch with 55 repairs as the highest. The other parts include Fenders at 54, Rocker Panels at 53, Brake Lines at 52, Struts at 51, Cab Corner Panels at 49, Shocks at 47 and Fuel Tanks at 46. Located information using the command: SELECT Repair AS PART_REPAIR, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance WHERE 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? Tires and windshields are being replaced the most. This was found by using the words “flat” and crack” as wildcards within the code. There was a total of 74 Tire Repairs, 66 Tire replacements and 63 Windshield replacements. Located information using the command: SELECT Repair AS PART_REPAIR, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance WHERE Reason LIKE '%flat%' or 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. Upon review of the information provided I have been able to identify some trends that will help gain overall efficiency within the company. First, the highest number of repairs are being made on Fuel Tanks with a total of 95 repairs. This may require some further research to determine why this is the most replaced or repaired part within your fleet. Secondly, the Midwest region will require more staff and inventory as it is the highest region with the number of repairs needed. This region accounts for roughly 31% of overall repairs, with the Northeast regions coming in at a close 2 nd at roughly 28%. Third, There are a large amount of parts that need replaced due to corrosion or rust. The parts that are all impacted are the following: wheel arch with 55 repairs as the highest. The other parts include fenders at 54, rocker panels at 53, brake lines at 52, struts at 51, cab corner Panels at 49, shocks at 47 and fuel tanks at 46. As you can see, these parts are all relatively close in

number and may require preventive measures to help reduce the formation of rust and corrosion. Lastly, accidental situations such as flat tires or cracked windshields have a large impact on repairs. It has been identified that there have been 74 tire repairs, 66 tire replacements and 63 windshield replacements. The company may want to invest in a higher grade tire as well as windshield product in effort to reduce the number of repairs. 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? Using the PartsMaintenance Table several queries were used to identify the trends in the dataset. The first query was to locate the number of parts replaces identifying the most replaced parts. The query used was: SELECT Repair AS PART_REPAIR, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance GROUP BY PART_REPAIR ORDER BY NUMBER_OF_REPAIRS DESC; This query selects the attribute in the repair column and counts the number of times it is in the PartsMaintenance table. The query groups the information by the number of repairs and lists them in descending order from highest to lowest. The second query was to identify which region had the highest number of repairs. The query used was: SELECT 'Southwest' AS REGION, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance WHERE State IN ('AZ', 'NM', 'TX', 'OK') UNION SELECT 'Southeast' AS REGION, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance WHERE State IN ('AK', 'LA', 'MS', 'AL', 'GA', 'FL', 'KY', 'TN', 'SC', 'NC', 'VA', 'WV', 'DE', 'MD') UNION SELECT 'Northeast' AS REGION, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance WHERE State IN ('PA', 'NJ', 'NY', 'CT', 'RI', 'MA', 'VT', 'NH', 'ME') UNION SELECT 'Midwest' AS REGION, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance WHERE State IN ('ND', 'SD', 'KS', 'NE', 'WI', 'IA', 'MO', 'MI', 'IN', 'IL', 'OH') UNION SELECT 'West' AS REGION, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance WHERE State IN ('WA', 'ID', 'MT', 'OR', 'WY', 'CO', 'UT', 'NV', 'CA') ORDER BY NUMBER_OF_REPAIRS DESC This query selected the region and counted the number of repairs in the states identified within the region. Since there were a total of five regions, each query was joined by using the UNION function. This allows all the information to be presented within one table, also listed in descending order from highest to lowest. The third query was to identify parts are being replaced most because of mechanical failure or accident, like a flat tire or rock through the windshield. The query used was:

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 PART_REPAIR, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance WHERE Reason IN ('corrosion', 'rust') GROUP BY PART_REPAIR ORDER BY NUMBER_OF_REPAIRS DESC; This query selects the part name but the count is only for those under the Reason column that equal “corrosion” or “rust. This query groups the information by the part name and lists in descending order from highest to lowest. The fourth query was to identify parts are being replaced most because of mechanical failure or accident, like a flat tire or rock through the windshield. The query used was: SELECT Repair AS PART_REPAIR, COUNT(*) AS NUMBER_OF_REPAIRS FROM PartsMaintenance WHERE Reason LIKE '%flat%' or Reason LIKE '%crack%' GROUP BY PART_REPAIR ORDER BY NUMBER_OF_REPAIRS DESC; This query selects the part name but the count is only for those under the Reason column that have “flat” or “crack”. Within this query, a wildcard function had to be included in order to locate the data. This query groups the information by the part name and lists in descending order from highest to lowest. 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? There are several benefits of using these types of queries to provide valuable information to stakeholders. The queries can identify trends within one’s company. In this exercise, the stakeholders were provided with information that can help save time and money if the information is used correctly. The trends were to locate the highest number of parts repairs as well as the region in which the highest number of repairs were made. It also identified the number of parts that were impacted by certain aspects that the company may have felt were out of their control, such as rust, corrosion, flat tires and cracked windshields. However, now that the company can see the actual numbers of these occurrences, they can choose to invest in higher quality parts that may help prevent these circumstances. 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 are imperative to organizing data with quick and accurate results. The SELECT function is used to select the specific data from the table for the information that was needed, which in this exercise was the part name. The GROUP BY functions is used to group the data by the data set names, in this exercise the information was grouped by the part names. The COUNT function calculated each of the part names within the database. The

UNION function was used to group several SELECT functions together in order to retrieve one result, in this exercise the information needed was the region information. This function allowed all of the information to be displayed as one table as opposed to several table making the data clean and easy to review.