Draw an entity-relationship diagram for the NCAA football database that must contain data of:
• University: (1) university name, (2) average cost of a game ticket in each year, (3) number of players in each year, (4) number of scholarships that the university offers in each year, (5) number of players who graduated in each year, (6) number of recruiting incidents (usually violations) in each year
• Player: (1) player id number, (2) player name, (3) birth date, (4) university that the player represents, (5) year when the player joined the university
- A player plays for one university. In a university, there are many players.
• Coach: (1) coach id number, (2) coach name, (3) birth date, (4) universities for which the coach worked and is working, (5) dates during which the coach worked for universities, (6) positions at universities
- A coach may have worked for more than one university.
- But, a coach cannot work for more than one university at one time.
- Further, a coach cannot have more than one position at one time.
• Stadium: (1) stadium name, (2) stadium size (i.e., no. of seats), (3) location
- Every stadium is owned by exactly one university.
- A university may own one or more stadiums.
• Game: (1) location (e.g., stadium), (2) date, (3) universities, (4) scores
• Bowl Game: as a subtype entity of the game entity. (1) organizer, (2) bowl description
The ERD must include not only entities and relationships with cardinalities but also all attributes available in the description. Underline or clearly mark the primary key of every entity. Enter all foreign keys in the ERD. There must be no many-to-many relationships and no multi-valued attributes.
Convert the above ERD into a relational model.