7.39
Given the three goals of relational database design, is there any reason to design a database schema that is in 2NF, but is in no higher-order normal form? (See Exercise 7.19 for the definition of 2NF).
2NF does not prohibit as much repetition of information since the schema \((A,B,C)\) with dependencies \(A \rightarrow B\) and \(B \rightarrow C\) is allowed under 2NF, although the same \((B,C)\) pair could be associated with many \(A\) values, needlessly duplicating \(C\) values. To void this we must go to 3NF. Repetition of information is allowed in 3NF in some but not all of the cases where it is allowed in 2NF. Thus, in general, 3NF reduces repetition of information. Since we can always achieve a lossless decomposition into 3NF, there is no loss of information needed in going from 2NF to 3NF.
Note that the decomposition \(\{ \{A, B\}, \{B, C\} \}\) is a dependency-preserving 3NF decomposition of the schema \((A, B, C)\). However, in case we choose this decomposition, retrieving information about the relationship between \(A\), \(B\) and \(C\) requires a join of two relations, which is avoided in the corresponding 2NF decomposition.