DAD 220 Final Project Guidelines and Rubric Overview

DAD 220 Final Project Guidelines and Rubric Overview The final project for this course is to understand an existing database, write queries against it, and eventually alter its design and data. This will provide you with real-world practice for something that you will be asked to do countless times as a developer. This assessment will assess your mastery with respect to the following course outcomes: • Ensure the integrity and functionality of programs by identifying and correcting syntax errors in SQL statements • Apply proper SQL syntax in the retrieval, sorting, and restriction of data • Develop meaningful information by combining data from multiple sources using SQL syntax and logical data manipulation and management methods • Perform accurate calculations by aggregating data and applying SQL summary functions • Assess the functionality of embedded SQL, functional calls, and stored procedures within various scenarios • Articulate the fundamental concepts of SQL application within data analytics to a variety of audiences Prompt When Bill James began writing books on advanced baseball statistics in the late 1970s, nobody realized what a profound effect it would have on the game of baseball in the future. After the Oakland Athletics employed their “Moneyball” strategy in the late 1990s based on James’s advanced sabermetrics, analyzing player statistics would become commonplace across all professional teams. You have been hired to intern for a professional baseball organization. You will need to quickly come up to speed with their existing database, generate the code to recreate it in another location, run reports for upper management, and eventually alter it to store additional data. As you start working on the database, you will find that there is not a backup being stored anymore. This would be extremely problematic if the database were to crash, and there is an immediate need to create the database in another location and populate it with the existing data. This would be an easy job if there were documentation from the original database creator, but alas, there is not. Once the housekeeping is done, the executives will start requesting data to help them assess players and evaluate trades. You will need to give them easy-to-read reports that will keep their team competitive, which will in turn keep the money rolling in. Meanwhile, the season is going on, and more data is flowing in, so we will need to insert it into the appropriate tables and provide reports across both existing and new data—the life of the statistician is never easy! And along with new data comes new types of data—as new ideas are thought up, those executives will want reports on that data, whether it exists or not. And at the end of the day, you will find that some reports will be ideal to run frequently—as the data changes, they will want daily snapshots of it. So you will develop stored procedures to run the reports for them, producing output that can one day be added to programs they could access from their smartphones. Guidelines for Submission For the final project submission, create a document that describes your role as the intern. The document should be the collection (and necessary analysis and/or revisions) of Milestones One through Seven. The document will need to include the following pieces: I. Introduction a) Describe the issue you will address and the strategy for accomplishing the task. b) Using an entity relationship diagram (ERD), identify the data and links (keys) for all the tables in the final version of the sbl database in your personal profile. II. Tables a) Include the following tables with appropriate annotated SQL syntax: i. From Milestone Four: 1. Provide the teamnum and playernum for all players with a battingavg over 0.25 2. Provide the teamnum for all teams that use Acme bats 3. Provide the name and age of each player, sorted in ascending order by age 4. Provide the name of the manager and coach associated with each team, sorted ascending by the team number (one row per team) ii. From Milestone Five: 1. Provide the name, age, and team name (not team number) for each player, sorted ascending by the team name and then by age 2. Provide the team name, player name, and player batting average for all players whose batting average is above 0.230, sorted by batting average ascending 3. Provide the name of the coach, current team name, and total number of years of experience each coach has (which will be the sum of all the years of experience the coach has in all of his positions) 4. Provide the name of the manager and coach associated with each team, sorted ascending by the team name 5. Provide the team name, name of all players or coaches or managers, and the person’s role (player, manager, or coach) for all of the teams, sorting ascending by team name 6. Provide the number of each player on each team, the name of the player, the team name, and the number of years the player has been playing, sorted by player number iii. From Milestone Six: Provide an output of each player name, team, and bat manufacturer. Make sure the team number in the BATS table and AFFILIATION table correspond to each other in this query. III. Final Submission a) Validate the results b) Assess how the output addresses the needs c) Consider alternatives d) Cite sources Technical Specification Prompt Specifically, the following critical elements must be addressed: I. Introduction: a) Purpose: What is the issue that you intend to address, and how is it important to the given scenario? What data do you have available to you? (i.e., which keys will link the tables, ERD) b) Task Strategy: Outline the steps you will take to accomplish your task. II. Annotated SQL Syntax Code: a) For each table required, create appropriate SQL syntax to manipulate the table and make calculations necessary to the task at hand. This should include pre-processing restrictions (WHERE clause), aggregations (GROUP BY clause), and post-processing restrictions (HAVING clause). Note that some tables might be used as-is. b) For each table required, create SQL syntax for linking the tables using an appropriate type of join. c) Using the syntax developed in your SQL strategy, load the process onto the available platform. Test the process and perform any necessary debugging to resolve syntax errors. d) If applicable to the platform, embed the final SQL code into the broader (non-SQL) programming environment (e.g., SQL code might be embedded in a web interface and called by another language such as C# or PHP). If embedding is not deemed appropriate, provide a full explanation. e) Stored Procedures: Determine possible stored procedures from identified commonalities. Develop these stored procedures, using “CREATE PROCEDURE,” and insert the calls to these procedures into the existing SQL process. f) Thoroughly annotate the SQL code with comments to assist future users in understanding the steps/logic you followed. III. Results and Alterations: a) Output Validation: Do the results make sense (have “face validity”)? Are there independent methods you could employ to check their accuracy? b) Assess the results/output of the SQL program you have written. How well do the output reports/tables address the needs of the task? c) Consider alternative uses for your developed procedure. i. Discuss what minor alteration would be necessary in order to adapt your procedure to other issues. ii. Discuss what generalizations of procedure would be necessary to adapt your code to meet the basic needs of a variety of applications. d) Resources: Cite the resources you utilized in developing your code and specification document. Final Project Rubric Instructor Feedback: This activity uses an integrated rubric in Blackboard. Students can view instructor feedback in the Grade Center. For more information, review these instructions. Critical Elements Exemplary (100%) Proficient (85%) Needs Improvement (55%) Not Evident (0%) Value Purpose Meets “Proficient” criteria, and illustration is comprehensive enough to be understood by end users with a variety of data experience Fully analyzes the problem at hand, determines the data and relationships that have been given, and explains the purpose of the technical specification document Illustrates the problem and data given in the scenario and explains the purpose of the technical specification document, but problem and relationships are not fully analyzed Does not illustrate the problem and data given in the scenario or explain the purpose of the technical specification document. Entity relationship diagrams are missing 7 Task Strategy Meets “Proficient” criteria, and the logical breakdown is discrete and comprehensive Task at hand is described and broken down into a logical sequence of steps that will guide the development of a meaningful solution Task is generally described, but it is not broken down into sequential logical steps relevant to developing a solution to the given problem Task is not described or broken down into steps 6 Manipulation of Tables Meet “Proficient” criteria, and SQL code is accurate and workable in approach SQL codes are complete and appropriate for the purpose of manipulating tables through restriction, aggregation, and sorting of data SQL codes are complete, but chosen syntax may not be the most appropriate for the given purpose SQL codes are incomplete or syntax is misaligned with the given purpose 8 Linking of Tables Meets “Proficient” criteria, and syntax and process utilized are most appropriate and concise for the given task SQL code accurately links tables to form the final report/output tables through logical syntax SQL code links tables to form the final report, but the syntax and process are not logical SQL code fails to link tables to form the final report/output tables 6 Linear Syntax Combination Meets “Proficient” criteria, and the concise syntax provides evidence of a keen insight into complexities of syntax merging SQL code created is merged to form a single, coherent process, combining table manipulations and joins where appropriate SQL code created is merged to form a single, coherent process, but combinations and joins are not appropriate SQL code is not merged to form a single, coherent process 7 Syntax Upload and Test Meets “Proficient” criteria, and corrections made evidence a thorough understanding of SQL syntax creation Successfully installs the SQL program created onto the chosen platform and performs a test run, and code log evidences the identification and correction of syntax errors to produce a working program Installs the SQL program onto the chosen platform for test run, but code log evidences incomplete or incorrect error corrections Does not install the code onto the SQL platform or run a test, or code log does not show identification of syntax errors and corrections 8 Embedded SQL Meets “Proficient” criteria, and explanation of embedded code provides a defense of why embedding is appropriate or inappropriate, if so deemed Code is successfully inserted into another software environment as an embedded SQL process, and full explanation is provided, or a full explanation of why it was not embedded is provided Attempt to embed of the SQL code into another software environment does not result in a completely workable process, or a full explanation of why no embedding was attempted is not provided SQL code is not inserted into another software environment and no explanation of process is provided 6 Stored Procedures Meets “Proficient” criteria, and developed stored procedures result in a streamlined overall program Similar and repetitive processes are successfully developed into stored procedures and reintroduced to the existing SQL program code where appropriate Similar and repetitive processes are developed into stored procedures and reintroduced to the existing SQL program code No stored procedures are developed and inserted into the existing program code 6 Annotation Meets “Proficient” criteria, and annotation clarity would allow other professionals to repeat the process and duplicate the code for other applications SQL code annotations describe the overall process and the individual steps logically and accurately and are clear to a variety of audiences SQL code annotations are logical and accurate, but are not clear to a variety of audiences SQL code annotations are not complete 8 Output Validation Meets “Proficient” criteria, and checks performed identified errors that impeded integrity of data but not functionality of program Proof of validity of output tables is evidenced by an explanation of the independent checks performed during the program development process and error correction Independent checks performed were not used as intended or errors found were not corrected No proof of checks and correction of identified errors is provided 8 Assessment of Results Meets “Proficient” criteria, and assessment is rich in detail and evidences thorough analysis of the scenario needs Includes full assessment of the utility and applicability of the results/outputs of the SQL program written Includes a limited assessment of the utility and applicability of the results/outputs of the SQL program written Does not include an assessment of the utility and applicability of the results/outputs of the SQL program written 8 Alternative Uses Meets “Proficient” criteria, and explanation of alternative uses is comprehensive in its inclusion of what adjustments would be necessary for successful application Fully expounds upon potential alternative uses for the program or parts of the program developed Potential alternative uses are not fully explained Submission does not consider possible alternative applications of the developed program 6 Resources Meets “Proficient” criteria, and resource citation is a relevant list of resources to influence future users Accurately cites all resources that influenced the completion of the SQL code and the creation of the technical specifications document Cites all resources that influenced the completion of the SQL code and the creation of the technical specifications document, with minor lapses in accuracy Does not cite all resources that influenced the completion of the SQL code and the creation of the technical specifications document 6 Articulation of Response Submission is free of errors related to citations, grammar, spelling, syntax, and organization and is presented in a professional and easy-to-read format Submission has no major errors related to citations, grammar, spelling, syntax, or organization Submission has major errors related to citations, grammar, spelling, syntax, or organization that negatively impact readability and articulation of main ideas Submission has critical errors related to citations, grammar, spelling, syntax, or organization that prevent understanding of ideas 10 Earned Total 100%