CS457 Syllabus & Progress

Technique to test for subset and superset conditions

• The Superset (Contains) (⊇) and Subset (⊆) Tests

• SQL does not support a subset/superset test operation

It is not possible to write the following boolean condition in SQL:

 ``` SELECT ... FROM ... WHERE set1 CONTAINS set2 ```

• As you will see, we will need to use:

 The superset (⊇) test in the set division query and The subset (⊆) test in some other useful queries

• Formulating the Superset ("contains") condition

• Superset:

• A set set1 contains (is a superset of) another set set2 if:

 Every element of set2 is also an element of set1

Graphically:

• We can perform a superset test operation as follows in SQL:

You can see that the condition that "set1 constains set2" is equivalent to:

 ``` set1 CONTAINS set2 <==> set2 - set1 = EMPTY ```

Therefore:

 ``` SELECT ... FROM ... WHERE set1 CONTAINS set2 // Pseudo code !!! Is equivalent to: SELECT ... FROM ... WHERE set2 - set1 = EMPTY // Pseudo code !!! Or: SELECT ... FROM ... WHERE NOT EXISTS {set2 - set1} // Almost SQL !!! Or: SELECT ... FROM ... WHERE NOT EXISTS (SELECT * FROM universe-relation R (with key x) WHERE x IN set2 AND x NOT IN set1) // SQL - but you still need to // write SQL command to find // set1 and set2 ```

• Formulating the Subset condition

• It should be clear from the above discussion that:

 ``` SELECT ... FROM ... WHERE set1 is a SUBSET of set2 // Pseudo code !!! Is equivalent to: SELECT ... FROM ... WHERE set1 - set2 = EMPTY // Pseudo code !!! Or: SELECT ... FROM ... WHERE NOT EXISTS {set1 - set2} // Almost SQL !!! Or: SELECT ... FROM ... WHERE NOT EXISTS (SELECT * FROM universe-set WHERE x IN set1 AND x NOT IN set2) // SQL - but you need to // "fill in the blanks" ```