4NF decomposition with MVD

Question

Consider the table with attributes A and B

 A                B



222              Jack

222              Jill

222              Joe

Here A ->-> B holds true. My book says that the above relation is in 4NF.

I don't understand how it can be in 4NF as for a relation to be in 4NF, A should be a super key for that relation if there is a MVD of form A->->B.

Here how is A a super key ? For same value of A (222), we have multiple values of B.

If `A` is a key column and `B` contains no repeated names for a given key, then this table has to be in 4NF, because there are no non trivial multivalued dependencies. I think you would need a third column to illustrate breaking 4NF. — Tim Biegeleisen, Jan 09 '18 at 05:38
Normalization (including 4NF) is about eliminating redundancies in your table. If `A` be a key, and there are no repeated `(A, B)` relations, then the table is in 4NF. As I mentioned, because it is in 4NF, I would have to add a third column to show what is not 4NF. — Tim Biegeleisen, Jan 09 '18 at 05:41
Ok. Wikipedia says that if union of A and B gives us attributes of entire relation, then it's trivial MVD. Now if you see the example in wikipedia of restaurant, we can combine two MVD's by union property . So new MVD would be restaurant -> pizza delivery(pizza and delivery are two attributes). Now even here union is entire relation but wikipedia says it's not in 4NF. Why is it so ? https://en.m.wikipedia.org/wiki/Fourth_normal_form — Zephyr, Jan 09 '18 at 05:45
Hi Zephyr, that Wikipedia does a great job of explaining 4NF, and I'm not sure that I can do any better. — Tim Biegeleisen, Jan 09 '18 at 05:52
@TimBiegeleisen I understood the entire article of wikipedia. But the point is they have not considered union property of MVD and treated each MVD separately. Did you read my previous comment? — Zephyr, Jan 09 '18 at 05:54
@TimBiegeleisen You might be interested in my answer. Re your comments, non-trivial MVDs do hold. (But the table isn't in 4NF & {A} isn't a CK.) — philipxy, Jan 09 '18 at 11:08
@philipxy Nice answer, but I have yet to encounter a real world scenario where 4NF is of concern. — Tim Biegeleisen, Jan 09 '18 at 11:10
Your question & comments do not get definitions correct. (That's true for other questions of yours.) If you have a question about the wiki article you should post a new question. But first you should apply the correct definition of 4NF. — philipxy, Jan 09 '18 at 11:11
@TimBiegeleisen [That's only because it is usually so obvious semantically when 4NF & 5NF are violated--it requires that a table meaning is the AND of independent meanings--that people avoid violations intuitively.](https://stackoverflow.com/a/47735550/3404097) (Just because violations end up being rare doesn't make them unproblematic/unimportant.) — philipxy, Jan 09 '18 at 11:36
Maybe you don't mean the *value* is in 4NF (it isn't). Maybe you mean that it's an example value of a base relation you know is in 4NF. It would help if you would explain where you got this. Eg there's a similar variable in Elrasmi-Navathe 6th edition Figure 15. An MVD held in *a different base relation* decomposed to that one. There is no theorem that a MVD holds in a component that has the same attributes. (The definition of MVD involves *all* the attributes of its relation.) But the MVD still held. But because it's trivial. They even say that. That's why {A] -->> {B} holds here. — philipxy, Jan 10 '18 at 18:24

philipxy · Answer 1 · 2018-01-09T11:40:08.983

That relation isn't even in 2NF

My book says that the above relation is in 4NF

Why do you say that? What is your book? That relation value has sole CK {B} but satisfies the FD {} -> A which is a partial dependency of a non-prime attribute on a CK so it isn't even in 2NF.

Your implication is wrong

for a relation to be in 4NF, A should be a super key for that relation if there is a MVD of form A->->B

Let us simplify that complicated phrasing: if a relation is in 4NF then if MVD (multi-valued dependency) {A} ->> {B} holds then {A} is a superkey.

From Wikipedia:

A table is in 4NF if and only if, for every one of its non-trivial multivalued dependencies X ↠ Y, X is a superkey

This tells us that if a relation is in 4NF then if non-trivial MVD X ->> Y holds then X is a superkey.

So it doesn't tell us what you claimed. You left out "non-trivial".

But since the relation isn't in 4NF the inner implication doesn't matter.

That MVD is trivial so if we had 4NF then the inner implication wouldn't apply

In your relation {A} ->> {B} holds, but it is trivial since {A} U {B} is the set of all attributes and

A trivial multivalued dependency X ↠ Y is one where either Y is a subset of X, or X and Y together form the whole set of attributes of the relation

So if the relation were in 4NF then {A} would not have to be a superkey.

(The (trivial) (binary) JD (join dependency) corresponding to the MVD is *{{A}, {A, B}}, ie the relation is the join of its projections on {A} & {A, B}.)

Other MVDs hold that violate 4NF

The definition tells us that a relation being in 4NF requires that if a non-trivial MVD X ->> Y holds then X is a superkey.

If you list all the MVDs relevant to that relation then you will find that they are all trivial except {} ->> {A} and {} ->> {B}. It happens that both of those hold. (They express the same condition, that the same JD holds, that the relation is a certain join.) But {} isn't a superkey. So 4NF is violated.

(In terms of JDs: The only possible non-trivial binary JD is *{{A}, {B}}, which happens to hold. But that is not implied by the CKs, ie it doesn't have to hold when the CK set is {{B}}, ie (per Fagin's membership algorithm) the join of its two elements is not on a common column set that includes a CK of both. So the relation is not in 4NF.)

But we already know 4NF is violated, because we know 2NF is violated.

How is {} -> B valid ? How can nothing or empty set determine B? — Zephyr, Jan 09 '18 at 11:23
I didn't say {} -> B. I said {} ->> {B}. Plug {} & {B} into a definition of when a MVD holds. Eg the relation is {A} U {} join {} U {B}. I did say {} -> A. Plug {} & A into a definition of when a FD (determining an attribute) holds. (Unfortunately, many textbooks & of course web pages have wrong & sloppy definitions.) — philipxy, Jan 09 '18 at 11:30
PS I addressed FDs with {} in [this answer to a question by you](https://stackoverflow.com/a/47632874/3404097). (Also I wrote about proper reasoning & use of terms, definitions & theorems.) — philipxy, Jan 09 '18 at 23:21
For downvoters: Please first say what you think is wrong, and read my last comment on the question re NFs of values vs variables. — philipxy, Jan 13 '18 at 02:13

4NF decomposition with MVD

1 Answers1