Re: Question on 5NF

From: Jan Hidders <hidders_at_gmail.com>
Date: 7 Oct 2006 00:44:26 -0700
Message-ID: <1160207066.497781.204580_at_m73g2000cwd.googlegroups.com>


davide.mauri_at_gmail.com schreef:

>
> I found myself in a situation where I have the following rule (speaking
> using wikipedia sample's terms): the Condition that the Insurance
> provider can cover are *only* those conditions covered by the
> Psychiatrist which are affiliated with that Insurance Provider.

That's actually not a static constraint, and that's what normalization deals with, so your question is not really a normalization question. To understand why it's not a static constraint try to formulate it as a logical constraint on the relation. You get something like:

"for every tuple (P,I,C) in the relation there is a tuple (P',I',C') in the relation such that I=I' and C=C' "

Which is a tautology. But if you formulate it as a dynamic constraint

"you can add a tuple (P,I,C) in the relation if there is already a tuple (P',I',C') in the relation such that I=I' and C=C' "

then it makes sense. :-)

> This should mean - for me - that the table
> Psychiatrist-to-Insurance-Provider-to-Condition *cannot* be decomposed
> into the 3 smaller table, since {Psychiatrist, Insurance Provider} is
> the foreign key of an hypotetical table which hold the information on
> which Psychiatrist is affialited with which Insurance Provider.

That is irrelevant. If you would split one of the results would be a relation with columns {Psychiatrist, Insurance Provider} which would then also be the foreign key to the hypothetical relation.

> To sum up, with the new rule said above, my model look like the
> following one;
>
> The relations that holds entity values
> P: (Psychiatrist#, ...)
> I: (Insurance#, ...)
> C: (Condition#, ...)
>
> and the relations holding
>
> PI: ({Psychiatrist#, Insurance#})
> PIC; ({Psychiatrist#, Insurance#}, Condition#)
>
> Is that right?

Probably. There would be redundancy it PI is always a projection of PIC, but I presume that is not the case.

> If yes, what kind of normal form is this one?

Depends on what FDs, MVDs and JDs you think hold. In particular you haven't told us yet if the JD that might lead to a split of PIC actually holds or not. Presuming that it does and there are no further non-trivial FDs and MVDs then you are not in 5NF.

  • Jan Hidders
Received on Sat Oct 07 2006 - 09:44:26 CEST

Original text of this message