databasedatabase-normalizationfunctional-dependencies

2nd normal form violation with composite LHS of FD has prime and non-prime attributes together


Given the relation R = {v,w,x,y,z} and functional dependency set {v -> w, y -> z, yz -> v, wx-> z} find a BCNF decomposition and check if dependency preservation holds.

I tried to find a minimal cover:
v -> w
y -> z
y -> v
wx -> z

I found only one candidate key:
xy

I started to check normal forms:

1st Normal Form: check

2nd Normal Form:

These dependencies violate 2NF:
y -> z
y -> v
wx -> z

I've never seen an example like the 3rd FD where the left-hand side is a composite of prime and non-prime attributes.

How do we solve this kind of situation?

Do we make a new relation for the 3rd FD making w and x primary key?


Solution

  • Whether one considers a FD (functional dependency) to "violate 2NF" depends on one's definition of 2NF. A common definition of 2NF is, no FDs hold where a non-prime attribute is partially functionally dependent on a CK (candidate key). So are the violating FDs the ones where a non-prime attribute is partially functionally dependent on a CK? Or the ones where a non-prime attribute is functionally dependent on a proper subset of a CK, by which the preceding FDs are partial? Or both? And/or others? Or what? The fact is that it isn't individual FDs that violate NFs but the set of all FDs that hold. If you want to talk about individual FDs violating then you need to give a definition for 2NF & then give & justify a definition of violating FD based on how the definition talks about such FDs.

    The following uses the 2NF definition above & talks about "bad" FDs explicitly disallowed by that definition, where a non-prime attribute is partially functionally dependent on a CK.

    Those three FDs are not bad. A FD is partial when its right hand side is functionally determined by a proper/smaller subset of its left hand side. None of those three FDs are partial dependencies on a CK (candidate key). None of them are even partial, because none has a right hand side that is determined by a subset of the left hand side (determinant). And none of them are even on a CK, because none of them have a CK as their left hand side.

    You might consider the first two to "violate 2NF" per a 2NF definition that there are no FDs with left side a proper subset of a CK & right side a non-prime attribute. That definition explicitly disallows those FDs. So we do not have 2NF.

    However the FDs xy->z & xy->v are partial, because proper/smaller subsets of xy determine z & v. And they are bad: xy is a CK and Z & v are non-prime attributes so both have a non-prime attribute partially dependent on a CK. So we do not have 2NF.

    wx->z isn't bad. And it doesn't "violate 2NF" per a 2NF definition that there are no FDs with left side a proper subset of a CK & right side a non-prime attribute.

    It doesn't matter whether "the left-hand side is a composite of prime and non-prime attributes". What matters is what is mentioned in your definitions. (It happens that you will never see such "an example" of a bad or "violating" FD. Because both those require left-hand sides with only CK attributes.)

    Read some academic definitions for partial FD & 2NF. (Many textbooks/presentations/courses are free online.) Memorize and apply definitions, theorems and algorithms exactly. You seem to not understand numerous things: