Mapping a ternary relationship to the relational model (Employes, Customer, Project)

When ternary relationships are expressed in a relational model, each of the entity sets with a "many" cardinality indicator becomes part of the primary key. In other words, I read your relationship as expressing the functional dependency (EmployeeID, ProjectID) -> CustomerID which will be physically represented as Assignment (EmployeeID PK/FK, ProjectID PK/FK, CustomerID FK).

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.

As I have already mentioned in the question in the first place, the description of the relation is the following:

• 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)

The problem is in the ER-Diagram itself: it does not exactly represent the description above. The problem lies in the constraint that a single project can be given by one single customer. That's why it would make more sense to model that with two separate binary relationships instead using a ternary one.

That being said, the relationship between Customer and Project should be a 1:n relationship, while the relationship between Project and Developer should be a m:n relationship. Mapping those relationships gives us the following:

• Customer(CustomerID) with Primary Key=CustomerID
• Project (ProjectID, CustomerID) with Primary Key=CustomerID and Foreign Key=CustomerID referencing the Customer
• Developer(DeveloperID) with PK=DeveloperID
• ProjectDevelopment (ProjectID, DeveloperID) with PK={ProjectID, DeveloperID)