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 players participating in each game for each team, the positions they play in that game, and the result of the game.

i. Design an E-R schema diagram for this application

To map this sports league system:

• 1. TEAM has players (PLAYER).
• 2. TEAM plays games (GAME).
• 3. PLAYER participates in a GAME at a specific Position.
• 4. GAME stores the overall Result.

  (Team_ID)                              (Game_ID)  (Result)

      |                                      |         |

  [ TEAM ] ———- < PLAYS > ———- [ GAME ]

      |

 < HAS_PLAYERS >

      |

 [ PLAYER ] ——— < PARTICIPATES > — [ GAME ]

   (Player_ID)              |

                        (Position)

Explanation:

• TEAM and GAME are connected via the < PLAYS > relationship.
• TEAM and PLAYER are connected via < HAS_PLAYERS > (1:N relationship).
• PLAYER and GAME are connected via the < PARTICIPATES > relationship. The attribute Position is attached directly to < PARTICIPATES > because a player can play different positions in different games.

ii. Map the E-R diagram into a relational model

To convert this ER diagram into relational tables, we create primary keys, foreign keys, and relationship tables:

1. 1. Table: TEAM -> Team_ID (Primary Key), Team_Name
2. 2. Table: GAME -> Game_ID (Primary Key), Game_Date, Result
3. 3. Table: PLAYER -> Player_ID (Primary Key), Player_Name, Team_ID (Foreign Key referencing TEAM)

4. Table: PARTICIPATES -> Player_ID (Foreign Key), Game_ID (Foreign Key), Position. Composite Primary Key: (Player_ID, Game_ID)