Please explain Ternary relationship to me.

SQL Apprentice Question
There is a very basic, simple ternary relationship.
http://explorer.x-y.net/data/f1.gif


The question is to get choose a possible combination of each A,B,C
entity count, and the number of triples in R.


I thought that since there should be 1 B for each A, max number of A->B
is the number of entities in A. And since there is a C for each A->B,
(A->B)->C is the number of A->B


But I was wrong. But the correct answer is not explained, and I
couldn't find any information about these kind of problem on the text
book. The text book just gives very simple concept explanation nothing
more. Would you please tell me more about ternary relationship and how
to count the number of triples? Thank you in advance.



Celko Answers

>> I couldn't find any information about these kind of problem on the text book. The text book just gives very simple concept explanation nothing more. Would you please tell me more about ternary relationship and how to count the number of triples? <<


You cannot break it into binary relations like you are trying to do.

Fifth Normal Form (5NF), also called the Join-Projection Normal Form or
the Projection-Join Normal Form, is based on the idea of a lossless
JOIN or the lack of a join-projection anomaly. This problem occurs
when you have an n-way relationship, where n > 2. A quick check for
5NF is to see if the table is in 3NF and all the candidate keys are
single columns.


As an example of the problems solved by 5NF, consider a table of house
notes that records the buyer, the seller, and the lender:


HouseNotes
buyer seller lender
==================================
'Smith' 'Jones' 'National Bank'
'Smith' 'Wilson' 'Home Bank'
'Nelson' 'Jones' 'Home Bank'


This table is a three-way relationship, but because many CASE tools
allow only binary relationships it might have to be expressed in an E-R
diagram as three binary relationships, which would generate CREATE
TABLE statements leading to these tables:


BuyerLender
buyer lender
=============================
'Smith' 'National Bank'
'Smith' 'Home Bank'
'Nelson' 'Home Bank'


SellerLender
seller lender
=======================
'Jones' 'National Bank'
'Wilson' 'Home Bank'
'Jones' 'Home Bank'


BuyerSeller
buyer seller
================
'Smith' 'Jones'
'Smith' 'Wilson'
'Nelson' 'Jones'


The trouble is that when you try to assemble the original information
by joining pairs of these three tables together, thus:


SELECT BS.buyer, SL.seller, BL.lender
FROM BuyerLender AS BL,
SellerLender AS SL,
BuyerSeller AS BS
WHERE BL.buyer = BS.buyer
AND BL.lender = SL.lender
AND SL.seller = BS.seller;


you will recreate all the valid rows in the original table, such as
('Smith', 'Jones', 'National Bank'), but there will also be false rows,
such as ('Smith', 'Jones', 'Home Bank'), which were not part of the
original table. This is called a join-projection anomaly.


There are also strong JPNF and overstrong JPNF, which make use of JOIN
dependencies (JD for short). Unfortunately, there is no systematic way
to find a JPNF or 4NF schema, because the problem is known to be NP
complete. This is a mathematical term that means as the number of
elements in a problem increase, the effort to solve it increases so
fast and requires so many resources that you cannot find a general
answer.