Let`s consider task:
Determine makers which produce models of all types (in Computer firm schema).
The keyword here is “all” which means that maker in Product table must have models of every type: PC, Laptop, and Printer.
There is special operation for solving this type of tasks in the relational algebra. This operation is relational division (DIVIDE BY).
This operation makes solution of considering task very simple :
The hooks determine projection operation to the corresponding attributes.
The relational division operation is superfluous. It can be expressed by the other operations of the relational algebra. Perhaps, that`s the reason why it absents in the SQL.
Let`s introduce some methods of implementation of relational division in SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL on considering example.
If we use fact that there is only three types of product corresponding to description of the knowledge domain, we may group data by maker and count quantity of unique types. Then we select only makers with quantity equal three.
But if the type`s count is arbitrary this solution will be right only in current state of database, not in every possible. So we need to replace hard number by “variable”, i.e. to use subquery:
If we subtract model types of every maker from all types then the resultant set must have no rows for maker with all types of product.
This query may be rewritten in shorter form if we take into account fact that the ALL predicate returns TRUE if the subquery have no rows:
The ALL predicate`s list will be empty for target makers, and in all other cases it would contain types which are absent in the maker product list and the operation “=” for all models returns FALSE.
There is no such type of product which is absent in the maker`s product list.
All solutions, except first one, are using correlation subquery for determining the maker`s product types set.
We should also note that the solution with grouping is not applicable for cases in which we need to divide by not full set of types, but its subset. For instance, if we need to find all makers with product`s set include (or equal to) set of types determined by some criteria. Other methods may be adapted for solving such tasks.
Suggested exercises: 71