Database Application Development.

IT 655 Final Project Guidelines and Rubric

Overview IT professionals are often tasked with optimizing, tuning, and resolving various errors associated with large commercial database systems. As the world becomes more and more focused on the collection and use of data for various purposes, there is a greater need for building database applications that serve numerous complex functions. This final assessment will provide a comprehensive overview of the concepts, techniques, and skills that you have acquired throughout the course. The ability to interface a database management system with other applications and even third-party services is a commonly performed task in this discipline. The scenario, presented below, provides a real-world situation in which such skills will need to be employed. You will review the scenario and create a presentation that addresses, in detail, the key issues pertaining to performance and database efficiency, while touching on recommendations for improvements. You will then create a more detailed plan for addressing the problems highlighted in the scenario.

Scenario The County of Everstone is a large county in a densely populated state in the United States. This county has a total of 34 incorporated cities within its boundaries. Overall, the county has a population exceeding 4 million residents. The county employs a proprietary database management system that is responsible for the county’s property tax assessment, parcel, and payment information. In a recent initiative, the county has been highly promoting the use of its online property tax payment and lookup information to reduce mailed payments and in-office visits. The marketing campaign for online property tax payments and information lookup has been highly successful. However, the county’s database systems have not sufficiently handled the increased traffic. As such, the web application available to residents often experiences high amounts of latency, frequently locks up, or in many instances is unavailable. The board of directors has asked executive IT management to look into the issue and provide a prompt resolution to the matter. A review of the hardware and software systems that the database management systems employ reveals that these systems “should” be able to take the capacity and load. Therefore, the IT management has ruled out the hardware systems as being the issue. Upon further investigation, a lead database systems administrator preliminarily reports that the issue appears to stem not from the hardware, but rather from a need to provide performance tuning on the database. The database design seems to be the culprit, since this database systems administrator noted large quantities of duplicated data, poorly written database queries, and triggers. Furthermore, the chief information officer (CIO) has also directed the web programmers and web developers to look at the web application for potential issues. The web development team has reported that while no design or programming issues were found with the web application, the stored procedures (queries) that it runs from the database server take long periods of time to run and frequently lock up the web servers. They suspect the queries were not designed for efficiency, as they were created before more individuals started using the online property tax payment system. The project is divided into two milestones, which will be submitted at various points throughout the course to scaffold learning and ensure quality final submissions. These milestones will be submitted in Modules Three and Six. The final product will be submitted in Module Nine.

In this assignment, you will demonstrate your mastery of the following course outcomes:

 Analyze database transactions to discover opportunities for performance tuning

 Design database queries for optimum performance that adhere to given requirements and industry best practices

 Resolve data duplication and redundancy issues using appropriate database normalization techniques

 Respond to run-time errors and optimize database processing successfully by manipulating system parameters

 Recommend specifications for advanced multi-user database applications that fit commercial database systems

Prompts Prompt I: Presentation for the Chief Information Officer (CIO) To address the concerns of the board of directors, the CIO has asked you, as the manager of the database systems team, to provide a presentation that can be delivered to the board of directors. Your presentation should address in detail the key issues pertaining to performance and database efficiency, while touching on recommendations for improvements. The format and tool used for your presentation must be approved by your instructor. It is recommended, however, that free tools with IT support be used, such as Jing, or more traditional tools such as PowerPoint (with accompanying presentation notes), since they will allow you to record yourself giving a presentation. Please review the Guidelines for Submission above the Final Presentation Rubric in this document for further information to guide you in creating your submission. Specifically, the following critical elements must be addressed during your presentation:

I. Performance Tuning: Describe what potential types of tuning could be done to enhance database performance, and how you determined this from your analysis of the provided information.

II. Query Design: Explain the application of query design and how queries can be made more efficient, in general, according to industry best practices. Your discussion should specifically touch upon, at minimum, issues relating to joins, sub-queries, and sorting.

III. Data Duplication and Redundancy: Provide an explanation of potential database normalization and other techniques that can be utilized to reduce data duplication and redundancy within the scenario organization. Describe areas where database redundancy could be occurring. Be sure to identify potential techniques that would improve the database within the context of the problem.

IV. Multiple-User Access Issues and Errors: a) Describe various applicable methods that will resolve record concurrency errors and issues as they occur in the scenario. b) Describe methods that could be used to enhance multi-user access within the context of the commercial database used in the scenario. Be sure

to defend your reasoning for recommending these methods.

Prompt II: Action Plan Report The second portion of the final project will assume that you successfully presented to the board of directors, convincing them to move forward with your project to improve the functionality of the database. As such, you are asked to develop an action plan that builds upon your presentation and provides more detailed steps and narrative as to what specifically should be done to address the various performance and latency issues experienced by the County of Everstone.

Your action plan will be in the form of an APA-style report, as is professionally acceptable. For further guidance in formatting and structure, please see the Guidelines for Submission located above the Final Action Plan Report Rubric. Your Action Plan Report should address the following critical elements:

I. Executive Overview: Provide a general, high-level overview outlining the key issues, high-level recommendations, and important considerations to remediate performance and latency concerns.

II. Performance Tuning a) Describe what types of tuning should be done to enhance database performance and how database transaction might be replicated to other

database servers. b) Defend your recommendations using relevant research and explaining how you formed your conclusions.

III. Query Design a) Explain the application of query design and how queries can be made more efficient, while attending to issues relating to joins, sub-queries, and

sorting. Additionally, you should explain issues that could arise from select, update, and delete queries. Be sure to cater your explanation to the specifics of the scenario case.

b) Consider how refining the selection criteria could enhance the efficiency of database queries. In other words, how would you propose attending to the issues relevant to queries highlighted in the scenario, and why?

c) Design database queries for optimum performance for the scenario, and defend your conclusions. IV. Data Duplication and Redundancy

a) Explain database normalization in terms of its use and purpose for the scenario. In other words, provide an explanation of database normalization and other techniques that can be utilized to reduce data duplication and redundancy within the scenario database.

b) Defend the selection of techniques explained as being potential solutions for data duplication and redundancy within the scenario database. V. Multiple-User Access Issues and Errors

a) Develop a strategy that will allow for troubleshooting run-time and application errors. Describe some common errors that are seen in commercial database systems and propose mitigation.

b) Describe what high-level methods could be used to enhance multi-user access within databases in general. Create a set of database design specifications that would provide for multi-user access for the property tax payment and information system. The design specifications should provide details on concurrency control, record locking, and user security, and they should also provide room for future database growth. Discuss the impact of these changes and what resources would be required.

Milestones Milestone One: Presentation for the Chief Information Officer (CIO) In Module Three, you will submit a Presentation for the Chief Information Officer (CIO). Your presentation should address in detail the key issues pertaining to performance and database efficiency, while touching on recommendations for improvements. The format and tool used for your presentation must be approved by your instructor. It is recommended, however, that free tools with IT support be used, such as Jing, or more traditional tools such as PowerPoint (with accompanying presentation notes), since they will allow you to record yourself giving a presentation. Please review the Guidelines for Submission in the Milestone One Rubric document for further information to guide you in creating your submission. This milestone will be graded using the Milestone One Rubric. Milestone Two: Action Plan Report In Module Six, you will submit an Action Plan Report. Develop an action plan that builds upon your presentation and provides more detailed steps and narrative as to what specifically should be done to address the various performance and latency issues experienced by the County of Everstone. Your action plan will be in the form of an APA-style report, as is professionally acceptable. Please review the Guidelines for Submission in the Milestone Two Rubric document for further information to guide you in creating your submission. This milestone will be graded using the Milestone Two Rubric. Final Submission: Finalized Presentation to the CIO and Action Plan Report In Module Nine, you will submit a Finalized Presentation to the CIO and Action Plan Report. It should be a complete, polished artifact containing all of the critical elements of the final product. It should reflect the incorporation of feedback gained throughout the course. The Finalized Presentation will be graded using the Final Presentation Rubric. The Action Plan Report will be graded using the Final Action Plan Report Rubric.

Final Presentation Rubric Guidelines for Submission: This presentation should be approximately 5–6 slides, not including reference and title slides. All references should be in APA format. For more information on creating effective presentations, click here. 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 (90%) Needs Improvement (70%) Not Evident (0%) Value

Performance Tuning Meets “Proficient” criteria and provides high-level examples to enhance description of performance tuning types

Describes types of tuning that would logically enhance database performance based on analysis of scenario information

Describes types of tuning, but types would not logically enhance database performance or types are not based on analysis of scenario information

Does not describe types of performance tuning

19

Query Design Meets “Proficient” criteria and explanation is supplemented with example queries to highlight best practices

Explains the application of query designs and how queries can be made more efficient according to industry best practices

Explains the application of query designs and how queries can be made more efficient, but not based on industry best practices

Does not explain the application of query design and how queries can be made more efficient

19

Data Duplication and Redundancy

Meets “Proficient” criteria and explanation is detailed with specific examples of techniques provided

Explains data duplication and redundancy issues that could be occurring and provides logical normalization and other techniques for improving the scenario database

Explains data duplication and redundancy issues that could be occurring and provides normalization and other techniques, but techniques would not logically improve the scenario database

Does not explain data duplication and redundancy issues that could be occurring and provide normalization and other techniques

19

Multiple-User Access Issues and Errors:

Concurrency

Meets “Proficient” criteria and methods described are detailed and discussed with regard to the types of issues occurring

Describes various applicable methods for solving record concurrency errors and issues as they occur in the scenario

Describes various methods for solving record concurrency errors and issues, but methods may not be applicable to the issues as they occur in the scenario

Does not describe various methods for solving record concurrency errors and issues

19

Multiple-User Access Issues and Errors:

Enhancement

Meets “Proficient” criteria and description of recommended methods is detailed to include specific improvements

Describes and defends recommended methods that can be used to enhance multi-user access within the context of the scenario, and defense includes support

Describes and defends recommended methods, but methods would not be useful in enhancing multi-user access within the context of the scenario or defense lacks clarity, detail, or support

Does not describe and defend recommended methods for enhancing multi-user access

19

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

5

Earned Total 100%

Final Action Plan Report Rubric Guidelines for Submission: Your report should be in APA format, following the organization, section inclusion, stylistic, and reference requirements for APA. For more information on creating successful APA reports, click here. 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 (90%) Needs Improvement (70%) Not Evident (0%) Value

Executive Overview Meets “Proficient” criteria and overview is detailed and precise so that it applies to both the scenario and issues common to the industry without unnecessary exclusion

Provides an accurate, high-level overview outlining key issues, high-level recommendations, and important considerations to remediate performance and latency concerns

Provides an overview, but overview is not high level, or does not accurately address key issues, high-level recommendations, and important considerations to remediate performances and latency concerns

Does not provide an overview including high-level recommendations and important considerations to remediate performances and latency concerns

8.7

Performance Tuning: Types

Meets “Proficient” criteria and clarity of recommendations is enhanced with specific examples and related cases

Makes clear, logical recommendations for performance tuning for the scenario with a detailed explanation of how database transaction might be replicated to other servers

Makes recommendations for performance tuning for the scenario with a detailed explanation of how database transaction might be replicated to other servers, but recommendations are not clear or are not logical

Does not make recommendations for performance tuning for the scenario with a detailed explanation of how database transaction might be replicated to other servers

8.7

Performance Tuning: Defend

Meets “Proficient” criteria and defense is qualified and supported using specific, relevant examples

Provides a thorough, logical, and supported defense for performance tuning recommendations

Provides a defense for performance tuning recommendations, but it is not thorough, not logical, or not supported

Does not provide a defense for performance tuning recommendations

8.7

Query Design: Explanation

Meets “Proficient” criteria and explanation is enhanced with clearly drawn connections between the scenario and best practice design techniques

Provides a detailed explanation of query design and improvement techniques as related to the context of the scenario

Provides an explanation of query design and improvement techniques, but it lacks detail or is not catered to the context of the scenario

Does not provide an explanation of query design and improvement techniques

8.7

Query Design: Efficiency

Meets “Proficient” criteria and recommendations are of a quality that would potentially enhance efficiency and could be implemented

Makes thorough and reasonable recommendations for attending to query issues highlighted in the scenario

Makes recommendations for attending to query issues highlighted in the scenario, but recommendations lack detail or reasonableness

Does not make recommendations for attending to query issues highlighted in the scenario

8.7

Query Design: Design and Defend

Meets “Proficient” criteria and clearly evidences the use of best practices in design as applied to the scenario

Designs and defends database queries that would optimize performance in accordance with the scenario requirements

Designs and defends database queries for optimum performances, but the queries would not successfully optimize performance according to the requirements of the scenario

Does not design and defend database queries for optimum performances

8.7

Data Duplication and Redundancy: Explain

Meets “Proficient” criteria and the explanation of purpose of normalization and other techniques evidences recognition of best practices for the scenario

Accurately explains the use and purpose of database normalizations and other potentially useful techniques within the context of the scenario

Explains the use and purpose of database normalizations and other potentially useful techniques, but explanation lacks detail or accuracy, or is not explained within the context of the scenario

Does not explain the use and purpose of database normalizations and other potentially useful techniques

8.7

Data Duplication and Redundancy: Defend

Meets “Proficient” criteria and supporting rationale is qualified with relevant, real-world examples

Provides a logical defense and supporting rationale for normalization and other techniques for mitigating duplication and redundancy within the scenario

Provides a defense and supporting rationale for normalization and other techniques, but defense lacks detail or has gaps in logic

Does not provide a defense and supporting rationale for normalization and other techniques

8.7

Multiple-User Access Issues and Errors:

Strategy

Meets “Proficient” criteria and quality of strategy is emphasized with supporting detail and cases evidencing best practices in developing multi-user strategy

Develops a multi-user strategy that will allow for troubleshooting run-time and application errors, specifically commonly occurring errors seen in commercial databases

Develops a multi-user strategy that will allow for troubleshooting, but strategy is not specific to those errors commonly seen in commercial databases

Does not develop a multi-user strategy that will allow for troubleshooting

8.7

Multiple-User Access Issues and Errors:

Methods

Meets “Proficient” criteria and description evidences keen insight into the best practices in enhancing multi-user access

Accurately describes what high- level methods could be used to enhance multi-user access within databases in general

Describes what high-level methods could be used to enhance multi-user access within databases in general, but with gaps in accuracy and detail

Does not describe which high-level methods could be used to enhance multi-user access within databases in general

8.7

Multiple-User Access Issues and Errors:

Specifications

Meets “Proficient” criteria and database design requirements comprehensively address all aspects of the scenario accurately

Creates and discusses the impact of a set of database design requirements that would provide multi-user access for tax payment and information systems; provides details on controls, record locking, user security, and database growth

Creates and discusses the impact of a set of database design requirements, but does not attend to one of the specified topics, or discussion of impact lacks detail or accuracy

Does not create and discuss the impact of a set of database design requirements

8.7

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

4.3

Earned Total 100%