Question

Consider the following relational model for a basketball league:

• Player (PlayerID, PName, Position, TeamID)
• Team (TeamID, TeamName, Venue)
• Game (GameNo, Date, Time, HomeTeamID, AwayTeamID)
• Record (GameNo, PlayerID, Points, Rebounds, Assists)

In this basketball league, each team has a unique name and each player plays for only one team. One team has at least 10 players. Two teams (home team versus away team) participate in each game at home team’s venue. Each team meets all other teams twice (i.e., double round-robin tournament), one time as home team and the other time as away team. For each game, the league records points, the number of rebounds and the number of assists for each player. If a player did not play for a game, there is no record for this player in that game.
Question 1a.Draw an ER-diagram model for the basketball league. (The relationship between two entities should be 1-to-1, 1-to-many, many-to-1 or many-to-many.)[20 marks]

1.All relationships are total participations.

2.PlayerID, TeamID and GameNo are unique attributes.

3.The relationship between Player and Team is 1-to-many relationship.

4.The relationship between Team and Game is many-to-many relationship.

Question 1b. Write a SQL to retrieve the distinct TeamID and TeamName of teams that have at least one game where the team participated as the home team getting more points than all the games where that team participated as the away team. [20 marks]

Question 2. Write a SQL to retrieve the PlayerID, PlayerName and Points of the players who achieve the highest point in a game. [20 marks]

Question 3. Write a SQL to retrieve the GameNo, TeamName, total points and total number of rebounds of a team (either home team or away team only) for each game and the team has the total number of rebounds in that game larger than 30. For a game, both teams, only home team, only away home, or none of both teams has total number of rebounds larger than 30 in that game. [20 marks]

Answer 1

1(a)

1(b)
select distinct TeamID,TeamName from Team where TeamId in
(select HomeTeamId in Game where Game.HomeTeamId = Team.TeamId and GameNo in
(select GameNo from Record where Game.GameNo = Record.GameNo and Record.Points >
(select max(Points) from Record where GameNo in(select GameNo from Game where Game.GameNo = Record.GameNo and AwayTeamId in (select TeamId from Team)))
);

(2)
select Player.PlayerID, Player.PlayerName, Record.Points from Player,Record where Player.PlayerId = Record.PlayerId and Record.Point > max(Record.Points);

(3)

(// FOR HOMETEAM ONLY)

select Game.GameNo,Team.TeamName,sum(Record.Points)as totalPoints, sum(Rebounds) as totalRebounds from Game,Team,Record where Game.HomeTeamId = Team.TeamId and totalRebounds > 30;

(// FOR AWAYTEAM ONLY)

select Game.GameNo,Team.TeamName,sum(Record.Points)as totalPoints, sum(Rebounds) as totalRebounds from Game,Team,Record where Game.AwayTeamId = Team.TeamId and totalRebounds > 30;