Question

Consider the relations
EMPLOYEE(emp#, name)
ASSIGNED_TO(project#, emp#)
PROJECT(project#, project_name, chief)
Express the following queries in Relational Algebra
(i) Get details of employee working on both comp354 and comp345 project numbers.
(ii)Find the employee number of employee who do not work on project comp678.



pleaz send me this qyestion anwer.

Answer 1

Answer on Question#43083 - Programming – Other

Consider the relations

EMPLOYEE(emp#, name)

ASSIGNED_TO(project#, emp#)

PROJECT(project#, project_name, chief)

Express the following queries in Relational Algebra

(i) Get details of employee working on both comp354 and comp345 project numbers.

**Solution:**

Set table EMPLOYEE to E, and set table ASSIGNED_TO to A.

Select employees working on projects comp354 and comp345 from table A:

SELECT * FROM ASSIGNED_TO WHERE project# = 'comp354' AND project# = 'comp345';

Join obtained table with table E on the basis of emp#:

SELECT E.* FROM EMPLOYEE E JOIN (SELECT * FROM ASSIGNED_TO WHERE project# = 'comp354' AND project# = 'comp345') A ON E.emp# = A.emp#;

Finally, select employee details from obtained table:

SELECT emp#, name FROM (SELECT E.* FROM EMPLOYEE E JOIN (SELECT * FROM ASSIGNED_TO WHERE project# = 'comp354' AND project# = 'comp345') A ON E.emp# = A.emp#);

**Answer:**

SELECT emp#, name FROM (SELECT E.* FROM EMPLOYEE E JOIN (SELECT * FROM ASSIGNED_TO WHERE project# = 'comp354' AND project# = 'comp345') A ON E.emp# = A.emp#);

SQL query for this:

SELECT * FROM EMPLOYED_TO ON EMPLOYEE.EMPNO=ASSIGNED_TO.EMPNO WHERE ASSIGNED_TO.PNO='comp345' OR ASSIGNED_TO.PNO='comp354';

(ii) Find the employee number of employee who do not work on project comp678.

**Solution:**

Set table EMPLOYEE to E, and set table ASSIGNED_TO to A.

Select employees not working on project comp678:

SELECT * FROM ASSIGNED_TO WHERE project# != 'comp678';

Join obtained table with table E on the basis of emp#:

SELECT E.* FROM EMPLOYEE E JOIN (SELECT * FROM ASSIGNED_TO WHERE project# != 'comp678') A ON E.emp# = A.emp#;

Finally, select employee number from obtained table:

SELECT emp# FROM (SELECT E.* FROM EMPLOYEE E JOIN (SELECT * FROM ASSIGNED_TO WHERE project# != 'comp678') A ON E.emp# = A.emp#);

**Answer:**

SELECT emp# FROM (SELECT E.* FROM EMPLOYEE E JOIN (SELECT * FROM ASSIGNED_TO WHERE project# != 'comp678') A ON E.emp# = A.emp#);

SQL query for this:

SELECT EMPLOYEE.EMPNO FROM EMPLOYEE JOIN ASSIGNED_TO ON EMPLOYEE.EMPNO=ASSIGNED_TO.EMPNO WHERE ASSIGNED_TO.PNO!='comp678';