Scribd
Upload
16 views

atish_dbms

Uploaded by

silverlye.k
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
16 views

atish_dbms

Uploaded by

silverlye.k
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

Lab 1 – Creating ER Diagram using Case Tools

Objective:To understand a given case study, notations used for ER Diagram and design the ER
Diagram using a tool of following scenarios.

Student has to submit a file named as Group No._G1 with the


complete solution.

Exercise-2 (Moderate Level)

Entity Relationship Model

Case Study 6:
Suppose you are given the following requirements 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,
•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 May 11th, 1999) and a score (such as 4 to 2).
Construct a clean and concise ER diagram. List your assumptions and clearly indicate the
cardinality mappings as well as any role indicators in your ER diagram.

Entities:

1. Team

o Attributes: Name, City, Coach

o Relationships: has Players, plays in Games

2. Player

o Attributes: Name, Position, Skill Level

o Relationships: belongs to Team, has Injury Records

3. Captain (since a captain is also a player, we will treat this as a role for the player, rather than a
separate entity)
4. InjuryRecord

o Attributes: Injury Details (description, date, etc.)

o Relationships: linked to Players

5. Game

o Attributes: Date, Host Team Score, Guest Team Score

o Relationships: played between Host Team and Guest Team

Relationships:

• Team-Player Relationship: A team has many players (1

), and each player belongs to one team.

• Captain-Team Relationship: A captain is a special player (1:1 relationship, a player acts as the
captain).

• Game-Team Relationship: Each game is played between two teams, represented as a host and
a guest team (M

).

• Player-InjuryRecord Relationship: Each player can have multiple injury records (1

).

Assumptions:

1. Every team has one captain, and a captain is a player on the team.

2. A player can have zero or more injury records.

3. A game has two teams, one acting as the host and the other as the guest.

4. A player can only belong to one team at a time.

5. There are no separate entities for coaches or positions.

ER Diagram Overview:

1. Team

o Connected to Player: 1 to N (One team has many players)

o Has a self-relationship with Player (captain role): 1 to 1 (One player is the captain)

2. Player
o Connected to InjuryRecord: 1 to N (A player can have many injury records)

3. Game

o Connected to Team (Host Team and Guest Team): M to N (One game is played between
two teams)

Cardinality Mapping:

• Team to Player: 1 to N (One team has many players, but each player belongs to only one team)

• Team to Captain (Player): 1 to 1 (One team has one captain, a captain is a player)

• Player to InjuryRecord: 1 to N (One player can have multiple injury records)

• Game to Team: M to N (One game involves two teams as host and guest)

ER Diagram Elements:

You might also like