Re: Fails Relational, Fails Third Normal Form

Nicola

Apologies for getting distracted for a day, with the opportunity to Determine Keys Directly. I am back, to giving you a worthy response to this post.

> On Sunday, 8 February 2015 21:39:01 UTC+11, Nicola wrote:
> In article <abc8bebc-4e27-4af4-a16d-dd94ad61f21c_at_googlegroups.com>,
> Derek Asirvadem <derek.asirvadem_at_gmail.com> wrote:
>
> > > On Sunday, 8 February 2015 05:39:26 UTC+11, Nicola wrote:
> > >
> > > > > To find the keys, you
> > > > > must determine which functional dependencies hold.
> > > >
> > > > That is a very slow method, but yes. The tables are dead simple, and the
> > > > attributes should be well-known to anyone who has any experience at all.
> > >
> > > Sure. If you find the keys directly, in fact you have defined some
> > > functional dependencies (from the keys). But, in general, you must
> > > ensure that no other relevant semantic constraints (not necessarily only
> > > functional) are missed.
> >
> > Do you have an example of such ?
>
> Do you mean, an example in which finding the keys directly is harder
> that finding FDs first and deriving the keys from them? ...

Closed under separate cover.

> Well, it depends
> on how clever you are at "seeing" keys, which in turn depends on how
> much experience you have.

My previous comments were not mocking your words, I was trying to say, I am using a scientific method, a few simple steps. No cleverness or special powers required.

The meaning is there in the data, in the proposed column names. One just needs to ask questions and to progress.

One limit that people often have, is they are blind to certain things. Check the Hierarchical Model thread, and you see how it sometimes takes a long time, to get past their blindness. In the case of you people, it is invented blindness, and I lay the blame for that on your teachers, who have taught you falsilties about the HM; the RM; RFS; etc. I can't help you with the blindness, as I can for the other aspects, because it is psychological, they have planted the falsities in your mind.

You guys have a different third problem, separate to not noticing the meaning in the data, the columns, separate to the blindness. You abstract yourself away from the data, such that whatever meaning there is, is lost. Subsequently, when you do the non-FD non-key thing, you are several levels of abstraction away from say me, who never left the data.

> For instance (from H. Koehler):
>
> CourseSchedule(Course, Lecturer, Room, Time)

And now the "harder" problem., with five elements in the UR. Done, Keys determined the "hard" way is actually easy, with very little effort. (Separate to the issue that the problem proposed in the paper disappears in the Relational context, that the paper is for Record Filing systems).

If you look at the data model, you will notice that there are only keys, there no non-key columns, except for the two lowest-levl, transactional tables, and even there the non-key columns are migrated foreign keys. SO the real science, the real value, is not in the Determination of Keys, that part is easy, the real science, the real value, is in the ability to work with Relational Keys. And there, yes, experience matters. But everyone, including me, had to start somewhere, and with experience, travelled to somewhere else. You people have not started, because you are fixed, glued at the hip, to RFSs. You have no idea how limited, how pre-1970, that filing system is. You might see a bit of it in this thread, but you will only really see what the difference is, if and when you start working with the RM, and real Relational Keys.

I am not asking you to give your RFS up, I am asking you to ADDITIONALLY dip your feet into the RM. It took me two years to get the length and breadth of it.

> where a course has only one lecturer, each class has a fixed duration,
> and the obvious constraints hold, such as teachers do not have the gift
> of ubiquity.

That is pretty much a 4-element version of the 5-element version that I solved. And you see how I solved it, with keys, the whole keys and nothing but keys. Yes ?

> You may find the keys "directly" here, of course, but I
> argue that, even if you do that, you are in fact (maybe unconsciously)
> reasoning about the valid FDs.

That isn't fair. I have always said, after Codd, the keys come first, teh FDs second, and yes, the FDs are used to verify and validate the key. But if there is no key, there is nothing for the attribute to be Functionally Dependent upon.

Nothing unconscious, the though process is fully conscious. Sure, I am practised, so I can do it very fast.

And there I am talking about a real FD. Not your non-FD (a tiny fragment of the real FD). So your statement is true if you mean the real FD, and false because you mean the non-FDs, because I do not consider them at all.

The fraudulent use of terms that are established for decades really places a strain on the communication. Only happens in this industry.

> Advantages of making the [non-]FDs explicit are that (1) they provide you with
> a systematic (algorithmic) way to derive all the keys (not that this is
> computationally inexpensive, but in many cases it is efficient enough);
> (2) you have a formal documentation of your systems's requirements.

Well, if you don't have FDs, then sure, you need something. Your non-FDs, plus the algorithm, all stated in 1's and 2's and a's and b's. That no user or developer will understand. But it is something, and it justifies what you have done.

It also speaks volumes about what you have not done.

And all that is unnecessary when you have real FDs, because they are written up everywhere, in every data model, every Business Rule, etc. In plain technical English. No question about the essential nature of documentation. But it has to be useful. Otherwise it is a door-stop.

I mean, look at the gap we have in communication, with your guys having totally different definitions for almost every term in the Relational database business. Even the word Relational is misused, abused, defrauded. So, if you write any of that (your meaning) using words that we know and love, you will be grossly misunderstood, your documentation, separate to it being unreadable, is not useable. You don't supply the dictionary to go with it.

> Since [non-]FDs are a more general concept than keys, you may capture
> constraints that are not captured by keys (I don't think that you were
> asking me to show you an example of this, since you may find it in so
> many books and papers, including Codd's).

I haven't seen it in Codd's, because he uses the real FD.

I have gone through many of the theoretical books you guys use, and I have posted the exact nature of their wrongs. Quickly here, there is nothing of value that I have found in them.

Take the Köhler paper as an example. I get to section [5], and the problem disappears. There is nothing to read after that.

I said he is not erecting a Straw Man argument, but the Date, Darwen, Abiteboul, Hull, Vianu, Fagin, all erect Straw Men, every single time. Propose a nn-problem in the non-RM, and then propose how to solve it outside the RM. Putrid filth. And sum total of it, over decades, diminishes the RM. Eg. why is it that people think there is nothing of the HM in the RM ? Why is it that people like James are surprised (good for him) to find out that that "truth" is in fact false ? It is planted; it is marketed; it is propagandised, at every opportunity.

Nevertheless, I would very much like an example of the thing that "capture constraints that are not captured by keys". Of course, if you know the RM, you will know that there is no such thing in the Relational context, because everything, constraints of any kind, not only FK constraints, are dependent on a Key. In my last project, I implemented probably 50 or so constraints of a type that you would not know about, way beyond all the constraints that you do know, partly because you think that cannot be done in the RM, in SQL, and done transactionally. That comes from mature use of the RM. But every single one of them is dependent on a key.

> Of course, if you are clever enough to design all of your schemas to be
> in Codd's 3NF to begin with (which, as far as I can tell, is implicit in
> your arguments),

Yes.

But not the "clever enough" part. Just if you are educated enough in the RM, and un-subverted enough. The RM is simpler than the various and sundry alternatives. And it is all integrated, woven into one whole, the rest is disintegrated fragments.

> then there's nothing else to discuss: of course all of
> your FDs will be full dependencies from keys.

That is the only kind of FD there is.

I have already stated, I am not saying that theoreticians should not have or use their non-FDS, I am saying it is fraudulent, mind-numbimg, to label that thing "FD".

> You'd never come up with
> the schema above in the first place.

I didn't. A developer who has been reading the afore-mentioned pornography did. And you guys couldn't find anything wrong with his model because who have read and ingested the same books.

> > > You say a schema with a surrogate "key" does not enforce row uniqueness.
> > > I write "key" to emphasize that it does not conform to Codd's definition
> > > because it is a totally made up set of values without any tie to reality
> > > - and that's fine with me to assume that.
> >
> > Ok, but that is despite my post, which details exactly why the word is false
> > and confusing. So to acknowledge that; to acknowledge that it is not
> > Relational; and then to continue using the term with double quotes as a
> > modifier, removes it from the previous category of unconscious fraud, and
> > places it in the category of conscious fraud.
> >
> > Either it is a Key, key, "key", 'key', or it is not.
>
> Ok, let's call it just "surrogate".

Well, that is the technical name (unless your reference is wiki).

Can you name one property of a Key that the surrogate has ?

It is like the problem I had with one of my developers (read your books, brains are scrambled) who was arguing with another developer (stopped reading your bokks after I put the new database in, and he can see that 99% of the problems have disappeared, brain slowly getting back to normal). The first was trying to tell the second that he had a non-unique key (meaning a surrogate). The second wasn't buying it, he knew better, but he could not articulate it (brains not quite normal yet). So I had to intervene, and explain that (a) a key is unique (b) therefore it is not possible to have a non-unique unique thing, (c) and if he had one, each part of the term cancels the other part, so what he has is zero, so stop talking about it. I sent him off to read acouple of articles I wrote decades ago, and he came back and later and said, "you are right, I have been taught to accept schizophrenia". He is sincere, that is why I have him, but the cancer, it is everywhere.

> > Or, you still think a surrogate has some properties of a key. In which case,
> > I have not gotten through to you, and there is something we need to pursue.
>
> We have agreed that a surrogate has no place in a relational schema. So,
> there's no need to discuss surrogates any further at the logical level.

They are not acceptable at the physical level either, so don't try anything freaky like that. It is pharisaic argument (ie. not science, not logic), to abstract surrogates out to the "physical only", and thus implement them. Secnd, it is a lie, becaue your non-FDS, depend on them. So the fact is, if you do use them, you are tightly bound to the physical. Hence, a pre-1970 Record Filing System, with NONE of the integrity, power, or Speed of the Relational model.

That abstracted-to-the-physical-so-it-doesn't-matter-in-the-logical lie, is Darwen's pig poop. I remember it. All of them use it. It is all in the category of "the physical is orthogonal to the logical" lie. Pig poop, fresh, warm and soft.

The RM is not "logical only". You might want to read it.

> > > Saying that it is not in 3NF is akin to saying that an
> > > integer does not run at 100Mph. Not false, but not particularly
> > > significant either.
> >
> > ???
>
> 1. Is 3NF defined in the context of the RM?

Yes.

> 2. Does it make sense to use a definition outside the context in which
> it is given?

No.

But that is a well-known abstractionist's trick. I have already explained in detail, when you exclude the context, when you break something up into fragments and isolate it from its context, from the other fragments, you lose its meaning and value.

> If your answers to these questions are not "yes" and "no", respectively.
> then I think I don't follow you. You are saying that something that is
> not a relational model lacks a property (3NF) that is defined for
> relational models.

See what I mean. I said no such thing. You said it. Yeah, I agree, it is a stupid thing to say.

> Strictly speaking, that's not a wrong statement: also
> integers lack the property of being able to run at 100Mph.

Sure, in a fragmented, unreal universe.

Not in the real universe.

> > 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.
>
> Exactly. So why are you so keen to point out that it fails 3NF? Just say
> that it is not relational.

Because the people I deal with, in the real universe, are not as fragmented as you.

Because mot people know (ok, not here) that while the RM and Normalisation are married, and inseparable, that is Normalisation post-RM, and they can also discuss Normalisation without reference to the RM. It happens dozens of times a day where I work, no one says, hey you can't say this, or that with out something else attached to it. Try wiki, that study in mediocrity, look up the NFs, see which ones reference the RM.

Because no one will accept any work, being returned to them rejected, with just "pass/fail" stuck onto it. If they have half a brain, they will want to know why, exactly what was wrong, so that they can fix it. You can't fix "failed RM on 22 counts", you have to know what each count was, what the specific prohition that they broke was, in order to fix it.

Evidently you have no such intent. Your intent is to change your ever-changing "definitions" so that the problem is a non-problem to you. There is not one hair on your head that can implement.

And when we do have those conversations, it is ordinary, common, to state "this breaks x NF", so that they have a clear understanding of what definition to look up, what discussion they need to have with their mentor, to fix it. Because if they keep making the same mistake, over and over, they will lose their job.

Second last, you missed the bit that I understood all this, and wrote my annotations for the benefit of both the fragmented and the integrated:

> > Re Normalisation, (if we consider that outside the Relational Model):
> > 3 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).

The "if we consider" covers all types. But you argue.

Last, why don't you try to take the meaning from the words, and deal with that, instead of dealing with the words, abstracted out of their meaning ?

> > I have every respect for formalism. I have no respect for a formalism that
> > is sooo isolated from reality (facts in the physical universe); sooo ignorant
> > of other sciences (ie. established truths); sooo abstracted such as to lose
> > the meaning of the very thing it is abstracting, such that it "proves"
> > something that is patently false; devoid of meaning.
> >
> > Eg. there is no problem at all to "prove" in an abstract, isolated, ignorant
> > sense (formal argument), that pigs can fly. But that has to be very
> > abstract, very isolated, very ignorant. And if you tell anyone who has not
> > lost his mind that you have proved theoretically that pigs can fly, he will
> > split his sides, because the extent of his laughter is physically damaging.
> > But you make that statement with a straight face.
> >
> > And if you make that statement to someone who is an authority, he will lock
> > you up, in order to protect society for such insanity.
>
> Progress in science often happens exactly because the "established
> truths" and the "facts in the physical universe" are subverted, and
> someone comes and says:. "what if pigs could fly?". You don't need
> non-Euclidean geometry or String Theory to find your way home, and you
> don't need to know number theory to buy things in your favorite online
> shop. You (and me) can be happy with Riemann's definition of an integral
> for all practical purposes (in fact, we might never need it in our
> lives), and ignore the fact that there are several other definitions,
> which give different results only in pathological cases. We can already
> build spatial databases that solve our problems satisfactorily, so why
> bother with a topological extension of the RM (see Norbert Paul's
> thread)?

Once I realised what he was trying to do, I told him exactly that. You can't "extend" the RM. The RM can be applied to any field. Applying it to topology is over twenty years old.

He, and I presume you, are talking about something else. Again, fraudulently labelled "RM". Something in your abstract space that keeps shifting and changing, that has a bunch of 42 or so different algebra for it. Sure, that thing, it can be extended.

> Yes, you don't need any of the many abstract definitions of normal forms
> in the literature to design and implement a good database that makes
> your customers happy.

Yes. The un-perverted 3NF, applied fully, is way beyond all the NF definitions that all of you put together can come up with in the next fifty years. The proof is, separate to what I do, that you have come up with nothing in the last forty five years.

They came up with BCNF, 4NF, 5NF, to Codd and me, they are all fragments of 3NF, that only idiots need to have spelled out.

Now they have ETNF, SCGHNFRD, which actively subvert the RM. #NF remains unchanged.

When Darwen made a big deal about 6NF (a) we had been using that construct for twenty years, gee, we jsut didn't have a sexy label for it. (b) there is another use, when I wrote to him about it, with full documentation, he couldn't understand it. (I am not going to reveal that use here, sorry.) Years later, I found out that, gee, just like you people, you can't read models.

> You don't need to care about the nested RM or
> infinite relations.

Pig poop. Breaks 2NF. I do al that (nested relations) in the current RDBMS, the current SQL, without breaking anything. Now Date & Darwen are trying to change the definition of 1NF, to squeeze their abortion "a relation can contain a relation" through, without understanding that we do that all the time, we have had the capability since 1984, as a matter of course. We don't need to break any NF, let alone change definitions, to supply it.

Notice, they are doing the same thing you are doing, further up, changing definitions to make a pig pass off as an eagle.

> You don't have to worry about the fact that a
> relational schema may have a factorial number of keys, or that a schema
> with with fourteen attributes and ten constraints may have tens of
> millions of possible decompositions in 3NF.

Not 3NF, no. Must be some private definition of yours.

> You will never ever find
> such things in the real world. That does not mean that we don't have the
> freedom to explore.

Of course you have the freedom to explore. I don;t see how anything I have said takes away from that. In fact, I have said that we desperately need theoreticians, because this industry is very poorly served. I have my years in R&D, I understand and support that exploratory freedom. God knows I have paid large amounts of money for others to have that freedom.

But some day, one day, you have to produce something that is of value, in the physical universe. And you have produced nothing, after Codd.

So the truth is, it is not about your freedom to explore. It is about not exploring anything of value.

Second, if anyone takes an analytical approach, you have masses of impediments: teachers who teach filth, lies, non-science; who diminish the science that we do have; all of you using definitions for terms that are different to those that the 99% use; etc.

> Welcome to c.d.t. (crazy.database.theory) ;)

Thank you. I have been here before.

And thanks for the clarity in your other posts.

Cheers
Derek Received on Mon Feb 09 2015 - 10:37:31 CET