Here is the relational schema given :

employee (person-name , street, city)
works (person-name, company-name, salary)
company (company-name, city)
manages (person-name, manager-name)

Q: Find the names of all employees who live in the same city and on the same street as do their managers.

I find the solution somewhere : Solution

but solution seems wrong which is just after second natural-join sign there is no select statement sign and just after that sign, a predicate appears 0_o , in that, from where does that manager-name attribute comes from?. I am completely lost.

My solution seems to be long since I used the Cartesian-product , and the query is bit long (of which I'm not even sure it's right or wrong) :(

  • 1
    Is this a homework question? Not sure if this is the right place for this... DBAs administer RDBMSs. I took database theory several years ago and have not had to look at this notation since then. ;-)
    – Anon246
    Commented Dec 27, 2011 at 23:30
  • @Strommy This is not homework. I'm just studying DBMS theory first.
    – Mr.Anubis
    Commented Dec 28, 2011 at 11:22

1 Answer 1


DISCLAIMER : Never Learned Relational Algebra but it looks interesting

From the schema given and your question, this is what the SQL should be:

    manages emp_mgr
    INNER JOIN employee emp ON emp_mgr.person_name  = emp.person_name
    INNER JOIN employee mgr ON emp_mgr.manager_name = mgr.person_name
    emp.street = mgr.street AND
    emp.city = mgr.city

Here is another query that only uses JOINs, no WHERE clause:

    (SELECT A.person_name,B.street,B.city FROM manages A
    INNER JOIN employee B ON A.person_name = B.person_name) emp
    (SELECT A.manager_name,B.street,B.city FROM manages A
    INNER JOIN employee B ON A.manager_name = B.person_name) mgr

The first query gets all employees who are managed and their managers in the form of a Cartesian Product. Then, it looks for a common street and city.

The second query collects personnel records (name,street,city) of employees and their managers and performs a NATURAL JOIN between the employess and their managers using (street,city).

If you can transalate both queries back to Relational Algebra, I think you will have what you are looking for. I believe the second may be of better help.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.