Using Relational Algebra specify the following queries on the COMPANY relational

Question

Using Relational Algebra specify the following queries on the COMPANY relational database schema

(a) For each project, find the total spent hours by all employees

(b) For each project, list the names of projects the have total spent hours (spent by all employees) less than the average spent hours of all projects.

(c) Retrieve the names of employees who work on every project controlled by Research department.

EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno DEPARTMENT Dname Dnumber Mgr ssn Mgr_start date DEPT LOCATIONS Dnumber Dlocation PROJECT PnamePnumber Plocation Dnum WORKS ON Essn Pno | Hours DEPENDENT Essn Dependent name SexBdate Relationship

Explanation / Answer

a)

First join WORKS_ON and PROJECT to find the employees working on a particular project then sum up the hours spent by those employees on a particular project by using Group By.

QUERY:

SELECT TOTAL(temp.HRS) FROM (SELECT a.Pnumber AS PNUM, b.Pno, b.Hours AS HRS FROM PROJECT a, WORKS_ON b WHERE a.Pnumber = b.Pno ) AS temp GROUP BY temp.PNUM;

b)

First retrieve the number of hours spent on each project using query (a) and then extend it by comparing with average hours of all projects.

QUERY:

SELECT PNAME FROM (SELECT a.Pnumber AS PNUM, a.Pname AS PNAME, b.Hours AS HRS, b.Pno FROM PROJECT a, WORKS_ON b WHERE a.Pnumber = b.Pno ) AS temp WHERE temp.HRS < (SELECT AVG(Hours) FROM WORKS_ON) GROUP BY temp.PNUM;

c)

In this query we first join tables project and department to retrieve project ID of the projects under research dept. and then retrieve the ESSN of the employees working on those projects by joining with WORKS_ON table. and finally join this result with Employee to find Fname and Lname of the ESSN numbers thus retrieved.

QUERY:

SELECT E.Fname, E.Lname FROM (SELECT w.EID FROM (SELECT a.Pnumber as PNUM FROM PROJECT a, DEPARTMENT b WHERE a.Dnum = b.Dnumber and b.Dname = 'Research') AS temp1, WORKS_ON w WHERE temp1.PNUM = w.Pno) AS temp2, EMPLOYEE E WHERE temp2.ESSN = E.ESSN;

Please Do give a thumbs up and comment if any doubts!

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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