1

I have problems in understanding why the following mapping from an ER-diagram to an ERM is correct, or, to be more precise complete. In the given example we have a ternary 1:1:N relationship between a project, a place and persons.

Example of an ER-diagram of a ternary relationship

Each entity has a primary key (ProjectID, PlaceID, PersonID). If I understand this diagram correct the combination of a person and a project can not get associated to more than one place. And in addition the combination of a person and a place can only be associated with one project. Furthermore a project at a specific place can have multiple persons.

This understanding of how to read a ternary relationship leads to my problem. I map the ERM to the following RM:

Project(ProjectID)
Place(PlaceID)
Person(PersonID)
Works(ProjectID, PersonID, PlaceID)

I now have two candidate keys in the table Works: (Place, PersonID) and (ProjectID, PersonID). Let's choose the first one as primary key. I should then have a correct RM (literature told me) but what I don't get is how to ensure that the same combination of person and project is not associated with different places? Don't I have to say somewhere that (ProjectID, PersonID) is also a candidate key or is this not part of the RM notation?

ProjectID   PersonID   PlaceID
1           Marvin     New York
1           Tom        Paris
1           Marvin     Tokyo
Marvin
  • 9,164
  • 3
  • 26
  • 44

1 Answers1

1

the combination of a person and a project can not get associated to more than one place. And in addition the combination of a person and a place can only be associated with one project.

Reading this carefully, you can make the further specification that every person has to be related to exactly one Project and exactly one Place. Since those are the only other entities in the relation, you can safely take Person (and more specifically PersonID) as the Primary Key for this relation.

Both (Place, PersonID) and (ProjectID, PersonID) are valid (non-primary) keys for the relation. You incorrectly assume that the example entries you give are correct. Seeing your relation, every personID, and by extension every (Place, PersonID) or (ProjectID, PersonID) pair, should appear only once in the table. So, the example table does not match the rules of the given ER-diagram.

Ghijs Kilani
  • 166
  • 1
  • 9
  • Thanks for your answer, but I don't understand why every person has to be related to exactly one project and one place. In my example table can't I change the third line to (2, Marvin, Tokyo) to match the rules of the given ER-diagram? – Marvin Aug 13 '15 at 08:02
  • Let's go through the members of the relation one by one. Take an arbitrary project. If we look at the "works" relation, it can be related to only 1 other place, and N people. That means, if 3 people are working on a given project, that project will appear in the table 3 times, with only the PersonID differing. The situation is analogous for a Place. A person however, can only be connected to 1 Project, and 1 Place. The diagram says that a project cannot be in 2 places at the same time. So every personID can only appear once in the table, not twice, and thus it is the primary key. – Ghijs Kilani Aug 14 '15 at 15:29
  • You can't change the third line to (2, Marvin, Tokyo), because for that to be possible, your ER-Diagram should have all N's, instead of a 1 at Project and Place. – Ghijs Kilani Aug 14 '15 at 15:30
  • My understanding of how to read the diagram was to always take two members of the relation as tuple and then see how they can be related to the third one. So I came to the conclusion that I can combine the same project with two different place-person-tuples. But the way you explain it (looking at the third member first and say that it can only be related to 1 other place and N people) sounds perfectly correct. Thanks again! – Marvin Aug 14 '15 at 16:09