An organization needs to maintain database having five attributes \(A, B, C,…
2019
An organization needs to maintain database having five attributes \(A, B, C, D, E\). These attributes are functionally dependent on each other for which functionality dependency set \(𝐹\) is given as: \(F:\{A→BC,D→E,BC→D,A→D\}\). Consider a universal relation \(R(A, B, C, D, E)\) with functional dependency set \(𝐹\). Also all attributes are simple and take atomic values only.
Assume that given table R is decomposed in two tables
\(R_1(A, B, C)\) with functional dependency set \(f_1=\{A→B,A→C\}\) and
\(R_2(A, D, E)\) with FD set \(F_2=\{A→D,D→E\}\)
Which of the following option is true w.r.t. given decomposition?
- A.
Dependency preservation property is followed
- B.
\(𝑅_1\)and\(𝑅_2\)are both in\(2 𝑁𝐹\) - C.
\(𝑅_2\)is in\(2 NF\)and\(𝑅_3\)is in\(3 𝑁𝐹\) - D.
\(𝑅_1\)is in\(3 𝑁𝐹\)and\(𝑅_2\)is in\(2 NF\)
Attempted by 193 students.
Show answer & explanation
Correct answer: D
Step 1: Find the candidate key for the universal relation R(A,B,C,D,E).
From the given dependencies A → B C and A → D, and D → E, A determines B, C, D and via D determines E. Therefore A → A,B,C,D,E, so A is a candidate key.
Step 2: Check dependency preservation for the decomposition R1(A,B,C) and R2(A,D,E).
Projected FDs: R1 has A → B and A → C; R2 has A → D and D → E. The union of these gives A → B,C,D,E and D → E.
The original FD BC → D is not present in the projections and cannot be derived from the projected FDs because BC does not determine A. Hence BC → D is not preserved.
Conclusion: The decomposition does not preserve all original dependencies (dependency preservation is not followed).
Step 3: Determine normal forms of R1 and R2.
R1(A,B,C): A is a key for R1, and all FDs in R1 have a key on the left-hand side. Therefore R1 is in Third Normal Form (and also in 2NF and BCNF in this case).
R2(A,D,E): A is a key for R2, but there is a dependency D → E where D is a non-key attribute determining another non-key attribute. This is a transitive dependency (A → D → E), so R2 violates Third Normal Form. Since the key is single-attribute A, there are no partial-key dependencies, so R2 satisfies Second Normal Form.
Final conclusion:
R1(A,B,C) is in Third Normal Form.
R2(A,D,E) is in Second Normal Form but not in Third Normal Form.
The decomposition does not preserve all original functional dependencies (BC → D is lost).
A video solution is available for this question — log in and enroll to watch it.