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

Barry wrote:
> Jan Hidders wrote:
>
> > Ah, I think I am beginning to see your point. But I am not really
> > sure what a "functional *in*dependency" is. Is it really the same
> > as a multi-valued dependency? Note that the absence of a functional
> > dependency is not the same as a multi-valued dependency. Can you
> > give me a (formal or informal) definition of a functional
> > independency?
>
> It'll have to be informal ...
>
> I'm thinking of non-key "a" (say, home_address) and non-key "b" (say,
> current_salary) each of which have a relationship to key "k"
> (person_identifier, say) but where the "a"'s relationship to "k" is
> different, and independent of, "b"'s relationaship to "k".
>
> In other words, f(k) -> a, the function "f" which applied to an
> instance value of "k" gives me the current corresponding value of "a"
> is different (g(k) -> b) to that which gives me "b". In the earlier
> posting, I gave "f" the more meaningful name "lives_at", and "g" the
> name "earns".

Ok. So you are only talking about functional dependencies here. ('f' and 'g' are functions, right?) I am not really sure what you mean by 'independent' and 'different' when you talk about 'f' and 'g'. My guess is that you mean the the values of 'a' and 'b' are not connected in the sense that the value of 'a' is not restricted in any way by the value of 'b'. Is that right?

I suspect that what you actually mean with 'independent' is that there is no functional dependency between 'a' and 'b'. Does this perhaps ring a bell?

Anyway, like I said, since you are talking about functional dependencies only, 4NF doesn't come into it.

> When I teach normalisation (my understanding of it, at least ;-), I'd cite
> the (historical, again) example of a friend who moved across the street,
> changing address *but* able to retain the original phone# since he was
> still connected to the same switch. This leads to interesting discussions
> (well, for me at least!) on the importance of data definition, the nuances
> it might embody, and how it plays out during normalisation ...

Hm. This still sounds a bit as if you are saying 'phone#' is independent of 'address' so the table should be split. That is not what normalization theory says. Only when the relationship between the key and 'phone#' becomes a one-to-many or many-to-many relationsip will 4NF be an issue.

> > ... because in standard normalization
> > theory dependencies are anonymous. ...
>
> On the face of it, I'm not sure I accept this, but I probably need to
> think about it a bit. On the face of it "dependency" implies a
> relationship between (at least) two things which I would think
> requires each end of the relationship *and* the relationship itself
> to be well-defined to be able to work with it all. Please feel free
> to explain what you meant ...

What I mean is that if you have to decide to split or not, the only thing you need to know is that certain dependencies hold. Whether certain dependencies have the same name or not, is simply not relevant. These things are nowhere mentioned in the theory and do not play a role in any decision.

> In the meantime, I can't help but feel the real issue is the branch
> of mathematics used to state the proposition. And that was my
> albeit-gratuitous reference to relational algebra v. relational
> calculus. As I remember it, you can state the same thing either way
> but that might not be obvious to the casual reader given the
> different notational styles of those mathematical branches.

Yes, but both are defined in a formal and exact way. That also holds for the definitions used in normalization theory. But your terms are still informal and not really well defined.

> And, to come full circle,

We seem to be doing that a lot. :-)

> I long ago accepted the above "f" v. "g"
> formulation of 4NF as equivalent to the Date version (which, to be
> honest, worries me a bit 'coz it seems to use far too many words for
> what is supposed to be a formalism rooted in mathematics).

In all modesty I think I can say that I have a fair grip of the mathematics of normalization, and as such I can say that the formulations by Date are correct and precize; they can be translated directly to the mathematical definitions in set theory.

> To try to connect the two: If two non-key attributes have different
> relationships to the key, then we have an MVD situation and resolve
> it by splitting 'em apart!

There are a few problems here. First, you still haven't defined what 'different relationships' means. When are two relationships exactly different? Then there is the problem that you what you call the key is actually 'what you would *like* to be the key' where normally in normalization theory we only call those things a key that actually '*are* a key'. A subtle but very important distinction. Finally, in everything that you have explained to me until now I haven't seen a single MVD. So you are still talking about only functional dependencies.

Let me also try to connect everything together. Consider again the original example:

  R(name, address, phone#)

Suppose we know that every person has a unique name, one address and phone#. The consequence is that {name} is a (candidate) key and we have the dependencies name -> address and name -> phone#. Now, you said that these dependencies have to be 'independent'. My guess is that what you actually mean by this is that there are no dependencies like address -> phone# or phone# -> address. Note that such dependencies imply that an update to address may lead to an update on phone# (the phone# belongs to the address) or vice versa.

But this has nothing to do with 4NF but is simply solved by 3NF. In Date's formulation you would say that there are 'transitive dependencies' (because name -> phone# but also name -> address -> pone#) which are not allowed in 3NF. Or in my formulation that there is a dependency address -> phone# with phone# not a key attribute and address not a key, which is not allowed in 3NF.

So what you have been talking about all this time is nothing else then plain good old 3NF. :-)

Kind regards,

-- 
  Jan Hidders