130 views
3 3 votes

Consider $R(A,B,C,D,E,F,G,H,I,J)$ with $F=\{AB\to CD,\ D\to EFG,\ FG\to H,\ A\to I,\ AB\to EG,\ AI\to IJ\}$. The candidate key is $AB$.

Which of the following is a valid $\text{2NF}$ decomposition?

  1. $R_1(A,I,J)$
    $R_2(A,B,C,D,E,F,G,H)$
     
  2. $R_1(A,I)$
    $R_2(A,B,C,D,E,F,G,H,J)$
     
  3. $R_1(A,J)$
    $R_2(A,B,C,D,E,F,G,H,I)$
     
  4. No decomposition is required because the only explicitly given partial dependency is $A\to I$

1 Answer

1 1 vote

The candidate key is $AB$.

Since $A$ is a proper subset of $AB$, first consider $A^+$.

We have $A\to I$.

$\therefore A^+=\{A,I\}$.

Now the FD $AI\to IJ$ can be used because both $A$ and $I$ are available.

Thus $J$ is also obtained:

$A^+=\{A,I,J\}$.

Therefore, both $A\to I$ and $A\to J$ hold in $F^+$.

Since $I$ and $J$ are non-prime attributes and $A$ is a proper subset of candidate key $AB$, both represent partial dependency information that must be removed from the main relation.

Hence create $R_1(A,I,J)$.

The remaining relation is $R_2(A,B,C,D,E,F,G,H)$.

Now check the options.

In B, $J$ remains in $R_2$. But $A\to J$ is implied by $F$, so $R_2$ would still contain a partial dependency.

In C, $I$ remains in $R_2$, and the explicit partial dependency $A\to I$ remains.

Thus neither is in $\text{2NF}$.

The important trap is that a $\text{2NF}$ violation does not have to appear explicitly in the original FD list. 

Dependencies in $F^+$ matter as well.

Therefore, the correct answer is A.

Answer:
Position:
Show:

Related questions

3 3 votes
1 1 answer
98
98 views
GO Classes asked Sep 17
98 views
Consider $R(A,B,C,D,E,F,G,H,I,J)$ with $F=\{AB\to C,\ A\to DE,\ B\to F,\ F\to GH,\ D\to IJ\}$. The candidate key is $AB$.Which of the following is a valid $\text{2NF}$ de...
2 2 votes
1 1 answer
109
109 views
GO Classes asked Sep 17
109 views
After representing the attributes atomically, consider $\text{Inventory(PartNbr, Warehouse, Location, QOH, Weight, PartColor)}$ with $\text{PartNbr} \to \text{Weight, Par...
2 2 votes
1 1 answer
94
94 views
GO Classes asked Sep 17
94 views
Consider $\text{CLASS(CourseNo, SectionNo, RoomNo, Capacity)}$ with candidate key $\text{(CourseNo, SectionNo)}$ and functional dependency $\text{RoomNo} \to \text{Capaci...
2 2 votes
1 1 answer
93
93 views
GO Classes asked Sep 17
93 views
Consider $R(A,B,C,D,E)$ with $F=\{A\to E,\ EC\to BD,\ D\to C\}$. The candidate keys are $AC$ and $AD$. Which decomposition correctly removes the $\text{2NF}$ violation?$R...