Mapping a ternary relationship to the relational model (Employes, Customer, Project)
I would like to convert this segment of an ER-Diagram to a relational model. We have a ternary relationship and what it says is the following:
- 1 Customer gives 1 Project to -> multiple Developers
- 1 Customer assigns 1 Developer with -> multiple Projects
- 1 Developer is assigned 1 Project by -> ONE Customer
A proposed solution would be this:
Assignment(EmployeeID, CustomerID, ProjectID)
where the primary key is composed of EmployeeID, CustomerID and ProjectID. And all of those attributes are foreign keys, each one refering to its respective entity.
But this solution is plain wrong as it doesn't express the same thing as the ER-Diagram. We have a composed primary key, so that means that the COMBINATION of those three things is UNIQUE. That implies that I can have the same ProjectID, with the same EmployeeID but given by a different CustomerID (which I do not want).
How do I resolve this?
EDIT: As many users find that the bullet points haven't clarified anything, I will give a short textual description of the concept of the relation:
- A single customer can give away one or more projects
- A single project can be given by ONE SINGLE CUSTOMER
- Each project can be finished by one or more developers
- Each developer can work on multiple projects (regardless of the customer by which the project was given)
For that purpose, I have concluded that it would be better to use two separate binary relations instead of a single ternary. See my answer below.
Answer
the COMBINATION of those three things is UNIQUE. That implies that I can have the same ProjectID, with the same EmployeeID but given by a different CustomerID (which I do not want).
The triplets being unique does not imply that--clearly, the triplets can be unique at the same time as certain combinations of rows are absent. On the other hand it doesn't enforce their absence. But the cardinality constraints do. What they say is what the bullets (try to) say--that only certain situations/states can arise. The bullets are not "what the relationship says"--either in the sense of what rows actually form the relationship/table in a given situation/state or in the sense of what a row says about the situation when it is in the relationship/table.
In this kind of diagram a diamond denotes an n-ary business or application relation(ship) or association and its corresponding table. A line in such a diagram represents a participation by an entity type and its corresponding FK (foreign key) (sadly, called a "relationship" in pseudo-ER methods.) A constraint is a restriction on what instances/rows can appear in a relationship/table. Each instance/row in a relationship/table "says" that that row of values satisfies the relationship. Constraints "say" there are limitations on what values can be so related over all situations/states. Cardinalities are constraints that say something about how many times values and/or combinations of values can appear in a relationship.
There are two main cardinality conventions, look-across & look-here. In look-across a number/range says how many of the entities of the type it is near can participate with one subrow of entities of the other entity types, ie how many times some subrow of the others can participate/be in the relationship/table. (Chen's original ER meaning.) In look-here a number/range says how many subrows of the other entity types can appear with an entity of the type it is near, ie how many times a nearby entity can participate/be in the relationship/table. (Look-here isn't very useful for relationships with arity > 2.)
We have a ternary relationship and what it says is the following:
What the relationship diamond says is that you are recording the rows (EmployeeID, CustomerID, ProjectID) where (something like) developer EmployeeID is assigned by customer CustomerID to project ProjectID. What the cardinalities say is that only certain sets of instances/rows can satisfy that relationship in any given situation/state.
- 1 Customer gives 1 Project to -> multiple Developers
- 1 Customer assigns 1 Developer with -> multiple Projects
- 1 Developer is assigned 1 Project by -> ONE Customer
Your bulleted constraints are not clear. Numbers have been stuck in front of entity types--almost as one would put id values in to get what that row of id values says when in the relationship/table--but the almost-sentences produced, which also have unexplained arrows, don't mean anything. Maybe you are trying to say, for a given customer-project subrow value there can be multiple developer values, etc? That would give the look-across cardinalities in the diagram. But you haven't said that.