Consider the relation Student (name, sex, marks), where the primary key is…
2004
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: r is the rename operator).
- 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 students4)
- D.
names of girl students with more marks than all the boy students
Attempted by 59 students.
Show answer & explanation
Correct answer: D
Goal: explain the given relational algebra expression and its result.
Left part:
P_name(r_{sex = female}(Student)) selects the set of names of female students.
Right part:
Consider r_{n,x,m}(Student) as Student renamed so attributes are n (name), x (sex), m (marks).
The join Student ⨝_{(sex = female ∧ x = male ∧ marks ≤ m)} r_{n,x,m}(Student) pairs a left tuple (a student) with a right tuple (another student) when the left student is female, the right student is male, and the female's marks ≤ the male's marks.
Projecting the name: P_name(...) from this join yields the names of female students who have marks less than or equal to some male student's marks (i.e., females that are beaten or tied by at least one male).
Final operation (set difference):
Subtracting the projected names from the right part from the set of all female names removes every female who is ≤ some male. The remaining female names are those who are not ≤ any male.
Conclusion: The expression returns the names of female students whose marks are greater than the marks of every male student (i.e., girls who have more marks than all the boys).
Note: The problem statement says the class has at least one boy and one girl, so the comparisons and set difference are meaningful.