Are all relational schemas with partial dependencies not in 3NF?

Question

From Database Management Systems, by Raghu Ramakrishnan, ‎Johannes Gehrk

Let R be a relation schema, F be the set of functional dependencies (FDs) given to hold over R , X be a subset of the attributes of R, and A be an attribute of R. R is in third normal form if, for every FD X -> A in F, one of the following statements is true:

• A $\in$ X; that is, it is a trivial FD, or
• X is a superkey, or
• A is part of some key for R.

Suppose that a dependency X -> A causes a violation of 3NF. There are two cases:

• X is a proper subset of some key K. Such a dependency is called a partial dependency.
• X is not a proper subset of any key. Such a dependency is called a transitive dependency, because it means we have a chain of dependencies K->X-> A.

1. Am I correct that "Figure 19.7 Case 1" and "Figure 19.8 Case 1" are not 3NF, while "Figure 19.8 Case 2" is 3NF?

2. Why is there no either "Figure 19.7 Case 2: A in KEY" or "Figure 19.7 Case 2: A in KEY1" where "KEY1" is different from "KEY"? Does that case exist?

   Am I correct that

   • If that case can exist, it will be 3NF.

   • If that case can't exist, all relational schemas with partial dependencies are not in 3NF?

Thanks.

Answer 1

Premise: The figures depict functional dependencies, not relation schemas. It is improper to say that a functional dependency is or not in any normal form. So, I suppose that you are asking if the functional dependencies violate or not a normal form.

You say:

Suppose that a dependency X -> A causes a violation of 3NF. There are two cases:

• X is a proper subset of some key K. Such a dependency is called a partial dependency.

• X is not a proper subset of any key. Such a dependency is called a transitive dependency, because it means we have a chain of dependencies K->X-> A.

You have given one correct definition of 3NF a few lines above, and according to this definition the two cases are true only if A is not a prime attribute (that is, is not part of a key), otherwise the dependency does not violate the 3NF.

So the answer to the question which is title of your post: “Are all relational schemas with partial dependencies not in 3NF?” is NO.

Am I correct that "Figure 19.7 Case 1" and "Figure 19.8 Case 1" are not 3NF, while "Figure 19.8 Case 2" is 3NF?

Yes, if you mean that in these cases the dependencies do not violate the 3NF.

Why is there no either "Figure 19.7 Case 2: A in KEY" or "Figure 19.7 Case 2: A in KEY1" where "KEY1" is different from "KEY"? Does that case exist?

Yes, both cases could exists: in general in a relation scheme there can be multiple candidate keys (and possibly with empty intersection).

Am I correct that

If that case can exist, it will be 3NF.

If that case can't exist, all relational schemas with partial dependencies are not in 3NF?

Since both cases can exists, the relevant dependencies do not violate the 3NF, while it is incorrect the assertion that “all relational schemas with partial dependencies are not in 3NF” (if a partial dependency determines a prime attribute, than it does not violate the 3NF).