Attempt to reduce the basketball league example to a set of tables.
Here is the E-R diagram for a subset of the basketball example:
Assuming we are storing data for only one league.
Commonly Used Strategy – introduce an ID number (or surrogate key) if you would otherwise have a composite key. If you don’t need to refer to records with a composite key from a different relation, you do need to introduce a surrogate key – see PlayedIn below.
|
Player: ID Name Number Height
Team |
= 0 ---------- Plays On / has players ------- 0 1 Adding attribute Captain. Unique forces each team to have a different captain.
Min cardinality 0 on many side, otherwise have chicken and egg problem – unable to enter a player w/o a team and unable to enter a team w/o a player. Removing NOT NULL on Captain allows us to enter a Team, then enter Team Captain. See text p. 231 for more discussion. |
Team: Name Mascott Captain, Unique |
|
Team |
= 0 --------- hosts / visits --------------------- 0 = (Game – new weak entity) ID Hosts Visits Date – to allow team A to play team B more than once (note doesn’t allow double headers – how would you allow this?) Constraint: same team cannot have the same value for both host and visits |
Team |
|
Player |
= 0 --------- plays in / has -------------------- 0 = (PlayedIn) PlayerID GameID |
Game |
|
Player |
= 0 --------- commits / is fouled by -------- 0 = (Foul) ID Perpetrator Victim Constraint: Player cannot foul him/herself Might have attributes: timestamp, typeOfFoul |
Player |
|
Game |
1 1 --------- has / is committed during ---- 0 = |
Foul |
Note: The Basket entity and its relationships are not included in this list.
Tables:
Player:
ID (PK)
Name
Number
Height – roster information
Team (FK to Team (Name))
Team:
Name (PK)
Mascott
Captain (FK to Player(ID)), Unique – cannot be enforced by the database
Game: (without ID, PK would be all 3 fields)
ID (PK)
HomeTeam (FK to Team (Name))
AwayTeam (FK to Team (Name)) (Check AwayTeam may not = HomeTeam)
Date
Time
Played-in: (a weak ID dependent entity formed because of the many:many from Player to Game)
Player (FK to Player (ID))
Game (FK to Game (ID))
Foul: (a recursive weak ID dependent entity)
FoulID (PK)
Perpetrator (FK to Player (ID))
Victim (FK to Player (ID)) (Constraint: Perpetrator != Victim)
Game (FK to Game(ID))