Re: Newbie question about db normalization theory: redundant keysOK?

#1

raylopez99 wrote:

Quote:

On Dec 14, 9:36 am, David Portas
REMOVE_BEFORE_REPLYING_dpor... (AT) acm (DOT) org> wrote:

I genuinely am having trouble understanding what any of this has to do
with saying that Joe's design is "wrong". None of the problems you
have mentioned are the usual ones given for using "artificial" keys -
not that I've seen anyway. I don't doubt that you have some real
issues in mind but I don't think you are explaining them very
precisely: "The real world gets in the way" tells us nothing about why
it would be a problem to update email address X to become email
address Y. I just don't see what you are getting at.

For methods of recording the history of change Date, Darwen and
Lorentzos have a book "Temporal Databases and the Relational Model",
which discusses the issues and solutions at length.
--

Don't feel obligated to answer in any way, but if you recommend a
single book for somebody who is coding for fun but has a science
background that gets into this theory please feel free to state so--if
it's Date et al let me know.

Also perhaps (and I'm just literally reading this stuff for the first
time, I absolutely no clue otherwise) the changing of a primary
compound key is a problem because if you change the key, and it's at
the top root of a tree of relationships, then the cascades and
triggers that are affected by changing the primary key are so
extensive that as a practical matter, for a *** Terabit dB, then
you'll have to spend a week of maintanence downtime to have the new
primary key "perculate through" the database (just a thought).

RL

You are confusing logical and physical issues. For example, if all of
the 'dependent' or 'downstream' relations are physically clustered with
the 'parent', there is no need to propagate anything. Or even any need
to store the key in multiple locations regardless how many relations the
key logically appears in.

I am not necessarily recommending such clustering. I merely point out
the irrelevance of performance with respect to a logical issue.

#2

raylopez99 wrote:

Quote:

On Dec 14, 5:07 am, "David Cressey" <cresse... (AT) verizon (DOT) 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/

Quote:

* 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::

Quote:

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.

#3

raylopez99 wrote:

Quote:

On Dec 14, 9:02 am, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:

Why does this thread seem to have an air of deja vu all over again?

http://groups.google.com/group/comp....owse_thread/th...

That was one long thread, and the introduction was very interesting,
but it was a bit over my head. I didn't finish the thread. From the
punny and ironic tone I could not figure out whether the OP was for or
against GUIDs (I think he was for them), but it did set a lot of
thought processes in motion, and I sort of see the point about data
integrity being "built in" using compound keys, while flexibility is
the most when using GUIDs (or something like that).

GUIDs don't buy you anything that I can see. Pay attention to JOG and
Liebniz equality.

I really need to

Quote:

buy a book (I saw a few names in the thread) on this stuff at some
point, though I should tell you I'm coding for fun as a hobby (and
doing a simple dB now) rather than as a profession.

What do you intend to get out of the hobby? I know people who do
carpentry as a hobby, and they want to learn about carpentry. I know
people who watch birds as a hobby, and they want to learn about
ornithology. When I meet someone who does applied mathematics as a
hobby, the person probably wants to learn about applied mathematics.

Quote:

And apparently Bob Badour, "Neo" really hates your guts! LOL. These
debates can get as heated as the ones at alt.global-warming!

I will let Neo speak to the issue whether he hates me. I didn't really
see any evidence of that. Profanity doesn't necessarily equate to hatred.

Neo appears to think very much at a concrete level with little
abstraction. He has put tremendous effort into whatever it is he calls
his product. xdb or something. Sadly, his apparent inability to abstract
things limits his product in spite of his efforts. I am never sure
whether to admire his tenacity or to pity his limitations.

While I would like nothing better than for Neo to come up with something
truly revolutionary for all his efforts, I try not to encourage him
because I think the possibility exceedingly unlikely.

Quote:

Good luck

RL

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Newbie question about db normalization theory: redundant keysOK?

comp.databases.theory comp.databases.theory

Re: Newbie question about db normalization theory: redundant keysOK? - 12-14-2007 , 03:21 PM

Re: Newbie question about db normalization theory: redundant keysOK? - 12-14-2007 , 03:28 PM

Re: Newbie question about db normalization theory: redundant keysOK? - 12-14-2007 , 03:40 PM