databasedatabase-normalizationfunctional-dependencies

2nd normal form violation with lhs of the dependency is composite (prime and non-prime together)

I was studying functional dependencies and normalization and I've come across a question. The original question is below:

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

First I tried to find minimal cover and came up with this:

Minimal Cover:
v -> w
y -> z
y -> v
wx -> z

Then I tried to found candidate keys, came up with only one candidate key:

Candidate Keys:
xy

Then I started to check normal forms:

1st Normal Form: check

2nd Normal Form:

I thought the below dependencies are violating 2nd normal form:

1) y -> z
2) y -> v
3) wx -> z

The first two were easy to solve. However, I've never seen an example of the 3rd 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 making w and x primary key?

If I solve that part, the 3rd and BC normal forms will be easy I guess.

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:

• Being in BCNF implies being in all lower NFs. Getting to BCNF does not require going through lower NFs.
• Examples of decompositions you have seen are not presentations of decomposition algorithms.
• We don't normalize via successive NFs. We use an algorithm for the NF we want. (Going through lower NFs can even mean good higher-NF designs become unavailable.)
• When some FDs hold, all the ones implied by them by Armstrong's axioms also hold.
• To determine CKs & NFs it's not enough to know that some FDs hold, we need to know what FDs hold & what FDs don't hold. You need to know a closure or cover of FDs.
• Each time we decompose we get new relations & sets of FDs & CKs for each.
• The FDs that hold in a component are all those of the original whose attributes are in it. (Those of a closure, not just those of a cover.)
• A FD is partial when its right hand side is functionally determined by a proper/smaller subset of its left hand side.
• A common 2NF definition explicitly disallows partial FDs of non-prime attributes on CKs.
• "Violating FD" is not a helpful term, refer to the things that definitions mention.