database design

Scenario Consider a database system for the organisation of fixtures and results in a soccer league. The data requirements are summarized as follows: The people involved in the league include players, coaches, managers,and referees. Each person is identified by a unique personnel id. They are also described by their first and last names along with the date and place of birth. Players are further described by other attributes such as theirposition on the field (goal-keeper, defender, midfielder, or forward) and have a squad number associated with them. Teams are uniquely identified by their names. Teams are also described by the city in which they are located and the division and league in whichthey play (such as premier division or first division). Teams have one manager, a number of coaches, and a number of players. One of these players is the captain. At the beginning of each season all fixtures should be available to view in the database. Each team plays every other team twice (once at home and once away). Games are played between two teams with one assigned referee. For any particular game on a particular date, there is one team designated as the home team and the other team designated as the away team. Once the game has taken place, the final result of the game is recorded. The score and the scorers are recorded for each team.