Question

a)

Design an E-R diagram for keeping track of the scoring statistics of your favorite sports team.

You should store the matches played, the scores in each match, the players in each match, and individual player scoring statistics for each match.

Summary statistics should be modeled as derived attributes with an explanation as to how they are computed.

b)

Construct appropriate relation schemas for the E-R diagram (ex. customer(name, ID, birth))

Answer 1

Question a)

Answer: The E-R diagram would keep track of the scoring statistics of my favorite sports team. Details about the match played, scores in each match, the players in each mathc and individual score of the players. Now, score is related to both the player and the match. Thus, attribute score should be used along with the relation so that it can be used both for the match and player.

There should be two tables in this scenario. One is Match , which stores the details of the match and the other is Player, stores the details of the player.

The required ER-Diagram is given below:

Here, Season_score attribute of Player table is derived attibute. It is derived depending on the date of the match. For example, let each month corresponds to a new season. Thus after every month, the Season_score attribute will be refreshed automatically for each players. So this is a derived attribute.

In Match table, Match_id is unique for each match, which is the primary key of Match table. Other details like Stadium, where the match is played, date of the match, opponent and score details are also there. On the other hand Player table stores the name of each player and Player_id, in case two players having the same name. Also the age and derived attribute is there. Lastly, score of each players is shared with each match, thus Score attribute is used in the relation "Played".

Question b)

Answer: The relational schema for the above ER-Diagram is given below:

Match (Match_id, Stadium, Opponent, Own_score, Opp_score, Date, Score)
Player (Player_id, Name, Age, Season_score, Score)