CONVERT THE FOLLOWING MYSQL QUERY TO RELATIONAL ALGEBRA USE company; -- Retrieve

Question

CONVERT THE FOLLOWING MYSQL QUERY TO RELATIONAL ALGEBRA

USE company;

-- Retrieve the last name of each supervisor who does not have any dependent and works on some project(s) controlled by his/her own department.
SELECT e.LName FROM Employee e
LEFT JOIN Works_on w ON e.ssn = w.essn
LEFT JOIN Project p ON w.pno = p.pnumber
WHERE e.ssn = w.essn
AND e.dno = p.dnum
AND w.pno = p.pnumber
AND EXISTS (SELECT mgrssn from Department d   -- gets supervisors last names
WHERE d.mgrssn = e.ssn
) AND NOT EXISTS (
SELECT d1.essn from Dependent d1
WHERE e.ssn = d1.essn
);

Explanation / Answer

1)

SELECT d1.essn from Department d1
WHERE e.ssn = d1.essn

E1=? d1.ssn ? e.ssn=d1.ssn(?e(employee) x ?d(department))

2)

SELECT mgrssn from Department d   -- gets supervisors last names
WHERE d.mgrssn = e.ssn

E2=? sname ? d.mgrssn=e.ssn(?e(employee) x ?d(department))

3)

SELECT mgrssn from Department d   -- gets supervisors last names
WHERE d.mgrssn = e.ssn
)

AND NOT EXISTS

(
SELECT d1.essn from Department d1
WHERE e.ssn = d1.essn)

E3= ? sname (E2 intersection symbol ( E2

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.