Log In
26 votes

Consider two relations $R_1(A,B)$ with the tuples $(1,5), (3,7)$ and $R_2(A,C) = (1,7),(4,9)$. Assume that $R(A,B,C)$ is the full natural outer join of $R_1$ and $R_2$. Consider the following tuples of the form (A,B,C):

$a = (1,5,null), b=(1,null,7), c=(3,null,9), d=(4,7,null), e=(1,5,7), \\ f=(3,7,null), g=(4,null,9).$

Which one of the following statements is correct?

  1. $R$ contains $a, b, e, f, g$ but not $c, d$.
  2. $R$ contains all $a, b, c, d, e, f, g$.
  3. $R$ contains $e, f, g$ but not $a, b$.
  4. $R$ contains $e$ but not $f, g$.
in Databases
edited by

I think this image is self explanatory

All the best for GATE

2 Answers

36 votes
Best answer
$R_1(A,B): \begin{array}{|c|c|} \hline \textbf{A} & \textbf{B} \\\hline \text {1} &  \text{5 }\\\hline \text{3} & \text{7} \\\hline \end{array}$

$R_2(A,C): \begin{array}{|c|c|} \hline \textbf{A} & \textbf{C} \\\hline \text {1} &  \text{7}\\\hline \text{4} & \text{9} \\\hline \end{array}$

Now , if we do full natural outer join:
$$\begin{array}{|c|c|} \hline \textbf{A} & \textbf{B} & \textbf{C}\\\hline \text {1} &  \text{5 } & \text{7}\\\hline \text{3} & \text{7} & \text{NULL} \\\hline  \text{4} & \text{NULL} & \text{9} \\\hline \end{array}$$
So, option (C) is correct.

edited by
Thank you @Arjun sir .. :)
why we cant write as 3  7   9. why 3   7   null??
only 1 match between two tables so that row will have all the values in 3 7 case

A - 3, B-7 no C so bcz it is FOJ so put that row in the table with null

@kshitij arunabh because in R2 9 of C is with 4 of A, thats why in R(A,B,C) 9 is with (4, null, 9) 

I still doesn't understood this problem. If we are joining both tables like natural join then there must be only one tuple 1 5 7. But if we are doing full outer join then there would be 5 tuple possible 1 5 7, 1 5 null, 3 7 null, 1 null 7, 4 null 9.

But what is natural full outer join ?
5 votes
Ans C. Full outer join means take all matching rows from left table and right table based on common columns.
No full outer join doesnt mean that.It means take all tuples irrespective of any condition and print all the tuples putting null where the attributes are not applicable.Its cardinality turns out to be n(A) + n(B) option a is correct.
How C  is the answer?

How 3rd tuple (4,null,9) is coming? if that happens then i think from left outer join (1,5,null)tuple also should come.
ok clear.
Ya same question .. No one has explained that.

Related questions

42 votes
4 answers
Consider a simple checkpointing protocol and the following set of operations in the log. (start, T4); (write, T4, y, 2, 3); (start, T1); (commit, T4); (write, T1, z, 5, 7); (checkpoint); (start, T2); (write, T2, x, 1, 9); (commit, T2); (start, T3); (write, T3, z, 7, 2); ... the redo list? Undo: T3, T1; Redo: T2 Undo: T3, T1; Redo: T2, T4 Undo: none; Redo: T2, T4, T3, T1 Undo: T3, T1, T4; Redo: T2
asked Feb 13, 2015 in Databases jothee 12.2k views
32 votes
3 answers
With reference to the B+ tree index of order $1$ shown below, the minimum number of nodes (including the Root node) that must be fetched in order to satisfy the following query. "Get all records with a search key greater than or equal to $7$ and less than $15$ " is ______.
asked Feb 12, 2015 in Databases jothee 6.4k views
56 votes
8 answers
A Young tableau is a $2D$ array of integers increasing from left to right and from top to bottom. Any unfilled entries are marked with $\infty$, and hence there cannot be any entry to the right of, or below a $\infty$. The following Young tableau consists of unique ... $1$) to be shifted, to remove $1$ from the given Young tableau is _____.
asked Feb 12, 2015 in DS jothee 6.2k views
29 votes
4 answers
Consider the following transaction involving two bank accounts $x$ and $y$. read(x); x:=x-50; write (x); read(y); y:=y+50; write(y) The constraint that the sum of the accounts $x$ and $y$ should remain constant is that of Atomicity Consistency Isolation Durability
asked Feb 12, 2015 in Databases jothee 5.4k views