Re: Fails Relational, Fails Third Normal Form
Date: Thu, 5 Feb 2015 00:55:25 -0800 (PST)
Message-ID: <ecc21a5e-0843-475e-8caf-89481805f5f8_at_googlegroups.com>
Op donderdag 5 februari 2015 04:15:51 UTC+1 schreef Derek Asirvadem:
> > On Wednesday, 4 February 2015 21:35:12 UTC+11, Jan Hidders wrote:
>
> Thank you for breaking the ice.
>
> > Op dinsdag 3 februari 2015 03:11:05 UTC+1 schreef Derek Asirvadem:
> > >
> > > 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.
>
> I will take each of them separately
>
> > Yes, it is in 5NF.
>
> Ok. I think there is no disagreement that, if it "satisfies" 5NF, then it means it "satisfies" 4NF, 3NF, 2NF, 1NF. Yes ?
As these normal forms are usually defined in normalization theory, yes, that is true by definition.
> That is what the developer says, so you are confirming his declaration.
>
> I don't know what to say.
Really? This is you not knowing what to say? :-)
> First, from my position of
> - Normalising data for 39 years, as a science
.. engineering
> - following Normalisation after it was defined as NFs (Codd 1970, 1971), within that science, because those early declarations proved science and articulated it
The math behind it is science, the rest engineering. Does not make it less valuable, but it is important to make the distinction.
> - after seeing the putrid garbage that various blind people proposed, and dismissing them, as an attack on Codd, on science
> - which act reinforced the science, the principle, and categorised the blind who populate certain parts of our field as gross incompetents
> - I am left with The Three Normal Forms (not counting HNF and RNF, because you guys have not heard about that [which btw suggests that the blind are deaf as well] )
> I declare:
> ____the proposed data model [A] fails 3NF____
> and in general:
> ____the proposed data model [A] is not Normalised____
For the private definitions of 3NF and Normalised that you seem to use and have not yet made explicit, this might al very well be true. Hard to say.
> The model fails for the following reasons (many instances of said reasons). The ordering of the issues is mine, in that if it isn't Relational, it is not worth bothering about the specifics of a Normalisation error. It fails *Relational* mandates on two counts, it is non-relational.
>
> 1 Definition for keys, from the RM: "a Key is made up from the data"
> __ A surrogate (RecordId) is not made up from the data
> __ There are no keys on the data
>
> 2 The RM demands unique rows (data)
> __ A surrogate does not provide row (data) uniqueness
>
> 3 Re Normalisation, (if we consider that outside the Relational Model), it breaks Third Normal Form, because in each case, there is no Key for the data to be Functionally Dependent upon (it is dependent, yes, but on a pork sausage, outside the data, not on a Key).
>
> This is a classic Record Filing System, anti-relational. It is nowhere near ready for Normalisation, let alone Relational Normalisation.
>
> None of you theoretician identified any of that.
Because that is not what you asked. You asked if it was in 5NF, not if it was a well-designed relational schema. Being in 5NF is neither a necessary nor a sufficient condition for being well-designed, not does normalization theory claim that.
> > But it is not dependency preserving.
>
> Very good insight. Trusting that you do not have private definitions for "depency" and "preserving". Give that man a purple cloak and a point hat!
You don't know what "dependency preserving" means in the context of database normalization?
> I can't see how any model can "satisfy" any NF, 5NF in this case, and *NOT* have the property of preserving dependencies.
And you don't know in which NFs dependency preservation is achievable? This is text book stuff, Derek.
> > > 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.
>
> That is very interesting. There are no "candidate keys" in the model.
Nope. That is a big omission and already sufficient to consider it badly designed.
> Big tick if you intuitively determined some, but they do not count. The only keys to deal with are those defined in his model.
Not if you're serious about data integrity and normalisation, and wondering if this is a well-designed schema.
> > > 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.
>
> I don't see how you would accept a model that fails your (not science's) 5NF, as normalised, but I won't argue, I will let that one pass.
Please don't. It's at the core of the misunderstanding.
> ----
>
> Ok, back to business. I informed the developer as to what was wrong, what was unacceptable with his model, and sent him off. Gratefully, it took far less time to communicate that to him, than it does to communicate that to you guys. He came back today, with his corrected version.
>
> He still declares 5NF for sure, and Relational to the best of his understanding (which is based on books by the same authors that you read).
>
> http://www.softwaregems.com.au/Documents/Article/Normalisation/Relational%20Database%20101%20B.pdf
>
> My corrections to his /previous/ model [A] are on page 2.
>
> Please answer the questions in any order:
>
> 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.
>
> 2. The developer refers to *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 /that apply/ are "simple", and he has added keys to correct his previous errors. He expects quick approval. If not, please state why, any errors that it may have, etc. Again, minimal discussion.
>
> 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.
- Jan Hidders