7-1 DAD 220 Database Documentation 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

8

Uploaded by BaronKuduPerson693

Report
DAD 220 Database Documentation Template 1.Begin by writing SQL commands to  capture usable data  (which you’ve preloaded into Codio) for your analysis.
Commands/Explanation: Chmod +x change_perm.sh ./change_perm.sh mysql USE Quantigrationupdates; These commands allowed me to start with the SQL command process. I then used the count(*) functions to verify the data was loaded into the tables accurately. 2.Specifically, the product manager wants you to analyze the following: o Analyze  the  number of returns   by state  and describe your findings in your report.
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
Commands/Explanation: SELECT Collaborators.State AS State, Count(*) AS Return_Number FROM Orders INNER JOIN RMA ON Orders.OrderID = RMA.OrderID INNER JOIN Collaborators ON Collaborators.CollaboratorID = Orders.CollaboratorID GROUP BY State ORDER BY Return_Number DESC LIMIT 15; In the analysis of returns by state in descending order, we can observe interesting patterns in the data displayed. When arranging the states by the highest number of returns, we can see the top five are Massachusetts, Arkansas, Oregon, West Virginia, and Alabama. This can suggest that these states have certain factors that contribute to the higher rate of return such as product quality or distribution issues.
Commands/Explanation: SELECT Collaborators.State AS State, Count(*) AS Return_Number FROM Orders INNER JOIN RMA ON Orders.OrderID = RMA.OrderID INNER JOIN Collaborators ON Collaborators.CollaboratorID = Orders.CollaboratorID GROUP BY State ORDER BY Return_Number ASC LIMIT 15; When arranging data in ascending order, the states with the lowest returns are displayed. South Carolina, New Jersey, Colorado, Georgia, and Nebraska are amount the states with the least number of returns. These results could imply that these states have better quality products, more satisfied customers, or better logistics. Further investigation or analysis would help us to understand the reasons behind the patterns we see displayed and could assist in improving customer experience or reducing returns.
o Analyze  the  percentage of returns by product type  and describe your findings in your report. Commands/Explanation SELECT SKU AS Product_SKU, Description AS Product_Description, COUNT(*) AS Total, (COUNT(*) / (SELECT COUNT(*) FROM Orders INNER JOIN RMA ON Orders.OrderID = RMA.OrderID) * 100) AS Percentages_of_Returns FROM Orders INNER JOIN RMA ON Orders.OrderID = RMA.OrderID WHERE UPPER (Status) = 'COMPLETE' GROUP BY Product_SKU ORDER BY Percentages_of_Returns
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
DESC LIMIT 15; In the analysis, we can see that the Basic Switch 10/100/1000 BaseT 48 port is the highest returned product with 7405 returned at a percentage of 19.71%. Enterprise Switch 40GigE SFP+ 48 port comes in second with 5470 returned at a percentage of 14.56%. Enterprise Switch 10GigE SFP+ 48 port comes in third with 3847 returned at a percentage of 10.24%. It would be valuable to investigate these top three returned products to see if there is a common failure in each or a common complaint amongst customers. These findings suggest there could be flaws within the design or functionality of these products. Investigating these products further could assist with lowering the number of returns for each and improving customer satisfaction with these products. 3.In your report, clearly  summarize your analysis of the data for stakeholders . Include screenshots of the results of each query. When summarizing results, you may want to consider the following questions: o How does the data provide the product manager with usable information? The data provided allows the product manager to see what states and products have the highest returns. Knowing this information tells the manager which states and products need to be looked at and investigated more closely to determine the issues that are present. If we dig deeper into the root issues being represented, we can put fixes into place to improve the overall product and experience for the customer. o What are the potential flaws in the data that has been presented?
One of the largest potential flaws within the data being presented is that the data could have been entered in incorrectly which would create incorrect data being displayed. The data presented doesn’t indicate a clear reason why products are being returned. We would need to run further analysis to determine the reasons behind why products are being returned. o Are there any limitations on your conclusions, or any other ways of looking at it that you haven’t considered? Clearly communicate your findings to stakeholders. There are limitations as we haven’t dived deep into the reasons behind the returns. We haven’t dived deeper into seeing if items are returned more in certain cities in the states with the highest returns or if the population is higher in those states. Further investigation is needed to get a better picture of why certain items are being returned at such a high rate. From the data that is presented, we can see that the state of Massachusetts has the highest number of returns. Further investigation could help us learn what is causing the high number of returns and could assist us with gathering a solution to put in place. We can do the same for the products that are returned at a high rate. 5 out of 9 of our products have a return percentage higher than 10%, which is considerably high for such a few products.