Проектирование баз данных методом нормализации
Проектирование базы данных Предметная область - это часть реального мира, данные о которой мы хотим отразить в базе данных. Например, в качестве предметной области можно выбрать бухгалтерию какого-либо предприятия, отдел кадров, банк, магазин и т. д. Предметная область бесконечна и содержит как существенно важные понятия и данные, так и малозначащие или вообще не значащие данные. Так, если в качестве предметной области выбрать учет товаров на складе, то понятия "накладная" и "счет-фактура" являются существенно важными понятиями, а то, что сотрудница, принимающая накладные, имеет двоих детей - это для учета товаров неважно. Однако, с точки зрения отдела кадров данные о наличии детей являются существенно важными. Таким образом, важность данных зависит от выбора предметной области. Для одной и той же предметной области реляционные отношения можно спроектировать множеством различных способов: спроектировать несколько отношений с большим количеством атрибутов, или наоборот, разнести все атрибуты по большому числу мелких отношений.
Критерии качества базы данных 1. Адекватность базы данных предметной области : ь состояние базы данных в каждый момент времени должно соответствовать состоянию предметной области; ь изменение состояния предметной области должно приводить к соответствующему изменению состояния базы данных; ь ограничения предметной области, отраженные в модели предметной области, должны некоторым образом отражаться и учитываться базе данных. 2. Легкость разработки и сопровождения базы данных. 3. Скорость выполнения операций обновления данных (вставка, обновление, удаление кортежей). 4. Скорость выполнения операций выборки данных.
Описание предметной области : 1. Сотрудники организации выполняют проекты. 2. Проекты состоят из нескольких заданий. 3. Каждый сотрудник может участвовать в одном или нескольких проектах, или временно не участвовать ни в каких проектах. 4. Над каждым проектом может работать несколько сотрудников, или временно проект может быть приостановлен, тогда над ним не работает ни один сотрудник. 5. Над каждым заданием в проекте работает ровно один сотрудник, в каждом проекте сотрудник может выполнять только одно задание. 6. Каждый сотрудник числится в одном отделе. 7. Каждый сотрудник имеет телефон, находящийся в отделе сотрудника. В ходе дополнительного уточнения того, какие данные необходимо учитывать, выяснилось следующее: 1. О каждом сотруднике необходимо хранить табельный номер и фамилию. Табельный номер является уникальным для каждого сотрудника. 2. Каждый отдел имеет уникальный номер. 3. Каждый проект имеет номер и наименование. Номер проекта является уникальным. 4. Каждое задание из проекта имеет номер, уникальный в пределах проекта. Работы в разных проектах могут иметь одинаковые номера.
Универсальное отношение СОТРУДНИКИ_ОТДЕЛЫ_ПРОЕКТЫ Н_СОТР ФАМ Н_ОТД ТЕЛ Н_ПРО ПРОЕКТ Н_ЗАДАН 1 Иванова 1 11 -22 -33 1 Космос 1 1 Иванова 1 11 -22 -33 2 Климат 1 2 Петров 1 11 -22 -33 1 Космос 2 3 Сидоров 2 33 -22 -11 1 Космос 3 3 Сидоров 2 33 -22 -11 2 Климат 2 Использование универсального отношения приводит либо к необходимости дополнительных усилий для реализации всех ограничений (правил), определенных в предметной области (дополнительный программный код), либо невозможности отобразить состояние предметной области – аномалии модификации. Аномалии проявляются при отображении в базе данных изменений, происходящих в предметной области. Вставка Удаление Обновление
Определение нормализации Для решения проблемы аномалии модификации данных при проектировании реляционных баз данных проводится нормализация отношений - декомпозиция отношения, находящегося в предыдущей нормальной форме, в два или более отношения, удовлетворяющих требованиям следующей нормальной формы. Декомпозицией схемы отношения R называется замена её совокупностью схем отношений Аi таких, что R = U Аi Декомпозиция отношения не должна приводить к потере зависимостей между атрибутами сущностей должна существовать операция реляционной алгебры, применение которой позволит восстановить исходной отношение. Проекция R[X] отношения R на множество атрибутов X называется собственной, если множество атрибутов X является собственным подмножеством множества атрибутов отношения R (множество атрибутов X не совпадает с множеством всех атрибутов отношения ). Собственные проекции R 1 и R 2 отношения R называются декомпозицией без потерь, если отношение R точно восстанавливается из них при помощи естественного соединения для любого состояния отношения R. Теорема (Хеза). Пусть R(A, B, C) является отношением, и A, B, C - атрибуты или множества атрибутов этого отношения. Если имеется функциональная зависимость A B, то проекции R(A, B) и R(A, C) образуют декомпозицию без потерь.
Нормальные формы Ппроектирование базы данных методом нормализации заключается в декомпозиции отношения, находящегося в предыдущей нормальной форме, в два или более отношения, удовлетворяющих требованиям следующей нормальной формы. Обычно выделяют следующую последовательность нормальных форм: 1. первая нормальная форма (1 NF); 2. вторая нормальная форма (2 NF); 3. третья нормальная форма (3 NF); 4. нормальная форма Бойса-Кодда (BCNF); 5. четвертая нормальная форма (4 NF); 6. пятая нормальная форма, или нормальная форма проекции-соединения (5 NF или PJ/NF). Основные свойства нормальных форм: • каждая следующая нормальная форма в некотором смысле лучше предыдущей; • при переходе к следующей нормальной форме свойства предыдущих нормальных свойств сохраняются. Нормальные формы отношений основываются на понятии функциональной зависимости.
Функциональная зависимость В отношении R множество атрибутов Y функционально зависит от множества атрибутов X тогда и только тогда, когда для любого состояния отношения R для любых его кортежей r 1 и r 2 из того, что r 1 X=r 2 X следует что r 1 Y=r 2 Y (т. е. во всех кортежах, имеющих одинаковые значения атрибутов X, значения атрибутов Y также совпадают). Из этого не следует , что если r 1 Y=r 2 Y , то r 1 X=r 2 X. Символически функциональная зависимость записывается : X Y Множество атрибутов X называется детерминантом функциональной зависимости, а множество атрибутов Y называется зависимой частью. Функциональная зависимость X Y называется полной, если атрибут Y не зависит функционально от любого точного подмножества X. Функциональная зависимость X Y называется транзитивной, если существует такой атрибут Z, что имеются функциональные зависимости X Z и Z Y и отсутствует функциональная зависимость Z X. Атрибуты называются взаимно независимыми, если ни один из них не является функционально зависимым от другого.
Вторая нормальная форма (2 NF) Отношение R находится во второй нормальной форме в том и только в том случае, когда находится в 1 NF, и каждый неключевой атрибут находится в полной функциональной зависимости от ключа. Отношение R находится во второй нормальной форме тогда и только тогда, когда отношение находится в 1 NF и нет неключевых атрибутов, зависящих от части составного ключа. Неключевой атрибут - это атрибут, не входящий в состав никакого потенциального ключа (в том числе и первичного). Если потенциальный ключ отношения является простым, то отношение автоматически находится во второй нормальной форме. Функциональные зависимости в отношении СОТРУДНИКИ_ОТДЕЛЫ_ПРОЕКТЫ {Н_СОТР, Н_ПРО} ФАМ Н_СОТР ФАМ {Н_СОТР, Н_ПРО} Н_ОТД Н_СОТР Н_ОТД {Н_СОТР, Н_ПРО} ТЕЛ Н_СОТР ТЕЛ {Н_СОТР, Н_ПРО} ПРОЕКТ Н_ПРО ПРОЕКТ {Н_СОТР, Н_ПРО} Н_ЗАДАН Н_ОТД ТЕЛ Приведенные функциональные зависимости отражают взаимосвязи и ограничения обнаруженные в предметной области, а не выведены из внешнего вида отношения.
Переход ко второй нормальной форме СОТРУДНИКИ_ОТДЕЛЫ ЗАДАНИЯ ФАМ Н_ОТД ТЕЛ 1 Иванов 1 11 -22 -33 2 Петров 1 11 -22 -33 3 Сидоров 2 33 -22 -11 ПРОЕКТЫ Н_ПРО Космос 2 Климат Н_ПРО Н_ЗАДАН 1 1 2 1 2 3 1 3 2 2 ПРОЕКТ 1 Н_СОТР 3 Н_СОТР Вставка Часть аномалий устранена, но в отношении СОТРУДНИКИ_ОТДЕЛЫ остались аномалии: Удаление Обновление
Третья нормальная форма (3 NF) Отношение R находится в третьей нормальной форме (3 NF) в том и только в том случае, если находится в 2 NF и каждый неключевой атрибут нетранзитивно зависит от первичного ключа. . Отношение R находится в третьей нормальной форме (3 НФ) тогда и только тогда, когда отношение находится в 2 НФ и все неключевые атрибуты взаимно независимы. Неключевой атрибут - это атрибут, не входящий в состав никакого потенциального ключа (в том числе и первичного). Функциональные зависимости в отношении СОТРУДНИКИ_ОТДЕЛЫ Н_СОТР ФАМ Н_СОТР Н_ОТД Н_СОТР ТЕЛ Н_ОТД ТЕЛ Н_ОТД В отношении присутствует функциональная зависимость неключевых атрибутов (зависимость номера телефона от номера отдела): Для того, чтобы устранить зависимость неключевых атрибутов, нужно произвести декомпозицию отношения на несколько отношений. При этом те неключевые атрибуты, которые являются взаимно зависимыми, выносятся в отдельное отношение, при этом детерминанты функциональных зависимостей остаются также в исходном отношении, в новых отношениях они становятся первичными ключами.
Переход к третьей нормальной форме (3 NF) СОТРУДНИКИ ЗАДАНИЯ ФАМ Н_ОТД 1 Иванов 1 2 Петров 1 3 Сидоров 2 ОТДЕЛЫ Н_СОТР Н_ПРО Н_ЗАДАН 1 1 2 1 2 3 1 3 3 Н_СОТР 2 2 ПРОЕКТЫ Н_ОТД ТЕЛ Н_ПРО ПРОЕКТ 1 11 -22 -33 1 Космос 2 33 -22 -11 2 Климат
Сравнение нормализованных и ненормализованных моделей Отношения слабо нормализованы (1 НФ, 2 НФ) Отношения сильно нормализованы (3 НФ) ХУЖЕ ЛУЧШЕ СЛОЖНЕЕ ЛЕГЧЕ Скорость выполнения вставки, обновления, удаления МЕДЛЕННЕЕ БЫСТРЕЕ Скорость выполнения выборки данных БЫСТРЕЕ МЕДЛЕННЕЕ Критерий Адекватность базы данных предметной области Легкость разработки и сопровождения базы данных сильно нормализованные отношения больше соответствуют предметной области, легче в разработке, для них быстрее выполняются операции модификации базы данных. Однако выполнение операций выборки данных происходит медленнее.
Нормальная форма Бойса-Кодда (BCNF) предметная область Пусть требуется хранить данные о поставках деталей некоторыми поставщиками. Предположим, что наименования поставщиков являются уникальными. Кроме того, каждый поставщик имеет свой уникальный номер. Данные о поставках можно хранить в следующем отношении: ПОСТАВКИ Номер поставщика PNUM Наименование поставщика PNAME Номер детали DNUM Поставляемое количество VOLUME 1 Фирма 1 1 100 1 Фирма 1 2 200 1 Фирма 1 3 300 2 Фирма 2 1 150 2 Фирма 2 2 250 3 Фирма 3 1 1000 Отношение содержит два потенциальных ключа - {PNUM, DNUM} и {PNAME, DNUM}
Нормальная форма Бойса-Кодда (BCNF) (анализ функциональных зависимостей) Данные хранятся в отношении с избыточностью - при изменении наименования поставщика, это наименование нужно изменить во всех кортежах, где оно встречается, т. е. просмотреть все кортежи отношения. Функциональные зависимости в отношении ПОСТАВКИ PNUM PNAME {PNUM, DNUM} VOLUME {PPNAME, DNUM} VOLUME PNAME PNUM {PNUM, DNUM} PNAME {PNAME, DNUM} PNUM Анализ функциональных зависимостей показывает, что отношение находится в 3 NF, тем не менее присутствует аномалия обновления и удаления. Отношение находится в нормальной форме Бойса-Кодда (BCNF) тогда и только тогда, когда детерминанты всех функциональных зависимостей являются потенциальными ключами. Отношение ПОСТАВКИ не находится в BCNF – имеются зависимости (выделены цветом), детерминанты которых не являются потенциальными ключами.
Переход к нормальной форме Бойса-Кодда (1 вариант) ПОСТАВКИ_1 ПОСТАВЩИКИ Номер поставщика PNUM Номер детали DNUM Поставляемое количество VOLUME Номер поставщика PNUM Наименование поставщика PNAME 1 1 100 1 Фирма 1 1 2 200 2 Фирма 2 1 3 300 3 Фирма 3 2 1 150 2 2 250 3 1 1000
Переход к нормальной форме Бойса-Кодда (2 вариант) ПОСТАВКИ_2 ПОСТАВЩИКИ Наименовани е поставщика PNAME Номер детали DNUM Поставляемое количество VOLUME Номер поставщика PNUM Наименование поставщика PNAME Фирма 1 1 100 1 Фирма 1 2 200 2 Фирма 1 3 300 3 Фирма 2 1 150 Фирма 2 2 250 Фирма 3 1 1000
Четвертая нормальная форма (4 NF) предметная область Пусть требуется учитывать данные об абитуриентах, поступающих в ВУЗ. При анализе предметной области были выделены следующие требования: • Каждый абитуриент имеет право сдавать экзамены на несколько факультетов одновременно. • Каждый факультет имеет свой список сдаваемых предметов. • Один и тот же предмет может сдаваться на нескольких факультетах. • Абитуриент обязан сдавать все предметы, указанные для факультета, на который он поступает, несмотря на то, что он, может быть, уже сдавал такие же предметы на другом факультете. АБИТУРИЕНТЫ_ФАКУЛЬТЕТЫ_ПРЕДМЕТЫ Абитуриент Факультет Предмет Иванов Математический Математика Иванов Математический Информатика Иванов Физический Математика Иванов Физический Физика Петров Математический Математика Петров Математический Информатика
Четвертая нормальная форма (4 NF) избыточность Отношение содержит только ключевые атрибуты, которые являются взаимнонезависимыми – выполняется условие NFBC. Однако в отношении имеется аномалия обновления, связанная с тем, что дублируются фамилии абитуриентов, наименования факультетов и наименования предметов. Эта аномалия легко устраняется стандартным способом вынесением всех наименований в отдельные отношения, оставляя в исходном отношении только соответствующие номера АБИТУРИЕНТЫ_ФАКУЛЬТЕТЫ_ПРЕДМЕТЫ Абитуриент Факультет Предмет Иванов Математический Математика Иванов Математический Информатика Иванов Физический Математика Иванов Физический Физика Петров Математический Математика Петров Математический Информатика
Четвертая нормальная форма (4 NF) аномалии Аномалия вставки. При попытке добавить новый кортеж, например (Сидоров, Математический, Математика), в отношение АБИТУРИЕНТЫ_ФАКУЛЬТЕТЫ_ПРЕДМЕТЫ мы обязаны добавить также и кортеж (Сидоров, Математический, Информатика), т. к. все абитуриенты математического факультета обязаны иметь один и тот же список сдаваемых предметов. АБИТУРИЕНТЫ_ФАКУЛЬТЕТЫ_ПРЕДМЕТЫ Абитуриент Факультет Предмет Иванов Математический Математика Иванов Математический Информатика Иванов Физический Математика Иванов Физический Физика Петров Математический Математика Петров Математический Информатика Сидоров Математический Математика Сидоров Математический Информатика
Четвертая нормальная форма (4 NF) аномалии (продолжение) Аномалия удаления. 1. При удалении из отношения АБИТУРИЕНТЫ_ФАКУЛЬТЕТЫ_ПРЕДМЕТЫ кортежа, например (Петров, Математический, Математика), мы обязаны удалить также и кортеж (Петров, Математический, Информатика. 2. Если Иванов забрал документы с физического факультета, то теряются сведения об экзаменах, сдаваемых на физическом факультете АБИТУРИЕНТЫ_ФАКУЛЬТЕТЫ_ПРЕДМЕТЫ Абитуриент Факультет Предмет Иванов Математический Математика Иванов Математический Информатика Иванов Физический Математика Иванов Физический Физика Петров Математический Математика Петров Математический Информатика
Четвертая нормальная форма (4 NF) Пусть R - отношение, и X, Y, Z- некоторые из его атрибутов (или непересекающиеся множества атрибутов). Тогда атрибуты (множества атрибутов) Y и Z многозначно зависят от X (обозначается X->> Y|Z), тогда и только тогда, когда из того, что в отношении содержатся кортежи r 1(x, y, z 1) и r 2(x, y 1, z) следует, что в отношении содержится также и кортеж r 3(x, y, z). Многозначная зависимость X->> Y|Z называется нетривиальной многозначной зависимостью, если не существует функциональных зависимостей X Y и X Z. Отношение R находится в четвертой нормальной форме (4 НФ) тогда и только тогда, когда отношение находится в НФБК и не содержит нетривиальных многозначных зависимостей. Теорема (Фейджина). Пусть X, Y, Z - непересекающиеся множества атрибутов отношения R. Декомпозиция отношения на проекции R 1(X, Y) и R 2(X, Z) будет декомпозицией без потерь тогда и только тогда, когда имеется многозначная зависимость X->> Y|Z. Замечание. Если зависимость является тривиальной, т. е. существует одна из функциональных зависимостей X Y или X Z, то получаем теорему Хеза.
Четвертая нормальная форма (4 NF) (переход) АБИТУРИЕНТЫ_ФАКУЛЬТЕТЫ_ПРЕДМЕТЫ Факультет Абитуриент Факультет Предмет Математический Иванов Математический Математика Физический Иванов Математический Информатика Математический Сидоров Физический Математика Физический Физика
Скачать презентацию Проектирование баз данных методом нормализации Проектирование базы
Нормализация v1.1.ppt