Consider the relation Student(name, sex, marks), where the primary key is shown underlined, pertaining to students in a class that has at least one boy and one girl. What does the following relational algebra expression produce? (Note: rho is the rename operator)
rho_{temp(name', sex', marks')}(Student) ⋈_{sex='female' AND sex'='male' AND marks>=marks'} rho_{temp}(Student)
More precisely: pi_{name}(sigma_{sex='female'}(Student)) - pi_{name}(sigma_{sex='female' AND sex'='male' AND marks<marks'}(Student JOIN rho_{temp}(Student)))
A. Names of girl students with the highest marks
B. Names of girl students with more marks than some boy student
C. Names of girl students with marks not less than some boy student
D. Names of girl students with more marks than all the boy students
GATE 2004 · Databases · Relational Algebra · medium
Answer: Names of girl students with marks not less than some boy student (Option C).
Understand the join condition: The join pairs each female student with each male student where the female's marks >= the male's marks. So a female student appears in the result if there EXISTS at least one male student with marks <= female's marks.
Determine what the projection returns: After the join, we project on name (the female student's name). A female student's name appears iff she has marks >= marks of at least one male student. This is equivalent to: her marks are not less than some boy's marks.