A Relational Algebra Question: Combination of RENAME and WHERE
The question is about an expression in the Date's book (8th edition, page 199):
ADD COUNT ((SP RENAME S# AS X) WHERE X = S#)
and specially the part: "COUNT ((SP RENAME S# AS X) WHERE X = S#)"
How this calculates the number of shipments for each supplier separately?
I think the condition X = S# is true for all rows, and the COUNT function should count all rows, unless it resets to 0 each time!
The point of EXTEND is that the COUNT part behaves like a correlated subquery - it gets computed for each tuple in S. The supplier number is renamed as X in SP, so the WHERE expression means X (in SP) = S# (in S).
Did you try it out yourself? Rel (Rel) includes the scripts to create the supplier and parts relvars and the sample data.