Re: On Normalisation & the State of Normalisation

Op dinsdag 3 februari 2015 03:11:05 UTC+1 schreef Derek Asirvadem:
> New Thread Normalisation
>
> [.. snip ..]
>
> ----
>
> Ok. Here is the latest extension that a developer proposed to me, to be added to the RDb.
>
> http://www.softwaregems.com.au/Documents/Article/Normalisation/Relational%20Database%20101%20A.pdf
>
> We need a set of tables to rationalise and consolidate all Addresses. The company sells their gear on the internet as well as Australia, the addresses have to be international. This is what the developer says is the cluster of all address type data that will be used as reference, in order for the data quality in the single Address table to be maintained. The CountryCode and StateCode are ISO 3166-1 and 3166-2, and the CountyCode is ANSI/FIPS, or something meaningful outside America (I already have that loaded elsewhere).
>
> Answer the questions in sequence, please. No point in going to [2] if you reject it at [1], etc.
>
> 1. The developer declares that the proposed extension satisfies 5NF. Is that correct ? If not, please state why, which NF is breaks, any errors that it may have, etc. A few words will suffice. I expect minimal discussion, but don't let me stop you.

Yes, it is in 5NF. But it is not dependency preserving.

> 2. The developer is excited because he has read the C J Date and R Fagin paper *Simple Conditions for Guaranteeing Higher Normal Forms in Relational Databases*, he has complied with the requirements, and he is sure of his 5NF declaration. He asserts that all the keys are "simple". He expects quick approval. If not, please state why, any errors that it may have, etc. Again, minimal discussion.

His conclusion is correct, but he is basing it on wrong assumptions. Not all his candidate keys are simple.  

> 3. Is this acceptable to you, as a human being, as a scientific logical person, as a set of relational tables ? If not, why not, please name the problems, if any.

To me it is. But this might also have been the case if it was not in 5NF. Normalization theory does not tell you in which normal form you should be, just whether you are or not and what some of the consequences of that might be.

• Jan Hidders