Database Assignment 3

Due Monday 1 October 2012

I have produced an E-R model, and your task is to convert it to a relational schema, and some appropriate queries.

The Soccer League

There is a local soccer league in Lennoxville, with 4 teams (named Jaguars, Lions, Tigers and Gazelles) Girls aged 10-14 can sign up to join the league, later they are assigned to teams. Each team has a head coach, who is assisted by some other adults. Note that assistants may help more than one team.

One game can be played per day on the single field that is available, at Atto-Beaver park. A schedule will be set up, the two teams that play on a day will be designated “home” who get to wear colourful jerseys (shirts), while the other will be “visitor” and wear white jerseys. After a game is played, each team will have 1 added to its number of wins, losses or ties, depending upon the outcome of the game.

The attributes for each entity are:

Players:	name, address, phone, birthdate

Teams:	name, wins, losses, ties	-- counts initially 0

Coaches:	name, phone, year_joined

The E-R diagram is shown below.

Design the relational schema, indicating primary and foreign keys, and any referential integrity or other constraints you believe are necessary. For instance, a team cannot play itself!

Also write SQL queries for the following:

A list of names and phone numbers of all the players assigned to the Gazelles.

The schedule for the Jaguars, ordered by date. (note: the DATE type in SQL can be ordered)

The Tigers' head coach and assistants, their names and phone numbers (one or 2 queries).

The Lions have today beaten the home team Tigers by a score of 3-2. Update the database accordingly.

A list of all games won by the Lions, with the score, team names, and date.

The number of players on each team, and the birthdates of the oldest player and the youngest player (on each team). I expect a single query, using GROUP BY

(optional) Add to the last query also the average age, in days, of the players on each team. Note that current_date - birthdate is the age of a person in days, an integer that can be averaged.

Players Teams Coaches assigned Head coach assists game date home_points visitor_points

home visitor

Lin Jensen

2005-02-07T18:25:24

Lin Jensen