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 locksup, or in many instances is unavailable.
The board of directors has asked executive ITmanagement 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 theissue. 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 lockup the web servers. They suspect the queries were not designed for efficiency, as they were created before more individual's startedusing the online property tax payment system.
Produce an Action Plan Report that 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.