1 / 17

“National Electoral Division Profiles” Case Study

“National Electoral Division Profiles” Case Study. Vincent Tang High Distinction Assignment, Autumn 2007. Introduction to National Electoral Division Profiles. The database refers to the website: http://www.aec.gov.au/_content/Who/profiles/divisional_profiles_2004.pdf

muncel
Download Presentation

“National Electoral Division Profiles” Case Study

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. “National ElectoralDivision Profiles”Case Study Vincent Tang High Distinction Assignment, Autumn 2007

  2. Introduction to National Electoral Division Profiles The database refers to the website: http://www.aec.gov.au/_content/Who/profiles/divisional_profiles_2004.pdf Holds information about 14 National/Federal Electoral Divisions The current/previous members of those divisions First Name Surname Parties the members belong too The length of period they are members’ of the division Year Started Year Ended Contact Details for the State Head Office

  3. ERD

  4. One to Many Relationship – 1:m Division Table divisionname| state| size | firstelection| demographic ------------+------+------+--------------+------------- …………………….. | ….. | ….| ……….| …. Prospect| NSW | 159| 1968| Inner Metro Reid | NSW | 62| 1922| Inner Metro Calare | NSW | 21621| 1906| Rural Foreign Key • State in Division Table is Foreign Key in StateOffice Table Primary Key StateOffice Table stateabv|buildingno| street | suburb | postcode| areacode| phoneno | faxno ---------+----------+-----------------------+----------+---------+---------+---------+--------- NSW | 24| Campbell Street | Sydney | 2000| 2| 93756333| 92127659 VIC | 2| Lonsdale Street | Melbourne| 3000| 3| 92857100| 92857174 QLD | 488| Queen Street | Brisbane | 4000| 7| 38343440| 38323058 WA | 111| St Georges Terrace | Perth | 6848| 8| 63638080| 63638016 SA | 1| King William Street | Adelaide | 5000| 8| 82376555| 82312664 TAS | 86| Collins Street | Hobart | 7000| 3| 62350501| 62344268 NT | 80| Mitchell Street | Darwin | 800| 8| 89828000| 89817964 ACT | | Queen Victoria Terrace| Parkes | 2600| 2| 62714411| 62714558 (8 rows)

  5. Many to Many Relationship – m:m Memberships Table Member Table memid*| partyabv* -------+--------- 1 | ALP 2 | ALP 3 | ALP 8 | LP 9 | LP 10 | ALP 11 | IND memid | memfirstname | memsurname -------+--------------+------------ 1 | C | Bowen 2 | J | Crosio 3 | R E | Klugman 8 | C | Pearce 9 | PE | Nugent 10 | J | Saunderson 11 | P | Andren PartName Table partyabv | partyname ----------+------------------------------------------- ALP |Australian Labor Party IND |Independent LP |Liberal Party of Australia (formed in 1946)

  6. Query: Simple • Display Divisions that belong to NSW select * from division where state='NSW'; divisionname | state | size | firstelection | demographic --------------+-------+-------+---------------+------------- Prospect | NSW | 159 | 1968 | Inner Metro Reid | NSW | 62 | 1922 | Inner Metro Calare | NSW | 21621 | 1906 | Rural (3 rows)

  7. Query: Natural Join • Display everything in both member table and membership table (natural join): • - shows the members name and the party they belong in select * from member natural join memberships; memid | memfirstname | memsurname | partyabv -------+--------------+-------------+---------- 1 | C | Bowen | ALP 2 | J | Crosio | ALP 3 | R E | Klugman | ALP 4 | L | Ferguson | ALP 5 | T | Uren | ALP 6 | C A | Morgan | ALP 7 | J T | Lang | LANG LAB 8 | C | Pearce | LP 9 | PE | Nugent | LP 10 | J | Saunderson | ALP 11 | P | Andren | IND 12 | D W | Simmons | ALP 13 | A J | MacKenzie | NCP 13 | A J | MacKenzie | NP

  8. Query: Cross Product • Display everything in both member table and membership table (cross-product): • - shows the members name and the party they belong in select * from member, memberships where member.memid = memberships.memid; memid | memfirstname | memsurname | memid | partyabv -------+--------------+-------------+-------+---------- 1 | C | Bowen | 1 | ALP 2 | J | Crosio | 2 | ALP 3 | R E | Klugman | 3 | ALP 4 | L | Ferguson | 4 | ALP 5 | T | Uren | 5 | ALP 6 | C A | Morgan | 6 | ALP 7 | J T | Lang | 7 | LANG LAB 8 | C | Pearce | 8 | LP 9 | PE | Nugent | 9 | LP 10 | J | Saunderson | 10 | ALP 11 | P | Andren | 11 | IND 12 | D W | Simmons | 12 | ALP 13 | A J | MacKenzie | 13 | NCP 13 | A J | MacKenzie | 13 | NP

  9. Query: Group By • Determine the total amount of divisions in the each state select state, count(*) as total from division group by state; state | total -------+------- TAS | 1 NT | 1 ACT | 1 SA | 1 VIC | 3 WA | 1 QLD | 3 NSW | 3 (8 rows)

  10. Query: Group By & Having • Determine the states with more than 2 divisions select state, count(*) as total from division group by state having count(*) >2; state | total -------+------- VIC | 3 QLD | 3 NSW | 3 (3 rows)

  11. Query: Sub-Query • Display previous members who we’re members for a division greater then the average length select memfirstname, memsurname, divisionname, yrend-yrstart as length from member natural join period where yrend-yrstart> (select avg(yrend-yrstart) from period); memfirstname | memsurname | divisionname | length --------------+-------------+--------------+-------- J | Crosio | Prospect | 14 T | Uren | Reid | 32 PE | Nugent | Aston | 11 D W | Simmons | Calare | 13 J A | England | Calare | 15 Dr A C | Theophanous | Calwell | 17 R F | Shipton | Higgins | 15 H E | Holt | Higgins | 18 B C | Humphreys | Griffith | 19 D M | Cameron | Griffith | 11 E E | Darling | Lilley | 13 W | Truss | Wide Bay | 16 B P | Hansen | Wide Bay | 13 N | Andrew | Wakefield | 21 D | Williams | Tangney | 11 (15 rows)

  12. Query: Self Join Show divisions which are considered the same demographic as Lilley SELECT d2.divisionname, d2.demographic FROM division d1, division d2 WHERE d1.divisionname = 'Lilley' AND d1.demographic = d2.demographic; divisionname | demographic --------------+------------- Prospect | Inner Metro Reid | Inner Metro Higgins | Inner Metro Griffith | Inner Metro Lilley | Inner Metro Tangney | Inner Metro Solomon | Inner Metro Denison | Inner Metro Canberra | Inner Metro (9 rows)

  13. Action Statement: “CHECK” CREATE TABLE Period ( MemID INTEGER , YrStart INTEGER NOT NULL, YrEnd INTEGER , DivisionName TEXT , ………………, ………………………, CONSTRAINT di_Period_YrStart CHECK ((YrStart >= 1900) AND (YrStart <= 2500)), ………… ); Output – When constraint is violated INSERT INTO Period VALUES (37,1880,null,'Denison'); ERROR: new row for relation "period" violates check constraint "di_period_yrstart"

  14. “CHECK” Constraints CREATE TABLE Division ( DivisionName TEXT , State Varchar(3) , Size INTEGER NOT NULL, FirstElection INTEGER , Demographic TEXT NOT NULL, …………………, ……………………, CONSTRAINT di_Division_Demographic CHECK (Demographic IN ('Inner Metro', 'Outer Metro', 'Provincial', 'Rural')) ); Output – When constraint is violated INSERT INTO Division VALUES ('Canberra','ACT',1900,1974, ‘Inner'); ERROR: new row for relation "division" violates check constraint "di_division_demographic"

  15. Action Statement:“ON DELETE RESTRICT” • To prevent delete in Member Table without deleting data from Memberships Table first CREATE TABLE Memberships ( MemID INTEGER NOT NULL, PartyAbv TEXT , CONSTRAINT MSPK PRIMARY KEY (MemID, PartyAbv), CONSTRAINT MS_MemIdFK FOREIGN KEY (MemID) REFERENCES Member ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT MS_PartyFK FOREIGN KEY (PartyAbv) REFERENCES PartyName ON DELETE RESTRICT ON UPDATE CASCADE); Output – When constraint is violated DELETE FROM Member WHERE MemId = 1; ERROR: update or delete on "member" violates foreign key constraint "ms_memidfk" on "memberships"

  16. Creating a “VIEW” • To create a view containing the previous/current members of the division ‘Prospect’ CREATE VIEW Prospect (Id, Surname, FirstName, Start) AS SELECT memid, memsurname, memfirstname, yrstart FROM member natural join period WHERE divisionname = 'Prospect'; select * from Prospect; id | surname | firstname | start ----+---------+-----------+------- 1 | Bowen | C | 2004 2 | Crosio | J | 1990 3 | Klugman | R E | 1969 (3 rows)

  17. Query a “View” • To search the view table ‘Prospect’ for members who started after 1980 select * from prospect where start > 1980; id | surname | firstname | start ----+---------+-----------+------- 1 | Bowen | C | 2004 2 | Crosio | J | 1990 (2 rows)

More Related