Can all SQL queries be represented in Relational A

Question

My query includes a having and count or all in. How are these represented in RA/DRC/TRC? Would I have to simplify my SQL query even more? Here is a simplified example:

empl(employee (primary key), city)
managers(employee (primary key), manager (foreign key of employee))

If I were to find all the employees who are managers (from any city) of ALL the employees in city X.. I would need to use having/count. Not sure how this would be done in RA/DRC/TRC.

I know the need for such a query might not make sense but assume it is sensible for the purpose of this question.

Thanks

Answer 1

Your query was stated a bit ambiguous. It is indeed the intent to find all managers who are the manager for EACH AND EVERY employee that is in city X ?

As dportas indicated, that's perfectly doable in RA.

Here's how :

Get the collection of all the employees in city X. Call that EMPX.

Get the collection of all managers. Call that MGRS.

Make the cartesian product of the two. Call that MGRS_EMPX.

Subtract from that the actual value of the table (appropriately projected down to the needed attributes) that says which managers manage which employee. That difference holds all the combinations of managers that really exist, with an employee that is located in X, but where that manager does not manage that employee.

Project that difference down onto the manager attribute. That relation tells you which managers exist such that there exists some employee in city X that is NOT managed by that manager.

Subtract this relation from MGRS. Obviously, this relation tells you which managers exist such that there does NOT exist an employee located in city X that is NOT managed by that manager.

Rewriting this negation of an existential quantifier as a universal quantification will reveal that this is precisely the result that you want : NOT EXISTS (EMP : EMP is in X AND EMP managed by MGR) === FORALL EMP : NOT (EMP is in X AND EMP managed by MGR) === FORALL EMP : (EMP is not in X OR EMP is managed by MGR) === FORALL EMP : ( if EMP is in X then EMP is managed by MGR).

And all of these are perfectly fine algebra operations.

(Side exercise : see what happens if there are no employees located in city X at all.)

Answer 2

Aggregate queries aren't expressible in RA.

Answer 3

maybe you oversimplified your question.. but finding manager for employees in city X is a simple join - no Count or Having.

edit:

select  * 
from managers m,
( select employee from empl where city = 'XXXXX' ) e
where m.employee = e.employee

Answer 4

Relational Division is the answer to your specific example - you don't need an aggregate. Division is part of the algebra.

Your more general question is a complex one because of the problems of determining when any possible SQL result is the same as a relational one. Is a SQL query that returns duplicate rows equivalent to a relational expression that doesn't? Is a SQL query with SQL-style nulls equivalent to any relational expression? How do you express a relational projection over no attributes using only SQL syntax?

I think the only sensible answer is to say that the SQL model and relational one are two quite different and incompatible things. You probably shouldn't go looking too hard for correspondences between them.