Re: Nearest Common Ancestor Report (XDb1's $1000 Challenge)

On Fri, 21 May 2004 17:32:02 +1000, thirdrock <iktaccounts at optusnet dot com dot au> wrote:

Hi Ian,

>I was cheering you on until you said this ...
>
>>
>> You might be surprised to hear this, but there's a reason that the
>> relational model is called "relational". It's about storing relations.
>
>No, unless I miss-understood what you meant by that. A relation refers to
>the relationship between data members of the same 'table'. A 'table' was
>originally called a relation. Joins between relations in not the reason
>why the relational model is called relational.
>
>Or is that what you said??

Did I write that? (goes back to check) Oh dear, I did.

I meant to write: "It's about storing relationships", but somehow I forgot to type the hips. Apologies for causing confusion.

>> There would have been redundancy if each object's class was stored along
>> with it's reference, like this:
>>
>> thing class otherThing otherClass hierarchy
>> --------- --------- ---------- ---------- ---------
>> (snip some rows)
>> 'fido' 'dog' 'john' 'person' 'leader'
>> 'fido' 'dog' 'mary' 'person' 'leader'
>> 'fido' 'dog' 'luke' 'person' 'leader'
>
>The above table does not meet the 1N rule, correct?

When you write "the 1N rule", do you mean "1NF" (first normal form), or are you refering to something else?

AFAIS, this table does not violate 1NF (no repeating groups). It does violate 2NF (class and otherClass are not fully dependent on the primary key).

>> This example of a bad design has redundant storage of the same relation.
>
>Er.. actually it's an example of non-normalised data. I don't think design
>has anything to do with it.

Huh? Normalizing *IS* one of the steps you take when you design the schema of a relational database. And the aim of normalizing is to remove redundant relationships (not relations) from the design.

>> You seem to be thinking that storing the character strings 'fido' and
>> 'leader' several times is bad.
>
>Bad, or just not normalised?

If I understand Neo correctly, he thinks it is not fully normalised, AND he thinks that anything that is not fully normalised is bad.

>> This does indeed reduce the number of times you'll see the string 'fido'
>> in the database to 1. But we still have multiple references to fido (note
>> the important distinction between fido (no quotes - a dog) and 'fido'
>> (quotes - a name). We didn't remove anything from the database, we only
>> changed the way things are references and added an extra relation.
>
>True, but if fido goes down to the Names Registry and changes his name to
>'FiFi' in preparation for a sex change operation, then that change is not
>cascaded out to all the records of things. Whereas in your second table,
>it does. So what exactly is your point? Did you say that your first table
>is normalised?

Hmmm, you're right. I did indeed forget to add "on update cascade" and "on delete cascade" to the "references" clauses in my original message (the first one I posted in this discussion, four days ago).

>> You claim that my model has redundancy. But if I follow your suggestion
>> of
>> "improving", I end up with the same data in the existing tables and some
>> extra relations that I could do without.
>
>No, you end up with normalised data, which I think is Neo's point (is it
>Neo?)

You end up with data that is no more normalised than you started with, but that does need extra columns in some tables (or: extra relationships in the schema).

>> I don't know what definition of "redundant" you've been taught. I think
>> that "Exceeding what is necessary or natural; superfluous." (*) does
>> quite
>> accurately describe the schema with both thingID and thingName, not my
>> original schema!
>
>Well, writing a trigger to cascade out fido's name change to 'fifi' is
>redundant if your model was such that it was un-neccessary.

Why write a trigger if you can use DRI?

>> XDb1 fails in a much more spectacular way, as I'll explain in another
>> message.
>>
>I'll look for that message too.
>
>Have fun.
>
>Ian

Best, Hugo

-- 

(Remove _NO_ and _SPAM_ to get my e-mail address)