GATE CS 2006 Q68 — Tuple Calculus and SQL

To determine which statement is correct, let us analyze the schema and the behavior of each query with respect to duplicate rows.

1. Schema Analysis

• enrolled(student, course): The primary key is (student, course). This means a single student can be enrolled in multiple courses, so a student can appear multiple times in this relation.
• paid(student, amount): The primary key is student. This means each student can appear at most once in this relation.

2. Query Analysis

• Query 1:

  select student from enrolled where student in (select student from paid)

  This selects from enrolled. If a student is enrolled in $k$ courses and has paid, this query will return that student's ID $k$ times.
• Query 2:

  select student from paid where student in (select student from enrolled)

  This selects from paid. Since student is the primary key of paid, each student who has paid and is enrolled in at least one course will be returned exactly once.
• Query 3:

  select E.student from enrolled E, paid P where E.student = P.student

  This performs an inner join. For a student enrolled in $k$ courses who has paid, the join produces $k imes 1 = k$ rows. Thus, this query returns that student's ID $k$ times, matching the output of Query 1.
• Query 4:

  select student from paid where exists (select * from enrolled where enrolled.student = paid.student)

  This selects from paid. It checks for the existence of a matching record in enrolled. Since it selects from paid, each matching student is returned exactly once, matching the output of Query 2.

3. Evaluating the Options

• Option A is incorrect: If a student is enrolled in multiple courses, Query 1 and Query 2 will return different numbers of duplicate rows.
• Option B is correct: Query 2 and Query 4 both select from paid and return each matching student exactly once, making them identical for all databases. However, if a student is enrolled in multiple courses, Query 1 (which returns duplicates) and Query 2 (which does not) will produce different row sets.
• Option C is incorrect: Since $k e 0$ for matching students, Query 3 (which returns $k$ rows per matching student) will always return at least as many rows as Query 2 (which returns $1$ row per matching student). It can never return strictly fewer rows.
• Option D is incorrect: Standard SELECT queries do not modify data and cannot cause integrity violations.