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

## No comments:

Post a Comment