Question on 5NF

Hi to all

I've a question about 5NF. For the sake of brevity, let's take the explanation and the example present on Wikipedia:

http://en.wikipedia.org/wiki/5NF

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.

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.

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?
If yes, what kind of normal form is this one?

If you tell me that this is not right and I have to decompose into 3 tables (PI, PC, CI) to obtain 5NF...i have another question for you :-)

Thanks in advance!

Davide
www.davidemauri.it Received on Thu Oct 05 2006 - 09:22:41 CDT