retagged
16,099 views
45 votes
45 votes

Let r be a relation instance with schema R = (A, B, C, D). We define $r_1 = \pi_{A, B, C} (R)$ and $r_2=\pi_{A, D} (r)$. Let $s =r_1 \: * \: r_2$ where $*$ denotes natural join. Given that the decomposition of $r$ into $r_1$ and $r_2$ is lossy, which one of the following is TRUE?

  1. $s \subset r$

  2. $r \cup s =r$

  3. $r \subset s$

  4. $r*s=s$

retagged

5 Answers

Best answer
72 votes
72 votes

Answer is C $r \subset s.$ 

$$\overset{\text{r}}{\begin{array}{|l|l|l|l|}\hline \textbf{A}  &  \textbf{B} & \textbf{C} & \textbf{D} \\\hline \text{1} & \text{2} & \text{3} & \text{3} \\ \text{1} & \text{5} & \text{3} & \text{4}\\\hline \end{array}} \qquad \overset{\text{r1}}{\begin{array}{|l|l|l|l|}\hline \textbf{A}  &  \textbf{B} & \textbf{C}  \\\hline \text{1} & \text{2} & \text{3}  \\ \text{1} & \text{5} & \text{3} \\\hline \end{array}}\qquad\overset{\text{r2}}{\begin{array}{|l|l|l|l|}\hline \textbf{A}  &  \textbf{D}  \\\hline \text{1} & \text{3} \\ \text{1}  & \text{4}\\\hline \end{array}}\qquad\overset{\text{s = r1 * r2}}{\begin{array}{|l|l|l|l|}\hline \textbf{A}  &  \textbf{B} & \textbf{C} & \textbf{D} \\\hline \textbf{1} & \textbf{2} & \textbf{3} & \textbf{3} \\ \text{1} & \text{2} & \text{3} & \text{4}\\\text{1} & \text{5} & \text{3} & \text{4}\\ \textbf{1} & \textbf{5} & \textbf{3} & \textbf{4}\\\hline \end{array}}$$

All the rows of $r$ are in $s$ (marked bold). So, $r \subset s.$  

And one more result $r * s = r.$

edited by
27 votes
27 votes
in short what happens in lossy when you join the tables r1 ,r2 to form r you get the tuples already in r and also some extra tuples(spurious tuples) which makes the table ambiguous like a person having previously one address now its showing more than one address .so to conclude r is a subset of s..

check this link for clarification:

http://stackoverflow.com/questions/12671362/lossy-decomposition
14 votes
14 votes

Here

decomposition of r to r1 and r2 is lossy.

Lossy decomposition :R1 and R2 has a common attribute but the common attribute A is not key in either of R1 or R2.

Due to this when R1 and R2 are joined,we get spurious tuples which are not in Original R.

s=r1*r2

C.rs

0 votes
0 votes

Given that decomposition of r into r1 and r2 is lossy ===> common attribute(A) is neither key in r1 nor in r2

∴ A should be have duplicates, but on natural join we check the condition on A only ===> result of natural join ⊃ r

 

take the sample set:-

r:-

A     B   C    D

1     a    i     x

1     b    ii   y

2     c   iii   y

 

r1 :-

A  B  C

1     a    i  

1     b    ii  

2     c   iii  

 

r2:-

A  D

1   x

1   y

2   y

 

r1 * r2

A   B   C   D

1    a    i  x

1    a   i   y  --------> Spurious tuple

1    b   ii   x --------> Spurious tuple

1   b   ii   y

2   c   iii  y

 

∴ option C is right.

 

s ⊃ r ===> s ∪ r = s

s * r = s ∩ r , given that s * r = s ====> s ∩ r = s , but we know that s ⊃ r ===> s ∩ r = r

Answer:

Related questions

1 votes
1 votes
1 answer
1
raginibhayana asked Feb 15, 2022
755 views
why don't we use the projection operator in conditional join?
1 votes
1 votes
1 answer
2