2

There are many related questions on SO, but none that I can find that answer this question:

Is it possible to have a 3NF relation that can be lossless-join decomposed into BCNF relations while preserving dependencies?

I'm aware that you can decompose a 3NF relation to BCNF if you are prepared to loosen one or more dependencies. And Beeri and Bernstein have proved that FDs of the form {AB->C, C->B} give a 3NF relation that can't be reduced to BCNF. But is there even a case where you are in 3NF and you can reduce to BCNF?

For extra unofficial nerd points, I'd love to know a good term for dependencies allowed by 3NF but not BCNF? It's so easy to distinguish between 1NF, 2NF and 3NF on the basis of partial and transitive dependencies, but to my mind half the problem with BCNF is there's no easy name for the type of dependency prohibited.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
beldaz
  • 4,299
  • 3
  • 43
  • 63

1 Answers1

1

But is there even a case where you are in 3NF and you can reduce to BCNF?

Yes. Wikipedia has an example. 

Court  Start Time  End Time  Rate Type
--
1      09:30       10:30     SAVER
1      11:00       12:00     SAVER
1      14:00       15:30     STANDARD
2      10:00       11:30     PREMIUM-B
2      11:30       13:30     PREMIUM-B
2      15:00       16:30     PREMIUM-A

The functional dependencies for that example aren't listed, but you can derive them. I've listed the FDs below.

AB->CD
AC->BD
BD->AC
CD->AB
ABC->D
BCD->A
ABD->C
ACD->B
D->A

For extra unofficial nerd points, I'd love to know a good term for dependencies allowed by 3NF but not BCNF? . . . half the problem with BCNF is there's no easy name for the type of dependency prohibited.

In BCNF, every determinant (left-hand side) must be a candidate key. Or you could say that every arrow is an arrow out of a candidate key. Turning that around, BCNF prohibits determinants that are not candidate keys.

The candidate keys in the Wikipedia article are {AB, AC, BD, CD}. The starting relation isn't in BCNF, because of the functional dependency D->A. The determinant of that FD, D, isn't a candidate key.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • Yes, that example is certainly 3NF. However the decomposition to BCNF involves a sleight of hand by introducing an additional field `Member`. If that field had been included in the original schema I'm pretty sure it would no longer be in 3NF. (I'll leave that as an exercise, as I might set it as an activity for my own students) – beldaz Mar 02 '15 at 00:07
  • Normalization through BCNF never *requires* introducing new columns, right? – Mike Sherrill 'Cat Recall' Mar 02 '15 at 01:21
  • Well at least for _decomposition_, you should have the same set of attributes in both the start and end schemas. _Normalization_ is the process of moving to a higher normal form through decomposition. – beldaz Mar 02 '15 at 01:28