Football database

National Football Championship. Information on a single season of one team. Each game lasts exactly 90 minutes.

Tables

Players:

Players (player_id, first_name, last_name, nickname, citizenship, dob, role)

player_id -uniform number,
nickname - name on the uniform,
citizenship (NULL for native players),
dob – date of birth,
role - position;

Games:

Games(game_id, team, city, goals, game_date, own)

team - opposing team,
city -city where the match took place (NULL if it was a home game),
goals -goals scored by the opponent, including own goals shot by own players (NULL if not scored),
game_date - match date/time,
own - own goals by opposing team (null if there were none);

Participation in games:

Lineups(start, game_id, player_id, time_in, goals, cards)
start - 'B' - the player was in the starting lineup, 'S' - he started the match on the bench;
time_in - playing time in minutes (NULL if the player did not enter the game);
goals - the number of goals the player scored in the match (NULL if he didn't score any goals);
cards – penalty cards: 'Y' (yellow), 'Y2' (two yellow cards), 'R' (direct red), 'YR' (yellow+direct red), NULL (no cards shown).

Database schema explanatory notes

1. The role corresponds to the player’s position on the pitch. The database uses exclusively the following roles: ‘DEFENDER’, ‘MIDFIELDER’, ‘FORWARD’, and ‘GOALKEEPER’.
2. The competition is held as a round-robin tournament, with each team playing against each other competitor twice – at home and away, once during the first half-season and once during the second one.
3. A player is sent off the pitch for two yellow cards or a direct red card.
4. A penalty card can be issued to a player on the substitutes’ bench, i.e. to a substitute player off the pitch. However, if the player given a card participated in the game it’s assumed he received it while being on the pitch.
5. The half-time break in a match is 15 minutes long.
6. If not stated otherwise, it’s assumed matchdays are played in number order.

Association football

General description

Association football, or simply football, or soccer, is a team sport played between two teams with the players trying to move the projectile – a spherical ball – into the opponent's vertical frame (so-called goal) using any parts of their bodies except for arms and hands. When the ball enters the goal it’s also called a goal. Simplifying somewhat it can be said that the player of the attacking team who was the last one to touch the ball before it entered the opponent's goal scores a goal. If the ball went into the goal last touched by the member of the defending team it's said the respective player scored an own goal. Such goals are also credited to the attacking team. The objective of the game is to score more goals than the opponent. A record containing the number of goals scored by each of the opposing teams with a delimiter between them is called a scoreline. E.g., 3:1 means the team listed first scored three goals, and the second one a single goal. One could also say, the first team won against the second one (or beat/defeated the second team) three to one. If both teams scored an equal number of goals it’s called a draw or tie.

Per each team, 11 players are on the pitch simultaneously, having different roles or specialties: forwards (attackers or strikers) whose primary focus is scoring goals on behalf of their team; defenders (full-backs or just backs) with the main task of preventing the opposing team from scoring; and midfielders (half-backs) acting as a connecting link between the attack and the defense of their team. All these players are collectively called outfield players. Note that the roles of outfield players are arbitrary to a large extent, since the actual positioning of the players depends on the formation chosen for the game and, moreover, outfield players can change positions during the match. The fourth role in each of the teams is the goalkeeper (goalie, keeper). Each team has just one such player, and unlike the others they can use their hands and arms to defend their goal (in a certain zone around it called penalty area). An arbitrary replacement of the goalkeeper by an outfield player during the game is not allowed. Note that this restriction applies specifically to the goalkeeper’s position on the pitch, not to the role itself.

A match consists of two halves of 45 minutes each. There is a 15 minutes long break called half-time between the halves for the players to rest. In real competitions, the duration of the match can be extended to compensate for any stops (e.g., when a player gets injured and needs medical attendance), but in the educational database "Football (soccer) club" all games are exactly 90 minutes long.

Championship organization. Arrangement of games

To participate in the championship, any team has to meet certain requirements, one of which is owning a stadium. Games played by a team in their own stadium are called home games (with this team being the home team or host); matches played in the opponents' stadiums are referred to as guest games, away games or road games, and the team as the guest/road/visiting/away team.

The championship is held in double round-robin format, i.e. each team plays against each other team twice – once home and once on the road. For each set of matches called matchday, pairs of teams are formed to play against each other. All games of a matchday are played on a single day or a few adjacent days, usually at the end of the week (although there are no strict rules in this regard). The next matchday, teams face other opponents, and so on, until every team meets each other contest participant. The collection of games with each team playing against each other participant exactly once is called half-season. After that, the teams play their second matches against the opponents they met in the first half-season (although probably in a different order), with the team that was the visiting one becoming the host, and vice versa. Matchdays are enumerated sequentially starting at one. Usually, there are no rescheduled games in the educational database, i.e. their chronological order matches the enumeration of the matchdays. If rescheduling has to be taken into account nonetheless, this will be mentioned in the exercise – either directly or implicitly (say, by referring specifically to the date/time of an event rather than to the matchday number).

Team's roster for championship

Though in a single game, no more than eleven players can be on the pitch simultaneously per team, it's impossible to get through a championship (typically lasting 8 to 10 months) with such a minimum squad. On the other hand, official tournaments don't allow the other extreme either – like, using a completely new set of players for every subsequent game or players freely changing teams during the season. Therefore, before the start of the tournament each participating team submits to the organizers a so-called roster – a list of footballers allowed to play for said team. First of all, a roster contains the players' first and last names, as well as their numbers (the respective number is printed on the player's uniform); in addition, it contains the players' nicknames or squad names (also placed on the uniform). In most cases, the player's last name is used as the squad name, but there are exceptions (e.g., if there are players sharing the same surname in the team their initials are added to the squad name; besides, some players prefer using a moniker not matching any of their names. One of the most prominent examples is "The King of Football" Pelé, whose real name was Edson Arantes do Nascimento). Note that the number is assigned to the respective player for the whole season. In modern football, squad numbers are assigned rather arbitrary or are chosen by the players themselves; number one is usually reserved for the main goalkeeper of the team, but you can't rely on this rule 100 percent. Besides, the roster contains the citizenship of the players (because of restrictions on foreign footballers in several national championships), their date of birth, and role. Some tournaments require additional data in the roster – like, the height and weight of the players. In the educational database "Football (soccer) club", the table Players corresponds to the team's roster. In real championships, the clubs usually can add to and remove players from the roster within specific timeframes and adhering to certain regulations, but in the educational database the roster is deemed to be fixed for the whole tournament for the sake of simplicity.

Game squad. Substitutions

In addition to the championship roster, the club submits a squad list before each game. It contains players present on the pitch from the beginning of the match (so-called starters who form the starting lineup) and substitutes (subs) or bench players (they can enter the pitch in exchange for another player in case of injury or on the team manager's decision). The number of possible substitutions as well as the number of bench players is limited and depends on the regulations of the given tournament. The overall number of bench players normally is greater than the number of allowed substitutions to make it possible for the team to vary the setup depending on the game situation. Return substitutions (when a player who has been substituted before re- enters the game) are not allowed in official matches, but it's possible for a sub to be substituted in their turn. In case a player can't continue the match (e.g. due to an injury) and all substitutions are used up, they leave the pitch without replacement.

Refereeing. Penalties. Record of the match

Any official match is served by a referee crew led by the main referee on the pitch. The referee crew does the timekeeping of the match, records its main events, enforces the rules of the game and penalizes players for violating them. For most severe offenses the players receive penalty cards: a red one, which means the player is sent off (removed from the game without replacement); or a yellow one, which is "the last caution" of sorts. If, after being shown a yellow card, the player commits another serious violation they'll be sent off. Thus, there are three ways for an offender to be dismissed from the game: by being shown a single red card (so-called direct red card); a yellow card, followed by a direct red one; two yellow cards. Note that cards can be received not only by players on the pitch but also by substitutes on the bench. If a substitute is sent off, the number of active players on the pitch doesn't change, but the offender no longer can enter the game. Sanctions imposed on players for accumulating penalty cards during the season depend on the regulations of the given tournament. But it can be considered a universal rule a player sent off can't be on the squad for the next game (you also could say, they are suspended or disqualified for one match).

During the match, its record is kept, containing the squad details for both opponents, and the log of all important game events – substitutions, showing penalty cards, goals and the players who scored them (the scorers), including the minute of the game the respective event happened at. The Lineups table represents a simplified model of the match record in regard to one of the opposing teams.

Some football terms

Brace – two goals in a game scored by a single player.

Clean sheet (to keep a clean sheet) – a match the team didn't concede any goals. Note that if a goalkeeper didn't play the whole game and goals were conceded by their team when they weren't on the pitch, the goalie would keep a clean sheet but their team wouldn't.

Hat-trick, hat trick – three goals in a game scored by a single player. Specific types are the natural hat-trick, when the player scores three consecutive goals in a match without any other player scoring in between, and the flawless hat-trick, which is a natural hat-trick scored within a single half.

Haul, poker – four goals in a game scored by a single player.

Glut – five goals in a game scored by a single player.

Open the score/the scoring – score the very first goal in a game.

Streak (winning/draw/unbeaten etc. streak) – a sequence of consecutive matches of a team with one or two possible outcomes missing.