Re: Fails Relational, Fails Third Normal Form

Jan

> On Friday, 6 February 2015 04:07:52 UTC+11, Jan Hidders wrote:

> I'm afraid I have to back-pedal here. The dependency I had in mind is actually preserved after all.
>
> What I was thinking of is the following. We were told that the CountryCode and StateCode are ISO 3166-1 and 3166-2. Now, ISO 3166-2 actually contains the country code from ISO 3166-1. So if you would join everything into a single table (Universal Relation, and all that) there would be an FD StateCode -> CountryCode. In the current schema that FD no longer lives in one of the relations, but I had missed that it actually follows from the local FDs / CKs.

1. I have no idea why you are contemplating the UR, the exercise is to accept or reject the tables as given, for the criteria given. The exercise is not to contemplate all the possibilities of all the data or what non-fds could exist on what non-keys.

If you people are struggling to determine the keys, using that particular long and winding method, you are lost.

The data is purposely simple. Everyone should know what a State is; what a County Key should be.

And there is no request to determine the Keys.

2. Separately.
If you do what you are saying re 3166-1 and 3166-2, you have more holes in your head that I realised. The StateCode you are describing breaks 1NF. I can't believe you did that, or that you thought that would be acceptable in a Relational Database that is Normalised (our 3NF; your 42 NFs). Again, it goes to show that you guys can't keep track of your abstractions, you accept a sub-standard model and the basis of such, whilst being hilariously ignorant of the fact that it breaks RM Compliance and 3NF. And not, Jesus Almighty, you yourself break 1NF.

The state that the theoreticians in the space are in, is very sad.

I suppose you don't know how you are breaking 1NF, if you did, you wouldn't break it, right ? So I had better give another discourse.

1. Every attribute shall be Atomic.
2. Your notion of StateCode since it contains CountryCode as well as StateCode, is not Atomic.
3. You are taking the 3166-1 and 3166-2 spec literally. Govt Departments, ISO, are not that technical. That is meant for the post office and stamp-lickers. Each spec stands on its own. Technical people do not do that. They understand what the specs mean, they esteem Atomicity, and they implement a correct set of tables:

CountryCode = "US", "NL"

StateCode = "AL", "NY", "WY" ... "ZE", "FR"

The notion of a state code such as "USAL" or "US-AL" etc, breaks 1NF.

d. Keep in mind that the users and developers mean "Alaska" when they use "AL", they do not want to, and should not have to, use "US-AL". Ie. a StateCode is an Atomic item, it would be preposterous if we had to mess with SUBSTR(StateCode, 3, 2).

e. Likewise for the CountyCode. It is not CHAR(7) ie. containing CountryCode and StateCode. It is CHAR(3) containing CountyCode, only CountyCode, and nothing but CountyCode, so help me Codd. FIPS is US only, numeric; most countries use a string.

> Showing this is left to the reader as an exercise. :-)

I suppose that is a good distraction, if you can't execute the main exercise.

> > Which dependencies are not preserved? With respect to what schema?

Far be it from me, to get entangled in the flagella, so only to increase the pace a little:

- the correct FDs preserve all dependencies 
- the tables are too immature, too non-relational at this stage for us to worry about the FDs yet, he has to fix the Keys so that they are correct first
- granted, you are working with non-FDs on non-keys

> On Friday, 6 February 2015 20:54:20 UTC+11, Jan Hidders wrote:
> Op donderdag 5 februari 2015 12:24:55 UTC+1 schreef Derek Asirvadem:
> >
> > > > 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.
>
> Let's focus a little on this.

What, despite the fact that, two short paras later, I posted:

> > I am only dealing with the context of this thread. That pretty much scopes it to the delta wrt 3NF (your "5NF") which causes a massive difference in the pass/fail status of models. Let's try an focus on why the model fails for me and passes for you. Why you accept Record Filing Systems (all surrogates) with no Relational integrity, power, speed, and I don't.

So you want to address a distraction. Reluctantly, then ...

> As you know I did not accept it as the full and exact description of Codd's definition of 3NF.

That is news to me.

> Your claim is that it is different form the standard textbook definition. That can be easily established by comparing them.

Not my claim, I think that was yours, but yes, it is true.

And I have already identified that the "textbook definition" is what falls into the category that you call "Normalisation Theory", an algebraic expression that has lost the meaning of the 3NF Definition, a fragment. And again I identify the problem with it six paras above this one.

Would you mind quoting the full and precise definition of 3NF by Codd?

I don't know why you don't know this. It is textbook stuff. I don't like using wiki, but in this simple case, it should suffice:

http://en.wikipedia.org/wiki/Third_normal_form

1. Of course, as usual, wiki posts pure excreta. The definition they attribute to Codd is false (proof: the term "superkey" was not known in 1971; I believe that was Zaniolo & Dates invention, circa 1981).
2. "A 3NF definition that is equivalent to Codd's, but expressed differently, was given by Carlo Zaniolo in 1982"

is totally incorrect because the definition attributed to Codd is false.

3. The "Nothing but the key" section is slightly better.

"A statement of Codd's definition of 3NF ... was given by Bill Kent: "[Every] non-key [attribute] must provide a fact about the key, the whole key, and nothing but the key."[7] A common variation supplements this definition with the oath: "so help me Codd"

Is half true and half fiction. wiki is famous for its efforts in re-writing history.

The Codd statement that he gave at the RM/T conference in Australia in ~1981 (?) was the one I gave, using "Functionally Dependent" instead of the wiki "provide a fact" quote. It was written up and discussed in many articles in those days. There were many "Codd Facts" and "Codd Rules" that we concerned ourselves with in those days, all well-known and eagerly discussed. Kent was known to have simplified it.

Of course a key point is by removing "Functionally Dependent", in the usual manner that abstractionists do, it breaks it up into fragments., and thus loses its meaning. Codd gave us Functional Dependency and 3NF together. The RM is an integrated set of rules. The 42 fragments are *dis*integrated possibilities. Just note the flagellation required for the latter, none required for the former.

That statement (attributed by wiki to Kent, in fact a simplified form of Codd's) was also, and famously, made by Date, and he kept repeating it, in many shows and presentations. IIRC one in Australia in ~1992. He added the "so help me Codd", because it was his way shutting down the argument (we wouldn't give FDs up), and of diminishing Codd subtly, at the same time. Typical liar, and he showed himself up many times, same as Einstein. For the very reason that he was fighting the widely accepted Codd/Kent 3NF definition, and wanted his algebraic fragment instead.

"Superkey" is Date and Darwen's baby, only required to make Record filing Systems pass off as "Normalised". It is an abortion, requiring additional columns and indices, that are simply not required in a Relational Database. The Darwen groupies who rewrite history falsely state that Codd used the term. Pulp. Fiction.

There, I have responded to your distraction. Now can we please get back to some of the central issues. For this one, I think it is established that

- the 3NF Definition was established in 1970, crystallised in 1971, in technical English; no algebraic expression was given
- (I am not at home, I don't have all Codd's papers on me, so I can't quote from them directly)
- that Date & Zaniolo first gave an algebraic expression of the "normalisation theory 3nf" in 1982, which is a mere fragment of the original (ie. the relevance of the Key is missing, plus others)
- that has been modified (evidence that the first was incorrect) severally since then

So what we have to deal with here is, we implementers use the original definition and reject the data model as failing 3NF; you use the 42 fragments and accept the same data model as "satisfying 5NF". It is pointless to argue about definitions, especially the established ones; better to examine the difference, and to determine why you people are so crippled.

You are concentrating on justifying your fragments. If you like, I can draw a picture for you, why your fragments fail.

And don't worry too much about whether mine has a formal definition or not, it is widely understood, accepted, and used. Keep in mind that it works; it takes a tiny fraction of the time that your takes (ie. I rejected the data model, and I can express the reasons in a few words). And yours, with 42 fragments that don't even approach mine, fails miserably. (ie. you accepted the data model that fails Relational and fails 3NF). If I were you, I would worry about the decrepit state and uselessness of your 42 formally defined fragments.

You can call mine (Codd's) a toy if you like. Just don't forget that my Tonka Truck wipes the floor with your 42 fragments, it beats your entire army. Two rounds ended with TKOs.

> Btw. that reminds me: have you already found a concrete example of a statement that Gary Boetticher makes in the movie you referred to where he uses standard normalization terminology in a non-standard way?

That is answered in a previous post in that thread, in great detail. You stopped at a certain point, it is past that point.

> You accused him of doing that,

No. I accused him of using the established term FD in a non-standard way, which has since been exposed as your "normalisation theory" way. So while some minor progress has been made re what each other is talking about when we use terms (confusion that does not exist in other industries), the bottom line remains: 99% of us know what an FD is, what 3NF is; you guys (the 1%) have abstractions of it, which are so abstracted that the meaning is lost (eg. the relevance of the Key), and you (plural) commit fraud and misrepresentation when you use labels that are already established for your novel fragments. Additionally, because you have many fragments (eg. BCNF, 4NF, 5NF, ETNF, SCGHNFRDB, etc, etc, etc) for what we consider a single logical concept, and those fragments still do not add up to the single logical concept, the fraud is serious.

If you (plural) were honest, you would use terms like "3NF.1 Zaniolo" and "3NF.42 Fagin", and you might be a little more useful to the community. Better still "Fragment 3NF.42", "Fragment FD.17".

Since it is now clear that when you (plural) use the term FD, you mean nothing of the sort, you mean some fragment of it, that does not have a Key to depend on, then if you were honest, you would use a different term.

Much like, it has been proved, and it will continue to be proved, that when you state the "rm says this or that", you are not referring to the RM. It is a pack of lies. Which gets exposed one lie at a time. See my response to Nicola. This doesn't happen in other industries, a theoretician in the car industry does not say he understands internal combustion engines, only to be proved wrong five days later. It is professionally embarrassing. You people have no shame.

> and even mentioned it as a reason to call him a fraud.

Yes. And I called him a fraud additionally (to the above, another count) because he was not even teaching your "normalisation theory fd theory" correctly. It is all in that post.

> You seemed to be moving towards providing some evidence of that, and then stopped.

No. Unlike you, who has left at least nine (I lost count) items hanging in three threads, with no response one way or the other, I have, as stated from the outset, continued each item to resolution.

I do admit, some days are busy, and sometimes, not usually, it takes a few days for me to respond. Eg. I posted on 7 Feb replying yours of 3 Feb. That is unusual. My apologies.

You might not be getting my posts. Also, if you are using "show/hide quoted text", it might be hiding new, un-quoted text. I say this because quite often you stop at one point in my post, and the rest remains unaddressed. On that thread, I can see 32 posts as at 07 Feb 15 07:25 UTC.

Cheers
Derek Received on Sat Feb 07 2015 - 09:09:51 CET