COMP3311 20T3 The University of New South Wales Database Systems
COMP3311 Database Systems 20T3
[Instructions] [Notes] [Database] [Course Website] [Cheat Sheets] [Q1] [Q2] [Q3] [Q4] [Q5] [Q6] [Q7] [Q8] [Q9] [Q10] [Q11] [Q12]
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
— 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 120);
— 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,
playedOn date not null,
primary key (id)
);
create table Teams (
id integer,
country varchar(50) not null,
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 120 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 …
End of Notes
);
primary key (id)
create table Involves (
match integer not null,
team integer 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,
memberOf integer not null,
position varchar(20),
primary key (id),
foreign key (memberOf) references Teams(id)
);
create table Goals (
id
integer,
integer not null,
integer not null,
scoredIn
scoredBy
timeScored GameTime not null,
rating varchar(20),
primary key (id),
foreign key (scoredIn) references Matches(id),
foreign key (scoredBy) references Players(id)
);
create table Cards (
id
);
integer,
integer not null,
integer not null,
GameTime not null,
CardColour not null,
givenIn
givenTo
timeGiven
cardType
primary key (id),
foreign key (givenIn) references Matches(id),
foreign key (givenTo) references Players(id)