GATE CSE 2012 | Question: 43

Question

Suppose $R_{1} (\underline{A}, B)$ and $R_{2} (\underline{C}, D) $ are two relation schemas. Let $r_{1}$ and $r_{2}$ be the corresponding relation instances. $B$ is a foreign key that refers to $C$ in $R_{2}$. If data in $r_{1}$ and $r_{2}$ satisfy referential integrity constraints, which of the following is ALWAYS TRUE?

• A. $\prod_{B}(r_{1})- \prod _{C}(r_{2})= \varnothing$
• B. $\prod_{C}(r_{2})- \prod _{B}(r_{1})= \varnothing$
• C. $\prod_{B}(r_{1}) = \prod _{C}(r_{2})$
• D. $\prod_{B}(r_{1}) - \prod _{C}(r_{2}) \neq \varnothing$

Comments

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

---

B,C can be true but question asking for  always true so ans is A only.

But D is always false.

---

C can have extra elements also , B is FK so it should have less than or equal to elements of C
subset - superset = NULL [ex : {1,2} − {1,2,3,4} = ∅ ] (we need to check elements of subset if its something left or not )

Answer 1

Answer is A.

Referential integrity means, all the values in foreign key should be present in primary key.

$r2(c)$ is the super set of $r1(b)$

So, {subset - superset} is always empty set.

Comments

@Deepak Poonia Sir, what if we have null values in the referencing relation ?
In that case, what will be the behaviour if we are projecting NULL values ?

---

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 

---

Also, the options do not have null on rhs . So even if we assumed extended RA then any operation on null would result in null; and null != phi . So option A still the best

@Deepak Poonia

Answer 2

Child - Parents = Game Over  

Answer is A. 

Comments

but what if child contains NULL? will it be included...

---

@Abhineet Singh

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 

Answer 3

Though foreign key values in B(r1) may be more than primary key values in C(r2), but here we use Relational Algebra query and operator is projection $\prod$, which doesn't retain duplicates. So, $\prod B(r1)-\prod C(r2) = \phi$

Answer 4

Correct me if I am wrong:

A: B might not use some values of C. But B cannot have anything that C doesn't contain. So the difference will be phi. This is the Ans.

B: Same as option A, C might have values that B doesn't use so the diffenerce cannot be phi here. Moreover the Q askes which one is always true, this one might not be true in some cases.

C: Same argument, B and C might not be equal always.

D: Difference is phi, proven in option A.

Comments

A,B.      C,D

1,X.       X,abc

2,null.    Y,xyz

{X,null}-{X,Y}={null}

Then how option one is correct

Phi is not equal to{null}

Where I am going wrong??

 

 

---

@Akhilesh_cs_gate,

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