Representing multiple candidate keys in RM
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.
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
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.
- Finding candidate keys for given relation
- What is the difference between candidate key and composite key?
- How does this relation produce candidate keys?
- Normal forms - 2nd vs 3rd - is the difference just composite keys? non trivial dependency?
- Are middle attributes on a relation schemma prime?
- Example of when you should use a foreign key that points to a candidate key, not a primary key?
- Are determinants and candidate keys the same?
- Subset of a candidate key
- Can a CK have a FK referencing to a CK in another table?
- Confused about third normal form: a table having more than one candidate key
- how can I infer the candidate key from csv files
- Determining candidate keys from functional dependencies: Exceptions where we don't use the minimal super key?
- 3NF Normalization and Decomposition
- How to implement priorities in SQL (postgres)
- How do primary keys work in junction tables for a DBMS? How can a composite key be a primary key?
- Find 3 minimal keys in the following relation
- what is Partial Dependency
- Finding the Candidate Keys of a Relation Using the FD's
- Candidate Key Identification with Functional Dependencies
- How to determine keys from functional dependencies
- Why is XY not a candidate key?
- SQL statement to prove that a candidate key constraint holds in a R(ABCD)
- Microsoft T-SQL does not support more than one Primary Key; Suggested Workarounds?
- Is there another candidate key? If so, what is it?
- Candidate keys after canonical cover
- How do I create a composite candidate key for a table in SQL Server?
- In which normal form are these FDs?
- Can a table have two columns which could act as primary key
- RDBMS keys confusion
- Table with unique identifier in Third normal form?