title: Creating an entity relationship diagram to model the NHL (example problem)
body: <p>We're learning about entity-relationship modeling in our database course, and I wanted to get some extra practice translating a given word problem into an ER diagram. I found <a href="https://www.csub.edu/~ychoi2/MIS/Assignment/ERD_Practice.pdf" rel="nofollow noreferrer">this exercise</a> from a university site:</p>
  
  <blockquote>
    <p>Suppose you are given the following requirements for a simple database for
    the National Hockey League (NHL):</p>
    
    <ul>
    <li><p>the NHL has many teams,</p></li>
    <li><p>each team has a name, a city, a coach, a captain, and a set of players,</p></li>
    <li>each player belongs to only one team,</li>
    <li>each player has a name, a position (such as left wing or goalie), a skill level, and a set
    of injury records,</li>
    <li>a team captain is also a player,</li>
    <li>a game is played between two teams (referred to as host_team and guest_team) and
    has a date (such as May 11th, 2017) and a score (such as 4 to 2).</li>
    </ul>
    
    <p>Construct a clean and concise ER diagram for the NHL database using the Chen notation
    as in your textbook. List your assumptions and clearly indicate the cardinality mappings
    as well as any role indicators in your ER diagram.</p>
  </blockquote>
  
  <p>I worked this out on paper and was hoping someone could review my work (I must stress that this is NOT homework—I do not attend that university). Here is my ER diagram:</p>
  
  <p><a href="https://i.stack.imgur.com/IKoGc.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/IKoGc.png" alt="enter image description here"></a></p>
  
  <p>Here are my justifications:</p>
  
  <ul>
  <li><p>One <code>Team</code> has many (but at least 1) <code>Player</code>s. Each <code>Player</code> belongs to exactly 1 <code>Team</code>. (1:m, both mandatory)</p></li>
  <li><p>Each <code>Captain</code> is-a <code>Player</code>, but not every Player is a <code>Captain</code> (1:1, mandatory only on the Captain end).</p></li>
  <li><p>Each <code>Team</code> has exactly one <code>Captain</code>, and each <code>Captain</code> belongs to exactly one <code>Team</code> (1:1, mandatory participation for both).</p></li>
  <li><p>A given <code>Team</code> can play against another <code>Team</code> (1:1).</p></li>
  <li><p>A <code>Player</code> can have many <code>InjuryRecord</code>s, but an <code>InjuryRecord</code> can belong to exactly one Player (1:m with mandatory participation for <code>InjuryRecord</code>).</p></li>
  </ul>
  
  <p><strong>My question consists of four specific concerns:</strong></p>
  
  <ol>
  <li><p>Was I wrong to use the <code>name</code> and <code>city</code> combination as the primary key for Team entity, or would <code>name</code> have sufficed? I'm not familiar with hockey, but I suspect I should've only used the name.</p></li>
  <li><p>With the given information, is it even possible to uniquely identify a Player? There's nothing stopping a team from having two players with the same name, position, and skill, is there?</p></li>
  <li><p>Was I wrong to model the injury record as a weak entity set? Should it instead be a multi-valued attribute?</p></li>
  <li><p>Should the recursive relationship <code>plays_against</code> require mandatory participation for both entities involved? I figured maybe there are teams that simply haven't played against other teams yet.</p></li>
  </ol>