All the data required, i.e. the attributes of the entities, are specified as one of three data types; integers, variable length strings of characters and dates and times. The ids and elapsed times will both be integers. The start time of a game is the only attribute that will use the date/time format. The variable length string data type is restricted to a maximum of 45 characters, a length adequate for recording names and the option choices in this exercise.
There are 5 specialised data types for use when presenting the available options during the data collection process. Winner and loser are the criteria for qualification from knock-out fixtures. The descriptions for each option type are restricted to a maximum length of 45 characters because they are sub-types of the varchar45 data type and inherit the constraint.
The linked organisational units above are all named entities and are derived from a common ancestor with two attributes, an id and a name. As already seen, three entity types; competitions, teams and venues are structured identically and only have two attributes (id and name) and no foreign keys unlike the other entity types. Head to heads have additional foreign keys to the two participating teams as do matches. This method of recording the participants for matches and head to heads ensures that the data about the two participating teams is only recorded once and not recorded separately for each game.
To recap, the secondary key is the device used to specify that the components of a competition must have names that are unique but only locally within a competition, tournament or phase. The above entity types are sufficient to describe the organisation of the three types of competitions. The entity types modelling round robin participation, league and group teams are shown below.
Other than head to head games and multi-leg games, game participants are recorded as attributes of the relevant game type entity. The game super type has three attributes; status, start time and venue. League, group, single-leg and apex games are linked to teams in the roles of team one or team two. The game super type has no primary key defined because one type of game, an apex game, can be uniquely identified using one of the apex options (the apex type attribute) combined with the tournament id and therefore does not need an id as a primary key. Games other than the apex games require an id as the primary key in addition to the organisational link.
It should be apparent what the qualification paths are from the published rules of a competition. The qualification paths from group and single-leg phases for the World Cup 2018 had been specified earlier using either the position of a team at the end of a group phase or the winners or losers of single-leg games. The path is uniquely identified by using the combination of the single-leg game reference and the role i.e. whether the qualifier will be team one or team two. The unlikely path is greyed out.
The roles, team one or team two are assigned according to the rules of the competition, sometimes a lottery. A qualifier for a single-leg phase usually qualifies in one of three ways; by rank at the end of a group phase (FIFA World Cup), by winning another single-leg knockout game (FIFA World Cup) or multi-leg knockout match (FA Cup). The qualification path to a league season is provided below.
The goal super type has 4 common attributes including an assigned unique identifier. The elapsed times for goals scored during extra time for knock out games will be reported as being between 91 - 105 and 106 - 120 minutes. If the deadlock is not broken during extra-time a penalty shoot-out is required to get a result as was shown in Chapter 4.
