GATE CS 2013 Q35 — Tuple Calculus and SQL

To find the correct option, let us analyze each of the given query representations to see if they correctly express the English query: “Find the distinct names of all students who score more than $90\%$ in the course numbered 107”.

I. SQL Query

SELECT DISTINCT S.sname FROM Students as S, Registration as R 
WHERE R.rollno=S.rollno AND R.courseno=107 AND R.percent > 90

• Join Condition: R.rollno = S.rollno correctly joins the Students and Registration tables.
• Filtering: R.courseno = 107 AND R.percent > 90 filters for the specified course and score.
• Projection: SELECT DISTINCT S.sname ensures only distinct student names are returned.

Thus, Query I is correct.

II. Relational Algebra (RA) Query

$$\Pi_{sname}(\sigma_{courseno=107 \land percent > 90} (Registration \bowtie Students))$$

• Natural Join: Registration ⋈ Students joins the two relations on their common attribute, which is rollno.
• Selection: $\sigma_{courseno=107 \land percent > 90}$ filters the joined tuples.
• Projection: $\Pi_{sname}$ projects the student names. In relational algebra, the projection operator $\Pi$ inherently eliminates duplicate values.

Thus, Query II is correct.

III. Tuple Relational Calculus (TRC) Query

$$\{T \mid \exists S \in Students, \exists R \in Registration ( S.rollno=R.rollno \land R.courseno=107 \land R.percent>90 \land T.sname=S.sname) \}$$

• This query defines a target tuple $T$ containing a single attribute sname (since $T.sname = S.sname$).
• It asserts the existence of a student tuple $S$ and a registration tuple $R$ such that their rollno values match, the course number is $107$, and the score is greater than $90\%$.

Thus, Query III is correct.

IV. Domain Relational Calculus (DRC) Query

$$\left\{ \langle S_N\rangle \mid \exists S_R \exists R_P (\langle S_R,S_N\rangle \in Students \land \langle S_R,107,R_P\rangle \in Registration \land R_P>90) \right\}$$

• The domain variables are mapped as follows:
  • For Students(rollno, sname): $\langle S_R, S_N\rangle$ where $S_R$ is rollno and $S_N$ is sname.
  • For Registration(rollno, courseno, percent): $\langle S_R, 107, R_P\rangle$ where $S_R$ is rollno, the course number is bound to constant $107$, and $R_P$ is percent.
• The condition $R_P > 90$ filters for scores greater than $90\%$.
• The output is the domain variable $\langle S_N\rangle$ representing the student's name.

Thus, Query IV is correct.

Conclusion

Since all four queries (I, II, III, and IV) are equivalent and correct, the correct option is A.