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