1 / 13

Exercise 1 Relational Algebra

Exercise 1 Relational Algebra. 6 Relations of Company. Exercises. (a) Retrieve the names of employees in department 5 who work more than 10 hours per week on the 'ProductX' project. (b) List the names of employees who have a dependent with the same first name as themselves.

lane
Download Presentation

Exercise 1 Relational Algebra

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Exercise 1Relational Algebra Database System-dww

  2. 6 Relations of Company Database System-dww

  3. Exercises • (a) Retrieve the names of employees in department 5 who work more than 10 hours per week on the 'ProductX' project. • (b) List the names of employees who have a dependent with the same first name as themselves. • (c) Find the names of employees that are directly supervised by 'Franklin Wong'. • (d) For each project, list the project name and the total hours per week (by all employees) spent on that project. • (e) Retrieve the names of employees who work on every project. Database System-dww

  4. Exercises • (f) Retrieve the names of employees who do not work on any project. • (g) For each department, retrieve the department name, and the average salary of employees working in that department. • (h) Retrieve the average salary of all female employees. • (i) Find the names and addresses of employees who work on at least one project located in Houston but whose department has no location in Houston. • List the last names of department managers who have no dependents. Database System-dww

  5. P Pnumber=Pno W ESSN=SSN E Fname, Lname (?) Pname=‘ProductX’ Hours > 10 Dno = 5 > 10 ProductX 5 WORKS_ON_ProductX (Rel.A) (PNAME=‘ProductX’(PROJECT) PNUMBER=PNO(WORKS_ON)) EMPLOYEE_DEPT5_WORKS_MORE_10_onProductX (Rel.B) ((EMPLOYEE) SSN=ESSN( HOURS>10(Rel.A))) RESULT  FNAME,LNAME(DNO=‘5’ (Rel.B)) (a) Retrieve the names of employees in department 5 who work more than 10 hours per week on the 'ProductX' project Graphical approach can be used to help the logical and prosedural approach Example of above query, the graphical approach: clue from query the relations that (may) work on the query are in the red text Database System-dww

  6. Dp Depend_name=Fname E Fname, Lname (?) ESSN=SSN RESULT  FNAME,LNAME(DEPENDENT (ESSN,DEPEND_NAME)=(SSN,FNAME)(EMPLOYEE)) List the names of employees who have a dependent with the same first name as themselves. Graphical approach Database System-dww

  7. E_SSN_FRANKLIN_WONG(Rel.A) ( SSN( FNAME=‘FRANKLIN’ LNAME=‘WONG’(EMPLOYEE))) E_SUPERVISED_BY_FRANKLIN_WONG (Result) ( FNAME, LNAMES(EMPLOYEE) SUPERSSN=SSN(Rel.A)) (c) Find the names of employees that are directly supervised by 'Franklin Wong'. Graphical for next questions (try by Urself) Database System-dww

  8. TOTAL_HOURS_PROJECT (Rel.A) (PNOℱSUM HOURS(WORKS_ON)) RESULT ( PNAME, TOT_HOURS(Rel.A) PNO=PNUMBER(PROJECT)) (d) For each project, list the project name and the total hours per week (by all employees) spent on that project Using function Database System-dww

  9. ALL_EMPLOYEE(SSN, PNO)(Rel.A) ( SSN, PNO(WORKS_ON)) ALL_PROJECT(PNO)(Rel.B) ( PNO(PROJECT)) E_ALL_PROJECT (Rel.A÷Rel.B) RESULT ( FNAME, LNAME (E_ALL_PROJECT * EMPLOYEE)) (e) Retrieve the names of employees who work on every project Database System-dww

  10. ALL_EMPLOYEE(SSN)(Rel.A) ( SSN(EMPLOYEE)) ALL_EMPLOYEE_WORKING(SSN)(Rel.B) ( ESSN(WORKS_ON)) E_NOT_WORKING (Rel.A - Rel.B) RESULT ( FNAME, LNAME ( (EMPLOYEE) SSN=ESSN(E_NOT_WORKING)) (f) Retrieve the names of employees who do not work on any project Database System-dww

  11. AVERAGE_SALARY_DEPARTMENT(Rel.A) (DNOℱAVG SALARY(EMPLOYEE)) RESULT ( DNO, AVG_SALARY(Rel.A * EMPLOYEE)) RESULT ℱAVG SALARY( SEX=‘F’(EMPLOYEE)) (g) For each department, retrieve the department name, and the average salary of employees working in that department (h) Retrieve the average salary of all female employees Database System-dww

  12. E_WORK_PROJ_IN_HOUSTON(Rel.A) ( ESSN(WORKS_ON) PNO=PNUMBER ( PLOCATION=‘HOUSTON’(PROJECT))) E_D_NOT_IN_HOUSTON(Rel.B) ( SSN(EMPLOYEE) DNO=DNUMBER ( DLOCATION≠‘HOUSTON’(DEPARTMENT_LOCATIONS))) RESULT ( FNAME, LNAME, ADDRESS((EMPLOYEE) (Rel.A-Rel.B)) (i) Find the names and addresses of employees who work on at least one project located in Houston but whose department has no location in Houston Database System-dww

  13. D_MNG_SSN(Rel.A)( SSN(DEPARTMENT) E_WITH_DEPENDENT(Rel.A)( SSN(DPENDENT) MNGR_NO_DEPENDENT(Rel.A – Rel.B) RESULT( LNAME(EMPLOYEE * MNGR_NO_DEPENDENT)) (j)List the last names of department managers who have no dependents Database System-dww

More Related