Re: looking for old Datamation poster on Normalizing

From: Jan Hidders <hidders_at_gmail.com>
Date: 4 Sep 2006 02:40:04 -0700
Message-ID: <1157362804.358560.134490_at_h48g2000cwc.googlegroups.com>


mrettig wrote:
> Hello,
> I'm the author of the poster, and stumbled across this conversation
> thanks to server logs.

Welcome. :-)

> When I created the new reproduction of the old poster, I did find a few
> mistakes. But... I'm writing this in a jet-lagged state from Singapore,
> and it's the wrong time to ponder 4th normal form. If someone here
> cares to suggest how to create a better example of 4NF for the poster
> -- stating an appropriate situation and presenting an appropriate table
> design -- I'd love to hear it. If I use it, I'll credit you and send
> you a print.

I see roughly tree options.

Option 1:

You could go with my suggestion in the preceding post. So keep the example as it is, but let every puppy have a set of costumes. After all, we need MVDs, not just FDs. Then the key of Puppy Tricks becomes {Puppy Number, Trick ID, Costume}. Since this not satisfies 2NF ("Trick where learned" and "Skill level" depend on part of the key) you should split to:

  • (Puppy Number, Trick ID, Trick Where Learned, Skill Level) with key {Puppy Number, Trick ID}
  • (Puppy Number, Trick ID, Costume) with key {Puppy Number, Trick ID, Costume}

Now we are in BCNF, but the latter relation is not in 4NF because of the MVD "Puppy Number" ->> "Costume". So we split it to:

  • (Puppy Number, Costume) with key {Puppy Number, Costume}
  • (Puppy Number, Trick ID) with key {Puppy Number, Trick ID}

The second relation is contained in another relation so is removed.

Option 2:

But explaining all that wouldn't fit in the little box. :-) Since you only want to get the basic intuition accros it might suffice to simply show 'Puppy Tricks" (with the correct key, of course) and then point out that that there is an MVD for Costume with a left-hand side that is not the key, so it has to be taken out. You could even point out that it is somewhat similar to the 2NF case, but here it's different since 'Costume' is not *functionally* dependent on anything. The result of splitting off (Puppy Number, Costume) with key {Puppy Number, Costume} would get you in 4NF immediately. You'd ignore the fact that you actually are not in 2NF to begin with, but normalization theory doesn't prescribe the order of normalization anyway.

Option 3:

Or you could take a simpeler and more straightforward example. You already mention the Kennel-Puppy relationship. If you mix that with another one-to-many relationship like Kennel-Breed you get (Kennel Code, Puppy ID, Breed) with key {Kennel Code, Puppy ID, Breed} and no FDs, so in BCNF, but not in 4NF because of "Kennel Code" ->> "Puppy ID" and "Kennel Code" ->> "Breed".

  • Jan Hidders
Received on Mon Sep 04 2006 - 11:40:04 CEST

Original text of this message