---

Set Division

---

• The Set Division operation
  • The best way to explain the set division operation is by means of an example....

    Consider the follow content of "Works-On" relation in the company database:

    The relation "Emp_Proj" is obtained by:

    • Emp_Proj = π _essn,pnum (works_on)

  • We see that when the project# are grouped by SSN in relation Emp_Proj, we get the following grouping:
    • SSN1: {P1, P2, P3, P4}
    • SSN2: {P1, P3}
    • SSN3: {P2, P3, P4}
    • SSN4: {P1, P2, P3}

  • The set "emp4_proj" is the set of project# worked on by employee SSN4:
    • emp4_proj = {P1, P2, P3}

    ---

  • The result of the set division of relation Emp_Proj by the relation emp4_proj is the set:

    Emp_Proj ÷ emp4_proj = {SSN1, SSN4}

    The reason that SSN1 is part of the result is all the tuples (SSN1, P1), (SSN1, P2), (SSN1, P3) are in the original data set

    The reason that SSN4 is part of the result is all the tuples (SSN4, P1), (SSN4, P2), (SSN4, P3) are in the original data set

  • Graphically:

  ---

  ---

• Example Query that uses the Set Division operation
  • Query:

    Find fname and lname of all employees who work on all projects that "John Smith" works on

    i.e., suppose "John Smith" works on projects {P1, P2, P3}, we must find employees that also work on P1, P2 and P3 (the employee may work on other projects in additions to P1, P2 and P3)

    ---

    Solution:

    Remember how the set division works:

    Employees SSN1, SSN4 works on all projects P1, P2 and P3

  ---

  ---

  ---

  ---

• Another Example Query that uses the Set Division operation
  • Query

    Find fname and lname of all employees who work on all projects controlled by the department number 5

    i.e., suppose department #5 controls projects {P1, P2, P3}, we must find employees that also work on P1, P2 and P3 (the employee may work on other projects in additions to P1, P2 and P3)

    Strategy:

    Obtain the set of project numbers controlled by department 5 Divide "Works_on(essn,pno)" by the set of project numbers controlled by department 5 This will give us the set of SSN Join the set of SSN with employee to get the fname and lname

    ---

    Solution: