Project ForgeConsider the following relational schema:
Students(rollno: integer, sname: string) Courses(courseno: integer, cname: string) Registration(rollno: integer, courseno: integer, percent: real)
Which of the following queries are equivalent to this query in English: "Find the distinct names of all students who score more than 90% in the course numbered 107"?
(I) SELECT DISTINCT S.sname FROM Students AS S, Registration AS R WHERE R.rollno=S.rollno AND R.courseno=107 AND R.percent>90
(II) Project sname of (Select courseno=107 AND percent>90 of (Registration NATURAL JOIN Students))
(III) Tuple relational calculus: {T | ∃S∈Students, ∃R∈Registration (S.rollno=R.rollno ∧ R.courseno=107 ∧ R.percent>90 ∧ T.sname=S.sname)}
(IV) Domain relational calculus: {<S_N> | ∃S_R ∃P_R (<S_R, S_N> ∈ Students ∧ <S_R, 107, P_R> ∈ Registration ∧ P_R>90)}
- I, II, III and IV
- BI, II and III only
- CI, II and IV only
- DII, III and IV only
Explanation
All four formulations express exactly the same condition (join on rollno, filter courseno=107 and percent>90, project just the name) using a different query language each: (I) is the direct SQL translation with DISTINCT for deduplication; (II) is relational algebra, where projection (π) automatically removes duplicates under set semantics; (III) is tuple relational calculus, quantifying over Students and Registration tuples with the same join/filter condition; (IV) is domain relational calculus, quantifying over the individual attribute values directly. All are correct and equivalent — I, II, III and IV.
Source: GATE CS 2013 (2013)
This question is part of a 15-day revision cycle — sign in and Project Forge will bring it back the day before you'd forget it.