Football
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
- The role corresponds to the player’s position on the pitch. The database uses exclusively the following roles: ‘DEFENDER’, ‘MIDFIELDER’, ‘FORWARD’, and ‘GOALKEEPER’.
- 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.
- A player is sent off the pitch for two yellow cards or a direct red card.
- 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.
- The half-time break in a match is 15 minutes long.
- If not stated otherwise, it’s assumed matchdays are played in number order.