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

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

2.3.1. Язык SQL

Реляционные операции реализуются на языке SQL. Действующим на данный момент стандартом является принятая Американским национальным институтом стандартов (ANSI) версия SQL92.

В СУБД Microsoft Access используется язык Access SQL (Jet SQL), который немного отличается от стандартной версии, но основные операторы и правила - стандартные.

Как и любой язык программирования, язык SQL имеет свои правила записи инструкций. Основные из них:

  1. Запятая используется для разделения элементов списков. Например, списка имен полей.
  2. Для задания имен полей, которые содержат недопустимые символы (например, пробел), используются квадратные скобки. Например, [Дата Рождения].
  3. Если в запрос включены поля нескольких таблиц, то используется полное имя поля, которое состоит из имени таблицы и имени поля, разделенных точкой. Например, Студенты.Фамилия.
  4. Символьные строки заключаются в апострофы или кавычки.
  5. В конце инструкции ставиться точка с запятой (;).
  6. В инструкциях SQL, разбитых на несколько строк, можно использовать отступы, которые указывают на продолжение предыдущей строки.

Основным видом запроса на SQL является SELECT – запрос на выборку. Его общий вид:

SELECT <список имен полей> (если все поля, то *)

FROM <имя таблицы>

[WHERE <условия выбора>] (можно использовать <, >, =, BETWEEN, AND, NOT, OR)

[GROUP BY <имена полей>] (группировка)

[ORDER BY <имена полей>] (сортировка)

Примеры:

1) Вывести из таблицы Студенты имя, фамилию, адрес и телефон, отсортировав по фамилии

SELECT Фамилия, Имя, Адрес, Телефон

FROM Студенты

ORDER BY Фамилия;

2) Вывести все поля таблицы Студенты, произведя группировку по группам

SELECT *

FROM Студенты

GROUP BY Группа;

3) Вывести всех студентов из таблицы Студенты, проживающих на улице Ленина

SELECT *

FROM Студенты

WHERE left([Адрес], 9)=’ул.Ленина’;

4) Выбрать студентов из таблицы Студенты, которые родились в сентябре 1985 года

SELECT *

FROM Студенты

WHERE [Дата Рождения] between 31.08.85 and 1.10.85;

2.3.2. Основные операторы реляционной алгебры

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

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

Реляционные операторы, аналогичные традиционным операциям над множествами:

1) Объединение. Результатом объединения отношений R1 и R2 является отношение R3, содержащее все кортежи, которые принадлежат хотя бы одному из R1 и R2.

В отличие от объединения множеств, результатом является не множество кортежей, а именно отношение. Поэтому кортежи должны быть однородны, т.е. объединяемые отношения должны быть совместимы по типу. Это значит, что:

a) каждое из них имеет одно и то же множество атрибутов;

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

На языке SQL: R1 UNION R2

Например: допустим, в БД Факультет имеются отдельные отношения Лаборанты и Преподаватели. Эти две таблицы можно объединить, в результате получиться отношение, содержащее все данные на сотрудников факультета: и преподавателей, и лаборантов.

2) Пересечение. Результатом пересечения отношений R1 и R2 является отношение R3, содержащее кортежи, принадлежащие и R1, и R2. Для этой операции также должно выполняться условие совместимости по типу.

НаязыкеSQL: R1 Intersect R2

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

3) Вычитание. Результатом вычитания отношения R2 из отношения R1 является отношение R3, все кортежи которого принадлежат R1 и не принадлежат R2. Условие совместимости по типу также должно выполняться.

На языке SQL:R1 Minus R2

Например, из тех же отношений Лаборанты и Студенты базы данных Факультет можно выяснить, какие студенты не работают лаборантами на факультете и наоборот.

4) Произведение (декартово). Результатом произведения отношений R1 и R2 является отношение R3, содержащее все возможные кортежи, которые являются сочетанием двух кортежей, принадлежащих

R1

 

R2

 

R3

a

 

x

 

a

x

b

 

y

 

a

y

c

     

b

x

       

b

y

       

c

x

       

c

y

5) соответственно отношениям R1 и R2.

На языке SQL:R1 TIMES R2

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

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

Собственно реляционные операторы:

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

Можно сказать, что это «горизонтальное» подмножество начального отношения.

На языке SQL:R1 WHERE xθy (θ (тэта) – любой оператор сравнения: <,>,=,…)

Например, из отношения Студенты (№ЗачетнойКнижки, Фамилия, Имя, Отчество, КодГруппы) вывести данные о тех, кто учится в группе ИНФ-31.

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

Можно сказать, что это «вертикальное» подмножество начального отношения.

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

Обратим внимание на частные случаи:

·возможно указание списка всех атрибутов исходного отношения - это тождественная проекция;

·возможно указание пустого списка атрибутов – это нулевая проекция.

Например, из отношения Студенты (№ЗачетнойКнижки, Фамилия, Имя, Отчество, ДатаРождения, Адрес, Телефон, Группа) вывести для всех студентов информацию только о фамилии, имени, отчестве и группе.

7) Соединение. Результатом соединения отношений R1 и R2 является отношениеR3, кортежи которого – это сцепление двух кортежей (принадлежащих соответственно R1 и R2), имеющих общее значение для одного или нескольких общих атрибутов R1 и R2.

Общий случай

 

Частный случай

R1

 

R2

 

R1

 

R2

a

x

 

x

l

 

a

x

 

x

l

b

y

 

y

m

 

b

y

 

y

m

c

z

 

z

n

 

c

z

 

y

n

   

R3

         

R3

   
 

a

x

l

     

a

x

l

 
 

b

y

m

     

b

y

m

 
 

c

z

n

     

b

y

n

 

Причем эти общие значения в результирующем отношении появляются только один раз.

На языке SQL:R1 JOIN R2

Соединение обладает свойствами:

  • ассоциативность: (R1 JOIN R2) JOIN R3=R1 JOIN (R2 JOIN R3);
  • коммутативность: (R1 JOIN R2) JOIN R3=R1 JOIN R2 JOIN R3.

Эта операция имеет несколько разновидностей, но самое распространенное – естественное соединение (на схеме). Есть еще θ(тэта)-соединение. Оно предназначено для случаев, когда два отношения соединяются на основе некоторых условий (xθy), отличных от эквивалентности.

В этом случае на SQL: (R1 TIMES R2) WHERE xθy,т.е. сочетание произведения и выборки.

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

Пример θ-соединения: соединение отношений Студенты и Группы по атрибуту Группа так, чтобы получить информацию о студентах только групп 5 курса.

8) Деление. Дадим определение для частного случая: для отношений R1 (бинарного) и R2 (унарного) результатом деления является отношение R3, содержащее все значения одного

R1

 

R2

a

x

 

x

a

y

 

y

a

z

   

b

x

 

R3

c

y

 

a

9) атрибута R1, которые соответствуют в другом атрибуте всем значениям R2. Для отношений с большим количеством атрибутов – аналогично.

НаязыкеSQL: R1 DIVIDEBY R2

Например, в БД Факультет есть отношение Занятия (Группа, Дисциплина, Преподаватель). Чтобы получить список групп, которые изучают заданный набор дисциплин можно применить деление этого отношения на специально созданное унарное отношение, содержащее заданный набор дисциплин.

2.3.3. Дополнительные операторы реляционной алгебры

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

Операция расширения

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

EXTEND <имя отношения> ADD (<скалярное выражение>) AS< имя нового атрибута>

Примеры:

1)  Пусть есть отношение Преподаватели (КодПреподавателя, Фамилия, Имя, Отчество, ДатаПринятия). Для каждого преподавателя вывести стаж работы.

EXTEND Преподаватели ADD (year(date())-year(ДатаПринятия)) AS Стаж

2)  Частный случай - добавление нового атрибута, заполненного одинаковыми значениями: в отношение СекцииКружки базы данных Факультет добавить атрибут МестоПроведения, заполнив его одинаковыми значениями для всех секций – «ВятГГУ».

EXTEND СекцииКружки ADD («ВятГГУ») AS МестоПроведения

3)  Частный случай - переименование атрибута Адрес отношения Студенты в АдресРегистрации.

EXTEND Студенты ADD (Адрес) AS АдресРегистрации

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

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

SUMMARIZE <имя отношения> BY (<список имен атрибутов>)

ADD< групповая операция> AS <имя поля для итогового значения>

Групповыми операциями могут быть:

sum (<имя атрибута>) – сумма числовых значений;

count (<имя атрибута>) – количество значений;

min (<имя атрибута>) – минимальное значение;

max (<имя атрибута>) – максимальное значение.

Примеры:

1) Имеется отношение Студенты (№ЗачетнойКнижки, Фамилия, Имя, Отчество, КодГруппы) Подсчитать количество студентов в каждой группе.

SUMMARIZE Студенты BY (КодГруппы) ADD count(№ЗачетнойКнижки)

AS КоличествоСтудентов

2)  Имеется отношение Занятия (КодГруппы, Дисциплина, Преподаватель). Подсчитать количество групп, которые изучают более 10 дисциплин.

(SUMMARIZE Занятия BY (КодГруппы) ADD count(Дисциплина) AS N) WHERE N>10

2.3.4. Операции обновления

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

Вставка: INSERT (<реляционное выражение или список атрибутов>) INTO <имя отношения>

Примеры:

1) Вставить новую запись в отношение Студенты.

INSERT (№зачетки=123456; Фамилия=«Иванов»; Имя=«Иван») INTO Студенты

2) В отношение Студенты31группы вставить те записи из отношения Студенты, которыесоответствуют студентам группы ИНФ-31.

INSERT (Студенты WHERE КодГруппы = «ИНФ-31») INTO Студенты31группы

Изменение: UPDATE <имя отношения> <реляционное выражение>

Пример: В отношении Студенты изменить группы «ИНФ-31» на «ИНФ-41».

UPDATE Студенты WHERE КодГруппы=«ИНФ-31» КодГруппы:=«ИНФ-41»

Удаление: DELETE< реляционное выражение>

Пример: Из отношения Студенты удалить всех студентов группы ИНФ-51.

DELETE Студенты WHERE КодГруппы=«ИНФ-51»

2.3.5. Значение реляционной алгебры

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

В качестве примеров можно привести такие применения выражений:

  • определение области выборки (WHERE…);
  • определение области обновления (т.е. данных для вставки, изменения или удаления);
  • определение правил целостности;
  • определение правил безопасности (т.е. данных, для которых осуществляется контроль доступа).

То есть, можно сказать, что выражения обозначают символическую запись намерений пользователя БД, и эта символическая запись существует на языке SQL.

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

Проектирование реляционных баз данных


*****

© 2009-2017 Банк лекций siblec.ru
Лекции для преподавателей и студентов. Формальные, технические, естественные, общественные, гуманитарные, и другие науки.