GATE CS 2015 Set 2 Q.42 (CS Q.32): Relational Algebra and Tuple Relational Calculus

Consider two relations $R_1(A, B)$ with the tuples $(1, 5), (3, 7)$ and $R_2(A, C)$ with $(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, \text{null}),\quad b = (1, \text{null}, 7),\quad c = (3, \text{null}, 9),

d = (4, 7, \text{null}),\quad e = (1, 5, 7),\quad f = (3, 7, \text{null}),\quad g = (4, \text{null}, 9).

Which one of the following statements is correct?

$R$ contains $a, b, e, f, g$ but not $c, d$ .

$R$ contains all of $a, b, c, d, e, f, g$ .

$R$ contains $e, f, g$ but not $a, b$ .

$R$ contains $e$ but not $f, g$ .

Official answer and solution

Official answer: C
From: Official GATE 2015 answer key

Solution

Construct the full natural outer join of $R_1(A, B)$ and $R_2(A, C)$ on common attribute $A$ .

Relations given:

$R_1(A, B) = \{(1, 5), (3, 7)\}$
$R_2(A, C) = \{(1, 7), (4, 9)\}$

The full natural outer join $R(A, B, C) = R_1 \;\text{FULL OUTER JOIN}\; R_2$ includes:

Natural inner join tuples on matching $A$ $A$ values:
- $A = 1$ : tuple $(1, 5, 7)$ (this is tuple $e$ ).
Tuples from $R_1$ $R_{1}$ with no matching $A$ $A$ in $R_2$ $R_{2}$ , padded with null for attribute $C$ $C$ :
- $A = 3$ : tuple $(3, 7, \text{null})$ (this is tuple $f$ ).
Tuples from $R_2$ $R_{2}$ with no matching $A$ $A$ in $R_1$ $R_{1}$ , padded with null for attribute $B$ $B$ :
- $A = 4$ : tuple $(4, \text{null}, 9)$ (this is tuple $g$ ).

The full outer join relation $R$ consists of exactly $\{e, f, g\}$ . It does not contain tuples $a = (1, 5, \text{null})$ or $b = (1, \text{null}, 7)$ because $A = 1$ matched and formed the combined tuple $e$ .

Hence, 'R contains $e, f, g$ but not $a, b$ ' (option C).

Final answer

Common trap

Assuming full outer join retains the null-padded versions of successfully matched tuples $a$ and $b$ .

The answer is the one in the official GATE answer key. The solution is this site's own explanation.

Source

Paper: GATE 2015 Computer Science and Information Technology (CS) Session 2
Question: 42 of 65, printed as CS Q.32
Section: Computer Science and Information Technology

Report an error in this question

GATE questions are the work of the organising institutes. This site is independent and is not affiliated with them.

Question

Solution

Source