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:
in to a set equality test:
At first, you may think we did not make any simplification
But:
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:
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
Then: A can only be one of these sets:
Notice that:

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

Example:

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:

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')