Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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) :(

share|improve this question
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 Dec 27 '11 at 23:30
@Strommy This is not homework. I'm just studying DBMS theory first. – Mr.Anubis Dec 28 '11 at 11:22
up vote 1 down vote accepted

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 =

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

    (SELECT A.person_name,B.street, FROM manages A
    INNER JOIN employee B ON A.person_name = B.person_name) emp
    (SELECT A.manager_name,B.street, 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.

share|improve this answer
it's about as interesting as a soggy stick – jcolebrand Mar 2 '12 at 21:41
@jcolebrand LOL. Maybe this should have been migrated to – RolandoMySQLDBA Mar 2 '12 at 21:53

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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