Авторы: Акжан Абдулин, Лиля Козленко, июнь 2000 года, версия 1.30.

Данная статья является частью разрабатываемой книги...

Информационные системы всё время взаимодействуют с внешним миром. Каждый раз, когда системе потребуется новая информация о каком-либо объекте реального мира, возможна ситуация, когда часть информации об объекте либо отсутствует, либо по отношению к данному объекту просто не имеет смысла. Например, отсутствует фотография в досье, неизвестна дата рождения, пока не определена дата выступления.

Все случаи отсутствия информации можно подразделить на следующие:

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

Объём оперативных данных, предъявляемых пользователю, обычно невелик. Однако для работы с такими данными обычно используется и набор справочников, объём которых часто на порядки больше объёма предъявляемых оперативных данных (к примеру, справочник предприятий области). Чтобы каким-либо образом упростить выбор из множества элементов, всегда используют сортировку и контекстный поиск, но часто этого недостаточно. Тогда все элементы справочника разбивают на общие категории, которые потом подразделяются на более частные и так далее, образуя древовидную структуру.

Подробно работа с иерархическими данными будет описана в соответствующей статье. Здесь же мы рассмотрим только используемые структуры данных и простейшие операции над ними. В примере используются параметризуемые запросы в нотации BDE 5.0 и выше (см. примечание 1 >>>).

Другим примером будет работа с агрегируемыми атрибутами. В данном случае агрегируемое поле представляет собой суммарную прибыль некоего предприятия. Причём некоторые предприятия могут быть бесприбыльными, а прибыль всегда является неотрицательной величиной.

Для реляционных СУБД Ф. Э. Коддом было предложен подход, в котором для представления отсутствующей информации используется специальный NULL-маркер. Например, атрибут "модель монитора" определяется, как ссылка на справочник моделей мониторов, при необходимости помеченная NULL-маркером. И для всех выше упомянутых случаев для данного атрибута будет установлен NULL-маркер.

Кроме того, помимо логических значений "Истина" (true) и "Ложь" (false), введено новое логическое значение - "Неизвестно" (unknown), так как результат сравнения неизвестной величины с любой другой величиной неизвестен. Таким образом, вместо использования булевой логики приходится использовать трёхзначную логику.

Ниже опишем значения предикатов для 2-х и 3-х значной логики.

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

где во второй колонке приведено значение предиката.

Следующие 3 таблицы определяют значения бинарных операций or, and, is.

Все предикаты, исключая следующие: EXISTS, NULL, UNIQUE, MATCH, в реляционной алгебре могут принимать значения TRUE, FALSE, UNKNOWN. Значение UNKNOWN возникает, когда NULL сравнивается с какими-либо другими значениями, в т.ч. и с другим NULL. Следующие таблицы описывают результаты логических операций.

где во второй колонке приведено значение предиката.

ледующие 3 таблицы определяют значения бинарных операций or, and, is.

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

Современные СУБД поддерживают 3-х значную логику. Обработка выражений идет так же согласно правилам 3-х значной логики (в данном случае выражение сперва обрабатывается оптимизатором согласно правилам 3VL (Rule-based оптимизатор), а после обработки выражения подключается стоимостной (cost-based) оптимизатор, если СУБД имеет таковой).

В современных СУБД приняты некоторые правила декларации атрибутов связанные с NULL-маркерами:

При проектировании базы данных всегда приходится учитывать возможность появления NULL в выборке.

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

С понятием NULL неразрывно связано и введение таких операций, как внешние соединения (outer joins). На практике внешние соединения оказались полезны при выполнении отчётов и в аналитических запросах. В современных визуальных инструментах по построению отчётов проще иметь дело со всегда присутствующими полями, помечаемыми NULL-маркерами при отсутствии данных, чем с полями из таблиц, ассоциированными с данной таблицей, соответствующие строки которых отсутствуют при отсутствии данных.

Следует отметить, что в большинстве реализаций NULL для числовых атрибутов хранится как правило как 0 со специальным маркером NULL, а для строк как пустая строка со специальным маркером NULL. Так значения атрибутов хранятся как в файлах данных так и в файлах индексов. Это означает что NULL не равен ни 0 для числовых атрибутов ни '' для строк. Это поведение операции IS регламентировано стандартом ANSI X3.135-1992. Тем не менее существуют реализации, где это не так. В частности для Oracle версий 7, 8 (а так же младших версий) для строковых атрибутов переменной длины '' и NULL эквивалентны. Следующий простой пример позволяет это увидеть -

Это же верно для типов char, varchar, varchar2, raw, long raw, long, date, rowid, blob, clob, MLSLABEL. Следует отметить, что Oracle обещает устранить данное несоответствие стандарту ANSI X3.135-1992 в следующих версиях системы и не рекомендует разработчикам использовать данную особенность при разработке приложений.

NULL не имеет никакого отношения к числам, символам и любым другим типам данных, поэтому некорректно говорить о том, что некий атрибут имеет значение NULL.

BDE для текстового атрибута, помеченного NULL-маркером, возвращает в качестве значения текст нулевой длины. Обратная операция - присваивание текста нулевой длины в качестве нового значения текстовому атрибуту, обычно не приводит к пометке данного атрибута NULL-маркером. Для того, чтобы всегда подразделять такие случаи, библиотека доступа к данным обычно предоставляет специальные методы. Например, класс TField библиотеки VCL предлагает метод Clear для пометки поля NULL-маркером, и свойство IsNULL для проверки на наличие такой пометки:

В своих новых работах Кодд предложил использовать не один, а два маркера - маркер неприменимости и маркер неизвестности, для отражения концептуальной разницы этих понятий. Соответственно им предлагается ввести в обиход четырёхзначную логику.

В стандарте ANSI/ISO SQL (см. примечание 2 >>>) данные идеи нашли отражение в том, что в стандарт были введены понятия NULL, логического значения unknown, и сопутствующих операций. В частности, проверку на наличие и отсутствие маркера NULL можно провести с помощью операций operand IS NULL и operand IS NOT NULL. Более поздние идеи Ф. Э. Кодда в стандарте ANSI/ISO SQL отражения не нашли.

Для атрибутов, входящих во множество атрибутов первичного ключа базового отношения, запрещается NULL. Тому есть вполне здравая причина: каждый экземпляр сущности должен быть уникально идентифицируемым. "В реляционной базе данных мы никогда не записываем информацию о чём-то, чего мы не можем идентифицировать" (см. примечание 3 >>>). В некоторых реализациях при декларировании PRIMARY KEY для набора атрибутов требуется и обязательная декларация NOT NULL для каждого из атрибутов, входящих в ключ.

Производные отношения (в SQL известны как Views - представления), в отличие от базовых отношений (в SQL известны как Tables - таблицы), вполне могут иметь строки с первичным ключом NULL. В качестве примера можно привести представление, которое показывает номера отделов, взятые из таблицы сотрудников предприятия, где часть сотрудников не приписана ни к одному отделу, с удалением дубликатов. В результирующем наборе данных в единственном столбце будет присутствовать одна строка с NULL.

При декларации внешних ключей так же допустимы NULL. Соответственно расширены правила ссылочной целостности. Если при определении внешнего ключа допускаются NULL (хотя бы для одного атрибута, входящего в ключ отсутствует декларация NOT NULL), то такой ключ должен либо ссылаться на реально существующую запись в целевой таблице, либо быть NULL, что означает, что данная ссылка отсутствует. Расширены правила декларативной ссылочной целостности, которые позволяют для таких случаев, как потеря записи владельца, устанавливать для внешнего ключа NULL - это декларация ссылочной целостности SET NULL.

В качестве универсального правила можно принять такое, - если данный атрибут всегда имеет какое-либо значение, всегда определяйте его как NOT NULL. Если при декларации ссылочной целостности у вас присутствует декларация вида NOT NULL REFERENCES … SET NULL, то не во всех реализациях данное предложение будет расцениваться как содержащее противоречие, такие реализации пропускают эту декларацию, а противоречивость ее будет выявлена только по факту работы декларативной ссылочной целостности. А потому данные ошибки проектирования придется отслеживать в ручную при использовании серверов с подобными реализациями диалектов sql. Аналогично при определении триггеров попытка модифицировать поле на NULL для атрибута, декларированного как NOT NULL, так же будет выявлена не при определении триггера, а по факту его работы. Все это относится к ошибкам проектирования, но большинство серверов их пропускают при создании объектов данных, а детектируются такие ошибки только когда при работе будет нарушено ограничение NOT NULL.

При получении выборки по факту выполнения Select пользовательское приложение получает вместе с кортежем (собственно значения, попавшие в выборку) также битовый вектор (может быть и последовательность байт), указывающий на наличие NULL - 0 детектирует отсутствие NULL, 1 - его наличие. Соответственно если при получение кортежа K пользовательское приложение так же получает битвектор вида (1,0,1,1) указывает с 4 полями - это указывает на наличие NULL для 1,3 и 4-го полей.

Физически в файле данных NULL отмечаются например так: если в записи значения нескольких атрибутов не определены (NULL), то в заголовке записи метится, что данный атрибут NULL, никаких других пометок (вроде глобального битового массива или чего-то еще не делается). В индексах (в деревьях) все NULL хранятся или справа или слева, в зависимости от того, дерево растет вправо или влево. Все NULL как правило считаются самыми большими в дереве, так их легче обрабатывать и исключать из выборки, когда это требуется. В bitmap-индексах для NULL отводится специальный битвектор, как и для любого другого значения. Сколько различных значений атрибута, столько и будет битвекторов плюс битвектор для NULL.

Древовидная структура с использованием NULL-маркеров представляется таким образом:

Корневые элементы справочника помечают поле "владелец" NULL-маркером:

Выборка элементов, подчинённых данному элементу (заданному параметрами OwnerId1 и OwnerId2), в общем случае производится так:

Следует отметить, что для СУБД, имеющих специальные механизмы обработки иерархий (Oracle, DB2 UDB, Linter SQL Server) атрибут, указывающий предка (ParentDictItemId), корневых элементов должен быть NULL. При выборках ссылки сами на себя не допустимы, это будет расценено как петля. Это как раз тот случай, когда NULL очень полезен. Подробнее обработка иерархий этими СУБД будет рассмотрена в соответствующей статье.

Здесь и далее по тексту агрегируемые данные - это данные, по которым выполняются агрегатные (статистические) операции, например, подсчёта количества уникальных значений, суммирования и нахождения среднего.

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

Единственная агрегатная функция, которая всегда учитывает NULL - это COUNT(*). NULL для этой агрегатной функции воспринимается как специальное значение и никогда не игнорируется. Для остальных агрегатных функций результатом будет следующее:

Пусть таблица T определена так.

Тогда в результате выполнения запросов получим

Такое поведение регламентируется стандартом ANSI X3.135-1992.

Здесь мы опишем простой пример работы с агрегируемым полем Income:

Последняя выборка вернёт среднее значение прибыли для двух предприятий, равное 60000.00 (три строки, но одну не считаем (поведение агрегатных функций по стандарту ANSI X3.135-1992), так как NULL для атрибута Income в данной предметной области означает, что предприятие бесприбыльное).

Есть также некоторые общие методики при работе с неопределённой информацией, которые позволяют уйти от использования NULL. Фактически осуществляется подмена NULL неким специальным значением.

Первая из этих методик заключается в использовании атрибутов с предопределённым возможным значением, которое означает отсутствие информации. Часто это специальное значение также является и значением по умолчанию, поддержка которого (DEFAULT) также введена в стандарт SQL. Внешние ключи могут быть объявлены на наборе атрибутов, для которых определены значения по умолчанию. Внешний ключ всегда должен ссылаться на реально существующую запись в целевой таблице, пусть даже он и имеет значение по умолчанию. Расширены правила декларативной ссылочной целостности, которые позволяют для таких случаев, как потеря записи владельца, устанавливать в качестве целевого значение по умолчанию (правило SET DEFAULT).

Наиболее часто это методика используется для символьных атрибутов, где, например, строка нулевой длины или строка, забитая пробелами, означает отсутствие информации. Для неотрицательных атрибутов таким предопределённым значением может стать любая отрицательная величина. Данную методику рекомендуется применять только для несчётных атрибутов (значения которых не используются при выполнении различных агрегатных операций типа суммирования), для случаев пока не известных значений. Следует отметить отклонения от стандарта некоторых СУБД, в частности данную методику не рекомендуется применять для Oracle 7, 8 (и младших версий), так как для типов char, varchar, varchar2, raw, long raw, long, date, rowid, blob, clob, MLSLABEL значение '' и NULL эквивалентны. В документации Oracle для версий 8 не рекомендуется пользоваться данной особенностью Oracle, так как в следующих версиях планируется привести обработку NULL в полное соответствие стандарту ANSI X3.135-1992.

К сожалению, при операциях над такими атрибутами также явно приходится учитывать факт возможного появления специального значения в выборке. К тому же в языке SQL нет однозначного декларативного способа описания поведения таких атрибутов (есть декларативный способ описания значений по умолчанию, но последние могут использоваться не только в качестве специальных значений), что приводит к тому, что база данных плохо документирует свою структуру. Приходится вчитываться в сопутствующую документацию и исходные тексты триггеров и выборок.

Древовидная структура с использованием специальных значений представляется таким образом:

Корневые элементы справочника заполняют поле "владелец" значением своего первичного ключа (создавая "петли"):

Выборка элементов, подчинённых данному элементу (заданному параметрами OwnerId1 и OwnerId2), в общем случае производится так:

Здесь представлен переработанный пример. Как мы видим, выполнять агрегатные операции над данными, представленными с использованием специальных значений, становится очень хлопотно.

Последняя выборка вернёт среднее значение прибыли для двух предприятий, равное 60000.00 (три строки, но одну явно не считаем по условию WHERE, так как атрибут Income у неё равен специальному значению, а это в данной предметной области означает, что предприятие бесприбыльное).

Многие сущности концептуальной модели представляют собой проекцию объектов реального мира. Но все объекты имеют свойства. Причём свойства, по сути, могут быть обязательными или необязательными, скалярными или векторными, простыми или составными. Классическое представление любых скалярных свойств в реляционной модели данных - в виде набора атрибутов, а любых векторных свойств, по правилам нормализации, - в виде дочерней таблицы. Данная методика предлагает выделять в дочерние таблицы также и необязательные скалярные свойства.

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

Методика выделения свойств более универсальна, чем прочие, равно как и более эффективна. При этом данная сущность является владельцем, а сущность, которая определяет данный необязательный атрибут - свойством владельца. Наиболее удобно в терминах IDEF1X/IE эта операция выражается через отношение подкатегории (has a). Например -

Если мы не знаем номер и серию паспорта для данного экземпляра "Человек", то у нас не будет определён соответствующий экземпляр сущности "Человек с известными паспортными данными". При этом выборка людей, для которых, например, известна дата рождения, будет выражена через недорогую операцию соединения.

Хотелось бы обратить Ваше внимание, что на вышеприведённой диаграмме Человек может являться одновременно и человеком с известными паспортными данными, и человеком с известной датой рождения, но не может быть одновременно мужчиной и женщиной (такие ограничения накладывает тот факт, что дискриминатор для Мужчины и Женщины один и тот же). В нотации UML такая связь между объектом и его типом называется множественной классификацией (см. примечание 4 >>>).

Во многих случаях использование данной методики более эффективно, и всегда требует меньших затрат при последующих модернизациях системы. В процессе эксплуатации, например, может возникнуть такая ситуация, что некое свойство у объекта может оказаться не скалярным, а векторным (мы закрепили за сущностью "Кабинет" свойство "Телефон", но позже в кабинеты стали ставить несколько телефонов). Другой пример - юридическим лицам в России несколько лет назад разрешили иметь несколько рублевых счетов и несколько счетов в различных валютах других государств. Был момент, когда ни одна из бухгалтерских программ еще не умела такие вещи поддерживать.

Агрегатные операции над такими объектами становятся более эффективными. Другим достоинством такой методики является интуитивная простота её восприятия в схеме данных, чего не может предложить схема с использованием специальных значений.

В тех случаях, когда это необходимо, с помощью операции внешнего соединения мы можем составить производное отношение (View), которое будет проецировать данные в виде одной таблицы с использованием NULL-маркеров. Следует отметить, что при оптимизации такого запроса view не обязательно будет обрабатываться отдельно, оптимизатор будет рассматривать запрос с "подставленным" текстом view и оптимизировать запрос целиком.

Древовидная структура с использованием методики выделения свойств может представляться, к примеру, таким образом:

Корневые элементы справочника принадлежат типу "RootDictionaryItem" (и не принадлежат типу "SubDictionaryItem"):

Выборка элементов, подчинённых данному элементу (заданному параметром OwnerId), в общем случае производится так:

Можно, как описано выше, с помощью операции внешнего соединения составить производное отношение (View), которое будет проецировать данные в виде одной таблицы с использованием NULL-маркеров:

Внимательный читатель, верно, уже подметил, что записи в таблицах "RootDictionaryItem" и "SubDictionaryItem" можно рассматривать как экземпляры двух независимых классов объектов, порождённых от класса "DictionaryItem". Таким образом, все экземпляры сущностей можно представить в качестве объектов, являющимися потомками некоего общего класса объектов. В качестве примера в одной из статей будет рассмотрена единая документная модель.

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

Прежде всего, появилась новая сущность. EnterpriseWithIncome является свойством предприятия, получающего прибыль. Концептуально лучше читать эту запись иначе - EnterpriseWithIncome является подтипом супертипа Enterprise (атрибуты, свойственные всем предприятиям, можно найти в таблице Enterprise, но атрибуты, свойственные только предприятиям, получающим прибыль, можно найти лишь в таблице EnterpriseWithIncome), т.е. определяет множество предприятий, получающих прибыль:

Последняя выборка вернёт среднее значение прибыли для двух предприятий в текущем финансовом году, равное 60000.00 (две строки, так как для бесприбыльных предприятий записей EnterpriseWithIncome не существует).

На этом мы завершаем обзор основных подходов к представлению отсутствующей информации. В этой статье мы рассмотрели три основных подхода: использование NULL, использование специальных значений и использование концепции необязательных свойств.

Рассмотрен подход с использованием NULL, кратко описаны его особенности. Внимательный читатель уже заметил, насколько неоправданно усложнились правила исчисления логических выражений, агрегатных функций и реляционных операций с введением NULL;

Было указано на негативные последствия применения NULL в базовых отношениях при проектировании концептуальной модели базы данных;

В тоже время там же рекомендуется использовать NULL для представления отсутствующей информации в производных отношениях, при выполнении отчётов и аналитических выборок;

Подвергнут критике альтернативный подход с использованием специальных значений, как в большинстве случаев плохо документируемый, а также предлагающий ограниченное решение;

Рассмотрен подход, основанный на некоторых идеях модели объект/отношение. Указано на его мощность, эффективность и однозначную сводимость на уровне представлений к подходу с использованием NULL посредством внешних соединений.

Copyright 1999-2000 by Akzhan Abdulin, Lilya Kozlenko. При публикации просьба указывать источник и авторство. Для получения последних исправлений заглядывайте изредка на уголок разработчика.

Комментарии, исправления, замечания и пожелания приветствуются по адресу: akzhan@beep.ru.