Tutorial 6 Relational Algebra Given COMPANY database schema

Tutorial # 6 Relational Algebra

Given COMPANY database schema below, specify the following queries using relational algebra 1. Retrieve the employees. information of male • σ sex='M' (EMPLOYEE) 2. List the project name and location that belongs to the company. • π Pname, Plocation (PROJECT) By: Arwa Alturki 2

Given COMPANY database schema below, specify the following queries using relational algebra 3. Retrieve the names of employees in department 5 who work more than 10 hours per week on the 'Product. X' project. • EMP_W_X ← (σ Pname='Product. X’ (PROJECT)) * (Pnumber), (PNO) (WORKS_ON) • EMP_WORK_10 ← (EMPLOYEE) * (SSN), (ESSN) (σ HOURS > 10 (EMP_W_X)) • RESULT ← π Lname, Fname (σ DNO=5 (EMP_WORK_10)) By: Arwa Alturki 3

Given COMPANY database schema below, specify the following queries using relational algebra 4. List the names of employees who have a dependent with the same first name as themselves. • E ← (EMPLOYEE) * (SSN, Fnmae), (ESSN, Dependant_Name) (DEPENDENT) • R ← π Lname, Fnmae (E) By: Arwa Alturki 4

Given COMPANY database schema below, specify the following queries using relational algebra 5. Find the names of employees that are directly supervised by 'Franklin Wong'. • WONG_SSN ← π SSN (σ Fname='Franklin' AND Lname='Wong’ (EMPLOYEE)) • WONG_EMPS ← (EMPLOYEE) * (Super_SSN), (SSN) (WONG_SSN) • RESULT ← π Lname, Fname (WONG_EMPS) By: Arwa Alturki 5

Given COMPANY database schema below, specify the following queries using relational algebra 6. Retrieve the names of employees who work on every project. • • PROJ_EMPS(PNO, SSN) ← π PNO, ESSN (WORKS_ON) ALL_PROJS(PNO) ← π Pnumber (PROJECT) EMPS_ALL_PROJS ← PROJ_EMPS ÷ ALLPROJS RESULT ← π Lname, Fname (EMPLOYEE * EMP_ALL_PROJS) By: Arwa Alturki 6

Given COMPANY database schema below, specify the following queries using relational algebra 7. Retrieve the names of employees who do not work on any project. • ALL_EMPS ← π SSN (EMPLOYEE) • WORKING_EMPS(SSN) ← π ESSN (WORKS_ON) • NON_WORKING_EMPS ← ALL_EMPS - WORKING_EMPS • RESULT ← π Lname, Fname (NON_WORKING_EMPS * EMPLOYEE) By: Arwa Alturki 7

Given COMPANY database schema below, specify the following queries using relational algebra 8. For each department, retrieve the department name, and the average salary of employees working in that department. • DEPT_AVG_SALS(Dnumber, AVG_SAL) ← DNO F AVG Salary(EMPLOYEE) • RESULT ← π Dname, AVG_SAL ( DEPT_AVG_SALS * DEPARTMENT ) 9. Retrieve the average salary of all female employees • RESULT(AVG_F_SAL) ← F AVG Salary (σ SEX='F’ (EMPLOYEE) ) By: Arwa Alturki 8

Given COMPANY database schema below, specify the following queries using relational algebra 10. List the last names of department managers who have no dependents. • DEPT_MANAGERS(SSN) ← π Mgr_SSN (DEPARTMENT) • EMPS_DEPENDENTS(SSN) ← π ESSN (DEPENDENT) • RESULT_EMPS ← DEPT_MANAGERS - EMPS_DEPENDENTS • RESULT ← π Lname, Fname (EMPLOYEE * RESULT_EMPS) By: Arwa Alturki 9

Any Question? By: Arwa Alturki 10