Проектирование БД. Нормальные формы В теории реляционных баз данных обычно выделяется следующая последовательность нормальных форм: первая нормальная. - презентация

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

2 Нормальные формы В теории реляционных баз данных обычно выделяется следующая последовательность нормальных форм: первая нормальная форма (1NF); вторая нормальная форма (2NF); третья нормальная форма (3NF); нормальная форма Бойса-Кодда (BCNF); четвертая нормальная форма (4NF); пятая нормальная форма, или нормальная форма проекции- соединения (5NF или PJ/NF). Основные свойства нормальных форм: каждая следующая нормальная форма в некотором смысле лучше предыдущей; при переходе к следующей нормальной форме свойства предыдущих нормальных свойств сохраняются.

3 Определения Определение 1. Функциональная зависимость В отношении R атрибут Y функционально зависит от атрибута X (X и Y могут быть составными) в том и только в том случае, если каждому значению X соответствует в точности одно значение Y: R.X (r) R.Y. Определение 2. Полная функциональная зависимость Функциональная зависимость R.X (r) R.Y называется полной, если атрибут Y не зависит функционально от любого точного подмножества X. Определение 3. Транзитивная функциональная зависимость Функциональная зависимость R.X -> R.Y называется транзитивной, если существует такой атрибут Z, что имеются функциональные зависимости R.X -> R.Z и R.Z -> R.Y и отсутствует функциональная зависимость R.Z --> R.X. (При отсутствии последнего требования мы имели бы "неинтересные" транзитивные зависимости в любом отношении, обладающем несколькими ключами.) Определение 4. Неключевой атрибут Неключевым атрибутом называется любой атрибут отношения, не входящий в состав первичного ключа (в частности, первичного). Определение 5. Взаимно независимые атрибуты Два или более атрибута взаимно независимы, если ни один из этих атрибутов не является функционально зависимым от других.

4 Первая нормальная форма (1NF) Переменная отношения находится в первой нормальной форме тогда и только тогда, когда в любом допустимом значении отношения каждый его кортеж содержит только одно значение для каждого из атрибутов. Вторая нормальная форма СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ (СОТР_НОМЕР, СОТР_ЗАРП, ОТД_НОМЕР, ПРО_НОМЕР, СОТР_ЗАДАН) Первичный ключ: СОТР_НОМЕР, ПРО_НОМЕР Функциональные зависимости: СОТР_НОМЕР -> СОТР_ЗАРП СОТР_НОМЕР -> ОТД_НОМЕР ОТД_НОМЕР -> СОТР_ЗАРП СОТР_НОМЕР, ПРО_НОМЕР -> СОТР_ЗАДАН СОТРУДНИКИ-ОТДЕЛЫ (СОТР_НОМЕР, СОТР_ЗАРП, ОТД_НОМЕР) Первичный ключ: СОТР_НОМЕР Функциональные зависимости: СОТР_НОМЕР -> СОТР_ЗАРП СОТР_НОМЕР -> ОТД_НОМЕР ОТД_НОМЕР -> СОТР_ЗАРП СОТРУДНИКИ-ПРОЕКТЫ (СОТР_НОМЕР, ПРО_НОМЕР, СОТР_ЗАДАН) Первичный ключ: СОТР_НОМЕР, ПРО_НОМЕР Функциональные зависимости: СОТР_НОМЕР, ПРО_НОМЕР -> CОТР_ЗАДАН Отношение R находится во второй нормальной форме (2NF) в том и только в том случае, когда оно находится в 1NF, и каждый неключевой атрибут полностью зависит от каждого ключа R.

5 Третья нормальная форма СОТРУДНИКИ-ОТДЕЛЫ (СОТР_НОМЕР, СОТР_ЗАРП, ОТД_НОМЕР) Первичный ключ: СОТР_НОМЕР Функциональные зависимости: СОТР_НОМЕР -> СОТР_ЗАРП СОТР_НОМЕР -> ОТД_НОМЕР ОТД_НОМЕР -> СОТР_ЗАРП СОТРУДНИКИ-ПРОЕКТЫ (СОТР_НОМЕР, ПРО_НОМЕР, СОТР_ЗАДАН) Первичный ключ: СОТР_НОМЕР, ПРО_НОМЕР Функциональные зависимости: СОТР_НОМЕР, ПРО_НОМЕР -> CОТР_ЗАДАН СОТРУДНИКИ и ОТДЕЛЫ: СОТРУДНИКИ (СОТР_НОМЕР, ОТД_НОМЕР) Первичный ключ: СОТР_НОМЕР Функциональные зависимости: СОТР_НОМЕР -> ОТД_НОМЕР ОТДЕЛЫ (ОТД_НОМЕР, СОТР_ЗАРП) Первичный ключ: ОТД_НОМЕР Функциональные зависимости: ОТД_НОМЕР -> СОТР_ЗАРП Отношение R находится в третьей нормальной форме (3NF) в том и только в том случае, если находится в 1NF, и каждый неключевой атрибут не является транзитивно зависимым от какого-либо ключа R.

6 Нормальная форма Бойса-Кодда Отношение R находится в нормальной форме Бойса-Кодда (BCNF) в том и только в том случае, если каждый детерминант является возможным ключом. Детерминант - любой атрибут, от которого полностью функционально зависит некоторый другой атрибут. Четвертая нормальная форма ПРОЕКТЫ (ПРО_НОМЕР,ПРО_СОТР, ПРО_ЗАДАН) В отношении R (A, B, C) существует многозначная зависимость R.A -> -> R.B в том и только в том случае, если множество значений B, соответствующее паре значений A и C, зависит только от A и не зависит от С. В отношении ПРОЕКТЫ существуют следующие две многозначные зависимости: ПРО_НОМЕР -> -> ПРО_СОТР ПРО_НОМЕР -> -> ПРО_ЗАДАН Отношение R находится в четвертой нормальной форме (4NF) в том и только в том случае, если в случае существования многозначной зависимости A -> -> B все остальные атрибуты R функционально зависят от A. ПРОЕКТЫ-СОТРУДНИКИ и ПРОЕКТЫ-ЗАДАНИЯ: ПРОЕКТЫ-СОТРУДНИКИ (ПРО_НОМЕР, ПРО_СОТР) ПРОЕКТЫ-ЗАДАНИЯ (ПРО_НОМЕР, ПРО_ЗАДАН)

7 Пятая нормальная форма СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ (СОТР_НОМЕР, ОТД_НОМЕР, ПРО_НОМЕР) Зависимость соединения. Отношение R (X, Y,..., Z) удовлетворяет зависимости соединения * (X, Y,..., Z) в том и только в том случае, когда R восстанавливается без потерь путем соединения своих проекций на X, Y,..., Z. Отношение R находится в пятой нормальной форме (нормальной форме проекции- соединения - PJ/NF) в том и только в том случае, когда любая зависимость соединения в R следует из существования некоторого возможного ключа в R. СОТРУДНИКИ-ОТДЕЛЫ (СОТР_НОМЕР, ОТД_НОМЕР) СОТРУДНИКИ-ПРОЕКТЫ (СОТР_НОМЕР, ПРО_НОМЕР) ОТДЕЛЫ-ПРОЕКТЫ (ОТД_НОМЕР, ПРО_НОМЕР)

8 ЛОГИЧЕСКОЕ И ФИЗИЧЕСКОЕ ПРОЕКТИРОВАНИЕ БД

9

10 Этапы проектирования БД создание представления (схемы, модели) БД, включающего определение важнейших сущностей (таблиц) и связей между ними, но не зависящего от модели БД и физической реализации. Этап 1-й. Концептуальное проектирование развитие концептуального представления БД с учетом принимаемой модели (иерархической, сетевой, реляционной и т.д.). Этап 2-й. Логическое проектирование развитие логической модели БД с учетом выбранной целевой СУБД Этап 3-й. Физическое проектирование

11 Логическое проектирование 1. Удаление и проверка элементов, не отвечающих принятой модели данных Удаление связей N:M. Если в концептуальной модели присутствуют связи N:M, то их следует устранить путем определения промежуточной сущности. Связь N:M заменяется двумя связями типа 1:M, устанавливаемыми со вновь созданной сущностью

12 1.2 Удаление многозначных атрибутов (атрибутов имеющих несколько значений). Многозначность устраняется путем введения новой сущности и связи 1:N 1.3 Удаление избыточных связей. Связь является избыточной, если одна и та же информация может быть получена не только через нее, но и с помощью другой связи Для обеспечения ссылочной целостности в дочерней таблице создается внешний ключ. Во внешний ключ входят поля связи дочерней таблицы. Для связей типа "один-ко-многим" внешний ключ по составу полей должен совпадать с первичным ключом родительской таблицы.

13 Пример. Необходимо разработать БД для предприятия, которое занимается производством мебели. БД должна храниться информация о мебели, материалах и поставщиках материалов. Выделим основные сущности предметной области: Справочник материалов Справочник поставщиков Справочник выпускаемой мебели Разработаем концептуальную модель БД в виде диаграммы Чена

14