Self-join for Tuple Relational Calculus

Question

Find the names, company names, and the manager names of all employees who work for a company located in the city of Vancouver.

How to write this in Tuple Relational Calculus? I have been thinking this for a long time. Whenever it goes to "manages", I don't know how to deal with the manages.manager_name to employee.person_name

Answer 1

If you take the cartesian product of all tuples in the works, company and manages relations, then restrict the results to those tuples where the company_names match between works and company, the person_names match between works and manages, and the city matches 'Vancouver', then you can take any person_name and company_name, together with manager_name from manages, to make up your result tuple, e.g.:

{t : {person_name, company_name, manager_name} |
     ∀ w, c, m (
         works(w) ∧ company(c) ∧ manages(m) ∧
         w.company_name = c.company_name ∧ c.city = 'Vancouver' ∧
         w.person_name = m.person_name ∧ t.person_name = w.person_name ∧
         t.company_name = c.company_name ∧ t.manager_name = m.manager_name
     )
}

If you wanted to join with employee for both person_name and manager_name in works (even though it would be redundant), then you could do so:

{t : {person_name, company_name, manager_name} |
     ∀ e1, w, c, m, e2 (
         employee(e1) ∧ works(w) ∧ company(c) ∧ manages(m) ∧ employee(e2) ∧
         e1.person_name = w.person_name ∧ e1.person_name = m.person_name ∧ 
         w.company_name = c.company_name ∧ c.city = 'Vancouver' ∧
         m.manager_name = e2.person_name ∧ t.manager_name = e2.person_name ∧
         t.person_name = e1.person_name ∧ t.company_name = c.company_name
     )
}

Hope that makes sense, it's been a while and I had to look it up.