Assignment 1- Data model and database implementation
At the time of writing, the State of South Australia is currently in the middle of an election. The data model and implementation that is required of you is based on a concept of electronic voting and the reporting requirements that may be considered necessary. You are required to develop the relational database structure for the scenario outlined below and to implement it. Your implementation will be demonstrated through the creation and output of SQL statements to your system. The scenario is pro- vided in terms of a broad description and a fictional dialogue between a systems analyst and the customer.
You can use any DBMS you wish but I suggest either SQlite or mySQL - it's up to you. Some particular notes -
1. It is deliberately a little vague, (this is the way it often occurs) and thus there is a requirement to
state your assumptions.
2. Any changes/clarifications to the specification will be posted on the FLO page for the topic and mentioned at the next lecture.
Scenario
The Electoral Commission for the newly created State of Central Australia needs to develop a system for entering and reporting elections. The system needs to handle aspects such as voter enrolment, candidate enrolment, polling day itself and the post-election analysis. The electoral system in Central Australia will have two houses, a lower House of Representatives and an upper house - The Senate. They have different voting procedures.
One of the important aspects of an election system is the privacy of people's votes - even from the Electoral Commission officers. Thus your implementation must contain an explanation of how security will be enforced - both in the data model construction and in the DBMS use.
Submission Components
The following is required to be provided
1. A full Entity-Relationship Diagram of your model.
2. A populated relational database schema of the system (as shown by appropriate SQL CREATE TABLE, INSERT and SELECT statements).
3. All assumptions clearly explained.
4. An explanation of how your design and implementation might enhance the security arrangements. (You don't have to implement the security - just describe how it might occur).
5. The SQL statements (and output) corresponding to the questions listed.
Dialogue
This fictional dialogue is between the Systems Analyst (SA) and the Administrative Officer (AO) of the Eastern Australian Electoral Commission.
SA Thank you for sparing the time this morning. We have a few questions we would like to ask regarding the proposed electoral system.
AO No problems.
SA How many candidates are there in an election?
AO Oh, any number. Some have party affiliation but we get a lot of independents so the ballot paper can get quite large, particularly in the upper house.
SA So you need the candidate's party, if they have one, to be recorded?
AO Yes. And remember that in the upper house, a party may have many candidates standing in an electorate while in the lower house a party only has one candidate in an electorate.
SA How many electorates are there? Are they the same for the two houses?
AO The number of electorates varies. We try and have the same number of voters - about 40,000 - in each electorate. And no, they are not the same for each house.
SA What do you record about voters, candidates and the like?
AO Basic details mainly - name and address. Party affiliation for candidates (if there is one). The number of votes each candidate gets. Whether the voter actually voted, etc. Who the voter voted for ...
SA Who they voted for?
AO Ah. Didn't we tell you. This is to be an online voting system. We want to do away with paper and have all voters use touch sensitive screens. We'll need to record their full preference list so we can calculate who has won. However, we want do not want any files that record voter id with their vote - can we use some sort of surrogate code ?
SA That should be simple enough. We'll think about how that be done. You want our system to work out who has won?
AO No. We have another system for doing that. We just want you to record the preferences and be able to store the results.
SA Preferences?
AO Yes. In the lower house, voters vote for all candidates in order of their preferences. In the upper house, everyone either votes for one party (only) or votes for all candidates in the order of their preferences. And one more thing. We have a list of common queries we use (supplied below). Please make sure that we store the data so they all work.
SA Okay, thank you again.
Queries
Once you have written your system, provide the SQL statements for the following queries:
1. List all candidates with more than 2000 first preference votes.
2. List all candidates standing in the electorate of "Bedford Park".
3. List all voters eligible to vote in the electorate of "Bedford Park".
4. List all candidates by electorate with their first preference counts.
5. List all voters who were eligible to vote in the electorate of "Bedford Park" but didn't.
6. List the name and address of all candidates who did not get more than 300 votes.
7. List all the candidates who received fewer votes than "Ellen".
8. List all electorates, in alphabetical order, who have four or more candidates standing.
9. List all candidates, in alphabetical order, who received no votes.
10. Change the name of the "Bedford Park" electorate to "Flinders University".
11. List all candidates that have stood for more than one election.
12. Display the party with the most seats.
Assignment 2- Presentation (Postgraduate)
You are required required to read some classic and up-to-date research papers (at least 6) on the one of the topics listed at the end of this document. Compose an annotated bibliography of research papers that relate to the issue so your annotated bibliography will help you (and others) to have a good resource to evaluate and think about the relevance and quality of material on the topic.
Make sure that the information meets the requirements of the topic and is from a reliable and academically respected source. Website references are not permitted unless the website is refereed e.g., an electronic journal such as IEEE Xplore digital library or ACM digital library both of which are available through the university library.
What is an annotated bibliography?
An annotated bibliography is a list of citations to books, articles, and documents. Each citation is followed by a brief (usually about 150 - 300 words) descriptive and evaluative paragraph, the annotation. The purpose of the annotation is to inform the reader of the relevance, accuracy, and quality of the sources cited.
Annotations are NOT abstracts. Abstracts are the purely descriptive summaries often found at the beginning of scholarly journal articles or in periodical indexes. Annotations are descriptive and critical; they expose the author's point of view, clarity and appropriateness of expression, and authority.
General Instructions
The annotated bibliography is to be produced using a word-processor and a reference database. The submitted document must be in PDF format.
The Process
Creating an annotated bibliography calls for the application of a variety of intellectual skills: concise exposition, succinct analysis, and informed library research. First, locate and record citations to books, periodicals, and documents that may contain useful information and ideas on your topic. Briefly examine and review the actual items. Then choose those works that provide a variety of perspectives on your topic. You may have to quickly review many works before you find good ones. Annotate at least six of these. The length of the annotation will depend on why it has been included and on the source itself. Cite the book, article, or document using a recognised reference style such as the Harvard style. Write a concise annotation that summarises the central theme and scope of the book or article. Include information that:
• evaluates the authority or background of the author,
• comments on the intended audience
• compares or contrasts this work with another you have cited, and
• explains how this work illuminates your bibliography topic.
Each student must ensure that the annotations are in their own words.