Identify the tables, in your final schema, that are expected to be extremely large and are...

   

Transcribed Image Text:

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. Write SQL queries for the following tasks: 1. Assume that the total number of voters column (in the Electorate table) is empty. This data is to be computed by aggregating data from Voter Registry. Write an SQL query to display the total number of voters registered in each of the electorate. Your query should produce a report consisting of the electorate name and total number of voters only. The result should be displayed in the descending order of the total number of voters. A sample result is shown below: RMIT Classification: TrustedRMIT Classification: Trusted Division MACARTHUR PATERSON MAYO ADELAIDE COWPER SPENCE STURT LONGMAN HINDMARSH BOOTHBY Electorate Adelaide Adelaide Adelaide Adelaide Adelaide Adelaide Adelaide 2. The names of candidates appear on the ballot paper in a totally randomized order, i.e. no political party or a group gets any advantage of having their candidates on the top of the list, or candidates with names starting A always appear at the top, etc. Write an SQL query to produce candidate lists for all electorates for the 2022 federal election (election event id: 20220521). The result set must be sorted by electorate, and then candidates within each electorate must be randomized. A sample result is shown below: Aston Aston Aston Aston Aston Aston Aston Ballarat Ballarat Ballarat Ballarat Ballarat Ballarat Ballarat Ballarat Electors on 20 C 133,501 132,123 130,767 130,151 129,887 129,243 129,151 129,110 . 128,814 128,766 . Candidate Name GRANTHAM, Amy GEORGANAS, Steve ALLWOOD, Sean MCMILLAN, Matthew GALDIES, Rebecca GERHARD, Faith ALLWOOD, Gayle SPELMAN, Rebekah Jane TUDGE, Alan COOKSON, Asher BRUCE, Ryan IBBOTSON, Craig ROCHE, Liam DOYLE, Mary BARNES, John GREEN, Ben GRAHAM, Alex SEDGMAN, Kerryn Political Party Liberal *** Australian Labor Party United Australia Party FUSION: Science, Pirate, Secular, Climate Emergency The Greens Australian Federation Party Pauline Hanson's One Nation United Australia Party Liberal The Greens TNL Pauline Hanson's One Nation Liberal Democrats Australian Labor Party The Greens Liberal Independent Australian Federation Party PRYSE-SMITH, Terri Elizabet United Australia Party TAXIS, Rosale MCGRATH, Julia KING, Catherine Pauline Hanson's One Nation 3. Registered voters who do not vote at an election receive a penalty (typically, a fine). Write an SQL query, using IN or NOT IN clause, to generate a report that lists the names and addresses of registered voters who did not vote in 2022 general election (election event id: 20220521) and also not voted in 2019 general election (election event id: 20190518). For each of the queries: Liberal Democrats Australian Labor Party Produce the SQL query, but do not include the results set. 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. RMIT Classification: Trusted RMIT Classification: Trusted 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). TASK 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). TASK 4: 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 stored function - previouslyvoted (), to check if the voter had voted before. This function reads the election code, electorate, voter identification as inputs and returns a Boolean value (true, if voted before and false, if not voted before). TASK 5: 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. TASK 6: 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. 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. Write SQL queries for the following tasks: 1. Assume that the total number of voters column (in the Electorate table) is empty. This data is to be computed by aggregating data from Voter Registry. Write an SQL query to display the total number of voters registered in each of the electorate. Your query should produce a report consisting of the electorate name and total number of voters only. The result should be displayed in the descending order of the total number of voters. A sample result is shown below: RMIT Classification: TrustedRMIT Classification: Trusted Division MACARTHUR PATERSON MAYO ADELAIDE COWPER SPENCE STURT LONGMAN HINDMARSH BOOTHBY Electorate Adelaide Adelaide Adelaide Adelaide Adelaide Adelaide Adelaide 2. The names of candidates appear on the ballot paper in a totally randomized order, i.e. no political party or a group gets any advantage of having their candidates on the top of the list, or candidates with names starting A always appear at the top, etc. Write an SQL query to produce candidate lists for all electorates for the 2022 federal election (election event id: 20220521). The result set must be sorted by electorate, and then candidates within each electorate must be randomized. A sample result is shown below: Aston Aston Aston Aston Aston Aston Aston Ballarat Ballarat Ballarat Ballarat Ballarat Ballarat Ballarat Ballarat Electors on 20 C 133,501 132,123 130,767 130,151 129,887 129,243 129,151 129,110 . 128,814 128,766 . Candidate Name GRANTHAM, Amy GEORGANAS, Steve ALLWOOD, Sean MCMILLAN, Matthew GALDIES, Rebecca GERHARD, Faith ALLWOOD, Gayle SPELMAN, Rebekah Jane TUDGE, Alan COOKSON, Asher BRUCE, Ryan IBBOTSON, Craig ROCHE, Liam DOYLE, Mary BARNES, John GREEN, Ben GRAHAM, Alex SEDGMAN, Kerryn Political Party Liberal *** Australian Labor Party United Australia Party FUSION: Science, Pirate, Secular, Climate Emergency The Greens Australian Federation Party Pauline Hanson's One Nation United Australia Party Liberal The Greens TNL Pauline Hanson's One Nation Liberal Democrats Australian Labor Party The Greens Liberal Independent Australian Federation Party PRYSE-SMITH, Terri Elizabet United Australia Party TAXIS, Rosale MCGRATH, Julia KING, Catherine Pauline Hanson's One Nation 3. Registered voters who do not vote at an election receive a penalty (typically, a fine). Write an SQL query, using IN or NOT IN clause, to generate a report that lists the names and addresses of registered voters who did not vote in 2022 general election (election event id: 20220521) and also not voted in 2019 general election (election event id: 20190518). For each of the queries: Liberal Democrats Australian Labor Party Produce the SQL query, but do not include the results set. 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. RMIT Classification: Trusted RMIT Classification: Trusted 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). TASK 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). TASK 4: 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 stored function - previouslyvoted (), to check if the voter had voted before. This function reads the election code, electorate, voter identification as inputs and returns a Boolean value (true, if voted before and false, if not voted before). TASK 5: 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. TASK 6: 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.