Реляционное деление

Рассмотрим следующую задачу.

Ключевым словом здесь является “всех”, т.е. производитель в таблице Product должен иметь модели каждого типа, т.е. и PC, и Laptop, и Printer.

Как раз для решения подобных задач в реляционную алгебру Коддом была введена специальная операция реляционного деления (DIVIDE BY).

С помощью этой операции наша задача решается очень просто [1]:

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

Здесь квадратными скобками обозначается операция взятия проекции на соответствующие атрибуты.

Операция реляционного деления избыточна, т.е. она может быть выражена через другие операции реляционной алгебры. Возможно, поэтому ее нет в языке SQL.

На примере решения сформулированной задачи я хочу показать несколько приемов реализации операции реляционного деления на языке SQL.

Группировка

Если использовать тот факт, что, согласно описанию предметной области, типов продукции всего три, то мы можем выполнить группировку по производителю и подсчитать количество уникальных типов. Затем мы отберем только тех производителей, у которых это число равно трем.

Итак,

SELECT maker
FROM Product
GROUP BY maker
HAVING COUNT(distinct type) = 3;

Однако если число типов продукции произвольно, то такое решение будет правильным только при нынешнем состоянии базы данных, а не при любом возможном. А это значит, что мы должны константу заменить “переменной”, значением которой будет текущее число типов, т.е. подзапросом:

SELECT maker  
FROM Product  
GROUP BY maker  
HAVING COUNT(DISTINCT type) =   
        (SELECT COUNT(DISTINCT type) FROM Product);

Разность

Если взять операцию разности ВСЕХ имеющихся типов моделей и типов у конкретного производителя, то результирующая выборка не должна содержать строк.

SELECT DISTINCT maker
FROM Product Pr1
WHERE 0 = (SELECT COUNT(*) 
           FROM (SELECT type 
                 FROM Product
                 EXCEPT
                 SELECT type 
                 FROM Product Pr2
                 WHERE Pr2.maker = Pr1.maker
                 ) X 
           );

Этот запрос можно написать короче, если воспользоваться тем свойством, что истинностное значение предиката ALL есть TRUE, если подзапрос не возвращает строк:

SELECT DISTINCT maker
FROM Product Pr1
WHERE type = ALL (SELECT type 
                  FROM Product
                  EXCEPT
                  SELECT type 
                  FROM Product Pr2
                  WHERE Pr2.maker = Pr1.maker
                  );

Для искомых производителей список типов в предикате ALL будет пуст (предикат равен TRUE). В остальных случаях он будет содержать типы моделей, отсутствующие у производителя из внешнего запроса, поэтому операция сравнения (равенство “=”) для всех его моделей даст FALSE.

Существование

Не должно существовать такого типа продукции, которого бы не было у искомого производителя.

SELECT DISTINCT maker
FROM Product Pr1
WHERE NOT EXISTS (SELECT type
                 FROM Product
                 WHERE type NOT IN (SELECT type
                                    FROM Product Pr2
                                    WHERE Pr1.maker = Pr2.maker
                                    )
                );

Кроме первого варианта с группировкой все остальные решения используют коррелирующие подзапросы для определения множества типов моделей производителя из основного запроса.

Следует также отметить, что решение с группировкой не подойдет для случая, когда требуется выполнить деление не на все множество имеющихся типов, а на некоторое их подмножество. Например, если требуется найти производителей, у которых множество типов включает в себя (или совпадает) множество типов, определяемое некоторыми критериями. Другие же приемы можно адаптировать для решения подобной задачи.

Рекомендуемые упражнения: 71