Search code examples
relational-databasedatabase-normalization

Checking 3rd Normal Form from Functional Dependencies

I have a question about determining whether a relation is in 3rd Normal Form based on functional dependencies.

R = {A,B,C,D,E}
A -> B
BC -> E ED -> A

From this, I determined the candidate keys to be: {ACD},{BCD},{CDE}
Wikipedia says that a relation is in 3rd Normal Form if, for each functional dependency X->Y, it meets at least one of the following requirements: 1. Y is a subset of X 2. X is a superkey Y is a subset of K for some key K

My work: A -> B satisfies 3 because of the key {BCD}, BC->E satisfies 3 because of {CDE}, and CD -> A satisfies 3 because of {ACD}.

Is this the correct way of interpreting those rules?

Solution

  • A -> B expresses a functional dependency. But these

    R1 = {A,B}
    R2 = {B,C,E}
    R3 = {E,D,A}

    express relations. (I'm assuming you meant us to take these as the relations you got from decomposing R.)

    Normal forms apply to relations; they don't apply to functional dependencies. It doesn't make any sense to say "A -> B is in 3NF", or "A determines B is in 3NF".

    So R1 = {A,B} is a relation. It doesn't "satisfy rule 3 because of the key {B,C,D}". It can't, because {B,C,D} isn't in R1.

    Those rules have to do with the candidate keys of the relations that result from decomposing R, not with the candidate keys of R itself.

    Later . . . I see I made an incorrect assumption. You're trying to evaluate R, not decompose it. In that case your reasoning is correct, and R is in 3NF. You could also conclude that R is in 3NF because there are no non-prime attributes. Since there are no non-prime attributes, the right-hand side of every FD must be part of a candidate key.

    But R is not in BCNF. (Or 4NF, or 5NF.) Can you fix that?