GATE CSE 2004 | Question: 13

Question

Let $R_1 \left(\underline{A}, B, C\right)$ and $R_2\left(\underline{D}, E \right) $ be two relation schema, where the primary keys are shown underlined, and let C be a foreign key in $R_1$ referring to $R_2$. Suppose there is no violation of the above referential integrity constraint in the corresponding relation instances $r_1$ and $r_2$. Which of the following relational algebra expressions would necessarily produce an empty relation?

• A. $\Pi_D (r_2) - \Pi_C (r_1)$

• B. $\Pi_C (r_1) - \Pi_D (r_2)$

• C. $\Pi_D \left(r_1 \bowtie_{C \neq  D}r_2\right)$

• D. $\Pi_C \left(r_1 \bowtie_{C = D}r_2\right)$

Comments

Why B? If C has something which isn't there in D then referential integrity is violated which isn't possible as its explicitly mentioned in question.

---

Detailed Video Solution: https://www.youtube.com/watch?v=h3pJZbed9M8&t=2513s 

---

So, All the non-null value of C must be present in D.

 Because they have told  
"There is no violation of the above referential integrity constraint in the
  corresponding relation instances r1  and  r2 ".

hence Optin B is correct.

Answer 1

Answer is (B).

$C$ in $R1$ is a foreign key referring to the primary key $D$ in $R2$. So, every element of $C$ must come from some $D$ element.

Comments

NOTE:

Relational Algebra doesn't have the concept of NULL values. So, for any Relational Algebra question, we can assume that the relation instances do Not have null values. See HERE.

SQL has Null values concept, Relational Algebra doesn't. See HERE.

Detailed Video Solution: https://www.youtube.com/watch?v=h3pJZbed9M8&t=2513s 

---

Why is option C wrong?

---

@P0535_Yedidyah_Sagar Option C is wrong because,
r1 join(where c!=d) r2 will be selecting those tuples of r1*r2 where c is null (as it is fk it can be null) and d is not null , and from these tuples we project D. notice that D may contain values that are not in C. and therefor when we perform the set differnce operation the answer might be not null.