As a basketball lover and an EECS484 enthusiast, you want to design a database to model...

Transcribed Image Text:

As a basketball lover and an EECS484 enthusiast, you want to design a database to model national basketball teams. This includes the teams themselves, the games they play, the players and the referees. In the design, you want to capture the following: For each basketball team, you want to store their unique team ID, name, main stadium, their home state, and the number of championships they've won. Each player on each team will have a unique ID, name, age, and jersey number. O Each team has at least one player and each player can belong to only one team. It is possible that a player does not belong to any team. Each manager on each team will have a name and age. Each manager manages only one team and each manager's name identifies them uniquely from among the team's administrators. If a team is deleted, you need not keep track of its manager any longer. You want to store data on all basketball games: O Each game must have exactly one away team and one home team O Each game has a game ID, date, and a final result. game ID is unique to each different game. O O The players that participated in a game (each game must have more than one player) must be stored, as well as: The number of points the player scored, and their total time playing in that game Players can substitute for other players during each game, which should be recorded as well: In one game, one player may substitute in or out. In one game, the same substitution can occur only once. Any number of substitutions can be made per game. A player cannot substitute for themselves. You also want to capture information about the referees for each game to analyze their performance and potential biases, so you'd also like to capture the following: O Each game has multiple referees. Each referee should have an ID, name, age, and years of experience. The IDs for different referees are unique. O There is exactly one main referee and one or more assistant referees per game. Each referee works as a main referee in at least one game, and as an assistant referee in at least one game. O O A referee in one game works as either main referee or assistant referee but not both. 1) Design an ER diagram that best reflects the constraints described above. You can start with the ER diagram below. Always follow the constraints described in the questions, even if you don't think they make sense in the real world. Try to reflect as many constraints as possible. Some constraints may not be possible to express in an ER diagram. Do not make any arbitrary assumptions or modify the constraints given in the problem statement. Make sure that all your entities, attributes and constraints are drawn (again, state assumptions clearly if they are not stated in the question). Please do not modify any given entities and attributes. (32 points) HINT: To complete this question, you may want to add more entities, relations and attributes. You may want to add constraints in the given diagram as well. Manages Team Belongs Player Manager Participates in Referee Is Assistant Ref Game Is Main Ref 2) It is possible that some constraints mentioned above cannot be enforced by the ER diagram features we learned in lecture. State these constraints. (4 points) As a basketball lover and an EECS484 enthusiast, you want to design a database to model national basketball teams. This includes the teams themselves, the games they play, the players and the referees. In the design, you want to capture the following: For each basketball team, you want to store their unique team ID, name, main stadium, their home state, and the number of championships they've won. Each player on each team will have a unique ID, name, age, and jersey number. O Each team has at least one player and each player can belong to only one team. It is possible that a player does not belong to any team. Each manager on each team will have a name and age. Each manager manages only one team and each manager's name identifies them uniquely from among the team's administrators. If a team is deleted, you need not keep track of its manager any longer. You want to store data on all basketball games: O Each game must have exactly one away team and one home team O Each game has a game ID, date, and a final result. game ID is unique to each different game. O O The players that participated in a game (each game must have more than one player) must be stored, as well as: The number of points the player scored, and their total time playing in that game Players can substitute for other players during each game, which should be recorded as well: In one game, one player may substitute in or out. In one game, the same substitution can occur only once. Any number of substitutions can be made per game. A player cannot substitute for themselves. You also want to capture information about the referees for each game to analyze their performance and potential biases, so you'd also like to capture the following: O Each game has multiple referees. Each referee should have an ID, name, age, and years of experience. The IDs for different referees are unique. O There is exactly one main referee and one or more assistant referees per game. Each referee works as a main referee in at least one game, and as an assistant referee in at least one game. O O A referee in one game works as either main referee or assistant referee but not both. 1) Design an ER diagram that best reflects the constraints described above. You can start with the ER diagram below. Always follow the constraints described in the questions, even if you don't think they make sense in the real world. Try to reflect as many constraints as possible. Some constraints may not be possible to express in an ER diagram. Do not make any arbitrary assumptions or modify the constraints given in the problem statement. Make sure that all your entities, attributes and constraints are drawn (again, state assumptions clearly if they are not stated in the question). Please do not modify any given entities and attributes. (32 points) HINT: To complete this question, you may want to add more entities, relations and attributes. You may want to add constraints in the given diagram as well. Manages Team Belongs Player Manager Participates in Referee Is Assistant Ref Game Is Main Ref 2) It is possible that some constraints mentioned above cannot be enforced by the ER diagram features we learned in lecture. State these constraints. (4 points)