[Assignment Spec] [SQL Schema] [Examples]

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 numeric attributes in

An SQL schema based on this design:

-- Football/Soccer Schema

-- Time in minutes since game started
create domain game_time 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 card_colour 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),
	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 game_time 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  game_time not null,
	card_type   card_colour 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

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 ...