A database is being constructed to keep track of the teams and games of a sports league. A team has a number of players, not all of whom participate in each game. It is desired to keep track of the players participating in each game for each team, the positions they played in that game, and the result of the game. Try to design an ER schema diagram for this application, stating any assumptions you make. Choose your favorite sport (soccer, Cricket or Tennis).
Some of the assumptions can be as followed:
- Each game in the schedule is identified by a unique Game#, and a game is also identified uniquely by the combination of Date, starting Time, and Field where it is played. The Performance attribute of PARTICIPATE is used to store information on the individual performance of each player in a game.
- A player would be laying for a team in a particular game which may be played on home ground or away.
- List all what you identify for your favorite Game….
Answer the following Questions:
- List and explain the Entities in the Scenario.
- List all the attributes that you feel necessary to represent an Entity.
- List and explain all the relationship types (for example Binary, Ternary ...)
- Draw the ER diagram. (You can use any software MS-Word (flow chart tools), Irwin, Rational Rose).
- Give the Relational schema for the Database that you have designed in question #4.
- Identify all the keys needed and explain why you have selected a particular candidate key as primary key.
- List any Specialization and Generalization Hierarchies.