Материал: Реляционная алгебра. Основы SQL

Реляционная алгебра. Основы SQL

Контрольная работа

Реляционная алгебра. Основы SQL

Содержание

1. Реляционная алгебра

.1 Традиционные реляционные операции

.2 Специальные реляционные операции

.3 Дополнительные реляционные операции

. Основы SQL

.1 Типы данных

.2 Создание и обслуживание таблиц

.3 Запрос на выборку

.4 Статистические функции

.5 Создание соединений

.6 Вложенные запросы

.7 Запрос на объединение

.8 Запросы, выполняющие реляционные операции вычитания, пересечения и деления

.9 Запросы на изменение

.10 Перекрестные запросы

Литература

. Реляционная алгебра

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

По определению Е. Кодда, основу реляционной алгебры составляют восемь операторов. Причем, некоторые из них могут выполняться при определенных условиях. Например, в математике объединение двух множеств является множеством всех элементов, принадлежащих или обоим, или одному из исходных множеств. Объединение множества кортежей поставщиков в отношении ПОСТАВЩИКИ и множества кортежей деталей в отношении ДЕТАЛИ является множеством, но не являются отношением - отношения не могут содержать смесь кортежей разных типов. В реляционной алгебре для объединения требуется, чтобы два исходных отношения имели одну и ту же форму, то есть - были совместимы по типу. Два отношения совместимы по типу, если каждое из них имеет одно и то же множество имен атрибутов и соответствующие атрибуты определены на одном и том же домене.

1.1 Традиционные реляционные операции

К традиционным операциям над множествами относятся операторы объединения, пересечения, вычитания и произведения.

Объединением двух совместимых по типу отношений А и В (A UNION B) называется отношение с тем же заголовком, как и в отношениях А и В, и с телом, состоящим из множества всех кортежей t, принадлежащих А или В или обоим отношениям. При этом совпадающие кортежи записываются один раз.

Пересечением двух совместимых по типу отношений А и В (A INTERSECT B) называется отношение с тем же заголовком, как и в отношениях А и В, и с телом, состоящим из множества всех кортежей t, которые принадлежат одновременно обоим отношениям А и В.

Вычитанием двух совместимых по типу отношений А и В (A MINUS B) называется отношение с тем же заголовком, как и в отношениях А и В, и с телом, состоящим из множества всех кортежей t, принадлежащих отношению А и не принадлежащих отношению В.

Рис. 1

Произведение. Декартово произведение двух отношений А и В (A TIMES B), где А и В не имеют общих имен атрибутов, определяется как отношение с заголовком, который представляет собой сцепление (конкатенацию) двух заголовков исходных отношений А и В, и телом, состоящим из множества всех кортежей t таких, что t представляет собой сцепление кортежа a, принадлежащего отношению А, и кортежа b, принадлежащего отношению В. Кардинальное число результата равняется произведению кардинальных чисел исходных отношений, а степень равняется сумме их степеней.

1.2 Специальные реляционные операции

К специальным реляционным операциям относятся выборка, проекция, соединение и деление.

Выборка (RESTRICT или SELECT) - это сокращенное название q-выборки, где q обозначает любой скалярный оператор сравнения (=, ¹, ³, > и т.д.). q-выборкой из отношения А по атрибутам X и Y (A WHERE X q Y) (порядок учитывается!) называется отношение, имеющее тот же заголовок, что и отношение А, и тело, содержащее множество всех кортежей t отношения А, для которых проверка условия «X q Y» дает значение истина. Атрибуты X и Y должны быть определены на одном и том же домене, а оператор сравнения q должен иметь смысл для данного домена.

Проекцией (PROJECT) отношения А по атрибутам X, Y, …, Z, где каждый из атрибутов принадлежит отношению А, называется отношение с заголовком {X,Y,…,Z} и телом, содержащим множество кортежей с атрибутами, совпадающими с соответствующими атрибутами отношения А.

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

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

Естественным (или внутренним) соединением отношений А и В (A JOIN B) с заголовками X,Y и Y,Z соответственно и с атрибутами Y, определенными на одном и том же домене, называется отношение с заголовком {X,Y,Z} и телом, содержащим множество кортежей с атрибутами, совпадающими с соответствующими атрибутами отношений А и В.

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

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

Соединение обладает свойствами ассоциативности и коммутативности. Если отношения А и В не имеют общих имен атрибутов, то естественное соединение превращается в декартово произведение. Пример операции соединения показан на рисунке 2.

Рис. 2

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

На рис. 2. показаны два примера деления отношений.

Следует быть осторожным при использовании этой операции (например, при наличии пустых отношений).

1.3 Дополнительные реляционные операции

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

Extend a add expr as z;

Результатом этого выражения будет отношение с заголовком, эквивалентным заголовку отношения А, расширенному новым атрибутом Z, который рассчитывается скалярным выражением expr для кортежа отношения А. При этом отношение А не должно иметь атрибута Z и выражение exp не должно ссылаться на атрибут Z. Кардинальное число результата равно кардинальному числу отношения А, степень равна степени отношения А плюс единица.

Пример: Подсчитать количество поставок, сделанных каждым поставщиком.

EXTEND Поставщики ADD COUNT ((Поставки RENAME П№ AS X) WHERE X= П№) AS Кол_П;

Результат действия этого выражения показан на рис. 3.

Рис. 3

Для данного поставщика в отношении ПОСТАВКИ выражение

((Поставки RENAME П№ AS X) WHERE X= П№);

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

Итоговая функция берет в качестве аргумента множество значений и возвращает одно значение. Кроме функции COUNT (подсчет) итоговыми функциями являются SUM (сумма), AVG (среднее значение), MAX (максимальное значение), MIN (минимальное значение), StDev (среднеквадратичное отклонение от среднего значения поля) и Var (дисперсия значений поля). Если аргумент такой функции будет пустым множеством, то функции COUNT и SUM возвращают нуль, функции MAX и MIN возвращают максимальное и минимальное значения соответствующего домена.

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

SUMMARIZE A BY (A1,A2,…,An) ADD EXP AS Z;

Здесь A1,A2,…,An - отдельные атрибуты отношения А. Результатом этого выражения будет отношение с заголовком {A1,A2,…,An,Z} и с телом, содержащим все такие кортежи t, которые являются кортежами проекции отношения А по атрибутам A1,A2,…,An, расширенного значением для нового атрибута Z. Такое новое значение Z подсчитывается вычислением итогового значения exp по всем кортежам отношения А, которое имеет те же самые значения для атрибутов A1,A2,…,An, что и кортеж t. Кардинальное число результирующего отношения равно кардинальному числу проекции отношения А по атрибутам A1,A2,…,An, а степень равна степени такой проекции плюс единица. Например, количество поставок, сделанных каждым поставщиком может быть получено при помощи выражения

SUMMARIZE Поставки BY (П№) ADD COUNT AS Кол_П;

Результат операции (рис. 4) не содержит сведений о поставках, сделанных поставщиком П5 (сравните с примером, показанным на рис. 1, где поставщик сделал ноль поставок). Причина состоит в том, что поставщика П5 нет в отношении ПОСТАВКИ.

Рис. 4

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

Поставки := Поставки MINUS (Поставки WHERE Кол = 0);

В реляционных системах существуют также явные операторы обновления INSERT, DELETE и UPDATE:

INSERT (Поставщики where Гор_П = Минск ) into Temp;

(здесь выбранные данные вставлены в отношение Temp, которое должно быть совместимым по типу с отношением Поставщики).

Update (Поставщики where Гор_П = Брест) статус := 40;

DELETE Поставщики where Статус < 20;

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

         Примеры использования реляционной алгебры для выражения словесных запросов в виде формул

1. Получить имена поставщиков, которые поставляют деталь Д2.

((Поставки JOIN Поставщики) WHERE Д№=’Д2’) [Имя_П];

. Получить имена поставщиков, которые поставляют по крайней мере одну черную деталь.

(((Детали WHERE Цв = ‘Черный’) JOIN Поставки) [П№] JOIN Поставщики) [Имя_П]

или

(((Детали WHERE Цв = ‘Черный’) [Д№] JOIN Поставки) JOIN Поставщики) [Имя_П]

. Получить имена поставщиков, которые поставляют все детали.