Please use relational algebra not SQL. Thank you! Table needed is pasted on and an example ion the 7 queries are pasted as well.

Show the result of each of the sample queries in Section 6.5 as it would apply to the database state in Figure 3.6.

These are the 7 queries!

Query 1. Retrieve the name and address of all employees who work for the Research department.

RESEARCH_DEPT Dname=Research(DEPARTMENT) RESEARCH_EMPS (RESEARCH_DEPT Dnumber=DnoEMPLOYEE) RESULT Fname, Lname, Address(RESEARCH_EMPS)

As a single in-line expression, this query becomes: Fname, Lname, Address (Dname=Research(DEPARTMENT Dnumber=Dno(EMPLOYEE))

Query 2. For every project located in Stafford, list the project number, the controlling department number, and the department managers last name, address, and birth date.

STAFFORD_PROJS Plocation=Stafford(PROJECT) CONTR_DEPTS (STAFFORD_PROJS Dnum=DnumberDEPARTMENT) PROJ_DEPT_MGRS (CONTR_DEPTS Mgr_ssn=SsnEMPLOYEE)

RESULT Pnumber, Dnum, Lname, Address, Bdate(PROJ_DEPT_MGRS)

Query3. Findthenamesofemployeeswhoworkonalltheprojectscontrolled by department number 5.

DEPT5_PROJS (Pno)(Pnumber(Dnum=5(PROJECT))) EMP_PROJ (Ssn, Pno)(Essn, Pno(WORKS_ON)) RESULT_EMP_SSNS EMP_PROJ DEPT5_PROJS RESULT Lname, Fname(RESULT_EMP_SSNS * EMPLOYEE)

Query 4. Make a list of project numbers for projects that involve an employee whose last name is Smith, either as a worker or as a manager of the department that controls the project.

SMITHS(Essn) Ssn (Lname=Smith(EMPLOYEE)) SMITH_WORKER_PROJS Pno(WORKS_ON * SMITHS)

MGRS Lname, Dnumber(EMPLOYEE Ssn=Mgr_ssnDEPARTMENT) SMITH_MANAGED_DEPTS(Dnum) Dnumber (Lname=Smith(MGRS)) SMITH_MGR_PROJS(Pno) Pnumber(SMITH_MANAGED_DEPTS * PROJECT) RESULT (SMITH_WORKER_PROJS SMITH_MGR_PROJS)

Query 5. List the names of all employees with two or more dependents.

Strictly speaking, this query cannot be done in the basic (original) relational algebra. We have to use the AGGREGATE FUNCTION operation with the COUNT aggregate function. We assume that dependents of the same employee have distinct Dependent_name values.

T1(Ssn, No_of_dependents) Essn ICOUNT Dependent_name(DEPENDENT) T2 No_of_dependents>2(T1)

RESULT Lname, Fname(T2 * EMPLOYEE)

Query 6. Retrieve the names of employees who have no dependents.

This is an example of the type of query that uses the MINUS (SET DIFFERENCE) operation.

ALL_EMPS Ssn(EMPLOYEE) EMPS_WITH_DEPS(Ssn) Essn(DEPENDENT) EMPS_WITHOUT_DEPS (ALL_EMPS EMPS_WITH_DEPS) RESULT Lname, Fname(EMPS_WITHOUT_DEPS * EMPLOYEE)

Query 7. List the names of managers who have at least one dependent.

MGRS(Ssn) Mgr_ssn(DEPARTMENT) EMPS_WITH_DEPS(Ssn) Essn(DEPENDENT) MGRS_WITH_DEPS (MGRS EMPS_WITH_DEPS) RESULT Lname, Fname(MGRS_WITH_DEPS * EMPLOYEE)

Figure 3.6 One possible database state for the COMPANY relational database schema. EMPLOYEE Fname Dno Minit B T John Franklin 5 5 Alicia J 4 S Lname Ssn Bdate Address Sex Salary Super_ssn Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 Wong 333445555 1955-12-08 638 Voss, Houston, TX M 40000 888665555 Zelaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 987654321 Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX F 43000 888665555 Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M 38000 333445555 English 453453453 1972-07-31 5631 Rice, Houston, TX F 25000 333445555 Jabbar 987987987 1969-03-29 980 Dallas, Houston, TX M 25000 987654321 Borg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL Jennifer Ramesh 4 K 5 A 5 Joyce Ahmad V 4 James E 1 DEPARTMENT DEPT_LOCATIONS Dname Dnumber Dnumber Dlocation Mgr_start_date 1988-05-22 Research 5 1 Mgr_ssn 333445555 987654321 888665555 4 1995-01-01 4 Administration Headquarters Houston Stafford Bellaire Sugarland 1 1981-06-19 5 5 5 Houston WORKS ON PROJECT Essn Pno Hours Pname Pnumber Plocation Dnum 123456789 1 32.5 ProductX 1 Bellaire 5 2 7.5 ProductY 2 5 123456789 666884444 Sugarland Houston 3 40.0 Productz 3 5 453453453 1 20.0 10 Stafford 4 453453453 2 20.0 Computerization Reorganization Newbenefits 20 Houston 1 333445555 2 10.0 30 Stafford 4 333445555 3 10.0 333445555 10 10.0 DEPENDENT 333445555 20 10.0 Essn Sex Bdate Dependent_name Alice 999887777 30 30.0 333445555 F 1986-04-05 999887777 10 333445555 Theodore M 1983-10-25 10.0 35.0 Relationship Daughter Son Spouse Spouse 987987987 10 F 1958-05-03 333445555 987654321 Joy Abner 987987987 30 5.0 M 1942-02-28 987654321 30 20.0 123456789 Michael M 1988-01-04 Son 987654321 20 15.0 123456789 Alice F 1988-12-30 Daughter Spouse 888665555 20 NULL 123456789 Elizabeth F 1967-05-05