Содержание лекции Лекция 6 по дисциплине ИОБД

Содержание лекции Лекция № 6 по дисциплине «ИОБД» для потока ПЭ 12 -09 (УК) 12. 02. 2018 Нормализация баз данных Элементы теории нормализации отношений Примеры Лекции по дисциплине «ИОБД» 1

Нормализация БД Нормализации баз данных Данные могут группироваться в таблицы (отношения) разными способами. При проектировании БД в качестве отправной точки может использоваться одно универсальное отношение, в которое включаются все необходимые атрибуты. Оно может содержать все данные, которые предполагается размещать в БД. В качестве примера рассмотрим универсальное отношение сотрудники, содержащее информацию о сотрудниках предприятия (таблица 13). 12. 02. 2018 Нормализации баз данных 2

Нормализации баз данных Таблица 13 – Исходное отношение Нормализация БД Код сотрудника ФИО Должность Номер отдела Наимено. Квалифивание кация отдела 7513 Програм. Иванов И. И. мист 128 Отдел проектирования 9842 Сергеева Админист. С. С. ратор БД 42 Финансовый отдел 6651 Програм. Петров П. П. мист 9006 12. 02. 2018 128 Системный Николаев админист- 128 Н. Н. ратор C, Java DB 2 Отдел проекти- VB, Java рования Отдел Windows, проекти. Linux рования Нормализации баз данных 3

Нормализация БД Нормализации баз данных При использовании универсального отношения возникают две проблемы: • избыточность данных; • потенциальная противоречивость (аномалии). Под избыточностью понимают повторение данных в разных строках одной таблицы или в разных таблицах БД. Так, для каждого сотрудника отдела 128 повторяются данные « 128, Отдел проектирования» . Аномалии – это проблемы, возникающие в данных из-за дефектов проектирования БД. Существуют три вида аномалий: вставки, удаления и модификации. 12. 02. 2018 Нормализации баз данных 4

Нормализация БД Нормализации баз данных Аномалии вставки проявляются при вводе данных в дефектную таблицу. Добавляя информацию о новом сотруднике, мы должны добавить номер и название отдела. Если ввести данные, не соответствующие имеющимся в таблице (например, 42, отдел проектирования), будет не ясно, какая из строк БД содержит правильную информацию. Аномалии удаления возникают при удалении данных из дефектной схемы. Предположим, что все сотрудники отдела 128 уволились в один и тот же день. После удаления записей этих сотрудников в БД больше не будет ни одной записи, содержащей информацию об отделе 128. 12. 02. 2018 Нормализации баз данных 5

Нормализация БД Нормализации баз данных Аномалии модификации возникают при изменении данных дефектной схемы. Предположим, что отдел 128 решили переименовать в отдел передовых технологий. Необходимо изменить соответствующие данные о каждом сотруднике отдела. Если мы пропустим хотя бы одну запись, возникнет аномалия модификации. Правилом разработки хорошей структуры БД является необходимость избегать схем с большим числом пустых атрибутов. Если мы хотим указать, что один из ста служащих имеет особую квалификацию, для хранения этой информации не следует добавлять в таблицу еще один столбец, поскольку для остальных 99 работников значением столбца будет NULL. Вместо этого следует добавить новую таблицу, в которой будут храниться только кодовые номера и информация о квалификации тех работников, которых это касается. 12. 02. 2018 Нормализации баз данных 6

Нормализации баз данных Нормализация БД Решение перечисленных проблем состоит в разделении данных и связей, что обеспечивается процедурой нормализации. Концепции и методы нормализации были разработаны Э. Ф. Коддом. Нормализация отношений – это формальный аппарат ограничений на формирование отношений, который позволяет устранить дублирование и потенциальную противоречивость хранимых данных, уменьшает трудозатраты на ведение БД. Процесс нормализации заключается в декомпозиции исходных отношений на более простые отношения. Цель нормализации – получение такого проекта БД, в котором «каждый факт появляется лишь в одном месте» . 12. 02. 2018 Нормализации баз данных 7

Содержание лекции Лекция № 6 по дисциплине «ИОБД» для потока ПЭ 12 -09 (УК) 12. 02. 2018 Нормализация баз данных Элементы теории нормализации отношений Примеры Лекции по дисциплине «ИОБД» 8

Общие положения Элементы теории нормализации отношений При переходе от ЕR-диаграммы к схеме реляционной БД возникают некоторые проблемы. Главная из них - ненормализованность полученных отношений, а значит, избыточное дублирование данных. Наиболее распространенная причина данного явления - недостаточная детализация ER- диаграммы. Это приводит к попаданию в одно отношение наряду с зависимостями от ключа других зависимостей между атрибутами. 12. 02. 2018 Элементы теории нормализации отношений 9

Общие положения Элементы теории нормализации отношений Теория нормализации основана на наличии зависимостей между атрибутами отношения. Существуют различные классы зависимостей между атрибутами. Наиболее простые и важные из них - функциональные зависимости. Функциональные зависимости, так же как и ключи, - ограничения целостности, диктуемые предметной областью. Знать этот вид ограничений необходимо для того, чтобы уметь нормализовать отношения. 12. 02. 2018 Элементы теории нормализации отношений 10

Нормализации баз данных Нормализация БД Теория нормализации основана на наличии зависимостей между атрибутами отношения. Основными видами зависимостей являются: • функциональные; • многозначные; • транзитивные 12. 02. 2018 Нормализации баз данных 11

Определение функциональной зависимости Элементы теории нормализации отношений Формальное определение и его толкование Пусть R - схема отношения r и А, В - некоторые атрибуты из R. Говорят, что отношение r(R) удовлетворяет функциональной зависимости А В , если каждому значению А соответствует ровно одно связанное с ним значение В (в любой момент времени). Запись А В читается так: "А функционально определяет В" Запись, А В можно прочитать так: "В функционально зависит от А" 12. 02. 2018 Элементы теории нормализации отношений 12

Элементы теории нормализации отношений Определение функциональной зависимости t 0 А a 0 В b 0 t 1 a 1 b 1 t 2 a 1 b 1 Если t 1 и t 2 совпад ают здесь Если t 1 и t 2 совпада ют здесь 12. 02. 2018 С практической точки зрения смысл этого определения состоит в следующем: • если в отношении r существует в данный момент только один кортеж t 0 с заданным значением а 0 атрибута А, то а 0 очевидным образом соответствует только одно значение b 0 атрибута В; • если в отношении r есть два или более кортежей с одним и тем же значением а 1 атрибута А, то каждый из этих кортежей должен иметь одно и то же значение b 1 атрибута В. Элементы теории нормализации отношений 13

Определение функциональной зависимости Элементы теории нормализации отношений Зависимость, при которой каждый неключевой атрибут зависит от всего составного ключа и не зависит от его частей, называется полной функциональной зависимостью. Если атрибут А зависит от атрибута В, а атрибут В зависит от атрибута С (С В А), но обратная зависимость отсутствует, то зависимость А от С называется транзитивной. 12. 02. 2018 Элементы теории нормализации отношений 14

Определение функциональной зависимости Элементы теории нормализации отношений Многозначная зависимость. Говорят, что один атрибут отношения многозначно определяет другой атрибут того же отношения, если для каждого значения первого атрибута существует множество соответствующих значений второго атрибута. Многозначные зависимости могут быть: один-ко-многим (1: М); многие-к-одному (М: 1); многие-ко-многим (М: М). 12. 02. 2018 Элементы теории нормализации отношений 15

Определение функциональной зависимости Элементы теории нормализации отношений Каждая ступень процесса нормализации приводит схему отношений в последовательные нормальные формы. Для каждой ступени имеются наборы ограничений. Выделяют следующую последовательность нормальных форм: • первая нормальная форма (1 НФ); • вторая нормальная форма (2 НФ); • третья нормальная форма (3 НФ); • усиленная 3 НФ или нормальная форма Бойса-Кодда (БКНФ); • четвертая нормальная форма (4 НФ); • пятая нормальная форма (5 НФ). 12. 02. 2018 Элементы теории нормализации отношений 16

Нормализации баз данных Таблица 13 – Исходное отношение Нормализация БД Код сотрудника ФИО Должность Номер отдела Наимено. Квалифивание кация отдела 7513 Програм. Иванов И. И. мист 128 Отдел проектирования 9842 Сергеева Админист. С. С. ратор БД 42 Финансовый отдел 6651 Програм. Петров П. П. мист 9006 12. 02. 2018 128 Системный Николаев админист- 128 Н. Н. ратор C, Java DB 2 Отдел проекти- VB, Java рования Отдел Windows, проекти. Linux рования Нормализации баз данных 17

Определение функциональной зависимости Нормализация баз данных - 1 НФ Отношение находится в первой нормальной форме (1 НФ), когда каждая строка содержит только одно значение для каждого атрибута (столбца), то есть все атрибуты отношения имеют единственное значение (являются атомарными). В столбце Квалификация ненормализованной таблицы 13 содержатся списки значений (С, Java и т. д. ). Чтобы привести схему к 1 НФ, необходимо разместить в этом столбце атомарные значения. Самый простой способ заключается в выделении по одной строке на каждый элемент квалификации (таблица 14). 12. 02. 2018 Элементы теории нормализации отношений 18

Нормализация баз данных - 1 НФ Таблица 14 ФИО Должность Номер отдела 7513 Нормализация БД Код сотрудника Иванов И. И. Программист 128 7513 Иванов И. И. Программист 128 9842 Сергеева С. С. Администратор БД 42 6651 Петров П. П. Программист 128 9006 Николаев Н. Н. Системный администратор 128 Отдел Windows проектирования 9006 Николаев Н. Н. Системный администратор 128 Отдел проектирования 12. 02. 2018 Наименование отдела Отдел проектирования Финансовый отдел Отдел проектирования Нормализации баз данных Квалификация C Java DB 2 VB Java Linux 19

2 НФ Нормализация баз данных - 2 НФ Отношение находится во второй нормальной форме (2 НФ), если оно находится в 1 НФ, и каждый не ключевой атрибут полностью функционально зависит от всех составляющих первичного ключа. Если атрибут не зависит полностью от первичного ключа, то он внесен ошибочно и должен быть удален. Нормализация производится путем нахождения существующего отношения, к которому относится данный атрибут, или созданием нового отношения, в который атрибут должен быть помещен. 12. 02. 2018 Элементы теории нормализации отношений 20

2 НФ Нормализация баз данных - 2 НФ Таблица Квалификации_сотрудников (таблица 14) находится в 1 НФ, но не удовлетворяет 2 НФ. Первичный ключ должен уникальным образом идентифицировать каждую строку. Единственным вариантом является использование в качестве первичного ключа комбинации Код сотрудника и Квалификация. Это порождает схему: Квалификации_сотрудников (Код сотрудника, ФИО, Должность, Номер отдела, Наименование отдела, Квалификация). 12. 02. 2018 Элементы теории нормализации отношений 21

2 НФ Нормализация баз данных - 2 НФ Одной из имеющихся здесь функциональных зависимостей будет: Код сотрудника, Квалификация ФИО, Должность, Номер отдела, Наименование отдела. Но, кроме того, мы также имеем: Код сотрудника ФИО, Должность, Номер отдела, Наименование отдела. Другими словами, можно определить имя, должность и отдел, используя только код сотрудника. Это значит, что указанные атрибуты функционально зависимы только от части первичного ключа, а не от всего первичного ключа. Следовательно, указанная схема не находится в 2 НФ. 12. 02. 2018 Элементы теории нормализации отношений 22

Нормализация баз данных - 2 НФ Для приведения этой схемы в 2 НФ необходимо декомпозировать исходное отношение на два, в которых все не ключевые атрибуты будут полностью функционально зависеть от ключа: сотрудники (Код сотрудника, ФИО, Должность, Номер отдела, Наименование отдела) и Квалификации_сотрудников (Код сотрудника, Квалификация) (таблицы 15– 16). 12. 02. 2018 Элементы теории нормализации отношений 23

Нормализация баз данных - 2 НФ таблица 15 2 НФ Код сотрудника ФИО Иванов И. И. 7513 9842 Должность Программист Номер Наименование отдела 128 Сергеева Администратор 42 С. С. БД 6651 Петров П. П. Программист Отдел проектирования Финансовый отдел Отдел 128 проектирования Николаев Системный Отдел 9006 128 Н. Н. администратор проектирования 12. 02. 2018 Элементы теории нормализации отношений 24

Нормализация баз данных - 2 НФ Таблица 16 Квалификация 7513 C 7513 Java 9842 DB 2 6651 VB 6651 Java 9006 Windows 9006 Linux 2 НФ Код сотрудника 12. 02. 2018 Элементы теории нормализации отношений 25

Нормализация баз данных - 3 НФ Отношение находится в третьей нормальной форме (ЗНФ), если оно находится во 2 НФ и ни один из его не ключевых атрибутов не связан функциональной зависимостью с любым другим не ключевым атрибутом. Атрибуты, зависящие от других не ключевых атрибутов, нормализуются путем перемещения зависимого атрибута и атрибута, от которого он зависит, в новое отношение. Формально, для приведения схемы в 3 НФ необходимо исключить все транзитивные зависимости. Схема отношения сотрудники (таблица 15) содержит следующие функциональные зависимости: Код сотрудника ФИО, Должность, Номер отдела, Наименование отдела и Номер отдела Наименование отдела. 12. 02. 2018 Элементы теории нормализации отношений 26

Нормализация баз данных - 3 НФ Первичным ключом является Код сотрудника, и все атрибуты полностью функционально зависимы от него (первичный ключ определяется единственным атрибутом). При этом Номер отдела ключом не является. Функциональная зависимость Код сотрудника Наименование отдела является транзитивной, поскольку содержит промежуточный шаг (зависимость Номер отдела Наименование отдела). Для приведения в 3 НФ необходимо исключить эту транзитивную зависимость, декомпозируя отношение на два: сотрудники (Код сотрудника, ФИО, Должность, Номер отдела) и отделы (Номер отдела, Наименование отдела) (таблицы 17– 18). 12. 02. 2018 Элементы теории нормализации отношений 27

Нормализация баз данных - 3 НФ Таблица 17 ФИО Должность Номер отдела 7513 3 НФ Код сотрудника Иванов И. И. Программист 128 9842 Сергеева С. С. Администратор БД 6651 42 Петров П. П. 128 Николаев Н. Н. 9006 Программист Системный администратор 128 Таблица 18 Номер отдела Наименование отдела 42 Финансовый отдел 128 Отдел проектирования 12. 02. 2018 Элементы теории нормализации отношений 28

Нормализация баз данных - БКНФ 3 НФ Нормальная форма Бойса-Кодда (БКНФ) является развитием 3 НФ и требует, чтобы в отношении были только такие функциональные зависимости, левая часть которых является потенциальным ключом отношения. Потенциальный ключ представляет собой атрибут (или множество атрибутов), который может быть использован для данного отношения в качестве первичного ключа. Фактически первичный ключ – это один из потенциальных ключей, назначенный в качестве первичного. Детерминантом называется левая часть функциональной зависимости. Отношение находится в БКНФ тогда и только тогда, когда каждый детерминант отношения является потенциальным ключом. 12. 02. 2018 Элементы теории нормализации отношений 29

Нормализация баз данных - БКНФ 3 НФ Алгоритм приведения ненормализованных схем в 3 НФ показан на рисунке 15. На практике построение 3 НФ в большинстве случаев является достаточным и приведением к ней процесс построения реляционной БД заканчивается. 12. 02. 2018 Элементы теории нормализации отношений 30

Алгоритм приведения ненормализованных схем в 3 НФ 12. 02. 2018 Элементы теории нормализации отношений 31

Нормализация баз данных - БКНФ 3 НФ Запомнить правила нормализации помогает изречение: «Нормализация – это ключ, целый ключ и ничего, кроме ключа» . Нормальные формы высших порядков (4 НФ и 5 НФ) представляют больший интерес для теоретических исследований, чем для практики проектирования БД. В них учитываются многозначные зависимости между атрибутами. Полной декомпозицией отношения называют такую совокупность произвольного числа его проекций, соединение которых позволяет получить исходное отношение. 12. 02. 2018 Элементы теории нормализации отношений 32

Определитель NULL указывает, что значение атрибута в настоящий момент неизвестно или неприемлемо для этого кортежа. Определитель NULL следует воспринимать как логическую величину «неизвестно» (например, в исторических записях иногда встречаются такие, как «Дата рождения неизвестна» ). Другими словами либо это значение не входит в область определения некоторого кортежа, либо никакое значение еще не задано. Ключевое слово NULL представляет собой способ обработки неполных или необычных данных. Однако определитель NULL не следует понимать как нулевое численное значение или незаполненную пробелами текстовую строку. 12. 02. 2018 Определитель NULL 33

Определитель NULL Нули и пробелы представляют собой некоторые значения, тогда как ключевое слово NULL призвано обозначать отсутствие какого-либо значения. Следовательно, определитель NULL следует рассматривать иначе, не так, как другие значения. Некоторые авторы используют термин «значение NULL» , но на самом деле определитель NULL не является значением, а лишь обозначает его отсутствие, а потому термин «значение NULL» использовать не рекомендуется. 12. 02. 2018 Определитель NULL 34

Определитель NULL Применение определителя NULL может вызвать проблемы на этапе реализации. Трудности возникают из-за того, что реляционная модель основана на исчислении предикатов первого порядка которое обладает двузначной или булевой логикой, т. е. допустимыми являются только два значения – истина и ложь. Применение определителя NULL означает, что придется вести работу с логикой более высокого порядка, например, трехзначной или даже четырехзначной (Codd, 1986, 1987, 1990). 12. 02. 2018 Определитель NULL 35

Определитель NULL Использование понятия NULL в реляционной модели является спорным вопросом. Э. Ф. Кодд (1990) рассматривает понятие NULL как составную часть этой модели, а другие специалисты считают этот подход неправильным, полагая, что проблема отсутствующей информации еще до конца не понята, удовлетворительное ее решение не найдено, а потому включение определителя NULL в реляционную модель является преждевременным (Date, 1995). Следует отметить, что не во всех реляционных системах поддерживается работа с определителем NULL. 12. 02. 2018 Определитель NULL Возврат 36

Домашнее задание • Подготовить конспект 6 -ой лекции • ЭТО ваша шпаргалка на экзамен и тестирование БЛАГОДАРЮ ЗА ВНИМАНИЕ! E-mail gal_vasina 123@mail. ru 12. 02. 2018 Заключение 37