Reference no: EM133189444

ISYS1101 Database Applications - RMIT University

Database Design, Database Optimisation, Implementation, and Demonstration

Individual Assessment
This assessment will determine your ability to:

1. analyse the requirements outlined in the problem description;
2. develop a conceptual model to assist you with the design of the database backend required for the system;
3. use an industry-standard ER modeling tool to draw the ER model and generate SQL DDL statements for generating table schemas;
4. identify and implementing efficient storage strategies for extremely large tables
5. identify and implementing efficient partition strategies for extremely large tables
6. write efficient queries on extremely large tables and describe the query plans that query optimiser would likely be using and explain how a cost-based query optimiser would execute such query plans;
7. write stored procedures and triggers to automate common tasks in a Oracle database

Learning Outcome 1: apply advanced data analysis and modeling concepts, physical design, integrity, security and transaction management.
Learning Outcome 2: create triggers, stored procedures and functions to enhance the usability of a database; CLO Learning Outcome 3: apply techniques for efficient storing, accessing, securing, and recovering of data;
Learning Outcome 4: build an efficient database application with an emphasis on storage management, indexing, and query optimization.

Assignment Task Description

Introduction
The Australian Electoral Commission (AEC) is responsible for providing the Australian people with an independent electoral service which meets their needs and encourages them to understand and participate in the electoral process. Australia's manual system of federal elections has one of the most complex and time-consuming counting operations in the world. While it can at times require patience, the federal election counting process delivers (1) integrity to the results, concentrating on (2) accuracy in a (3) highly transparent manner.

While manual process ensures these three key priorities, there are two areas of concern to may stake holders, namely:
1. The time it takes to count votes and the human resources required to complete the process within an acceptable time frame
2. The volume of papers it requires and the environmental impact of running a manual election.

Let's suppose you are employed by a software development company that just received a contract from AEC to build a computerised voting system for federal elections. As in the case with manual elections, the most important aspect of this system is to ensure the integrity of the voting system, accuracy, and transparency.

System requirements
The system is developed in several phases. The first phase, which you are responsible for, is limited to federal general elections for House of Representatives. The following voting processes are not in the scope of this phase:
1. Federal general elections for senate
2. Federal by-elections
3. State and territory elections
4. City council and shire council elections
5. Referendums
6. Any other election services provided by AEC

In this assignment, you are required to analyse the database requirements, design the database backend for the voting system, identify various database optimisations, and implement the system.
Elections
In Australia, federal elections are held every three years. In these elections, the voters get an opportunity to vote for both lower house (the house of representatives) and upper house (the senate). There are currently 151 seats in the lower house, and 76 seats in the senate.
As the scope of this assignment is limited to federal general elections for House of Representatives no further details are provided on the upper house (the senate) and how the elections are conducted for senate seats.
Members of the House of Representatives are elected by the voters registered in each electorate using full preferential voting. Each electorate elects one member.
Electorates
For the House of Representatives, each state and territory is divided into electoral divisions (or commonly known as electorates or seats). Population determines the number of electorates. To ensure continued equal representation, the boundaries of these electorates have to be redrawn (redistributed) periodically. As of last re-distribution based on 2017 population data, there are 151 electorates in Australia.

Assignment Tasks Milestone 1:
You are required to build the data model for this application using an Entity-Relationship diagram. This diagram should be developed on Oracle SQL Developer. This diagram should be of professional quality with sufficient details that any other database personnel should be able to comprehend.
Then, convert your data model into the physical database design and finally generate the DDL script to build the back-end database schema for the application. Ensure that primary keys and foreign keys are correctly identified.
In order to complete this milestone, you are required to demonstrate your workings and the final DDL script to your tutor during Week 5 lab sessions. It is very important to get it marked off by the tutor before you proceed to Milestone 2. If your design does not meet the business requirements, your tutor will allow you to make any amendments (only once) and present again for evaluation.

Milestone 2:
1) Identify the tables, in your final schema, that are expected to be extremely large and are expected to grow over time.
For each of these tables:
• Describe the expected record size (in bytes or kilobytes), the estimated initial table size, and estimated table size after 10 years of use.
• Describe a suitable storage strategy for such tables. Your answer must include the modified SQL DDL statements to define your chosen strategy.
2) Identify 3 common queries that would need to be run frequently against the database (at least 2 must include a join, and one must include a transaction with multiple steps).
For each of the queries:

• Produce the SQL to correctly produce the expected result.
• Identify what indexes would help. Identify the type of index and columns that are used to build these indexes (justify your design).
• Show the SQL commands for building these indexes in Oracle.
• Show the query execution plans both before the index is added and after adding the index.
• Explain how the index was utilised (or not) and why. What join algorithms were used? What changes would you need to make for the index to be properly utilised, or for a different join algorithm to be used instead? (Provide concrete details of the changes).

3) Describe a suitable partition strategy for extremely large tables you identified in step 1. Include details of the partitioning type and which columns/key should be used. You must include the SQL DDL statements used to implement your partition strategy.
You must justify your design decisions. Include details about which of the above queries it will improve the performance of and how it helps with concrete examples. (You must explain in clear terms - such as partition pruning, partition joins, and parallel SQL, applicable to each of these queries.)

(Note: While you will be able to test that there are no syntax errors, you may not be able to actually implement them on RMIT's Oracle servers due to permission restrictions).

5) Before a voter is allowed to vote, to ensure the integrity of the election system, the system should check if he/she had voted earlier on this election. Write a trigger - previouslyVoted(), to check if the voter had voted before.
This trigger reads the election code, electorate, voter identification as inputs and returns a Boolean value (true, if voted before and false, if not voted before).

6) Write a stored procedure - primaryVoteCount(), to complete the step 1 of the counting process. This stored procedure requires election code and electorate name as inputs. It will read Computerised Ballot Papers and does required processing, and update Election Results table with primary votes (first preferences) received by each candidate in chosen electorate in the chosen election.

7) Write a stored procedure - distributePreferences(), to complete the step 2 of the counting process. This stored procedure requires election code and electorate name as inputs. It will read Computerised Ballot Papers and does required processing, and update Election Results table with preference votes received by each candidate at each preference distribution in chosen electorate in the chosen election.
Milestone 3:

You are required to do a demo of your complete application hosted on the school's Oracle server. These demos will be conducted during the week of 5th September (Week 7). You should have some voter data and stored in the application and the tutor will test out the functionality by using a test bed of data.

Attachment:- Database Applications.rar