Базы данных и SQL

1. Базы данных и SQL

Лекция №2

2. ОПЕРАЦИИ РЕЛЯЦИОННОЙ АЛГЕБРЫ

ОСНОВНЫЕ ПОНЯТИЯ
РЕЛЯЦИОННЫХ БАЗ ДАННЫХ
тип данных
домен
атрибут
кортеж
первичный ключ
отношение

3. ПРОЕКЦИЯ

ТИП ДАННЫХ
простые типы данных;
структурированные типы данных;
ссылочные типы данных.
Реляционная модель требует, чтобы типы
используемых данных были простыми
(атомарными)
Требование, чтобы тип данных был простым,
нужно понимать так, что в реляционных
операциях не должна учитываться
внутренняя структура данных.

4. СЕЛЕКЦИЯ

ДОМЕН
• Домен – это семантическое понятие. Домен
можно рассматривать как подмножество значений
некоторого типа данных имеющих определенный
смысл. Домен характеризуется следующими
свойствами:
домен имеет уникальное имя (в пределах базы
данных);
домен определен на некотором простом типе
данных или на другом домене;
домен может иметь некоторое логическое условие,
позволяющее описать подмножество данных,
допустимых для данного домена;
домен несет определенную смысловую нагрузку.

5. ДЕКАРТОВО ПРОИЗВЕДЕНИЕ

АТРИБУТ
• Атрибут отношения А есть пара вида
• <Имя_атрибута : Имя_домена>

6. ОБЪЕДИНЕНИЕ

КОРТЕЖ
Кортеж отношения представляет собой
множество пар вида <Имя_атрибута :
Значение_атрибута>:
(<A1 : Val1>, <A2 : Val2>, …,<An : Valn>),таких,
что значение Vali атрибута Ai принадлежит
домену Di

7. РАЗНОСТЬ

ОТНОШЕНИЕ
• Отношение R, определенное на множестве
доменов D1, D2, …, Dn (не обязательно
различных), содержит две части: заголовок и
тело.
• Заголовок отношения содержит фиксированное
количество атрибутов отношения: (<A1 : D1>, <A2 :
D2>,…,<An : Dn>).
• Тело отношения содержит множество кортежей
отношения.
• Отношение обычно записывается в виде: R(<A1:
D1>, <A2: D2>, …, <An: Dn>), или короче R(A1, A2, …,
An), или просто R.

8. ПЕРЕСЕЧЕНИЕ

ОСНОВНЫЕ ПОНЯТИЯ
РЕЛЯЦИОННЫХ БАЗ ДАННЫХ
Заголовок
отношения
Ключ
Кортеж
Отношение
Наименование
атрибута
Код Фамилия
Имя
ДатаРождения
ДатаНайма
1
Белова
Мария
08-дек-1968
01-май-1992
2
Новиков
Павел
19-фев-1952
14-авг-1992
3
Бабкина
Ольга
30-авг-1963
01-апр-1992
4
19-сен-1958
03-май-1993
5
Воронова Дарья
Андрей
Кротов
04-мар-1955
17-окт-1993
6
Акбаев
Иван
02-июл-1963
17-окт-1993
7
Кралев
Петр
29-май-1960
02-янв-1994
8
Крылова
Анна
09-янв-1958
05-мар-1994
9
Ясенева
Инна
02-июл-1969
15-ноя-1994
Значение
Атрибут
атрибута
Основные понятия реляционных баз данных

9. СОЕДИНЕНИЕ

СООТВЕТСТВИЕ РЕЛЯЦИОННЫХ И
«ТАБЛИЧНЫХ» ТЕРМИНОВ
Реляционный термин
База данных
Схема базы данных
Отношение
Заголовок отношения
Тело отношения
Атрибут отношения
Кортеж отношения
Степень (-арность) отношения
Мощность отношения
Домены и типы данных
Первичный ключ
Соответствующий «табличный»
термин
Набор таблиц
Набор заголовков таблиц
Таблица
Заголовок таблицы
Тело таблицы
Наименование столбца таблицы
Строка таблицы
Количество столбцов таблицы
Количество строк таблицы
Типы данные в ячейках таблицы
Одно или несколько наименований
столбцов таблицы

10. ДЕЛЕНИЕ

ПРИМЕР ОТНОШЕНИЯ
• СОТРУДНИКИ(Код сотрудника, Фамилия,
Зарплата, Номер отдела)
{ (1, Иванов, 1000, 1),
(2, Петров, 2000, 2),
(3, Сидоров, 3000, 1) }
Код сотрудника Фамилия Зарплата Номер отдела
1
Иванов
1000
1
2
Петров
2000
2
3
Сидоров
3000
1

11. ДЕЛЕНИЕ

РЕЛЯЦИОННАЯ БАЗА ДАННЫХ
• Реляционной базой данных называется
набор отношений.
• Схемой реляционной базы данных
называется набор заголовков отношений,
входящих в базу данных.

12. ОСНОВНЫЕ ПОНЯТИЯ РЕЛЯЦИОННЫХ БАЗ ДАННЫХ

ФУНДАМЕНТАЛЬНЫЕ СВОЙСТВА
ОТНОШЕНИЙ
В отношении нет одинаковых кортежей
Кортежи не упорядочены (сверху вниз)
Атрибуты не упорядочены (слева направо)
Все значения атрибутов атомарные

13. ТИП ДАННЫХ

ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ
БАЗ ДАННЫХ С ИСПОЛЬЗОВАНИЕМ
НОРМАЛИЗАЦИИ
• Нормализация отношений – это
пошаговый обратимый процесс
композиции или декомпозиции исходных
отношений в отношения, обладающие
лучшими свойствами при включении,
изменении и удалении данных, назначение
им ключей по определенным правилам
нормализации и выявление всех
возможных функциональных зависимостей.

14. ДОМЕН

НОРМАЛЬНЫЕ ФОРМЫ
первая нормальная форма (1 НФ);
вторая нормальная форма (2 НФ);
третья нормальная форма (3 НФ);
нормальная форма Бойса-Кодда (НФБК);
четвертая нормальная форма (4 НФ);
пятая нормальная форма, или нормальная
форма проекции-соединения (5 НФ или
ПС/НФ).

15. АТРИБУТ

КЛЮЧИ
• Ключ отношения – это группа из одного
или более атрибутов, которая уникальным
образом идентифицирует кортеж. Таким
образом, ключ имеет уникальные в рамках
отношения значения. Если ключ состоит из
нескольких
атрибутов,
он
называется
составным. Ключей может быть несколько;
основной ключ – первичный, его значения не
могут обновляться. Другие ключи называются
возможными или потенциальными ключами.

16. КОРТЕЖ

ОТНОШЕНИЕ СЕКЦИЯ (простой ключ)
Код студента
123
324
210
434
Секция
Лыжи
Плавание
Волейбол
Плавание
Плата
3
4
5
4
ОТНОШЕНИЕ СЕКЦИЯ (составной ключ)
Код студента
210
324
434
123
434
210
123
Секция
Аэробика
Плавание
Аэробика
Лыжи
Плавание
Волейбол
Плавание
Плата
7
4
7
3
4
5
4

17. ОТНОШЕНИЕ

ФУНКЦИОНАЛЬНЫЕ ЗАВИСИМОСТИ
• В отношении R атрибут Y функционально
зависит от атрибута X (X и Y могут быть
составными) в том и только в том случае, если
каждому значению X соответствует в точности
одно значение Y: R.X > R.Y.
Функциональные зависимости обозначаются
следующим образом:
НомерЗачётнойКнижки > ФИО
НомерЗачётнойКнижки > Курс

18. ОСНОВНЫЕ ПОНЯТИЯ РЕЛЯЦИОННЫХ БАЗ ДАННЫХ

БАЗОВЫЕ ОПРЕДЕЛЕНИЯ
• Полная функциональная зависимость
• Детерминант
• Транзитивная функциональная
зависимость
• Неключевой атрибут
• Взаимно независимые атрибуты

19. СООТВЕТСТВИЕ РЕЛЯЦИОННЫХ И «ТАБЛИЧНЫХ» ТЕРМИНОВ

АНОМАЛИЯМИ МОДИФИКАЦИИ
Аномалия удаления
Избыточность
Аномалия обновления
Аномалия вставки
Отношение Секция
(Код студента, Секция, Плата)
Ключ: (Код студента )
Код
студента
Секция
Плата
123
324
210
434
Лыжи
Плавание
Волейбол
Плавание
3
4
5
4

20. ПРИМЕР ОТНОШЕНИЯ

КЛАССЫ ОТНОШЕНИЙ
Первая нормальная форма (1 НФ)
Вторая нормальная форма (2 НФ)
Третья нормальная форма (З НФ)
Нормальная форма Бойса-Кодда (НФБК)
Четвертая нормальная форма (4 НФ)
Пятая нормальная форма (5 НФ)
Взаимосвязь нормальных форм

21. РЕЛЯЦИОННАЯ БАЗА ДАННЫХ

ПЕРВАЯ НОРМАЛЬНАЯ ФОРМА
в отношении не может быть двух одинаковых кортежей;
порядок следования кортежей несущественен;
каждый атрибут должен иметь уникальное имя, но
порядок следования атрибутов в
отношении
несущественен.
значения всех атрибутов должны быть атомарными, и в
качестве значений не допускаются ни повторяющиеся
группы, ни массивы;
отношения должны быть «плоскими» (каждый кортеж
в отношении должен иметь одно и то же количество
атрибутов);
повторяющиеся
атрибуты
нужно
перемещать в отдельные связанные отношения;

22. ФУНДАМЕНТАЛЬНЫЕ СВОЙСТВА ОТНОШЕНИЙ

ПЕРВАЯ НОРМАЛЬНАЯ ФОРМА
в отношении не может быть двух одинаковых кортежей
Фамилия
Долгополова
Прохорова
Кремень
Кремень
Рабочий телефон
2095570
2095045
2095361
2095361

23. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БАЗ ДАННЫХ С ИСПОЛЬЗОВАНИЕМ НОРМАЛИЗАЦИИ

ПЕРВАЯ НОРМАЛЬНАЯ ФОРМА
порядок следования кортежей несущественен
Номер
зачетной
книжки
Фамилия
111111
222222
Иванов
Петров
333333
Сидоров
Имя
Иван
Петр
Телефон
+375171234567, Белтелеком
+375172345634, Белтелеком
+375291234560, MTC
Сидор +375258745779, Life

24. НОРМАЛЬНЫЕ ФОРМЫ

ПЕРВАЯ НОРМАЛЬНАЯ ФОРМА
значения всех атрибутов должны быть атомарными, и в качестве значений не
допускаются ни повторяющиеся группы, ни массивы;
Номер
зачетной
книжки
Фамилия
111111
222222
Иванов
Петров
333333
Сидоров
Имя
Иван
Петр
Телефон
+375171234567, Белтелеком
+375172345634, Белтелеком
+375291234560, MTC
Сидор +375258745779, Life

25. КЛЮЧИ

ПЕРВАЯ НОРМАЛЬНАЯ ФОРМА
отношения должны быть «плоскими» (каждый кортеж в отношении должен
иметь одно и то же количество атрибутов); повторяющиеся атрибуты нужно
перемещать в отдельные связанные отношения
Номер
зачетной
книжки
111111
222222
333333
Фамилия
Иванов
Петров
Сидоров
Имя
Телефон1
Оператор1
Телефон2
Оператор2
Иван +375171234567 Белтелеком
Петр +375172345634 Белтелеком +375291234560 MTC
Сидор +375258745779 Life

26. ОТНОШЕНИЕ СЕКЦИЯ (простой ключ)

ПЕРВАЯ НОРМАЛЬНАЯ ФОРМА
СТУДЕНТ(Номер зачетной книжки,
Фамилия, Имя)
Ключ: (Номер зачетной книжки)
Номер
зачетной
книжки
111111
222222
333333
Фамилия
Иванов
Петров
Сидоров
Имя
Иван
Петр
Сидор
ТЕЛЕФОН(Номер зачетной книжки,
Телефон, Оператор)
Ключ: (Номер зачетной книжки)
Номер
зачетной
книжки
111111
222222
222222
333333
Телефон
Оператор
+375171234567
+375172345634
+375291234560
+375258745779
Белтелеком
Белтелеком
MTC
Life

27. ФУНКЦИОНАЛЬНЫЕ ЗАВИСИМОСТИ

ВТОРАЯ НОРМАЛЬНАЯ ФОРМА
• Проверяйте составные ключи
Код студента
Секция
Плата
100
100
150
150
200
250
250
Футбол
Теннис
Плавание
Волейбол
Теннис
Волейбол
Плавание
3
5
4,5
3,5
5
3,5
4,5
Отношение СЕКЦИЯ, Ключ: (Код студента , Секция)

28. БАЗОВЫЕ ОПРЕДЕЛЕНИЯ

Нормализация отношения СЕКЦИЯ
СТУДЕНТ(Код студента книжки, Секция)
Ключ: (Код студента, Секция)
СЕКЦИЯ(Секция, Плата)
Ключ: (Секция)
Код студента
Секция
Секция
Плата
100
100
150
150
200
250
250
Футбол
Теннис
Плавание
Волейбол
Теннис
Волейбол
Плавание
Футбол
Теннис
Плавание
Волейбол
3
5
4,5
3,5

29. АНОМАЛИЯМИ МОДИФИКАЦИИ

ВТОРАЯ НОРМАЛЬНАЯ ФОРМА
• Отношение R находится во второй нормальной
форме (2 НФ) в том и только в том случае, когда
оно находится в 1 НФ, и каждый неключевой
атрибут функционально полно зависит от
любого возможного ключа этого отношения R.
• Для приведения отношения во 2 НФ необходимо:
– построить его проекцию, исключив атрибуты, которые
не находятся в полной функциональной зависимости от
составного ключа;
– построить дополнительно одну или несколько
проекций на часть составного ключа и атрибуты,
функционально зависящие от этой части.

30. КЛАССЫ ОТНОШЕНИЙ

ТРЕТЬЯ НОРМАЛЬНАЯ ФОРМА
• Проверяйте любые оставшиеся группы
информации. Отношение не должно иметь
транзитивных зависимостей.
Код студента
Общежитие
Плата
100
150
200
250
300
№7
№4
№1
№1
№2
18
20
23
23
20
Отношение ПРОЖИВАНИЕ (Код студента, Общежитие, Плата)

31. ПЕРВАЯ НОРМАЛЬНАЯ ФОРМА

Устранение транзитивной зависимости
в таблице ПРОЖИВАНИЕ
ПРОЖИВАНИЕ
(Код студента, Общежитие)
Ключ: (Код студента )
ОБЩЕЖИТИЕ
(Общежитие, Плата)
Ключ: (Общежитие)
Код студента
Общежитие
Общежитие
Плата
100
150
200
250
300
№7
№4
№1
№1
№2
№7
№4
№1
№2
18
20
23
20

32. ПЕРВАЯ НОРМАЛЬНАЯ ФОРМА

ТРЕТЬЯ НОРМАЛЬНАЯ ФОРМА
• Отношение R находится в третьей
нормальной форме (3 НФ) в том и только в
том случае, если находится во 2 НФ, и каждый
неключевой атрибут не является транзитивно
зависимым от какого-либо ключа R.
Для приведения отношения в 3 НФ необходимо при
наличии зависимости некоторых неключевых атрибутов от
других неключевых атрибутов произвести декомпозицию
отношения. Т. е. неключевые атрибуты, которые зависят от
других неключевых атрибутов, вынести в отдельное
отношение.

33. ПЕРВАЯ НОРМАЛЬНАЯ ФОРМА

Нормальная форма Бойса-Кодда
Каждый кортеж означает, что некоторый студент изучает
определенную дисциплину у указанного преподавателя
Код студента
Предмет
100
600
700
200
400
200
600
Программирование
Психология
Программирование
Программирование
Психология
Психология
Программирование
Преподаватель
Расолько Г.А.
Егоров К.Д.
Буяльская Ю.В.
Расолько Г.А.
Егоров К.Д.
Белова Т.О.
Буяльская Ю.В.
При этом существуют следующие ограничения:
• Каждый преподаватель может преподавать только по один предмет.
• Один предмет могут преподавать несколько преподавателей.
• Для студента один предмет ведет только один преподаватель
• Будем также предполагать, что у преподавателей не может быть
одинаковых фамилий.

34. ПЕРВАЯ НОРМАЛЬНАЯ ФОРМА

Нормальная форма Бойса-Кодда
Код студента
Предмет
100
600
700
200
400
200
600
Программирование
Психология
Программирование
Программирование
Психология
Психология
Программирование
Преподаватель
Расолько Г.А.
Егоров К.Д.
Буяльская Ю.В.
Расолько Г.А.
Егоров К.Д.
Белова Т.О.
Буяльская Ю.В.
Отношение находится в 3 НФ.
Наличие аномалий.
В данном отношении есть два потенциальных ключа
(Код студента, Предмет) и (Код студента, Преподаватель)
Оба ключа являются составными.
Ключи имеют общий атрибут Код студента, т.е. перекрываются

35. ПЕРВАЯ НОРМАЛЬНАЯ ФОРМА

Отношение Учёба
Код студента
Предмет
100
600
700
200
400
200
600
Программирование
Психология
Программирование
Программирование
Психология
Психология
Программирование
Преподаватель
Расолько Г.А.
Егоров К.Д.
Буяльская Ю.В.
Расолько Г.А.
Егоров К.Д.
Белова Т.О.
Буяльская Ю.В.
В данном отношении существует функциональна зависимость
Преподаватель → Предмет и Преподаватель при этом не
является потенциальным ключом, следовательно отношение не
находится в НФБК
Отношение R находится в нормальной форме
Бойса-Кодда (НФБК) в том и только в том случае,
если каждый детерминант является возможным
ключом

36. ПЕРВАЯ НОРМАЛЬНАЯ ФОРМА

Приведение отношения Учёба
к нормальной форме Бойса-Кодда
УЧЁБА
Ключ: (Код студента, Преподаватель)
ПРЕПОДАВАНИЕ
Ключ: Преподаватель
Преподаватель
Расолько Г.А.
Егоров К.Д.
Буяльская Ю.В.
Расолько Г.А.
Егоров К.Д.
Белова Т.О.
Буяльская Ю.В.
Предмет
Программирование
Психология
Программирование
Психология
Код студента
100
600
700
200
400
200
600
Преподаватель
Расолько Г.А.
Егоров К.Д.
Буяльская Ю.В.
Белова Т.О.

37. ВТОРАЯ НОРМАЛЬНАЯ ФОРМА

КОГДА 3 НФ <> НФБК
Отношение имеет два (или больше) потенциальных
ключа
Эти потенциальные ключи являются составными
Они перекрываются (т.е. имеют по крайней мере один
общий атрибут)
На практике комбинация всех трёх условий встречается редко, и для отношений,
в которых не выполняются все эти три условия, 3НФ и НФБК полностью
эквивалентны

38. Нормализация отношения СЕКЦИЯ

ЧЕТВЁРТАЯ НОРМАЛЬНАЯ ФОРМА
Таблица не должна содержать полей для двух
или более независимых, различных фактов
Код студента
Язык программирования
Иностранный язык
110011
110011
110011
110011
333333
333333
896700
C++
Java
C++
Java
Pascal
Java
C++
Английский
Английский
Немецкий
Немецкий
Английский
Английский
Немецкий
Отношение НАВЫКИ.
Ключ: (Код студента, Язык программирования, Иностранный язык)

39. ВТОРАЯ НОРМАЛЬНАЯ ФОРМА

Многозначная зависимость
• Атрибут X многозначно определяет атрибут Y в R
(или Y многозначно зависит от X), если каждому
значению атрибута X соответствует множество
(возможно, пустое) значений атрибута Y, никак не
связанных с другими атрибутами R. То есть для наличия
в отношении многозначной зависимости необходимо
иметь как минимум три атрибута.
• Многозначная зависимость существует, когда
отношение имеет минимум три атрибута, причем два из
них являются многозначными, а их значения зависят
только от третьего атрибута. В отношении R(А, В, С)
существует многозначная зависимость, если А
многозначным образом определяет В и С, а сами В и С не
зависят друг от друга.

40. ТРЕТЬЯ НОРМАЛЬНАЯ ФОРМА

Устранение многозначной
зависимости в отношении НАВЫКИ
ПРОГРАММИРОВАНИЕ(Код студента,
ИНОСТРАННЫЙ_ЯЗЫК(Код студента,
Язык программирования)
Иностранный язык)
Ключ: (Код студента, Язык программирования) Ключ: (Код студента, Иностранный язык)
Код
Язык
студента программирования
Код
студента
Иностранный
язык
110011
110011
333333
333333
896700
110011
110011
333333
896700
Английский
Немецкий
Английский
Немецкий
C++
Java
Pascal
Java
C++
Для приведения отношения в 4 НФ необходимо при
обнаружении
в
отношении
нетривиальных
многозначных зависимостей провести декомпозицию
для исключения таких зависимостей

41. Устранение транзитивной зависимости в таблице ПРОЖИВАНИЕ

ЧЕТВЁРТАЯ НОРМАЛЬНАЯ ФОРМА
Страны, которые посетил студент, и иностранные языки, которые знает студент
Студент
Иванов
Иванов
Иванов
Иванов
Петров
Петров
Сидоров
Страна
Египет
Турция
Египет
Турция
Болгария
Турция
Египет
Иностранный язык
Английский
Английский
Немецкий
Немецкий
Английский
Английский
Немецкий
Студент
Страна
Студент
Иванов
Иванов
Петров
Петров
Сидоров
Египет
Турция
Болгария
Турция
Египет
Иванов
Иванов
Петров
Сидоров
Иностранный язык
Английский
Немецкий
Английский
Немецкий

42. ТРЕТЬЯ НОРМАЛЬНАЯ ФОРМА

ПЯТАЯ НОРМАЛЬНАЯ ФОРМА
• Возможность восстановить исходные
данные, которые были удалены из таблиц
по причине их избыточности

43. Нормальная форма Бойса-Кодда

ПЯТАЯ НОРМАЛЬНАЯ ФОРМА
Предположим, что нужно хранить данные об ассортименте нескольких
дилеров, торгующих продукцией нескольких производителей (перечень товаров
у разных производителей может пересекаться):
• Дилеры (Иванов, Петров) представляют Производителей (Mercedes и Volvo).
• Производители выпускают Товары (автобусы и грузовики).
• Дилеры продают товары.
В большинстве реальных случаев возникают дополнительные ограничения
(бизнес-правила).
Пусть дилеру требуется лицензия на некоторые товары. Например, у Иванова
есть лицензия на продажу автобусов и грузовиков Mercedes и Volvo, а у Петрова
только лицензия на продажу автобусов Mercedes:
Дилер Производитель Товар
Иванов Mercedes
автобус
Иванов Mercedes
грузовик
Иванов Volvo
Иванов Volvo
автобус
грузовик
Петров Mercedes
автобус

44. Нормальная форма Бойса-Кодда

ПЯТАЯ НОРМАЛЬНАЯ ФОРМА
Дилер Производитель Товар
Иванов Mercedes
автобус
Иванов Mercedes
грузовик
Иванов Volvo
Иванов Volvo
Петров Mercedes
автобус
грузовик
автобус
Нельзя выполнить декомпозицию на две таблицы
Дилер Производитель
Производитель
Товар
Иванов Mercedes
Mercedes
автобус
Иванов Volvo
Петров Mercedes
Mercedes
Volvo
грузовик
автобус
Volvo
грузовик

45. Отношение Учёба

ПЯТАЯ НОРМАЛЬНАЯ ФОРМА
Дилер Производитель
Иванов Mercedes
Иванов Volvo
Петров Mercedes
Производитель
Товар
Mercedes
Mercedes
Volvo
автобус
грузовик
автобус
Volvo
грузовик
При соединении двух таблиц появляется «лишняя» строка
Дилер Производитель Товар
Иванов Mercedes
автобус
Иванов Mercedes
грузовик
Иванов Volvo
автобус
Иванов Volvo
Петров Mercedes
грузовик
автобус
Петров Mercedes
грузовик

46. Приведение отношения Учёба к нормальной форме Бойса-Кодда

ПЯТАЯ НОРМАЛЬНАЯ ФОРМА
Дилер
Производитель
Товар
Иванов
Mercedes
автобус
Иванов
Mercedes
грузовик
Иванов
Volvo
автобус
Иванов
Volvo
грузовик
Петров
Mercedes
автобус
Можно выполнить декомпозицию на три таблицы
Дилер Производитель
Производитель
Товар
Иванов Mercedes
Mercedes
автобус
Иванов
автобус
Иванов Volvo
Mercedes
грузовик
Иванов
грузовик
Петров Mercedes
Volvo
автобус
Петров
автобус
Volvo
грузовик
Дилер
Товар