### Using count() to formulate subset/superset queries

• A useful mathematical equality

• Proposition:

 ``` A ⊆ B <===> A = (A ∩ B) ```

• Proof:

 ``` We first prove: A ⊆ B ===> A = (A ∩ B) Given: A ⊆ B To prove that 2 set are equal: A = (A ∩ B) we must prove that: A ⊆ (A ∩ B) and (A ∩ B) ⊆ A First, this is trivially true: (A ∩ B) ⊆ A All that remains is to prove: A ⊆ (A ∩ B) Let x be an arbitrary element in A Then: x ∈ A And also: x ∈ B, because A ⊆ B (given) Therefore: x ∈ (A ∩ B) So: ∀ x ∈ A => x ∈ (A ∩ B) I.e.: A ⊆ (A ∩ B) Now we prove: A = (A ∩ B) ===> A ⊆ B Given: A = (A ∩ B) Let x be an arbitrary element in A Since A = (A ∩ B) and x ∈ A, we have that: x ∈ (A ∩ B) This means: x ∈ A and x ∈ B Therefore: ∀ x ∈ A ==> x ∈ B This means: A ⊆ B ```

• Discussion:

• The above mathematical equality allow us to change a subset test:

 ``` A ⊆ B ```

in to a set equality test:

 ``` A = (A ∩ B) ```

• At first, you may think we did not make any simplification

But:

• There is a special proporty between these 2 sets:

 The set (A ∩ B) is always a subset of A !!!

This property allows us to simplify the equality test among sets by simply counting the elements in the sets !!!

• Testing equality of 2 sets when you know that one set is always a subset of the other

• In general, we cannot use size of sets to check if the different sets are equal

Example:

 ``` A = { 1, 2, 3 } size(A) = 3 B = { 1, 4, 6 } size(B) = 3 ```

Size(A) = Size(B), but the sets A and B are not equal to each other.

• In the special case that a set A is a subset of another set B; we can check the equality of the sets by:

 checking the sizes !!!!

Illustration:

• Suppose you know that

 A is a subset of B (and B = {1, 4, 6})

Then: A can only be one of these sets:

 Empty {1} {4} {6} {1, 4} {1, 6} {4, 6} {1, 4, 6}

• Notice that:

• The set A is equal to the set B if an only if:

 Size(A) = Size(B)

• An alternative technique to formulate a division query: testing the size of 2 sets

• Example:

 Find fname and lname of employees who works on all projects controlled by department number 4.

• As we have seen before, it can be solve using the contains construct as follows:

 ``` SELECT fname, lname FROM employee WHERE "set of projects worked on by emplyee.ssn" CONTAINS "set of projects controlled by department 4" ```

• We can use the Mathematical Equality above and work out the query as follows:

 ``` SELECT fname, lname FROM employee WHERE "set of projects worked on by emplyee.ssn" CONTAINS "set of projects controlled by department 4" SELECT fname, lname FROM employee WHERE "set of projects controlled by department 4" ⊆ "set of projects worked on by emplyee.ssn" SELECT fname, lname FROM employee WHERE "set of projects controlled by department 4" = "set of projects worked on by emplyee.ssn AND controlled by dept 4" ```

• Notice that:

 The second set ("set of projects controlled by dept 4 and worked on by emplyee.ssn") is always a subset of the first set ("set of projects controlled by department 4" )

By the property discussed above, we test the equality of sets by testing the size of these 2 sets:

 ``` SELECT fname, lname FROM employee WHERE "set of projects worked on by emplyee.ssn AND controlled by dept 4" = "set of projects controlled by department 4" Change to size test: SELECT fname, lname FROM employee WHERE Size( "set of projects controlled by dept 4 and worked on by emplyee.ssn" ) = Size( "set of projects controlled by department 4" ) SELECT fname, lname FROM employee WHERE count( "set of projects controlled by dept 4 and worked on by emplyee.ssn" ) = count( "set of projects controlled by department 4" ) SELECT fname, lname FROM employee WHERE ( select count(distinct pno) from works_on, project where works_on.pno = project.pnumber and project.dnum = 4 and employee.ssn = works_on.essn ) = ( select count(distinct pnumber) from project where dnum=4 ) ```

• Another example: subset/superset test through the size of the sets

• Query:

 Find names of projects that is worked on by only employees in the 'Research' department

• Solution in pseudo SQL:

 ``` SELECT pname FROM project P WHERE "set of employees working on project P" ⊆ "set of employees in the 'Research' department" ```

• Apply the mathematical equality and we can re-write the query as follows:

 ``` SELECT pname FROM project P WHERE "set of employees working on project P" ⊆ "set of employees in the 'Research' department" SELECT pname FROM project P WHERE "set of employees working on project P " = "set of employees in the 'Research' department AND works on proj P" Because the 2nd set is always a subset of the first set, we can use the size test to test set equality: SELECT pname FROM project P WHERE size("set of employees working on project P ") = size("set of employees in the 'Research' department AND works on proj P") SELECT pname FROM project P WHERE (select count(distinct essn) from works_on where works_on.pno = P.pnumber) = (select count(distinct essn) from works_on, employee, department where works_on.pno = P.pnumber and works_on.essn = employee.ssn and employee.dno = department.dnumber and dname = 'Research') ```