Re: Fails Relational, Fails Third Normal Form
Date: Thu, 5 Feb 2015 03:24:54 -0800 (PST)
Message-ID: <50dfb32c-2cb2-4411-a041-d0d3d4e8593a_at_googlegroups.com>
Jan
> On Thursday, 5 February 2015 19:55:26 UTC+11, Jan Hidders wrote:
> 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:
Thanks for your response.
> > > Yes, it [A] 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.
Good.
I think you know full well, that one os the issues that will get exposed in this thread, is the delta between two things. So let me enumerate them. Let me start by saying this delta should not exist, it doesn't exist in other industries, it exists only in the software industry (to a lesser degeree) and this to an unacceptable degree. Theoreticians not being able to discuss the same science with the practitioners is an absurd situation.
So this:
> First, from my position of
> - Normalising data for 39 years, as a science
> - following Normalisation after it was defined as NFs (Codd 1970, 1971), within that science, because those early declarations proved science and articulated it
> - 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 )
is expanded to (ie. without excluding the above):
I Normalisation
- Normalisation as a science, a principle - Relational Model, to the fullness where Normalisation is concerned - which includes 1NF, 2NF, 3NF, ___ as originally defined, ___ (Ie. the original 3NF by taken to its full extent by someone who is not trying to subvert it, includes what you call BCNF, 4NF, 5NF)___ (I view ETNF, NSNF, 6NF, SCGHNF, etc, as pig poop) ___ (I view NSNF as a gross error, that we have it in the RM) - and to the fullness of the RM, which was the context of those definitions - which therefore includes HNM and RNF
___ out of scope for theoreticians in this space
I.a But the most important thing is the result. Many implementers and I use this, with ease, and it prevents various errors, and produces good Relational tables. Eg model [A] fails, and specifics are given. I.b Specifically, in addition to satisfying itself, this Normalisation "satisfies" any NF that you and your colleagues wrote, and will ever write in the future. I.c It is not "private" it is defined everywhere, the RM, etc.
Then, while not denying the relevance of theory, science, and mathematics, to any field, but sadly noting the absence of producing anything of value in the implementations in this field ... you have ...
ii "Normalisation Theory"
- non-science - 1NF and 2NF - an assault on 1NF currently in progress - a Fragment of 3NF, unfortunately you call it "3NF", which is fraud, since 3NF existed for decades before you came up with the fragment, and your fragment produces only that fraction of what 3NF produces - BCNF, "4NF", "5NF", "ETNF", "NSNF", "6NF", "SCGHNF", etc - all of which rely on "private definitions" which are illegal in most science - Please feel free to identify any fragments that I may have missed)
ii.a But the most important thing is the result. Record Filing Systems, which are non-relational or anti-relational, (such as [A]) are passed, as acceptable, as they "satisfy xxNF by definition".
So whatever it is you are anointing with your magic oil, your Normalisation Theory, (a) it stinks, (b) it is not Relational, and (c) it should not be accepted, or validated, or elevated in any way.
> .. engineering
Science
> > - 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.
Fine. For you. As long as you do not fragment it.
> > 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.
???
I do not have private Definitions.
???
3NF: I have always, and severally stated, Codd's definition 1970 and 1971. I quoted it the other day in the Theoretician Crippled thread, and you seem to have accepted it.
???
Normalisation: read up on it. Sure, I have extended or enhanced applications. Sure, it is deeper than soem practitioners understand it. And sure it is a world apart from you guys.
But that does not matter, I am not selling that. I did not ask for validation (Don't worry about "hard to say") and I am not obliged to justify it or prove it.
What the hell is the purpose of postulating over normalisation, devising a few fragmentary nuffs, that permit RFS in an RDB.
I am not introducing anything, I am not adding to 3NF. I recognise you have a three fragments "3NF", "4NF", "5NF", that equates to the original 3NF.
> > 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 ... not if it was a well-designed relational schema ...
Er. excuse me. Read the three questions. I asked for that, explicitly in [3].
> You asked if it was in 5NF, [] Being in 5NF is neither a necessary nor a sufficient condition for being well-designed, not does normalization theory claim that.
Good.
So throw the "5NF" definition out, then.
> > > 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?
No. You misunderstood my statement. Read again. I am merely confirming that YOU don't have yet another private definition for the established terms.
> > 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.
I do know, silly boy. I am just not playing your "dependency preserving" game. Because yours fails miserably anyway. Because mine (Codd's) preserves dependencies beyond your "dependency preserving" definition. Way, way beyond. But we will limit that to the scope of the original 3NF.
Textbooks. I went to college long before textbooks on this subject were written. I was teaching Normalisation for DBMS vendors, using these principles, when Codd came onto the stage, and right through his main acts (the 80's, not the 70's). We knew the three NFs from our scientists, and from practice. And those were the days of real textbooks.
The textbooks these days are manuals in bestiality, scriptures for devil worship. The results of which are theoreticians as I have to deal with here. In the first few decades, Date mostly, tried to diminish the three NFs. now it is free-floating cancer and everyone is doing it.
Point being, I do not accept "Normalisation Theory", that bag of fragments, that is nowhere near Normalisation. So there is no point in telling me that some fragment, and its smell, is "textbook stuff".
But don't t[let me stop you from expressing why a certain aspect of the given model has this small or that colur, and therefore passes or fails.
> > > > 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.
(I take your comment re "candidate keys" to mean one that you noticed, that were not in the model)
Good. So why did you pass it [A] then ? I was pointing out your contradiction, declaring it "candidate keys incomplete" or not explicit, and passing it anyway.
> > 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.
Misunderstanding. They count very much. in the statement above, the candidate keys that you have perceived, that are not in the model, do not count, because he did not identify them in the model.
You might be rushing during your lunch break. Take your time.
> > > > 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.
Fine. The argument remains, re why you pass some model that "satisfies" your fragments, why you find it acceptable [3], and I fail that model because it fails my integrated set.
> > Ok, back to business. I informed the developer as to what was wrong, ... 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.
>
> He is still in 5NF. He still has surrogate identifiers, but I personally do not consider that a big problem as long as he has correctly represented all candidate keys, which at first sight I don't think he has done fully. The argumentation with which he tries to prove that he is in 5NF is still wrong. You have to consider all candidate keys, period. It's not clear why he would want to use that argument anyway, because there seem to be no MVDs and JDs other the those implied by the FDs, so if he can verify that the schema is in BCNF then he can infer that it is also in 5NF. Finally, it is also still not dependency preserving, which might be a reason to denormalise to 3NF. Whether that is a good idea or not depends on circumstances that were not specified.
- Ok, so he is in your "5NF", but he fails the original unfragmented 3NF.
- The candidate keys are incomplete. Unacceptable to me.
- Surrogates are definitely not "identifiers", can you please explain. The term Identifiers in carved in stone, same as 3NF(1971), since 1976 It is part of the IDEF1X standard (which you have said you know). By definition, surrogates are NOT Identifiers.
Please explain why you think:
c.1 surrogates are keys (worthy of your detailed evaluation)
c.1 surrogates are "identifiers" (RM, and IDEF1X)
You may have missed my Fail[3] point above.
d. All-surrogates are acceptable to you, as long as the candidate keys are handled correctly, correct ?
e. The argumentation (and the basis) for his "5NF" declaration is wrong because [b].
f. >> It's not clear why he would want to use that argument anyway, because there seem to be no MVDs and JDs other the those implied by the FDs, so if he can verify that the schema is in BCNF then he can infer that it is also in 5NF.
That is precisely the kind of insane conversation that is produced by your "Normalisation Theory", which does not occur in the implementation field, and which, if someone started spouting, we would ask him to take the rest of the day off, have a long hot bath, and get a good nights sleep. It is not that I don't understand it, it is that that is such a sloooooooow way of dealing with small technical issues. We can figure that out at "lower" levels of abstraction than you are using.
But I won't stop you from doing that, certainly. You are free to express your point in whatever way you like. Why you imagine MVDs and JDs where there are none might be worth understanding, but let's not get distracted. Oh, wait. You are treating the surrogates as "keys".
g. Nor dependency preserving. I would say, that declaration is tending towards correct, but it can't be because [b], and the "keys" are not Keys, they are surrogates.
h. >> which might be a reason to denormalise to 3NF. Whether that is a good idea or not depends on circumstances that were not specified.
I don't accept den-ormalisation. #9 years, over 20 large implementations, over 150 consulting assignments, and I have always Normalised up, to fix any and all problems related to Normalisation (and speed). I have never seen any data store that is honestly de-nomrlaised. Unnormalised yes. Often their failure to Normalise is declared as "de-normalised".
Second, it should never be dependent on circumstances (which I take to mean, how the data is used, process). Data should be analysed and Normalised, as data, and only as data. The OO/ORM crowd mix data with process, and yes, that hinders, if not cripples, the Normalisation, and thus the resultant database.
In any case, let me declare, this set of tables is to be normalised within the cluster given, there are no other considerations.
So I take it you fail his model [B].
Cheers
Derek
Received on Thu Feb 05 2015 - 12:24:54 CET