Re: 4NF is Where It Is At! [WAS Re: 1:1 relationships]

Barry wrote:
> Jan Hidders wrote:
> > Barry wrote:
> > >
> > > And this is where I've concluded 4th Normal Form is actually the
> > > most important of all. At the risk of being flamed for
> > > overly-simplifying it, 4NF asks: do all non-key attributes have
> > > the *same* relationship to the key? If not, then split the
> > > non-key attributes apart.
> >
> > I do not really know how to say this but I think you are
> > over-simplifying. :-) The problem is that you are talking about
> > non-key attributes where the real 4NF talks about *all* attributes.
> > So your normal form is stronger than 3NF but weaker than 4NF and
> > different from BCNF. So I suggest that we call it the BJNF and
> > define it as follows:
> >
> > A relation is in BJNF if
> > 1. all multi-valued dependencies are functional dependencies, and
> > 2. it is in 3NF.
> >
> > I conjecture that -- just as 3NF -- you can always reach BJNF while
> > staying dependency preserving.
>
> Been a *long* time since I've had an opportunity to discuss this, so
> I'm doubtless in need of a refresher. But the 4NF formulation I
> remember is something like:
>
> For tuple (k, a, b) where k is the key, a and b are non key attributes, and
> f( k ) -> a, then f( k ) -> b for 4NF to be satisfied. If g( k ) -> b,
> then the tuple needs to be decomposed into (k, a) and (k, b).

Hm, this looks a little like a mixture of 4NF and 3NF. (Apart from the

fact that it is the simplified version for three columns with one key.)
For instance, if k is the key then f(k)->b is satisfied by definition.
But I suspect your definition of "the key" differs slightly from mine.

Let me give you the full definitions that I prefer to use: (I denote functional dependencies as X->Y where X and Y are sets of attributes, and multi-valued dependencies as X->>Y)

3NF: A relation is in 3NF if it holds for every non-trivial functional dependency X->Y with Y a single non-key attribute, that X is a candidate key.

BCNF: A relation is in BCNF if it holds for every non-trivial functional dependency X->Y that X is a candidate key.

4NF: A relation is in 4NF if it holds for every non-trivial multi-valued dependency X->>Y that X is a candidate key.

I have a feeling that you are now slightly overwhelmed and wondering "what is a candidate key?" and "when is a dependency trivial?" and maybe even "what on earth is a multi-valued dependency?". :-) If so, just ask me, and I will explain them. These concepts are quite easy to understand.

> The classic example used to be
> lives_at( person ) -> current_address
> and
> lives_at( person ) -> current_home_ph#
> but
> lives_at( person ) -/-> current_salary

Yes, this is a correct example. Let me explain how the definition of 4NF deals with this. We are talking about the following relation

  R(Person, Curr_addr, Curr_home_ph, Curr_sal)

It follows from the dependencies you gave that there is only one candidate key for this relation: {Person, Curr_sal}. Furthermore we now that the MVD Person->>Curr_sal holds but Person is not a candidate key. So this relation is not in 4NF and you need to "split off" this dependency. As a matter of fact, this relation is not even in 3NF because the dependencies Person->Curr_addr and Person->Curr_home_ph hold and both Curr_addr and Curr_home_ph are non-key attributes, but Person is not a candidate key. So it is not really a very good example.

So, if you have any questions, just let me know. There are also some pretty good resources on the net, from quick introductions to thorough formal treatments. I can give you some if you want me to.

-- 
  Jan Hidders