[Assignment Spec]   [SQL Schema]   [Examples]   [Testing]

Football/soccer/whatever is touted as the World Game†, and there are a plethora of sites dedicated particularly to international competition. Consider the following database design that could sit behind one such site:

Note that we have omitted the primary keys in the above diagram. They are all implemented as integer attributes in the schema.

An SQL schema based on this design:

-- Football/Soccer Schema

-- These domains are not actually defined in the database
-- They appear as constraints in the relevant tables

-- Time in minutes since game started
create domain GameTime as integer check (value between 0 and 90);

-- Cards are warnings given to players for naughty acts
-- Yellow card = final warning;  Red card = send off
create domain CardColour as varchar(6) check (value in ('red','yellow'));

-- Tables

create table Matches (
        id          integer,
        city        varchar(50) not null,
        played_on   date not null,
        primary key (id)
);

create table Teams (
        id          integer,
        country     varchar(50) unique not null,
        primary key (id)
);

CREATE TABLE Involves (
    match   integer     not null,
    team    integer     not null,
    is_home boolean     not null,
    primary key (match, team),
    foreign key (match) references Matches(id),
    foreign key (team)  references Teams(id)
);

create table Players (
        id          integer,
        name        varchar(50) not null,
        birthday    date,
        member_of   integer not null,
        position    varchar(20) not null,
        primary key (id),
        foreign key (member_of) references Teams(id)
);

create table Goals (
        id          integer,
        scored_in   integer not null,
        scored_by   integer not null,
        time_scored  GameTime not null,
        rating      varchar(20),
        primary key (id),
        foreign key (scored_in) references Matches(id),
        foreign key (scored_by) references Players(id)
);

create table Cards (
        id          integer,
        given_in    integer not null,
        given_to    integer not null,
        time_given  GameTime not null,
        card_type   CardColour not null,
        primary key (id),
        foreign key (given_in) references Matches(id),
        foreign key (given_to) references Players(id)
);

Some assumptions made in developing the above schema:

• players play for a single country throughout their career
• players are categorised into a particular position in the team
• what position each player plays in a given match is not recorded
• matches are played all over the world
• each match involves teams from two countries
• no match runs for more than 90 minutes
• we need to record who scored each goal and when in the match it was scored
• goals are rated by a "panel of experts" to determine how good they are
• if players do something naughty during the game, they are "shown a card"
• a yellow card means that they did something relatively minor
• a red card means that they did something bad and are sent off
• players will not score goals into their own team's goal; all goals are goals into the opponent team's goal

You should familiarise yourself with this schema before proceeding to solve the queries. It would also be useful to examine the database contents to ensure that you understand what all of the data represents.

† even though Aussie Rules is clearly a much better game ...