Most text books state that Ternary relationships are implemented as a
table with FKs for all the relations..

This seems pointless with 1-1-M relationships..

In 1-M binary relationships.. the FK is placed on the M end...
In this case it would make sense to put 2 FKs in the M end..

What is the proper way of implementing this?


Tnx,


Mike

miklesw (AT) gmail (DOT) com wrote:
To be exact, for a ternary relationship S(A, B, C) between the entity
types A, B and C you do the following:

1. Introduce a relation R(S) with columns PK(A) + PK(B) + PK(C) where

* PK(T) denotes the set of columns that is the primary key for the
relation R(T) you introduced for entity type T and
* the + means that you take the union of the PKs but rename some
columns so PK(A), PK(B) and PK(C) share no column.

2. Add PK(A) + PK(B) + PK(C) as the primary key of R(S)

3. Add three foreign keys

a. from the renamed PK(A) in R(S) to PK(A) in R(A)
b. from the renamed PK(B) in R(S) to PK(B) in R(B)
c. from the renamed PK(C) in R(S) to PK(C) in R(C)

I suspect your misunderstanding is that for a relationship we only
introduce a few foreign keys, but this is only sufficient for binary
relationships, and then only for those that are not many-to-many. In
other cases, such as ternary relationship, you add a relation that
represents the relationship.

Hope that helped.

-- Jan Hidders

Isn't a 1-1-M similar to a 1-M?

Let's say the M end requires total participation... wouldn't have 2
required FKs satisfy the relationship?

Isn't that better then introducing another join?


Jan Hidders wrote:

miklesw (AT) gmail (DOT) com wrote:
Not always. The usual definition of the term is as follows:

If S(A,B,C) is a 1-1-M relationship then in the relationship:
1. for every B-C *pair* there is at most one corresponding A entity
2. for every A-C *pair* there is at most one corresponding B entity
3. for every A-B *pair* there can be arbitrary many corresponding C
entities

By "satisfy" you mean "model", I presume?

Nope. That would be the case if the first 1 in 1-1-M would say
something like "for every C there is at most one A", but as you can see
above they don't. Actually that is hard to avoid because if there is a
1 upperbound on the A role in the relationship, what would that mean?
That there is at most one A for every C, or for every B, or for both,
or what?

Note btw. that if it was true that for every C there is at most one A
and at most one B then the relationship could (and should) actually be
split into two binary one-to-many relationships. So in that case you
indeed *would* be able to model it with two foreign keys. It could very
well be that you only misunderstood the meaning of a 1-1-M relationship
and the relationship you had in mind indeed can be represented as you
described.

-- Jan Hidders

Hi Jan,

If you recall a previous topic you helped me with...the ternary rel
represents:
(A1 breeds with A2) results in B - where B can't exist without A1
and A2

http://groups.google.com.mt/group/co... 11d131acf57a

I think I should have used 2 binary relationships from the beginning..
or perhaps..1 binary with a structural contraint of (2,2)..not sure if
that is possible..

Since A1 is related to A2 by the act of breeding, it made sense
conceptually, for them to be related to each other... However, in this
case, I can acheive the same result by linking them via B...

I think the point I have been overlooking, is that if there was no
total participation for the B,
the FK for A1 or A2 could be null.. as a result, the ternary
relationship would be incomplete...

In this case both FKs are be required.. so the breeding between A1 and
A2 is captured by the existense of B

Thanks for your help,

Mike


Jan Hidders wrote:

miklesw (AT) gmail (DOT) com wrote:
Aah, so that is the ternary relationship we are talking about. :-)

It's possible, but then it would be a many-to-many relationship, which
again requires a relation to be represented. Two binary relationships,
one for 'father-of' and one for 'mother-of' probably makes more sense,
and would be one-to-many.

-- Jan Hidders