Introduction

Referential integrity is a fundamental concept in relational databases. It ensures that values in a foreign key column of one table correspond to valid values in the primary key column of another table.

Relational Schema Example

Consider the following schemas:

R1(a, b): Table with attributes a and b.
R2(c, d): Table with attributes c and d.
            

Here, attribute b in R1 is a foreign key referencing attribute c in R2. This means every value in b must exist in c.

Example Data:

R1:
a   b
A1  1
A2  2
A3  3

R2:
c   d
1   D1
2   D2
3   D3
4   D4
            

Key Concepts

• Foreign Key: Ensures that values in one table correspond to values in another table.
• Referential Integrity: Guarantees consistency between related tables.
• Superset Relationship: Values in the referenced table (R2) can exceed those in the referencing table (R1).

GATE Question Analysis

Option A: π_B(R1) − π_C(R2) = ∅

This checks if there are any values in b (from R1) that do not exist in c (from R2). Since b is a foreign key, this set difference is always empty, making this statement true.

Option B: π_C(R2) − π_B(R1) = ∅

This checks if all values in c (from R2) exist in b (from R1). This is false because there can be values in c without corresponding values in b.

Option C: π_B(R1) = π_C(R2)

This checks if the sets of b and c are identical. This is false because c can have additional values not present in b.

Option D: π_B(R1) − π_C(R2) ≠ ∅

This checks if there are values in b that do not exist in c. This is false because the foreign key constraint ensures that b values must exist in c.

Conclusion

• Correct: Option A – Referential integrity ensures no values in b are missing in c.
• Incorrect: Options B, C, and D – These violate the properties of referential integrity.