Восходящее проектирование БД (лекция 6)

1.

Федеральное государственное бюджетное
образовательное учреждение
высшего образования
«Оренбургский государственный университет»
Кафедра прикладной информатики в экономике и
управлении
БАЗЫ ДАННЫХ
1

2. Лекция 6

Тема : Восходящее
проектирование БД
2 часа
Разработчик Панова Н.Ф.
2

3. План лекции

1.
2.
3.
4.
5.
Проектирование БД
Восходящее проектирование БД
Функциональные зависимости между
атрибутами отношения
Нормальные формы
Пример нормализации схемы отношения
3

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

Процесс проектирования БД представляет
собой
процесс
переходов
от
неформального
словесного
описания
информационной структуры предметной
области к формализованному описанию
объектов предметной области в терминах
некоторой модели. Конечной целью
проектирования
является
построение
конкретной БД.
4

5. Подходы к проектированию БД

Существует
2
основных
подхода
к
проектированию БД:
Восходящий;
Нисходящий.
Восходящий подход начинается с разработки
моделей низкого уровня – внутренней или
даталогической;
Нисходящий – с разработки высокоуровневых
моделей.
5

6. Восходящее проектирование реляционных БД. Нормализация отношений

Восходящее проектирование реляционных БД
начинается с уровня атрибутов.
Характерные для предметной области атрибуты
группируются в несколько отношений.
Затем
схемы отношений нормализуются в
соответствии с правилами нормализации.
6

7. Восходящее проектирование реляционных БД. Нормализация отношений

Обычно различают следующие проблемы:
избыточность данных;
аномалии обновления;
аномалии удаления;
аномалии ввода.
Избыточность данных характеризуется наличием в
кортежах отношений повторяющейся информации.
Многократное дублирование данных приводит к
неоправданному увеличению занимаемого объема
внешней памяти
7

8. Восходящее проектирование реляционных БД. Нормализация отношений

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

9. Восходящее проектирование реляционных БД. Нормализация отношений

Аномалии удаления могут возникать при удалении
записей
из
ненормализованных
таблиц
и
характеризуются вероятностью удаления не всех
дублированных кортежей.
Аномалии ввода возникают при добавлении в
таблицу новых записей, обычно в поля с
ограничениями NOT NULL (не пустые). Кода в
отношение на данный момент времени невозможно
ввести однозначную информацию.
9

10. Пример ненормализованного отношения

Поставка (поставщик, товар, количество товара,
дата поставки, адрес поставщика)
Очевидно,
что
один
поставщик
поставляет
несколько товаров и наоборот – один товар
поставляется несколькими поставщиками. Поэтому
адрес поставщика дублируется.
Кроме того, невозможно добавить поставщика,
если он не поставляет товар. А при удалении
кортежа можно потерять данные о поставщике,
если он поставляет только этот товар и наоборот,
потерять данные о товаре.
10

11. Функциональные зависимости между атрибутами

Эти
аномалии
обусловлены
некоторыми
нежелательными функциональными зависимостями
между атрибутами.
11

12. Функциональные зависимости между атрибутами

Функциональной зависимостью атрибута В
отношения R от атрибута А , обозначаемой A —> B,
называется
такая
связь
между
атрибутами
отношения, когда в предметной области в каждый
момент времени каждому значению атрибута
А соответствует только одно определенное
значение атрибута В. Однако, для заданного
значения атрибута В может существовать несколько
различных значений атрибута А.
12

13. Пример

Рассмотрим схему отношения
Студент(код,
фамилия,
имя, отчество,
дата
рождения)
Существуют Функциональные зависимости:
Код->фамилия
Код->имя
Код->отчество
Код->дата рождения в то же время обратных
зависимостей не существует, т.к. одному значению
фамилии
могут
соответствовать
несколько
значений атрибута код, т.к. фамилии повторяются.
13

14. Полные и частичные функциональные зависимости между атрибутами

Частичная функциональная зависимость – это
зависимость не ключевого атрибута от части
составного первичного ключа.
Полная функциональная зависимость – это
зависимость не ключевого атрибута от всего
составного первичного ключа.
Если в схеме отношения первичный ключ не
составной, то можно сказать, что в этой схеме
отношения отсутствуют частичные функциональные
зависимости.
14

15. Пример

Билет(номер поезда, дата, вагон, место,
тип вагона, ФИО пассажира, время
отправления)
ПК: номер поезда+дата+вагон+место
ПК->ФИО пассижира
Номер поезда->время отправления
Номер поезда+вагон->тип вагона
15

16. Транзитивные функциональные зависимости атрибутами

Атрибут В отношения R транзитивно зависит от
атрибута
А,
если
существует
атрибут С и выполняется условие существования
следующих функциональных зависимостей:
А —> С; С —> В,
при условии, что атрибут А функционально не
зависит ни от атрибута В, ни от атрибута С.
16

17. Пример

Автомобиль(номер,
марка,
владельца, адрес владельца)
Существуют зависимости:
Номер->ФИО ФИО->адрес
Значит,
имеется
функциональная зависимость
Номер->адрес владельца
цвет,
ФИО
транзитивная
17

18. Нормальные формы

Теория нормализации основана на концепции
нормальных форм. Каждой нормальной форме
соответствует набор ограничений. Отношение
находится в нормальной форме, если оно
удовлетворяет
свойственному
данной
форме
набору ограничений.
В теории реляционных БД обычно выделяется
следующая последовательность нормальных форм:
18

19. Нормальные формы

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

20. Свойства нормальных форм

каждая следующая нормальная форма, в некотором
смысле, улучшает свойства предыдущей;
при переходе к следующей нормальной форме
свойства
предыдущих
нормальных
форм
сохраняются.
20

21. 1НФ. Первая нормальная форма

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

22. 2НФ. Вторая нормальная форма

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

23. Приведение отношения к 2НФ

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

24. 3НФ. Третья нормальная форма

Схема отношения находится в 3НФ, если она
находится в 2НФ и в ней отсутствуют транзитивные
зависимости между не ключевыми атрибутами и
первичным ключом.
Для приведения схемы отношения к 3НФ:
Выполняем проекцию исходного отношения путем
вычеркивания атрибута В;
Выполняем проекцию исходного отношения на
атрибуты В и С.
24

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

Отношение находится в НФБК, если оно находится
в 3НФ и каждый детерминант отношения является
потенциальным ключом отношения.
Детерминант
–
это
атрибут
(совокупность
атрибутов),
находящийся
в
левой
части
функциональной зависимости. В одном отношении
может быть несколько детерминантов.
25

26. Нормальные формы

В большинстве случаев достижение третьей
нормальной формы, или формы Бойса-Кодда
считается достаточным для реальных проектов БД.
Четвертая
и
пятая
считаются
нормальными
формами высших порядков
26

27. Накладная № 234 Дата Покупатель Адрес 10.01.19 ООО «Гера» г. Орск, ул. Гая, 2 Отпущен товар Количество Ед. изм. Цена ед. изм

Пример
27

28. По накладной можно сформировать следующее отношение, удовлетворяющее 1НФ : Отпуск товара (номер накладной, дата, покупатель,

Пример
28

29.

Пример
Но
дата
мер
По
Го
купат род
ель
адрес товар
Ко
ли
чест
во
Ед.
изм
Це
на
Общ
стоим
234
10.01.19
Орск
ООО
«Гера»
Ул.
Гая,2
тушен
ка
10
банка 25
2500
234
10.01.19
Орск
ООО
«Гера»
Ул.
Гая,2
сахар
500
кг
10
5000
234
10.01.19
Орск
ООО
«Гера»
Ул.
Гая,2
макар
оны
300
кг
10
3000
29

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

Пример
30

31. Рассмотрим функциональные зависимости атрибутов от первичного ключа, при этом проще начинать рассмотрение с частей первичного

Пример
31

32. Рассмотрим функциональные зависимости атрибутов от первичного ключа в целом: Номер накладной + Товар → Количество Номер

Пример
32

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

Пример
33

34. Выполняем проекции на части первичного ключа: Накладная (номер накладной, дата, покупатель, город, адрес) Товар (товар,

Пример
34

35. В отношении Накладная выявляются зависимости: Номер накладной → Покупатель Покупатель → Город Покупатель → Адрес Следовательно,

Пример
35

36. Вычеркиваем из отношения Накладная товара атрибуты Город, Адрес, получаем отношение: Накладная (номер накладной, дата,

Пример
36

37. Схемы отношений в 3НФ

НАКЛАДНАЯ
Номер
накладной
Дата
Покупатель
ОТПУСК
ТОВАРОВ
Номер
накладной
Товар
Количество
Общая
стоимость
ТОВАР
Товар
Ед. изм.
Цена ед.изм
ПОКУПАТЕЛЬ
Покупатель
Город
Адрес
37

38. Рассмотрим следующую схему отношения: Разговор(Ф.И.О., адрес,номер телефона, дата, время разговора, город, длительность

НФБК
38

39. Имеют место следующие зависимости неключевых атрибутов от ключевых: Номер телефона+дата+время –-> длительность, город;

НФБК
39

40. Кроме перечисленных выше зависимостей, существуют еще две: Ф.И.О.+адрес--->номер телефона; номер телефона ---> Ф.И.О.+адрес ;

НФБК
40

41. Для приведения данного отношения к БКНФ его следует разделить на два отношения следующим образом: Абонент (Ф.И.О, адрес, номер

НФБК
41

42.

Вопросы
1.
2.
3.
4.
5.
6.
Какие подходы к проектированию БД
существуют?
Какие функциональные зависимости между
атрибутами Вы знаете?
Если первичный ключ отношения простой, в
какой нормальной форме находится
отношение?
На практике требования какой нормальной
формы считаются достаточными?
Как привести схему отношения к 2НФ?
В процессе нормализации число отношений
увеличивается или уменьшается?
42

43. Практика. Приведение схем отношений к 3НФ

Рецепт(наименование блюда, наименование
ингредиента, кухня, калорийность блюда,
стоимость блюда, количество ингредиента)
блюдо
Ингреди
ент
кухня
калорийн
ость
стоимость
Кол-во
ингред
каша
молоко
русская
150
90
100
каша
пшено
русская
150
90
150
омлет
молоко
французская 180
120
100
омлет
яйцо
французская 180
120
5
43

44.

ПК: наименование блюда+ингредиент
Наименование блюда->кухня
Наименование блюда->калорийность
Наименование блюда->стоимость
Отношение не находится в 2НФ
Приводим к 2НФ
Выявим полные ФЗ не ключевых атрибутов
от составного ПК
наименование блюда+ингредиент>количество ингредиента
44

45.

Выполняем проекцию на составной ПК и
зависящие от него атрибуты
Рецепт(наименование блюда, наименование
ингредиента, количество)
Выполняем проекцию на части ключа и
атрибуты, зависящие от этих частей
Блюдо(наименование, кухня, калорийность,
стоимость) содержит 2 кортежа
Находятся ли данные отношения в 3НФ?
В Рецепт только 1 не ключевой атрибут, нет
транзитивных ФЗ, находится в 3НФ
45

46.

Блюдо находится в 3НФ, т.к. все не
ключевые атрибуты находятся в ФЗ от
простого ПК
46