Question

policy number, owner name, insured amount, and name of the verencialy. (25 pts.) Design an ER diagram for a simple database for the National Hockey League (NHL): • The NHL has many teams Each team has a name, a city, a coach, a captain, and a set of players Each player belongs to only one team Each player has a name, a position (such as left wing or goalie), a skill level, and a set of injury records (each has an id and the injury description) A team captain is also a player A game is played between two teams (referred to as host_team and guest_team) and has a date (such as July 4th, 2016) and a score (such as 4 to 2) (Hint: Include a weak entity set in your design]

Answer 1

Please follow the below steps to draw the Entity Relationship Diagram for the National Hockey League:

Step 1: Find the Entities in the system

Below are the required entities for the system:

• Team: To store the record for the Teams
• Player: To store the data about players of the team
• Game: To store the game details for host and guest teams. This is a weak entity which will have identifying relation with owner entity Team
• Injury: To store the injury details

Step 2: List the attributes for the each entity

The entities will have below attributes where the primary key attribute is underlined and highlighted:

• Team(Name, city, coach)
• Player(player_id, name, position, akill_Level)
• Game (date, host_score, guest_score, winner, additional_comment); Game will be a weak entity having Name as host name and guest name from Team entity.
• Injury(injury_id, name, description)

Step 3: List the relationships in the entities along with cardinality:

• Player belongs to one Team whereas a Team has many players (Many to One relationship)
• Team is captained by one player but a player can be a captain to none or one team (Zero or One to One relationship)
• A player has none or many injury records whereas same injury can have record for none or many players (Many to Many relationship)
• One game can have one Guest Team whereas a Team can be a guest for many games (Many to One relationship). This is an identifying relationship as Game is a weak entity having Team as Owner entity.
• One game can have one Host Team whereas a Team can host many games (Many to One relationship). This is again an identifying relationship as Game is a weak entity having Team as Owner entity.

Step 4: Draw the ER diagram:

The Entity Relationship Diagram for the above entities, attributes and relationships is displayed as below:

National Hockey League date coach guest_score guest (1,M) city Game (1,1) host_score Name Team winner (1,1) (0,1) hosted by > additional_comme nt <captained by injury_id (1,1) has X 0M) belongs to Injury (1,M) name Player (0,N) description player_id skill_Level name position

Step 5: Other description:

The below are the notations used in the Diagram:

Entity is shown as rectangle:

Entity

Attribute is shown as oval symbol:

Attribute

Relationship is shown as diamond box:

Relationship

Weak entity is shown as double rectangle:

Entity

Identifying relationship is shown as double diamond:

Relationship

Primary key attribute is shown underlined:

Attribute

Assumptions: Team Name will be unique for all teams.