Question

Need this in MS VISIO.

Based on the following narrative description, conduct the conceptual data modeling and draw an Entity-Relationship diagram according to the requirements of NCAA Men Basketball. You should specify the key and non-key attributes for each entity type. For relationship types, you should specify: (1) the (min,max) cardinality ratios (you can use any representation notation), and (2) the relationship attributes if it is necessary to include them. If you discover the narrative is incomplete, provide reasonable explanations to complete the story. Supply these extra explanations along with your diagram. However, none of the assumptions you make can nullify or contradict the business rules stated. NCAA Men Basketball NCAA wants to develop a database to keep track of information about college men basketball. Each team belong to a University and associate with one conference. Teams have their mascots. A conference has several teams. Each team can has maximum of 60 players and a minimum of 20 players. Each player can only play for one team. Each team has 6 to 10 coaches. A coach can work only for one team. Each year, NCAA will schedule a set of games for each team, and two games for any two teams (e.g., UC and Xavier). Four qualified referees are assigned to each game. NCAA would like to keep track of the details of each game; such as game schedule (i.e., date, time, and game-place), and referees assigned, participating teams, and the final score. Attributes for players include name, admission-date, year (such as freshmen, sophomore, etc), major, GPA, blood-type, and birthday. Attributes for coaches are name, title, salary, address, and phone. Attributes for referees are name, referee-ID, salary, year-of-service, address, and gender. NCAA also wants to keep track of the performance details of players who participated in each game, such as the position they played in that game and the personal score, assistants, and fouls. NCAA also records the day when the team joins a particular conference. Deliverable: E-R diagram of your conceptual data modeling (I will not accept late homework.) The ER diagram should be prepared by a software tool (e.g., MS Visio).

Answer 1

The entity-relationship diagram is attached below:

1. There are total 9 entities - game,university,team,conference,players,refrees,coaches,mascots and performance.
2. The game entity has a 1-2 relationship with team, 1-n relation with performance and 1-n relation with refrees.
3. The university entity has 1-1 relation with team.
4. The team entity has 1-n relation with conference.
5. The players entity has n-1 relation with team and n-1 relation with performance and has a foreign key team_id to associate it with a team.
6. The refrees entity has n-1 relation with game and has a foreign key game_id to associate it with a particular game.
7. The coach entity has n-1 relation with team and has a foreign key team_id to associate it with a team.
8. The mascots entity has n-1 relation with team and has a foreign key team_id to associate it with a team.
9. The performance entity has two foreign key to associate it with a game and a particular player.

Normalization is performed to make the database model most optimal.