Реляционная алгебра Реляционная модель структуры данных

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

Реляционная модель – структуры данных • Домены: множества, элементы которых рассматриваются как скалярные значения • Отношения: предикаты, заданные на прямом произведении (не обязательно разных) доменов • Атрибуты: аргументы отношений

Реляционная модель • • Домены D 1, D 2, …, Dn Атрибуты A 1, A 2, …, An Кортежи t = <a 1, a 2, …, an> , ai∈Di Формально R : D 1 х. D 2. . Dn→ {0, 1} или R ⊂D 1 х. D 2. . Dn

Еще раз терминология • Домен – множество возможных значений какоголибо атрибута • Таблица - отношение • Экземпляр– конкретное наполнение базы данных • Конкретное наполнение таблицы – тело отношения • Совокупность атрибутов отношения – Заголовок отношения (схема) • Заголовок + тело = значение отношения • Строка таблицы называется кортежем

Заголовок (схема) отношения • Схема отношения - конечное множество упорядоченных пар вида <A, T>, где A называется именем атрибута, а T обозначает имя некоторого базового типа или ранее определенного домена. • По определению требуется, чтобы все имена атрибутов в заголовке отношения были различны. • Количество атрибутов называется арностью (размерностью) отношения.

Отношение – это множество • В реляционной БД не может быть двух одинаковых кортежей в одной таблице

Ключ • Возможный ключ: минимальный набор атрибутов, от которого функционально зависят все остальные (по которому можно определить все остальные) • Первичный ключ: один из возможных ключей • Ключи естественные и суррогатные

Связи • Также хранятся в отношении • Схема данного отношения составляется из ключевых атрибутов объектов, участвующих связи

Реляционная алгебра – механизм манипулирования реляционными данными Все операции производятся над отношениями, и результатом операции является отношение. R=f(R 1, R 2, … , Rn)

Замкнутость • Реляционная алгебра является замкнутой, в качестве аргументов в реляционные операторы можно подставлять другие реляционные операторы, подходящие по типу: R=f(f 1(R 11, R 12, …), f 2(R 21, R 22, …) • В реляционных выражениях можно использовать вложенные выражения сколь угодно сложной структуры.

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

Две группы операций реляционной алгебры • теоретико-множественные операции (4) • специальные реляционные операции (4)

Теоретико-множественные операции 1. 2. 3. 4. Объединение отношений. Пересечение отношений. Разность отношений. Декартово произведение отношений.

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

Объединение union • При выполнении операции объединения (UNION) двух отношений с одинаковыми заголовками производится отношение, включающее все кортежи, которые входят хотя бы в одно из отношений-операндов. • A B = { c: c A OR c B}

Пересечение intersect • пересечением множеств A и B является такое множество C{c}, что для любого c существуют такие элементы a, принадлежащий множеству A, и b, принадлежащий множеству B, что c=a=b; • A B = { c: c A AND c B}

Разность minus • разностью множеств A и B является такое множество C{c}, что для любого c существует такой элемент a, принадлежащий множеству A, что c=a, и не существует такой элемент b, принадлежащий B, что c=b. • A B = { c: c A AND c B}

Есть ли избыточная операция?

Чему тождественно равно выражение (A B) (A B) • (A B) (B (A B)) • (A B) (B A) • A B

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

Декартово произведение times • R (a 1, a 2, …, an) S(b 1, b 2, …, bm), • Заголовок R × S (a 1, a 2, …, an, b 1, b 2, …, bm), • Тело R×S = {< r, s>: r ∈ R , s ∈ S} • Мощность [R × S ]= [R] × [S]

Свойства операций (OP) • Ассоциативность (A OP B) OP C = A OP (B OP C) • Коммутативность (кроме разности) A OP B = B OP A

Специальные реляционные операции 1. Ограничение отношения (селекция) – горизонтальная вырезка. 2. Проекция отношения – вертикальная вырезка. 3. Соединение отношений (по условию, эквисоединение и естественное соединение). 4. Деление отношений.

Селекция • Селекция (выборка) — это операция, которая выделяет множество строк в таблице, удовлетворяющих заданным условиям. • Условием может быть любое логическое выражение. • R (a 1, a 2, …, an), условие f

R where f • Условие ограничения может иметь вид: – (a операция_сравнения b), где а и b – имена атрибутов ограничиваемого отношения; атрибуты a и b определены на одном домене, для значений которого поддерживается операция сравнения; – (a операция_сравнения const), где a – имя атрибута ограничиваемого отношения, а const –константа; атрибут a должен быть определен на домене или базовом типе, для значений которого поддерживается операция сравнения. – Операцией сравнения могут быть = != > >= < <=

Селекция (where) • Условие может состоять из нескольких простых условий, связанных булевскими операторами AND NOT OR Приоритеты – NOT AND OR • Результатом селекции является отношение, заголовок которого совпадает с заголовком отношения-операнда, а в тело входят те кортежи отношения-операнда, для которых значением условия ограничения является выполненным.

Как обойтись только простыми условиями? • A WHERE (comp 1 AND comp 2) (A WHERE comp 1) ? ? (A WHERE comp 2); • A WHERE (comp 1 OR comp 2) (A WHERE comp 1) ? ? (A WHERE comp 2); • A WHERE NOT comp 1 (A WHERE comp 1) ? ? .

Так обойтись только простыми условиями • A WHERE (comp 1 AND comp 2) = (A WHERE comp 1) (A WHERE comp 2); • A WHERE (comp 1 OR comp 2) = (A WHERE comp 1) (A WHERE comp 2); • A WHERE NOT comp 1 = A (A WHERE comp 1).

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

Проекция PROJECT • Проекцией отношения R по атрибутам X, Y, …, Z, где каждый из атрибутов принадлежит отношению , называется отношение с заголовком (X, Y, …, Z) и телом, содержащим множество кортежей вида (x, y, …z) , таких, для которых в отношении найдутся кортежи со значением атрибута X равным x, значением атрибута Y равным y, …, значением атрибута Z равным z. • Синтаксис операции проекции: (X, Y, … Z) (R) = {x, y, …z : a 1, a 2, …, an R AND x= ai 1, y=ai 2, …, z=aim}

Соединение по условию – JOIN Тэта-соединение • Три операнда – соединяемые отношения и простого условия. • Условие – см. селекцию. • Операнды совместимы по взятию декартова произведения. • A JOIN B WHERE f = (A × B) WHERE f. • R⊳⊲ f. S = σf(R×S)

Эквисоединение • Операция соединения называется операцией эквисоединения (EQUI JOIN) , если условие соединения имеет вид (a = b), где a и b – атрибуты разных операндов соединения. • Если соединение происходит по атрибутам с одинаковыми наименованиями, то в результирующем отношении появляется два атрибута с одинаковыми значениями. => проекция по всем атрибутам, кроме одного из дублирующих.

Естественное соединение NATURAL JOIN • Операция естественного соединения применяется к паре отношений R(A, X) и S(X, B), обладающих (возможно, составным) общим атрибутом X (т. е. атрибутом с одним и тем же именем и определенным на одном и том же домене). • T(A, X, B) - в синтаксисе естественного соединения не указываются, по каким атрибутам производится соединение. Естественное соединение производится по всем одинаковым атрибутам.

Естественное соединение через другие операции?

Естественное соединение через другие операции • • • Переименование Декартово произведение Селекция Проекция R⊳⊲S = атрибуты R, SS. AσR. A=S. A(R×S)

Деление • По определению, результатом деления A на B (A DIVIDE BY B) является «унарное» отношение C (a), тело которого состоит из кортежей v таких, что в теле отношения A содержатся кортежи <v, w> для любого w из B. • Запросы, реализуемые с помощью операции деления, обычно в своей формулировке имеют слово "все". • (A DIVIDE BY B) = С : С×B ⊂ A

Деление • Пусть заданы два отношения: • A с заголовком {a 1, a 2, . . . , an, b 1, b 2, . . . , bm} • B с заголовком {b 1, b 2, . . . , bm}. • Будем считать, что атрибут bi отношения A и атрибут bi отношения B (i = 1, 2, …, m) не только обладают одним и тем же именем, но и определены на одном и том же домене. • Назовем множество атрибутов {aj} составным атрибутом a, а множество атрибутов {bj} – составным атрибутом b. • После этого будем говорить о реляционном делении «бинарного» отношения A{a, b} на унарное отношение B{b}.

Деление R DIVIDE S = 1, 2, . . . r-s(R)- 1, 2, . . . r-s(R)x. S)-R).

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

SELECT "I do not seek, I find" – Pablo Picasso

$Формат оператора SELECT [ALL | DISTINCT ] {*|[имя_столбца [AS новое_имя]]} [, . . .$

Формат оператора SELECT [ALL | DISTINCT ] {*|[имя_столбца [AS новое_имя]]} [, . . . n] [INTO new_table ] FROM имя_таблицы [[AS] псевдоним] [, . . . n] [WHERE <условие_поиска>] [GROUP BY имя_столбца [, . . . n]] [HAVING <критерии выбора групп>] [ORDER BY имя_столбца [, . . . n]]

Обработка элементов оператора SELECT : • FROM – определяются имена используемых таблиц; • WHERE – выполняется фильтрация строк объекта в соответствии с заданными условиями; • GROUP BY – образуются группы строк , имеющих одно и то же значение в указанном столбце; • HAVING – фильтруются группы строк объекта в соответствии с указанным условием; • SELECT – устанавливается, какие столбцы должны присутствовать в выходных данных; • ORDER BY – определяется упорядоченность результатов выполнения операторов.

Пример простого запроса SELECT * FROM person Select attr 1, attr 2, attr 3 -обеспечивает независимость программы от изменений структуры таблицы SELECT * FROM person WHERE name=‘Анна’

Проекция SELECT name FROM person SELECT name, birth FROM person

Уникальные строки SELECT name FROM person SELECT DISTINCT name FROM person

Упорядочение строк SELECT name FROM person ORDER by name SELECT name FROM person ORDER BY birth DESC, name ASC

Ограничим результат SELECT [ TOP (expression) [PERCENT] [ WITH TIES ] ] name FROM person ORDER by name

Ограничим результат SELECT TOP (10) PERCENT WITH TIES name FROM person ORDER by name

Ограничим результат SELECT name FROM person ORDER by name SELECT name FROM person ORDER BY birth DESC, name ASC

Список атрибутов SELECT * table. * column 1, column 3 table. column 1, table. column 3 FROM table

Переименование атрибутов SELECT Фирма AS Company, Фамилия AS Name FROM Клиент

Вычисляемые атрибуты SELECT Фирма, Фамилия+’ ‘+ Left(Имя, 1)+’. ’+Left(Отчество, 1)+’. ’AS ФИО FROM Клиент

Вычисляемые атрибуты SELECT name AS first_name , zarplata*0, 13 AS nalog , ‘student’ AS status FROM person

Создание новой таблицы SELECT name AS first_name , zarplata*0, 13 AS nalog , ‘student’ AS status INTO new_table FROM person

WHERE () NOT AND OR • Выражение вычисляется слева направо. • Первыми вычисляются подвыражения в скобках. • Операторы NOT выполняются до выполнения операторов AND и OR. • Операторы AND выполняются до выполнения операторов OR.

WHERE • Сравнение: сравниваются результаты вычисления одного выражения с результатами вычисления другого. • • Диапазон Принадлежность множеству Значение NULL Соответствие шаблону

Селекция сравнение SELECT Название, Цена FROM Товар WHERE Цена>=100 AND Цена<=150

WHERE • Сравнение • Диапазон: проверяется, попадает ли результат вычисления выражения в заданный диапазон значений. • Принадлежность множеству • Значение NULL • Соответствие шаблону

Селекция диапазон SELECT Название, Цена FROM Товар WHERE Цена BETWEEN 100 AND 150

WHERE • Сравнение • Диапазон • Принадлежность множеству: проверяется, принадлежит ли результат вычислений выражения заданному множеству значений. • Значение NULL • Соответствие шаблону

Селекция принадлежность множеству SELECT Фамилия, Город FROM Клиент WHERE Город IN (‘Москва’, ‘Самара’)

WHERE Сравнение Диапазон Принадлежность множеству Значение NULL: проверяется, содержит ли данный столбец определитель NULL (неизвестное значение). • Соответствие шаблону • •

Селекция пустое значение SELECT Фамилия FROM Клиент WHERE Телефон Is Null SELECT Клиент. Фамилия, Клиент. Телефон FROM Клиент WHERE Клиент. Телефон Is Not Null

WHERE • • • Сравнение Диапазон Принадлежность множеству Значение NULL Соответствие шаблону: проверяется, отвечает ли некоторое строковое значение заданному шаблону.

Селекция поиск по образцу SELECT Клиент. Фамилия, Клиент. Телефон FROM Клиент WHERE Клиент. Телефон Like ‘_4%’ SELECT Клиент. Фамилия, Клиент. Телефон FROM Клиент WHERE Клиент. Телефон Like ‘_[24]%’

Селекция поиск по образцу • Символ % – вместо этого символа может быть подставлено любое количество произвольных символов. • Символ _ заменяет один символ строки. • [] – вместо символа строки будет подставлен один из возможных символов, указанный в этих ограничителях. • [^] – вместо соответствующего символа строки будут подставлены все символы, кроме указанных в ограничителях.

Реляционные операции в SQL: проекция/селекция SELECT L FROM R WHERE C В реляционной алгебре: ( c(R)) L

Реляционные операции в SQL: произведение SELECT * FROM person, dept

Декартово произведение - пример

Агрегатные функции • COUNT (Выражение) - определяет количество записей в выходном наборе; • MIN/MAX (Выражение) – наименьшее/ наибольшее из множества значений; • AVG (Выражение) - среднее значение. • SUM (Выражение) - сумма множества значений.

Агрегирование • aggregate_function ([ALL | DISTINCT] выражение) • Можно использовать только после SELECT и HAVING • В качестве выражения могут быть: любая константа, функция комбинация из названий столбцов, констант и функций, соединенных арифметическими или битовыми операциями.

Агрегирование без группировки SELECT COUNT(*), SUM(salary), AVG(salary) FROM emp • COUNT(*) – подсчет всех строк, включая NULL • Остальные функции перед вычислением отбрасывают нулевые значения. Если DISTINCT отбрасываются дубликаты

Count() T 1 Курс Группа Имя Балл Телефон 4 1 Иванов 5 NULL 4 1 Петров 4 NULL 4 3 Сидоров 3 777 -77 -77 4 3 Иванов 4 NULL NULL SELECT COUNT(*), COUNT(Имя), COUNT (DISTINCT Группа), COUNT(Телефон) FROM T 1

Count() T 1 Курс Группа Имя Балл Телефон 4 1 Иванов 5 NULL 4 1 Петров 4 NULL 4 3 Сидоров 3 777 -77 -77 4 3 Иванов 4 NULL NULL SELECT COUNT(*), COUNT(Имя), COUNT (DISTINCT Группа), COUNT(Телефон) FROM T 1 5, 4, 2, 1

Среднее значение SELECT AVG(Балл) AS Ср_балл FROM T 1

Выбор полей • Если список в предложении SELECT содержит итоговые функции, а в тексте запроса отсутствует фраза GROUP BY, обеспечивающая объединение данных в группы, то ни один из элементов списка предложения SELECT не может включать каких-либо ссылок на поля, за исключением ситуации, когда поля выступают в качестве аргументов итоговых функций.

Агрегирование с группировкой SELECT группа, AVG(балл) AS Ср_балл FROM T 1 GROUP BY группа

Агрегирование с группировкой нескольких полей SELECT Курс, Группа, AVG(Балл) AS Ср_балл FROM T 1 GROUP BY Курс, Группа

Условие на группировку SELECT Группа, AVG(балл) AS Ср_балл FROM T 1 GROUP BY Группа HAVING COUNT(*)>1

Агрегирование с группировкой T (курс, группа, фамилия, балл) SELECT курс, группа, AVG(балл) FROM T GROUP BY курс, группа HAVING MIN(балл) >= 4

Вложенные запросы Select *, (select deptname from dept where dno=emp. dno) From emp

Вложенные запросы Select * From emp, (select * from dept) dd Where dd. dno = emp. dno

Вложенные запросы SELECT Max(балл) FROM T

Вложенные запросы SELECT фамилия, Max(ср_балл) FROM T

Вложенные запросы SELECT фамилия, ср_балл FROM T WHERE ср_балл =(SELECT Max(ср_балл) FROM T)

Вложенные запросы SELECT курс, группа, AVG(ср_балл) FROM T GROUP BY курс, группа HAVING AVG(ср_балл)>(SELECT AVG(ср_балл) FROM T)

Операции соединения в SQL SELECT * FROM emp, dept WHERE emp. dno = dept. dno SELECT * FROM Emp JOIN dept ON emp. dno=dept. dno

$Внешнее соединение SELECT * FROM имя_таблицы_1 {[INNER] | LEFT | RIGHT|FULL} JOIN имя_таблицы_2 ON$

Внешнее соединение SELECT * FROM имя_таблицы_1 {[INNER] | LEFT | RIGHT|FULL} JOIN имя_таблицы_2 ON условие_соединения

$Внешнее соединение SELECT * FROM имя_таблицы_1 {[INNER] | LEFT | RIGHT|FULL} JOIN имя_таблицы_2 ON$

Внешнее соединение SELECT * FROM имя_таблицы_1 {[INNER] | LEFT | RIGHT|FULL} JOIN имя_таблицы_2 ON условие_соединения Различные типы операций соединения: • тета-соединение R FS; • соединение по эквивалентности R =S; • естественное соединение R S; • внешнее соединение R S; • полусоединение RFS.

Внешнее соединение • JOIN: возвращает строки, когда есть хотя бы одно совпадение в обоих таблицах • LEFT JOIN: возвращает строки из левой таблицы, даже если нет ни одного совпадения в правой • RIGHT JOIN: возвращает строки из правой таблицы, даже если нет ни одного совпадения в левой • FULL JOIN: возвращает строки из обеих таблиц

Группа 101 102 103 Студент Сидоров Петров Иванов Николаев Ин_язык Английский Немецкий Английский Испанский S Аудитория Ин_язык 5 Английский 33 Немецкий 24 Французский R

INNER JOIN SELECT Группа, студент, S. ин_язык, аудитория FROM S JOIN R ON S. Ин_язык = R. Ин_язык Группа Студент Ин_язык Аудитория 101 Сидоров Английский 5 101 Петров Немецкий 33 102 Иванов Английский 5

LEFT JOIN SELECT Группа, студент, S. ин_язык, аудитория FROM S LEFT JOIN R ON S. Ин_язык = R. Ин_язык Группа Студент Ин_язык Аудитория 101 Сидоров Английский 5 101 Петров Немецкий 33 102 Иванов Английский 5 103 Николаев Испанский NULL

RIGHT JOIN SELECT Группа, студент, S. ин_язык, аудитория FROM S RIGHT JOIN R ON S. Ин_язык = R. Ин_язык Группа Студент Ин_язык Аудитория 101 Сидоров Английский 5 101 Петров Немецкий 33 102 Иванов Английский 5 NULL Французский 24

FULL JOIN SELECT Группа, студент, S. ин_язык, аудитория FROM S FULL JOIN R ON S. Ин_язык = R. Ин_язык Группа Студент S. Ин_язык Аудитория 101 Сидоров Английский 5 101 Петров Немецкий 33 102 Иванов Английский 5 103 Николаев Испанский NULL Французский 24

JOIN одинаковых таблиц SELECT a. *, b. * FROM S as a JOIN S as b ON a. Ин_язык= b. Ин_язык

JOIN «без условий» SELECT * FROM S JOIN R ON 1=1