display the order in relational algebra

Question

Suppose we have this relational schema

homebuilder(hID, hName, hStreet, hCity, hZip, hPhone)

model(hID, mID, mName, sqft, story)

subdivision(sName, sCity, sZip)

offered(sName, hID, mID, price)

lot(sName, lotNum, lStAddr, lSize, lPremium)

sold(sName, lotNum, hID, mID, status)

I have problem by doing relational algebra for each subdivision , find the number of models offered and the average, minimum and maximum price of the models offered at that subdivision. Also display the result in descending order on the average price of a home.

I am done with SQL formula, but it hard for me to translate this SQL to relational algebra. Can someone help me? Here is what I got so far:

SQL:= 
    SELECT S, avg (O.price), min (O.price), max (O.price), count(*)
    FROM   offered O, subdivision S
    WHERE O.sName = S.sName
    GROUP BY S.sName
    ORDER BY 4 desc;

Answer 1

+1 to DPenner's comment: quite true that you can't do ordering in RA. (Although those q's and a's referenced seem to have some 'difficulties'.)

Another thing you can't do in RA (contra the SQL that JaveLeave shows) is to have anonymous columns referenced by position. If SQL were a sensible language (or indeed any sort of language at all), you could name the column in the SELECT clause ..., max (O.price) AS maxPrice, ... then ORDER BY maxPrice desc. But no, you can't do that. In SQL you have to repeat ORDER BY max (O.price) desc. (By the way, the question asked for ordering by average price, not max(?) That's column 2.)

Contrast that the RA Group operation returns a relation. And being a relation it must have attributes only addressable by name.

Back to the question as asked. The nearest you can get to an ordering is to put a column on each row with the ordinal position of this row relative to the overall table. Since the question asks for descending sequence, the first step is to find the subdivision with minimum average price and tag it with ordinal 1. Then select all but that one, get the minmium of those, tag it with 2. And in general: take all not tagged so far; get the minmum; tag it with highest tag so far +1; recurse. So you need the transitive closure operation (which is another 'missing' feature of standard RA). You can find some SQL code to achieve this sort of thing in comp.database.theory -- from memory Joe Celko gives examples.

Off-topic: I'm puzzled why courses/professors/textbooks in SQL also ask you to do impossible things in RA. Certainly it's good to have a grounding in RA. It's a powerful mental model to understand data structures that SQL only obscures. RA (as an algebra) underpins most SQL engines. But then why leave the impression that RA is some sort of 'poor cousin' to SQL? There are no commercial implementations of RA; there are no job advertisements for RA programmers. Why try to make it what it isn't?