Can all SQL queries be represented in Relational Algebra, Domain and Tuple relational calculus

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

Sewell

See this article for more about relational division – Capri

Which is the question you are asking--the post title or the post body? What versions of RA/DRC/TRC? What does "represented in" mean exactly? What are you able to do following what reference? – Scientistic

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.)

Stableman

Excellent reply. Thorough and informative. Much obliged, kind sir! – Sewell

Answer 2

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.

Deify

How would I use division in this case? Are you referring to set difference? – Sewell

Managers DIVIDE (RESTRICT(city='x') Empl) – Deify

Answer 3

Aggregate queries aren't expressible in RA.

Meteorite

True but the OP's assertion, "I would need to use having/count," is false. – Chant

Answer 4

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

Background

Hi Randy, thanks for the comment. The query is to find managers (who can be in different cities) of ALL employees of city X. – Sewell

Randy, your query would give managers of ANY (1 or more) employees in city X. The example question asks for the managers of ALL employees in city X. For example if city X had three employees, E1 E2 and E3.. Assume M1 is manager of E1, E2, E3 and M2 is a manager of only E2, E3.. The table would only return M1 – Sewell

And it uses a SQL Antipattern of implicit syntax which is a very poor programming technique and there is no excuse to ever be using in the 21st century – Chufa

@John Smith - ok i did not get that from your original post. – Background