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: Head coach Players assigned Teams Coaches assists home visitor home_points game visitor_points date 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

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: Head coach Players assigned Teams Coaches assists home visitor home_points game visitor_points date 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