Re: Newbie question about db normalization theory: redundant keys OK?
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