BCNF vs 3NF technical questions

Question

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.

Answer 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.