Consider the following relational schema. Students(rollno: integer, sname:…
2013
Consider 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) ∏sname(σcourseno=107 ∧ percent>90 (Registration ⋈ Students))
(III) {T | ∃S∈Students, ∃R∈Registration ( S.rollno=R.rollno ∧
R.courseno=107 ∧ R.percent>90 ∧T.sname=S.sname)}
(IV) {<SN> | ∃SR∃RP ( <SR, SN>∈Students ∧ <SR, 107, RP>∈Registration ∧ RP>90)}
- A.
I, II, III and IV
- B.
I, II and III only
- C.
I, II and IV only
- D.
II, III and IV only
Attempted by 68 students.
Show answer & explanation
Correct answer: A
Key idea: All four expressions return the set of student names for students who have a Registration record with courseno = 107 and percent > 90. Duplicate names are eliminated by DISTINCT or by the set semantics of projection/relational calculus.
SQL (I): SELECT DISTINCT sname ... joins Students and Registration on rollno and filters courseno = 107 and percent > 90; DISTINCT removes duplicate names.
Relational algebra (II): project sname after selecting and joining the same relations with the same conditions; projection over a relation is set-valued, so duplicates are not retained.
Tuple relational calculus (III): asserts existence of a Students tuple and a Registration tuple with equal rollno and with courseno = 107 and percent > 90; the comprehension returns the set of matching sname values.
Domain relational calculus (IV): uses domain variables to state the same existence/join and filter conditions and therefore returns the same set of student names.
Therefore all four forms are equivalent to the English query; the correct choice is the one that includes every listed formulation.
A video solution is available for this question — log in and enroll to watch it.