I love Cricket. I`m a right arm fast bowler and you can say a pinch hitter. I was reading about ICC(International Cricket Council) World Cup and I started visualizing the Graph for the World Cup. I admit that I am a RDBMS lover but since I am exploring Neo4j Graph Database, I am able to visualize problems more in terms of graphs than in tabular format with joins between tables to extract information.
In ICC 2015 World Cup which will be hosted by Australia and New Zealand together, 14 teams will be competing to grab the World Cup Trophy. ICC have divided all the International Cricket Playing nations into two Pools, pool A and pool B. Each Pool having 7 teams.Pool A teams will play matches to other teams in Pool A similarly Pool B teams will play matches among themselves. Each match will be played between two teams (no surprise) at some Venue.
In each match three umpires will be there, one of them playing the role of third umpire and other two will be field umpires. As the World Cup tournament will proceed teams will have to travel to different cities of Australia and New Zealand to play their matches. Some teams will be traveling more as compared to others. My aim is to find out the total travelling for each team.
I have taken the demo data for ICC World Cup 2015 Fixtures from ICC official website " ICC World Cup 2015 Fixtures ".
One possible RDBMS database design will be something like this. Having different tables , with foreign keys pointing to other tables and you can get whatever you want by joining tables. But one query that I am specifically looking for is finding the total travelling for a team. I think this query will be easier in Neo4j then a RDBMS.
Pools Table
| pool_id | pool_name |
| 1 | A |
| 2 | B |
Teams Table
| pool_id | team_id | team_name |
| 1 | 1 | AUSTRALIA |
| 1 | 2 | ENGLAND |
| 2 | 3 | INDIA |
| 2 | 4 | SOUTH AFRICA |
Umpires Table
| umpire_id | umpire_name | age |
| 1 | Aleem Dar | 39 |
| 2 | Rod Tucker | 42 |
| 3 | Nigel Llong | 40 |
| 4 | Steve Davis | 48 |
Venues Table
| venue_id | venue_name | city | country |
| 141 | Sydney Cricket Ground, SCG | Sydney | Australia |
| 142 | Melbourne Cricket Ground, MCG | Melbourne | Australia |
Matches Table
| match_number | team_A | team_B | venue_id | first_umpire | second_umpire | third_umpire |
| 1 | AUSTRALIA | ENGLAND | 141 | 1 | 2 | 3 |
| 2 | INDIA | PAKISTAN | 142 | 3 | 2 | 1 |
In my graph model , I have two nodes for pools and 14 team nodes each representing a Team. I could have put the pool information in the team node itself , like (team:Team{name:"India",pool:"A"}). I also have Match nodes representing matches , Umpire nodes and Venue nodes and relationship among these nodes.
//Create Two Pools of Teams , Pool A and Pool B
create (poolA:Pool{name:"A"}),
(poolB:Pool{name:"B"})
//Creating 14 Teams
create (england:Team{name:"ENGLAND"}),
(australia:Team{name:"AUSTRALIA"}),
(srilanka:Team{name:"SRI LANKA"}),
(bangladesh:Team{name:"BANGLADESH"}),
(newzealand:Team{name:"NEW ZEALAND"}),
(afghanistan:Team{name:"AFGHANISTAN"}),
(scotland:Team{name:"SCOTLAND"}),
(southafrica:Team{name:"SOUTH AFRICA"}),
(india:Team{name:"INDIA"}),
(pakistan:Team{name:"PAKISTAN"}),
(westindies:Team{name:"WEST INDIES"}),
(zimbabwe:Team{name:"ZIMBABWE"}),
(ireland:Team{name:"IRELAND"}),
(uae:Team{name:"UNITED ARAB EMIRATES"})
//Creating Relationship Between Teams and Pools
create (poolA)-[:includes]->(england),
(poolA)-[:includes]->(australia),
(poolA)-[:includes]->(srilanka),
(poolA)-[:includes]->(bangladesh),
(poolA)-[:includes]->(newzealand),
(poolA)-[:includes]->(afghanistan),
(poolA)-[:includes]->(scotland),
(poolB)-[:includes]->(southafrica),
(poolB)-[:includes]->(india),
(poolB)-[:includes]->(pakistan),
(poolB)-[:includes]->(westindies),
(poolB)-[:includes]->(zimbabwe),
(poolB)-[:includes]->(ireland),
(poolB)-[:includes]->(uae)
//Creating 6 Umpires
create (aleem:Umpire{name:"Aleem Dar"}),
(steve:Umpire{name:"Steve Davis"}),
(rod:Umpire{name:"Rod Tucker"}),
(nigel:Umpire{name:"Nigel Llong"}),
(kumar:Umpire{name:"Kumar Dharmasena"}),
(marais:Umpire{name:"Marais Erasmus"})
//Creating 42 Pool Matches to decide which teams will qualify for Quarter Finals
create (match1:Match{number:1,date:"2015-02-14"}),
(match2:Match{number:2,date:"2015-02-14"}),
(match3:Match{number:3,date:"2015-02-15"}),
(match4:Match{number:4,date:"2015-02-15"}),
(match5:Match{number:5,date:"2015-02-16"}),
(match6:Match{number:6,date:"2015-02-17"}),
(match7:Match{number:7,date:"2015-02-18"}),
(match8:Match{number:8,date:"2015-02-19"}),
(match9:Match{number:9,date:"2015-02-20"}),
(match10:Match{number:10,date:"2015-02-21"}),
(match11:Match{number:11,date:"2015-02-21"}),
(match12:Match{number:12,date:"2015-02-22"}),
(match13:Match{number:13,date:"2015-02-22"}),
(match14:Match{number:14,date:"2015-02-23"}),
(match15:Match{number:15,date:"2015-02-24"}),
(match16:Match{number:16,date:"2015-02-25"}),
(match17:Match{number:17,date:"2015-02-26"}),
(match18:Match{number:18,date:"2015-02-26"}),
(match19:Match{number:19,date:"2015-02-27"}),
(match20:Match{number:20,date:"2015-02-28"}),
(match21:Match{number:21,date:"2015-02-28"}),
(match22:Match{number:22,date:"2015-03-01"}),
(match23:Match{number:23,date:"2015-03-01"}),
(match24:Match{number:24,date:"2015-03-03"}),
(match25:Match{number:25,date:"2015-03-04"}),
(match26:Match{number:26,date:"2015-03-04"}),
(match27:Match{number:27,date:"2015-03-05"}),
(match28:Match{number:28,date:"2015-03-06"}),
(match29:Match{number:29,date:"2015-03-07"}),
(match30:Match{number:30,date:"2015-03-07"}),
(match31:Match{number:31,date:"2015-03-08"}),
(match32:Match{number:32,date:"2015-03-08"}),
(match33:Match{number:33,date:"2015-03-09"}),
(match34:Match{number:34,date:"2015-03-10"}),
(match35:Match{number:35,date:"2015-03-11"}),
(match36:Match{number:36,date:"2015-03-12"}),
(match37:Match{number:37,date:"2015-03-13"}),
(match38:Match{number:38,date:"2015-03-13"}),
(match39:Match{number:39,date:"2015-03-14"}),
(match40:Match{number:40,date:"2015-03-14"}),
(match41:Match{number:41,date:"2015-03-15"}),
(match42:Match{number:42,date:"2015-03-15"})
//Creating Relationship between Matches and Teams
create (srilanka)-[:played]->(match1)<-[:played]-(newzealand),
(england)-[:played]->(match2)<-[:played]-(australia),
(southafrica)-[:played]->(match3)<-[:played]-(zimbabwe),
(india)-[:played]->(match4)<-[:played]-(pakistan),
(westindies)-[:played]->(match5)<-[:played]-(ireland),
(newzealand)-[:played]->(match6)<-[:played]-(scotland),
(bangladesh)-[:played]->(match7)<-[:played]-(afghanistan),
(zimbabwe)-[:played]->(match8)<-[:played]-(uae),
(england)-[:played]->(match9)<-[:played]-(newzealand),
(pakistan)-[:played]->(match10)<-[:played]-(westindies),
(australia)-[:played]->(match11)<-[:played]-(bangladesh),
(srilanka)-[:played]->(match12)<-[:played]-(afghanistan),
(southafrica)-[:played]->(match13)<-[:played]-(india),
(england)-[:played]->(match14)<-[:played]-(scotland),
(westindies)-[:played]->(match15)<-[:played]-(zimbabwe),
(ireland)-[:played]->(match16)<-[:played]-(uae),
(afghanistan)-[:played]->(match17)<-[:played]-(scotland),
(srilanka)-[:played]->(match18)<-[:played]-(bangladesh),
(southafrica)-[:played]->(match19)<-[:played]-(westindies),
(australia)-[:played]->(match20)<-[:played]-(newzealand),
(india)-[:played]->(match21)<-[:played]-(uae),
(england)-[:played]->(match22)<-[:played]-(srilanka),
(pakistan)-[:played]->(match23)<-[:played]-(zimbabwe),
(southafrica)-[:played]->(match24)<-[:played]-(ireland),
(pakistan)-[:played]->(match25)<-[:played]-(uae),
(australia)-[:played]->(match26)<-[:played]-(afghanistan),
(bangladesh)-[:played]->(match27)<-[:played]-(scotland),
(india)-[:played]->(match28)<-[:played]-(westindies),
(southafrica)-[:played]->(match29)<-[:played]-(pakistan),
(zimbabwe)-[:played]->(match30)<-[:played]-(ireland),
(newzealand)-[:played]->(match31)<-[:played]-(afghanistan),
(australia)-[:played]->(match32)<-[:played]-(srilanka),
(england)-[:played]->(match33)<-[:played]-(bangladesh),
(india)-[:played]->(match34)<-[:played]-(ireland),
(srilanka)-[:played]->(match35)<-[:played]-(scotland),
(southafrica)-[:played]->(match36)<-[:played]-(uae),
(bangladesh)-[:played]->(match37)<-[:played]-(newzealand),
(england)-[:played]->(match38)<-[:played]-(afghanistan),
(india)-[:played]->(match39)<-[:played]-(zimbabwe),
(australia)-[:played]->(match40)<-[:played]-(scotland),
(westindies)-[:played]->(match41)<-[:played]-(uae),
(pakistan)-[:played]->(match42)<-[:played]-(ireland)
//Creating Relationship between Umpires and Matches
create (aleem)-[:umpired_in{role:"field_umpire"}]->(match1)<-[:umpired_in{role:"field umpire"}]-(steve),
(rod)-[:umpired_in{role:"field umpire"}]->(match2)<-[:umpired_in{role:"field umpire"}]-(nigel),
(kumar)-[:umpired_in{role:"field umpire"}]->(match3)<-[:umpired_in{role:"field umpire"}]-(marais),
(aleem)-[:umpired_in{role:"field umpire"}]->(match4)<-[:umpired_in{role:"field umpire"}]-(steve),
(rod)-[:umpired_in{role:"field umpire"}]->(match5)<-[:umpired_in{role:"field umpire"}]-(nigel),
(kumar)-[:umpired_in{role:"field umpire"}]->(match6)<-[:umpired_in{role:"field umpire"}]-(marais),
(aleem)-[:umpired_in{role:"field umpire"}]->(match7)<-[:umpired_in{role:"field umpire"}]-(steve),
(rod)-[:umpired_in{role:"field umpire"}]->(match8)<-[:umpired_in{role:"field umpire"}]-(nigel),
(kumar)-[:umpired_in{role:"field umpire"}]->(match9)<-[:umpired_in{role:"field umpire"}]-(marais),
(aleem)-[:umpired_in{role:"field umpire"}]->(match10)<-[:umpired_in{role:"field umpire"}]-(steve),
(rod)-[:umpired_in{role:"field umpire"}]->(match11)<-[:umpired_in{role:"field umpire"}]-(nigel),
(kumar)-[:umpired_in{role:"field umpire"}]->(match12)<-[:umpired_in{role:"field umpire"}]-(marais),
(aleem)-[:umpired_in{role:"field umpire"}]->(match13)<-[:umpired_in{role:"field umpire"}]-(steve),
(rod)-[:umpired_in{role:"field umpire"}]->(match14)<-[:umpired_in{role:"field umpire"}]-(nigel),
(kumar)-[:umpired_in{role:"field umpire"}]->(match15)<-[:umpired_in{role:"field umpire"}]-(marais),
(aleem)-[:umpired_in{role:"field umpire"}]->(match16)<-[:umpired_in{role:"field umpire"}]-(steve),
(rod)-[:umpired_in{role:"field umpire"}]->(match17)<-[:umpired_in{role:"field umpire"}]-(nigel),
(kumar)-[:umpired_in{role:"field umpire"}]->(match18)<-[:umpired_in{role:"field umpire"}]-(marais),
(aleem)-[:umpired_in{role:"field umpire"}]->(match19)<-[:umpired_in{role:"field umpire"}]-(steve),
(rod)-[:umpired_in{role:"field umpire"}]->(match20)<-[:umpired_in{role:"field umpire"}]-(nigel),
(kumar)-[:umpired_in{role:"field umpire"}]->(match21)<-[:umpired_in{role:"field umpire"}]-(marais),
(aleem)-[:umpired_in{role:"field umpire"}]->(match22)<-[:umpired_in{role:"field umpire"}]-(steve),
(rod)-[:umpired_in{role:"field umpire"}]->(match23)<-[:umpired_in{role:"field umpire"}]-(nigel),
(kumar)-[:umpired_in{role:"field umpire"}]->(match24)<-[:umpired_in{role:"field umpire"}]-(marais),
(aleem)-[:umpired_in{role:"field umpire"}]->(match25)<-[:umpired_in{role:"field umpire"}]-(steve),
(rod)-[:umpired_in{role:"field umpire"}]->(match26)<-[:umpired_in{role:"field umpire"}]-(nigel),
(kumar)-[:umpired_in{role:"field umpire"}]->(match27)<-[:umpired_in{role:"field umpire"}]-(marais),
(aleem)-[:umpired_in{role:"field umpire"}]->(match28)<-[:umpired_in{role:"field umpire"}]-(steve),
(rod)-[:umpired_in{role:"field umpire"}]->(match29)<-[:umpired_in{role:"field umpire"}]-(nigel),
(kumar)-[:umpired_in{role:"field umpire"}]->(match30)<-[:umpired_in{role:"field umpire"}]-(marais),
(aleem)-[:umpired_in{role:"field umpire"}]->(match31)<-[:umpired_in{role:"field umpire"}]-(steve),
(rod)-[:umpired_in{role:"field umpire"}]->(match32)<-[:umpired_in{role:"field umpire"}]-(nigel),
(kumar)-[:umpired_in{role:"field umpire"}]->(match33)<-[:umpired_in{role:"field umpire"}]-(marais),
(aleem)-[:umpired_in{role:"field umpire"}]->(match34)<-[:umpired_in{role:"field umpire"}]-(steve),
(rod)-[:umpired_in{role:"field umpire"}]->(match35)<-[:umpired_in{role:"field umpire"}]-(nigel),
(kumar)-[:umpired_in{role:"field umpire"}]->(match36)<-[:umpired_in{role:"field umpire"}]-(marais),
(aleem)-[:umpired_in{role:"field umpire"}]->(match37)<-[:umpired_in{role:"field umpire"}]-(steve),
(rod)-[:umpired_in{role:"field umpire"}]->(match38)<-[:umpired_in{role:"field umpire"}]-(nigel),
(kumar)-[:umpired_in{role:"field umpire"}]->(match39)<-[:umpired_in{role:"field umpire"}]-(marais),
(aleem)-[:umpired_in{role:"field umpire"}]->(match40)<-[:umpired_in{role:"field umpire"}]-(steve),
(rod)-[:umpired_in{role:"field umpire"}]->(match41)<-[:umpired_in{role:"field umpire"}]-(nigel),
(kumar)-[:umpired_in{role:"field umpire"}]->(match42)<-[:umpired_in{role:"field umpire"}]-(marais)
//Creating 14 Venues
create (hagley:Venue{name:"Hagley Oval",city:"Christchurch"}),
(mcg:Venue{name:"Melbourne Cricket Ground",city:"Melbourne"}),
(seddon:Venue{name:"Seddon Park",city:"Hamilton"}),
(adelaideoval:Venue{name:"Adelaide Oval",city:"Adelaide"}),
(saxtonoval:Venue{name:"Saxton Oval",city:"Nelson"}),
(universityoval:Venue{name:"University Oval",city:"Dunedin"}),
(manukaoval:Venue{name:"Manuka Oval",city:"Canberra"}),
(regionalstadium:Venue{name:"Wellington Regional Stadium",city:"Wellington"}),
(gabba:Venue{name:"The Gabba",city:"Brisbane"}),
(scg:Venue{name:"Sydney Cricket Ground",city:"Sydney"}),
(edenpark:Venue{name:"Eden Park",city:"Auckland"}),
(waca:Venue{name:"WACA",city:"Perth"}),
(mclean:Venue{name:"McLean Park",city:"Napier"}),
(bellerive:Venue{name:"Bellerive Oval",city:"Hobart"})
//Creating Relationship between Matches and Venues
create (match1)-[:played_at]->(hagley),
(match2)-[:played_at]->(mcg),
(match3)-[:played_at]->(seddon),
(match4)-[:played_at]->(adelaideoval),
(match5)-[:played_at]->(saxtonoval),
(match6)-[:played_at]->(universityoval),
(match7)-[:played_at]->(manukaoval),
(match8)-[:played_at]->(saxtonoval),
(match9)-[:played_at]->(regionalstadium),
(match10)-[:played_at]->(hagley),
(match11)-[:played_at]->(gabba),
(match12)-[:played_at]->(universityoval),
(match13)-[:played_at]->(mcg),
(match14)-[:played_at]->(hagley),
(match15)-[:played_at]->(manukaoval),
(match16)-[:played_at]->(gabba),
(match17)-[:played_at]->(universityoval),
(match18)-[:played_at]->(mcg),
(match19)-[:played_at]->(scg),
(match20)-[:played_at]->(edenpark),
(match21)-[:played_at]->(waca),
(match22)-[:played_at]->(regionalstadium),
(match23)-[:played_at]->(gabba),
(match24)-[:played_at]->(manukaoval),
(match25)-[:played_at]->(mclean),
(match26)-[:played_at]->(waca),
(match27)-[:played_at]->(saxtonoval),
(match28)-[:played_at]->(waca),
(match29)-[:played_at]->(edenpark),
(match30)-[:played_at]->(bellerive),
(match31)-[:played_at]->(mclean),
(match32)-[:played_at]->(scg),
(match33)-[:played_at]->(adelaideoval),
(match34)-[:played_at]->(seddon),
(match35)-[:played_at]->(bellerive),
(match36)-[:played_at]->(regionalstadium),
(match37)-[:played_at]->(seddon),
(match38)-[:played_at]->(scg),
(match39)-[:played_at]->(edenpark),
(match40)-[:played_at]->(bellerive),
(match41)-[:played_at]->(mclean),
(match42)-[:played_at]->(adelaideoval)MATCH(india:Team{name:"INDIA"})-[r1:played]->(m:Match)-[r2:played_at]->(venue:Venue)
RETURN venue.name AS Ground,venue.city AS City;MATCH (venue:Venue{name:"Hagley Oval",city:"Christchurch"})<-[:played_at]-(m:Match),
(teamA:Team)-[:played]->(m)<-[:played]-(teamB:Team)
RETURN teamA,teamB,m,venue;MATCH (ireland:Team{name:"IRELAND"})-[:played]->(m:Match) ,
(teamB:Team)-[:played]->(m)-[:played_at]->(venue:Venue)
where m.date='2015-02-25'
RETURN ireland.name AS TeamA,teamB.name AS TeamB,venue.name AS Ground,venue.city AS City,m.date AS Date;MATCH (m:Match) where m.date='2015-03-08'
MATCH (teamA:Team)-[:played]->(m)<-[:played]-(teamB:Team),(m)-[:played_at]->(v:Venue)
RETURN teamA,teamB,m,v;MATCH(india:Team{name:"INDIA"})-[r1:played]->(m:Match)-[r2:played_at]->(venue:Venue)
RETURN DISTINCT venue.name AS Ground,venue.city AS City ;MATCH(ind:Team{name:"INDIA"})-[:played]->(m:Match)<-[:played]->(pak:Team{name:"PAKISTAN"}),(m)-[:played_at]->(venue:Venue)
RETURN ind.name,pak.name,m.date,venue.name,venue.city ;MATCH(ind:Team{name:"INDIA"})-[:played]->(m:Match)<-[:played]->(team2:Team)
RETURN team2.name as OpponentMATCH (ind:Team{name:"INDIA"})-[r1:played]->(m:Match)<-[r2:played]-(pak:Team{name:"PAKISTAN"}),
(u1:Umpire)-[r3:umpired_in{role:"field umpire"}]->(m)<-[r4:umpired_in{role:"field umpire"}]-(u2:Umpire)
RETURN ind,pak,m,r1,r2,r3,r4,u1,u2;MATCH (m:Match)-[r1:played_at]->(venue:Venue)
WITH venue,count(venue) AS matches
ORDER BY venue.name ASC ,matches DESC
RETURN venue.name AS Ground,matches as Totalmatch(m:Match) return count(m) as Total_Pool_Matches;I will add distances between venues and try to find out the total travelling that teams have to do.
Created by Mahtab Alam:
really cool graphgist with a good story and use-cases
could you add a cricket picture up front?
and hide the setup query with
//hideso the reader doesn't have to scroll across it.thanks a lot