1

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.

philipxy
  • 14,416
  • 5
  • 32
  • 77
J.Marvin
  • 15
  • 2
  • 1
    Why would you trasform first the scheme in 2NF to bring a relation in BCNF? The analysis algorithm (presented in many database books) does not require this step. You find the dependencies that violates the BCNF and then uses them to produce decomposed schemes. – Renzo Jun 08 '17 at 08:44
  • @philipxy Thank you for the explanation. However, doesn't BCNF requires the relation to be 3rd normal form, which requires to be 2nd normal form, which requires to be 1st normal form? That's why I was trying to start from 1st and continue 2nd, 3rd etc (that's what I've been taught to do anyway, it's wrong maybe). And could you explain why (y -> z) is not an FD here? y is part of the candidate key and z is a non-prime, doesn't it? What am I missing here? – J.Marvin Jun 08 '17 at 09:20
  • See my answer. PS I said "y->z is not a partial FD here" & I said "read a definition" of "partial FD". Have you? (Rhetorical.) And read a definition of "2NF". ("y is part of" is irrelevant to y->z being partial.) See my recent database normalization answers. – philipxy Jun 08 '17 at 12:14

1 Answers1

0

Those FDs do not violate 2NF. A FD (functional dependency) 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.

However the FDs xy->z & xy->v are partial, because proper/smaller subsets of xy determine z & v. And they violate 2NF because xy is a CK and v is a non-prime attribute. And wxy->z is partial, because its proper/smaller subset xy determines z (since xy is a CK, so determines all subsets). But it doesn't violate 2NF because its determinant isn't a CK.

You will never see "an example [2NF-violating FD] where the left-hand side is a composite of prime and non-prime attributes". Because a 2NF-violating FD has a left-hand side that is a CK, so has no non-prime attributes. Although it's partial, so it has a proper/smaller subset that also determines the right hand side.

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.)
  • Each time we decompose per a bad FD we get new relations, FDs & CKs.
  • The FDs that hold in a component are all those of the original whose attributes are in it. (Ie those of the closure of a cover, 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.
  • 2NF is violated by partial FDs of non-prime attributes on CKs.
philipxy
  • 14,416
  • 5
  • 32
  • 77