Re: Newbie question about db normalization theory: redundant keys OK?

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Fri, 14 Dec 2007 17:28:41 -0400
Message-ID: <4762f58d$0$5261$9a566e8b_at_news.aliant.net>


raylopez99 wrote:

> On Dec 14, 5:07 am, "David Cressey" <cresse..._at_verizon.net> wrote:
>

>>A few comments here:
>>
>>2NF applies only to compound keys.  A table that is in 1NF and has only a
>>simple key is in 2NF necessarily.  1NF and 3NF can apply in situations where
>>the key is a simple key.

>
> Interesting, I'll keep this in mind.
>
>
>>Be aware that the definition of 1NF changed rather profoundly since the
>>1980s  (when I learned relational databases).  By the definition used by
>>Date & Darwen a relation is always in 1NF.  The same is true of a relvar.
>>Hence if your model is a relational one,  the issue of 1NF is moot,  in the
>>D&D framework.  The D&D framework is widely accepted.

>
> OK, that's interesting.
>
>
>>I never claimed that star schemas would be free from UPDATE anomalies.  I am
>>not sure of the case you outline above,  but I most certainly can come up
>>with update problems that affect star schemas,  but do not affect fully
>>normalized databases.  My claims of star schema have to do with ease of
>>access,  not ease of update.  And the "ease" I'm talking about is largely a
>>matter of perceived difference rather than logical difference.

>
> Yes, and even the textbook I was looking at by Carli Watson (quite
> good) on C# database programming has a simple example that shows what
> I had in mind as a recursive UPDATE as difficult is not difficult at
> all, as long as no key is "stranded" (i.e. dB normalized) so
> "synching" is not a problem.
>
>
>>1NF, 2NF,  and 3NF are not the end of the story.  In addition,  there are
>>BCNF,  4NF,  5NF, and a final normal form called domain-key normal form.
>>I've een references to 6NF in this newsgroup.  As far as a can tell, 6NF is
>>the same thing as domain-key normal form.

>
> OK, I wish I had a cheat sheet with examples for these configurations,
> just to satisfy my curiosity, but I'll keep them in mind.

It's not the best resource in the world, and it is a little dated, but for true db-geekdom, one has to have a copy of:

http://www.marcrettig.com/poster/

>>>* as a further practical consideration, one dB I use, personally more
>>>than anything else, is Access, which doesn't have compound keys to
>>>begin with
>>
>>I beg to differ.  MS Access has supported compound keys since Access 97, and
>>maybe earlier.   (I'm just coming up to speed on Access myself,  having used
>>more classical SQL products.)  If you buy the design of either the
>>"Northwind" database,  or any of the databases  that the wizards can create
>>for you,  you are going to learn several unfortunate design habits.
>>
>>The absence of compound keys is just one of them.

>
> OK. I am using MS Access 2003 but will update to 2007 to get rid of
> the annoying nag screen in Vista (apparently 2003 version does stuff
> that gives Vista security fits). I'm wondering outloud whether MS SQL
> Server and Access are interchangeable, I suppose they are (you can
> import the latter into the former). Also I'll keep a lookout for
> compound key, but since I'm in love with GUIDs as Primary Keys,

Yuck! ::shudder::

> also the Long Integer seems to do a good job at generating a primary
> key, at this newbie stage I'm not in a fit to rush off and try a
> compound key, maybe later when I get a bit more experienced.
>
> RL

If you are going to be doing this database stuff, I suggest you try to get a grasp of the fundamentals. Try to find a good book on normalization etc. Date's _Introduction..._ is a comprehensive text. Pascal's _Practical Issues..._ is excellent once one knows a little SQL etc. Received on Fri Dec 14 2007 - 22:28:41 CET

Original text of this message