Re: Fails Relational, Fails Third Normal Form

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? Well, it depends on how clever you are at "seeing" keys, which in turn depends on how much experience you have. For instance (from H. Koehler):

CourseSchedule(Course, Lecturer, Room, Time)

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. 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.

Advantages of making the 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. Since 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).

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), then there's nothing else to discuss: of course all of your FDs will be full dependencies from keys. You'd never come up with the schema above in the first place.

> > 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".

> 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.

> > 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?
2. Does it make sense to use a definition outside the context in which it is given?

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. Strictly speaking, that's not a wrong statement: also integers lack the property of being able to run at 100Mph.

> 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.

> 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

Ok.

> 2 The RM demands unique rows (data)
> __ A surrogate does not provide row (data) uniqueness

Ok.  

> 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).

See above.

> [4] This is a classic Record Filing System, anti-relational. It is nowhere
> near ready for Normalisation, let alone Relational Normalisation.

Ok.  

> 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)?

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. You don't need to care about the nested RM or infinite relations. 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. You will never ever find such things in the real world. That does not mean that we don't have the freedom to explore.

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

Nicola

• news://freenews.netfront.net/ - complaints: news_at_netfront.net ---