Текст книги "SQL – язык реляционных баз данных"

При удалении кортежа в родительском отношении возможны:

· запрет – не разрешается удаление кортежа, если имеется хотя бы один кортеж в отношении-потомке, ссылающийся на удаляемый кортеж;

· каскадирование – разрешается удаление кортежа родительского отношения, если в отношении-потомке есть ссылающиеся на него кортежи. При этом будут удалены «по каскаду» все кортежи отношений-потомков, ссылающихся на удаляемый кортеж;

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

При вставке кортежа в отношении-потомке возможны:

· запрет – не разрешается вставка, если внешний ключ во вставляемом кортеже не соответствует ни одному значению первичного ключа родительского отношения;

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

При обновлении кортежа в отношении-потомке возможны:

· запрет – не разрешается обновление, если внешний ключ в обновляемом кортеже становится не соответствующим ни одному значению первичного ключа родительского отношения;

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

Операции над отношениями

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

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

· теоретико-множественные:

– объединение;

– пересечение;

– вычитание;

– декартово произведение;

· реляционные:

– проекция;

– селекция;

– соединение;

– деление.

Теоретико-множественные операции объединения, пересечения и вычитания имеют ту специфику, что они определены на отношениях. Это значит, что элементами множеств являются кортежи, причем кортежи должны быть совместимыми по типу.

Совместимыми по типу называются отношения, которые имеют одну и ту же структуру (идентичные заголовки), а именно: · каждое отношение имеет одно и то же множество имен атрибутов (одну и ту же степень);

· одноименные (соответствующие) атрибуты определены на одном и том же домене (должны иметь один и тот же тип и размер).

Объединением двух совместимых по типу отношений R₁ и R₂ называется отношение R с тем же заголовком, что и у отношений R₁ и R₂, и телом, состоящим из кортежей, принадлежащих или R₁, или R₂, или обоим отношениям (рис. 5).

Пусть имеются два подмножества: таблица, представляющая список сотрудников, работающих в отделе 102 (R₁), и список сотрудников-инженеров (R₂). Оба подмножества должны быть совместимы по типу. Тогда результатом объединения этих двух списков будет список сотрудников, которые или работают в отделе 102, или являются инженерами по должности, или удовлетворяют обоим условиям одновременно.

Рис. 5. Операция объединения

Сотрудники-инженеры ( R ₁)

Сотрудники 102 отдела ( R ₂)

Сотрудники ( R )

Вычитанием двух совместимых по типу отношений R₁ и R₂ называется отношение R с тем же заголовком, что и у отношений R₁ и R₂, и телом, состоящим из кортежей, принадлежащих отношению R₁ (уменьшаемому) и не принадлежащих отношению R₂ (вычитаемому, рис. 6).

Рис. 6. Операция вычитания

Допустим, исходные данные те же, что и в предыдущем примере, и нужно сформировать список сотрудников, которые являются инженерами, но не работают в отделе 102.

Сотрудники-инженеры не из отдела 102

Пересечением двух совместимых по типу отношений R₁ и R₂ называется отношение R с тем же заголовком, что и у отношений R₁ и R₂, и телом, состоящим из кортежей, принадлежащих одновременно обоим отношениям R₁ и R₂ (рис. 7).

Рис. 7. Операция пересечения

Операция пересечения является зависимой и может быть выражена через другие операции:

R=R1∩R2=R1-(R1-R2)

Допустим, исходные данные те же, что и в предыдущем примере, и нужно сформировать список сотрудников, которые являются инженерами и работают в отделе 102.

Сотрудники-инженеры отдела 102

Декартовым произведением двух отношений R₁ (со степенью n₁ и мощностью k₁) и R₂ (со степенью n₂ и мощностью k₂) называется отношение R, заголовок которого является сцеплением заголовков отношений R₁ и R₂, а тело состоит из кортежей, являющихся сцеплением кортежей отношений R₁ и R₂.

Синтаксис операции декартового произведения:

R=R1xR2={(r1,r2)lr1∈R1∧r2∈R2}

Степень отношения R:

n=n1+n2

Мощность отношения R:

k=k1⋅k2

Таким образом, кортежи декартового произведения представляют собой сцепление кортежей отношений-операндов во всех возможных сочетаниях.

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

Допустим, что при описании некоторой предметной области таблица «Сотрудники» представляет список сотрудников, а таблица «Отделы» – список отделов, где они работают.

Сотрудники ( R ₁)

Отделы ( R ₂)

Декартово произведение ( R )

Проекцией отношения R₁ (A, B, C, D, E) на атрибуты A, C, E, где множество {A, C, E} является подмножеством полного списка атрибутов заголовка отношения R₁ {A, B, C, D, E}, называется отношение R с заголовком A, C, E и телом, содержащим кортежи отношения R₁ без дублей (рис. 8).

Рис. 8. Операция проекции

Операция проекция порождает результат как вертикальную выборку, выборку столбцов-атрибутов реляционной таблицы без дублей строк-кортежей.

Допустим, что из отношения «Сотрудники» формируется как проекция отношение «Штатное расписание».

Сотрудники ( R ₁)

Штатное расписание ( R )

Селекцией отношения R₁ по формуле F называется отношение R, имеющее тот же заголовок, что и отношение R₁, и тело, содержащее кортежи отношения R₁, которые удовлетворяют истинности логического выражения, заданного формулой F (рис. 9).

Рис. 9. Операция селекции

Операция селекция порождает результат как горизонтальную выборку, выборку строк-кортежей реляционной таблицы, удовлетворяющих заданному условию. Ввиду многообразия средств спецификации условий отбора операция селекции является одной из наиболее распространенных на практике операций над реляционными таблицами. Например, если из списка, заданного отношением-таблицей «Сотрудники» (R₁), нужно выбрать сотрудников отдела 101, результат можно получить при помощи операции селекции по условию «номер отдела=101».

Сотрудники отдела 101 ( R )

Операция соединения отношений, наряду с операциями выборки и проекции, является одной из наиболее важных реляционных операций, так как лежит в основе большинства реальных запросов, адресуемых к данным нескольких связанных реляционных таблиц.

Обычно рассматривается несколько разновидностей операции соединения.

Наиболее общим является так называемое q-соединение, которое формирует результат на основе сцепления кортежей отношений-операндов, чьи атрибуты связи находятся друг с другом в отношении q (=, ≠, >, <, ≥, ≤).

Частным случаем q-соединения является эквисоединение или соединение на основе условия равенства атрибутов связи.

Частным случаем эквисоединения является естественное, или внутреннее, соединение. Дадим неформальное определение этой важной операции.

Естественным (внутренним) соединением отношений R₁ (A, B, C) и R₂ (B, C, D) называется отношение R (A, B, C, D), полученное по условию равенства (эквисоединение) общих (часто одноименных) атрибутов связи (B, C), из которого исключаются дубли атрибутов.

Атрибуты связи (B и C в данном примере), принадлежащие разным отношениям-операндам, могут иметь разные имена, но должны быть определены на совместимых доменах.

Операция естественного соединения является зависимой и может быть выражена через другие операции. Синтаксис операции естественного соединения:

R=R1⊳⊲R2=pA,B,C,D(sR₁.B=R₂.BЩR₁.C=R₂.C(R1xR2))

Результирующее отношение R состоит из кортежей, которые представляют собой сцепление только тех кортежей отношений-операндов R₁ и R₂, которые содержат совпадающие значения общих атрибутов (B, C), без дублей столбцов.

Допустим в кадровой информационной системе между отношениями «Сотрудники» (R₁) и «Дети» (R₂) имеется идентифицирующая связь типа «один ко многим». Атрибуты первичных ключей подчеркнуты. Семантика связи такова, что не у всех сотрудников могут быть дети. Причем у любого сотрудника может быть более одного ребенка. Тогда список сотрудников и их детей может быть получен при помощи естественного соединения.

Сотрудники» ( R ₁)

Дети ( R ₂)

Здесь атрибутами связи являются атрибуты «Сотрудники.номер сотрудника» и «Дети.номер сотрудника», а результатом является отношение «Сотрудники с детьми».

Сотрудники с детьми ( R )

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

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

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

Для обозначения несовпадающих значений кортежей используется определитель Null.

Результат операции левого соединения зависит от типа связи и роли отношений в качестве левого или правого операнда. В нашем примере между отношениями «Сотрудники» и «Дети» имеется идентифицирующая связь «один ко многим». По определению идентифицирующей связи в родительском отношении «Сотрудники» допускаются кортежи, которым нет соответствия в отношении-потомке «Дети». Если в качестве левого операнда используется родительское отношение («Сотрудники»), то левое соединение даст полный список всех сотрудников и их отношение к детям.

Все сотрудники ( R ₁)

Результат операции правого соединения зависит от типа связи и роли отношений в качестве левого или правого операнда. Например, между отношениями «Сотрудники» и «Дети» имеется идентифицирующая связь «один ко многим». По определению идентифицирующей связи в отношении-потомке «Дети» не может быть кортежей, которым нет соответствия в родительском отношении «Сотрудники». Если в качестве правого операнда используется отношениепотомок («Дети»), то правое соединение даст результат, не отличающийся от результата естественного соединения.

Сотрудники с детьми ( R )

Результат операции правого соединения зависит от типа связи. Например, если между отношениями «Отдел» и «Сотрудники» имеется неидентифицирующая связь «один ко многим», то по определению неидентифицирующей связи в отношении-потомке «Сотрудники» допускаются кортежи, которым нет соответствия в родительском отношении «Отдел». Если в таком случае в качестве правого операнда используется отношение-потомок («Сотрудники»), то правое соединение даст полный список сотрудников, в том числе временно не приписанных ни к какому отделу (сотрудники, допустим, ликвидированного отдела 102).

Отдел ( R ₁)

Сотрудники ( R ₂)

Все сотрудники ( R )

Делением отношений R₁(A, B, C) и R₂ (C) называется отношение R(A, B), тело которого содержит кортежи такие, что для всех кортежей отношения R₂ найдется кортеж в отношении R₁.

Операция деления является зависимой и может быть выражена через другие операции. Синтаксис операции деления:

R=R1ёR2=pA.B(R1)-p((R2ґpA.B(R1))-R1)

Результирующее отношение R состоит из кортежей отношения R₁, определенных на множестве атрибутов (A, B), которые соответствуют комбинации всех кортежей отношения R₂.

Деление отношений аналогично делению чисел с остатком.

Отношение R₁ выступает в роли делимого, отношение R₂ выступает в роли делителя.

Допустим отношение «Исполнитель проекта» (R₁) моделирует связь «многие ко многим» между отношениями «Сотрудники» и «Проекты» (R₂). Между родительским отношением «Проект» и отношением-потомком «Исполнитель проекта» имеется идентифицирующая зависимость. Тогда ответ на вопрос, какие сотрудники участвовали в работе над всеми проектами, даст отношение «Передовики» (R), полученное при помощи операции деления.

Исполнитель проекта ( R ₁)

Проект ( R ₂)

Передовики ( R )

SQL – язык структурированных запросов

Введение в SQL

Различают два вида языков запросов для реляционной модели данных.

Алгебраические языки позволяют выразить запросы средствами специальных операторов над отношениями – операторов реляционной алгебры.

Языки реляционного исчисления позволяют описать запросы на языке исчисления предикатов – языке математической логики. Алгебраические языки являются процедурными языками. Языки реляционного исчисления относятся к классу декларативных языков, т. е. языков более высокого уровня. Эти языки являются абстрактными языками и в чистом виде не реализованы, но они служат эталоном для оценки реальных языков запросов. По своим выразительным возможностям эти языки эквивалентны. Они были предложены в свое время автором реляционной модели Коддом для представления минимальных возможностей любого реального языка запросов реляционной модели. Восемь алгебраических операторов: объединение, пересечение, вычитание, декартово произведение, проекция, селекция, соединение и деление, – введенные Коддом, являются мерой оценки выразительной силы любого языка баз данных. Реальный язык баз данных обладает свойством реляционной полноты, если по своим выразительным возможностям он не уступает реляционной алгебре или реляционному исчислению.

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

· добавление данных;

· модификация данных;

· удаление данных;

· арифметические вычисления и сравнения;

· присваивание и отображение;

· вычисление функций агрегации, выполняемые над однородными элементами данных (вычисление количества, суммы, среднего, минимального или максимального значения и других). Наиболее распространенным языком запросов реляционной модели данных в настоящее время является язык структурированных запросов SQL (Structured Query Language).

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

В силу исторических причин SQL стал стандартным реляционным языком и в настоящее время поддерживается практически всеми системами баз данных. Поэтому каждый специалист по базам данных должен быть знаком с ним.

В чистом виде как язык, поддерживающий реляционную модель, наиболее популярен стандарт языка, известный как SQL/92 (International Standard Database Language SQL).

Ни один из коммерческих продуктов не поддерживает в полной мере стандарт SQL/92. Известные версии этого языка, по образному выражению Дейта [2], можно назвать «надмножествами подмножеств» языка SQL/92. Другими словами, любая коммерческая система, не поддерживая некоторые аспекты стандарта, в других отношениях, возможно, превосходит его. Это замечание справедливо и в отношении версии SQL Microsoft Jet, реализованной в популярной СУБД MS Access.

Язык SQL является языком декларативного типа. В нем отсутствуют какие-либо команды управления ходом вычислительного процесса типа IF-THEN-ELSE, SWITCH, WHILE, DO-WHILE, FOR, GO TO и др. Управление ходом выполнения процесса обработки данных может выполняться интерактивно, в результате действий самого пользователя или при помощи процедурных языков программирования высокого уровня. В связи с этим различают две разновидности SQL – интерактивный и вложенный. По большей части обе формы работают одинаково, но используются различно.

Интерактивный SQL используется для функционирования непосредственно в базе данных и реализуется непосредственно при вводе пользователем отдельных команд. При такой форме SQL при вводе команды она сейчас же выполнится и вы сможете увидеть результат немедленно. Вложенный SQL состоит из команд SQL внедренных внутрь программ, написанных на процедурных языках программирования высокого уровня, что делает программы более мощными и эффективными. Однако при таком способе управления процессом обработки данных приходится иметь дело со структурой SQL и стилем управления, который требует некоторых расширений к интерактивному SQL. На практике существуют два основных способа использования SQL в программах: · внедрение SQL-операторов в исходный текст программы с последующей ее компиляцией и компоновкой;

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

В учебном пособии, рассчитанном на неподготовленного пользователя SQL, ограничимся интерактивной формой языка. Это даст возможность обсуждать команды и результаты их выполнения, не заботясь о том, как они связаны с помощью интерфейса с другими языками. Интерактивный SQL – это наиболее полезная для непрограммистов форма. Все, что вы узнаете относительно интерактивного SQL, в основном применимо и к вложенной форме.

В соответствии со стандартом SQL выразительные возможности языка распределены по шести типам подмножеств команд: · язык определения данных (Data Definition Language – DDL), представлен инструкциями CREATE, ALTER, DROP;

· язык манипулирования данными (Data Manipulation Language – DML), представлен инструкциями INSERT, UPDATE, DELETE;

· язык запросов данных (Data Query Language – DQL), представлен многофункциональной командой SELECT;

· язык управления данными (Data Control Language – DCL), представлен командами GRANT (предоставление привилегий) и REVOKE (отмена привилегий);

· язык обработки транзакций (Transaction Processing Language –

TPL), включает команды BEGIN (начать транзакцию), COMMIT (завершить транзакцию), ROLLBACK (откатить транзакцию);

· язык управления курсором (Cursor Control Language – CCL) предназначен для выполнения операций с отдельными строками одной или нескольких таблиц, представлен командами DECLARE CURSOR, OPEN, CLOSE, FETCH INTO, DROP CURSOR.

К сожалению, эти подмножества не используются повсеместно во всех реализациях. Они подчеркиваются ANSI и полезны на концептуальном уровне. В частности, интерактивный Microsoft Jet SQL, реализованный в популярной СУБД MS Access, поддерживает только первые три подмножества.

Определение данных в SQL

Рассмотрим терминологию SQL. В языке SQL поддерживаются такие термины, как таблица, столбец, строка, вместо реляционных терминов – отношение, атрибуты, кортежи.

Связь терминов реляционной модели данных, SQL и Access представлена в табл. 2.

Таблица 2

Соответствие терминов

База данных в SQL представляет собой набор взаимосвязанных таблиц. При этом различают базовые таблицы и таблицы-представления. Базовая таблица (TABLE) – это основной структурный элемент базы данных. Ей соответствуют реальные хранимые данные. Концептуальная модель базы данных представляет собой совокупность взаимосвязанных базовых таблиц. Представление (VIEW) является виртуальной таблицей, которая выглядит как реально существующая таблица. Представление не содержит собственных данных, в нем скомбинированы данные из одной или нескольких связанных базовых таблиц. Средством наполнения таблицы-представления являются запросы, в которых реализуются информационные потребности пользователей базы данных. С помощью представления реализуются локальные представления (внешняя модель данных) базы данных, используемые в прикладных программах или запросах пользователей. Сразу необходимо отметить, что концепция представлений в версии SQL Microsoft Jet по умолчанию не поддерживается.

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

Действующим на данный момент стандартом языка SQL является принятая Американским национальным институтом стандартов (American National Standards Institute – ANSI) версия SQL-92. Фирмыразработчики СУБД при реализации языка SQL могут вносить в него расширения, но обязаны реализовать базовый набор команд ANSI SQL. Процессор обработки данных Microsoft Jet является составной частью Access и выполняет инструкции Access SQL (SQL Microsoft Jet), который отличается от ANSI SQL существенно [12] (как правило, настольные СУБД, совместимые со стандартом SQL, реализуют не все инструкции ANSI SQL).

Язык SQL Microsoft Jet почти соответствует стандарту ANSI SQL-89. В реализацию языка SQL для Microsoft Jet 4. x (начиная с версии Microsoft Access 2000) внесены несколько расширений, которые приближают его к стандарту ANSI SQL-92 и Transact-SQL – диалекту языка SQL для Microsoft SQL Server. Для того чтобы обеспечить совместимость с предыдущими версиями Microsoft Jet, эти расширения можно использовать только в специальном режиме – ANSI SQL-92. Основные различия языков SQL Microsoft Jet и ANSI SQL состоят в следующем [12]: · они имеют разные наборы зарезервированных слов и типов данных;

· разные правила применимы к оператору Between… And, используемому для определения условий выборки записей;

· подстановочные знаки ANSI и Microsoft Jet, которые используются в операторе Like, взаимно исключают друг друга;

· язык Jet SQL обычно предоставляет пользователю большую свободу, например разрешается группировка и сортировка по выражениям;

· язык Jet SQL позволяет использовать более сложные выражения.

Типы данных языка SQL ядра базы данных Microsoft Jet включают следующие основные типы данных [12] (табл. 3).

Таблица 3

Типы данных SQL Microsoft Jet

Типы данных ANSI SQL, эквивалентные им типы данных языка SQL Microsoft Jet и допустимые синонимы приведены ниже [12] (табл. 4).

Таблица 4

Типы данных стандартного SQL и SQL Microsoft Jet

Ядро базы данных Microsoft Jet SQL в основном совместимо с ANSI-89 Level 1. Однако некоторые возможности языка запросов ANSI SQL не реализованы в Microsoft Jet SQL. Режим ANSI-89 является настройкой по умолчанию для новой базы данных Microsoft Access в формате файла 2002–2003, 2007, 2010.

В ANSI-92 имеются новые зарезервированные слова, правила синтаксиса и подстановочные знаки, расширяющие возможности создания запросов и использования команд SQL. Этот режим близок к спецификации ANSI-92 уровня 1, но не является совместимым с ANSI-92 уровня 1. В данном режиме запроса содержится больше синтаксиса ANSI, а подстановочные знаки удовлетворяют спецификации SQL.

Использование ANSI-92 SQL может потребоваться, если предполагается будущее развитие приложения до проекта Microsoft Access и требуется разработать запросы, которые будут выполняться с минимальными изменениями в базе данных Microsoft SQL Server.

Для настройки режима совместимости с более совершенной версией стандарта ANSI-92 SQL для текущей базы данных рекомендуется выполнить следующие действия:

1. «Запустить» MS Access.

2. Создать пустую базу данных.

3. В левом верхнем углу окна нажать кнопку <Office>.

4. В диалоговом окне нажать кнопку <Параметры Access>.

5. В диалоговом окне «Параметры Access» в списке слева выбрать «Конструкторы объектов».

6. В разделе «Конструктор запросов» в опции «Синтаксис для SQL Server (ANSI 92)» установить флажок «эта база данных».

7. Нажать кнопку <OK>.