3

ER-Diagram

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.

Infecto
  • 178
  • 2
  • 9
  • I'm not sure your commentary is saying the same as the diagram, in terms of cardinalities. Is each Project controlled/owned by a single Customer? Is it possible the same Developer can work on multiple Projects for multiple Customers? – AntC Nov 09 '17 at 22:04
  • Please [always use text when you can, and images/diagrams only in addition](https://meta.stackoverflow.com/a/285557/3404097). Here you should give DDL & the diagram is redundant. – philipxy Nov 10 '17 at 04:24
  • It's still not clear what relationship/predicate each cardinality constraint is about here or in your answer; you don't give the defining predicates for them or Assigns & though the new bullets are better you aren't fully using the clear language from my answer re a given attribute/subtuple quantity : quantity of subtuples of all others. You still confuse relationship predicates & constraints--you give constraints, but not predicates--what the relationships are that have those constraints. Give relationships/predicates then give any cardinality constraints for each. – philipxy Apr 20 '20 at 07:36

3 Answers3

2

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.

philipxy
  • 13,916
  • 5
  • 30
  • 68
1

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

reaanb
  • 8,956
  • 2
  • 21
  • 33
  • Hmm, I'm not sure the question is well expressed. I agree that because CustomerID doesn't have a "many" cardinality, it should not be (part of) any key. I think each Project belongs to 1 Customer only(? Nowhere does it say several Customers assign a given Project.) Multiple Developers might work on a given Project. Or a Developer might work on multiple Projects (and therefore multiple Customers). I'd have two relations: `Assignment (ProjectID, CustomerID)` keyed by `ProjectID`; `WorksOn(DeveloperID, ProjectID)` key is all attribs. – AntC Nov 09 '17 at 21:59
0

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)
Infecto
  • 178
  • 2
  • 9