---

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 is 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 is that SSN2 is part of the result is all the tuples (SSN2, P1), (SSN2, P2), (SSN2, 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:

  ---

  ---

  ---

  ---

• Expressing the division operation in terms of other relational algebra operations
  • The set division operation can be expressed as a sequence of other relational algebra operations

  • The steps are easier to understand using a concrete example....

    ---

  • Consider the following content of the Emp_Proj and emp4_proj relations:

    ---

  • OK, I will now show the steps the achieve "set division.

    For each step, I will show the result using the above database content (as illustrative example)

    construct a "minimum qualifying" relation (SSN, Part#) : The "minimum qualify" relation will contain the following: Meaning of this relation: This relation contains the tuples that is necessary for each employee to be selected by the query The "qualify" relation can be constructed as follows: qualify   =   π ssn (Emp_Proj) × emp4_proj         Compute the difference "qualify - Emp_Proj": If we project out the SSN from the difference set, we will obtain all disqualified SSNs !!! The last step is to obtain the set of qualified SSN, that's simple use set difference again: π ssn (Emp_Proj) − π ssn (UnQualify)

    ---

  • Summary: the division of relation R1(A,B) by relation R2(B) can be obtained using this sequence of set algebra operations: