Redirected
edited by
3,420 views
1 votes
1 votes

Consider a schema $R(A, B, C, D)$ and following functional dependencies.

  • $A \rightarrow B$
  • $B \rightarrow C$
  • $C \rightarrow D$
  • $D \rightarrow B$

Then decomposition of $R$ into $R1(A, B)$, $R2(B, C)$ and $R3(B, D)$ is ________

  1. Dependency preserving and lossless join
  2. Lossless join but dependency preserving
  3. Dependency preserving but not lossless join
  4. Not dependency preserving and not lossless join
edited by

5 Answers

2 votes
2 votes

The given relation $R(A,B,C,D)$ and $FDs$ of $R$ = $\left \{ A \rightarrow B, B \rightarrow C, C \rightarrow D, D \rightarrow B \right \}$ 

So, We have the following results from the FD set of $R$ :

$A \rightarrow B,C,D$
$B \rightarrow C,D$
$C \rightarrow D,B$
$D \rightarrow B,C$

1. Dependency Preservation :

We say that in any decomposition {$R1,R2...Rn$} of $R$, Dependency is Preserved when $\left \{ F1 \cup F2 \cup F3....\cup Fn \right \}$ covers $F$ where $F$ is the FD set of $R$ and $Fn$ is the FD set of $Rn$.

So, here in given question, $F1 = \left \{ A \rightarrow B \right \}$

$F2 = \left \{ B \rightarrow C, C \rightarrow B \right \}$

$F3 = \left \{ B \rightarrow D, D \rightarrow B \right \}$

$F1 \cup F2 \cup F3 = \left \{ A \rightarrow B, B \rightarrow C, C \rightarrow B, B \rightarrow D, D \rightarrow B \right \}$

We can see that $F1 \cup F2 \cup F3$ Covers $F$. Hence, Dependency Preserved.

2. Lossless Join :

The strategy to find Lossless Join or Not is that Try to merge all $Ri$ where $i = 1 \,\,to\,\, n$ by taking Two Relations at a time such that the common attribute(s) is a Key in at least one of the tables being merged. If there is some way(at least one) of doing so then Decomposition is Lossless.

Here in the Question, We can merge $R1 \,\,and \,\, R2$ because the common attribute ($B$) is a key in one of the tables ($R2$) and after that We can merge $R3$ with them because then also the common attribute ($B$) is a key in at least one of the tables. So, The decomposition of $R$ into $R1,R2,R3$ is Lossless Join decomposition. 

1 votes
1 votes
The decomposition of R into (A,B), (B,C), (B,D)

A--->B is present in (A,B)

B--->C is present in (B,C)

C---->D is indirectly present in (B<D

i.e.   C---->B

        B--->D

------------------------

       C--->D

so it is dependency preservation

METHOD TO FIND A DECOMPOSITION WHICH IS IN 3 NF AND LOSSLESS JOIN AND DEPENDENCY PRESERVATION

1 Form one relation scheme for each FDs

2 Check for each candidate key .  i.e. Any one of the relation contains in its attribute set supper key of the original relation

 Relation scheme for each given FDs are following

(A,B) (B,C) (C,D), (D,B)

BUT HERE NO supper key so it is not lossless join.

SO ANSWER C IS CORRECT
edited by
0 votes
0 votes

Answer: A. Dependency preserving and lossless join.

Since,

For relation R1(A, B) : A-->B(dep. preserved),

For relation R2(B, C) : B-->C(dep. preserved) and

For relation R3(B, D) :D-->B(dep. preserved)[order of attributes does not matter in relations] .

Also while joining these relations, We have attribute B common in R1, R2 and R3 thus, no loss.

0 votes
0 votes

Dependency preserving but not lossless join. As in relation R2(BC) , B is key attribute  and in  R3(BD) D is key attribute. For lossless join common attribute must be key in one table. B is common attribute in both R1,R2 but it is not key. Answer C

Answer:

Related questions