Логическое (даталогическое) проектирование БД

1. Логическое проектирование

Логическая (даталогическая модель)

2. 2. ЛОГИЧЕСКОЕ (ДАТАЛОГИЧЕСКОЕ) ПРОЕКТИРОВАНИЕ

Преобразование инфологической модели в модель данных.
Разработка осуществляется в терминах конкретной модели
данных, но без учета конкретной СУБД.
2

3. 2. Логическая модель

Логическое проектирование состоит
нескольких этапов:
2.1 нормализация таблиц (для реляционных
моделей данных);
2.2 преобразование концептуальной модели
данных в логическую модель
3

4. 2.1Нормализация

Цель нормализации –
избавиться от
избыточности
данных,
приводящих к
аномалиям вставки, удаления и обновления
данных в отношении.
4

5. НОРМАЛИЗАЦИЯ

Процесс нормализации был предложен Коддом в 1972 г
Нормализация предлагает формальный аппарат ограничений
на формирование отношений.
Нормализация это последовательность тестов для таблиц с
целью проверки на соответствие требованиям заданной
нормальной формы .
5

6. В теории реляционных БД выделяются следующие нормальные формы:

Первая нормальная форма
Вторая нормальная форма
Третья нормальная форма
Нормальная форма Бойса – Кодда
1НФ
2НФ
3НФ
БКНФ
Четвертая нормальная форма
4НФ
Пятая нормальная форма 5НФ или форма
проекции-соединения
6

7.

По правилам нормализации есть семь нормальных форм
баз данных:
● первая,
● вторая,
● третья,
● нормальная форма Бойса-Кодда,
● четвёртая,
● пятая,
● шестая не может быть подвергнута
дальнейшей декомпозиции без потерь
7

8. НОРМАЛИЗАЦИЯ

Нормализация отношений последовательно может
переводить отношения от 1НФ к последующим без
пропуска, от 1НФ ко 2НФ,
от 2НФ к 3НФ
и тд
8

9. ОПРЕДЕЛЕНИЕ 1НФ

Отношение находится в 1НФ,
если на пересечении
каждой строки и каждого столбца находится только одно
значение атрибута , т.е. все атрибуты простые (атомарные).
В отношении должны отсутствовать дубли строк
.1НФ – является обязательной
9

10. Понятие атомарности атрибута

Отношение в 1НФ, если атрибуты ФИО и
Дата рождения предполагается использовать
целиком
ФИО
Дата рождения
Иванов Иван Иванович
01 марта 2000 г.
Петров Петр Петрович
15 мая 2001 г.
Сидоров Сидор Сидорович 31 декабря 1999 г.
10

11. Пример Раздельное использование полей

Фамилия Имя
Отчество
Дата рождения
Год рождения
Иванов
Иван
Иванович
01 марта
2000
Петров
Петр
Петрович
15 мая
2001
Сидоров
Сидор
Сидорович 31 декабря
1999
11

12. Пример таблицы, не находящейся в 1НФ

Группа
День
№пары
Дисциплина
аудитория Тип
занятий
8826
Пн
2
3
4
5
Базы данных 12-41
Статистика 12-51
Мир инф
52-24
ресурсы
лекции
лекции
лаб раб
8827
Пн
2
3
Базы данных 12-41
Мир инф
52-24
ресурсы
лекции
лаб раб
12

13. Таблица в 1НФ

группа День
№пары Дисциплина
Аудит Тип
ория занятий
8126
Пн
2
Базы данных
12-41 лекции
8126
Пн
3
Статистика
12-51 лекции
8126
Пн
4
Мир инф.
ресурсы
52-24 лаб. раб
8127
Пн
2
Базы данных.
12-41 лекции
8127
Пн
3
Мир инф.
ресурсы
52-24 лаб. раб
13

14. Функциональные зависимости

Атрибуты могут быть первичными ключами,
потенциальными
первичными
ключами,
простыми
атрибутами.
Неключевые атрибуты отношения называются
описательными.
Функциональные зависимости описывают связь
между атрибутами отношения.
Описательные
атрибуты
должны
быть
функционально зависимы первичного ключа.
14

15. Пример № страхового полиса

Пример
Описателные атрибуты
№ страхового полиса
Ключевой
атрибут
№ ст. полиса
ФИО
Арес прописки
1111111111Иванов И.И С-Пб.,Ул Авиационная 2-3
22222222Петров П.П С-Пб., Ул Авиационная 2-3
333333333Иванов И.И С-Пб., Лиговский пр. 18-9
15

16.

Между атрибутами отношения А и В существует
функциональная зависимость, если любое значение
атрибута А однозначно определяет значение атрибута В.
А
В связь 1
1
В предыдущем примере каждому номеру страхового
полиса всегда соответствует только одна фамилия.
Значит номер страхового полиса однозначно определяет
фамилию .
Между этими атрибутами есть функциональная
зависимость.
16
номер страхового полиса
фамилия

17. Функциональные зависимости

№ сотрудника
ФИО
Должность
1111
Иванов А.А.
Начальник отдела
1112
Петров П.П.
Программист
1113
Иванов А.А.
Программист
17

18. Пример:

№ сотрудника
1
№ сотрудника
определяет
:
ФИО
1
ФИО
№ сотрудника определяет Должность
1
:
№ сотрудника
1
Должность
Ключевой атрибут должен определять все
остальные атрибуты отношения.
18

19. Проверка остальных атрибутов

ФИО Иванов А.А.
ФИО Иванов А.А.
ФИО
1: M
№ сотрудника 1111
№ сотрудника 1112
№ сотрудника
19

20. Функциональные зависимости

Атрибут А функционально определяет
атрибут В и
является детерминантом
Атрибут В функционально зависит от А
В примере № сотрудника – детерминант.
Описательные атрибуты должны быть
функционально
зависимы от ключа.
В примере ФИО, должность
функционально зависимы
от ключа.
20

21. Вывод:

Ключ – атрибут, однозначно определяющий строки
таблицы ( может быть составным).
Ключевой атрибут должен быть детерминантом
для остальных атрибутов отношения.
Несколько атрибутов – ключей в отношении:
один из них
- первичный ключ, остальные ключи
потенциальные Пример: Номер сотрудника, паспорт
21

22.

Отсутствия атрибута - ключа в отношении:
суррогатный ключ
Плохо – не избавляет от дублирования содержания строк
код
ФИО
адрес
телефон
1
Иванов А.А.
Ленсовета
111-11-11
2
Петров П.П
Б. Морская
222-22-22
3
Иванов А.А.
Ленсовета
111-11-11
22

23. ОПРЕДЕЛЕНИЕ 2НФ

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

24. ПОЛНАЯ ФУНКЦИОНАЛЬНАЯ ЗАВИСИМОСТЬ ПФЗ

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

25. Пример

Рассмотрим отношение Дипломники.
(Таб_№преп, №_зач, ФИО_преп, Должность, ФИО_студ, Тема_диплома)
(Таб_№преп, №_зач) → (ФИО_преп, Должность, ФИО_студ,
Тема_диплома)
Таб_№преп → ФИО_преп, Должность
№_зач → ФИО_студ, Тема_диплома
25

26. Вывод

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

27. ОПРЕДЕЛЕНИЕ 3НФ

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

28. ПРИМЕР

№_зач
11111
11112
11121
ФИО
Зайцев
Чигров
Рудаков
Дата_рожд группа староста
12.12.90
101
Иванов
30.01.89
101
Иванов
01.08.90
102
Петров
28

29. Транзитивная зависимость

А,В,С атрибуты отношения R
Если А В, а В
С,
то А
С транзитивно
Если №_зач
группа, а группа
староста,
то №_зач
староста транзитивно
29

30. Аномалии при транзитивной зависимости

Если
студент переходит в другую группу, то в
записи студента вместе с номером группы
изменится и староста. Атрибут староста находится
в транзитивной зависимости от атрибута № зач.
Аномалии обновления отношения проявятся, если
в группе поменяется староста. Изменения коснутся
каждой записи студентов этой группы.
30

31. Вывод:

Если в отношении есть описательные атрибуты,
определяющие другие описательные атрибуты, то
такие атрибуты надо выделить в отдельное
отношение.
Применяется декомпозиция.
Атрибуты, находящиеся в транзитивной зависимости,
удаляются из отношения в новое отношение.
31

32.

Студент= { №зач, ФИО, дата_ рожд.}
Группа={группа, староста }
32

33. ВЫВОД:

Приведение отношений ко 2НФ и 3НФ позволяет
избежать аномалий обновления данных при работе с
БД и избавиться от информационной избыточности в
отношениях.
33

34. ВЫВОД:

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

35. Вывод

Высокая нормализация может привести к созданию
большого количества таблиц и связей
Высокая нормализация усложнит работу с БД и снизит
производительность СУБД.
Обычно данные нормализуют до третьей нормальной
формы.
35

36.

2.2 Преобразование ER-диаграммы в схему
БД
1.
2.
3.
Каждая сущность преобразуется в
определенное отношение
Связь между сущностями, которая
отображалась глаголом, преобразуется в
связь между отношениями.
Связи между отношениями реализуются с
помощью ключей - первичных и внешних.
36

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

Метод ER-диаграмм
37

38. Обозначение связей в нотации Мартина

38

39. Метод ER-диаграмм

Сущности, объединяемые связью,
называются участниками.
Степень связи определяется количеством
участников связи.
Класс принадлежности сущности (КП)
39

40. Класс принадлежности

Класс принадлежности (КП) О
Для рассматриваемой пары сущностей
каждый
экземпляр
одной
сущности
обязательно
связан
с
каким-то
экземпляром другой сущности. Такое
участие сущности называется полным
(обязательным)
Класс принадлежности (КП) Н
Экземпляр одной сущности может быть
не связан ни с одним экземпляром другой
сущности.
40

41. Правила метода ER диаграмм

.Правило использует две характеристики связи
между парой сущностей:
• мощность связи
• класс принадлежности сущностей
Правила с 1-3
Правила 4-5
Правило 6
Мощность 1:1
Мощность 1:М
Мощность М:М
41

42. Правило 1 Мощность 1:1 КП: О-О

Если мощность связи между парой сущностей
1:1 и класс принадлежности обеих сущностей
обязательный (КП: О–О),
то формируется одно отношение, ключом
которого, может быть назначен первичный ключ
любой из двух сущностей. Ключ же второй
сущности будет выступать в роли
альтернативного (возможного) ключа.
Пример: сущность преподаватель –
сущность паспортные данные
42

43. 1:1 КП - О КП - О

Преподаватели
Паспортные данные
101 Иванов
доцент
1111111101.05.1988
102 Петров
профессор
222222223.06.2000
Выдан в Спб
Выдан в
Москве
43

44.

Пусть К1 – первичный ключ сущности С1, а К2 –
первичный ключ сущности С2. Тогда по правилу 1
должно быть сформировано отношение R1(К1, К2,...)
или отношение R2(К2, К1,...)
44

45. Правило 2 Мощность 1:1 КП: О-Н или Н-О

Формируются два отношения со своими
первичными ключами.
К отношению, соответствующему сущности
с КП:О добавляется как альтернативный
ключ первичный ключ сущности с КП:Н.
45

46.

Преподаватели
Дисциплины
1Иванов доцент
информатика
лекции
физика
лекции
2Петров профессор
3Сидоров аспирант
46

47. Множественность связи 1:1 КП: О – Н или Н – О

1
1
Дисциплина
Преподаватель
.
1
Ведет
Н
О
Sпреподаватель=(Код_П, ФИО, Должность)
Sдисциплина=(Код_Д, Название, Вид занятий)
Связь будет 1:М Необязательный класс сущности
становится главным
Sпреподаватель=(Код_П, ФИО, Должность, Название)
Sдисциплина=(Код_Д, Название, вид занятий, код_п)

48. Правило 3 Мощность 1:1 КП: Н-Н

Формируются три отношения.
Два из них будут соответствовать
связываемым сущностям со своими
первичными ключами, а третье – для связи,
в качестве первичного ключа которого
может выступать первичный ключ любой
сущности. Тогда первичный ключ другой
сущности будет выступать в роли
возможного (альтернативного) ключа
48

49.

Преподаватели
Дисциплины
1Иванов доцент
2Петров профессор
информатика
лекции
Физика
Техноэтика
Лекции
Практика
3Сидоров аспирант
49

50. Множественность связи 1:1 КП: Н Н

1
1
Дисциплина
Преподаватель
.
1
Ведет
Н
Н
Sпреподаватель=(Код_П, ФИО, Должность)
Sдисциплина=(Код_Д, Название, Вид занятий)
Sпреподаватель=(Код_П, ФИО, Должность, Название)
Sдисциплина=(Код_Д, Название, вид занятий,)
Sп_д=(Код_П,код_Д) или Sд_п=(Код_Д,код_П)

51.

преподаватель
Код_П
1
Дисциплина
Код_Д
М
1
М
Код_П
(FK)
Код_Д
(FK)
51

52. Правило 4 Мощность 1:М или М:1 КП:О для М-сущности

Формируются два отношения со своими
первичными ключами.
Первичный ключ главной сущности
добавляется в подчиненную сущности как
внешний ключ
52

53. ПРИМЕР

ПРЕПОДАВАТЕЛЬ М КП О
Табельный номер
ФИО
Должность
Степень
1
КАФЕДРА
№ кафедры
Название
Факультет
ФИО зав кафедры
Телефон
53

54. ПРИМЕР

ПРЕПОДАВАТЕЛЬ
Табельный номер
ФИО
Должность
Степень
№ кафедры FK
КАФЕДРА
№ кафедры
Название
Факультет
ФИО зав кафедры
Телефон
54

55. Правило 5 Мощность 1:М или М:1 КП:Н для М-сущности

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

56. ПРИМЕР

ПРЕПОДАВАТЕЛЬ КП Н М
Табельный номер
ФИО
Должность
Степень
1
КАФЕДРА
№ кафедры
Название
Факультет
ФИО зав кафедры
Телефон
Табельный номер (FK)
№ кафедры
56

57.

Пример:
В сущности
Преподаватель может быть
преподаватель, не работающий ни на одной
кафедре
Sпреподаватель=(таб#, ФИО, Должность, степень)
КП Н
Sкафедра=(№кафедры, название, факультет,
ФИО зав кафедры, Телефон)
Создается отношение связка
Sпрепод_каф=(таб#, №кафедры)
57

58. Правило 6 Мощность М : N КП любой

Всегда формируются три отношения.
Два из них будут соответствовать
связываемым сущностям со своими
первичными ключами,
а третье отношение – связка с составным
первичным ключом
58

59. СОЗДАНИЕ ОБЪЕКТА СВЯЗКИ ( ПРОДАННЫЙ ТОВАР)

покупатель
№ покупателя 1
фио
адрес
продукт
код продукта
наименование
поставщик
проданный товар
М
М
№ покупателя
(FK)
код продукта (FK)
1
М
59

60. ПРИМЕР: Избыточность данных

60

61.

Сотрудники отдела
• №_сотрудника
• ФИО
• Должность
• Оклад
• №_отдела
• Корпус
• Телефон отдела
61

62. .

№
ФИО
сотр
Должность
Оклад
Ко Теле
р. фон
40000
№_
отдел
а
5
21
Иванов
менеджер
2
111-11-12
37
Петров
инженер
40000
3
1
111-11-11
14
Сидоров
70000
3
1
111-11-11
Миронов
программи
ст
менеджер
09
30000
7
3
111-11-13
05
Федоров
зав . отд
150000 3
1
111-11-11
41
Иванов
секретарь
20000
2
111-11-12
5
62

63. ИЗБЫТОЧНОСТЬ ДАННЫХ И АНОМАЛИЯ ОБНОВЛЕНИЯ

SСотрудники отделов = (№_сотр,ФИО.
должность, Оклад,№_отд. корпус, телефон)
Таблица содержит избыточные данные:
Все поля, связанные с № отдела повторяются корпус, телефон
Если в отношениях содержатся избыточные
данные, то возникает проблемы с модификацией,
вставкой и удалением информации из отношения.
63

64. Вставка

При добавлении новой информации - в данном
примере сотрудники существующих отделов нужно
точно повторить информацию, связанную с №
отдела (корпус, телефон).
В противном случае данные будут противоречивы,
(т е один отдел может находится в разных
корпусах или иметь другой телефон)
СУБД не сможет контролировать эти ошибки.
64

65. Удаление

при удалении записи, содержащей сведения
о сотруднике (Миронове удалятся сведения
и о 7 отделе, который больше не фигурирует
ни в одной записи, и при появлении нового
сотрудника 7 отдела их придется искать в
бумажных документах)
65

66. Обновление

изменение номера телефона 3 отдела
придется производить в 3 записях,
если будет пропущена хотя бы одна запись
с таким же номером отдела, то данные будут
противоречивы
66

67. Вывод

Таблица 1 содержит повторяющиеся
избыточные данные, приводящие к
аномалии обновления.
Для решения этой проблемы применяется
декомпозиция таблицы, те она делится на
две таблицы.
Возможно деление исходной таблицы и на
большее число
67

68. Таблица 2 Сотрудники

№
сотр
21
ФИО
должность
Оклад
Иванов
менеджер
200
37
Петров
инженер
100
14
Сидоров
программист
1000
09
Степанов
менеджер
400
05
Федоров
зав . отд
1500
41
Иванов
секретарь
200
68

69. Таблица 3 Отделы

№_отд
корпус
телефон
3
1
111-11-11
5
2
111-11-12
7
3
111-11-13
69

70. Процесс декомпозиции имеет 2 свойства

1.
2.
Соединение без потерь.
Восстановление исходного отношения
соединением отношений после
декомпозиции
Сохранение зависимостей, которые
позволят сохранять ограничения,
наложенные на исходное отношение
70

71. 3.Физическое проектирование

Выбор СУБД.
При выборе оцениваются характеристики
СУБД:
средства поддержки целостности БД
языковые средства и их поддержка
трудоемкость разработки прикладных
программ
стоимость эксплуатации системы.
Реализация проекта. Создание системного
каталога БД, заполнение данными,
импортирование данных.
Для выбранной СУБД, разрабатывается схема
БД.
71

72. Цикл проектирования БД

Описание
предметной
области
Инфологическое
проектирование
Инфологическая
модель
Логическ
ое
проекти
рование
БД
Физическое
проектирование
Логическая модель
72