views:

328

answers:

2

Yes, this is a homework question but the names have been changed to protect the innocent. Meaning, I am not asking the homework question itself, but rather a small part of it so I can understand the whole.

Let's say you have a SQL query like this:

--The query would list car prices that occur more than once.

select car_price from cars
group by car_price
having count (car_price) > 1;

The general form of this in relational algebra is Y (gl, al) R Where Y is the greek symbol, GL is list of attributes to group, AL is list of aggregations

So the relational algebra would look like:

 Y (count(car_price)) cars

So, how is the "having" clause written in that statement? Is there a shorthand? If not, do I just need to select from that relation? Something like this maybe?

SELECT (count(car_price) > 1) [Y (count(car_price)) cars]

I have searched the internet on this for hours and have found no examples of converting HAVING to relational algebra. Thanks for the help!

+2  A: 

select count(*) from (select * from cars where price > 1) as cars;

also known as relational closure.

Simon Bertrang
A: 

For a more or less precise answer to the actual question asked, "Relational algebra - what is the proper way to represent a ‘having’ clause?", it needs to be stated first that the question itself seems to suggest, or presume, that there exists such a thing as "THE" relational algebra, but that presumption is simply untrue !

An algebra is a set of operators, and anyone can define any set of operators he likes, meaning anyone can define any algebra he likes ! In his most recent publication, Hugh Darwen mentions that RESTRICT is not a fundamental operator of the algebra, though lots of others do consider it as such.

Especially with respect to aggregations and summaries, there is little consensus as to how those should be incorporated in a relational algebra. Defining operators such as COUNT() (that take a relation as an argument value and return an integer) as part of the algebra, might be problematic wrt the closure property of the algebra, precisely because such operators do not return a relation ...

So the sorry, but nevertheless most appropriate, answer here seems to be that a conclusive answer to this question is almost impossible to give ...

Erwin Smout