# Relational algebra question

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 :

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

-
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

DISCLAIMER : Never Learned Relational Algebra but it looks interesting

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

``````SELECT
emp_mgr.person_name
FROM
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
WHERE
emp.street = mgr.street AND
emp.city = mgr.city
;
``````

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

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

-
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 cstheory.stackexchange.com – RolandoMySQLDBA Mar 2 '12 at 21:53