My exam answers key and my answer (and answers of online tools) are different for the typical "Determine highest normal form of relation" question and I want to know why.
Exam question: For the given relation R with schema H = {A, B, C, D, E} and functional dependencies F = {{B, C} -> {D, E}, {C, D} -> {B, E}, {D} -> {C}, {E} -> {B}}. Determine the highest normal form of R. Assume it's in the 1NF.
My answer:
I already have 1NF. Next I check 2NF.
To do that, I need candidate keys. "A" is not in any dependency, so it has to be in the key. I can also add "D", and from {D} -> {C} I have {A, C, D}. Then from {C, D} -> {B, E} I have all {A, B, C, D, E}, so the {A, D} is indeed a candidate key. I can do the same for {A, B, C} and {A, C, E}, so I have candidate keys: {A, D}, {A, B, C}, {A, C, E}.
2NF requires that "no non-prime attribute can be functionally dependent on any proper subset of any candidate key; a non-prime attribute is not a part of any candidate key of the relation". But I have {B, C} -> {D, E}, so E (a non-prime attribute) depends on {B, C} (a proper subset of {A, B, C}), so it's not in 2NF. Therefore it's only in 1NF.
The exam answer:
The relation is in the 3NF. Also this handy tool which checks normal form tells me it's 3NF.
My question:
Is this in 1NF or 3NF? My only doubt is for {B, C} -> {D, E} dependency. As I've written above, E is non-prime, but {D, E} as the whole contains 1 prime and 1 non-prime attribute. Do I make some mistake here?
Assuming F a cover of the functional dependences of R, you are correct assuming that the candidate keys are AD, ABC and ACE. So all the attributes are primes, no dependency can violate the 3NF, the relation is in 3NF, and for this reason it is also in 2NF.