Database Assignment 2

Due date given on course webpage.

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 follows:

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 give the Relation Algebra for queries 1. and 2. below. Again, there are problems for plain text files:

Greek characters

for π use PROJ, for σ use SEL, for ⋈ use JOIN, for τ use SORT

To represent tree structure

use (parentheses), or

use / | \ to form the tree

Example

		PROJ(code, title, credits)
		  |
		JOIN(name=prof)
		/		\
	SEL(prof='Jensen')       \
	   |			  \
	COURSE			 PROF

Database Assignment 3

Due date given on course webpage.

Also write SQL queries for the following: (If not possible, reconsider your design)

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

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

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

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

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

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