What normal forms are these two relations in?

Question

I have a set of relations {A, X, Y, Z}:

Given that the functional dependencies: AX→Y, AX→Z, Y→A, Z→X, I would think that this relation would be in the normal form 1NF, because there is a partial dependency for the composite key, YZ; Y determines A and Z determines X. However, I ran across this example on a university website claiming the relationship was in 3NF. Am I wrong about this relation being in 1NF?

On a similar note, given the same relation but with functional dependencies: A→XY, Y→AZ, would this be in 3NF or BCNF, I do not see any reason why this would not be in BCNF, but I have seen a similar example where there was a further decomposition, that was also BCNF. So is the decomposition wasteful because the original relation with given dependencies was already in BCNF?

Answer 1

The relation is actually in 3NF (so that it is also in 1NF and 2NF). The reason is that each attribute of the relation is prime, that is, it belongs to a (candidate) key (the are four keys in this relation: (A X), (A Z), (X Y), (Y Z)).

The definition of the 2NF (which has only an historical interest), is the following (Database System Concepts, 6th edition, Korth, Silberschatz, Sudharshan):

A functional dependency X → Y is called a partial dependency if there is a proper subset Z of X such that Z → Y . We say that Y is partially dependent on X. A relation schema R is in second normal form (2NF) if each attribute A in R meets one of the following criteria:

• It appears in a candidate key.

• It is not partially dependent on a candidate key.

so the relation respects this definition, since each attribute appear in a key.

While the definition of Third Normal Form is (same source as above):

A relation schema R is in third normal form with respect to a set F of functional dependencies if, for all functional dependencies in F⁺ of the form X → Y, where X ⊆ R and Y ⊆ R, at least one of the following holds:

• X → Y is a trivial functional dependency.

• X is a superkey for R.

• Each attribute A in Y − X is contained in a candidate key for R.

so the relations respects this definition too.

For your second question, the relation has two keys, A and Y, and it is already in BCNF as well as in 3NF.

Note that the analysis algorithm to decompose a relation in BCNF should return the original relation, since no dependency violates its definition (each determinant is a superkey).