代写ISYS1101/1102 | Semester 2 2024 Assignment 1: Database Design and Optimisation代做数据库编程

Database Applications

ISYS1101/1102 | Semester 2 2024

Assignment 1: Database Design and Optimisation

Assessment Type	Database Design, Database Optimisation, Implementation, and Demonstration Individual Assessment
Due Date (M1)	Week 4, during the lab sessions. Further details will be provided on Canvas.
Due Date (M2)	23:59 Sunday 01 September 2024
Demonstrations (M3)	Week 7 - 8, during the lab sessions. Further details will be provided on Canvas.
Silence Period (M1)	None
Silence Period (M2 & M3)	Starts at 5:00PM Friday 30 August 2024
Weight	Milestone 1: 5 marks Milestone 2 and 3 (marked together): 20 marks
Submission	Online, via Canvas. Submission instructions are provided on Canvas.

1 Overview

1.1 Assessment Criteria

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 convert your ER model into a relational database schema;

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 T-SQL procedures and functions to automate common tasks in an SQL Server database.

1.2 Learning Outcomes

This assessment will assess how you attained the following course learning outcomes:

CLO 1: apply advanced data analysis and modeling concepts, physical design, integrity, security and transaction management.

CLO 2: create stored procedures and functions to enhance the usability of a database;

CLO 3: apply techniques for efficient storing, accessing, securing, and recovering of data;

CLO 4: build an efficient database application with an emphasis on storage management, indexing, and query optimization

2 Assessment Details

2.1 Preparation Work

You are required to implement the database backend for the below mentioned application on Microsoft SQL server. In order to successfully complete these tasks you must have completed Week 1 – 4 lab sheets and ensure that your SQL Server account is correctly configured and Azure Data Studio on your laptop is configured correctly.

2.2 Assignment Task Description

Introduction

The Australian Electoral Commission (AEC – https://aec.gov.au) 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 many 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, which is in the scope of this assignment) and upper house (the senate, which not part of this assignment). There are currently 151 seats in the lower house, and 76 seats in the upper house.

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 Electoral Division using full preferential voting. Each Electoral Division elects one member.

Electoral Divisions

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 electoral divisions. To ensure continued equal representation, the boundaries of these divisions have to be redrawn

(redistributed) periodically. As of last re-distribution based on 2017 population data, there are 151 electoral divisions in Australia.

State	Population (in 2017)	Electoral Divisions
NSW	7,797,791	47
VIC	6,244,227	38
QLD	4,883,739	30
WA	2,567,788	16

SA	1,716,966	10
TAS	519,050	5
ACT	419,256	3
NT	247,512	2
Total	24,396,329	151

Election Process

When the sitting government is nearing its term (3 years) or under circumstances it is dissolved, the

Australian Electoral Commission (AEC) calls for nominations of candidates. Registered political parties will then nominate their candidates for one or more electoral divisions. Most political parties nominate candidates for many electoral divisions. Independent candidates can nominate themselves for the

election.

Once the nomination process is over, AEC will determine the election date and will print ballot papers for each electoral division. A sample ballot paper is shown below. A screenshot of a real ballot paper   (Higgins electoral division in 2016 election) is also shown.

On the election date, registered voters are required to attend a polling station and cast their vote on a ballot paper similar to above. The actual voting process is much more flexible with pre-poll voting,

postal voting, absentee voting, and declaration votes. However, for the scope of this assignment, we only consider regular voting process on election day.

The preferential voting system

Candidates for the house of representatives are elected using the preferential voting system. In this

system, the voters are required to cast their order of preferences to ALL candidates contesting in their electoral division. As shown in the above ballot paper, the voter has given first preference to the fifth

candidate on the ballot paper. The voter has given their second preference to the first candidate on the ballot paper, and so forth.

At the end of the election day, after all the polling stations are closed, the counting begins. The counting of preferential votes is a complex process.

Step 1: Count of first preferences (primary vote)

In this step, all of the number “1” votes are counted for each candidate. If a candidate gets more than half the total first preference votes, that candidate will be elected.

Step 2: Distribution of preferences

If no candidate has more than half of the votes, the candidate with the fewest votes is excluded. This  candidate’s votes are transferred to the candidates according to the second preferences of the voters on the ballot papers for the excluded candidate. If still no candidate has more than half the votes, the second-last candidate who now has the fewest votes are excluded and the votes are transferred

according to the next preference on the ballot papers. This process is continued until one candidate has more than half the total number of valid votes.

This process is illustrated with a real example (distribution of preferences in Aston electoral division in

2019 election) on the page 32 of the following document.

https://www.aec.gov.au/about_aec/Publications/electoral_pocketbook/2019/2019-electoral- pocketbook.pdf

A screen shot of the above-mentioned page:

At the end of this two-stage counting process, the winning candidate is declared as the new Member of Parliament for the corresponding electoral division.

Components of the proposed system.

The proposed computerised election system must be able to conduct the entire election process for federal house of representatives elections. The major tasks in the election process are listed below:

1.    Maintenance of electoral role

2.    Maintenance of information required for conduct of an election (such as basic election

information, electoral division information, political party information, candidate information, etc. Refer to details below.

3.    Election Day – This process should mimic the manual process where a voter visits a polling

information, once identification is established a ballot paper issued, marks their preferences and lodges the ballot paper.

4.    Counting of ballot papers (counting is a complex process, refer to details below).

This system will maintain the following information.

1. Computerised Electoral Role

The system will maintain a computerised electoral role, i.e. a database of registered voters for each electoral division. For each registered voter, following information is stored:

•    Title

•    First name*

•    Middle names (if any)

•    Last name*

•    Gender

•    Date of Birth*

•    Residential Address* (Unit number, street number, street name, suburb, postcode, state)

•    (no letter box addresses accepted)

•    Postal Address (as above, or can be different)

•    Contact Details (daytime phone number, mobile phone number, email address)

•    Electoral Division (determined by the system based on residential address)

There are around 17,259,000 Australians are currently enrolled to vote

(https://www.aec.gov.au/Enrolling_to_vote/Enrolment_stats/index.htm). The increase of the size of the

electoral role is approximately proportional to the population growth in Australia. The current population growth in Australia is approximately 1.2%.

(https://population.gov.au/sites/population.gov.au/files/2022-04/2022-23_budget_overview.pdf)

2. Details of Elections

The following details about elections are required to be stored in the database.

•    Election Serial Number (a unique code generated and stored by the system)

•    Date of the election

•    Type of election (house of representative, senate, by-election, etc)

•    Total number of electoral divisions

•    Total number of registered voters (the number of registered voters at the closing of the electoral role registrations for the corresponding election)

3. Details of Electoral Divisions

The following details about electoral divisions are required to be stored in the database.

•    Electoral Division Name (refer to page 156 – 158 of

https://www.aec.gov.au/about_aec/Publications/electoral_pocketbook/2019/2019-electoral- pocketbook.pdf) for full list

•    Total number of currently registered voters

•     Historical record of registered voters (the historical data are captured at closing date of the  electoral role registrations for the past elections. Both the date and no. of voters are stored)

•    Name and party of the current member of parliament

4. Details of Political Parties

The following details about political parties are required to be stored in the database.

•    Party Code (refer to page 160 of

https://www.aec.gov.au/about_aec/Publications/electoral_pocketbook/2019/2019-electoral- pocketbook.pdf)

•    Name of the party

•    Party Logo

•    Postal address of the party headquarters

•    Secretary of the party

•    Contact Person (name and other contact details such as daytime phone number, mobile, and email)

5. Details of Candidates

The following details about political parties are required to be stored in the database.

•    Name

•    Political Party Code (or IND, if they are independent)

•    Contact Details name and other contact details such as daytime phone number, mobile, and email)

•    Election Code

•    Electoral Division Contesting

6. Computerised Ballot papers cast

The computerised ballot paper captures and stores voters’ preferences (similar to what’s written on a paper-based ballot paper.

[Very important] To ensure integrity and confidentiality of the voting process, once a voter is issued a computerised ballot paper, there should not have any identification records to positively identify who cast that vote. As such, only the following data are stored with each computerised ballot paper.

•    Election Code

•    Electoral Division

•    Preferences cast (i.e which candidate got the first preference, who got the second preference, etc)

However, there must be a mechanism in place to record the issuance of a ballot paper to a voter. The issuance record must capture the following information:

•    Election code

•    Electoral Division

•    Polling Station Name

•    Identifying details of the voter – these data should be sufficient to uniquely refer to a voter in the electoral role

•    Timestamp

7. Election results

At the end of counting process, for each electoral division, the following result data are stored.

•    Election Code

•    Electoral Division

•    Primary vote for each candidate (i.e. first preferences)

•    Preferential vote count for each candidate, at the each iteration of elimination process (refer to page 32 of https://www.aec.gov.au/about_aec/Publications/electoral_pocketbook/2019/2019- electoral-pocketbook.pdf)