26.45M

Категория:

Базы данных

Похожие презентации:

Виды Зависимостей между атрибутами

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

Проектирование БД

Язык sql

Метод нормализации. Метод «Сущность – связь». Пример проектирования БД

Технология проектирования реляционных баз данных. Нормализация и функциональные зависимости

Принципы управления данными с помощью языка структурированных запросов SQL на примере различных реляционных СУБД

Методы проектирования БД

Введение в базы данных

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

Виды Зависимостей между атрибутами

3.

Виды Зависимостей между атрибутами
• Частично-фукнциональная
• Полная функциональная –
• Транзитивная -

5.

Подумайте
Какими
характеристиками
обладает студент?

6.

Дата
рождения
Номер
телефона
Адрес
Пол
Отделение
Отчество
Курс
Имя
Группа
Фамилия
Стипендия

Фамилия
Имя
Отчество
Пол
Дата
рождения
Телефон
Адрес
Отделение
Курс
Группа
Стипендия
Кустова
Ксения
Анатольевна
женский
04.12.2000
13-45-13
г. Ярославль, ул. Ньютона, д.20, кв.12.
Техническое
3
18КС
есть

8.

Группа: 18КС
Курс: 3
Отделение: техническое

9.

Нормализация
-процесс преобразования данных путем ликвидации
избыточности данных и иных противоречий с целью
приведения таблиц к виду, позволяющему осуществить
непротиворечивое и корректное редактирование данных
- это процесс приведения таблиц БД к ряду нормальных
форм с целью избежания избыточности, аномалий вставки,
удаления и редактирования данных.
• Цель- получение оптимальной структуры данных

10.

5НФ
4НФ
НФБК
3НФ
2НФ
1НФ
Каждая форма –
набор требований

11.

• Декомпозиция (разбиение) таблицы –
процесс деления таблицы с целью
устранения избыточности данных
• Избыточность – повторяющиеся данные,
содержащиеся в БД

12.

Нормальные формы
- 1НФ
-2НФ
-3НФ
- БКНФ (Бойса-Кодда НФ) – усиленная 3НФ
-4НФ
-5НФ
- Доменно-ключевая НФ

13.

1НФ
Определение
• Переменная отношения находится в первой
нормальной форме (1НФ) тогда и только
тогда, когда в любом допустимом значении
отношения каждый его кортеж содержит
только одно значение для каждого из
атрибутов

14.

- В каждом поле таблицы должно быть
только одно значение (атомарность)
- Не должно быть повторяющихся строк

15.

1НФ до нормализации

16.

1НФ после нормализации

17.

2НФ
Определение
• Переменная отношения находится во
второй нормальной форме (2НФ) тогда и
только тогда, когда она находится в 1НФ и
каждый
ее
неключевой
атрибут
неприводимо
(функционально
полно)
зависит от ее потенциального ключа

18.

- Таблица в 1НФ
- Есть первичный ключ
- Все атрибуты зависят от первичного
ключа целиком, а не от какой-то его
части

19.

2НФ до нормализации

20.

21.

2НФ добавим первичный ключ

22.

2НФ декомпозиция

23.

3НФ
Определение
• Переменная отношения находится в
третьей нормальной форме (3НФ) тогда и
только тогда, когда она находится во 2НФ и
отсутствуют транзитивные функциональные
зависимости неключевых атрибутов от
ключевых

24.

- Таблица во 2НФ
- Все атрибуты зависят только от
первичного ключа, но не от других
атрибутов

25.

3НФ до нормализации

26.

3НФ до нормализации

27.

3НФ после нормализации

28.

Проверка и закрепление знаний
• Тестирование в Kahoot
- введите PIN-код, отображенный на экране
- введите Фамилию и Имя в поле NAME
Критерии оценивания
- 1 ошибка – «отлично»
- 2 ошибки – «хорошо»
- 3 ошибки – «удовлетворительно»
- -более трех ошибок – «неудовлетворительно»

29.

Вопросы
Что нового вы сегодня узнали?

30.

Вопросы
Зачем по вашему мнению необходима
нормализация?

31.

Вопросы
Какие нормальные формы вы теперь
знаете?

32.

Вопросы
Где в дальнейшем возможно применение
знаний по теме «Нормализация баз данных»?

33.

34.

35.

Все)
Идем делать практические работы

36.

ОПБД.
НФ БК, 4 НФ, 5
НФ

37.

Бойса-Кодда Нормальная форма
Определение
• Переменная отношения находится в
нормальной форме Бойса — Кодда (иначе
— в усиленной третьей нормальной форме)
тогда и только тогда, когда каждая её
нетривиальная и неприводимая слева
функциональная зависимость имеет в
качестве своего детерминанта некоторый
потенциальный ключ.

38.

• Таблица в 3 НФ
• Ключевые атрибуты не должны
зависеть он НЕключевых

39.

Пример БКНФ
Дано:
Каждый сотрудник может курировать только ту работу для
которой он квалифицирован
– Максим - курирует маркетинг,
– Рома - программирование,
– Илья - дизайн
–…
Есть множество проектов над которыми они работают
Для каждого из проектов могут быть выполнены и
Разработка и дизайн и маркетинг
Куратор по каждому из направлений у проекта может быть
только один (дабы не было путанницы)

40.

БКНФ до нормализации
project_id
Вид работы
Ответственный
1
Разработка
Рома
2
Маркетинг
Максим
2
Дизайн
Илья
1
Дизайн
Илья
3
Маркетинг
Диана
3
Разработка
Миша

41.

Пример БКНФ
• Первичный ключ = составной (Id_Проектa +
Задача)

42.

Пример БКНФ
• Первичный ключ = составной (Id_Проектa +
Задача)
• Но
проявляется
зависимость
части
первичного
ключа
(задачи)
от
ответственного

43.

Пример БКНФ
• Первичный ключ = составной (Id_Проектa +
Задача)
• Но
проявляется
зависимость
части
первичного ключа (задачи) от ответственного
• Зная кем является ответственный можно
четко сказать какую задачу он выполняет в
проекте

44.

БКНФ до нормализации
project_id
Вид работы
Ответственный
1
Разработка
Рома
2
Маркетинг
Максим
2
Дизайн
Илья
1
Дизайн
Илья
3
Маркетинг
Диана
3
Разработка
Миша
Решение?

45.

БКНФ до нормализации
project_id
Вид работы
Ответственный
1
Разработка
Рома
2
Маркетинг
Максим
2
Дизайн
Илья
1
Дизайн
Илья
3
Маркетинг
Диана
3
Разработка
Миша
Декомпозиция

46.

БКНФ после нормализации
id
name
Вид работы
Id_проекта
Id_разработки
1
Рома
Разработка
1
1
2
Максим
Маркетинг
2
2
3
Илья
Дизайн
2
3
4
Илья
Дизайн
1
4
5
Диана
Маркетинг
3
5
6
Миша
Разработка
3
6
работы
проекты

47.

4НФ
Определение
• Переменная отношения находится в
четвёртой нормальной форме, если она
находится в нормальной форме Бойса —
Кодда и не содержит нетривиальных
многозначных зависимостей.

48.

• Таблица находится в НФ БК
• Устраняются многозначные зависимости

49.

4НФ Пример
Для одного сотрудника есть
- МНОЖЕСТВО проектов
- МНОЖЕСТВО увлечений
worker_id
project
hobbie
1
Сантехник
Радиотехника
1
КайзерДом
Гитара
2
FabioRoss
Футбол
3
КайзерДом
Хоккей
3
Доска Почета
Гитара

50.

4НФ Пример
• Атрибуты Проект и Увлечение напрямую зависят
от первого столбца, но друг от друга они
независимы
worker_id
project
hobbie
1
Сантехник
Радиотехника
1
КайзерДом
Гитара
2
FabioRoss
Футбол
3
КайзерДом
Хоккей
3
Доска Почета
Гитара

51.

4НФ Пример
• А что будет если увлечений у данного сотрудника
больше чем проектов?
worker_id
project
hobbie
1
Сантехник
Радиотехника
1
КайзерДом
Гитара
2
FabioRoss
Футбол
3
КайзерДом
Хоккей
3
Доска Почета
Гитара

52.

4НФ Пример
• Еще вариант - выбрать все хобби сотрудников,
которые участвуют в проекте “КайзерДом”
worker_id
project
hobbie
1
Сантехник
Радиотехника
1
КайзерДом
Гитара
2
FabioRoss
Футбол
3
КайзерДом
Хоккей
3
Доска Почета
Гитара

53.

4НФ Пример
worker_id
project
hobbie
1
Сантехник
Радиотехника
1
КайзерДом
Гитара
2
FabioRoss
Футбол
3
КайзерДом
Хоккей
3
Доска Почета
Гитара
Решение?

54.

4НФ Пример
worker_id
project
hobbie
1
Сантехник
Радиотехника
1
КайзерДом
Гитара
2
FabioRoss
Футбол
3
КайзерДом
Хоккей
3
Доска Почета
Гитара
Декомпозиция

55.

4НФ Пример
worker_id
project
worker_id
hobbie
1
Сантехник
1
Радиотехника
1
КайзерДом
1
Гитара
2
FabioRoss
2
Футбол
3
КайзерДом
3
Хоккей
3
Доска Почета
3
Гитара
Декомпозиция

56.

5НФ
Определение
• Переменная отношения находится в пятой
нормальной форме (иначе — в
проекционно-соединительной нормальной
форме) тогда и только тогда, когда каждая
нетривиальная зависимость соединения в
ней определяется потенциальным ключом
(ключами) этого отношения.

57.

• Таблица находится в 4 НФ
• Устраняются нетривиальные зависимости
(Декомпозиция без потерь)

58.

5НФ Пример
worker
project
part
Миша
КайзерДом
Frontend
Рома
КайзерДом
Backend
Рома
Сантехник
API Integration
Ярик
Доска почета
Backend
Миша
Доска Почета
Frontend
Миша
Резина
Frontend
Миша делает только Frontend
Рома наоборот, делает все, кроме Frontend
Миша участвует в большом количестве проектов,
Ярик только в одном
...

59.

5НФ Пример
worker
project
part
Миша
КайзерДом
Frontend
Рома
КайзерДом
Backend
Рома
Сантехник
API Integration
Ярик
Доска почета
Backend
Миша
Доска Почета
Frontend
Миша
Резина
Frontend
Декомпозируем без потерь таким образом, чтобы
при объединении декомпозированных таблиц мы
получили исходную таблицу

60.

5НФ Пример
worker
Миша
Рома
Рома
project
КайзерДом
КайзерДом
Сантехник
worker
part
Миша
Frontend
Рома
Backend
API
Integration
Ярик
Доска
почета
Рома
Миша
Доска
Почета
Ярик
Миша
Резина
Backend
project
part
КайзерДом
Frontend
КайзерДом
Backend
Сантехник
API
Integration
Доска
почета
Backend
Доска
Почета
Frontend
Резина
Frontend
Декомпозируем без потерь таким образом, чтобы
при объединении декомпозированных таблиц мы
получили исходную таблицу

61.

62.

Все)
ДЗ:
• Прочитать примеры в википедии по
нормальным формам.
• Ознакомится с 6НФ и Доменно-ключевой НФ.
• https://ru.wikipedia.org/wiki/%D0%9D%D0%BE%D
1%80%D0%BC%D0%B0%D0%BB%D1%8C%D0%BD
%D0%B0%D1%8F_%D1%84%D0%BE%D1%80%D0
%BC%D0%B0

63.

SQL
- декларативный язык программирования,
применяемый для создания, модификации и
управления данными в реляционной базе данных,
управляемой соответствующей системой управления
базами данных.
- IBM 1970-e - SEQUEL
– целью разработки было создание простого непроцедурного
языка, которым мог воспользоваться любой пользователь,
даже не имеющий навыков программирования
-* Идем в Википедию и читаем подробный разбор
стандартизации SQL

64.

SQL – Srtuctured Query Language –
структурированный язык запросов для
взаимодействия с БД
Задача:
-для предоставления простого и эффективного
способа чтения и записи информации из БД

65.

Расширения SQL
• Многие поставщики СУБД расширили
возможности SQL, введя в язык дополнительные
операторы или инструкции:
• - дополнительная функциональность
• -упрощение определенных операций
• - специфичны для конкретной БД
• - редко поддерживаются более чем одним
поставщиков

66.

Преимущества SQL:
• -не относится к числу патентованных
языков
• - легко изучить (инструкции состоят из
простых английских слов)
• -мощный язык (можно выполнять очень
сложные операции с БД)

67.

SQL - элементы
- операторы определения данных (Data Definition Language, DDL):
– CREATE создаёт объект базы данных (саму базу, таблицу,
представление, пользователя и так далее),
– ALTER изменяет объект,
– DROP удаляет объект;
-операторы манипуляции данными (Data Manipulation Language, DML):
– SELECT выбирает данные, удовлетворяющие заданным условиям,
– INSERT добавляет новые данные,
– UPDATE изменяет существующие данные,
– DELETE удаляет данные

68.

SQL - элементы
- операторы определения доступа к данным (Data Control Language,
DCL):
• GRANT предоставляет пользователю (группе) разрешения на
определённые операции с объектом,
• REVOKE отзывает ранее выданные разрешения,
• DENY задаёт запрет, имеющий приоритет над разрешением;
- операторы управления транзакциями (Transaction Control Language,
TCL):
• COMMIT применяет транзакцию,
• ROLLBACK откатывает все изменения, сделанные в контексте
текущей транзакции,
• SAVEPOINT делит транзакцию на более мелкие участки.

69.

Далее, мы будем
рассматривать только
MySQL как пример РСУБД

70.

Типы данных
Тип данных
Диапазон
UNSIGNED
TINYINT
-128 — 127
0 — 255
SMALLINT
-32 768 — 32 767
0 — 65 535
MEDIUMINT
-8 388 608 — 8 388 607
0 — 16 777 215
INT
-2 147 483 648 — 2 147 483 647
0 — 4 294 967 295
BIGINT
-9 223 372 036 854 775 808 —
9 223 372 036 854 775 807
0 — 18 446 744 073 709 551 615
Указание размера для целочисленных типов ( int(3) ) не меняет диапазон
возможных значений. Это справочно-сервисная информация, которую иногда
используют инструменты для работы с MySQL.

71.

Типы данных-дробные числа
Тип данных
Максимальный диапазон
FLOAT(M,D)
-3,402823466E+38 до -1,175494351E-38, 0, и от
1,175494351E-38 до 3,402823466E+38
DOUBLE(M,D)
-1,7976931348623157E+308 до -2,2250738585072014E-308, 0, и от
2,2250738585072014E-308 до 1,7976931348623157E+308
DECIMAL(M,D)
65 цифр
M — количество отводимых под число символов.
D — количество символов дробной части.
UNSIGNED - запрещает указывать отрицательные значения

72.

Типы данных - строки
Тип данных
Длина
N
VARCHAR(N)
0 - 65535
Переменная
CHAR(N)
0 - 255
Фиксированная (дополняется справа пробелами
при недостаточно длине)

73.

Типы данных
Тип данных
Формат
Примечание
DATE
YYYY-MM-DD
в качестве разделителя может выступать не только дефис «», но и любой символ отличный от цифры
DATETIME
YYYY-MM-DD
HH:mm:SS
ANSI - стандарт даты/времени
TIMESTAMP
00000000000000
в виде количества секунд, прошедших с полуночи 1 января
1970 года по гринвичскому времени
TIME
HH:mm:SS
-838:59:59.000000 до 838:59:59.000000
YEAR
YYYY
TIME, DATETIME, TIMESTAMP поддерживают так же указание дробной
части секунд

74.

Создание БД
-- Простое создание базы данных
CREATE DATABASE test;
-- Создание базы данных с указанием кодировки по
умолчанию
CREATE DATABASE test DEFAULT CHARACTER SET utf8;
-- Создать базу данных, если таковая не существует
-- Если существует - не пытаться создавать
CREATE DATABASE IF NOT EXISTS test;

75.

Удаление/переименование БД
-- Удаление базы данных
DROP DATABASE test;
-- Удаление БД. Если БД не существует - не выдаст
ошибку.
DROP DATABASE IF EXISTS test;
-- Переименование БД
RENAME DATABASE test TO production;

76.

Создание таблицы
CREATE TABLE `department` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

103.

Сортировка полученных данных
ORDER BY заставляет СУБД отсортировать
данные в алфавитном порядке

104.

105.

Сортировка по нескольким столбцам

106.

Сортировка по положению столбца

107.

108.

Фильтрация данных
использование предложения WHERE

109.

Операторы в предложении
WHERE

110.

111.

Сравнение с одиночным значением

112.

113.

114.

115.

116.

117.

Задание)
• 1. Выведите товары без цены
• 2. Вывести клиентов, у которых не указан
email

118.

Ответы

119.

Расширенная фильтрация данных
оператор AND

Инструкция SELECT извлекает все товары,
предлагаемые поставщиками DLL01 и BRS01.
После оператора IN указан список значений
через запятую, а весь список заключен в
скобки.

130.

131.

Преимущества оператора IN
При работе с длинными списками необходимых значений
синтаксис логического оператора IN гораздо понятнее.
При использовании оператора IN совместно с операторами
AND и OR гораздо легче управлять порядком обработки.
Операторы IN почти всегда быстрее обрабатываются, чем
списки операторов OR (впрочем, это сложно заметить в случае
коротких списков).
Самое большое преимущество логического оператора IN
заключается в том, что в нем может содержаться еще одна
инструкция SELECT, а это позволяет создавать очень гибкие
предложения WHERE.

132.

Оператор NOT
Ключевое
слово,
применяемое
в
предложении where для отрицания какоголибо условия.
Пример: извлекается список товаров,
предлагаемых всеми поставщиками, кроме
DLL01.

133.

Логический оператор NOT отрицает
условие, следующее за ним. Поэтому
СУБД извлекает не те значения
vend_id, которые совпадают с DLL01, а
все остальные.

134.

Фильтрация с использованием
метасимволов

135.

136.

137.

138.

1. Вывести название товаров, названия в
которых начинаются на F и заканчиваются на у.
2. Вывести еmail в которых есть сочетание
@forte.com

139.

1. Вывести название товаров, названия в
которых начинаются на F и заканчиваются на
у.

140.

2. Вывести еmail в которых есть сочетание
@forte.com

141.

142.

Метасимвол «знак подчеркивания»(_)
Еще одним полезным метасимволом
является знак подчеркивания (_). Он
используется так же, как и знак %, но при
этом учитывается не множество символов, а
только один.
1. Вывести названия товаров в которых
неизвестны
первые
два
значения
и
заканчивается на словосочетания inch teddy
bear.
2. Повторите первое задание только с
использованием метасимвола % .

143.

Метасимвол
«квадратные скобки» ([])
служит для указания набора символов,
каждый из которых должен совпадать с
искомым значением, причем в точно
указанном месте (в позиции метасимвола).
1. Найти всех клиентов
начинаются на J или M
имена
которых

144.

145.

Метасимвол, обозначающий
противоположное действие (^)
• 1. Найти всех клиентов имена которых НЕ
начинаются на J или M

146.

147.

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

148.

Создание вычисляемых полей
Примеры использования

149.

Конкатинация
Комбинирование
значений
(путем
присоединения их друг к другу) для получения
одного “длинного” значения.
SQL Server для конкатинации использует
оператор «+»
Пример: создать таблицу, в которой в одном
столбце объединены записи из нескольких
столбцов (по условию отчета)

150.

• Чтобы убрать пробелы справа от указанного значения, необходимо
использовать функцию RTRIM (). Отбрасывает все пробелы справа от
указанного значения
• R- право по-английски

151.

152.

153.

Псевдоним
- это альтернативное имя для поля или
значения. Псевдонимы присваиваются с
помощью ключевого слова AS.
Рассмотрим следующую инструкцию SELECT.

154.

155.

Математические вычисления

156.

157.

Математические операторы в SQL

158.

Использование функций обработки
данных
Функции — это операции, которые чаще всего
приходится выполнять над данными, включая
различные преобразования и вычисления.

159.

Применение функций

160.

Функции для работы с текстом

161.

Наиболее часто используемые текстовые
функции

162.

Применение функции SOUNDEX

163.

164.

Функции для работы с датой/временем
• Извлечь список всех заказов, сделанных в
2012 году

165.

Функции для работы с числами

166.

Использование итоговых функций

167.

Итоговые функции

168.

AVG

169.

Самостоятельная работа
1. Найти среднюю цену товара,
предлагаемых поставщиком DLL01.

170.

171.

Функция COUNT
Подсчитывает количество строк:
- общее число строк в таблице
- число
строк,
удовлетворяющих
определенному критерию

172.

• Общее количество клиентов, содержащихся
в таблице Castomers
1. Клиенты, имеющие адреса электронной
почты

173.

Функция MAX
• Возвращает наибольшее значение в
указанном столбце
Указана цена самого дорого товара

174.

Функция MIN
возвращает наименьшее значение в
указанном столбце
Указана цена самого дешевого товар

175.

Функция SUM
Отображает сумму значений столбца

176.

• Вычислить общую стоимость заказа по
каждому элементу в таблице OrderItems,
для которых номер заказа равен 20005

177.

• Вычислить среднее значение цены по
уникальным значениям в таблице Products
для поставщика DLL01

178.

• Выведите одной таблицей количество
элементов в таблице Products, самую
высокую цены, самую низкую и среднюю
стоимость товара

179.