Normal Form Practice Questions

Option D: $R_1(A, B) \cap R_2(A, C, D) = \{A\}$. Since $A \to B$, $A$ is a superkey for $R_1(A, B)$. The condition $R_1 \cap R_2 \to R_1$ holds. Hence lossless. Option A: intersection is empty - lossy. Option B: $R_1 \cap R_2 = \{A, C\}$. $AC^+ = \{A, B, C, D\}$ but $AC$ is not minimal key for either sub-relation in a way that satisfies the binary lossless condition - needs further verification and does not hold cleanly. Option C: $R_1 \cap R_2 = \{A, D\}$. $AD^+$ does not determine all of $R_1$ or $R_2$.

Split: $A \to B,\ A \to C,\ B \to A,\ B \to C,\ C \to A,\ C \to B$. Check redundancy: $A \to C$: $A^+$ without $A \to C$ = via $A \to B \to C$ = $\{A,B,C\}$. Redundant! Remove. $B \to C$: $B^+$ without $B \to C$ = $\{B,A\} \to \{B,A,C,...\}$. $B \to A,\ A \to B$ cycle, $A \to C$ removed... $B^+ = \{B,A\}$, $C \notin$. Not redundant. Similarly $C \to B$ not redundant. After removing $A \to C$, check $B \to C$: $B \to A \to B$ (cycle), $B^+ = \{B,A\}$... hmm, $C$ still unreachable without $B \to C$. Keep. Final canonical cover after merging unique LHS: $\{A \to B,\ B \to AC,\ C \to AB\}$ - 3 FDs.

1. Lossless Join: $R_1 \cap R_2 = \{A\}$. Since $A \to BC$, $A$ is a superkey for $R_1(A, B, C)$. The condition $R_1 \cap R_2 \to R_1$ holds. Hence lossless. 2. Dependency Preservation: $A \to BC$ is preserved in $R_1$ ✓. $D \to A$ is preserved in $R_2$ ✓. All FDs are preserved.

Lossless check: $R_1 \cap R_2 = \{B, C\}$. $BC \to DE$ so $BC \to E$, making $BC$ a superkey for $R_2(B, C, E)$. Merge $R_1$ and $R_2$ into $R_{12}(A, B, C, D, E)$. $R_{12} \cap R_3 = \{A\}$. $A \to BCD$ so $A$ is a superkey for $R_{12}$. Lossless. Dependency check: $A \to BCD$ in $R_1$ ✓; $BC \to DE$ ($BC \to D$ in $R_1$, $BC \to E$ in $R_2$) ✓; $B \to E$ in $R_2$ ✓. All FDs preserved.

$A \to C$ is redundant ($A \to B \to C$). Remove it. Remaining: $\{A \to B,\ B \to C,\ C \to A\}$. No extraneous attributes, all LHS unique. Canonical cover has 3 FDs.

Check redundancy: $A \to B$: without it, $A^+ = \{A\}$, $B$ unreachable. Not redundant. $B \to C$: without it, $B^+ = \{B,D,B,...\} = \{B,D\}$ ($D \to B$ cycle). $C \notin \{B,D\}$. Not redundant. $C \to D$: without it, $C^+ = \{C\}$. Not redundant. $D \to B$: without it, $D^+ = \{D\}$. Not redundant. All 4 FDs are needed. $F_c$ has 4 FDs.

Lossless join requires intersection to be a superkey.

After splitting: $F = \{A \to B,\ A \to C,\ A \to D,\ AB \to D,\ BC \to A,\ BC \to D\}$. For $AB \to D$: check if $A$ or $B$ is extraneous. $A^+ = \{A,B,C,D\}$ (from $A \to B,C,D$). Since $D \in A^+$, $B$ is extraneous in $AB \to D$. Simplified: $A \to D$ (already exists, so $AB \to D$ becomes fully redundant).

For a decomposition of relation R into R1 and R2 to be lossless-join, the intersection (R1 ∩ R2) must be a superkey for R1 OR (R1 ∩ R2) must be a superkey for R2.

1. Identify the intersection (R1 ∩ R2):
   Given R1 = {A, B, C} and R2 = {C, D, E}.
   The intersection R1 ∩ R2 = {C} (ComponentID).

2. Check if {C} is a superkey for R1 ({A, B, C}):
   Calculate the closure of {C} under the given FDs:
   {C}+ = {C}
   Using C → D: {C}+ = {C, D}
   Using D → E: {C}+ = {C, D, E}
   Since {C}+ = {C, D, E} does not contain all attributes of R1 ({A, B, C}), {C} is NOT a superkey for R1.

3. Check if {C} is a superkey for R2 ({C, D, E}):
   Calculate the closure of {C} under the given FDs:
   {C}+ = {C, D, E} (as calculated above)
   Since {C}+ = {C, D, E} contains all attributes of R2 ({C, D, E}), {C} IS a superkey for R2.

Because {ComponentID} (C) is a superkey for PT2, the decomposition is a lossless-join decomposition. Therefore, option B is the correct answer.

A canonical cover $F_c$ satisfies: (1) $F_c^+ = F^+$ (same closure), (2) no functional dependency in $F_c$ contains an extraneous attribute (on either side), and (3) each LHS of a dependency in $F_c$ is unique (no two FDs share the same LHS).

1. Lossless Join: $R_1 \cap R_2 = \{A, B\}$. Since $B \to C$, we have $AB \to C$, so $AB$ is a superkey for $R_1(A, B, C)$. The condition $R_1 \cap R_2 \to R_1$ holds. Hence lossless. 2. Dependency Preservation: $B \to C$ is preserved in $R_1$ and $B \to D$ is preserved in $R_2$. All FDs are preserved.

In 3NF, for every $X \to A$, either $X$ is a superkey OR $A$ is a prime attribute. In BCNF, $X$ MUST be a superkey. If it's in 3NF but not BCNF, it must satisfy the 'A is prime' condition while failing the 'X is a superkey' condition.

For $D_1$: $R_3 \cap R_4 = Y$, and $Y \to ZW$, so $Y$ is a key for $R_4$. Merging $R_3, R_4$ gives $R_{34}(Q, Y, Z, W)$. Now $R_1 \cap R_{34} = Q$, and $Q \to Y$, so it's lossless. This recursive check confirms $D_1$ is lossless.

After splitting $A \to BC$: $\{A \to B,\ A \to C,\ B \to C,\ AB \to D,\ D \to A\}$. Check $B$ extraneous in $AB \to D$: $A^+ = \{A,B,C,D,A\}$ (via $A \to B,\ AB \to D$: actually $A \to B$ gives $B$, then $AB \to D$, so $A^+ \ni D$). So $B$ is extraneous; $AB \to D$ becomes $A \to D$. Now check $A \to C$: $A^+ = \{A,B,C,D,...\}$ without $A \to C$: $A \to B \to C$. Redundant! Only $A \to C$ is redundant by standard steps. So (iii) only.