2

ER diagram ternary 1:1:1

Suppose A, B, C have the keys id(A), id(B) and id(C). I want to represent the ternary relation in the relational model, i was following the algorithm from the book "Fundamentals of Database Systems (7th edition)" (Chapter 9.1.1, step 7):

The primary key of S (The ternary relation) is usually a combination of all the foreign keys that reference the relations representing the participating entity types. However, if the cardinality constraints on any of the entity types E participating in R is 1, then the primary key of S should not include the foreign key attribute that references the relation E′ corresponding to E.

This makes me doubt, the relation should be composed only by foreign keys(Im not sure if that is correct in the relational model?), should i create a new unique identifier for the relation (i.e. S = (id(s), id(A), id(B), id(C)), or any of the primary keys of the participating entities could be the primary key of the relation? (i.e. id(A))

I know that what i could is just create a relation A with key id(A) and the attirubutes id(B), id(C) and the remaining attributes, but i want to avoid this if possible.

Magic code
  • 45
  • 7
  • The triplet is a superkey, but it's not a CK. Each FK is a CK. This is just another example of how the ERM & pseudo-ERM methods restrict relational model designs & do not necessarily produce the best designs. PS PKs have no role in RM theory. You can see here how ERM presentations use PK when they should use superkey. There are other reasons why one might want PKs. Interestingly although one of the CKs is adequate to identify a relationship instance, there are times where when one appears elsewhere with another the pair must be a FK because they are referring to the same relationship instance. – philipxy Sep 05 '18 at 22:04
  • Can you find an answer googling 'site:stackoverflow.com ER: Converting a binary 1:1 to relational model'? What does textbook "Step 3: Mapping of Binary 1:1 Relationship Types." suggest? PS [Re whether PKs even matter.](https://stackoverflow.com/a/37174517/3404097) – philipxy Sep 05 '18 at 23:07
  • 1. Why can you not just have one table/object type Z with all attributes of A,B and C? 2. Why can you not have three tables/object types A,B,C with a PK id of the same value accross all those three tables/objects? - sorry to bother, but I think without understanding your motivation our answers will tip tap somewhat in the dark. – Quicker Sep 06 '18 at 12:54
  • @Quicker 0) Rows and tables don't correspond to objects and classes. Rows and tables represent facts and predicates, respectively. Use OOP for systems modeling, not for data modeling. 1) Total participation isn't indicated, i.e. some values in A, B, and C may exist without being involved in a relationship. 2) Identification of entities and relating them are separate concerns - we want identities to remain stable even when we change associations. – reaanb Sep 07 '18 at 08:55
  • @reaanb 0) The question referring to databases and my comments to tables which are building blocks to databases. I don't get your point about refering to OOP. 1) would not be my preferred option either. However this solution would still work for me since I personally do not mind practical tradeoff over way-too-expensive theoretical perfection. I would simply accept the tradeoff of having 0/NULL-foreign keys. 2) Mhh, records in A,B and C having a field trioid with a unique value 103 stay stable if you change triod in or all of those records. I do not understand your argument. – Quicker Nov 15 '18 at 15:29

0 Answers0