1 ГОУ ВПО Уральский государственный технический университет – УПИ

2 Кафедра «Автоматика и управление в технических системах» направление – Автоматизация и управление специальность – Управление и информатика в технических системах ИНФОРМАЦИОННОЕ ОБЕСПЕЧЕНИЕ СИСТЕМ УПРАВЛЕНИЯ Лекция ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД 5.3. Нормализация отношений Первая нормальная форма (1НФ) Вторая нормальная форма (2НФ) Третья нормальная форма (3НФ) Нормальная форма Бойса – Кодда (НФБК) Преподаватели: Чесноков Юрий Николаевич, доц., к.т.н., Дружинина Надежда Геннадьевна, доц.

3 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Цель изучения материала: научиться проектировать реляционную модель данных с помощью нормализации отношений; научиться проектировать первую нормальную форму (1НФ) БД; научиться проектировать вторую нормальную форму (2НФ) БД; научиться проектировать третью нормальную форму (3НФ) БД; научиться проектировать нормальную форму Бойса – Кодда (НФБК). Компетенций, формирующиеся в процессе знакомства с материалом: способность применять современные информационные компьютерные технологии: самостоятельно работать с СУБД (ИК-1); готовность работать с информацией из различных источников (ИК- 4); способность к приобретению новых знаний, используя современные информационные технологии (СЛК-4); способность разрабатывать информационное обеспечение систем с использованием стандартных СУБД (ПКД-5); готовность использовать современные инструментальные средства и технологии проектирования программных средств (ПТД-2); способность составлять техническую документацию на разработку программного обеспечения (ПТД-4).

4 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Содержание лекции ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД 5.3. Нормализация отношений Первая нормальная форма (1НФ) Вторая нормальная форма (2НФ) Третья нормальная форма (3НФ) Нормальная форма Бойса – Кодда (НФБК)

5 Нормализация отношений – это приведение отношений к нормальным формам. Нормализация выполняется декомпозицией их схем. Декомпозиция схемы отношения R = {A 1, A 2, …, A k } – это замена ее совокупностью схем ρ = {R 1, R 2, …, R k } подмножеств таких, что R 1 R 2 … R k = R = {A 1, A 2, …, A k }. Схемы R i могут пересекаться. Декомпозиция должна обладать свойством соединения без потерь (когда естественное соединение восстанавливает первоначальное отношение) и свойством сохранения зависимостей. 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД 5.3. Нормализация отношений

6 Нормализация отношений определяется требованиями рациональной группировки атрибутов по различным отношениям. Известны шесть названий нормальных форм отношений: Первая нормальная форма (1НФ); Вторая нормальная форма (2НФ); Третья нормальная форма (3НФ); Нормальная форма Бойса – Кодда (НФБК); Четвертая нормальная форма (4НФ); Пятая нормальная форма (5НФ). 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД 5.3. Нормализация отношений

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

8 На рис. 5.6 приведен пример схемы отношения, которое не находится в 1НФ. ФИОАдрес Сидоров Иван ПетровичМосква, Б-Черем., д1, кв2 Рис Пример схемы отношения, не отвечающего требованиям 1НФ

9 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Первая нормальная форма (1НФ) На рис. 5.7 приведен пример этого же отношения в 1НФ. Стоит заметить, что в ряде случаев нецелесообразно разбивать, например, поле «Адрес» на отдельные поля, если при эксплуатации БД запросы к элементам адреса будут исключены. ФамилияИмяОтчествоГородУлицаДом Квартира СидоровИванПетровичМоскваБ-Черем.12 Рис Пример схемы отношения, в котором соблюдены требования 1НФ

10 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Первая нормальная форма (1НФ) Повторяющаяся группа – это атрибут, имеющий несколько значений в каждой строке [12], например имена детей (рис. 5.8). Рис Отношение с повторяющимися группами Таб. ФамилияИмяИмя ребенка 1001ПетровИванМаша, Петя 1002СидоровПетрМаша 1003ИвановНиколайСаша, Лена, Катя, Юра

11 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Первая нормальная форма (1НФ) В представлении отношений в виде таблиц можно дать такое определение: повторяющаяся группа – это столбцы, которые повторяются в пределах одной и той же строки для хранения нескольких значений одного и того же атрибута [13]. Для устранения повторяющихся групп при приведении отношения к 1НФ можно ввести дополнительные столбцы (рис. 5.9).

12 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Первая нормальная форма (1НФ) Рис Отношение с дополнительными столбцами Таб. Фамилия ИмяИмя ребенка 1 Имя ребенка 2 Имя ребенка Петров ИванМашаПетяNULL 1002 Сидоров ПетрМашаNULL 1003 ИвановНиколай СашаЛенаКатя 1003 ИвановНиколай ЮраNULL

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

14 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Первая нормальная форма (1НФ) Рис Декомпозиция отношения с повторяющимися группами на два отношения Таб. Фамили я ИмяТаб. Имя ребенка 1001ПетровИван1001Маша 1002СидоровПетр1002Петя 1003ИвановНиколай1002Маша 1003Саша 1003Лена 1003Катя 1003Юра

15 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Вторая нормальная форма (2НФ) Пусть Х – ключ отношения R, Y Х, А – непервичный атрибут отношения R, т.е. атрибут, который не является ключевым. Частичная зависимость (неполная ФЗ) имеет место в R, если Х А и Y A. Если нет Y A, то А функционально полно зависит от Х в R.

16 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Вторая нормальная форма (2НФ) Отношение R находится во второй нормальной форме (2НФ), если R находится в 1НФ и каждый его непервичный атрибут функционально полно зависит от первичного ключа (т.е. не содержит неполных функциональных зависимостей непервичных атрибутов от атрибутов первичного ключа).

17 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Вторая нормальная форма (2НФ) Отношение, схема которого дана на рис. 5.11, не находится в 2НФ. Ключ: («Поставщик», «Поставка»). Атрибут «Адрес поставщика» функционально зависит от «Поставщик» – части ключа. Рис Пример схемы отношения, не отвечающего требованиям 2НФ ПоставщикПоставкаКоличествоАдрес поставщика

18 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Вторая нормальная форма (2НФ) Отношение, показанное на рис. 5.11, необходимо представить двумя отношениями, схемы которых представлены на рис ПоставщикПоставкаКоличество Рис Результат декомпозиции отношения (см. рис. 5.11) на два отношения в 2НФ ПоставщикАдрес поставщика

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

20 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Третья нормальная форма (3НФ) Транзитивная зависимость присутствует в отношении только тогда, когда детерминант, не являющийся первичным ключом, не подходит на роль ключа отношения. Отношение с транзитивной зависимостью следует разбить на два отношения, в каждом из которых в качестве первичного ключа будет выступать один из детерминантов транзитивной зависимости.

21 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Третья нормальная форма (3НФ) Декомпозиция (проецирование) отношения «Хранение» на множества атрибутов («Фирма», «Склад») и («Склад», «Объем склада») устраняет транзитивные зависимости (рис. 5.15). ФирмаСклад Рис Результат декомпозиции отношения «Хранение» на два отношения в 3НФ СкладОбъем склада

22 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Нормальная форма Бойса – Кодда (НФБК) Отношения в 3НФ имеют аномалии (трудности) включения, обновления, удаления в следующих случаях: отношение имеет два или более потенциальных ключа; эти потенциальные ключи являются составными; два или более потенциальных ключа перекрываются.

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

24 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Нормальная форма Бойса – Кодда (НФБК) Детерминант – это атрибут (или их комбинация), от которого какой-либо другой атрибут зависит функционально полно (не зависит от подмножества атрибутов детерминанта). Левые части полных ФЗ являются детерминантами. Из этого определения следует, что 2НФ и 3НФ не соответствуют НФБК, так как в этих формах имеются детерминанты, которые не могут быть ключами.

25 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Нормальная форма Бойса – Кодда (НФБК) В 2НФ детерминантом помимо полного ключа является часть ключа, от которой функционально зависит непервичный атрибут. Очевидно, что эта часть ключа не может быть ключом всего отношения. В 3НФ детерминантом является непервичный атрибут, от которого функционально зависит другой непервичный атрибут (члены транзитивной зависимости). Безусловно непервичный атрибут не может быть ключом.

26 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Нормальная форма Бойса – Кодда (НФБК) Более строгое определение НФБК формулируется так: отношение находится в НФБК тогда и только тогда, когда каждая его нетривиальная и полная ФЗ имеет в качестве детерминанта некоторый потенциальный ключ [3]. Определение НФБК концептуально проще, чем определение 3НФ, так как в нем нет явных ссылок на 1НФ и 2НФ, а также не используется концепция транзитивной зависимости [3].

27 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Нормальная форма Бойса – Кодда (НФБК) Эквивалентное определение НФБК [8] имеет такую формулировку: отношение находится в НФБК, если оно находится в 3НФ и если в нем отсутствуют зависимости первичных атрибутов от непервичных. В [9] дано еще такое определение: отношение находится в НФБК, если для всех его нетривиальных ФЗ (X Y) X представляет собой суперключ всей системы.

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

29 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Нормальная форма Бойса – Кодда (НФБК) Рассмотрим примеры схем отношений, которые не отвечают определениям НФБК. В отношении, схема которого показана на рис [8], имеются следующие ФЗ: («Детали», «Проект») «Поставщик» – по определению ключа; «Поставщик» «Проект» – зависимость первичного атрибута от непервичного. ПроектДеталиПроектПоставщик Рис Схема отношения «Проект», не отвечающая условиям НФБК

30 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Нормальная форма Бойса – Кодда (НФБК) Отношение отражает использование в проектах деталей, поставляемых поставщиками. Каждый поставщик обслуживает только один проект. Это отношение в 3НФ, так как отсутствуют неполные ФЗ и транзитивные зависимости непервичных атрибутов от ключей.

31 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Нормальная форма Бойса – Кодда (НФБК) Но в этом отношении есть аномалии: включения (факт поставки поставщиком деталей для проекта нельзя занести в БД, пока в проекте не начнут использоваться эти детали); удаления (если последний из типов деталей, поставляемых поставщиком, для проекта использован, данные о поставщике будут удалены из БД); обновления (при изменении поставщика некоторого типа деталей для проекта необходим просмотр отношений для изменения всех кортежей, содержащих эти детали).

32 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Нормальная форма Бойса – Кодда (НФБК) Декомпозиция отношения «Проект» позволяет устранить перечисленные аномалии (рис. 5.17). ДеталиПроект Рис Декомпозиция отношения «Проект» на два отношения в НФБК ПоставщикПроект

33 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Нормальная форма Бойса – Кодда (НФБК) На рис приведен пример схемы ФЗ отношения [3], которое не находится в НФБК. В нем имеются следующие ФЗ: («Студент», «Предмет») «Преподаватель», так как каждый студент изучает определенный предмет только у одного преподавателя; «Преподаватель» «Предмет», так как один преподаватель ведет один предмет, но один предмет могут вести разные преподаватели.

34 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Нормальная форма Бойса – Кодда (НФБК) Рис Схема ФЗ отношения, которое не находится в НФБК Студент Предмет Преподаватель

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

36 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Нормальная форма Бойса – Кодда (НФБК) По причине несовпадения потенциальных ключей и детерминантов отношение не будет в НФБК, хотя находится в 3НФ. В этом отношении есть функциональная зависимость части ключа от неключевого атрибута. Для приведения к НФБК отношение следует разбить на два отношения со схемами: («Студент», «Предмет»), («Преподаватель», «Предмет»).

37 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Выводы и заключение по лекции: научились проектировать реляционную модель данных с помощью нормализации отношений; научились проектировать первую нормальную форму (1НФ) БД; научились проектировать вторую нормальную форму (2НФ) БД; научились проектировать третью нормальную форму (3НФ) БД; научились проектировать нормальную форму Бойса – Кодда (НФБК).

38 5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННЫХ БД Перечень источников: Четвериков В.Н. Базы и банки данных/ В.Н. Четвериков, Г.И. Ревунков, Э. Н. Самохвалов; под ред. В.Н. Четверикова. М.: Высшая школа, с. Дейт К. Дж. Руководство по реляционной СУБД DB2/ К. Дж. Дейт. М.: Финансы и статистика, с. Дейт К. Дж. Введение в системы баз данных/ К. Дж. Дейт. М.: Издательский дом «Вильямс», 2001, 1072 с. Дмитриев В.И.Прикладная теория информации/В.И. Дмитриев. М.:Высшая школа, с. Гайдамакин Н.А. Автоматизированные информационные системы, базы и банки данных/ Н.А. Гайдамакин. М.: Гелиос АРВ, С. Карпова Т.С. Базы данных: модели, разработка, реализация/ Т.С. Карпова. СПб.: Питер, с. Мамаев Е.В. MS SQL Server 7.0. Проектирование и реализация баз данных/ Е.В. Мамаев. СПб.: БХВ-Санкт-Петербург, с. Озкарахан Э. Машины баз данных и управление базами данных/ Э. Озкарахан. М.: Мир, с. Селко Джо. SQL для профессионалов. Программирование/ Джо Селко. М.:«Лори», с. Системы управления базами данных и знаний/ А.Н. Наумов [и др.]; под общ. ред. А.Н. Наумова. М.: Финансы и статистика, с. Теория автоматического управления/ С.Е. Душин [и др.]; под общ. ред. Б. Б. Яковлева. М.: Высшая школа, с. Харрингтон Дж. Л. Проектирование реляционных баз данных. Просто и доступно/ Дж. Л. Харрингтон. М.: «Лори», с. Хендерсен К. Delphi 3 и системы клиент/сервер: руководство разработчика/ К. Хендерсен. Киев: Диалектика, с.