Register

GO Classes DPP | GATE CS, DA | DBMS | 2NF Decomposition

93 views
2 2 votes

After representing the attributes atomically, consider $\text{Inventory(PartNbr, Warehouse, Location, QOH, Weight, PartColor)}$ with $\text{PartNbr} \to \text{Weight, PartColor}$, $\text{Warehouse} \to \text{Location}$, $\text{PartNbr, Warehouse} \to \text{QOH}$.

The candidate key is $\text{(PartNbr, Warehouse)}$.

Which is a correct $\text{2NF}$ decomposition?

  1. $R_1\text{(PartNbr,Warehouse, QOH)}$
    $R_2\text{(PartNbr,Weight, PartColor)}$
    $R_3\text{(Warehouse, Location)}$
     
  2. $R_1\text{(PartNbr, Warehouse, QOH, Location)}$
    $R_2\text{(PartNbr, Weight, PartColor)}$
     
  3. $R_1\text{(PartNbr, Warehouse, Location, QOH)}$
    $R_2\text{(PartNbr, Weight, PartColor)}$
     
  4. No decomposition is necessary

1 Answer

0 0 votes

The candidate key is $\text{(PartNbr, Warehouse)}$.

Now examine the dependencies.

First, $\text{PartNbr} \to \text{Weight, PartColor}$.

$\text{PartNbr}$ is a proper subset of the candidate key, while $\text{Weight}$ and $\text{PartColor}$ are non-prime.

Therefore, this is a $\text{2NF}$ violation.

Similarly,

$\text{Warehouse} \to \text{Location}$ has $\text{Warehouse}$ as another proper subset of the candidate key and $\text{Location}$ as a non-prime attribute.

So this is another $\text{2NF$} violation.

However,

$\text{PartNbr, Warehouse} \to \text{QOH}$ uses the entire candidate key.

Thus $\text{QOH}$ is fully functionally dependent on the key.

Separate the two partial dependencies:

$R_2\text{(PartNbr, Weight, PartColor)}$

$R_3\text{(Warehouse, Location)}$.

The key-dependent fact remains in $R_1\text{(PartNbr, Warehouse, QOH)}$.

Each resulting relation is in $\text{2NF}$.

In options B and C, $\text{Warehouse} \to \text{Location}$ remains inside a relation whose composite key contains $\text{Warehouse}$, so the partial dependency survives.

Therefore, the correct answer is A.

Voting & Accepting an answer helps improve the community
Answer:
← Previous · GO Classes DPP | GAT...Next → · GO Classes DPP | GAT...
← Previous in category · GO Classes DPP | GAT...Next in category → · GO Classes DPP | GAT...
Position:
Show:

Related questions

3 3 votes
1 1 answer
121
121 views
GO Classes asked Sep 17
121 views
GO Classes DPP | GATE CS, DA | DBMS | 2NF Decomposition
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 $...
3 3 votes
1 1 answer
93
93 views
GO Classes asked Sep 17
93 views
GO Classes DPP | GATE CS, DA | DBMS | 2NF Decomposition
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
86
86 views
GO Classes asked Sep 17
86 views
GO Classes DPP | GATE CS, DA | DBMS | 2NF Decomposition
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
86
86 views
GO Classes asked Sep 17
86 views
GO Classes DPP | GATE CS, DA | DBMS | Decomposition
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...