This chapter aims to explain how to construct a round robin competition model using the group A games of the FIFA World Cup 2018 for illustration. The participation of teams in groups is an example of a many-to-many linkage. A group may have none, one or many participating teams and a team may have participated in, be participating in or be eligible to participate in none, one or many groups.
The decomposition of that single many-to-many link into 2 one-to-many links is achieved by the creation of an intermediate entity, the group team. It has just two attributes and they are also foreign keys. The primary key of the group team type of entity is composed of the two foreign keys referencing the group and team entities. Similarly, a league team type of entity represents the participation of a team in a league season.
For group games, the score will only involve goals during 90 minutes of play, without the option of extra time or a penalty shoot-out. A reference to the group game is the only attribute specific to a group goal entity type. The attributes common to all goals in the extended model are an id, the elapsed time, the beneficiary (i.e. either team one or team two) and the type of goal (open play, penalty and own goal). Group goals scored during stoppage time are recorded as being scored in the 45th or 90th minute.
The 11 sample group A goals shown below are examples of goals that may be scored during the group phase of Russia 2018. If the scorer of a goal is required, a reference to the individual can be added for each goal. If team sheets are also recorded, the beneficiary can be deduced using the team sheets, the scorer and the type of goal. The beneficiary attribute is then unnecessary and should be removed (Occam’s razor). That’s all the data required in this model to produce the group game results and group points tables.
An example of a data entry form to record the above data is shown below. If required, the id is can be generated automatically and does not necessarily have to be an integer. The role of the beneficiary can be ascertained from the drop-down list of the game details.
The goal data is used to obtain the tallies of goals scored by each team during the game. There will be no tallies for teams who do not score. The tallies for team one and team two (group tally one and group tally two respectively) are generated separately.
To produce group game results, I have used another query to bring together the two tallies and to record a zero for the teams that did not score during a game. In MS Access this is done by using the null zero function; Nz(), which returns a zero if there are no goals scored by a team. The results are only generated when the game status has been set to “complete data”. I have also generated a outcome attribute for each game to denote if it is a group game without a known outcome, (0), a win for team one, (1), a win for team two, (2) or a draw, (3).
An IF, THEN, ELSE programming construct is also required to deduce the scores and the game outcomes for completed games.
The above information is used to produce a list of results for the group and to generate the points table.
The group points table is generated by combining two other tables. Just as the tallies were generated for teams one and two separately, group points tables are generated separately for teams in the role of team one and for teams in the role of team two. For an English Premier League season, after 380 league games, the team one points table will have the statistics for the 19 home games of each team and the team two points table the 19 away games statistics. So, while the separate tables may be of interest during a league season or even a UEFA Champions’ League group phase, they are just a part of the process in the context of a FIFA World Cup tournament.
The fixture list published for the WC 2018 tournament has ensured all four teams in group A play at least one game each as team one and as team two. In group phases where a team is only listed as either team one or team two only, the null zero function, Nz(), will be additionally required when adding the two tables. The addition of the two points tables is shown below.
The final rankings based on the number of points scored at the end of the group phase will be used to specify the criteria for qualification for the subsequent single-leg knock-out phase. If teams are drawn on points, other criteria such as the goal difference, the number of goals scored by a team etc. will be applied. For the FIFA World Cup, these rules are published and should have been included but have been ignored here for the sake of simplicity (and because of my indolence). It is worth bearing in mind that if teams cannot be separated by the published list of criteria, a coin toss may be required.
The single-leg knock-out games for Round 2 are listed below. The qualifying teams for Round 2 will be the winners and the runners-up from the 8 groups. To generate the sample data below, I have assumed that the qualifiers will be the two highest ranked teams from each group, using the FIFA December 2017 ranking. The data model and the data specifying the qualifying teams are shown below.
