92 views
0 0 votes

Consider $\mathrm{A}(\mathrm{c1},\mathrm{c2},\mathrm{c3})$ and $\mathrm{B}(\mathrm{c1},\mathrm{c2},\mathrm{c3})$, where $\mathrm{c1}$ is the primary key of each relation.

Assume the join attributes $\mathrm{c2}$ are non-NULL.

Consider the statement $:$

"If every value appearing in $\mathrm{B.c2}$ also appears in $\mathrm{A.c2}$, then 

$\mathrm{A}\ \mathbin{⟗}_{\mathrm{A.c2}=\mathrm{B.c2}}\ \mathrm{B}$ has the same number of tuples as $\mathrm{A}\ \bowtie_{\mathrm{A.c2}=\mathrm{B.c2}}\ \mathrm{B}$."

Which assessment is correct?

  1. True, because every tuple of $\mathrm{B}$ has a match.
     
  2. False, because $\mathrm{A}$ may still contain a $\mathrm{c2}$ value absent from $\mathrm{B}$, creating dangling tuples from $\mathrm{A}$.
     
  3. False, because a full outer join always contains more tuples than an inner join.
     
  4. True, because $\mathrm{c1}$ is a primary key.

1 Answer

0 0 votes

The condition tells us that every value appearing in $\mathrm{B.c2}$ also occurs in $\mathrm{A.c2}$.

Therefore, every tuple of $\mathrm{B}$ can find at least one matching value on the $\mathrm{A}$ side. 

So there are no dangling tuples from $\mathrm{B}$.

However, this tells us nothing about the reverse direction.

For eg, suppose:

$\mathrm{A.c2}=\{10,20\}$ and $\mathrm{B.c2}=\{10\}$.

Every value of $\mathrm{B.c2}$ appears in $\mathrm{A.c2}$, so the stated condition is satisfied.

But the tuple in $\mathrm{A}$ having $\mathrm{c2}=20$ has no matching tuple in $\mathrm{B}$.

An inner join discards it.

A full outer join preserves it and fills the $\mathrm{B}$ attributes with $\mathrm{NULL}$.

Therefore, the full outer join contains an additional tuple.

For the full outer join to add no dangling tuples from either side, we need matching values in both directions:

$\mathrm{values}(\mathrm{A.c2})\subseteq\mathrm{values}(\mathrm{B.c2})$

and

$\mathrm{values}(\mathrm{B.c2})\subseteq\mathrm{values}(\mathrm{A.c2})$.

Equivalently,

$\mathrm{values}(\mathrm{A.c2})=\mathrm{values}(\mathrm{B.c2})$.

Thus, the original one-direction containment condition is insufficient.

Hence, Answer : B

Answer:
Position:
Show:

Related questions

0 0 votes
1 1 answer
90
90 views
GO Classes asked Sep 25
90 views
Consider :$$\begin{aligned}\begin{gathered}\mathrm{Customer} \\[-2pt]\begin{array}{|c|c|}\hline\mathrm{cid} & \mathrm{name} \\\hline1 & \mathrm{Joe} \\2 & \mathrm{Betty} ...
1 1 vote
1 1 answer
84
84 views
GO Classes asked Sep 25
84 views
Consider $\mathrm{Homes}(\mathrm{home\_id},\mathrm{city},\ldots)$ and $\mathrm{Transactions}(\mathrm{home\_id},\mathrm{sale\_price},\ldots)$.We want every home in Berkele...
2 2 votes
3 3 answers
129
129 views
GO Classes asked Sep 25
129 views
Consider $ \mathrm{MEMBER}(\mathrm{personName}, \mathrm{groupName}) $ and $ \mathrm{PICTURE}(\mathrm{groupName}, \mathrm{picture}) $.Every person belongs to at least one ...
1 1 vote
1 1 answer
82
82 views
GO Classes asked Sep 25
82 views
Let $\mathrm{R}(\mathrm{A},\mathrm{B})$ and $\mathrm{S}(\mathrm{A},\mathrm{B})$ be relations under set semantics.Which expression is not always equivalent to the others?$...