Рис. 6. Схема операций реляционной алгебры Основная идея реляционной алгебры состоит в том, что средства манипулирования отношениями, рассматриваемыми как множества, основаны на традиционных множественных операциях, дополненных некоторыми специфическими операциями для БД.
Существует много подходов к определению реляционной алгебры, которые различаются набором операций и способами их интерпретации, но в принципе все они более или менее равносильны. Опишем вариант алгебры, предложенный Коддом. В этом варианте набор алгебраических операций состоит из восьми основных. Проекция отношения – при осуществлении проекции отношения на заданный набор его атрибутов будет получено отношение, кортежи которого взяты из соответствующих кортежей первоначального отношения. Алгебраическая запись проекции имеет вид: Т = R[X], где R – исходное отношение, T – результирующее отношение, X – список атрибутов в структуре отношения Т (условие проекции).
Пример Рассмотрим два отношения: Т1, содержащее сведения о заявке на изделия и Т2, в котором указаны цены на изделия и комплектующие. Т1 Отправитель Получатель Адрес получателя Изделие Количество на месяц Прибор Полет Русская, 86 ХТ – 12 1000 Дальтехника Скала Луговая,91 АМ – 18 350 Прибор Сокол Карбышева, 86 ХТ – 12 800 Звезда Луч Калинина,80 ЛН – 15 500 Т2 Изделие Цена Комплектующие ХТ – 12 100 АСК АМ – 18 150 ТРН ЛН – 15 130 НЕК Если требуется отношение Х1, содержащее сведения только о получателе, то оно образуется в результате выполнения проекции Х1 = Т1[Получатель, адрес получателя] и имеет вид Х1 Получатель Адрес получателя Полет Русская, 86 Скала Луговая,52 Сокол Карбышева, 16 Луч Калинина, 80 Выборка отношения – результатом выборки отношения по некоторому условию является отношение, которое включает только те кортежи первоначального отношения, которые удовлетворяют этому условию. Существуют две простейшие разновидности условия выборки:
Условие вида Имя-атрибута Значение, где допускаются знаки сравнения =, #, >, =>, Условие вида Имя-атрибута 1 Имя атрибута 2. Например, Факт > План. Алгебраическая запись выборки имеет вид T = R[p], где R – исходное отношение, T – результирующее отношение, р – условие выборки. Например, X2 = T1[Количество в месяц > 800]. Х2 Отправитель Получатель Адрес получателя Изделие Количество на месяц Прибор Полет Русская, 86 ХТ – 12 1000 Прибор Сокол Карбышева, 86 ХТ – 12 800 При выполнении операции объединения двух отношений будет получено отношение, включающее все кортежи, входящие в участвующие в операции отношения. Х3 = U(R1, R2). В качестве результата операции пересечения двух отношений получается отношение, включающее все кортежи, входящие в оба первоначальные отношения, Х4 = I(R1, R2). Отношение, являющееся разностью двух отношений, включает все кортежи, входящие в первое отношение и одновременно такие, что ни один из них не входит в отношение являющееся вторым, Х5 = M(R1, R2). При соединении двух отношений по некоторому условию образуется результирующее отношение, кортежи которого являются сочетанием кортежей первого и второго отношений, удовлетворяющим этому условию. Условие соединения имеет вид:
Имя- атрибута-1 Имя-атрибута-2, где Имя- атрибута-1 находится в одном исходном отношении, Имя- атрибута-2 в другом. Операция имеет следующее обозначение Х6 = R1[p]R2, где p – условие соединения. Наиболее важный частный случай соединения называется натуральным соединение и имеет следующие особенности: знаком сравнения в условии соединения является =; Имя- атрибута-1 и Имя-атрибута-2 должны совпадать, а точнее содержать пересечение списков атрибутов исходных отношений; список атрибутов результирующего отношения образуется в результате объединения списков атрибутов исходных отношений. Обозначение натурального соединения не содержит условия соединения и имеет вид Х7 = R1*R2. Операция натурального соединения имеет ряд свойств, например коммутативность и ассоциативность. Свойство коммутативности означает, что операции X8 = R*S и X9 = S*R порождают одно и то же отношение. Свойство ассоциативности означает, что операция X10 = (R*S)*Т и операция X11 = R*(S*Т) дают одинаковый результат. Описание операции деления начнем с примера. Пусть существует отношение Х12, в котором для каждого специалиста указано программное средство, которым он владеет. Определить специалистов, которые владеют MS Excel и Project-Expert. Х12 ФИО Программное средство Иванов MS Excel Иванов Project-Expert Петров MS Word Петров MS Excel Петров MS Access Сидоров MS Word Сидоров MS Excel Сидоров Project-Expert Определим операцию, называемую «образ». В отношении Т(А, В) образом значения а атрибута А является множество значений атрибута В, и каждый элемент b этого множества образует вместе с а некоторую строку (или часть строки) отношения Т. im B(a) = {b1, b2, …, bk}, где im – знак операции «образ», а – значение, образ которого вычисляется, B – имя атрибута для образа значения а b1, b2, …, bk – значения атрибута В. Задача решается путем вычисления образа значений MS Excel и Project-Expert и последующего пересечения найденных образов. im ФИО(«MS Excel») = {«Иванов», «Петров», «Сидоров»} im ФИО(«Project-Expert») = {«Иванов», «Сидоров»} im ФИО(«MS Excel») ? im ФИО(«Project-Expert») = {«Иванов», «Сидоров»} Таким образом, выполнена операция деления исходного отношения на отношение, которое имеет вид: Х13 MS Excel Project-Expert Результат деления содержит следующие значения: Х14 Иванов Сидоров Операция деления имеет вид: Q = D(W, V), где D – знак операции деления. § 2.1.3. Операции над отношениями с применением конструкций языка SQL Structured Query Language – язык структурированных запросов – используется при: формировании запросов к реляционным базам данных, таким как MS Access; обновлении реляционных баз данных; управлении реляционными базами данных. Результаты выполнения запросов можно обрабатывать с помощью специальных групповых функций, таких как максимум, минимум, среднее значение и т.д. Список операторов, предикатов и директив с соответствующими параметрами Ключевое слово Перевод
Select Выбрать
From Из Where условие поиска При
In В Inner join Объединяя
Group by Группируя по
Having Имеющие
Order by Упорядочивая по
As Как All, distinct Все, различные Distinctrow, top Различные ряды, первые При составлении запросов необходимо руководствоваться следующими правилами: составляющие один запрос директивы не должны быть разделены символами «конец строки». Для принудительного перехода можно использовать комбинацию клавиш Ctrl+Enter; обязательными компонентами запроса являются Select и From; Select является первым в определении запроса; при указании более, чем одного поля , их имена отделяются запятыми; перечислять имена полей необходимо в той последовательности, в которой они будут показаны в запросе; если имя поля содержит знак пробела или другой разделитель, имя следует заключить в квадратные скобки;
если в запросе обрабатывается несколько таблиц, то во избежание неопределенности в списках полей рекомендуется приводить полную спецификацию поля, т.е. , ; каждый запрос заканчивается ; Оператор SELECT SELECT Клиенты.Фирма FROM Клиенты, [Заинтересованные лица] WHERE Клиенты.Фирма = [Заинтересованные лица].Фирма; Выборка будет содержать все фирмы, которые присутствуют как в таблице «Заинтересованные лица», так и в таблице «Клиенты». Директива FROM SELECT Клиенты.* FROM Клиенты; С помощью * из таблицы можно выбрать все поля. Директива WHERE Является необязательной, но если присутствует, то должна следовать за директивой FROM. Access выберет записи данных, которые соответствуют определенным в WHERE условиям отбора. Если WHERE отсутствует, то будут выбраны все записи данных. Параметр IN Данный параметр используется при работе с базами данных других форматов, с которыми может работать Access, а так же для отбора данных из неактивной базы Access. SELECT Клиенты.Фирма FROM Клиенты, [Заинтересованные лица] IN C:DBASEDATACLEENTS «DBASEIV;» WHERE Клиенты.Фирма = [Заинтересованные лица].Фирма; С помощью данного параметра можно сформировать только одну связь к внешней БД. Для указания формата этой БД необходимо после формата поставить ; и все выражение взять в кавычки. Предикаты 1. ALL SELECT ALL Фамилия, Имя, Отчество FROM Клиенты; Access выберет перечисленные поля из всех записей данных в таблице Клиенты. Предикат представляет собой дополнительный фильтр. Если предикаты не используются, то по умолчанию считается, что применен предикат ALL. Следующие два примера позволяют выполнить одинаковые выборки. SELECT ALL * FROM Клиенты; SELECT * FROM Клиенты; 2. DISTINCT SELECT DISTINCT Клиенты.Фирма FROM Клиенты, Заинтересованные лица WHERE Клиенты.Фирма = Заинтересованные лица.Фирма; Директива WHERE задает условия отбора, в соответствии с которыми в выборку включаются те фирмы, которые присутствуют как в таблице Клиенты, так и в таблице Заинтересованные лица. Предикат DISTINCT используется в том случае, если нужно включить в выборку только уникальные значения для выбираемых полей. Предикат DISTINCT приводит к отбрасыванию дублируемых значений поля «Фирма». DISTINCTROW SELECT DISTINCTROW [Название фирмы] FROM Клиенты INNER JOIN Заказы ON Клиенты.[Код клиента] = Заказы.[Код клиента] ORDER BY [Название фирмы]; Предикат DISTINCTROW используется, когда следует пропустить данные, представляющие собой полностью дублирующие записи в выборке, но не дублирование значений отдельных полей. Приведен пример запроса для составления списка фирм, которые сделали хотя бы один заказ. Предикат TOP Используется для включения в выборку определенного числа записей, расположенных в начале или в конце группы записей, отобранных с помощью критерия отбора WHERE, упорядоченных с помощью директивы ORDER BY. Предположим, что нужно отобрать 25 лучших студентов выпуска 2001 года: SELECT TOP 25[Фамилия], [Имя], [Отчество] FROM Студенты WHERE [Год выпуска] = 2001 ODER BY [Средний балл] DESC; Можно использовать ключевое слово для того, чтобы включить в выборку определенный процент из верхней или нижней части диапазона, отсортированного с помощью директивы ORDER BY. SELECT TOP 10 PERCENT [Фамилия], [Имя], [Отчество] FROM Студенты WHERE [Год выпуска] = 2001 ORDER BY [Средний балл] DESC; Используемое число в предикате TOP должно быть целым без знака. Операция объединения INNER JOIN Операция формирует связь эквивалентности, являясь частью параметра FROM Такая связь является наиболее употребительным типом объединения. Объединение производится при условии равенства содержимого полей, приведенных после ключевого слова ON в записях таблиц, указанных в операции INNER JOIN. Записи их двух таблиц объединяются при обнаружении совпадающих значений в указанных полях. Такое объединение записей используется наиболее часто. ТАБЛИЦА 1 INNER JOIN ТАБЛИЦА 2 ON ТАБЛИЦА 1.Поле А = ТАБЛИЦА 2.Поле В В данном случае устанавливается связь между ТАБЛИЦА 1 и ТАБЛИЦА 2. В выходной набор будут включены записи из этих таблиц при условии равенства содержимого: ТАБЛИЦА 1.Поле А = ТАБЛИЦА 2.Поле В. В операции могут участвовать два числовых поля любого типа или , если поля не числовые, поля одинакового типа и размера. Директива GROUP BY При использовании директивы GROUP BY все записи, содержащие в заданном поле идентичные значения, объединяются в один элемент выходного набора. В нашем примере повторяющиеся имена фирм будут включены в выходной набор только один раз. Используя параметр GROUP BY, необходимо учитывать, что этот параметр не является обязательным, а является уточняющим при использовании параметра FROM и WHERE. SELECT [Название товара], Sum([Стоимость товара]) FROM Товары GROUP BY [Название товара]; Директива HAVIHG Эта директива используется для фильтрации записей после группирования только в случае использования в запросе директивы GROUP BY. Отличие WHERE и HAVING Директива WHERE определяет, какие записи данных должны участвовать в группировании, т. е. фильтрует записи до группировки. HAVING oпределяет, какие из получившихся в результате группировки записи будут включены в результирующую выборку, т. е. фильтрует записи после группирования. SELECT Отдел, Count([Отдел]) FROM Сотрудники GROUP BYОтдел HAVING Count(Отдел)>50; В выборку будут включены отделы, в которых число сотрудников более 50 человек. Директива ORDER BY Директива определяет список полей и порядок сортировки записей данных, включенных в выборку. Директива является обязательной только при использовании предиката TOP. В других случаях необязательна. По умолчанию используется сортировка по возрастанию. Этому порядку сортировки соответствует ключевое слово ASC. Ключевое слово DESC задает сортировку по убыванию. ORDER BY Является последней директивой в запросе. Порядок перечисления полей задает иерархию сортировки. Прежде всего записи в выборке сортируются по первому указанному в ORDER BY полю, затем записи с совпадающими значениями первого поля сортируются по второму по второму и т. д. Групповые функции С помощью групповых функций можно получить ряд обобщающей статистической информации. К групповым функциям относятся следующие функции: Обозначение Функция Назначение Sum Сумма Вычисляет сумму сгруппированных значений Avg Среднее Вычисляет среднее значение сгруппированных значений Min Минимум Находит наименьшее из сгруппированных значений Max Максимум Находит наибольшее из сгруппированных значений Count Счетчик Находит количество сгруппированных записей