Test by Bikram | Mock GATE | Test 3 | Question: 30

Question

Consider three tables with the following number of tuples in each:

$X$$\left ( a,b,c \right )$ $= 100$   ||   $Y$$\left ( a,d,e \right )$ $= 80$   ||   $Z$$\left ( x,d,f \right )$ $= 90$

Tuples in $X$ and $Y$ with same value of attribute ’$a$’ $= 60$.

Tuples in $Y$ and $Z$ with same value of attribute ’$d$’ $= 70$.

The maximum number of tuples in  ( $X$ left outer join $Y$ )  full outer join $Z$  is ________.

Comments

First we'll do $X\ \color{red}{LOJ}\ Y$. In the result, we'll get 100 rows. (60 matched rows and 40 additional rows of X)

Let's call this "Intermediary_result"

 

(Assumption 1): If each value of d is distinct, then in this Intermediary_result, we lost 20 d values. So, intermediary_result and Z now have 50 rows in common.

 

Now do: $Intermediary\_result\ \color{red}{FOJ}\ Z$. We'll get 140 rows (50 matched rows, 50 additional rows from Intermediary_result, and 40 additional rows from Z)

=> 140 rows.

---

Assumption 2:

Each value of d isn't necessarily distinct. We're sure that at least 70 d values are distinct, however. Let's assume we have those minimum 70 distinct values, and 10 duplicates.

In the Intermediary_result, we'll lose 20 values. For the best case, assume 10 of these 20 values are duplicates.

=> We lost only 10 d values.

 

So, $X\ \color{red}{LOJ}\ Y$. In the result, we'll get 100 rows.

Then

$Intermediary\_result\ \color{red}{FOJ}\ Z$. We'll get 130 rows ( 60 matched rows, 40 additional rows from Intermediary_result, and 30 additional rows from Z)

---

Out of 130 and 140; 140 is the maximum.

Answer 1

A = ( X left outer join Y ): This will have 100 rows, with d = null for 40(= 100 - 60) rows.

A full outer join Z: Here the common values of d in tables A and Z can be between 50 and 60.

1. Minimum value of common values of d between A and Z :

Out of 80 values of d in Y , 20 were not found in A (since A has only 60 non-null values of d).  These 20 can all be the ones that were common between A and T In this case the common
values between  A and Z will be 50.

2. Maximum value of common values of d between A and Z :

Now assume that 20 values of d that were lost, none were common between X and Y. But this is not possible, because Y has 80 tuples and 70 values are common with Z . So at least 10 common values will get lost. Thus max. value of common d’s will b 60.

Now full outer join ( A + Z - common values of d in A and Z ) will be  100 + 90 - 50 . Thus maximum is 140 .

Comments

130 is minimum value but question asked about maximum value thats why it is 140 .

---

Sir I have 2 queries here :

1.) If no condition is specified on an outer join so does it fall back to some sort of natural join where join is performed on same attribute ?

2.) Ans = 140 but it the test 130 is being accepted.

---

now i corrected, it is 140  correct answer, ur marks will increase..

---

@Bikram Sir, shouldn’t there be any constraint mentioned in the question like:

 no tuples are repeated in any relation / X(a), Y(a,d), Z(d) are primary key in relations ?

Because if values are repeated then the join condition would give more tuples !

Please correct me if I am going wrong !

Answer 2

What if all the common attributes(where ever they are common) are the same value. I mean here X.a = Y.a, say all the common values have the same value say = "1" then max total tuples will be 60*60 + 40(nulls) = 3640. and applying the same logic to the whole question we get max as 252070 tuples. 

Please look at the tables and corresponding queries. it is a smaller database but obeys the approach and conditions that are mentioned in the question. 

size of X = 10 , Y = 8, Z = 9 ;

common(X, Y) = 6, common(Y,Z) = 7

Result Set X

select * from X ;
 

A	B	C
1	10	11
1	10	11
1	10	11
1	10	11
1	10	11
1	10	11
2	10	11
3	10	11
4	10	11
5	10	11

Download CSV
10 rows selected.

Result Set Y

select * from Y;

A	D	E
1	10	11
1	10	11
1	10	11
1	10	11
1	10	11
1	10	11
44	10	11
44	11	11

Download CSV
8 rows selected.

Result Set Z

select * from Z ;
 

P	D	F
1	10	11
2	10	11
3	10	11
4	10	11
5	10	11
6	10	11
7	10	11
3	11	11
32	234	32

Download CSV
9 rows selected.

Result Set 

select count(*) from X left join Y on X.a = Y.a full outer join Z on Y.d = Z.d ;

COUNT(*)
258

Download CSV

Comments

I think this is wrong answer.

I too have doubt same as @susgir2

---

Tuples with same value in X and Y -- the meaning of this is ambiguous.

---

Thank you Sir for Reply