Re: Fails Relational, Fails Third Normal Form

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

> > Once you have found all the keys, you may tell whether your
> > schema is in 3NF or not (in this case, it is - provided that I interpret
> > correctly, and I suppose I do, the meaning of the remaining attributes).
>
> No, I have stated that it FAILS 3NF.

Look, I overall accept your line of argument and its conclusions, with a caveat. Let me see if we can converge.

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. A logical consequence of the previous statement is that a surrogate "key" cannot be part of a relational schema. E.g., this representation:

CountryId CountryName

1         Australia
2         Australia

depicts two identical tuples. Then the instance above is not a relation, hence the model is not a relational model. Fine.

But then, that diagram is ill-formed, as it does not represent a logical relational model. 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.

Btw, your argument about surrogate "keys" contradicts the business rule "Country is uniquely Identified by (CountryId)", stated in the document you have shown us. Why doesn't that statement imply that CountryId is a key? How would that be different from "Country is uniquely Identified by (Name)" (which would imply that Name is a key)? The difference is in the eyes of the beholder, it seems.

> (There is an interaction going on with Jan, where we have teased out that you
> people have a fragment of 3NF which you fraudulently call "3NF". Result
> being, as evidenced here, you accept a total failure, a non-relational Record
> Filing System, to be satisfactory.)

No. We are making a formal argument. In other words, we *interpret*

CountryId CountryName

1         Australia
2         Australia

as having *two* distinct tuples. And we are *assuming* that CountryId -> CountryName (as per the stated business rule). Formally, this does not look any different from

X Y
1 A
2 A
FDs: {X -> Y}

where I don't need to care what those attribute mean any longer to tell that this is a relation, and its schema happens to be in 3NF (and 5NF, too).

Since we are discussing upon different *assumptions*, we are not going anywhere with this game of "it is/it is not 3NF". If you want to continue arguing on the irrationality or dumbness of the formal position above, feel free to do that, but mathematics is mathematics, and modeling reality is a totally different business (I have already expressed my almost complete support for you point of view, see above).  

> I think the pivotal difference is, Codd and I require a Key, before
> Functional Dependency can be worked with, and you people have non-FDs, and on
> non-keys.

The formal definition of a key depends on the notion of FD: FDs come first, keys can be derived from them. I don't know what you mean by "non-FDs", but FDs on "non-keys" exist, because the reality to be modeled mandates it.

Finally, I have summarized in a few words the problem with that model: "it does not capture the real constraints" (sorry if it's more that six). I don't think my posts will ever be longer than yours :)

Btw, thanks for the clarification about the post codes.

Nicola

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