Transcript PowerPoint

Australian Electoral
Commission Database
By: Jason Murphy
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
AEC Database ER Diagram
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

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)
Many to Many Linked By
TABLE THAT LINKS THE PREVIOUS AND
THE NEXT SLIDE: AEC_ER
erid
980012
213652
(2 rows)
| electionresultsid| electorateid
| 82465
| Lowe
| 72465
| Lowe
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)
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)
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)
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)
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)
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)
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)
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))
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)
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
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
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
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;
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
The End
Thank you for listening about the
AEC database