Australian Football League AFL Database

1
Australian Football League (AFL) Database

• By De Wen Zhong

2
Description of Database

• This database holds data about the teams that are
  involved in the AFL
• There are eight tables altogether each holding
  data about various things related to each team.
• There is a table that contains data on each of
  the teams such as when they joined the league and
  how many premierships they have won in the past.
• Has information about players and what teams
  they play for at the moment as well as the team
  they may have transferred from.
• Has coach information such as who they coach and
  what type of coach they are (Head or Assistant).
• Information in each teams home grounds in which
  they play at such as the capacity and location.
• Also has information on what the team colours are
  for each team.

3
ER Diagram
TeamPlayer TeamName PlayerID PastClubs
PlayerDetails PlayerID PlayerLastName PlayerFirstN
ame Debut DOB
Colours ColourID ColourName
Teams TeamName TeamStart TeamPremierships TeamMasc
ot
TeamColours TeamName ColourID
CoachDetails CoachID TeamName CoachLastName Coach
FirstName CoachType FirstYear CoachPremierships
VenueDetails VenueID VenueName Location Capacity
TeamVenue TeamName VenueID
4
One-to-Many Relationship

• One team can have many coaches

coachlastname teamname ----------------
---------------------- Clarkson Hawthorn
Connelly Fremantle Craig
Adelaide Daniher Melbourne Eade
Footscray Laidley North Melbourne
Lyon St Kilda Malthouse
Collingwood Matthews Brisbane/Fitzroy
Pagan Carlton Roos
Sydney/South Melbourne Sheedy Essendon
Thompson Geelong Wallace
Richmond Williams Port Adelaide Worsfold
West Coast Longmire Sydney/South
Melbourne Blakey Sydney/South
Melbourne Berbakov Sydney/South
Melbourne Allison Sydney/South
Melbourne (20 rows)
Foreign Key
Primary key
CoachDetails CoachID TeamName CoachLastName Coach
FirstName CoachType FirstYear CoachPremierships
Teams TeamName TeamStart TeamPremierships TeamMasc
ot
5
Many-to-Many Relationship

• One team can have many colours while many teams
  can share one colour

Colours ColourID ColourName
Primary Key
Primary Key
Foriegn Key
Teams TeamName TeamStart TeamPremierships TeamMasc
ot
TeamColours TeamName ColourID
Foriegn Key
6
Many-to-Many Relationship

• One team can have many colours while many teams
  can share one colour

Teams
Colours
ColourID comes from Colours table
colourid colourname ----------------------
1 Black 2 Blue 3
Brown 4 Green 5 Maroon
6 Navy Blue 7 Red 8 Teal
9 Yellow 10 White (10 rows)
teamname other
columns ---------------------------------------
Adelaide ...
Brisbane/Fitzroy ... Carlton
... Collingwood
... Essendon ... Fremantle
... Geelong
... ... ... (16 rows)
TeamColours
teamname colourid ---------------
------------------- Adelaide
1 Adelaide 7
Adelaide 9
Brisbane/Fitzroy 9
Brisbane/Fitzroy 5 Carlton
10 Carlton
6 Collingwood 1
Collingwood 10 ...
... (39 rows)
TeamName comes from Teams table
7
Simple Query

• Show all the teams that have won more than 2
  premierships
• SELECT teamname, teamstart, teampremierships
• FROM teams
• WHERE teampremierships gt2

teamname teamstart
teampremierships --------------------------------
--------------------- Brisbane/Fitzroy
1983 11 Carlton
1897 16 Collingwood
1897 14 Essendon
1897 16
Geelong 1859
6 Hawthorn 1873
9 North Melbourne 1869
4 Melbourne 1856
12 Richmond
1908 10 Sydney/South Melbourne
1897 4 West Coast
1987 3 (11 rows)
8
Natural Join

• Show the coaches who are coaching Sydney/South
  Melbourne
• SELECT teamname, coachlastname, coachfirstname,
  coachtype
• FROM teams NATURAL JOIN coachdetails
• WHERE teamname Sydney/South Melbourne

teamname coachlastname
coachfirstname coachtype --------------------
-----------------------------------------------
----- Sydney/South Melbourne Roos
Paul Head Coach Sydney/South
Melbourne Longmire John
Assistant Coach Sydney/South Melbourne Blakey
John Assistant Coach
Sydney/South Melbourne Berbakov Peter
Assistant Coach Sydney/South Melbourne
Allison Brett Assistant
Coach (5 rows)
9
Cross Product

• Show which coach is coaching which team
• SELECT teams.teamname, coachlastname,
  coachfirstname, coachtype
• FROM teams, coachdetails
• WHERE teams.teamname coachdetails.teamname AND
• teams.teamname Sydney/South Melbourne

teamname coachlastname
coachfirstname coachtype --------------------
-----------------------------------------------
----- Sydney/South Melbourne Roos
Paul Head Coach Sydney/South
Melbourne Longmire John
Assistant Coach Sydney/South Melbourne Blakey
John Assistant Coach
Sydney/South Melbourne Berbakov Peter
Assistant Coach Sydney/South Melbourne
Allison Brett Assistant
Coach (5 rows)
10
Group By with HAVING

• Show the amount of home grounds a team has given
  they have more than 1
• SELECT teamname, count() as amount
• FROM teamvenue
• GROUP BY teamname HAVING count() gt1

teamname amount -----------------
-------------- Footscray 4
Essendon 2 Collingwood
2 Hawthorn 2
Sydney/South Melbourne 2 North Melbourne
3 Richmond 2
Carlton 2 Melbourne
3 (9 rows)
11
Sub Queries

• Find the players who have played in the same
  previous club as Barry Hall
• SELECT playerfirstname, playerlastname, pastclubs
• FROM teamplayer NATURAL JOIN playerdetails
• WHERE pastclubs (
• SELECT pastclubs
• FROM teamplayer NATURAL JOIN playerdetails
• WHERE playerfirstname Barry AND
  playerlastname Hall

playerlastname playerfirstname ----------------
----------------- Everitt Peter Hall
Barry (2 rows)
12
Self Join

• Find the players who have played in the same
  previous club as Barry Hall
• SELECT p1.playerfirstname, p1.playerlastname,
  t1.pastclubs
• FROM teamplayer t1, teamplayer t2 , playerdetails
  p1, playerdetails p2
• WHERE p2.playerfirstname Barry AND
• p2.playerlastname Hall AND
• t1.playerno p1.playerno AND
• t2.playerno p2.playerno AND
• t1.pastclubs t2.pastclubs

playerlastname playerfirstname ----------------
----------------- Everitt Peter Hall
Barry (2 rows)
13
CHECK statements

• CHECK statements makes sure the user does not
  enter in invalid data
• To check for a valid year for a team to join the
  league
• CONSTRAINT TeamStart CHECK(TeamStart gt 1856)
• Check for a valid capacity for a venue (more then
  10 000 people but less than 200 000 people)
• CONSTRAINT Capacity CHECK((Capacity gt 10000) AND
  (Capacity lt 200000))
• Check for a valid debut year for a player (after
  1970)
• CONSTRAINT Debut CHECK(Debut gt 1970)
• Check for a valid date of birth for a player
  (after 1950)
• CONSTRAINT DOB CHECK(DOB gt 1950)

14
CHECK statements

• Check the coachtype will be either head coach or
  assistant coach
• CONSTRAINT CoachType CHECK((CoachType 'Head
  Coach') OR (CoachType 'Assistant Coach'))
• Check the first year of the coach coaching is
  after 1950
• CONSTRAINT FirstYear CHECK(FirstYear gt 1950)

15
ON DELETE/UPDATE CASCADE

• Make sure to retain the integrity of the database
• When one record is dependent an another, when the
  main record is deleted, delete the dependent
  record as well
• When one record is dependent an another, when the
  main record is being changed, change the
  dependent record as well
• CONSTRAINT TeamNameFK FOREIGN KEY (TeamName)
• REFERENCEs Teams
• ON DELETE CASCADE
• ON UPDATE CASCADE,

16
Create View

• Show all the venues that Footscray plays on as a
  home ground
• CREATE VIEW footscrayvenues (venuename,
  location, capacity)
• AS SELECT venuename, location, capacity
• FROM teamvenue natural join venuedetails
• WHERE teamname 'Footscray'

venuename location
capacity ---------------------------------------
-------- Melbourne Cricket Ground Melbourne
100000 Telstra Dome Melbourne
56347 Manuka Oval Canberra
15000 Marrara Oval Darwin
15000 (4 rows)
17
Create View

• Show all the venues that Footscray plays on as a
  home ground that has a capacity of over 50 000
• SELECT FROM footscrayvenues
• WHERE capacity gt 50000

venuename location
capacity ---------------------------------------
-------- Melbourne Cricket Ground Melbourne
100000 Telstra Dome Melbourne
56347 (2 rows)