NFL E/R Design Problem

1
NFL E/R Design Problem

• Design an Entity-Relationship Diagram that models
  the following objects and relationships in the
  world of football (NFL) teams, players, games,
  managers and contracts. Each (NFL-) team has a
  unique team name, and a city it plays in. Each
  person being part of the NFL-world has a unique
  ssn and a name. Additionally, for players their
  weight, height, position and birth dates are of
  importance. Players have a contract with at most
  one team and receive a salary for their services,
  and teams have at least 24 and at most 99 players
  under contract. Each team has one to three
  managers managers can work for at most 4 teams
  and receive a salary for each of their
  employments. Players cannot be managers. A game
  involves a home-team and visiting-team
  additionally, the day of the game, and the score
  of the game are of importance teams play each
  other several times in a season (not on the same
  day!). Moreover, for each game played we like to
  know which players participated in the game and
  how many minutes they played.
•  
• Indicate the cardinalities for each relationship
  type assign roles (role names) to each
  relationship if there are ambiguities! Use
  sub-types, if helpful to express constraints!

2
empl.
name
ssn
Sal
(0,4)
name
Manager
?
(1,3)
Person
Team
city
?
contr
(24,99)
(0,1)
Player
birthd
Home
Visit
NFL E/R Problem
(0,)
(0,)
weight
pos
height
Sal
(0,)
play
played-in.
score
min
(1,1)
(22,)

• Scoring
• Play relationship a Set 3
• Person/Player/Manager 3
• Weak Game Entity 3
• Played-in 2
• Can Only Play once on a day 1
• Contract 3
• Salary, score, min attribute 3

Game
Date
3
Problem2
Team(name, city)
Player(ssn, birthd, pos,)
Person(ssn, name)
Game(home, visit, day, score)
Played_in(home,visit, day, ssn, min)
4
empl.
name
ssn
Sal
(0,4)
name
Manager
isa
(1,3)
Person
Team
isa
city
contr
(24,99)
(0,1)
Player
birthd
Home
Visit
(0,)
(0,)
weight
pos
height
Sal
(0,)
play
played-in.
Day
(0,)
score
min
Date
(1,1)
(22,)

• Scoring
• Play relationship a Set 3
• Person/Player/Manager 3
• Weak Game Entity 3
• Played-in 2
• Can Only Play once on a day 1
• Contract 3
• Salary, score, min attribute 3

Game
Another different Solution!