2023年11月25日

数据库代写｜ISYS1101/1102 Assignment 1: Database Design and Optimisation

这是一篇来自澳洲的关于数据库设计和优化的数据库代写

 

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;
  1. use an industry-standard ER modeling tool to draw the ER model and generate SQL DDL statements for generating table schemas;
  1. identify and implementing efficient storage strategies for extremely large tables
  2. identify and implementing efficient partition strategies for extremely large tables
  3. 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;
  1. write stored procedures and triggers to automate common tasks in a Oracle 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 triggers, 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 School’s Oracle server. In order to successfully complete these tasks you must have completed Week 1 – 4 lab sheets and ensure that your Oracle account is correctly initiated and SQL Developer on your laptop is configured correctly.

Another learning outcome of this assignment is to learn to use an industry-standard ER modeling tool to draw the ER model and generate SQL DDL statements for generating table schemas. We use Oracle SQL Developer tool for this purpose. Complete Part 2 of the Week 3 Tute/ Lab session prior to attempting the data modeling part of this assignment.

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 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
  1. 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.