loading..
Ðóññêèé    English
21:03

Relational division

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 [1]:

  1. Product[maker, type] DIVIDE BY Product[type]

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.

Grouping

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.

Èòàê,

Console
Execute
  1. SELECT maker
  2. FROM Product
  3. GROUP BY maker
  4. HAVING COUNT(DISTINCT type) = 3;

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:

Console
Execute
  1. SELECT maker
  2. FROM Product
  3. GROUP BY maker
  4. HAVING COUNT(DISTINCT type) = (SELECT COUNT(DISTINCT type) FROM Product);

Subtraction

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.

Console
Execute
  1. SELECT DISTINCT maker
  2. FROM Product Pr1
  3. WHERE 0 = (SELECT COUNT(*) FROM
  4. (SELECT type FROM Product
  5.  EXCEPT
  6.  SELECT type FROM Product Pr2 WHERE Pr2.maker = Pr1.maker
  7. ) X );

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:

Console
Execute
  1. SELECT DISTINCT maker
  2. FROM Product Pr1
  3. WHERE type = ALL
  4. (SELECT type FROM Product
  5.  EXCEPT
  6.  SELECT type FROM Product Pr2 WHERE Pr2.maker = Pr1.maker
  7. );

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.

Existence

There is no such type of product which is absent in the maker`s product list.

Console
Execute
  1. SELECT DISTINCT maker
  2. FROM Product Pr1
  3. WHERE NOT EXISTS (SELECT type
  4.                   FROM Product
  5.                   WHERE type NOT IN (SELECT type
  6.                                      FROM Product Pr2
  7.                                      WHERE Pr1.maker = Pr2.maker));

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

Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.