Netball case study

1
Netball case study

• Harryanto Surjani

2
domain

• Team names
• Team locations where theyre based
• Game venues Venue name and what the stadium is
  called
• Draw whos playing who and in what rounds
• Games played games played so far

3
erd
4
1 to M
cityid city -------------------- 1
Canberra 2 Melbourne 3 Hobart
venueid cityid
stagename --------------------------------------
----------------------- 1 1 AIS
Arena 2 1 Subway ACT Netball
Centre 3 2 State Netball Hockey
Centre
5
MM
teamid cityid teamname ---------------
------------------------ 1 1
Canberra Darters 2 2 Melbourne
Kestrels 3 2 Melbourne Phoenix
drawid hometeamid awayteamid venueid
round -----------------------------------------
------- 1 2 3
4 1 2 7 1
11 1 3 8 5
12 1
6
simple query

• select from hsteam

Shows all rows in the hsteam table.

• teamid cityid teamname
• ---------------------------------------
• 1 1 Canberra Darters
• 2 2 Melbourne Kestrels
• 3 2 Melbourne Phoenix
• 4 4 Hunter Jaegers
• 5 5 Adelaide Thunderbirds
• 6 6 Perth Orioles
• 7 7 Queensland Firebirds
• 8 9 Sydney Swift

7
natural join

• select from hsteam natural join hscity

Shows which city each team is based in.

• cityid teamid teamname city
• -----------------------------------------------
  ---
• 1 1 Canberra Darters
  Canberra
• 2 2 Melbourne Kestrels
  Melbourne
• 2 3 Melbourne Phoenix
  Melbourne
• 4 4 Hunter Jaegers
  Newcastle
• 5 5 Adelaide Thunderbirds Sydney
• 6 6 Perth Orioles Perth
• 7 7 Queensland Firebirds
  Brisbane
• 9 8 Sydney Swift
  Adelaide

8
cross product

• select from hsteam, hscity where hsteam.cityid
  hscity.cityid

Shows which city each team is based in.

• teamid cityid teamname cityid
  city
• -----------------------------------------------
  -----------
• 1 1 Canberra Darters 1
  Canberra
• 2 2 Melbourne Kestrels 2
  Melbourne
• 3 2 Melbourne Phoenix 2
  Melbourne
• 4 4 Hunter Jaegers 4
  Newcastle
• 5 5 Adelaide Thunderbirds 5
  Sydney
• 6 6 Perth Orioles 6
  Perth
• 7 7 Queensland Firebirds 7
  Brisbane
• 8 9 Sydney Swift 9
  Adelaide

9
group by

• select teamname, count() as home_games
• from hsdraw natural join hsteam
• where hometeamid teamid
• group by teamname

Shows how many home games each team has.

• teamname home_games
• -----------------------------------
• Melbourne Phoenix 7
• Perth Orioles 7
• Adelaide Thunderbirds 7
• Hunter Jaegers 7
• Melbourne Kestrels 7
• Queensland Firebirds 7
• Canberra Darters 7
• Sydney Swift 7

10
subquery

• select teamname, awaypoints, homepoints
• from hsdraw natural join hsgame, hsteam
• where awayteamidteamid and
• awaypoints gt (select max(homepoints)
  from hsgame)

Shows the most points an away team has had over a
home team.

• teamname awaypoints homepoints
• -----------------------------------------------
• Adelaide Thunderbirds 69 40

11
cross product

• select home.teamname as home_teamname,
  away.teamname as away_teamname
• from hsdraw, hsgame, hsteam home, hsteam away
• where hsdraw.hometeamid home.teamid and
• hsdraw.awayteamidaway.teamid and
• homepoints gt awaypoints and
• round 1 and
• hsgame.drawidhsdraw.drawid

Shows the home teams that won in round 1.

• home teamname away teamname
• ---------------------------------------------
• Queensland Firebirds Canberra Darters
• Sydney Swift Adelaide Thunderbirds
• Hunter Jaegers Perth Orioles

12
constraints
constraint check_hshomepoints check (homepoints gt
0), constraint check_hsawaypoints check
(awaypoints gt 0) constraint validhscity check
(city in ('Canberra', 'Melbourne', 'Hobart',
'Newcastle', 'Sydney', 'Perth',
'Brisbane', 'Wollongong',
'Adelaide', 'Grafton'))
13
delete cascades

• constraint fkhscityid foreign key (cityid)
  references hscity (cityid)
• on update cascade
• on delete cascade
• constraint fkhshometeamid foreign key
  (hometeamid) references hsteam (teamid)
• on delete cascade
• on update cascade,
• constraint fkhsawayteamid foreign key
  (awayteamid) references hsteam (teamid)
• on delete cascade
• on update cascade,

14
views

• CREATE view hshomepointstable (team, points)
• as select teamname, count()2
• from hsgame natural join hsdraw, hsteam
• where homepoints gt awaypoints and hometeamid
  teamid
• group by teamname
• CREATE view hsawaypointstable (team, points)
• as select teamname, count()2
• from hsgame natural join hsdraw, hsteam
• where awaypoints gt homepoints and
  awayteamidteamid
• group by teamname
• CREATE view hspointstable (team, points)
• as select team,
• CASE WHEN home.points is
  null THEN away.points
• WHEN away.points is
  null then home.points
• ELSE
  home.pointsaway.points
• END
• from hshomepointstable home FULL JOIN
  hsawaypointstable away