База данных

4. Операция  декартова произведения.

 

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

Оператор Select в конструкции, представляющей операцию декартова  произведения на языке структурированных  запросов, присутствует только один и  имеет следующий вид:

 

Select *

From  R₁ cross join  R₂

 

Здесь R₁ и R₂ – имена исходных отношений-операндов. Опция cross join  обеспечивает, что в результирующее отношение запишутся все атрибуты (все, потому что в первой строчке оператора поставлен значок «*»), соответствующие всем парам кортежей отношений R₁ и R₂.

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

 

r ₄(S ₄) = r ₁(S ₁) × r ₂(S ₂) = {t (S ₁ ∪ S ₂) | t [S ₁] ∈ r ₁ & t (S ₂) ∈ r ₂}, S ₁ ∩ S ₂= ∅;

 

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

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

 

R₁ ( A, B),

R₂ (B, C);

 

Мы видим, что атрибуты R₁.B и R₂.B имеют одинаковые имена. С учетом этого оператор Select, реализующий на языке структурированных запросов эту операцию декартова произведения, будет выглядеть следующим образом:

 

Select  А, R₁.B as  B1, R₂.B as  B2, C

From  R₁ cross join  R₂;

 

Таким образом, с использованием опции переименования as, у машины не возникнет «вопросов», по поводу совпадающих имен двух исходных отношений-операндов.

5. Операции  внутреннего соединения.

 

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

Итак, для начала вспомним определение операции внутреннего  соединения, которое мы проходили  раньше:

 

r ₁(S ₁) × _P r ₂(S ₂) = σ <P>  (r ₁ × r ₂), S ₁ ∩  S ₂ = ∅.

 

Для нас в этом определении  особенно важно то, что рассматриваемые  схемы отношений-операндов S ₁ и S ₂ не должны пересекаться.

Для реализации операции внутреннего  соединения в языке структурированных  запросов существует специальная опция inner join , которая и переводится с английского буквально «внутреннее объединения» или «внутреннее соединение».

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

 

Select *

From  R₁ inner join  R₂;

 

Здесь, как и раньше, R₁ и R₂ – имена исходных отношений-операндов.

При реализации этой операции нельзя допускать пересечения схем отношений-операндов.

6. Операция  естественного соединения.

 

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

Так как рассматриваемая  нами операция естественного соединения является частным случаем операции внутреннего соединения, для ее реализации используется та же опция, что и для  предыдущей рассмотренной операции, т. е. опция inner join . Но поскольку при составлении оператора Select для операции естественного соединения необходимо еще учесть условие равенства кортежей исходных отношений-операндов на пересечении их схем, то дополнительно к означенной опции применяется ключевое слово on . В переводе с английского, это буквально означает «на», а применительно к нашему смыслу, можно перевести как «при условии».

Общий вид оператора Select для  выполнения операции естественного  соединения следующий:

 

Select *

From  имя отношения 1  inner join  имя отношения 2

on  условие равенства кортежей ;

 

Рассмотрим пример.

Пусть даны два отношения:

 

R₁ ( A, B, C),

R₂ (B, C, D);

 

Операцию естественного  соединения этих отношений можно  реализовать с помощью следующего оператора:

 

Select  А , R ₁.B , R ₁.C , D

From  R ₁ inner join  R ₂

on  R ₁.B  = R ₂.B  and R ₁.C  = R ₂.C

 

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

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

Интересно, что использованная формулировка условия соединения (R₁.B = R₂.B and R₁.C = R₂.C) предполагает, что общие атрибуты соединяемых отношений Null-значений не допускают. Это изначально встроено в систему языка структурированных запросов.

7. Операция  левого внешнего соединения.

 

Выражение на языке структурированных  запросов SQL операции левого внешнего соединения получается из реализации операции естественного соединения заменой ключевого слова inner  на ключевое слово left outer .

Таким образом, на языке структурированных  запросов эта операция запишется  следующим образом:

 

Select *

From  имя отношения  1 left outer join  имя отношения  2

on  условие равенства кортежей ;

 

8. Операция  правого внешнего соединения.

 

Выражение для операции правого  внешнего соединения на языке структурированных  запросов получается из осуществления  операции естественного соединения заменой ключевого слова inner  на ключевое слово right outer .

Итак, получаем, что на языке  структурированных запросов SQL операция правого внешнего соединения запишется  следующим образом:

 

Select *

From  имя отношения  1 right outer join  имя отношения  2

on  условие равенства кортежей ;

 

9. Операция  полного внешнего соединения.

 

Выражение на языке структурированных  запросов операции полного внешнего соединения получается, как и в  двух предыдущих случаях, из выражения  для операции естественного соединения путем замены ключевого слова inner  на ключевое слово full outer .

Таким образом, на языке структурированных  запросов эта операция запишется  так:

 

Select *

From  имя отношения 1  full outer join  имя отношения 2

on  условие равенства кортежей ;

 

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

 

4. Использование  подзапросов

 

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

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

 

Предметы (Код предмета , Имя предмета);

Студенты (№ зачетной книжки , Фамилия, Имя, Отчество);

Сессия (Код предмета , № зачетной книжки , Оценка);

 

Сформулируем SQL-запрос, возвращающий ведомость с указанием номера зачетной книжки, фамилии и инициалов студента и оценки для предмета с наименованием «Базы данных». Такую информацию в университетах необходимо получать всегда и своевременно, поэтому приведенный далее запрос является едва ли не самой востребованной единицей программирования с использованием таких баз данных.

Для удобства работы, дополнительно  предположим, что атрибуты «Фамилия», «Имя» и «Отчество» не допускают Null-значений и не являются пустыми. Это требование вполне объяснимо и закономерно, ведь в базу данных любого учебного заведения первыми из данных на нового ученика вводятся именно данные о его фамилии, имени и отчестве. И само собой разумеется, что не может быть записи в подобной базе данных, в которой присутствуют данные на ученика, но при этом неизвестно его имя.

Заметим, что атрибут «Имя предмета» схемы отношения «Предметы» является ключом, поэтому, как следует  из определения (подробнее об этом будет  сказано дальше), все наименования предметов являются уникальными. Это  тоже понятно и без пояснения  представления ключа, ведь все преподающиеся  в учебном заведении предметы должны иметь и имеют различные  имена.

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

Во-первых, нам будет полезна функция Trim , записывается Trim («строка» ), т. е. аргументом этой функции является строка. Что делает эта функция? Они возвращает сам аргумент без пробелов, стоящих в начале и в конце этой строки, т. е., эту функцию применяют, например, в случаях: Trim («Богучарников») или Trim («Максиме-енко»), когда после или до аргумента стоят по несколько лишних пробелов.

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

Например, результатом операции:

 

Left («Михаил, 1») + «.» + Left («Зиновьевич, 1»)

 

будут инициалы «М. З.». Именно для выведения инициалов студентов  мы и будем использовать эту функцию  в нашем запросе.

Итак, приступим к составлению  искомого запроса.

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

 

Select  № зачетной книжки, Оценка

From  Сессия

Where  Код предмета  = (Select  Код предмета

From  Предметы

Where  Имя предмета  = «Базы данных» )

as  «Оценки „Базы данных“ ;

 

Применение здесь опции as означает, что мы присвоили этому  запросу псевдоним «Оценки „Базы данных“ . Сделали мы это для удобства дальнейшей работы с этим запросом.

Далее, в этом запросе подзапрос:

 

Select  Код предмета

From  Предметы

Where  Имя предмета  = «Базы данных» ;

 

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

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

А весь запрос «Оценки „Базы данных“  позволяет выделить из отношения «Сессия» данные о тех студентах (их номера зачетных книжек и оценки), которые удовлетворяют условию, оговоренному в подзапросе, т. е. информацию о предмете под названием «База данных».

Теперь составим основной запрос, используя уже полученные результаты.

 

Select  Студенты.  № зачетной книжки ,

Trim  (Фамилия ) + « » + Left  (Имя , 1) + «.» + Left  (Отчество , 1) + «.»as  ФИО , Оценки «Базы данных» . Оценка

From  Студенты  inner join

(

Select  № зачетной книжки, Оценка

From  Сессия

Where  Код предмета  = (Select  Код предмета

From  Предметы

Where  Имя предмета  = «Базы данных» )

) as  «Оценки „Базы данных“.

on  Студенты.  № зачетной книжки  = Оценки «Базы данных».  № зачетной книжки.

 

Итак, сначала мы перечисляем  атрибуты, которые будет необходимо вывести, после окончания работы запроса. Необходимо упомянуть, что  атрибут «№ зачетной книжки» из отношения Студенты, оттуда же –  атрибуты «Фамилия», «Имя» и «Отчество». Правда, два последних атрибута выводим  не полностью, а только первые буквы. Также мы упоминаем атрибут «Оценка» из запроса Оценки «Базы данных , которое ввели раньше.

Выбираем мы все эти  атрибуты из внутреннего соединения отношения «Студенты» и запроса «Оценки „Базы данных“ . Это внутреннее соединение, как мы можем видеть, берется нами по условию равенства номеров зачетной книжки. В результате этой операции внутреннего соединения, к отношению «Студенты» добавляются оценки.

Надо заметить, что так  как атрибуты «Фамилия», «Имя» и  «Отчество» по условию не допускают Null-значений и не являются пустыми, то формула вычисления, возвращающая атрибут «ФИО» (Trim  (Фамилия) + « » + Left  (Имя, 1) + «.» + Left  (Отчество, 1) + «.»as  ФИО), соответственно не требует дополнительных проверок, упрощается.

 

Лекция  № 7. Базовые отношения

 

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