0

I'm a bit confused at how to translate the Official entity in my ER diagram. I know for a 1:N relationship, the 1 side of the relationship becomes a foreign key in the N side of the relationship.

So for the relationship: Game - Head Officiated - Official, the primary key (game ID) should be a foreign key in the entity Official? Here's what I currently have and the entity Official doesn't look right.

Official (OfficialID, Fname, LName, Home City, GameID*??)

Game (GameID, Date, City, Head Official*)

My ER diagram

I'm beginning to doubt if my ER diagram is correct as well. Here's is a description of the database (I omitted the parts that are not relevant to the question:

For the officials, you must keep track of their first and last names (each will be 20 letters) and their official id (a 2 digit field) and their home city (15 letter field).

For games, you must keep track of the game id (2 digits), the date of the game and the city the game took place in (15 letters field).

Games are officiated by officials (refs). Every game has at least one official, but may have more. Every game has one official who acts as the head official (so every game will at least 2 officials, a regular one and a head one, HINT: keep track of both (officiates game and head officiates game) as 2 separate relationships). Some officials will be in our database who have not yet officiated any games.

EDIT: min,max notation messed me up. I get it now, sorry if I confused anyone.

Deik
  • 134
  • 9
  • You need a third table for which officials are not the head official for each game. Like `nonHeadGameOfficials` with the gameID and officialID for each official who isn't the head official for the game – Beth Nov 20 '17 at 21:00
  • I'm aware I need a table for the N:M relationship: Official - Officiate - Game. But what I'm trying to ask is if my Official table is correct. Official (**OfficialID**, Fname, LName, Home City, GameID*??) I'm unsure if GameID should be a foreign key in the official entity table. I know the primary key in the "1" side should be a foreign key in the "N" side but it doesn't look right to me. – Deik Nov 20 '17 at 21:02
  • no, officials can participate in more than one game, so no, gameID cannot go into the official table – Beth Nov 20 '17 at 21:08
  • That's what I thought, so what should I do in this case? I'm thinking maybe I should create a table for the relation officialHeadOfficiates. is it ok to make a table for a relationship that isn't N:M? – Deik Nov 20 '17 at 21:16
  • nvm the min,max notation messed me up. I understand what to do now. – Deik Nov 20 '17 at 21:27
  • Perhaps the look-across convention for cardinality indicators will work better for you than the look-here convention. – reaanb Nov 21 '17 at 05:46

0 Answers0