Australian Electoral Commission Database

2. Australian Electoral Commission Database By: Jason Murphy

3. What is the AEC Database • It records all former and present Federal Members of parliament • It shows the results of each electorate and the relevant member who achieved such results • Lists all political parties • Provides contact information for constituents

4. AEC Database ER Diagram

5. One to Many relationship • One party has many members jmurphy=> select memberid, partyid, firstname, lastname from AEC_Member where partyid = 'LP'; memberid | partyid | firstname | lastname ----------+---------+-----------+---------- 258632 | LP | Tony | Abott 344523 | LP | Phillip | Ruddock

6. Many to many relationship • Many party’s have many election results as they vary from electorate to electorate TABLE 1 :AEC_Electorate electorateid | electionresultsid | state | landsize | age18 | age30 | age50 --------------+-------------------+-------+----------+-------+-------+------- Lowe | 82465 | NSW | 45000 | 10 | 20 | 68 Sydney | 92465 | NSW | 65000 | 1 | 67 | 5 Berowra | 79462 | NSW | 76000 | 25 | 25 | 30 Flinders | 23122 | TAS | 100000 | 25 | 25 | 30 (4 rows)

7. Many to Many Linked By TABLE THAT LINKS THE PREVIOUS AND THE NEXT SLIDE: AEC_ER erid | electionresultsid| electorateid 980012 | 82465 | Lowe 213652 | 72465 | Lowe (2 rows)

8. Many to Many Cont’d AEC_ElectionResults electionresultsid | liberal | labor | noinformal | totalvotes | year -------------------+---------+-------+------------+------------+------ 79462 | 60.2 | 10.8 | 25 | 15000 | 2004 72465 | 30.2 | 30.9 | 5 | 12500 | 2001 92465 | 18.2 | 5 | 1 | 11250 | 2004 82465 | 20.24 | 30.62 | 30 | 15100 | 2004 23122 | 5.98 | 10 | 60 | 12332 | 2001 (5 rows)

9. Simple query of a single table Show all election results where labor achieved greater than 30% on a two party preferred basis jmurphy=> Select * from AEC_ElectionResults where labor > 30; electionresultsid | liberal | labor | noinformal | totalvotes | year -------------------+---------+-------+------------+------------+------ 72465 | 30.2 | 30.9 | 5 | 12500 | 2001 82465 | 20.24 | 30.62 | 30 | 15100 | 2004 (2 rows)

10. Natural Join Query When you want to join two tables together the automatic way is a natural join statement. The below query joins the AEC_Member and AEC_Electorate table together jmurphy=> Select electorateid, firstname, lastname, landsize from AEC_Member natural join AEC_Electorate; electorateid | firstname | lastname | landsize --------------+-----------+----------+---------- Lowe | John | Murphy | 45000 Sydney | Tony | Abott | 65000 Flinders | Sarah | Stevens | 100000 Berowra | Phillip | Ruddock | 76000 (4 rows)

11. Cross Product Natural Join • This is the same query but the extended version where in the SQL the links between the tables i.e the primary and foreign keys need to be manually correlated select AEC_Electorate.electorateid, firstname, lastname, landsize from AEC_Member, AEC_Electorate where AEC_Member.electorateid = AEC_Electorate.electorateid; electorateid | firstname | lastname | landsize --------------+-----------+----------+---------- Lowe | John | Murphy | 45000 Sydney | Tony | Abott | 65000 Flinders | Sarah | Stevens | 100000 Berowra | Phillip | Ruddock | 76000 (4 rows)

12. Group By • When trying to find the number of electorates in each state a group by function is needed jmurphy=> select state, count (*) as Number from AEC_Electorate group by state order by state; state | number -------+-------- NSW | 3 TAS | 1 (2 rows)

13. Subquery Show the electorate, the state in which the electorate is when the land is greater than average jmurphy=> select electorateid, state, landsize from AEC_Electorate where landsize > (select AVG(landsize) from AEC_Electorate); electorateid | state | landsize --------------+-------+---------- Berowra | NSW | 76000 Flinders | TAS | 100000 (2 rows)

14. Self Join query When you want to ensure that the electorateid is correct. This is just in case a member changes electorates and the database wasn’t changed. jmurphy=> select ass1.electorateid, ass2.electorateid, mstate from AEC_Member ass1, AEC_Electorate ass2 where ass1.electorateid = ass2.electorateid; electorateid | electorateid | mstate --------------+--------------+-------- Lowe | Lowe | NSW Sydney | Sydney | NSW Flinders | Flinders | TAS Berowra | Berowra | NSW (4 rows)

15. CHECK statements • It is important to ensure that the data entered is correct. The following check statement ensures that the number of informal votes entered is • (a) not negative • (b) doesn’t exceed the total number of votes • CONSTRAINT AEC_ElectionResults_Invalid_Informals CHECK ((NoInformal >= 0) AND (NoInformal <= TotalVotes))

16. Another CHECK constraint • The following CHECK constraint ensures that the member has a starting date greater than or in 1901. • This is because Australia was not federated until 1901 and that is the first possible year within the database • CONSTRAINT AEC_Member_Invalid_StartYear CHECK (StartYear >= 1901)

17. Delete Cascade When PartyID is deleted this will flow throuhgh the AEC_Member table CONSTRAINT AEC_Member1_Party_FK FOREIGN KEY (PartyID) REFERENCES AEC_Party ON DELETE CASCADE

18. Delete cascade When Electorate ID is deleted this will flow throuhgh the AEC_Electorate table CONSTRAINT AEC_Member2_Electorate_FK FOREIGN KEY (ElectorateID) REFERENCES AEC_Electorate ON DELETE CASCADE

19. Views in the AEC database • Views allow us to have the data on hand without having to constantly re type the same sql. • The view is stored within the database and can be easily used again with a view name set by the user • CREATE VIEW name AS query

20. Views • Say we want to have the following data shown: electorateid, firstname, lastname, contactnumber where there is no finish date • This could be used by the AEC to issue a phone number for people trying to get in contact with their member of parliament Output: | firstname | lastname | contactnumber | | John | Murphy | 0297474211 | | Tony | Abott | 0295611876 | | Sarah | Stevens | 0487299833 | | Phillip | Ruddock | 0411299833 | CREATE VIEW contact AS firstname, lastname, contactnumber from AEC_Member where finishdate = null;

21. How to use a view once saved • \dv checks to see the views that have been stored, this is a user shortcut List of relations Name | Type | Owner -------------------------------+------+---------- contact | view | jmurphy

22. The End Thank you for listening about the AEC database