Поддержка авторизации доступа к данным в языке SQL
В общем случае база данных является слишком дорогостоящим предметом, чтобы можно было использовать ее в автономном режиме. Обычно с достаточно большой базой данных (параллельно или последовательно) работает много приложений и пользователей, и не для всех них было бы разумно обеспечивать равноправный доступ к хранящимся данным.
В языке SQL (SQL:1999) предусмотрены возможности контроля доступа к разным объектам базы данных, в том числе к следующим объектам: таблицам;столбцам таблиц;представлениям;доменам;наборам символов;порядкам сортировки символов (collation);преобразованиям (translation);триггерам;подпрограммам, вызываемым из SQL;определенным пользователями типам.
В совокупности в SQL:1999 может поддерживаться девять видов защиты разных объектов в соответствии со следующими возможными действиями (см. ).
При разработке средств контроля доступа к объектам баз данных создатели SQL придерживались принципа сокрытия информации об объектах, содержащихся в схеме базы данных, от пользователей, которые лишены доступа к этим объектам. Другими словами, если некоторый пользователь не обладает, например, привилегией на просмотр таблицы PRO, то при выполнении операции SELECT * FROM PRO он получит такое же диагностическое сообщение, как если бы таблица PRO не существовала. Если бы в случае отсутствия этой таблицы и в случае отсутствия привилегии доступа выдавались разные диагностические сообщения, то непривилегированный пользователь получил бы данные о том, что интересующая его таблица существует, но он лишен доступа к ней.
| Просмотр | SELECT | Таблицы, столбцы, подпрограммы, вызываемые из SQL |
| Вставка | INSERT | Таблицы, столбцы |
| Модификация | UPDATE | Таблицы, столбцы |
| Удаление | DELETE | Таблицы |
| Ссылка | REFERENCES | Таблицы, столбцы |
| Использование | USAGE | Домены, определенные пользователями типы, наборы символов, порядки сортировки символов, преобразования |
| Инициирование | TRIGGER | Таблицы |
| Выполнение | EXECUTE | Подпрограммы, вызываемые из SQL |
| Подтипизация | UNDER | Структурные типы |
В лекции 16 мы бегло упоминали, что в SQL-ориентированной системе каждому зарегистрированному в системе пользователю соответствует его уникальный идентификатор (в стандарте используется термин идентификатор авторизации, authorization identifier – authID). Как мы отмечали, в стандарте SQL:1999 не зафиксированы точные правила представления идентификатора пользователя, хотя обычно в реализациях SQL ниладическая функция CURRENT USER выдает текстовую строку, содержащую регистрационное имя пользователя, как оно сохраняется в файлах соответствующей операционной системы (ОС). Привилегии доступа к объектам базы данных могут предоставляться пользователям, представляемым своими идентификаторами, а также ролям (см. следующий подраздел), выполнение которых, в свою очередь, может предоставляться пользователям. Кроме того, в SQL поддерживается концепция псевдоидентификатора (или идентификатора псевдо) пользователя PUBLIC, который соответствует любому приложению или пользователю, зарегистрированному в системе баз данных. «Пользователю» PUBLIC могут предоставляться привилегии доступа к объектам базы данных, как и любому другому пользователю.
В модели контроля доступа SQL создатель любого объекта базы данных автоматически становится владельцем этого объекта. При этом владелец объекта может идентифицироваться либо своим идентификатором пользователя, либо именем своей роли. Вообще говоря, владелец объекта обладает полным набором привилегий для выполнения действий над объектом (с одним исключением, которое мы обсудим в данном разделе позже). Владелец объекта, помимо прочего, обладает привилегией на передачу всех (или части) своих привилегий другим пользователям или ролям. В частности, владелец объекта может передать другим пользователям или ролям привилегию на передачу привилегий последующим пользователям или ролям (эти действия с передачей привилегии на передачу привилегий могут продолжаться рекурсивно).
Во многих реализациях поддерживаются привилегии уровня DBA (DataBase Administrator) для возможности выполнения операций DDL – Data Definition Language (CREATE, ALTER и DROP над объектами, входящими в схему базы данных).В стандарте SQL требуется лишь соблюдение следующих правил. Любые пользователь или его роль могут выполнять любые операции DDL внутри схемы, которой владеют.Не допускается выполнение каких-либо операций DDL внутри схемы, которой не владеет пользователь или роль, пытающиеся выполнить соответствующую операцию.Эти правила не допускают исключений.
Поддержка согласованности ссылок
Никакое ссылочное значение никогда не идентифицирует какую-либо строку, кроме той, с которой оно было ассоциировано с самого начала. Если эта строка удаляется, то значение ничего не идентифицирует и никогда не может быть связано с другой строкой. Из этого следует, что система должна каким-либо образом узнавать о том, идентифицирует ли данное ссылочное значение какую-то хранимую строку или ничего не идентифицирует (является висящей ссылкой). Но как система может это узнать, не потратив множество ресурсов? Отчасти здесь может помочь раздел SCOPE. В этом разделе указывается одна таблица, в которой строки должны существовать для всех значений данного местоположения, типом данных которого является некоторый REF-тип. (В будущих версиях стандарта SQL, по всей видимости, будет разрешено указывать в разделе SCOPE список имен типизированных таблиц или даже использовать некоторую конструкцию, означающую «все таблицы, ассоциированные с данным структурным типом».)
Итак, если определяется столбец таблицы, поле строчного типа или атрибут структурного типа, и типом этого местоположения является REF-тип, то можно специфицировать раздел SCOPE. Однако если такой раздел действительно указывается, то требуется также указать, нужна ли проверка ссылочных значений. Для этого служит конструкция reference_scope_check, определяемая следующим синтаксическим правилом:
reference_scope_check ::= REFERENCES ARE [ NOT ] CHECKED [ ON DELETE referential_action ]
Если указывается REFERENCES ARE NOT CHECKED или если раздел SCOPE не задается, то в определяемом местоположении можно хранить любое ссылочное значение, независимо от того, является ли оно значением самоссылающегося столбца какой-либо таблицы, на строку которой предположительно указывает ссылка. В этом случае система не гарантирует, что ссылочное значение действительно указывает на строку (но, конечно, это значение должно быть значением правильного типа – REF-типа указанного структурного типа).
Если же указывается REFERENCES ARE CHECKED, то каждый раз при сохранении значения в определяемом столбце, поле или атрибуте система обращается к указанной в разделе SCOPE таблице, чтобы убедиться в том, что в ней имеется строка, значение самоссылающегося столбца которой совпадает с сохраняемым ссылочным значением. Кроме того, если указывается REFERENCES ARE CHECKED, то можно также указать ссылочное действие, которое должно выполняться при удалении строки, идентифицируемой ссылочным значением. Как обычно (см. лекцию 16), возможными ссылочными действиями являются RESTRICT, CASCADE, SET NULL и NO ACTION. Если ссылочное действие явно не указывается, по умолчанию принимается NO ACTION. (Для поля строчного типа (ROW TYPE) и атрибута структурного типа допускается только NO ACTION.)
Заметим, что если раздел SCOPE включается в определение атрибута структурного типа, то в конструкции column_options столбца типизированной таблицы, соответствующего данному атрибуту, раздел SCOPE присутствовать не может – это считается синтаксической ошибкой.
Поддержка ссылочной целостности и ссылочные действия
В связи с определением ограничения внешнего ключа нам осталось рассмотреть еще два необязательных раздела – ON DELETE referential_action и ON UPDATE referential_action. Прежде всего, приведем синтаксическое правило:
referential_action ::= { NO ACTION | RESTRICT | CASCADE | SET DEFAULT | SET NULL }
Чтобы объяснить, в каких случаях и каким образом выполняются эти действия, требуется сначала определить понятие ссылающейся строки (referencing row). Если в определении ограничения внешнего ключа отсутствует раздел MATCH или присутствуют спецификации MATCH SIMPLE либо MATCH FULL, то для данной строки t таблицы T строкой таблицы S, ссылающейся на строку t, называется каждая строка таблицы S, значение внешнего ключа которой совпадает со значением соответствующего возможного ключа строки t. Если в определении ограничения внешнего ключа присутствует спецификация MATCH PARTIAL, то для данной строки t таблицы T строкой таблицы S, ссылающейся на строку t, называется каждая строка таблицы S, отличные от NULL значения столбцов внешнего ключа которой совпадают со значениями соответствующих столбцов соответствующего возможного ключа строки t. В случае MATCH PARTIAL строка таблицы S называется ссылающейся исключительно на строку t таблицы T, если эта строка таблицы S является ссылающейся на строку t и не является ссылающейся на какую-либо другую строку таблицы T.
Теперь приступим к ссылочным действиям. Пусть определение ограничения внешнего ключа содержит раздел ON DELETE referential_action. Предположим, что предпринимается попытка удалить строку t из таблицы T. Тогда: если в качестве требуемого ссылочного действия указано NO ACTION или RESTRICT, то операция удаления отвергается, если ее выполнение вызвало бы нарушение ограничения внешнего ключа;если в качестве требуемого ссылочного действия указано CASCADE, то строка t удаляется, и если в определении ограничения внешнего ключа отсутствует раздел MATCH или присутствуют спецификации MATCH SIMPLE или MATCH FULL, то удаляются все строки, ссылающиеся на t.
Если же в определении ограничения внешнего ключа присутствует спецификация MATCH PARTIAL, то удаляются только те строки, которые ссылаются исключительно на строку t;если в качестве требуемого ссылочного действия указано SET DEFAULT, то строка t удаляется, и во всех столбцах, которые входят в состав внешнего ключа, всех строк, ссылающихся на строку t, проставляется заданное при их определении значение по умолчанию. Если в определении внешнего ключа содержится спецификация MATCH PARTIAL, то подобному воздействию подвергаются только те строки таблицы S, которые ссылаются исключительно на строку t;если в качестве требуемого ссылочного действия указано SET NULL, то строка t удаляется, и во всех столбцах, которые входят в состав внешнего ключа, всех строк, ссылающихся на строку t, проставляется NULL. Если в определении внешнего ключа содержится спецификация MATCH PARTIAL, то подобному воздействию подвергаются только те строки таблицы S, которые ссылаются исключительно на строку t.
Пусть определение ограничения внешнего ключа содержит раздел ON UPDATE referential_action. Предположим, что предпринимается попытка обновить столбцы соответствующего возможного ключа в строке t из таблицы T. Тогда: если в качестве требуемого ссылочного действия указано NO ACTION или RESTRICT, то операция обновления отвергается, если ее выполнение вызвало бы нарушение ограничения внешнего ключа;если в качестве требуемого ссылочного действия указано СASCADE, то строка t обновляется, и если в определении ограничения внешнего ключа отсутствует раздел MATCH или присутствуют спецификации MATCH SIMPLE или MATCH FULL, то соответствующим образом обновляются все строки, ссылающиеся на t (в них должным образом изменяются значения столбцов, входящих в состав внешнего ключа). Если же в определении ограничения внешнего ключа присутствует спецификация MATCH PARTIAL, то обновляются только те строки, которые ссылаются исключительно на строку t;если в качестве требуемого ссылочного действия указано SET DEFAULT, то строка t обновляется, и во всех столбцах, которые входят в состав внешнего ключа и соответствуют изменяемым столбцам таблицы T, всех строк, ссылающихся на строку t, проставляется заданное при их определении значение по умолчанию.
Если в определении внешнего ключа содержится спецификация MATCH PARTIAL, то подобному воздействию подвергаются только те строки таблицы S, которые ссылаются исключительно на строку t, причем в них изменяются значения только тех столбцов, которые не содержали NULL;если в качестве требуемого ссылочного действия указано SET NULL, то строка t обновляется, и во всех столбцах, которые входят в состав внешнего ключа и соответствуют изменяемым столбцам таблицы T, всех строк, ссылающихся на строку t, проставляется NULL. Если в определении внешнего ключа содержится спецификация MATCH PARTIAL, то подобному воздействию подвергаются только те строки таблицы S, которые ссылаются исключительно на строку t.
С учетом замечания по поводу особого толкования семантики неопределенных значений, сделанного в предыдущей сноске.
Если определяется внешний ключ, состоящий из одного столбца, то явное указание спецификации MATCH любой разновидности становится бессмысленным, поскольку в этом случае MATCH SIMPLE, MATCH PARTIAL и MATCH FULL ведут себя одинаково.
Из приведенных ранее объяснений действия ограничения внешнего ключа при наличии в определении внешнего ключа раздела MATCH PARTIAL ясно следует, что в этом случае одна строка таблицы S может являться ссылающейся на несколько разных строк таблицы T.
Как можно видеть из приведенных объяснений, ссылочные действия служат тому, чтобы автоматически поддерживать ссылочную целостность при обновлениях таблиц, к строкам которых ведут ссылки. Довольно часто ссылочные действия, являющиеся частью определения внешнего ключа, называют декларативными триггерами.
Подключения и сессии
Может показаться странным, что мы оставили на конец этой лекции материал, который, казалось бы, необходимо знать, чтобы иметь возможность приступить к работе с какой-либо из современных систем баз данных. Объяснение очень простое. Чем ниже уровень средств языка SQL, чем ближе эти средства соприкасаются с индивидуальными особенностями реализаций, тем менее точен и конкретен стандарт SQL. А в данном разделе речь идет о средствах, реализация которых в СУБД разных поставщиков обладает очень большой спецификой.
Сильно упрощая текущую ситуацию, можно сказать, что практически все современные продукты управления SQL-ориентированными базами данных основаны на архитектуре «клиент-сервер». Принципиальная схема клиент-серверной организации показана на .
Рис. 22.8. Клиент-серверная архитектура СУБД
Конечно, это рисунок весьма условен. Под термином пользователь здесь, конечно, понимается некоторое приложение, с которым реально работает конечный пользователь (например, в этом приложении может быть реализован монитор прямого SQL). Клиентская часть СУБД – это тот системный компонент, с которым непосредственно взаимодействует пользователь. Данный компонент скрывает специфику реальных взаимодействий с серверной частью СУБД (например, используемые сетевые протоколы, если клиентская и серверная части СУБД разнесены по разным компьютерам сети). Наконец, сервер баз данных представляет собой основную часть СУБД, где, собственно, и происходит выполнение операторов SQL и осуществляется доступ к базе данных.
Важно обратить внимание, что программные компоненты, представляющие пользователя и клиентскую часть СУБД, обычно выполняются на одном компьютере, а сервер баз данных работает на другом (серверном) компьютере. Но вполне может быть, что все три перечисленных программных компонента в действительности размещены на одном компьютере.
Подтаблицы и супертаблицы
Далее, при определении типизированной таблицы можно объявить ее подтаблицей некоторой другой типизированной таблицы (имя супертаблицы указывается в разделе UNDER). Таблица R' является собственной подтаблицей супертаблицы R, если R' не совпадает с R (в этом случае таблица R является собственной супертаблицей подтаблицы R'). Супертаблица должна быть ассоциирована со структурным типом, являющимся непосредственным супертипом определяемой подтаблицы. Каждый столбец указанной супертаблицы наследуется подтаблицей; наследуются и характеристики столбцов супертаблицы – значения по умолчанию, ограничения целостности и т. д. Эти столбцы называются унаследованными столбцами подтаблицы, и они соответствуют атрибутам UDT подтаблицы, унаследованным от UDT супертаблицы. Кроме того, подтаблица будет содержать по одному столбцу для каждого собственного атрибута ассоциированного структурного типа. Такие столбцы подтаблицы называются заново определенными.
Как это принято в SQL, столбцы типизированной таблицы имеют порядковые номера. При этом унаследованные столбцы нумеруются до заново определенных столбцов и имеют те же номера, которые имели столбцы супертаблицы.
Полнота Алгебры A
Покажем, что Алгебра A является полной, т. е. на основе введенных операций выражаются все операции алгебры Кодда, рассмотренной в предыдущей лекции.
К настоящему моменту в состав базовых операций Алгебры A входят операция <REMOVE> в качестве аналога операции PROJECT, а также операция переименования атрибутов <RENAME>. UNION является частным случаем операции <OR>, TIMES, INTERSECT и NATURAL JOIN – частные случаи операции <AND>. Нам осталось показать, что через операции Алгебры A выражаются операции взятия разности MINUS, ограничения (WHERE), соединения общего вида (JOIN) и реляционного деления (DIVIDE BY).
Получение реляционной схемы из ER-диаграммы
Опишем типовую многошаговую процедуру преобразования ER-диаграммы в реляционную (более точно, в SQL-ориентированную) схему базы данных.
Получение схемы реляционной базы данных из диаграммы классов UML
Если не обращать внимания на различия в терминологии, то здесь выполняются практически те же шаги, что и в случае преобразования в схему реляционной БД ER-диаграммы. Поэтому ограничимся только некоторыми рекомендациями, специфичными для диаграмм классов.
Рекомендация 1. Прежде чем определять в классах операции, подумайте, что вы будете делать с этими определениями в среде целевой РСУБД. Если в этой среде поддерживаются хранимые процедуры, то, возможно, некоторые операции могут быть реализованы именно с помощью такого механизма. Но если в среде РСУБД поддерживается механизм определяемых пользователями функций, возможно, он окажется более подходящим.
Рекомендация 2. Помните, что сравнительно эффективно в РСУБД реализуются только ассоциации видов «один ко многим» и «многие ко многим». Если в созданной диаграмме классов имеются ассоциации «один к одному», следует задуматься о целесообразности такого проектного решения. Реализация в среде РСУБД ассоциаций с точно заданными кратностями ролей возможна, но требует определения дополнительных триггеров, выполнение которых понизит эффективность.
Рекомендация 3. Для технологии реляционных БД агрегатные и в особенности композитные ассоциации неестественны. Подумайте о том, что вы хотите получить в реляционной БД, объявив некоторую ассоциацию агрегатной. Скорее всего, ничего.
Рекомендация 4. В спецификации UML говорится о том, что, определяя однонаправленные связи, вы можете способствовать эффективности доступа к некоторым объектам. Для технологии реляционных баз данных поддержка такого объявления вызовет дополнительные накладные расходы и тем самым снизит эффективность.
Рекомендация 5. Не злоупотребляйте возможностями OCL.
Диаграммы классов UML – это мощный инструмент для создания концептуальных схем баз данных, но, как известно, все хорошо в меру.
Хотя язык OCL формально считается частью UML, он специфицирован в отдельном документе, в котором присутствуют ссылки на другие части спецификации UML, а также вводятся собственные понятия и определения.
Следует заметить, что ни в спецификации UML, ни в описании какой- либо другой объектной модели никогда прямо не говорится, что в операциях над множествами объектов в действительности участвуют идентификаторы объектов. Но другого понимания не существует.
Обратите внимание, что хотя, в UML допускаются n-арные связи, в OCL речь идет только об уже привычном для нас бинарном варианте.
В контексте проектирования реляционных БД (если не иметь в виду использование объектно-реляционных СУБД) последняя разновидность типа коллекции является бессмысленной, поскольку в реляционных БД упорядоченность не поддерживается. Поэтому мы не будем обсуждать детали операций над последовательностями.
Если снова не иметь в виду использование объектно-реляционных СУБД.
Для коллекций значений возможно также применение операций min, max и avg, выдающих минимальное, максимальное и среднее значение элементов коллекции соответственно.
Очевидным аналогом класса является тип сущности, а аналогом связи-ассоциации — связь в смысле ER-модели. Кстати, различия и беспорядок в терминологии действительно удручают. В ER-модели связь (relationship) — это ассоциация (association) между двумя типами сущности. В UML ассоциация (association) — это один из видов связи (relationship). Да еще зачем-то в UML введен специальный термин link для обозначения экземпляра ассоциации. И снова хотелось бы использовать в качестве русского эквивалента термин связь, но он уже безнадежно занят, и приходится переводить link как соединение. Это, конечно, не противоречит смыслу, но тоже очень плохо, поскольку в области реляционных БД термин соединение и без этого имеет два разных смысла – операции соединения и соединения с сервером баз данных. Мне очень жаль переводчиков книг, посвященных UML.
Пользователи и роли
Как говорилось в начале этого раздела, любой пользователь характеризуется своим идентификатором авторизации (authID). В стандарте ничего не говорится о том, что authID должен быть идентичен регистрационному имени пользователя в смысле операционной системы. Согласно стандарту SQL:1999, authID строится по тем же правилам, что и любой другой идентификатор, и может включать до 128 символов. Тем не менее во многих реализациях SQL, выполненных в среде ОС семейства UNIX, длина authID составляет не более восьми символов, как это свойственно ограничениям на длину регистрационного имени в этих ОС.
В стандарте языка SQL не специфицированы средства создания идентификаторов авторизации. Если говорить более точно, в стандарте не определяется какой-либо явный способ создания допустимых идентификаторов пользователей. Идентификатор авторизации может являться либо идентификатором пользователя, либо именем роли, а для создания ролей в SQL поддерживаются соответствующие средства (см. ниже). Но в соответствии с правилами стандарта SQL, все authID должны отслеживаться СУБД (имеются в виду все authID, для которых существует хотя бы одна привилегия). И в стандарте поддерживаются точные правила порождения и распространения привилегий. Привилегии по отношению к объекту базы данных предоставляются системой владельцу схемы при создании объекта в этой схеме, и привилегии могут явно передаваться от имени одного authID другому authID при наличии у первого authID привилегии на передачу привилегий.
Итак, authID может являться либо идентификатором пользователя, либо идентификатором роли. Попробуем разобраться в сути термина роль. При работе с большими базами данных в крупных организациях часто сотни служащих производят над базой данных одни и те же операции. Конечно, для этого каждый из служащих должен быть зарегистрированным пользователем соответствующей системы баз данных и тем самым, обладать собственным authID. Используя базовые средства авторизации доступа (зафиксированные в стандарте SQL/92), можно предоставить каждому пользователю группы одни и те же привилегии доступа к требуемым объектам базы данных.
Но схема авторизации доступа при этом становится очень сложной. В некотором смысле имя роли идентифицирует динамически образуемую группу пользователей системы баз данных, каждый из которых обладает, во-первых, привилегией на исполнение данной роли и, во-вторых, всеми привилегиями данной роли для доступа к объектам базы данных. Другими словами, наличие ролей упрощает построение и администрирование системы авторизации доступа. Проиллюстрируем это на .
Каждая стрелка на соответствует мандату доступа (паре <authID, набор_привилегий_доступа_к_объекту_БД>), который требуется сохранять в каталоге базы данных и проверять при попытке доступа от имени authID. Как видно, в случае (a) требуется сохранение и проверка n*m мандатов, где n – число пользователей в группе, а m – число объектов базы данных, для которых пользователи группы должны иметь одни и те же привилегии. В случае (b) число требуемых для корректной работы мандатов равно лишь n+m, и схема авторизации резко упрощается.
Группы пользователей, объединенных одной ролью, являются динамическими, поскольку в SQL поддерживаются возможности предоставления пользователю привилегии на исполнение данной роли и лишения пользователя этой привилегии (см. ниже в этом разделе). Более того, имеются возможности предоставления заданной роли 1 всех или части привилегий другой роли 1. Естественно, что при этом привилегии изменяются у всех пользователей, которые могут исполнять роль 1.
Рис. 22.1. Привилегии, пользователи и роли
Более того, имеются возможности предоставления заданной роли A всех или части привилегий другой роли B. Естественно, что при этом привилегии изменяются у всех пользователей, которые могут исполнять роль A.
В языке обеспечиваются возможности определения
В языке обеспечиваются возможности определения триггеров, которые вызываются («срабатывают») при вставке одной или нескольких строк в указанную таблицу, при модификации одной или нескольких строк в указанной таблице или при удалении одной или нескольких строк из указанной таблицы. Вообще говоря, триггер может производить любое действие, необходимое для соответствующего приложения. Можно определить триггеры, срабатывающие по одному разу для операций INSERT, UPDATE или DELETE, но существует и возможность определения триггеров, вызываемых при вставке, модификации или удалении каждой отдельной строки. Таблица, с которой связывается определение триггера, называется предметной таблицей (subject table), а оператор SQL, выполнение которого приводит к срабатыванию триггера, мы будем называть инициирующим (triggering SQL statement).
Триггерымогут срабатывать после и до реального выполнения инициирующего оператора SQL. В теле триггера допускается доступ к значениям вставляемых, модифицируемых и удаляемых строк. В случае операции модификации возможен доступ к значениям строк до модификации и к значениям после модификации. В соответствии со стандартом SQL:1999 любой триггер ассоциируется только с одной базовой таблицей. Не допускается определение триггеров над представлениями.
Можно придумать различные способы полезного применения механизма триггеров, но принято считать, что основными областями использования этого механизма являются следующие. Журнализация и аудит. С помощью триггеров можно отслеживать изменения таблиц, для которых требуется поддержка повышенного уровня безопасности. Данные об изменении таблиц могут сохраняться в других таблицах и включать, например, идентификатор пользователя, от имени которого выполнялась операция обновления; временную метку операции обновления; сами обновляемые данные и т. д.Согласование и очистка данных. С любым простым оператором SQL, обновляющим некоторую таблицу, можно связать триггеры, производящие соответствующие обновления других таблиц. Например, с операцией вставки новой строки в таблицу EMP (прием на работу нового служащего) можно было связать триггер, модифицирующий значения столбцов DEPT_EMP_NO и DEPT_TOTAL_SAL строки таблицы DEPT со значением столбца DEPT_NO, которое соответствует номеру отдела нового служащего.Операции, не связанные с изменением базы данных. В триггерах могут выполняться не только операции обновления базы данных. Стандарт SQL позволяет определять хранимые процедуры (которые могут вызываться из триггеров), посылающие электронную почту, печатающие документы и т. д.
Порождаемые таблицы с горизонтальной связью (lateral_derived_table)
Во всех вариантах построения запросов, обсуждавшихся ранее в этой и предыдущей лекциях, оставалась действующей общая семантика выполнения запроса: на первом шаге вычисляется расширенное декартово произведение таблиц, специфицированных в списке раздела FROM. Это остается верным и для случаев порождаемых и соединенных таблиц – вычисление выражения запросов или выражения соединений соответственно производится как подшаг вычисления раздела FROM. Однако в SQL имеется один специальный случай спецификации ссылки на таблицу (table_reference), который, вообще говоря, изменяет семантику раздела FROM. В этом подразделе мы кратко рассмотрим этот специальный случай.
Как показывают синтаксические правила, приведенные в лекции 17, один из возможных способов спецификации ссылки на таблицу состоит в следующем:
table_reference ::= LATERAL (query_expression) [ [ AS ] correlation_name [ ( derived_column_list ) ] ]
Таблица, ссылка на которую специфицируется таким образом, называется порождаемой таблицей с горизонтальной связью (lateral_derived_table; для краткости будем называть такие таблицы LD-таблицами). Отличие LD-таблицы от обычной порождаемой таблицы состоит в том, что в выражении запросов LD-таблицы разрешается использовать ссылки на столбцы таблиц, специфицированных ранее в разделе FROM (т. е. таких таблиц, ссылки на которые содержатся в списке раздела FROM слева от ссылки на данную LD-таблицу). Покажем на примере, каким образом наличие в списке раздела FROM ссылки на LD-таблицу меняет семантику этого раздела.
Предположим, что раздел FROM имеет вид FROM T1, T2, причем таблица T2 является LD-таблицей. Обозначим соответствующее выражение запросов через Q2. Тогда таблица T, являющаяся результатом раздела FROM, будет вычисляться следующим образом. Последовательно, строка за строкой просматривается таблица T1. Пусть s1 является очередной строкой T1. Тогда в Q2 все ссылки на столбцы вида T1.ck, где ck – имя некоторого столбца T1, заменяются значением s1.ck, и вычисляется полученное таким образом выражение запросов.
Обозначим результирующую таблицу этого выражения через T2s1. Обозначим через T12s1 таблицу, являющуюся результатом расширенного декартова произведения s1 CROSS JOIN T2s1. Таблица T получается путем объединения с сохранением дубликатов таблиц T12s1, полученных для всех строк s1 таблицы T1.
Видимо, наиболее важным (хотя и не единственным) частным случаем применения LD-таблицы является тот случай, когда в результате выполнения раздела FROM формируется соединение таблиц. Многие из формулировок запросов, приводившихся в этой лекции в качестве примеров, можно переформулировать с использованием данного механизма. Приведем лишь один простой пример.
Пример 19.19. Найти номера служащих, не являющихся руководителями отделов и получающих заплату, размер которой равен размеру зарплаты какого-либо руководителя отдела (еще одна формулировка запроса из примера из лекции 18).
SELECT EMP.EMP_NO FROM DEPT, LATERAL (SELECT EMP1_SAL FROM EMP EMP1 WHERE EMP1.EMP_NO = DEPT.DEPT_MNG), LATERAL (SELECT EMP_NO FROM EMP WHERE EMP_SAL = EMP1_SAL AND EMP.EMP_NO <> DEPT.DEPT_MNG);
Я не могу привести ни одного примера запроса, который было бы невозможно сформулировать без использования порождаемых таблиц с горизонтальной связью. Возникает впечатление (возможно, ошибочное), что эта конструкция была введена в язык по двум причинам – (a) из соображений общности и (b) по причине простоты реализации (в том смысле, что для реализации LD-таблиц не требуется изобретать какие-то новые технические приемы).
В соответствии со стандартом языка
В соответствии со стандартом языка SQL:1999 транзакции могут образовываться явным образом с использованием оператора START TRANSACTION, либо неявно, когда выполняется оператор, для которого требуется контекст транзакции, а этого контекста не существует. Например, операторы SELECT, UPDATE или CREATE TABLE могут выполняться только в контексте транзакции, а для выполнения оператора CONNECT (см. раздел ) такой контекст не требуется, и выполнение оператора CONNECT не приводит к неявному образованию транзакции. Для завершения транзакции должен быть явно использован один из двух операторов – COMMIT (требование завершить транзакцию с фиксацией ее результатов) или ROLLBACK (требование завершить транзакцию с удалением результатов всех выполненных операций из состояния базы данных).
Потребности информационных систем
Удовлетворяют ли рассмотренные выше базовые возможности файловых систем потребности информационных систем? Типовая информационная система, главным образом, ориентирована на хранение, выбор и модификацию данных соответствующей прикладной области. Структура таких данных зачастую очень сложна, и, хотя структуры данных различны в разных информационных системах, между ними часто бывает много общего.
На начальном этапе использования вычислительной техники для построения информационных систем проблемы структуризации данных решались индивидуально в каждой информационной системе. Производились необходимые надстройки над файловыми системами (библиотеки программ), подобно тому, как это делается в компиляторах, редакторах и т. д. ().
Рис. 1.4. Примитивная схема структуризации данных в информационной системе
Но поскольку информационные системы требуют сложных структур данных, эти дополнительные индивидуальные средства управления данными являлись существенной частью информационных систем и практически повторялись от одной системы к другой. Стремление выделить общую часть информационных систем, ответственную за управление сложно структурированными данными, явилось, на мой взгляд, первой побудительной причиной создания СУБД. Очень скоро стало понятно, что невозможно обойтись общей библиотекой программ (), реализующей над стандартной базовой файловой системой более сложные методы хранения данных.
Рис. 1.5. Две информационные системы с общей библиотекой
Поясним это на примере. Предположим, что требуется реализовать простую информационную систему, поддерживающую учет служащих некоторой организации. Система должна выполнять следующие действия: выдавать списки служащих по отделам;поддерживать возможность перевода служащего из одного отдела в другой;обеспечивать средства поддержки приема на работу новых служащих и увольнения работающих служащих.
Кроме того, для каждого отдела должна поддерживаться возможность получения: имени руководителя отдела;общей численности отдела;общей суммы зарплаты служащих отдела, среднего размера зарплаты и т. д.
Для каждого служащего должна поддерживаться возможность получения: номера удостоверения по полному имени служащего (для простоты допустим, что имена всех служащих различны);полного имени по номеру удостоверения;информации о соответствии служащего занимаемой должности и о размере его зарплаты.
Правила функциональных зависимостей
Приведенный набор правил является достаточно грубым. В стандарте SQL:1999 он уточняется набором дополнительных правил, устанавливающих восприимчивость различных языковых конструкций к операциям обновления и вставки. В основе этих правил лежит понятие функциональной зависимости (Functional Dependency – FD, см. раздел ). Полагая, что в целом понятие функциональной зависимости уже не должно вызывать у читателей каких-либо затруднений, приведем несколько дополнительных определений, требуемых для понимания подхода, используемого в SQL:1999. Пусть S обозначает некоторое множество столбцов таблицы T, а SS обозначает некоторое подмножество S (SS
Любое множество столбцов, являющееся двойником BUC-множества, также есть BUC-множество, так что это свойство распространяется через различные выражения, производящие виртуальные таблицы. Если имеются два множества столбцов S1 и S2, такие, что S1
На основе этих определений в стандарте SQL:1999 устанавливаются правила функциональных зависимостей для 11 компонентов языка. Базовые таблицы. Если у таблицы имеется первичный ключ, то соответствующее множество столбцов образует BPK-множество этой таблицы. Если у таблицы имеется не откладываемое ограничение уникальности и ни у одного столбца, указанного в этом ограничении, не допускается наличие неопределенных значений, то соответствующее множество столбцов является BUC-множеством. Если множество столбцов UCL базовой таблицы – BUC-множество, а CT обозначает все множество столбцов этой таблицы, то FD UCLCT представляет собой известную функциональную зависимость базовой таблицы.Конструкторы табличных значений. Поскольку для конструкторов табличных значений невозможно определять ограничения, в стандарте SQL:1999 для них не специфицированы BUC- и BPK-множества. В стандарте не определяются известные функциональные зависимости для такого рода конструкций, отличные от аксиоматических. Однако стандарт допускает, чтобы реализации SQL включали дополнительные механизмы определения известных функциональных зависимостей.Соединенные таблицы.
Если говорить о соединенных таблицах, получаемых в результате применения операций естественного соединения (NATUARAL JOIN) или соединения c заданием списка имен столбцов, значения которых должны совпадать (USING), то понятно, что соединенная таблица будет содержать двойников из одной или двух исходных таблиц. Если обозначить через S некоторое множество столбцов результирующей таблицы, а через CT – все множество столбцов этой таблицы, то S является BPK-множеством в том и только в том случае, когда имеет двойника в одной или обеих исходных таблицах. В таком случае во всех столбцах S не допускаются неопределенные значения, и FD SCT является известной функциональной зависимостью.
В стандарте определяется несколько правил, на основе которых устанавливаются известные функциональные зависимости соединенных таблиц, но здесь мы приведем только простейшее из этих правил. Если соединенная таблица производится на основе одной из двух указанных выше операций, то в первой таблице-источнике присутствует один или более столбцов, соответствующих одноименным столбцам второй таблицы-источника. Обозначим через SLCC список следующих выражений (элемент списка соответствует общему столбцу):
COALESCE (t1.colname, t2.colname) AS colname
Пусть JT обозначает ключевые слова, определяющие тип соединения (INNER, LEFT, RIGHT, FULL и т.д.), и пусть TN1 и TN2 обозначают имена таблиц или (если они заданы) имена псевдонимов двух таблиц-источников соответственно. Обозначим через IR результат вычисления следующего выражения запросов:
SELECT SLCC, T1*, T2* FROM T1 JT JOIN T2;
Тогда, в соответствии с правилами SQL, дополнительными известными функциональными зависимостями являются следующие: если JT задает INNER или LEFT, то действует FD COALESCE (T1.Ci, T2.Ci)T1.Ci для всех i от единицы до числа столбцов в IR;если JT задает INNER или RIGHT, то действует FD COALESCE (T1.Ci, T2.Ci)T2.Ci для всех i от единицы до числа столбцов в IR.
Обозначим через SL некоторый список выборки. Пусть: если все столбцы первой и второй таблиц-источников являются общими, то SL совпадает с SLCC;если среди столбцов таблиц-источников нет общих столбцов, то SL состоит из списка столбцов первой таблицы-источника, за которым следует список столбцов второй таблицы-источника;если все столбцы первой таблицы-источника являются общими, но у второй таблицы-источника имеются необщие столбцы, то SL состоит из SLCC, за которым следует список необщих столбцов второй таблицы-источника;аналогично, если все столбцы второй таблицы-источника являются общими, но у первой таблицы-источника имеются не общие столбцы, то SL состоит из SLCC, за которым следует список не общих столбцов первой таблицы-источника;наконец, если среди столбцов первой таблицы-источника и среди столбцов второй таблицы-источника имеются необщие столбцы, то SL состоит из SLCC, за которым следует список необщих столбцов первой таблицы-источника, а далее располагается список не общих столбцов второй таблицы-источника.
Тогда, в соответствии со стандартом, известными функциональными зависимостями виртуальной таблицы, получаемой путем соединения, являются известные функциональные зависимости выражения
SELECT SL FROM IR;
Ссылки на таблицы. Столбцы виртуальной таблицы, производимой по ссылке на таблицу, являются естественными двойниками столбцов таблицы, которая идентифицируется ссылкой. Поэтому BUC- и BPK-множества результирующей таблицы являются двойниками BUC- и BPK-множеств исходной таблицы, и известные функциональные зависимости результирующей таблицы получаются путем замены имен столбцов исходной таблицы на имена столбцов результирующей таблицы в известных функциональных зависимостях исходной таблицы.Раздел FROM. Описывая в лекции 17 общую семантику оператора выборки, мы отмечали, что на первом шаге выполнения этого оператора производится (виртуальная) таблица, являющаяся расширенным декартовым произведением всех таблиц, специфицированных в разделе FROM. Поэтому в стандарте SQL естественным образом формулируются следующие правила. Если в списке ссылок на таблицы раздела FROM содержится всего одна ссылка, то BUC- и BPK-множества результирующей таблицы являются двойниками BUC- и BPK-множеств исходной таблицы. Если в списке раздела FROM содержатся две или более ссылки на таблицы, то, в соответствии со стандартом, BUC- и BPK-множества результирующей таблицы не определены. Известные функциональные зависимости результирующей таблицы состоят из известных функциональных зависимостей каждой таблицы, специфицированной в разделе FROM.Раздел WHERE. В стандарте содержится набор правил, позволяющих определить BUC- и BPK-множества результирующей таблицы этогораздела, а также известные функциональные зависимости результирующей таблицы. Правила основываются на особенностях поведения предиката сравнения по равенству и логической операции AND.Раздел GROUP BY. Для определения BUC- и BPK-множеств и известных функциональных зависимостей результирующей таблицы раздела GROUP BY требуется фактическое образование в результирующей таблице нового столбца, значения которого могли бы каким-то образом идентифицировать строки исходной таблицы, образующие группы сгруппированной таблицы.Раздел HAVING.
BUC- и BPK-множества и известные функциональные зависимости результирующей таблицы раздела HAVING получаются из соответствующих множеств и FD таблицы, к которой применяется этотраздел, на основе правил, связанных с условным выражением раздела HAVING (как и в случае условия раздела WHERE, в данных правилах учитываются операции сравнения по равенству и логические операции AND).Раздел SELECT. На определение BUC- и BPK-множеств и известных функциональных зависимостей результата спецификации запроса влияет наличие в списке выборки выражений (value_expression), отличных от ссылок на столбцы.Выражение запроса. На определение BUC- и BPK-множеств и известных функциональных зависимостей результата выражения запроса влияет наличие в этом выражении операций UNION, INTERSECT и EXCEPT. В стандарте отсутствуют какие-либо правила для определения функциональных зависимостей в результатах рекурсивных запросов. Отмечается лишь возможность введения таких правил в реализациях.
Обратите внимание, что формально эта формулировка не отвечает требованиям SQL/92 для спецификаций запросов, допускающих применение операций обновления. Но в действительности здесь вложенный подзапрос вычисляется в единственное значение при отсутствии какой-либо корреляции с внешним вхождением таблицы EMP.
Множество, элементы которого невозможно различить, может быть либо пустым, либо содержать только один элемент.
В этом случае таблица соответствует понятию мультимножества.
Определение выражения COALESCE (V1, V2) см. в разделе лекции 16.
Напомним из лекции 17, что в соответствии с семантикой оператора выборки в результат раздела WHERE входят все строки результата раздела FROM, для которых результатом вычисления логического условия раздела WHERE является true.
Напомним из лекции 17, что на вход раздела HAVING подается результат раздела GROUP BY, если этот раздел присутствует в спецификации запроса, иначе – результат раздела WHERE, если этот раздел присутствует в спецификации запроса, иначе – результат раздела FROM.
Правильно построенные формулы
Правильно построенная формула (Well-Formed Formula, WFF) служит для выражения условий, накладываемых на кортежные переменные.
Предикат between
Предикат позволяет специфицировать условие вхождения в диапазон значений. Операндами являются строки:
between_predicate ::= row_value_constructor [ NOT ] BETWEEN row_value_constructor AND row_value_constructor
Все три строки-операнды должны иметь одну и ту же степень. Типы данных соответствующих значений строк-операндов должны быть совместимыми.
Пусть X, Y и Z обозначают первый, второй и третий операнды. Тогда по определению выражение X NOT BETWEEN Y AND Z эквивалентно выражению NOT (X BETWEEN Y AND Z). Выражение X BETWEEN Y AND Z по определению эквивалентно булевскому выражению X >= Y AND X <= Z.
Пример 19.5. Найти номера отделов и минимальный и максимальный размер зарплаты служащих для отделов, в которых средний размер зарплаты служащих не меньше среднего размера зарплаты служащих во всей компании и не больше 30000 руб.
SELECT DEPT_NO, MIN(EMP_SAL), MAX(EMP_SAL) FROM EMP WHERE DEPT_NO IS NOT NULL GROUP BY DEPT_NO HAVING AVG(EMP_SAL) BETWEEN (SELECT AVG(EMP_SAL) FROM EMP) AND 30000.00;
Еще раз приведем возможную формулировку этого запроса без использования разделов GROUP BY и HAVING (пример 19.5.1): SELECT DISTINCT DEPT_NO, (SELECT MIN(EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP1.DEPT_NO = EMP.DEPT_NO), (SELECT MAX(EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP1.DEPT_NO = EMP.DEPT_NO) FROM EMP WHERE (SELECT AVG(EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP1.DEPT_NO = EMP.DEPT_NO) BETWEEN (SELECT AVG(EMP_SAL) FROM EMP) AND 30000.00;
Как видно, отказ от использования раздела GROUP BY приводит к размножению однотипных подзапросов, строящих одну и ту же группу строк, над которой вычисляется агрегатная функция.
Предикат distinct
Пример 19.13. Найти номера отделов, которые можно отличить от любого другого отдела по дате рождения руководителя и среднему размеру зарплаты.
SELECT DEPT.DEPT_NO FROM DEPT, EMP EMP1, EMP EMP2 WHERE DEPT.DEPT_NO = EMP1.DEPT_NO AND DEPT.DEPT_MNG = EMP2.EMP_NO GROUP BY DEPT.DEPT_NO, EMP2.EMP_BDATE HAVING (EMP2.EMP_BDATE, AVG (EMP1.EMP_SAL)) DISTINCT FROM (SELECT EMP2.EMP_BDATE, AVG (EMP1.EMP_SAL) FROM DEPT DEPT1, EMP EMP1, EMP EMP2 WHERE DEPT1.DEPT_NO = EMP1.DEPT_NO AND DEPT1.DEPT_MNG = EMP2.EMP_NO AND DEPT1.DEPT_NO <> DEPT.DEPT_NO GROUP BY DEPT.DEPT_NO, EMP2.EMP_BDATE);
Предикат exists
Предикат exists определяется следующим синтаксическим правилом:
exists_predicate ::= EXISTS (query_expression)
Значением условия EXISTS (query_expression) является true в том и только в том случае, когда мощность таблицы-результата выражения запросов больше нуля, иначе значением условия является false.
Пример 19.9. Найти номера отделов, в которых средний размер зарплаты служащих равен максимальному размеру зарплаты служащих какого-либо другого отдела (другая формулировка для ).
SELECT DEPT.DEPT_NO FROM DEPT, EMP WHERE DEPT.DEPT_NO = EMP.DEPT_NO GROUP BY DEPT.DEPT_NO HAVING EXISTS (SELECT * FROM EMP EMP1 WHERE EMP1.DEPT_NO <> DEPT.DEPT_NO GROUP BY EMP1.DEPT_NO HAVING MAX (EMP1.EMP_SAL)= AVG (EMP.EMP_SAL));
В этой формулировке основной интерес представляет подзапрос, в котором корреляция с внешним запросом происходит через вызов агрегатной функции от группы строк внешнего запроса. Здесь также можно избавиться от разделов GROUP BY и HAVING во внешнем запросе (пример 19.9.1): SELECT DEPT.DEPT_NO FROM DEPT WHERE EXISTS (SELECT EMP.DEPT_NO FROM EMP WHERE EMP.DEPT_NO <> DEPT.DEPT_NO GROUP BY EMP.DEPT_NO HAVING MAX (EMP.EMP_SAL)= (SELECT AVG (EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP1.DEPT_NO = DEPT.DEPT_NO));
Предикат in
Предикат позволяет специфицировать условие вхождения строчного значения в указанное множество значений. Синтаксические правила следующие:
in_predicate ::= row_value_constructor [ NOT ] IN in_predicate_value in_predicate_value ::= table_subquery | (value_expression_comma_list)
Строка, являющаяся первым операндом, и таблица-второй операнд должны быть одинаковой степени. В частности, если второй операнд представляет собой список значений, то первый операнд должен иметь степень 1. Типы данных соответствующих столбцов операндов должны быть совместимы.
Пусть X обозначает строку-первый операнд, а S – множество строк второго операнда. Обозначим через s строку-элемент этого множества. Тогда по определению условие X IN S эквивалентно булевскому выражению OR
По определению условие X NOT IN S эквивалентно NOT (X IN S).
Пример 19.7. Найти номера отделов, в которых средний размер зарплаты служащих равен максимальному размеру зарплаты служащих какого-либо другого отдела.
SELECT DEPT.DEPT_NO FROM DEPT, EMP WHERE DEPT.DEPT_NO = EMP.DEPT_NO GROUP BY DEPT.DEPT_NO HAVING AVG(EMP.EMP_SAL) IN (SELECT MAX(EMP1.EMP_SAL) FROM EMP, DEPT DEPT1 WHERE EMP.DEPT_NO = DEPT1.DEPT_NO AND DEPT1.DEPT_NO <> DEPT.DEPT_NO GROUP BY DEPT.DEPT_NO);
Этот запрос, помимо прочего, демонстрирует наличие в условии раздела HAVING вложенного подзапроса с корреляцией. Как и раньше, можно избавиться от разделов GROUP BY и HAVING во внешнем запросе (пример 19.7.1):
SELECT DEPT.DEPT_NO FROM DEPT WHERE (SELECT AVG(EMP_SAL) FROM EMP WHERE EMP.DEPT_NO = DEPT.DEPT_NO) IN (SELECT MAX(EMP1.EMP_SAL) FROM EMP, DEPT DEPT1 WHERE EMP.DEPT_NO = DEPT1.DEPT_NO AND DEPT1.DEPT_NO <> DEPT.DEPT_NO GROUP BY DEPT.DEPT_NO);
Но в данном случае мы не можем отказаться от раздела GROUP BY во втором вложенном запросе, поскольку без этого невозможно получить множество значений результатов вызова агрегатной функции.
Предикат is distinct
Предикат позволяет проверить, являются ли две строки дубликатами. Условие определяется следующим синтаксическим правилом: distinct_predicate ::= row_value_constructor IS DISTINCT FROM row_value_constructor
Строки-операнды должны быть одинаковой степени. Типы данных соответствующих значений строк-операндов должны быть совместимы.
Напомним, что две строки s1 с именами столбцов c1, c2, …, cn и s2 с именами столбцов d1, d2, …, dn считаются строками-дубликатами, если для каждого i ( i = 1, 2, …, n ) либо ci и di не содержат NULL, и (ci = di) = true, либо и ci, и di содержат NULL. Значением условия s1 IS DISTINCT FROM s2 является true в том и только в том случае, когда строки s1 и s2 не являются дубликатами. В противном случае значением условия является false.
Заметим, что отрицательная форма условия – IS NOT DISTINCT FROM – в стандарте SQL не поддерживается. Вместо этого можно воспользоваться выражением NOT s1 IS DISTINCT FROM s2.
Предикат is null
Предикат is null позволяет проверить, являются ли неопределенными значения всех элементов строки-операнда:
null_predicate ::= row_value_constructor IS [ NOT ] NULL
Пусть X обозначает строку-операнд. Если значения всех элементов X являются неопределенными, то значением условия X IS NULL является true; иначе – false. Если ни у одного элемента X значение не является неопределенным, то значением условия X IS NOT NULL является true; иначе – false.
Замечание: условие X IS NOT NULL имеет то же значение, что условие NOT X IS NULL для любого X в том и только в том случае, когда степень X равна 1. Полная семантика предиката null приведена в .
| X IS X NULL | IS NOT NULL | NOT X IS NULL | NOT X IS NOT NULL | |
| Степень 1: значение NULL | true | false | false | true |
| Степень 1: значение отлично от NULL | false | true | true | false |
| Степень > 1: у всех элементов значение NULL | true | false | false | true |
| Степень > 1: у некоторых(не у всех) элементов значение NULL | false | false | true | true |
| Степень > 1: ни у одного элемента нет значения NULL | false | true | true | false |
Предикат like
Формально предикат like определяется следующими синтаксическими правилами:
like_predicate ::= source_value [ NOT ] LIKE pattern_value [ ESCAPE escape_value ] source_value ::= value_expression pattern_value ::= value_expression escape_value ::= value_expression
Все три операнда (source_value, pattern_value и escape_value) должны быть одного типа: либо типа символьных строк, либо типа битовых строк. В первом случае значением последнего операнда должна быть строка из одного символа, во втором – строка из 8 бит. Второй операнд, как правило, задается литералом соответствующего типа. В обоих случаях значение предиката равняется true в том и только в том случае, когда исходная строка (source_value) может быть сопоставлена с заданным шаблоном (pattern_value).
Если обрабатываются символьные строки, и если раздел ESCAPE условия отсутствует, то при сопоставлении шаблона со строкой производится специальная интерпретация двух символов шаблона: символ подчеркивания ('_') обозначает любой одиночный символ; символ процента ('%') обозначает последовательность произвольных символов произвольной длины (длина последовательности может быть нулевой). Если же раздел ESCAPE присутствует и специфицирует некоторый одиночный символ x, то пары символов «x_» и «x%» представляют одиночные символы «_» и «%» соответственно.
В случае обработки битовых строк сопоставление шаблона со строкой производится восьмерками соседних бит (октетами). В соответствии со стандартом SQL:1999, при сопоставлении шаблона со строкой производится специальная интерпретация октетов со значениями X'25' и X'5F' (коды символов подчеркивания и процента в кодировке ASCII). Первый октет обозначает любой одиночный октет, а второй – последовательность произвольной длины произвольных октетов (длина может быть нулевой). В разделе ESCAPE указывается октет, отменяющий специальную интерпретацию октетов X'25' и X'5F'.
Значение предиката like есть unknown, если значение первого или второго операндов является неопределенным. Условие x NOT LIKE y ESCAPE z эквивалентно условию NOT x LIKE y ESCAPE z.
Пример 19.8. Во всех отделах найти имена и число служащих, у которых в данном отделе имеются однофамильцы и фамилии которых начинаются со строки символов, изображающей фамилию руководителя отдела.
SELECT EMP_NAME, COUNT(*) FROM EMP, DEPT WHERE EMP.DEPT_NO = DEPT.DEPT_NO GROUP BY DEPT.DEPT_NO, EMP_NAME HAVING COUNT(*) > 1 AND EMP.EMP_NAME LIKE (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP1.EMP_NO = DEPT.DEPT_MNG) '%';
Конечно, и в этом случае условие с предикатом LIKE можно переместить из раздела HAVING в раздел WHERE. Этот запрос можно переформулировать в виде, лишенном разделов GROUP BY и HAVING (пример 19.8.1), но вряд ли это разумно, поскольку формулировка является менее понятной и существенно более сложной.
SELECT EMP_NAME, (SELECT COUNT(*) FROM EMP EMP1 WHERE EMP1.DEPT_NO = EMP.DEPT_NO AND EMP1.EMP_NAME = EMP.EMP_NAME AND EMP1.EMP_NO <> EMP.EMP_NO) + 1 FROM EMP WHERE (SELECT COUNT(*) FROM EMP EMP1 WHERE EMP1.DEPT_NO = EMP.DEPT_NO AND EMP1.EMP_NAME = EMP.EMP_NAME AND EMP1.EMP_NO <> EMP.EMP_NO) > 1 AND EMP_NAME LIKE (SELECT EMP1.EMP_NAME FROM EMP EMP1, DEPT WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND EMP1.EMP_NO = DEPT.DEPT_MNG) '%';
Предикат match
Предикат позволяет сформулировать условие соответствия строчного значения результату табличного подзапроса. Синтаксис определяется следующим правилом: match_predicate ::= row_value_constructor MATCH [ UNIQUE ] [ SIMPLE | PARTIAL | FULL ] query_expression
Степень первого операнда должна совпадать со степенью таблицы-результата выражения запроса. Типы данных столбцов первого операнда должны быть совместимы с типами соответствующих столбцов табличного подзапроса. Сравнение пар соответствующих значений производится аналогично тому, как это специфицировалось для предиката сравнения.
Пусть x обозначает строку-первый операнд. Тогда: Если отсутствует спецификация вида сопоставления или специфицирован тип сопоставления SIMPLE, то: если значение некоторого столбца x является неопределенным, то значением условия является true;если в x нет неопределенных значений, то: если не указано UNIQUE, и в результате выражения запроса существует (возможно, не уникальная) строка s в такая, что x = s, то значением условия является true;если указано UNIQUE, и в результате выражения запроса существует уникальная строка s, такая, что x = s, то значением условия является true;в противном случае значением условия является false.Если в условии присутствует спецификация PARTIAL, то: если все значения в x являются неопределенными, то значение условия есть true;иначе: если не указано UNIQUE, и в результате выражения запроса существует (возможно, не уникальная) строка s, такая, что каждое отличное от неопределенного значение x равно соответствующему значению s, то значение условия есть true;если указано UNIQUE, и в результате выражения запроса существует уникальная строка s, такая, что каждое отличное от неопределенного значение x равно соответствующему значению s, то значение условия есть true;в противном случае значение условия есть false.Если в условии присутствует спецификация FULL, то: если все значения в x неопределенные, то значение условия есть true;если ни одно значение в x не является неопределенным, то: если не указано UNIQUE, и в результате выражения запроса существует (возможно, не уникальная) строка s, такая, что x = s, то значение условия есть true;если указано UNIQUE, и в результате выражения запроса существует уникальная строка s, такая, что x = s, то значение условия есть true;в противном случае значение условия есть false.в противном случае значение условия есть false.
Предикат null
Пример 19.6. Найти номера и число служащих отделов, данные о руководителях которых не содержат номер отдела (конечно, в этом случае нас интересуют только те отделы, у которых имеется руководитель).
SELECT DEPT.DEPT_NO, COUNT(*) FROM DEPT, EMP EMP1, EMP EMP2 WHERE DEPT.DEPT_NO = EMP2.DEPT_NO AND DEPT.DEPT_MNG = EMP1.EMP_NO GROUP BY DEPT.DEPT_NO, EMP1.DEPT_NO HAVING EMP1.DEPT_NO IS NULL;
Как и в , условие раздела HAVING можно переместить в раздел WHERE и получить вторую формулировку (пример 19.6.1):
SELECT DEPT.DEPT_NO, COUNT(*) FROM DEPT, EMP EMP1, EMP EMP2 WHERE DEPT.DEPT_NO = EMP2.DEPT_NO AND DEPT.DEPT_MNG = EMP1.EMP_NO AND EMP1.DEPT_NO IS NULL GROUP BY DEPT.DEPT_NO;
Кстати, в этом случае, поскольку в запросе присутствует только один вызов агрегатной функции, формулировка без использования раздела GROUP BY оказывается более понятной и не менее эффективной (даже при следовании предписанной семантике выполнения оператора SELECT), что показывает пример 19.6.2:
SELECT DEPT.DEPT_NO, (SELECT COUNT(*) FROM EMP WHERE DEPT.DEPT_NO = EMP.DEPT_NO) FROM DEPT, EMP WHERE DEPT.DEPT_MNG = EMP.EMP_NO AND EMP.DEPT_NO IS NULL;
Предикат overlaps
Этот предикат служит для проверки перекрытия во времени двух событий. Условие определяется следующим синтаксисом:
overlaps_predicate ::= row_value_constructor OVERLAPS row_value_constructor
Степень каждой из строк-операндов должна быть равна 2. Тип данных первого столбца каждого из операндов должен быть типом даты-времени, и типы данных первых столбцов должны быть совместимы. Тип данных второго столбца каждого из операндов должен быть типом даты-времени или интервала. При этом: если это тип интервала, то точность типа должна быть такой, чтобы интервал можно было прибавить к значению типа дата-время первого столбца;если это тип дата-время, то он должен быть совместим с типом данных дата-время первого столбца.
Пусть D1 и D2 – значения первого столбца первого и второго операндов соответственно. Если второй столбец первого операнда имеет тип дата-время, то пусть E1 обозначает его значение. Если второй столбец первого операнда имеет тип INTERVAL, то пусть I1 – его значение, а E1 = D1 + I1. Если D1 является неопределенным значением или если E1 < D1, то пусть S1 = E1 и T1 = D1. В противном случае, пусть S1 = D1 и T1 = E1. Аналогично определяются S2 и T2 применительно ко второму операнду. Результат условия совпадает с результатом вычисления следующего булевского выражения:
(S1 > S2 AND NOT (S1 >= T2 AND T1 >= T2)) OR (S2 > S1 AND NOT (S2 >= T1 AND T2 >= T1)) OR (S1 = S2 AND (T1 <> T2 OR T1 = T2))
Предикат similar
Формально предикат similar определяется следующими синтаксическими правилами:
similar_predicate ::= source_value [ NOT ] SIMILAR TO pattern_value [ ESCAPE escape_value ] source_value ::= character_expression pattern_value ::= character_expression escape_value ::= character_expression
Все три операнда (source_value, pattern_value и escape_value) должны иметь тип символьных строк. Значением последнего операнда должна быть строка из одного символа. Второй операнд, как правило, задается литералом соответствующего типа. В обоих случаях значение предиката равняется true в том и только в том случае, когда шаблон (pattern_value) должным образом сопоставляется с исходной строкой (source_value).
Основное отличие предиката similar от рассмотренного ранее предиката like состоит в существенно расширенных возможностях задания шаблона, основанных на использовании правил построения регулярных выражений. Регулярные выражения предиката similar определяются следующими синтаксическими правилами:
regular_expression ::= regular_term | regular_expression vertical_bar regular_term regular_term ::= regular_factor | regular_term regular_factor regular_factor ::= regular_primary | regular_primary * | regular_primary + regular_primary ::= character_specifier | % | regular_character_set | ( regular_expression ) character_specifier ::= non_escape_character | escape_character regular_character_set ::= _ | left_bracket character_enumeration_list right_bracket | left_bracket ^ character_enumeration_list right_bracket | left_bracket : regular_charset_id : right_bracket character_enumeration ::= character_specifier | character_specifier – character_specifier regular_charset_id ::= ALPHA | UPPER | LOWER | DIGIT | ALNUM
Поскольку в синтаксических правилах регулярных выражений символы «|», «[» и «]», используемые нами в качестве метасимволов в BNF, являются терминальными символами, они изображены как vertical_bar, left_bracket и right_bracket соответственно.
Создаваемое по приведенным правилам регулярное выражение представляет собой символьную строку, содержащую все символы, которые требуется явно сопоставлять с символами строки-источника.
В строке могут находиться специальные символы, представляющие собой заменители обычных символов («%» и «_»), обозначения операций («|»), показатели числа возможных повторений («*» и «+») и т. д. При вычислении регулярного выражения образуются все возможные символьные строки, не содержащие специальных символов и соответствующие исходному шаблону. Тем самым, значением предиката similar является true в том и только в том случае, когда среди всех символьных строк, генерируемых по регулярному выражению pattern_value, найдется символьная строка, совпадающая с source_value.
Рассмотрим несколько примеров регулярных выражений.
Выражение '(This is string1)|(This is string2)' производит две символьные строки: '(This is string1)' и '(This is string2)'. В общем случае в круглых скобках могут находиться произвольные регулярные выражения rexp1 и rexp2. Результатом вычисления '(rexp1)|(rexp2)' является множество символьных строк, генерируемых выражением rexp1, объединенное с множеством символьных строк, генерируемых выражением rexp2.
Выражение 'This is string [12]*' генерирует символьные строки 'This is string ', 'This is string 1', 'This is string 2', 'This is string 11', 'This is string 22', 'This is string 12', 'This is string 22', 'This is string 111' и т. д. Конструкция в квадратных скобках представляет собой один из вариантов определения набора символов (regular_character_set). В данном случае символы, входящие в определяемый набор, просто перечисляются. При вычислении регулярного выражения в каждой из генерируемых символьных строк конструкция в квадратных скобках заменяется одним из символов соответствующего набора.
Специальный символ «*», стоящий после закрывающей квадратной скобки, является показателем числа повторений. «Звездочка» означает, что в генерируемых символьных строках элемент регулярного выражения, непосредственно предшествующий «звездочке», может появляться ноль или более раз. Использование в такой же ситуации специального символа «+» означает, что в генерируемых символьных строках элемент регулярного выражения, непосредственно предшествующий символу «плюс», может появляться один или более раз.
Другая форма определения набора символов иллюстрируется регулярным выражением 'This is string [:DIGIT:]'. В этом случае конструкция в квадратных скобках представляет любой одиночный символ, изображающий десятичную цифру. Другими допустимыми в SQL идентификаторами наборов символов (regular_charset_id) являются ALPHA (любой символ алфавита), UPPER (любой символ верхнего регистра), LOWER (любой символ нижнего регистра) и ALNUM (любой алфавитно-цифровой символ).
Определяемый набор символов может задаваться нижней и верхней границей диапазона значений кодов допустимых символов. Например, в регулярном выражении 'This is string [3-8]' конструкция в квадратных скобках представляет собой любой одиночный символ, изображающий цифры от 3 до 8 включительно. Заметим, что при задании диапазона можно использовать любые символы, но требуется, чтобы значение кода символа левой границы диапазона было не больше значения кода символа правой границы.
Наконец, имеется еще одна возможность определения набора символов. Соответствующая конструкция позволяет указать, какие символы из общего набора символов SQL не входят в определяемый набор символов. Например, регулярное выражение '_S[^t]*ing%' генерирует все символьные строки, у которых вторым символом является «S», за которым (не обязательно непосредственно) следует подстрока «ing», но между «S» и «ing» отсутствуют вхождения символа «t».
Как и в предикате like, символ, определенный в разделе ESCAPE, поставленный перед любым специальным символом, отменяет специальную интерпретацию этого символа.
В заключение данного пункта вернемся к отложенному в разделе лекции 17 обсуждению функции SUBSTRING ... SIMILAR ... ESCAPE. Напомним, что вызов этой функции определяется следующим синтаксисом:
SUBSTRING (character_value_expression SIMILAR character_value_expression ESCAPE character_value_expression)
Предположим, что в разделе ESCAPE (который должен присутствовать обязательно) задан символ «x». Тогда символьная строка, задаваемая во втором операнде, должна иметь вид 'rexp1x"rexp2x"rexp3', где rexp1, rexp2 и rexp3 являются регулярными выражениями.Функция пытается разделить символьную строку первого операнда на три раздела, первый из которых определяется путем сопоставления начала строки со строками, генерируемыми rexp1, второй – путем сопоставления оставшейся части строки первого операнда с rexp2 и третий – путем сопоставления конца этой строки с rexp3. Возвращаемым значением функции является средняя часть символьной строки первого операнда.
Вот пример вызова функции: SUBSTRING ( 'This is string22' SIMILAR 'This is\"[:ALPHA:]+\"[:DIGIT:]+' ESCAPE '\' )
Результатом будет строка 'string'.
Предикат сравнения
Этот предикат предназначен для спецификации сравнения двух строчных значений. Синтаксис предиката следующий:
comparison_predicate ::= row_value_constructor comp_op row_value_constructor comp_op ::= = | <> («не равно»)| < | > | <= («меньше или равно») | >= («больше или равно»)
Строки, являющиеся операндами операции сравнения, должны быть одинаковой степени. Типы данных соответствующих значений строк-операндов должны быть совместимы.
Пусть X и Y обозначают соответствующие элементы строк-операндов, а xv и yv – их значения. Тогда: если xv и/или yv являются неопределенными значениями, то значение условия X comp_op Y - unknown;в противном случае значением условия X comp_op Y является true или false в соответствии с естественными правилами применения операции сравнения.
При этом: Числа сравниваются в соответствии с правилами алгебры.Сравнение двух символьных строк производится следующим образом: если длина строки X не равна длине строки Y, то для выравнивания длин строк более короткая строка расширяется символами набивки (pad symbol); если для используемого набора символов порядок сортировки явным образом не специфицирован, то в качестве символа набивки используется пробел;далее производится лексикографическое сравнение строк в соответствии с предопределенным или явно определенным порядком сортировки символов.Сравнение двух битовых строк X и Y основано на сравнении соответствующих бит. Если Xi и Yi – значения i-тых бит X и Y соответственно и если lx и ly обозначает длину в битах X и Y соответственно, то: X равно Y тогда и только тогда, когда lx = ly и Xi = Yi для всех i;X меньше Y тогда и только тогда, когда (a) lx < ly и Xi = Yi для всех i меньших или равных lx, или (b) Xi = Yi для всех i < n и Xn = 0, а Yn =1 для некоторого n меньшего или равного min (lx, ly).Сравнение двух значений типа дата-время производится в соответствии с видом интервала, который получается при вычитании второго значения из первого. Пусть X и Y – сравниваемые значения, а H – наименее значимое поле даты-времени X и Y.
Результат сравнения X comp_op Y определяется как (X – Y) H comp_ op INTERVAL (0) H. (Два значения типа дата-время сравнимы только в том случае, если они содержат одинаковый набор полей даты-времени.)Сравнение двух значений анонимного строкового типа производится следующим образом. Пусть Rx и Ry обозначают строки-операнды, а Rxi и Ryi – i-тые элементы Rx и Ry соответственно. Вот как определяется результат сравнения Rx comp_op Ry: Rx = Ry есть true тогда и только тогда, когда Rxi = Ryi есть true для всех i;Rx <> Ry есть true тогда и только тогда, когда Rxi <> Ryi есть true для некоторого i;Rx < Ry есть true тогда и только тогда, когда Rxi = Ryi есть true для всех i < n, и Rxn < Ryn есть true для некоторого n;Rx > Ry есть true тогда и только тогда, когда Rxi = Ryi есть true для всех i < n, и Rxn > Ryn есть true для некоторого n;Rx <= Ry есть true тогда и только тогда, когда Rx = Ry есть true или Rx < Ry есть true;Rx >= Ry есть true тогда и только тогда, когда Rx = Ry есть true или Rx > Ry есть true;Rx = Ry есть false тогда и только тогда, когда Rx <> Ry есть true;Rx <> Ry есть false тогда и только тогда, когда Rx = Ry есть true;Rx < Ry есть false тогда и только тогда, когда Rx >= Ry есть true;Rx > Ry есть false тогда и только тогда, когда Rx <= Ry есть true;Rx <= Ry есть false тогда и только тогда, когда Rx > Ry есть true;Rx >= Ry есть false тогда и только тогда, когда Rx < Ry есть true;Rx comp_op Ry есть unknown тогда и только тогда, когда Rx comp_op Ry не есть true или false.
Предикат сравнения с квантором
Этот предикат позволяет специфицировать квантифицированное сравнение строчного значения и определяется следующим синтаксическим правилом:
quantified_comparison_predicate ::= row_value_constructor comp_op { ALL | SOME | ANY } query_expression
Степень первого операнда должна быть такой же, как и степень таблицы-результата выражения запросов. Типы данных значений строки-операнда должны быть совместимы с типами данных соответствующих столбцов выражения запроса. Сравнение строк производится по тем же правилам, что и для предиката сравнения.
Обозначим через x строку-первый операнд, а через S – результат вычисления выражения запроса. Пусть s обозначает произвольную строку таблицы S. Тогда: условие x comp_op ALL S имеет значение true в том и только в том случае, когда S пусто, или значение условия x comp_op s равно true для каждой строки s, входящей в S. Условие x comp_op ALL S имеет значение false в том и только в том случае, когда значение предиката x comp_op s равно false хотя бы для одной строки s, входящей в S. В остальных случаях значение условия x comp_op ALL S равно unknown;условие x comp_op SOME S имеет значение false в том и только в том случае, когда S пусто, или значение условия x comp_op s равно false для каждой строки s, входящей в S. Условие x comp_op SOME S имеет значение true в том и только в том случае, когда значение предиката x comp_op s равно true хотя бы для одной строки s, входящей в S. В остальных случаях значение условия x comp_op SOME S равно unknown;условие x comp_op ANY S эквивалентно условию x comp_op SOME S.
Предикат unique
Этот предикат позволяет сформулировать условие отсутствия дубликатов в результате запроса: unique_predicate ::= UNIQUE (query_expression)
Результатом вычисления условия UNIQUE (query_expression) является true в том и только в том случае, когда в таблице-результате выражения запросов отсутствуют какие-либо две строки, одна из которых является дубликатом другой. В противном случае значение условия есть false.
Пример 19.10. Найти номера отделов и средний размер зарплаты служащих для таких отделов, где средний размер зарплаты служащих отличается от среднего размера зарплаты всех других отделов.
SELECT DEPT.DEPT_NO, AVG (EMP.EMP_SAL) FROM DEPT, EMP WHERE DEPT.DEPT_NO = EMP.DEPT_NO GROUP BY DEPT.DEPT_NO HAVING UNIQIUE (SELECT AVG (EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP1.DEPT_NO IS NOT NULL GROUP BY EMP1.DEPT_NO HAVING AVG (EMP1.EMP_SAL) = AVG (EMP.EMP_SAL));
Вот альтернативная формулировка этого запроса с использованием предиката NOT EXISTS (пример 19.10.1):
SELECT DEPT.DEPT_NO, AVG (EMP.EMP_SAL) FROM DEPT, EMP WHERE DEPT.DEPT_NO = EMP.DEPT_NO GROUP BY DEPT.DEPT_NO HAVING NOT EXISTS (SELECT EMP1.DEPT_NO FROM EMP EMP1 WHERE EMP1.DEPT_NO <> DEPT.DEPT_NO GROUP BY EMP1.DEPT_NO HAVING AVG (EMP1.EMP_SAL)= AVG (EMP.EMP_SAL));
Предикатные синхронизационные блокировки
Несмотря на привлекательность метода гранулированных синхронизационных захватов, следует отметить, что он не решает проблему фантомов (если, конечно, не ограничиться использованием блокировок таблиц в режимах S и X). Давно известно, что для решения этой проблемы необходимо перейти от блокировок индивидуальных («физических») объектов базы данных, к блокировке условий (предикатов), которым удовлетворяют эти объекты. Проблема фантомов не возникает при использовании для блокировок уровня таблиц именно потому, что таблица как логический объект представляет собой неявное условие для входящих в него кортежей. Блокировка таблицы – это простой и частный случай предикатной блокировки.
Поскольку любая операция над реляционной базой данных задается некоторым условием (т.е. в ней указывается не конкретный набор объектов базы данных, над которыми нужно выполнить операцию, а условие, которому должны удовлетворять объекты этого набора), идеальным выбором было бы требовать синхронизационную блокировку в режиме S или X именно этого условия. Но если посмотреть на общий вид условий, допускаемых, например, в языке SQL, то становится абсолютно непонятно, как определить совместимость двух предикатных блокировок. Ясно, что без этого использовать предикатные блокировки для сериализации транзакций невозможно, а в общей форме проблема неразрешима.
Один из компромиссных подходов предлагался участниками проекта System R. Подход основывался на том, что при открытии сканирования таблицы по индексу в RSS передается дополнительная информация (диапазон сканирования), которая ограничивает множество кортежей, среди которых не должны возникать фантомы.
Опираясь на наличие этой информации, предлагалось ввести в систему блокировок System R элементы предикатных блокировок. Заметим сначала, что в System R блокировки сегментов (файлов), таблиц и кортежей технически трактовались единообразно, как блокировки идентификаторов кортежей (tid'ов). При блокировке кортежа на самом деле блокировался его tid.
При блокировке сегмента или таблицы на самом деле блокировался tid описателя соответствующего объекта во внутренних таблицах-каталогах сегментов или таблиц.
Предлагалось расширить систему синхронизации, разрешив применять блокировки к паре «идентификатор индекса, интервал значений ключа этого индекса». К такой паре можно было применять блокировки в любом из допустимых режимов, причем две такие блокировки считались совместимыми в том и только в том случае, если они были совместимы в соответствии с приведенной таб. 13.2 или указанные диапазоны значений ключей не пересекались.
При наличии такой возможности, если открывается сканирование таблицы через индекс, то таблица блокируется в режиме IS, и в этом же режиме блокируется пара «идентификатор индекса, диапазон сканирования». При занесении (удалении) кортежа таблица блокируется в режиме IX, и в этом же режиме для каждого индекса, определенного на данной таблице отношении, блокируется пара «идентификатор индекса, значение ключа из затрагиваемого операцией кортежа». Это позволяет избежать конфликтов читающих транзакций с теми изменяющими транзакциями, которые затрагивают диапазоны сканирования читающих транзакций. При этом решается проблема фантомов, и параллельность транзакций ограничивается «по существу», т.е. только в тех случаях, когда их параллельное выполнение создает проблемы.
Заметим сразу, что описанное решение проблемы фантомов далеко от идеального. Во-первых, по-прежнему при сканировании таблиц без использования индексов отсутствие фантомов можно гарантировать только при блокировке всего отношения в режиме S. Во-вторых, даже при сканировании по индексу условие реальной выборки кортежа часто может быть гораздо строже простого указания диапазона сканирования, а это значит, что блокировка этого диапазона будет слишком сильной, т.е. затронет более широкое множество кортежей, чем то, которое будет реальным результатом сканирования.
Известно следующее более совершенное решение.
Будем называть простым условием конъюнкцию простых предикатов сравнения, имеющих вид имя_поля { = > < } значение. В типичных СУБД, поддерживающих двухуровневую организацию (языковой уровень и уровень управления внешней памяти), в интерфейсе подсистемы управления памятью (которая обычно заведует и сериализацией транзакций) допускаются только простые условия. Подсистема языкового уровня производит компиляцию оператора SQL со сложным условием в последовательность обращений к подсистеме управления памятью, в каждом из которых содержатся только простые условия.
Более точно, простое условие явно указывается в операции открытия сканирования таблицы (напрямую или через индекс; в последнем случае оно конъюнктивно соединяется с условием, задаваемым диапазоном сканирования). Кроме того, при открытии сканирования всегда можно указать, для какой цели оно будет использоваться: для выборки кортежей, для их удаления или для их обновления (это известно компилятору SQL). Кроме того, неявные условия задаются операциями вставки и удаления кортежей (конъюнктивное логическое выражение, состоящее из простых предикатов вида имя_поля = значение
для всех полей таблицы), а также операциями обновления кортежей (конъюнктивное логическое выражение, состоящее из простых предикатов вида имя_поля = значение для всех обновляемых полей таблицы). Поэтому в случае типовой организации SQL-ориентированной СУБД простые условия можно использовать как основу предикатных захватов.
Для простых условий совместимость предикатных блокировок легко определяется на основе следующей геометрической интерпретации. Пусть Tab
– таблица с полями a1, a2, ..., an, а m1, m2, ..., mn
– множества допустимых значений a1, a2, ..., an
соответственно (естественно, все эти множества – конечные). Тогда можно сопоставить Tab
конечное n-мерное пространство возможных значений кортежей Tab. Легко видеть, что любое простое условие, представляющее собой конъюнкцию простых предикатов, «вырезает» в этом пространстве k-мерный прямоугольник (k ≤ n).
Достаточно очевидно следующее утверждение:
Пусть имеются два простых условия scond1
и scond2. Пусть транзакция T1
запрашивает блокировку scond1, а транзакция T2
– scond2
в режимах, которые были бы несовместимы, если бы scond1
и scond2
являлись не условиями, а объектами базы данных (S-X, X-S, X-X). Эти блокировки совместимы в том и только в том случае, когда прямоугольники, соответствующие scond1
и scond2, не пересекаются.
Это утверждение действительно очевидно (каждому k-мерному прямоугольнику в n-мерном пространстве возможных значений кортежей Tab
соответствует некоторое подмножество возможных значений кортежей, и отсутствие пересечения у двух прямоугольников гарантирует отсутствие конфликтов транзакций), но для наглядности на рис. 13.5 приводится иллюстрирующий пример, показывающий, что в каких бы режимах не требовала транзакция T1
блокировки условия (0 < a < 5) & (b = 5), а транзакция T2
– блокировки условия (0 < a <6) & (0 < b <4), эти блокировки всегда будут совместимы.
Рис. 13.5. Простые условия, блокировки которых совместимы
Интересно, что при поддержке такой системы блокировок простых условий можно обойтись без гранулированных блокировок. В частности, чтобы гарантированно заблокировать таблицу целиком, достаточно заблокировать условие &1
(min(mi) < имя_поляi
< max(mi)). Чтобы заблокировать базу данных, достаточно заблокировать условие, являющееся конъюнкцией условий блокировки всех таблиц этой базы данных.
Заметим, что блокировки простых условий описываются таблицами, немногим отличающимися от таблиц традиционных синхронизаторов с гранулированными блокировками. Поэтому введение в СУБД механизма предикатных блокировок не приводит к значительным усложнениям.
Предикаты сравнения
Пример 19.1. Найти номера отделов, в которых работает ровно 30 служащих.
SELECT DEPT_NO FROM EMP WHERE DEPT_NO IS NOT NULL GROUP BY DEPT_NO HAVING COUNT(*) = 30;
Конечно, этот запрос можно сформулировать и без использования разделов GROUP BY и HAVING. Например, возможна следующая формулировка (пример 19.1.1):
SELECT DISTINCT DEPT_NO FROM EMP WHERE (SELECT COUNT (*) FROM EMP EMP1 WHERE EMP1.DEPT_NO = EMP.DEPT_NO) = 30;
Обратите внимание, что в формулировке отдельная проверка условия DEPT_NO IS NOT NULL не требуется.
Пример 19.2. Найти номера всех отделов, в которых средний размер зарплаты служащих превосходит 12000 руб. SELECT DEPT_NO FROM EMP WHERE DEPT_NO IS NOT NULL GROUP BY DEPT_NO HAVING AVG(EMP_SAL) > 12000.00;
Очевидно, что и в этом случае возможна формулировка запроса без использования разделов GROUP BY и HAVING (пример 19.2.1):
SELECT DISTINCT DEPT_NO FROM EMP WHERE (SELECT AVG(EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP1.DEPT_NO = EMP.DEPT_NO) > 12000.00;
Немного задержимся на этих примерах и обсудим, что означает различие в формулировках запросов. В соответствии с семантикой оператора SELECT, при выполнении запросов и для каждой строки таблицы EMP в цикле просмотра внешнего запроса будет выполняться подзапрос, который в случае наших примеров выберет из таблицы EMP (EMP1) все строки со значением столбца DEPT_NO, равным значению этого столбца в текущей строке внешнего цикла. Другими словами, для каждой строки внешнего цикла образуется группа, для нее проверяется условие выборки, и в списке выборки используется имя столбца этой неявной группировки. Из-за того, что группа образуется и оценивается для каждой строки таблицы EMP, мы вынуждены указать в разделе SELECT спецификацию DISTINCT.
Формулировки и обеспечивают более четкие указания для выполнения запроса. Нужно сразу сгруппировать таблицу EMP в соответствии со значениями столбца DEPT_NO, отобрать нужные группы, и для каждой отобранной группы вычислить значения выражений списка выборки. В этом случае семантика выполнения запроса не предписывает выполнения лишних действий.
Конечно, в развитой реализации SQL компилятор должен суметь понять, что формулировки и эквивалентны формулировкам и соответственно, и избежать выполнения лишних действий.
Пример 19.3. Найти номера всех отделов, в которых суммарный объем зарплаты служащих меньше суммарного объема зарплаты всех руководителей отделов.
SELECT DEPT_NO FROM EMP WHERE DEPT_NO IS NOT NULL GROUP BY DEPT_NO HAVING SUM(EMP_SAL) < (SELECT SUM(EMP1.EMP_SAL) FROM EMP EMP1, DEPT WHERE EMP1.EMP_NO = DEPT_MNG);
И в этом случае возможна формулировка без использования разделов GROUP BY и HAVING (пример 19.3.1). Эта формулировка является более сложной, чем в случае двух предыдущих примеров, но и к ней применимы приведенные выше замечания.
SELECT DISTINCT DEPT_NO FROM EMP WHERE (SELECT SUM(EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP1.DEPT_NO = EMP.DEPT_NO) < (SELECT SUM(EMP1.EMP_SAL) FROM EMP EMP1, DEPT WHERE EMP1.EMP_NO = DEPT_MNG);
Пример 19.4. Для каждого отдела найти его номер, имя руководителя, число служащих, минимальный, максимальный и средний размеры зарплаты служащих.
SELECT DEPT.DEPT_NO, EMP.EMP_NAME, COUNT(*), MIN(EMP1.EMP_SAL), MAX(EMP1.EMP_SAL), AVG(EMP1.EMP_SAL) FROM DEPT, EMP, EMP EMP1 WHERE DEPT.DEPT_NO = EMP1.DEPT_NO GROUP BY DEPT.DEPT_NO, DEPT.DEPT_MNG, EMP.EMP_NO, EMP.EMP_NAME HAVING DEPT.DEPT_MNG = EMP.EMP_NO;
Этот запрос иллюстрирует несколько интересных особенностей языка SQL. Во-первых, это первый пример запроса с соединениями, в котором присутствуют разделы GROUP BY и HAVING. Во-вторых, одно условие соединения находится в разделе WHERE, а другое – в разделе HAVING. На самом деле, можно было бы перенести в раздел WHERE и второе условие соединения, и, скорее всего, на практике использовалась бы формулировка, приведенная в примере 19.4.1:
SELECT DEPT.DEPT_NO, EMP.EMP_NAME, COUNT(*), MIN(EMP1.EMP_SAL), MAX(EMP1.EMP_SAL), AVG(EMP1.EMP_SAL) FROM DEPT, EMP, EMP EMP1 WHERE DEPT.DEPT_NO = EMP1.DEPT_NO AND DEPT.DEPT_MNG = EMP.EMP_NO GROUP BY DEPT.DEPT_NO, EMP.EMP_NAME;
Но первая формулировка тоже верна, поскольку второе условие соединения определено на столбцах группировки.
Наконец, легко видеть, что, по существу, группировка производится по значениям столбца DEPT.DEPT_NO. Остальные столбцы, указанные в списке столбцов группировки, функционально определяются столбцом DEPT.DEPT_NO. Тем не менее, в первой формулировке мы включили в этот список столбцы DEPT.DEPT_MNG и EMP.EMP_NO, чтобы их имена можно было использовать в условии раздела HAVING, и столбец EMP.EMP_NAME, чтобы можно было использовать его имя в списке выборки раздела SELECT. Другими словами, мы вынуждены расширять запрос избыточными данными, чтобы выполнить формальные синтаксические требования языка. Как видно, во второй формулировке мы смогли удалить из списка группировки два столбца. Кстати, не следует думать, что многословие первой формулировки помешает СУБД выполнить запрос настолько же эффективно, как запрос во второй формулировке. Грамотно построенный оптимизатор SQL сам приведет первую формулировку ко второй.
И этот запрос можно сформулировать без использования раздела GROUP BY за счет использования подзапросов в списке раздела SELECT (пример 19.4.2):
SELECT DEPT.DEPT_NO, EMP.EMP_NAME, (SELECT COUNT(*) FROM EMP WHERE EMP.DEPT_NO = DEPT.DEPT_NO), (SELECT MIN(EMP_SAL) FROM EMP WHERE EMP.DEPT_NO = DEPT.DEPT_NO), (SELECT MAX(EMP_SAL) FROM EMP WHERE EMP.DEPT_NO = DEPT.DEPT_NO), (SELECT AVG(EMP_SAL) FROM EMP WHERE EMP.DEPT_NO = DEPT.DEPT_NO) FROM DEPT, EMP WHERE DEPT.DEPT_MNG = EMP.EMP_NO;
Здесь мы снова имеем замаскированную группировку строк по значениям столбца DEPT.DEPT_NO и вычисление агрегатных функций для каждой группы. Формально группа строится каждый раз заново при вызове каждой агрегатной функции. Хороший компилятор SQL должен привести формулировку к виду .
И последнее замечание. Во всех приведенных формулировках в результат не попадут данные об отделах, в которых отсутствует руководитель (столбец DEPT.DEPT_MNG может содержать неопределенное значение). Вообще говоря, это не противоречит условию запроса, но если бы мы хотели выдавать в результате NULL в качестве имени руководителя отдела с отсутствующим руководителем, то можно было немного усложнить формулировку запроса, например, следующим образом (пример 19.4.3): SELECT DEPT.DEPT_NO, CASE WHEN DEPT.DEPT_MNG IS NULL THEN NULL ELSE (SELECT EMP.EMP_NAME FROM EMP WHERE EMP.EMP_NO = DEPT.DEPT_MNG), COUNT(*), MIN(EMP1.EMP_SAL), MAX(EMP1.EMP_SAL), AVG(EMP1.EMP_SAL) FROM DEPT, EMP, EMP EMP1 WHERE DEPT.DEPT_NO = EMP1.DEPT_NO GROUP BY DEPT.DEPT_NO;
Предикаты сравнения с квантором
Пример 19.11. Найти номера отделов и средний возраст служащих для таких отделов, что найдется хотя бы один другой отдел, средний возраст служащих которого больше, чем в данном.
SELECT DEPT_NO, AVG (CURRENT_DATE – EMP_BDATE) FROM EMP WHERE DEPT_NO IS NOT NULL GROUP BY DEPT_NO HAVING AVG (CURRENT_DATE – EMP_BDATE)< SOME (SELECT AVG (CURRENT_DATE – EMP1.EMP_BDATE) FROM EMP EMP1 WHERE EMP1.DEPT_NO IS NOT NULL GROUP BY EMP1.DEPT_NO);
Напомним, что «ниладическая» функция CURRENT_DATE выдает текущую дату, и, следовательно, значением выражения CURRENT_DATE – EMP_BDATE является интервал, представляющий текущий возраст служащего. На наш взгляд, формулировка этого запроса несколько упрощается, если пользоваться предикат предикатом EXISTS (пример 19.11.1):
SELECT DEPT_NO, AVG (CURRENT_DATE – EMP_BDATE) FROM EMP WHERE DEPT_NO IS NOT NULL GROUP BY DEPT_NO HAVING EXISTS (SELECT EMP1.DEPT_NO FROM EMP EMP1 WHERE EMP1.DEPT_NO IS NOT NULL GROUP BY EMP1.DEPT_NO HAVING AVG (CURRENT_DATE – EMP1.EMP_BDATE) > AVG (CURRENT_DATE – EMP.EMP_BDATE));
Пример 19.12. Найти номера отделов и средний возраст служащих для отделов с минимальным средним возрастом служащих.
SELECT DEPT_NO, AVG (CURRENT_DATE – EMP_BDATE) FROM EMP WHERE DEPT_NO IS NOT NULL GROUP BY DEPT_NO HAVING AVG (CURRENT_DATE – EMP_BDATE) <= ALL (SELECT AVG (CURRENT_DATE – EMP_BDATE) FROM EMP WHERE DEPT_NO IS NOT NULL GROUP BY DEPT_NO);
Этот запрос легко формулируется в более понятном виде с использованием предиката NOT EXISTS (пример 19.12.1):
SELECT DEPT_NO, AVG (CURRENT_DATE – EMP_BDATE) FROM EMP WHERE DEPT_NO IS NOT NULL GROUP BY DEPT_NO HAVING NOT EXISTS (SELECT EMP1.DEPT_NO FROM EMP EMP1 WHERE EMP1.DEPT_NO IS NOT NULL GROUP BY EMP1.DEPT_NO HAVING AVG (CURRENT_DATE – EMP1.EMP_BDATE) < AVG (CURRENT_DATE – EMP.EMP_BDATE));
м году, когда мной был
История этого курса началась в 1995- м году, когда мной был подготовлен курс «Основы современных баз данных» для Центра Информационных Технологий (ЦИТ). Материалы этого курса были опубликованы в библиотеке CITForum.ru в 1996 г. и затем в течение ряда использовались мной для чтения лекций как в ЦИТ, так и на факультете ВМиК МГУ. Надеюсь, что они пригодились и многим другим читателям.
Однако со временем курс стал меняться. Если в середине 90-х гг. мне казались наиболее важными программистские аспекты организации СУБД, то потом постепенно на передний план стали выходить модельные и языковые аспекты баз данных. Материалы старого курса стали мне казаться несколько поверхностными и охватывающими слишком большое число тем. Захотелось большей строгости и большей глубины погружения в наиболее важные темы.
Кроме того, в конце 90-х гг. появилась технология объектно-реляционных баз данных, затверженная в стандарте SQL:1999. С появлением этого стандарта часть старого курса, посвященная SQL, совершено устарела. Я же все в большей степени начал склоняться к тому, что в стандарте SQL, по сути, определяется законченная модель данных, похожая на реляционную модель, но во многом от нее отличная.
В то же время начали выходить книги Дейта и Дарвена, посвященные их новой трактовке реляционной модели данных, которую они считают восходящей к исходным постулатам Эдгара Кодда и одновременно обеспечивающей все практические потребности, базируемые на модели данных SQL и объектно-ориентированной модели данных. Во многих отношениях идеи Дейта и Дарвена являются интересными и плодотворными.
Все это с годами впитывалось в курс лекций, читаемый для студентов факультета ВМиК. Одновременно накапливались материалы, часть из которых удавалось вместить в этот годовой курс, а часть оставалась на жестком диске моего компьютера в ожидании лучших времен.
В 2005 г. по инициативе Интернет-Университета Информационных технологий был издан курс лекций «Основы баз данных», и на основе этих материалов были созданы два электронных курса, доступных на сайте Intuit.ru.
В первом из этих курсов рассматривается реляционная модель данных, во втором – модель данных SQL. В 2007 г. бумажное издание с материалами этих двух курсов было переиздано с необходимыми исправлениями.
В 2008 г. в издательстве Бином-пресс вышел учебник «Базы данных. Языки и модели», в котором я попытался представить реляционную модель данных в ее сравнении и сопоставлении с моделью данных SQL, объектно-ориентированной моделью данных и «истинно реляционной» моделью Дейта и Дарвена. Мне нравится этот учебник, его можно с пользой использовать для самообразования и для подготовки курсов, но из-за перенасыщенности материалами его содержимое невозможно изложить в каком-либо курсе обозримой продолжительности.
Кроме того, в обоих случаях в материалах произошел перекос в сторону теории: там вообще исчезли лекции, посвященные методам и алгоритмам построения СУБД, которые в действительности студентам ВМиК читаются. В варианте курса, предлагаемому вашему вниманию, этот перекос исправляется. Большей частью этот материал базируется на варианте Intuit.ru (немного поправленном и заново отредактированном), к которому добавлены четыре лекции, первая из которых (Лекция 2) посвящена обзору наиболее популярных моделей данных, а в трех (Лекции 12-14) обсуждаются вопросы организации данных, управления транзакциями и журнализации изменений и восстановления баз данных после сбоев соответственно. В этом виде текст можно использовать непосредственно для чтения лекций (что я и делаю), хотя по-прежнему материал немного избыточен (это мне кажется совсем не вредным для электронного издания: много – не мало).
Представление в реляционной схеме супертипов и подтипов сущности
В этом подразделе мы предполагаем, что реляционная схема базы данных проектируется в расчете на использование обычной SQL-ориентированной СУБД, не поддерживающей объектно-реляционные расширения. Кстати, заметим, что поддержка таких расширений не слишком помогает при переходе от концептуальной схемы базы данных в модели «Сущность-Связь» к объектно-реляционной схеме, соответствующей последним стандартам языка SQL.
Если в концептуальной схеме (ER-диаграмме) присутствуют подтипы, то возможны два способа их представления в реляционной схеме: (a) собрать все подтипы в одной таблице; (b) для каждого подтипа образовать отдельную таблицу.
При применении способа (a) таблица создается для максимального супертипа (типа сущности, не являющегося подтипом), а для подтипов могут создаваться представления (см. лекцию 17). Таблица содержит столбцы, соответствующие каждому атрибуту (и связям) каждого подтипа. В таблицу добавляется, по крайней мере, один столбец, содержащий код ТИПА; он становится частью первичного ключа. Для каждой строки таблицы значение этого столбца определяет тип сущности, экземпляру которого соответствует строка. Столбцы этой строки, которые соответствуют атрибутам и связям, отсутствующим в данном типе сущности, должны содержать неопределенные значения.
При использовании метода (b) для каждого подтипа первого уровня (для более глубоких уровней применяется метод (a)) супертип воссоздается с помощью представления UNION (из всех таблиц подтипов выбираются общие столбцы – столбцы супертипа).
У каждого способа есть свои достоинства и недостатки. К достоинствам первого способа (одна таблица для супертипа и всех его подтипов) можно отнести следующее: соответствие логике супертипов и подтипов; поскольку любой экземпляр любого подтипа является экземпляром супертипа, логично хранить вместе все строки, соответствующие экземплярам супертипа;обеспечение простого доступа к экземплярам супертипа и не слишком сложный доступ к экземплярам подтипов;возможность обойтись небольшим числом таблиц.
Недостатки метода (a): прикладная программа, имеющая дело с одной таблицей супертипа, должна включать дополнительную логику работы с разными наборами столбцов (в зависимости от значения столбца ТИП) и разными ограничениями целостности (в зависимости от особенностей связей, определенных для подтипа);общая для всех подтипов таблица потенциально может стать узким местом при многопользовательском доступе по причине возможности блокировки таблицы целиком;для индивидуальных столбцов подтипов должна допускаться возможность содержать неопределенные значения; таким образом, потенциально в общей таблице будет содержаться много неопределенных значений, что при использовании некоторых РСУБД может потребовать значительного объема внешней памяти.
Достоинства метода (b) состоят в следующем: действуют более понятные правила работы с подтипами (каждому подтипу соответствует одноименная таблица);упрощается логика приложений; каждая программа работает только с нужной таблицей.
Недостатки метода (b): в общем случае требуется слишком много отдельных таблиц;работа с экземплярами супертипа на основе представления, объединяющего таблицы супертипов, может оказаться недостаточно эффективной;поскольку множество экземпляров супертипа является объединением множеств экземпляров подтипов, не все РСУБД могут обеспечить выполнение операций модификации экземпляров супертипа.
Представление в реляционной схеме взаимно исключающих связей
Существуют два способа формирования схемы реляционной БД при наличии взаимно исключающих связей (имеются в виду связи «один ко многим», причем конец связи «многие» находится на стороне сущности, для которой связи являются взаимно исключающими): (a) общее хранение внешних ключей; (b) раздельное хранение внешних ключей.
Понятно, что если имеются взаимно исключающие связи упомянутой категории, то в таблице, соответствующей сущности, для которой связи являются взаимно исключающими, необходимо хранить внешние ключи. Если внешние ключи всех потенциально связанных таблиц имеют общий формат, то можно применить способ (a), т. е. создать два столбца: идентификатор связи и идентификатор сущности (возможно, составной). Столбец идентификатора связи используется для различения связей, покрываемых дугой исключения. В столбце (столбцах) идентификатора сущности хранятся значения уникального идентификатора сущности на дальнем конце соответствующей связи.
Если результирующие внешние ключи не относятся к одному домену, то приходится прибегать к использованию способа (b), т. е. создавать для каждой связи, покрываемой дугой исключения, явные столбцы внешних ключей; все эти столбцы могут содержать неопределенные значения.
Преимущество подхода (a) состоит в том, что в таблице, соответствующей сущности, появляется всего два дополнительных столбца. Очевидным недостатком является усложнение выполнения операции соединения: чтобы воспользоваться для соединения одной из альтернативных связей, нужно сначала произвести ограничение таблицы в соответствии с нужным значением столбца, содержащего идентификаторы связей.
При использовании подхода (b) соединения являются явными (и естественными). Недостаток состоит в том, что требуется иметь столько столбцов, сколько имеется альтернативных связей. Кроме того, в каждом из таких столбцов будет содержаться много неопределенных значений, хранение которых потенциально может привести к серьезным накладным расходам внешней памяти.
Рис. 10.14. Возможные модификации ER-диаграмм, позволяющие избежать взаимно исключающих связей
Модификация, показанная на (b), основана на том наблюдении, что коль скоро связи являются альтернативными, то они разделяют множество экземпляров сущности A на два или более непересекающихся подмножества, которые могут лежать в основе определения подтипов A1 и A2. Это хороший вариант, если такие подтипы могут пригодиться еще для чего-нибудь. Например, в случае взаимно исключающей связи, представленной на , у исправных и неисправных самолетов могут имется несовпадающие множества атрибутов (скажем, у типа сущности ИСПРАВНЫЕ САМОЛЕТЫ может иметься атрибут дата завершения гарантийного срока, а у типа сущности НЕИСПРАВНЫЕ САМОЛЕТЫ – атрибут тип неисправности). С другой стороны, как отмечалось в предыдущем разделе, для использования этого подхода требуется возможность динамического изменения типа существующего экземпляра.
Модификация, показанная на (с), основана на том наблюдении, что коль скоро типы сущности B и C участвуют в альтернативной связи, то, по всей видимости, у этих сущностей имеется что-то общее. Возможно, их было бы правильнее определять как подтипы некоторого общего типа сущности. Заметим, что пример с явно демонстрирует, что далеко не всегда типы сущности, участвующие в альтернативной связи, обладают общими чертами. Создание общего супертипа для типов сущности ПИЛОТ и АВИАРЕМОНТНОЕ ПРЕДПРИЯТИЕ представляется весьма странной идеей.
На этом мы заканчиваем краткую экскурсию в семантическое моделирование с использованием ER-диаграмм.
Поскольку базовым элементом выражения запросов
Поскольку базовым элементом выражения запросов является спецификация запроса, прежде всего нужно понять, какой класс спецификаций запросов является допускающим операции обновления (термин updatable – обновляемый, используемый в стандарте SQL, кажется не слишком удачным в русском варианте). В стандарте SQL/92 спецификация запроса считалась допускающей операции обновления в том и только в том случае, когда выполнялись следующие условия: в разделе SELECT спецификации запроса отсутствует ключевое слово DISTINCT (т.е. не требуется удаление строк-дубликатов из результата запроса);все элементы списка выборки раздела SELECT являются именами столбцов, и ни одно имя столбца не встречается в этом списке более одного раза;в разделе FROM присутствует только одна ссылка на таблицу, и она указывает либо на базовую таблицу, либо на порождаемую таблицу, допускающую операции обновления;прямые или косвенные ссылки на базовую таблицу, прямо или косвенно идентифицируемую ссылкой на таблицу в разделе FROM, не встречаются в разделе FROM ни одного подзапроса, участвующего в разделе WHERE спецификации запроса;в спецификации запроса отсутствуют разделы GROUP BY и HAVING.
Нетрудно убедиться в том, что эти требования являются достаточными для однозначной интерпретации операций обновления над представлениями. Например, пусть имеется следующая спецификация запроса (пример 21.9):
SELECT EMP_SAL FROM (SELECT EMP_SAL, DEPT_NO FROM EMP WHERE EMP_NAME = (SELECT EMP_NAME FROM EMP WHERE EMP_NO = 4425)) WHERE DEPT_NO <> 630;
Эту спецификацию можно упростить до эквивалентной формулировки: SELECT EMP_SAL FROM EMP WHERE EMP_NAME = (SELECT EMP_NAME FROM EMP WHERE EMP_NO = 4425 ) AND DEPT_NO <> 630;
Предположим, что с данной спецификацией запроса связано представление с именем EMPSAL. Тогда операция
UPDATE EMPSAL SET EMP_SAL = EMP_SAL – 1000.00;
эквивалентна операции
UPDATE EMP SET EMP_SAL = EMP_SAL – 1000.00 WHERE EMP_NAME = (SELECT EMP_NAME FROM EMP WHERE EMP_NO = 4425 ) AND DEPT_NO <> 630;
В стандарте SQL: 1999 правила применимости операций обновления к спецификации запроса существенно уточнены.
Операция
DELETE FROM EMPSAL WHERE EMP_SAL > 20000.00;
эквивалентна операции DELETE EMPSAL WHERE EMP_SAL > 20000.00 AND EMP_NAME = (SELECT EMP_NAME FROM EMP WHERE EMP_NO = 4425 ) AND DEPT_NO <> 630;
Операция вставки над представлением EMPSAL
INSERT INTO EMPSAL 25000.00;
трактуется как INSERT INTO EMP ROW (DEFAULT, DEFAULT, DEFAULT, 25000.00, DEFAULT, DEFAULT);
Понятно, что такая операция будет отвергнута системой, потому что для столбца EMP_NO таблицы EMP значения по умолчанию не определены (это первичный ключ таблицы, значения которого должны явно задаваться в любой операции вставки).
С другой стороны, условия допустимости операций обновления, специфицированные в SQL/92, не являются необходимыми. Например, над представлением EMPMNG, определенным над спецификацией запроса («выбрать данные о служащих, являющихся руководителями отделов») SELECT * FROM EMP WHERE EXISTS (SELECT * FROM DEPT WHERE DEPT_MNG = EMP_NO);
можно было бы совершенно корректно выполнять операции обновления (с некоторыми оговорками насчет операции вставки; см. ниже в этом разделе).
Представления, над которыми возможны операции обновления
В подразделе лекции 17 было введено понятие представления (VIEW). Кратко повторим, что представление – это сохраняемое в каталоге базы данных выражение запросов, обладающее собственным именем и, возможно, собственными именами столбцов. Для удобства повторим синтаксические правила определения представления:
create_view ::= CREATE [ RECURSIVE ] VIEW table_name [ column_name_comma_list ] AS query_expression [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
В операциях выборки к любому представлению можно адресоваться таким же образом, как и к любой базовой таблице. Естественно, возникает вопрос: а можно ли использовать имена представлений и в операциях обновления базы данных и если такая возможность допускается, то как это следует понимать?
Напомним, что в соответствии с семантикой языка SQL при выполнении запроса, в разделе FROM которого прямо или косвенно присутствует имя представления, прежде всего, производится материализация представления, т.е. вычисляется результат соответствующего выражения запросов, сохраняется во временной базовой таблице, и далее запрос выполняется по отношению к этой базовой таблице. Хотя в реализациях SQL обычно стремятся избегать материализации представлений, любая реализация обязана обеспечить такое выполнение запроса над представлением, которое было бы эквивалентно выполнению запроса с явной материализацией представления.
Если допустить выполнение над представлениями операций обновления (сразу заметим, что, вообще говоря, в языке SQL это всегда разрешалось), то в этом случае семантика материализации явно не подходит. На первое место выходит требование, чтобы операция обновления над представлением однозначно отображалась в одну или несколько операций обновления над теми постоянно хранимыми базовыми таблицами, над которыми прямо или косвенно определено данное представление.
Представляемые таблицы, или представления (VIEW)
Еще одним примером рекурсивности спецификаций языка SQL является то, что в конце этой лекции мы вынуждены прервать обсуждение оператора выборки и ввести понятие представляемой таблицы, или представления, которую можно использовать в операторе выборки наряду с базовыми таблицами. Только после этого можно будет считать обсуждение ссылок на таблицы в разделе FROM условно завершенным. Итак, оператор создания представления в общем случае определяется следующими синтаксическими правилами:
create_view ::= CREATE [ RECURSIVE ] VIEW table_name [ column_name_comma_list ] AS query_expression [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
Рекурсивные представления (такие, в определении которых присутствует ключевое слово RECURSIVE) и необязательный раздел WITH CHECK OPTION мы обсудим в лекции 21 (пока лишь заметим, что этот раздел связан с особенностями выполнения операций обновления базы данных через представления). Здесь мы кратко рассмотрим только простую форму представлений, определяемых по следующим правилам:
create_view ::= CREATE VIEW table_name [ column_name_comma_list ] AS query_expression
Имя таблицы, задаваемое в определении представления, существует в том же пространстве имен, что и имена базовых таблиц, и, следовательно, должно отличаться от всех имен таблиц (базовых и представляемых), созданных тем же пользователем. Если имя представления встречается в разделе FROM какого-либо оператора выборки, то вычисляется выражение запроса, указанное в разделе AS, и оператор выборки работает с результирующей таблицей этого выражения запроса. Явное указание имен столбцов представляемой таблицы требуется в том случае, когда эти имена не выводятся из соответствующего выражения запроса.
Как и для всех других вариантов оператора CREATE, для CREATE VIEW имеется обратный оператор DROP VIEW table_name, выполнение которого приводит к отмене определения представления (реально это выражается в удалении данных о представлении из таблиц-каталогов базы данных). После выполнения операции пользоваться представлением с данным именем становится невозможно.
Преобразование задаваемых пользователем ссылочных значений к ссылочному типу
В этом случае в определении структурного типа может присутствовать конструкция ref_cast_option (вернее, она должна присутствовать в определении соответствующего максимального супертипа). Синтаксис этой конструкции приводился в предыдущем разделе, но для удобства мы его повторим здесь:
ref_cast_option ::= cast_to_ref | cast_to_type cast_to_ref ::= CAST ( SOURCE AS REF ) WITH identifier cast_to_type ::= CAST ( REF AS SOURCE ) WITH identifier
Чтобы пояснить эту конструкцию, предположим, что в определении структурного типа указано REF USING INTEGER. Тогда соответствующие приложения отвечают за то, чтобы обеспечить глобально уникальные целые значения самоссылающегося столбца во всех строках всех типизированных таблиц, ассоциированных с этим структурным типом. Но приложения обеспечивают значения целого типа, а типом данных самоссылающегося столбца является некоторый ссылочный тип.
Для решения именно этой проблемы и предназначена конструкция ref_сast_option. В этой конструкции вводятся имена двух SQL-функций, первая из которых служит для преобразования ссылочных значений, обеспечиваемых приложением, к соответствующему REF-типу при вставке или обновлении строк типизированной таблицы (SOURCE AS REF). Вторая функция преобразует значения REF-типа к соответствующему встроенному типу данных при выборке строк из типизированной таблицы (REF AS SOURCE). Система автоматически генерирует обе подпрограммы, и конструкция ref_сast_option позволяет лишь назначить подпрограммам имена. Если конструкция ref_сast_option явно не включается в определение структурного типа с REF USING predefined_type, то имена подпрограммам назначаются системой. Единственным преимуществом явного назначения имен является возможность явного вызова этих функций при написании SQL-операторов, содержащих выражения REF-типа, которые нужно привести к соответствующему встроенному типу. Заметим, что такие функции невозможно написать вручную, поскольку правила отображения зависят от реализации SQL.
Если для структурного типа выбирается альтернатива порождения ссылочных значений, то система использует для порождения ссылочных значений значения неявно указанных столбцов (соответствующих явно указанным атрибутам ассоциированного структурного типа). При этом остаются все упомянутые выше проблемы, хотя в таком случае явно требуется объявление ограничений PRIMARY KEY или UNIQUE для соответствующего набора столбцов.
Наконец, при выборе последней альтернативы (системно-генерируемые ссылочные значения) каждой строке, вставляемой в типизированную таблицу, ассоциированную с соответствующим структурным типом, присваивается уникальный идентификатор. Это значение сохраняется в самоссылающемся столбце и может быть использовано любым приложением для уникальной идентификации данной строки на всем протяжении жизни таблицы.
Приближенные числовые типы
К категории приближенных числовых типов в SQL относятся те типы, значения которых представляют числа приближенным образом. Приближенные числа представляются в виде пары <мантисса, порядок>, где мантисса состоит из значащих цифр числа, а порядок определяет реальный размер числа. В реализациях приближенным числовым типам SQL обычно соответствуют типы с плавающей точкой. В SQL поддерживаются три варианта приближенных числовых типов. Тип REAL. Значения типа соответствуют числам с плавающей точкой одинарной точности. Точность определяется в реализации, но обычно совпадает с точностью одинарной плавающей арифметики, поддерживаемой на аппаратной платформе, которая используется реализацией. При определении столбца указывается просто REAL.Тип DOUBLE PRECISION. Точность значений этого типа определяется в реализации, но она должна быть больше точности типа REAL. Обычно приближенным числам SQL с двойной точностью соответствуют поддерживаемые аппаратурой числа с плавающей точкой двойной точности. При определении столбца указывается просто DOUBLE PRECISION.Тип FLOAT. Это параметризуемый тип, значение параметра p которого задает необходимую точность значений. Требуется, чтобы реально обеспечиваемая реализацией точность значений была не меньше p. Допустимый диапазон значений параметра p определяется в реализации. При определении столбца можно указать либо FLOAT (p), либо просто FLOAT. В последнем случае подразумевается точность, определяемая реализацией по умолчанию.Литералы приближенных числовых типов представляются в виде литерала точного числового типа, за которым могут следовать символ «E» и литерал целого числового типа. Примеры литералов приближенных числовых типов: 123, 123.12, 123E12, 123.12E12. Литеральное выражение xEy представляет значение x*(10y).
Применение идентификаторов пользователей и имен ролей
В этом подразделе мы вынуждены использовать понятие SQL-сессии, которое более последовательно обсуждается в третьем основном разделе лекции . Как и в предыдущих лекциях, посвященных языку SQL, мы можем оправдать подобную нелогичность только рекурсивной природой стандарта SQL.
Итак, в любой момент заданная SQL-сессия ассоциируется с идентификатором пользователя, называемым идентификатором пользователя SQL-сессии и с именем роли, называемым именем роли SQL-сессии. Почти всегда привилегии, связанные с этими идентификатором и именем, используются для определения допустимости выполнения различных операций во время данной сессии.
В стандарте не специфицированы все способы ассоциирования authID с SQL-сессией. Определено лишь то, что если сессия образуется с помощью оператора CONNECT (см. раздел ), то authID указывается в качестве параметра соответствующей операции. Для реализаций SQL допускается, чтобы пользовательский идентификатор SQL-сессии совпадал с регистрационным именем пользователя с точки зрения операционной системы или являлся идентификатором, специально устанавливаемым специалистами организации, ответственными за обеспечение безопасности. Кроме того, допускается наличие в реализации оператора SET SESSION AUTHORIZATION, применение которого приводит к смене пользовательского идентификатора SQL-сессии. В начале SQL-сессии значение текущего идентификатора пользователя SQL-сессии совпадает со значением пользовательского идентификатора SQL-сессии, и такая ситуация сохраняется до тех пор, пока пользовательский идентификатор SQL-сессии не будет каким-либо образом изменен. Значение текущего пользовательского идентификатора SQL-сессии возвращается вызовом ниладической функции SESSION_USER (лекция 17).
Для каждой SQL-сессии существует также текущее имя роли (это имя можно получить путем вызова функции CURRENT_ROLE). Сразу после образования сессии текущему имени роли соответствует неопределенное значение, что трактуется как «роль для сессии не назначена». Имеется несколько способов подмены пользовательского идентификатора и/или имени роли SQL-сессии. При этом если задается идентификатор пользователя, то одновременно полагается, что неявно указывается имя роли, имеющее неопределенное значение. Если же задается имя роли, то, за несколькими исключениями, считается, что неявно указывается идентификатор пользователя, имеющий неопределенное значение. Более подробно мы обсудим подобные возможности ниже в этом разделе.
Если либо текущий идентификатор пользователя, либо текущее имя роли содержат неопределенное значение, то тот идентификатор или то имя, у которого значение не является неопределенным, используется в качестве текущего authID SQL-сессии. Если ни текущий идентификатор, ни текущее имя не содержат NULL, то текущим authID сессии служит текущий идентификатор пользователя.
Как и везде в этом курсе, мы опустим детали, относящиеся к особенностям авторизации при использовании встраиваемого и динамического SQL.
Примеры инвариантов
В заключение обзора языка OCL приведем примеры четырех инвариантов, выраженных на этом языке. Будем основываться на диаграмме классов, показанной на .
Рис. 11.14. Диаграмма классов, используемая для примеров на языке OCL
Пример 11.1. Определить ограничение «возраст служащих должен быть больше 18 и меньше 100 лет».
context Служащий inv: self.возраст > 18 and self.возраст < 100
Условие инварианта накладывает требуемое ограничение на значения атрибута возраст, определенного в классе Служащий. В условном выражении инварианта ключевое слово self обозначает текущий объект класса-контекста инварианта. Можно считать, что при проверке данного условия будут перебираться существующие объекты класса Служащий, и для каждого объекта будет проверяться, что значения атрибута возраст находятся в пределах заданного диапазона. Ограничение удовлетворяется, если условное выражение принимает значение true для каждого объекта класса-контекста.
Пример 11.2. Выразить на языке OCL ограничение, в соответствии с которым в отделах с номерами больше 5 должны работать служащие старше 30 лет.
context Отдел inv: self.номер
В этом случае условное выражение инварианта будет вычисляться для каждого объекта класса Отдел. Подвыражение справа от операции or вычисляется слева направо. Сначала вычисляется подвыражение self.служащий, значением которого является множество объектов, соответствующих служащим, которые работают в текущем отделе. Далее к этому множеству применяется операция select (возраст 30), в результате которой вырабатывается множество объектов, соответствующих служащим текущего отдела, возраст которых не превышает 30 лет. Значением операции size () является число объектов в этом множестве. Все выражение принимает значение true, если последняя операция сравнения «=0» вырабатывает значение true, т. е. если в текущем отделе нет служащих младше 31 года. Ограничение в целом удовлетворяется только в том случае, если значением условия инварианта является true для каждого отдела.
Тот же инвариант можно сформулировать в контексте класса Сотрудник:
context Сотрудник inv: self.возраст > 30 or self.отдел.номер
Здесь следует обратить внимание на подвыражение self.отдел.номер
Пример 11.3. Определить ограничение, в соответствии с которым у каждого отдела должен быть менеджер, и любой отдел должен быть основан не раньше соответствующей компании:
context Отдел inv: self.служащий
Здесь должность – атрибут класса Служащий, а атрибуты с именем годОснования имеются и у класса Отдел, и у класса Компания. В условном выражении этого инварианта подвыражение self.служащий
Обратите внимание, что в этом случае снова законным является подвыражение self.компания.годОснования, поскольку кратность роли компания в ассоциации классов Отдел и Компания равна единице.
Пример 11.4. Условие четвертого инварианта ограничивает максимально возможное количество служащих компании числом 1000:
context Компания inv: self.отдел
Здесь полезно обратить внимание на использование операции collect. Проследим за вычислением условного выражения. В нашем случае в классе Компания всего один объект, и он сразу становится текущим. В результате выполнения операции self.отдел будет получено множество объектов, соответствующих всем отделам компании.При выполнении операции collect (служащие) для каждого объекта-отдела по соединению с объектами класса СЛУЖАЩИЕ будет образовано множество объектов-служащих данного отдела, а в результате будет образовано множество объектов, соответствующих всем служащим всех отделов компании, т. е. всем служащим компании.
Примеры изменения набора табличных ограничений
Напомним, что мы добавили к таблице EMP столбец EMP_BONUS, в котором сохраняются размеры ежемесячных премий служащих. Предположим, что премии выплачиваются из фонда заработной платы отдела, в котором работает служащий. Тогда проверочное ограничение столбца DEPT_TOTAL_SAL, устанавливающее, что объем фонда зарплаты отдела не должен быть меньше суммарной зарплаты служащих этого отдела, становится недостаточным, и нам требуется добавить к набору ограничений таблицы DEPT новое ограничение:
ALTER TABLE DEPT ADD CONSTRAINT TOTAL_INCOME CHECK (DEPT_TOTAL_SAL >= (SELECT SUM(EMP_SAL + COALESCE(EMP_BONUS,0)) FROM EMP WHERE EMP.DEPT_NO = DEPT_NO)).
Хотя это ограничение на вид довольно сложное, смысл его очень прост: суммарный доход служащих отдела не должен превышать объем зарплаты отдела. В арифметическом выражении под знаком агрегатной операции SUM используется операция COALRSCE. Эта двуместная операция определяется следующим образом:
COALESCE (x, y) IF x IS NOT NULL THEN x ELSE y,
т. е. значением операции является значение первого операнда, если оно не равно NULL, и значение второго операнда – в противном случае. Нам пришлось воспользоваться этой операцией, поскольку в столбце EMP_BONUS допускается наличие неопределенных значений.
Понятно, что новое ограничение столбца DEPT_TOTAL_SAL сильнее предыдущего, и это предыдущее ограничение можно было бы отменить. Конечно, с логической точки зрения наличие обоих ограничений ничему не повредит (предыдущее ограничение является логическим следствием нового), но при использовании не слишком интеллектуальной реализации SQL может привести к замедлению работы системы, поскольку оба ограничения могут проверяться независимо. К сожалению, при определении таблицы EMP мы не присвоили явное имя проверочному ограничению столбца DEPT_TOTAL_SAL и поэтому не можем немедленно продемонстрировать оператор отмены этого ограничения. Это не значит, что его нельзя отменить вообще. В стандарте языка SQL требуется, чтобы ограничения целостности, которым не назначены явные имена, получали имена, автоматически генерируемые системой.
Любой квалифицированный пользователь SQL-ориентированной СУБД ( скорее всего, администратор) может обнаружить имя любого ограничения, обратившись к системной таблице-каталогу ограничений целостности.
Кстати, новому ограничению мы присвоили явное имя. К этому привели следующие рассуждения. Когда создавалась исходная схема базы данных, руководство предприятия ничего не говорило о премиях служащих. Теперь начальство решило, что премии будут выплачиваться из фонда зарплаты. Для этого, мы добавили новый столбец и новое ограничение целостности. Но кто знает, не изменится ли снова решение о премиях? Чтобы не добавлять себе работы в будущем, дадим новому ограничению явное имя и не будем отменять предыдущее ограничение.
При определении таблицы EMP было специфицировано проверочное табличное ограничение PRO_EMP_NO, устанавливающее, что над одним проектом не должно работать более 50 служащих. Мы уже отмечали, что это ограничение носит чисто административный характер и может быть отменено без нарушения логики базы данных. Для отмены ограничения нужно выполнить следующий оператор:
ALTER TABLE EMP DROP CONSTRAINT PRO_EMP_NO;
Другими словами, это естественное ограничение требует, чтобы значения столбца DEPT_EMP_NO были «правильными», т.е. действительно соответствовали числу служащих, работающих в данном отделе.
По этой причине мы ввели в предыдущей лекции такую большую верхнюю границу – 20000000.00 – значений домена SALARY.
Другими словами, это естественное ограничение требует, чтобы размер фонда заработной платы отдела никогда не был меньше суммарной зарплаты, получаемой служащими этого отдела.
Не считая те табличные ограничения целостности, которые (a) определены в составе определения базовой таблицы, содержащей данный столбец и (b) не содержат ссылок на какие-либо другие столбцы.
Хотя формально требуется указывать одно из этих ключевых слов в любом действии DROP CONSTRAINT.
Не следует расценивать эти рассуждения как руководство к действию. Мы привели их только для того, чтобы обосновать пример, хотя рассуждения, конечно, не лишены смысла.
Примеры изменения определения домена
Немного поупражняемся с доменом SALARY. Для изменения значения заработной платы по умолчанию с 10000 на 11000 руб. нужно выполнить оператор
ALTER DOMAIN SALARY SET DEFAULT 11000.00;
Для отмены значения по умолчанию в домене SALARY следует воспользоваться оператором
ALTER DOMAIN SALARY DROP DEFAULT;
Если к определению домена SALARY требуется добавить ограничение (например, запретить значение зарплаты, равное 15000 руб.), необходимо выполнить оператор
ALTER DOMAIN SALARY ADD CHECK (VALUE <> 15000.00);
Наконец, если требуется отменить (именованное!) ограничение целостности, препятствующее наличию неопределенных значений в столбцах, которые определены на домене SALARY, то нужно выполнить оператор
ALTER DOMAIN SALARY DROP CONSTRAINT SAL_NOT_NULL;
Примеры изменения определения столбца
Предположим, что на предприятии ввели систему премирования служащих. Каждый служащий может дополнительно к зарплате получать ежемесячную премию, не превышающую размер его зарплаты. Тогда разумно добавить к таблице EMP новый столбец EMP_BONUS, используя оператор ALTER TABLE:
ALTER TABLE EMP ADD EMP_BONUS SALARY DEFAULT NULL CONSTRAINT BONSAL CHECK (VALUE < EMP_SAL);
Обратите внимание, что мы присвоили проверочному ограничению столбца явное имя, чтобы в случае, если ограничения на размер премии изменятся (что вполне возможно), можно было бы легко отменить это ограничение, воспринимая его как табличное.
При определении столбца EMP_SAL таблицы EMP для этого столбца явно не определялось значение по умолчанию (оно наследовалось из определения домена). Если в какой-то момент это стало неправильным (например, повысился размер минимальной зарплаты), можно установить новое значение по умолчанию:
ALTER TABLE EMP ALTER EMP_SAL SET DEFAULT 15000.00.
При определении столбца DEPT_TOTAL_SAL таблицы DEPT для него было установлено значение по умолчанию 1000000. Главный бухгалтер предприятия может быть недоволен тем, что такие важные данные, как объем фонда зарплаты отделов, могут устанавливаться по умолчанию. Тогда можно отменить это значение по умолчанию:
ALTER TABLE DEPT ALTER DEPT_TOTAL_SAL DROP DEFAULT.
Обратите внимание, что после выполнения этого оператора при вставке новой строки в таблицу DEPT всегда потребуется явно указывать значение столбца DEPT_TOTAL_SAL. Хотя формально у столбца будет существовать значение по умолчанию, наследуемое от домена SALARY (10000.00), оно не может быть занесено в таблицу DEPT, поскольку противоречит ограничению столбца DEPT_TOTAL_SAL CHECK (VALUE >= 100000.00).
Можно задуматься, действительно ли требуется поддерживать в таблице DEPT столбец DEPT_EMP_NO. Как мы видели, для его поддержки требуется проверять громоздкое ограничение целостности, а число служащих в любом отделе можно получить динамически с помощью простого запроса к таблице EMP (собственно, этот запрос входит в ограничение целостности). Поэтому может оказаться разумным отменить определение столбца DEPT_EMP_NO, выполнив следующий оператор ALTER TABLE:
ALTER TABLE DEPT DROP DEPT_EMP_NO CASCADE.
Напомним, что спецификация CASCADE ведет к тому, что при выполнении оператора будет уничтожено не только определение указанного столбца, но и определения всех ограничений целостности и представлений, в которых используется уничтожаемый столбец. В нашем случае единственное связанное с этим столбцом ограничение целостности, определенное вне определения столбца, было бы отменено, даже если бы в операторе отмены определения столбца DEPT_EMP_NO содержалась спецификация RESTRICT, поскольку это единственное внешнее определение ограничения является ограничением только столбца DEPT_EMP_NO.
Примеры определений базовых таблиц
Определим таблицы служащих (EMP), отделов (DEPT) и проектов (PRO). Эти таблицы имеют заголовки, показанные на .
Рис. 16.1. Заголовки таблиц EMP, DEPT и PRO
Столбцы EMP_NO, EMP_SAL, DEPT_NO, PRO_NO, DEPT_TOTAL_SAL, DEPT_MNG и PRO_MNG определяются на ранее определенных доменах (определения доменов EMP_NO и SALARY приведены в предыдущей лекции). Первичными ключами отношений EMP, DEPT и проектов PRO являются столбцы EMP_NO, DEPT_NO и PRO_NO соответственно. В таблице EMP столбцы DEPT_NO и PRO_NO являются внешними ключами, указывающими на отдел, в котором работает служащий, и на выполняемый им проект соответственно. В таблице DEPT внешним ключом является столбец DEPT_NO, указывающий на служащего, являющегося руководителем соответствующего отдела, а в таблице PRO внешним ключом является столбец PRO_MNG, указывающий на служащего, являющегося менеджером соответствующего проекта. Другие ограничения целостности мы обсудим позже.
Определим таблицу EMP:
(1) CREATE TABLE EMP ( (2) EMP_NO EMP_NO PRIMARY KEY, (3) EMP_NAME VARCHAR(20) DEFAULT 'Incognito' NOT NULL, (4) EMP_BDATE DATE DEFAULT NULL CHECK ( VALUE >= DATE '1917-10-24'), (5) EMP_SAL SALARY, (6) DEPT_NO DEPT_NO DEFAULT NULL REFERENCES DEPT ON DELETE SET NULL, (7) PRO_NO PRO_NO DEFAULT NULL, (8) FOREIGN KEY PRO_NO REFERENCES PRO (PRO_NO) ON DELETE SET NULL, (9) CONSTRAINT PRO_EMP_NO CHECK ((SELECT COUNT (*) FROM EMP E WHERE E.PRO_NO = PRO_NO) <= 50));
Последовательно обсудим части этого определения. В части (1) указывается, что создается таблица с именем EMP. В части (2) определяется столбец EMP_NO на домене EMP_NO. У этого столбца не определено значение по умолчанию, и он объявлен первичным ключом таблицы (это ограничение целостности добавляется через AND к ограничениям, унаследованным столбцом от определения домена). Помимо прочего, это означает неявное указание запрета для данного столбца неопределенных значений. В части (3) определен столбец EMP_NAME на базовом типе данных символьных строк переменной длины с максимальной длиной 20.
Для столбца указано значение по умолчанию – строка 'Incognito', и в качестве ограничения целостности запрещены неопределенные значения. В части (4) определяется столбец EMP_BDATE (дата рождения служащего). Он имеет тип данных DATE, значением по умолчанию является NULL (даты рождения некоторых служащих неизвестны). Кроме того, ограничение столбца запрещает принимать на работу лиц, о которых известно, что они родились до Октябрьского переворота. В части (5) определен столбец EMP_SAL на домене SALARY. Значение по умолчанию и ограничения целостности наследуются из определения домена. В части (6) столбец DEPT_NO определяется на одноименном домене (для наших целей его определение несущественно), но явно объявляется, что значением по умолчанию этого столбца будет NULL (некоторые служащие не приписаны ни к какому отделу). Кроме того, добавляется ограничение внешнего ключа: столбец DEPT_NO ссылается на первичный ключ таблицы DEPT. Определено ссылочное действие: при удалении строки из таблицы DEPT во всех строках таблицы EMP, ссылавшихся на эту строку, столбцу DEPT_NO должно быть присвоено неопределенное значение. В части (7) определяется столбец PRO_NO. Его определение аналогично определению столбца DEPT_NO, но ограничение внешнего ключа вынесено в часть (8), где оно определяется в полной форме как табличное ограничение. Наконец, в части (9) определяется табличное проверочное ограничение с именем PRO_EMP_NO, которое требует, чтобы ни в одном проекте не участвовало больше 50 служащих (правила построения соответствующего условного выражения поясняются в лекции 18).
Определим таблицу DEPT:
(1) CREATE TABLE DEPT ( (2) DEPT_NO DEPT_NO PRIMARY KEY, (3) DEPT_EMP_NO INTEGER NO NULL CHECK ( VALUE BETWEEN 1 AND 100), (4) DEPT_NAME VARCHAR(200) DEFAULT 'Nameless' NOT NULL, (5) DEPT_TOTAL_SAL SALARY DEFAULT 1000000.00 NO NULL CHECK (VALUE > = 100000.00), (6) DEPT_MNG EMP_NO DEFAULT NULL REFERENCES EMP ON DELETE SET NULL CHECK (IF (VALUE IS NOT NULL) THEN ((SELECT COUNT(*) FROM DEPT WHERE DEPT.DEPT_MNG = VALUE) = 1), (7) CHECK (DEPT_EMP_NO = (SELECT COUNT(*) FROM EMP WHERE DEPT_NO = EMP.DEPT_NO)), (8) CHECK (DEPT_TOTAL_SAL >= (SELECT SUM(EMP_SAL) FROM EMP WHERE DEPT_NO = EMP.DEPT_NO)));
Это определение мы обсудим в менее систематической манере, чем предыдущее. Отметим только наиболее интересные моменты. В части (3) столбец DEPT_EMP_NO (число служащих в отделе) определен на базовом типе INTEGER без значения по умолчанию, с запретом неопределенного значения и с проверочным ограничением, устанавливающем допустимый диапазон значений числа служащих в отделе. Еще одно проверочное ограничение этого столбца – (7) – вынесено на уровень определения табличного ограничения. Это ограничение устанавливает, что в каждой строке таблицы DEPT значение столбца DEPT_EMP_NO должно равняться общему числу строк таблицы EMP, в которых значение столбца DEPT_NO равно значению одноименного столбца данной строки таблицы DEPT. В части (5) для определения столбца DEPT_TOTAL_SAL (объем фонда заработной платы отдела) используется домен SALARY. Но при этом явно установлено значение столбца по умолчанию (отличное от значения по умолчанию домена), запрещено наличие неопределенных значений и введено дополнительное проверочное ограничение, определяющее нижний порог объема фонда заработной платы отдела. Еще одно проверочное ограничение – (8) – вынесено на уровень определения табличного ограничения. Это ограничение устанавливает, что в каждой строке таблицы DEPT значение столбца DEPT_TOTAL_SAL должно быть не меньше суммы значений столбца EMP_SAL во всех строках таблицы EMP, в которых значение столбца DEPT_NO равно значению одноименного столбца данной строки таблицы DEPT. Обратите внимание на определение столбца DEPT_MNG – часть (6). Этот столбец объявляется внешним ключом таблицы DEPT. Но мы хотим сказать больше. У отдела могут временно отсутствовать руководители, поэтому в столбце допускаются неопределенные значения. Но если у отдела имеется руководитель, то он должен являться руководителем только этого отдела. На первый взгляд можно было бы воспользоваться ограничением столбца UNIQUE. Но такое ограничение допускало бы наличие неопределенного столбца DEPT_MNG только в одной строке таблицы DEPT, а мы хотим допустить отсутствие руководителя у нескольких отделов.
Поэтому потребовалось ввести более громоздкое проверочное ограничение столбца.
По поводу двух приведенных определений базовых таблиц у читателей могут возникнуть два вопроса: (a) почему проверочное ограничение (9) в первом определении и проверочные ограничения (7) и (8) во втором определении вынесены из определений соответствующих столбцов, хотя формально являются именно ограничениями столбцов?(b) почему ограничению (9) в первом определении присвоено явное имя, а ограничения (7) и (8) во втором определении оставлены безымянными?
На первый вопрос можно ответить следующим образом. Да, эти ограничения можно было бы включить в определения столбцов. Это дело вкуса. Но все три ограничения являются очень важными с точки зрения организации таблиц в целом. Поэтому лучше показывать их на уровне определения табличных ограничений.
Вот ответ на второй вопрос. Ограничение (9) в первом определении и ограничения (7) и (8) во втором определении внешне похожи, но сильно отличаются по своей сути. Ограничения (7) и (8) связаны с агрегатной семантикой столбцов DEPT_EMP_NO и DEPT_TOTAL_SAL таблицы DEPT. Отмена ограничений изменила бы смысл этих столбцов. Ограничение (9) является текущим административным ограничением. Если руководство предприятия примет решение разрешить использовать в одном проекте более 50 служащих, ограничение можно отменить без изменения смысла столбцов таблицы EMP. Имея это в виду, мы ввели явное имя ограничения (9), чтобы при необходимости имелась простая возможность отменить это ограничение с помощью оператора ALTER TABLE.
Наконец, определим таблицу PRO.
(1) CREATE TABLE PRO ( (2) PRO_NO PRO_NO PRIMARY KEY, (3) PRO_TITLE VARCHAR(200)DEFAULT 'No title' NOT NULL, (4) PRO_SDATE DATE DEFAULT CURRENT_DATE NOT NULL, (5) PRO_DURAT INTERVAL YEAR DEFAUL INTERVAL '1' YEAR NOT NULL, (6) PRO_MNG EMP_NO UNIQUE NOT NULL REFERENCES EMP ON DELETE NO ACTION, (7) PRO_DESC CLOB(10M));
Столбец PRO_SDATE содержит дату начала проекта, а столбец PRO_DURAT – продолжительность проекта в годах.В этом определении имеет смысл прокомментировать часть (6). Мы считаем, что если отдел, по крайней мере временно, может существовать без руководителя, то у проекта всегда должен быть менеджер. Поэтому определение столбца PRO_MNG является гораздо более строгим, чем определение столбца DEPT_MNG в таблице DEPT. Сочетание ограничений UNIQUE и NOT NULL при отсутствии значений по умолчанию приводит к абсолютной уникальности значений столбца PRO_MNG. Другими словами, этот столбец обладает всеми характеристиками первичного ключа, хотя объявлен только как возможный ключ. Кроме того, он объявлен как внешний ключ с действием при удалении строки таблицы EMP с соответствующим значением первичного ключа NO ACTION, запрещающим такие удаления. В совокупности это гарантирует, что у любого проекта будет существовать менеджер, являющийся служащим предприятия. В части (5) столбец PRO_DESC (описание проекта) определен как большой символьный объект с максимальным размером 10 Мбайт.
Примеры определений доменов
В дальнейших примерах нам понадобятся определения нескольких доменов. Приведем их в этом подразделе. В примерах мы будем иметь дело с таблицами служащих (EMP), отделов (DEPT) и проектов (PRO). Каждый служащий обладает уникальным номером (EMP_NO) и получает заработную плату (SALARY). Определим домены EMP_NO и SALARY.
CREATE DOMAIN EMP_NO AS INTEGER CHECK (VALUE BETWEEN 1 AND 10000);
Номера служащих являются целыми числами, поэтому базовый тип домена EMP_NO есть тип INTEGER. Кроме того, на значения этого домена устанавливается следующее ограничение: они должны быть больше нуля и не превосходить целое значение 10000.
Домен SALARY определим следующим образом:
CREATE DOMAIN SALARY AS NUMERIC (10, 2) DEFAULT 10000.00 CHECK (VALUE BETWEEN 10000.00 AND 20000000.00) CONSTRAINT SAL_NOT_NULL CHECK (VALUE IS NOT NULL);
Размер заработной платы является значением точного числового типа NUMERIC из десяти десятичных цифр, две из которых составляют дробную часть. По умолчанию размер заработной платы составляет 10000 руб. Установлен диапазон допустимого размера зарплаты от 10000 руб. до 20000000 руб. Неопределенное значение зарплаты не допускается (на уровне определения домена).
Примеры результатов действия раздела WITH CHECK OPTION
Чтобы пояснить результаты действия раздела WITH CHECK OPTION, допустим, что в базе данных присутствуют определения двух представлений MIDDLE_RICH_EMP и MORE_RICH_EMP: CREATE VIEW MIDDLE_RICH_EMP AS SELECT * FROM EMP WHERE EMP_SAL < 20000.00 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ];
CREATE VIEW MORE_RICH_EMP AS SELECT * FROM MIDDLE_RICH_EMP WHERE EMP_SAL > 18000.00 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ];
Очевидно, что в тело (материализованного) представления MIDDLE_RICH_EMP будут входить следующие строки базовой таблицы EMP:
| 2440 | 1 | 1950 | 15000.00 |
| 2441 | 1 | 1950 | 16000.00 |
| 2442 | 1 | 1960 | 14000.00 |
| 2443 | 1 | 1960 | 19000.00 |
| 2444 | 2 | 1950 | 17000.00 |
| 2445 | 2 | 1950 | 16000.00 |
| 2446 | 2 | 1960 | 14000.00 |
| 2448 | 3 | 1950 | 18000.00 |
| 2449 | 3 | 1950 | 13000.00 |
В тело (материализованного) представления MORE_RICH_EMP будут входить следующие строки представляемой таблицы MIDDLE_RICH_EMP:
| 2443 | 1 | 1960 | 19000.00 |
В каждом из представлений MIDDLE_RICH_EMP и MORE_RICH_EMP может отсутствовать или присутствовать (в одном из двух видов) раздел WITH CHECK OPTION. В совокупности возможен один из девяти случаев:
| none | Случай 1 | Случай 2 | Случай 3 |
| LOCAL | Случай 4 | Случай 5 | Случай 6 |
| CASCADED | Случай 7 | Случай 8 | Случай 9 |
Чтобы рассмотреть каждый из возможных случаев по отдельности, обсудим, что будет происходить в каждом случае при выполнении следующих двух операций модификации строк (будем называть эти операции U1 и U2 соответственно): UPDATE MORE_RICH_EMP SET EMP_SAL = EMP_SAL + 7000.00;
UPDATE MORE_RICH_EMP SET EMP_SAL = EMP_SAL – 7000.00;
Случай 1. Ни в одном из представлений не содержится раздел WITH CHECK OPTION.
Первый неожиданный результат состоит в том, что после выполнения операции U1 тело представления MORE_RICH_EMP оказывается пустым. Действительно, у единственной строки таблицы EMP (со значением EMP_NO, равным 2443), одновременно удовлетворяющей условиям обоих представлений, столбец EMP_SAL принимает значение 26000.00.
После этого строка перестает удовлетворять условию представления MIDDLE_RICH_EMP и исчезает из результирующей таблицы MORE_RICH_EMP. Этот результат может быть особенно неожиданным для пользователей базы данных, которым известно, что условие представления MORE_RICH_EMP имеет вид EMP_SAL > 18000.00, и соблюдение этого условия должно сохраняться при увеличении размера зарплаты.
Выполнение операции U2 также приведет к опустошению тела MORE_RICH_EMP (в базовой таблице EMP не останется ни одной строки, удовлетворяющей условию этого представления). Возможно, это будет достаточно естественно для пользователей представления MORE_RICH_EMP, которым известно условие представления, но те, кто работает с представлением MIDDLE_RICH_EMP, с удивлением обнаружат в теле результирующей таблицы новые строки.
Случай 2. В определении представления MIDDLE_RICH_EMP содержится раздел WITH LOCAL CHECK OPTION, а в определении MORE_RICH_EMP раздел WITH CHECK OPTION отсутствует.
В этом случае, в соответствии с первыми двумя правилами проверки корректности выполнения операций обновления над представлениями, операция U1 должна быть отвергнута системой (поскольку ее выполнение нарушает условие представления MIDDLE_RICH_EMP). Но заметим, что такое поведение системы будет совершенно неожиданным и непонятным для тех пользователей базы данных, которым известно только определение «верхнего» представления MORE_RICH_EMP, поскольку операция U1 явно не может нарушить видимое ими ограничение.
С другой стороны, операция U2 будет успешно выполнена и по-прежнему приведет к опустошению тела результирующей таблицы представления MORE_RICH_EMP.
Случай 3. В определении представления MIDDLE_RICH_EMP содержится раздел WITH CASCADED CHECK OPTION, а в определении MORE_RICH_EMP раздел WITH CHECK OPTION отсутствует.
В этой ситуации будут проверяться условия, содержащиеся в определении представления MIDDLE_RICH_EMP, а также все ограничения целостности таблицы EMP и всех других представлений, определенных над этой базовой таблицей.
В результате операция U1 будет отвергнута системой, а операция U2 будет «успешно» выполнена. Другими словами, повторится Случай 2.
Случай 4. В определении представления MIDDLE_RICH_EMP раздел WITH CHECK OPTION отсутствует, а в определении MORE_RICH_EMP содержится раздел WITH LOCAL CHECK OPTION.
Понятно, что в этом варианте операция U2 не сработает (ее выполнение не будет допущено условием «ограничения целостности» представления MORE_RICH_EMP). Но операция U1 (увеличение размера зарплаты служащих) будет успешно выполнена, поскольку она не противоречит локальным ограничениям представления MORE_RICH_EMP.
Случай 5. В определениях представлений MIDDLE_RICH_EMP и MORE_RICH_EMP содержится раздел WITH LOCAL CHECK OPTION.
Выполнение обеих операций U1 и U2 будет справедливо отвергнуто. На первый взгляд все в порядке. Но если над представлением MORE_RICH_EMP будет определено еще одно представление V, то мы можем получить ситуацию Случая 2, где V будет играть роль MORE_RICH_EMP, а MIDDLE_RICH_EMP – роль MORE_RICH_EMP.
Случай 6. В определении представления MIDDLE_RICH_EMP содержится раздел WITH CASCADED CHECK OPTION, а в определении MORE_RICH_EMP содержится раздел WITH LOCAL CHECK OPTION.
Снова, если над представлением MORE_RICH_EMP будет определено еще одно представление V, то мы можем попасть в ситуацию Случая 2, где V будет играть роль MORE_RICH_EMP, а MIDDLE_RICH_EMP – роль MORE_RICH_EMP.
Случай 7. В определении представления MIDDLE_RICH_EMP раздел WITH CHECK OPTION отсутствует, а в определении MORE_RICH_EMP содержится раздел WITH CASCADED CHECK OPTION.
Если над представлением MORE_RICH_EMP будет определено еще одно представление V, то мы можем попасть в ситуацию Случая 3, где V будет играть роль MORE_RICH_EMP, а MIDDLE_RICH_EMP – роль MORE_RICH_EMP.
Случай 8. В определении представления MIDDLE_RICH_EMP содержится раздел WITH LOCAL CHECK OPTION, а в определении MORE_RICH_EMP – раздел WITH CASCADED CHECK OPTION.
Если над представлением MORE_RICH_EMP будет определено еще одно представление V, то мы можем получить ситуацию Случая 3, где V будет играть роль MORE_RICH_EMP, а MIDDLE_RICH_EMP – роль MORE_RICH_EMP.
Случай 9. В определениях представлений MIDDLE_RICH_EMP и MORE_RICH_EMP содержится раздел WITH CASCADED CHECK OPTION.
Только в этом случае операции обновления будут выполняться корректно, независимо от того, имеются ли в базе данных представления, определенные над MORE_RICH_EMP или между MORE_RICH_EMP, MIDDLE_RICH_EMP и EMP.
Очевидный вывод из приведенного анализа заключается в том, что единственным способом обеспечить корректность выполнения операций обновления через представления (допускающие операции обновления) является включение в определение каждого представления раздела WITH CASCADED CHECK OPTION. В этом случае поведение системы будет оставаться корректным при введении дополнительных представлений над представлением MORE_RICH_EMP, между представлениями MORE_RICH_EMP и MIDDLE_RICH_EMP или между представлением MIDDLE_RICH_EMP и базовой таблицей EMP, если в определениях всех этих представлений присутствует раздел WITH CASCADED CHECK OPTION.
Примеры соединений разного вида
Основное назначение приводимых ниже примеров состоит не в том, чтобы продемонстрировать практическую значимость разнообразных соединений, а лишь в том, чтобы помочь в них разобраться. Поэтому мы будем использовать упрощенные и формальные таблицы и показывать заголовки и тела результирующих таблиц.
Итак, пусть имеются таблицы table1 (a1, a2, c1, c2) и table2 (b1, b2, c1, c2) со следующими телами:
| a1 | a2 | c1 | c2 |
| 1 | 1 | 1 | 1 |
| 1 | 1 | 2 | 3 |
| 1 | 1 | 2 | 3 |
| 2 | 3 | 4 | NULL |
| 3 | NULL | NULL | 5 |
| b1 | b2 | c1 | c2 |
| 1 | 1 | 1 | 1 |
| 1 | 2 | 2 | 3 |
| 3 | 3 | 2 | 3 |
| 4 | 4 | 4 | 4 |
| 3 | NULL | NULL | 5 |
| 3 | NULL | NULL | 5 |
Обозначим через JR таблицу, являющуюся результатом соединения. Тогда для операции table1 INNER JOIN table2 ON a1=b1 AND a2<b2 (внутреннее соединение по условию) тело JR будет следующим:
| a1 | a2 | table1.c1 | table1.c2 | b1 | b2 | table2.c1 | table2.c2 |
| 1 | 1 | 1 | 1 | 1 | 2 | 2 | 3 |
| 1 | 1 | 2 | 3 | 1 | 2 | 2 | 3 |
| 1 | 1 | 2 | 3 | 1 | 2 | 2 | 3 |
Строки-дубликаты появились в JR, поскольку в первом операнде присутствовали строки-дубликаты, удовлетворяющие условию соединения.
Результатом операции table1 INNER JOIN table2 USING (c2) (внутреннее соединение по совпадению значений указанных одноименных столбцов) будет следующая таблица.
| a1 | a2 | table1.c1 | c2 | b1 | b2 | table2.c1 |
| 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 2 | 3 | 1 | 2 | 2 |
| 1 | 1 | 2 | 3 | 3 | 3 | 2 |
| 1 | 1 | 2 | 3 | 1 | 2 | 2 |
| 1 | 1 | 2 | 3 | 3 | 3 | 2 |
| 3 | NULL | NULL | 5 | 3 | NULL | NULL |
| 3 | NULL | NULL | 5 | 3 | NULL | NULL |
Результат операции table1 INNER JOIN table2 USING (c1,c2):
| a1 | a2 | c1 | c2 | b1 | b2 |
| 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 2 | 3 | 1 | 2 |
| 1 | 1 | 2 | 3 | 3 | 3 |
| 1 | 1 | 2 | 3 | 1 | 2 |
| 1 | 1 | 2 | 3 | 3 | 3 |
Такой же результат будет получен при выполнении операции table1 NATURAL INNER JOIN table2 (естественное внутреннее соединение). Более того, для произвольных таблиц table1 и table2 результаты операций table1 INNER JOIN table2 USING (с1, c2, ...cn) и table1 INNER NATURAL JOIN table2 совпадают в том и только в том случае, когда список имен столбцов с1, c2, ...cn включает все имена столбцов, общие для таблиц table1 и table2.
Результатом операции table1 LEFT OUTER JOIN table2 ON a1=b1 AND a2<b2 (левое внешнее соединение по условию>) будет следующая таблица:
| a1 | a2 | table1.c1 | table1.c2 | b1 | b2 | table2.c1 | table2.c2 |
| 1 | 1 | 1 | 1 | 1 | 2 | 2 | 3 |
| 1 | 1 | 2 | 3 | 1 | 2 | 2 | 3 |
| 1 | 1 | 2 | 3 | 1 | 2 | 2 | 3 |
| 2 | 3 | 4 | NULL | NULL | NULL | NULL | NULL |
| 3 | NULL | NULL | 5 | NULL | NULL | NULL | NULL |
Как видно, в результате левого внешнего соединения сохраняются все данные первого (левого) операнда.
Результатом операции table1 RIGHT OUTER JOIN table2 ON a1=b1 AND a2<b2 (правое внешнее соединение по условию) будет следующая таблица:
| a1 | a2 | table1.c1 | table1.c2 | b1 | b2 | table2.c1 | table2.c2 |
| 1 | 1 | 1 | 1 | 1 | 2 | 2 | 3 |
| 1 | 1 | 2 | 3 | 1 | 2 | 2 | 3 |
| 1 | 1 | 2 | 3 | 1 | 2 | 2 | 3 |
| NULL | NULL | NULL | NULL | 1 | 1 | 1 | 1 |
| NULL | NULL | NULL | NULL | 3 | 3 | 2 | 3 |
| NULL | NULL | NULL | NULL | 4 | 4 | 4 | 4 |
| NULL | NULL | NULL | NULL | 3 | NULL | NULL | 5 |
| NULL | NULL | NULL | NULL | 3 | NULL | NULL | 5 |
Как видно, в результате правого внешнего соединения сохраняются все данные второго (правого) операнда.
Результатом операции table1 FULL OUTER JOIN table2 ON a1=b1 AND a2<b2 (полное внешнее соединение по условию) будет следующая таблица:
| a1 | a2 | table1.c1 | table1.c2 | b1 | b2 | table2.c1 | table2.c2 |
| 1 | 1 | 1 | 1 | 1 | 2 | 2 | 3 |
| 1 | 1 | 2 | 3 | 1 | 2 | 2 | 3 |
| 1 | 1 | 2 | 3 | 1 | 2 | 2 | 3 |
| 2 | 3 | 4 | NULL | NULL | NULL | NULL | NULL |
| 3 | NULL | NULL | 5 | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | 1 | 1 | 1 | 1 |
| NULL | NULL | NULL | NULL | 3 | 3 | 2 | 3 |
| NULL | NULL | NULL | NULL | 4 | 4 | 4 | 4 |
| NULL | NULL | NULL | NULL | 3 | NULL | NULL | 5 |
| NULL | NULL | NULL | NULL | 3 | NULL | NULL | 5 |
Как видно, в результате полного внешнего соединения сохраняются данные обоих операндов. Кстати, полное внешнее соединение иногда называют еще симметричным внешним соединением. Очевидно, что все операции внутреннего соединения и операция полного внешнего соединения коммутативны, а операции левого и правого соединения коммутативными не являются.
Результатом операции table1 LEFT OUTER JOIN table2 USING (c2) (левое внешнее соединение по совпадению значений указанных одноименных столбцов>) будет следующая таблица:
| a1 | a2 | table1.c1 | c2 | b1 | b2 | table2.c1 |
| 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 2 | 3 | 1 | 2 | 2 |
| 1 | 1 | 2 | 3 | 3 | 3 | 2 |
| 1 | 1 | 2 | 3 | 1 | 2 | 2 |
| 1 | 1 | 2 | 3 | 3 | 3 | 2 |
| 3 | NULL | NULL | 5 | 3 | NULL | NULL |
| 3 | NULL | NULL | 5 | 3 | NULL | NULL |
| 2 | 3 | 4 | NULL | NULL | NULL | NULL |
Результатом операции table1 RIGHT OUTER JOIN table2 USING (c2) (правое внешнее соединение по совпадению значений указанных одноименных столбцов) будет следующая таблица:
| a1 | a2 | table1.c1 | c2 | b1 | b2 | table2.c1 |
| 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 2 | 3 | 1 | 2 | 2 |
| 1 | 1 | 2 | 3 | 3 | 3 | 2 |
| 1 | 1 | 2 | 3 | 1 | 2 | 2 |
| 1 | 1 | 2 | 3 | 3 | 3 | 2 |
| 3 | NULL | NULL | 5 | 3 | NULL | NULL |
| 3 | NULL | NULL | 5 | 3 | NULL | NULL |
| NULL | NULL | NULL | 4 | 4 | 4 | 4 |
Результатом операции table1 FULL OUTER JOIN table2 USING (c2) (полное внешнее соединение по совпадению значений указанных одноименных столбцов) будет следующая таблица:
| a1 | a2 | table1.c1 | c2 | b1 | b2 | table2.c1 |
| 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 2 | 3 | 1 | 2 | 2 |
| 1 | 1 | 2 | 3 | 3 | 3 | 2 |
| 1 | 1 | 2 | 3 | 1 | 2 | 2 |
| 1 | 1 | 2 | 3 | 3 | 3 | 2 |
| 3 | NULL | NULL | 5 | 3 | NULL | NULL |
| 3 | NULL | NULL | 5 | 3 | NULL | NULL |
| 2 | 3 | 4 | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | 4 | 4 | 4 | 4 |
Результатом операции table1 LEFT OUTER JOIN table2 USING (c2, c1) (и операции table1 NATURAL LEFT OUTER JOIN table2 – естественное левое внешнее соединение) будет следующая таблица:
| a1 | a2 | c1 | c2 | b1 | b2 |
| 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 2 | 3 | 1 | 2 |
| 1 | 1 | 2 | 3 | 3 | 3 |
| 1 | 1 | 2 | 3 | 1 | 2 |
| 1 | 1 | 2 | 3 | 3 | 3 |
| 2 | 3 | 4 | NULL | NULL | NULL |
| 3 | NULL | NULL | 5 | NULL | NULL |
Результатом операции table1 RIGHT OUTER JOIN table2 USING (c2, c1) (и операции table1 NATURAL RIGHT OUTER JOIN table2 – естественное правое внешнее соединение) будет следующая таблица:
| a1 | a2 | c1 | c2 | b1 | b2 |
| 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 2 | 3 | 1 | 2 |
| 1 | 1 | 2 | 3 | 3 | 3 |
| 1 | 1 | 2 | 3 | 1 | 2 |
| 1 | 1 | 2 | 3 | 3 | 3 |
| NULL | NULL | 4 | 4 | 4 | 4 |
| NULL | NULL | NULL | 5 | 3 | NULL |
| NULL | NULL | NULL | 5 | 3 | NULL |
Результатом операции table1 FULL OUTER JOIN table2 USING (c2, c1) (и операции table1 NATURAL FULL OUTER JOIN table2 – естественное полное внешнее соединение) будет следующая таблица:
| a1 | a2 | c1 | c2 | b1 | b2 |
| 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 2 | 3 | 1 | 2 |
| 1 | 1 | 2 | 3 | 3 | 3 |
| 1 | 1 | 2 | 3 | 1 | 2 |
| 1 | 1 | 2 | 3 | 3 | 3 |
| 2 | 3 | 4 | NULL | NULL | NULL |
| 3 | NULL | NULL | 5 | NULL | NULL |
| NULL | NULL | 4 | 4 | 4 | 4 |
| NULL | NULL | NULL | 5 | 3 | NULL |
| NULL | NULL | NULL | 5 | 3 | NULL |
Наконец, результатом операции table1 UNION JOIN table2 (соединение объединением) будет следующая таблица:
| a1 | a2 | table1.c1 | table1.c2 | b1 | b2 | table2.c1 | table2.c2 |
| 1 | 1 | 1 | 1 | NULL | NULL | NULL | NULL |
| 1 | 1 | 2 | 3 | NULL | NULL | NULL | NULL |
| 1 | 1 | 2 | 3 | NULL | NULL | NULL | NULL |
| 2 | 3 | 4 | NULL | NULL | NULL | NULL | NULL |
| 3 | NULL | NULL | 5 | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | 1 | 1 | 2 | 3 |
| NULL | NULL | NULL | NULL | 1 | 2 | 2 | 3 |
| NULL | NULL | NULL | NULL | 3 | 3 | 2 | 3 |
| NULL | NULL | NULL | NULL | 4 | 4 | 4 | 4 |
| NULL | NULL | NULL | NULL | 3 | NULL | NULL | 5 |
| NULL | NULL | NULL | NULL | 3 | NULL | NULL | 5 |
За очевидностью мы опустим примерCROSS JOIN.
Примеры запросов с использованием предиката сравнения
Пример 18.1. Найти номера отделов, в которых работают служащие с фамилией 'Smith'.
SELECT DISTINCT EMP.DEPT_NO FROM EMP WHERE EMP.EMP_NAME = 'Smith';
Мы добавили спецификацию DISTINCT в раздел SELECT, потому что в одном отделе могут работать несколько служащих с фамилией 'Smith', а их число нас в данном случае не интересует. Кстати, если бы нас интересовало число служащих с фамилией 'Smith' в каждом отделе, где такие служащие работают, то следовало бы, например, написать такой запрос (пример 18.1.1):
SELECT EMP.DEPT_NO, COUNT(*) FROM EMP WHERE EMP.NAME = 'Smith' GROUP BY EMP.DEPT_NO;
В этом варианте запроса спецификация DISTINCT не требуется, поскольку в запросе содержится раздел GROUP BY, группировка производится в соответствии со значениями столбца EMP.DEPT_NO, и строка результата соответствует одной группе.
Пример 18.2. Найти номера, имена и номера отделов служащих, родившихся после 15 апреля 1965 г.
SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO FROM EMP WHERE EMP.EMP_BDATE > DATE '1965-04-15';
В результате этого запроса дубликатов быть не может, поскольку в список выборки включен столбец, являющийся первичным ключом таблицы EMP. Должно быть ясно, что по этой причине все строки результата будут различными.
Пример 18.3. Найти номера, имена и номера отделов служащих, размер заработной платы которых составляет больше одной десятой объема фонда заработной платы их отделов.
SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO FROM EMP WHERE EMP.EMP_SAL > 0.1 * (SELECT DEPT_TOTAL_SAL FROM DEPT WHERE DEPT.DEPT_NO = EMP.DEPT_NO);
В этом SQL-запросе имеются две интересные особенности, которые мы до сих пор не обсуждали. Во-первых, второй операнд операции сравнения содержит подзапрос, возвращающий единственное значение, поскольку логическое выражение раздела WHERE этого подзапроса состоит из условия, однозначно определяющего значение первичного ключа таблицы DEPT. Во-вторых, в условии раздела WHERE подзапроса используется ссылка на столбец таблицы EMP, указанной в разделе FROM «внешнего» запроса.
Пример 18.19. Найти номера проектов, которые выполнялись в период с 15 января 2000 г. по 31 декабря 2002 г. SELECT PRO_NO FROM PRO WHERE (PRO_SDATE, PRO_DURAT) OVERLAPS (DATE '2000-01-15', DATE '2002-12-31');
Пример 18.20. Найти названия проектов, которые будут выполняться в течение следующего года. SELECT PRO_TITLE FROM PRO WHERE (PRO_SDATE, PRO_DURAT) OVERLAPS (CURRENT_DATE, INTERVAL '1' YEAR);
В стандарте SQL:1999 разрешается применять предикат LIKE только для битовых строк типа BLOB. Битовые строки типов BIT и BIT VARYING не допускаются.
Пример 18.21. Найти номера служащих отдела номер 65, зарплата которых в этом отделе не является минимальной. SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL > SOME (SELECT EMP1.EMP_SAL FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);
Одна из возможных альтернативных формулировок этого запроса может основываться на использовании предиката EXISTS (пример 18.21.1): SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EXISTS(SELECT * FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_SAL > EMP1.EMP_SAL);
Вот альтернативная формулировка этого запроса, основанная на использовании агрегатной функции MIN (пример 18.21.2): SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL > (SELECT MIN(EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);
Пример 18.22. Найти номера и имена служащих отдела 65, однофамильцы которых работают в этом же отделе. SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO = 65 AND EMP_NAME = SOME (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO);
Заметим, что эта формулировка эквивалентна следующей формулировке (пример 18.22.1): SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO = 65 AND EMP_NAME IN (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO);
Возможна формулировка с использованием агрегатной функции COUNT (пример 18.22.2): SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO = 65 AND (SELECT COUNT(*) FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO ) >= 1;
Наиболее лаконичным образом этот запрос можно сформулировать с использованием соединения ( пример 18.22.3): SELECT DISTINCT EMP.EMP_NO, EMP.EMP_NAME FROM EMP, EMP EMP1 WHERE EMP.DEPT_NO = 65 AND EMP.EMP_NAME = EMP1.EMP_NAME AND EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO;
В последней формулировке мы вынуждены везде использовать уточненные имена столбцов, потому что на одном уровне используются два вхождения таблицы EMP.
Пример 18.28. Найти номера и имена служащих отдела 65, которых можно отличить по данным об имени и дате рождения от руководителя отдела 65. SELECT EMP1.EMP_NO, EMP2.EMP_NO FROM EMP EMP1, EMP EMP2 WHERE EMP1.EMP_NO <> EMP2.EMP_NO AND NOT ((EMP1.EMP_NAME, EMP1.EMP_BDATE) IS DISTINCT FROM (EMP2.EMP_NAME, EMP2.EMP_BDATE));
Пример 18.29. Найти все пары номеров таких служащих отдела 65, которых нельзя различить по данным об имени и дате рождения. SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO = 65 AND (EMP_NAME, EMP_BDATE) IS DISTINCT FROM (SELECT EMP1.EMP_NAME, EMP1.EMP_BDATE FROM EMP EMP1, DEPT WHERE EMP1.DEPT_NO = EMP.DEPT_NO AND DEPT.DEPT_MNG = EMP1.EMP_NO);
Подобные подзапросы в терминологии SQL традиционно называются корреляционными, и их следует понимать следующим образом.
При выполнении внешнего запроса последовательно, строка за строкой, в некотором порядке, определяемом системой, производится проверка соответствия строк результирующей таблицы раздела FROM условию раздела WHERE. Если это условие включает корреляционные подзапросы, то внутри каждого из этих подзапросов ссылка на столбец внешней таблицы трактуется как ссылка на столбец текущей строки данной таблицы во внешнем цикле. Естественно, условие WHERE любого подзапроса может включать более глубоко вложенные подзапросы, на которые распространяется то же правило корреляции с внешними таблицами.
Кстати, эквивалентная формулировка на языке SQL выглядит следующим образом (пример 18.3.1):
SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO FROM EMP, DEPT WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND EMP.EMP_SAL > 0.1 * DEPT.TOTAL_SAL;
Мы видим, что в терминах реляционной алгебры этот запрос представляет собой ограничение (по условию EMP.EMP_SAL > 0.1 * DEPT.TOTAL_SAL) эквисоединения таблиц EMP и DEPT (по условию EMP.DEPT_NO = DEPT.DEPT_NO). Подобную операцию часто называют полусоединением (semijoin), поскольку в результирующей таблице используются столбцы только одного из операндов операции эквисоединения. Мы привели вторую формулировку запроса, преследуя две цели: (1) продемонстрировать, каким образом предикат сравнения можно использовать для задания условия соединения, и (2) показать, что запросы, содержащие вложенные запросы, часто могут быть переформулированы в запросы с соединениями.
Пример 18.4. Найти номера, имена, номера отделов и имена руководителей отделов служащих, размер заработной платы которых меньше 15000 руб.
SELECT EMP1.EMP_NO, EMP1.EMP_NAME, EMP1.DEPT_NO, EMP2.EMP_NAME FROM EMP AS EMP1, EMP AS EMP2, DEPT WHERE EMP1.EMP_SAL < 15000.00 AND EMP1.DEPT_NO = DEPT.DEPT_NO AND DEPT.DEPT_MNG = EMP2.EMP_NO;
Этот запрос представляет собой эквисоединение ограничения таблицы EMP (по условию EMP_SAL < 15000.00) с таблицами DEPT и EMP (по условиям EMP.DEPT_NO = DEPT.DEPT_NO и DEPT.DEPT_MNG = EMP2.EMP_NO соответственно).
Таблица EMP участвует в качестве операнда операции эквисоединения два раза. Поэтому в разделе FROM ей присвоены два псевдонима – EMP1 и EMP2. Следуя предписанному стандартом порядку выполнения запроса, можно считать, что введение этих псевдонимов обеспечивает переименование столбцов таблицы EMP, требуемое для выполнения раздела FROM с образованием расширенного декартова произведения таблиц-операндов. Заметим также, что в данном случае мы имеем дело с полным эквисоединением трех таблиц (а не с полусоединением), поскольку в списке выборки присутствуют имена столбцов каждой из них.
Покажем способ формулировки этого запроса с использованием вложенного подзапроса в качестве элемента списка выборки (пример 18.4.1):
SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO, (SELECT EMP_NAME FROM EMP WHERE EMP_NO = DEPT_MNG) FROM EMP, DEPT WHERE EMP.EMP_SAL < 15000.00 AND EMP.DEPT_NO = DEPT.DEPT_NO;
Как показывает последний пример, в условии выборки подзапроса, участвующего в списке выборки, можно использовать имена столбов таблиц внешнего запроса. Из этой возможности языка SQL видно, что в подразделе предыдущей лекции для облегчения понимания материала мы немного исказили семантику оператора выборки. Там было сказано следующее: «После выполнения раздела WHERE (если в запросе отсутствуют разделы GROUP BY и HAVING, случай (a)) или выполнения явно или неявно заданного раздела HAVING (случай (b)) выполняется раздел SELECT. При выполнении этого раздела на основе таблицы T1 в случае (a) или на основе сгруппированной таблицы T3 в случае (b) строится таблица T4, содержащая столько строк, сколько строк или групп строк содержится в таблицах T1 илиT3 соответственно». В действительности, в общем случае очередная строка таблицы T4 должна строиться в тот момент, когда очередная строка или группа строк заносится в таблицу T1 или T3 соответственно.
Пример 18.23. Найти номера служащих отдела номер 65, зарплата которых в этом отделе является максимальной. SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL >= ALL(SELECT EMP1.EMP_SAL FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);
Одна из возможных альтернативных формулировок этого запроса может основываться на использовании предиката NOT EXISTS (пример 18.23.1): SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND NOT EXISTS (SELECT * FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_SAL < EMP1.EMP_SAL);
Можно сформулировать этот же запрос с использованием агрегатной функции MAX (пример 18.23.2): SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL = (SELECT MAX(EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);
Пример 18.24. Найти номера и имена служащих, не имеющих однофамильцев. SELECT EMP_NO, EMP_NAME FROM EMP WHERE EMP_NAME <> ALL (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP1.EMP_NO <> EMP.EMP_NO);
Этот запрос можно переформулировать на основе использования предиката NOT EXISTS или агрегатной функции COUNT (по причине очевидности мы не приводим эти формулировки), но, в отличие от случая в , формулировка в виде запроса с соединением здесь не проходит. Формулировка запроса SELECT DISTINCT EMP_NO, EMP_NAME FROM EMP, EMP EMP1 WHERE EMP.EMP_NAME <> EMP1.EMP_NAME AND EMP1.EMP_NO <> EMP.EMP_NO);
эквивалентна формулировке SELECT EMP_NO, EMP_NAME FROM EMP WHERE EMP_NAME <> SOME (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP1.EMP_NO <> EMP.EMP_NO);
Очевидно, что этот запрос является бессмысленным («Найти служащих, для которых имеется хотя бы один не однофамилец»).
Примеры запросов с использованием предиката between
Пример 18.5. Найти номера, имена и размер зарплаты служащих, получающих зарплату в размере от 12000 до 15000 руб.
SELECT EMP_NO, EMP_NAME, EMP_SAL FROM EMP WHERE EMP_SAL BETWEEN 12000.00 AND 15000.00;
Пример 18.6. Найти номера, имена и размер зарплаты служащих, получающих зарплату, размер которой не меньше средней зарплаты служащих своего отдела и не больше зарплаты руководителя отдела.
SELECT EMP_NO, EMP_NAME, EMP_SAL FROM EMP WHERE EMP_SAL BETWEEN (SELECT AVG(EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO) AND (SELECT EMP1.EMP_SAL FROM EMP EMP1 WHERE EMP1.EMP_NO = (SELECT DEPT.DEPT_MNG FROM DEPT WHERE DEPT.DEPT_NO = EMP.DEPT_NO));
В этом запросе можно выделить три интересных момента. Во-первых, диапазон значений предиката BETWEEN задан двумя подзапросами, результатом каждого из которых является единственное значение. Первый подзапрос выдает единственное значение, поскольку в списке выборки содержится агрегатная функция (AVG) и отсутствует раздел GROUP BY, а второй – потому что в его разделе WHERE присутствует условие, задающее единственное значение первичного ключа. Во-вторых, в обоих подзапросах таблица EMP получает псевдоним EMP1 (в формулировке этого запроса мы старались использовать как можно меньше вспомогательных идентификаторов). Поскольку подзапросы выполняются независимо один от другого, использование общего имени не вызывает проблем. Наконец, в условии второго подзапроса присутствует более глубоко вложенный подзапрос, и в условии его раздела WHERE используется ссылка на столбец таблицы из самого внешнего раздела FROM.
