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

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.

>

> > * 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, and 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 Received on Fri Dec 14 2007 - 20:13:08 CET