---

Ниже — готовый отчёт по ПР №7 на основе схемы из ПР №6. В ПР №6 итоговая схема уже приведена к 3НФ, поэтому в ПР №7 в качестве исходной берётся схема до корректировок: с атрибутом Автор в таблице Книга и составным ФИО.

---

Практическая работа №7

«Приведение базы данных к нормальной форме 3НФ»

Тема: приведение БД к 3НФ.
Цель: проанализировать схему на избыточность и привести её к 3НФ.

---

Задача 1. Анализ исходной схемы

Исходная схема — из ПР №6 до нормализации:

· Книга(Книжный_номер, Название, Автор, Год_издания, Издательство)
· Читатель(Номер_билета, ФИО, Телефон, Адрес, Дата_регистрации)
· Экземпляр(Инвентарный_номер, Книжный_номер, Состояние, Местоположение)
· Сотрудник(Табельный_номер, ФИО, Должность, Телефон)
· Выдача(Номер_выдачи, Дата_выдачи, Дата_возврата_план, Дата_возврата_факт, Номер_билета, Инвентарный_номер, Табельный_номер)

Выявленные проблемы

Проблема Где проявляется Нарушение
Многозначный атрибут Автор В одной книге может быть несколько авторов, например Ильф И.А.; Петров Е.П. 1НФ
Составной атрибут ФИО Читатель.ФИО, Сотрудник.ФИО 1НФ
Повторение данных об авторе Один автор повторяется в нескольких строках Книга Избыточность
Повторение издательства АСТ, Эксмо, Азбука повторяются у разных книг Избыточность, но не нарушение 3НФ
Частичные зависимости Все первичные ключи простые, кроме будущей Книга_Автор В исходной схеме явно не выражены
Транзитивные зависимости Издательство не определяет другие неключевые атрибуты, т.к. данных об издательстве больше нет Явно не выражены

Вывод по задаче 1: исходная схема нарушает 1НФ из-за многозначного Автор и составного ФИО. После устранения этих нарушений схема приводится к 3НФ.

---

Задача 2. Исходная схема с данными

Таблица Книга

Книжный_номер Название Автор Год_издания Издательство
978-5-01-000001-1 Мастер и Маргарита Булгаков М.А. 1967 АСТ
978-5-01-000002-8 Собачье сердце Булгаков М.А. 1987 АСТ
978-5-01-000003-5 Преступление и наказание Достоевский Ф.М. 1866 Азбука
978-5-01-000004-2 Идиот Достоевский Ф.М. 1868 Азбука
978-5-01-000005-9 Война и мир Толстой Л.Н. 1869 Эксмо
978-5-01-000006-6 Анна Каренина Толстой Л.Н. 1877 Эксмо
978-5-01-000007-3 Двенадцать стульев Ильф И.А.; Петров Е.П. 1928 АСТ
978-5-01-000008-0 Золотой телёнок Ильф И.А.; Петров Е.П. 1931 АСТ

Избыточность: автор Булгаков М.А. повторяется в двух строках, Достоевский Ф.М. — в двух, Толстой Л.Н. — в двух. Издательство АСТ повторяется многократно. В строках 7–8 в одном поле записаны два автора — это нарушение 1НФ.

Таблица Читатель

Номер_билета ФИО Телефон Адрес Дата_регистрации
1 Иванов Иван Иванович +7-900-100-01-01 г. Москва, ул. Ленина, д. 1, кв. 2 2024-01-10
2 Петрова Анна Сергеевна +7-900-100-01-02 г. Москва, ул. Мира, д. 5, кв. 10 2024-01-12
3 Сидоров Пётр Алексеевич +7-900-100-01-03 г. Москва, ул. Гагарина, д. 7, кв. 3 2024-01-15
4 Иванова Мария Ивановна +7-900-100-01-04 г. Москва, ул. Ленина, д. 1, кв. 5 2024-01-18
5 Смирнов Олег Викторович +7-900-100-01-05 г. Москва, ул. Пушкина, д. 9, кв. 8 2024-01-20

Избыточность/нарушение: ФИО — составной атрибут. Адрес также можно считать составным, но в данной работе он оставлен атомарным.

Таблица Экземпляр

Инвентарный_номер Книжный_номер Состояние Местоположение
101 978-5-01-000001-1 хорошее зал 1
102 978-5-01-000001-1 хорошее зал 1
103 978-5-01-000002-8 удовлетворительное зал 1
104 978-5-01-000003-5 хорошее зал 2
105 978-5-01-000004-2 хорошее зал 2
106 978-5-01-000005-9 хорошее зал 3
107 978-5-01-000007-3 удовлетворительное зал 3
108 978-5-01-000008-0 хорошее зал 3

Таблица Сотрудник

Табельный_номер ФИО Должность Телефон
10 Иванов Пётр Сергеевич библиотекарь +7-900-200-01-01
11 Смирнова Ольга Ивановна библиотекарь +7-900-200-01-02
12 Кузнецов Дмитрий Андреевич администратор +7-900-200-01-03

Нарушение: ФИО — составной атрибут.

Таблица Выдача

Номер_выдачи Дата_выдачи Дата_возврата_план Дата_возврата_факт Номер_билета Инвентарный_номер Табельный_номер
1001 2024-02-01 2024-02-15 2024-02-14 1 101 10
1002 2024-02-03 2024-02-17 NULL 2 103 11
1003 2024-02-05 2024-02-19 2024-02-18 1 104 10
1004 2024-02-07 2024-02-21 NULL 3 106 11
1005 2024-02-10 2024-02-24 2024-02-23 4 107 12
1006 2024-02-12 2024-02-26 NULL 5 108 10

---

Задача 3. Нормализация по шагам

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

Требование: все значения атомарны, нет повторяющихся групп.

Что делаем:

1. Убираем многозначный атрибут Автор из Книга.
2. Создаём таблицу Автор.
3. Создаём связующую таблицу Книга_Автор для связи M:M.
4. В Читатель и Сотрудник заменяем ФИО на Фамилия, Имя, Отчество.

Таблица Автор

ID_автора Фамилия Имя Отчество
1 Булгаков Михаил Афанасьевич
2 Достоевский Фёдор Михайлович
3 Толстой Лев Николаевич
4 Ильф Илья Арнольдович
5 Петров Евгений Петрович

Таблица Книга_Автор

Книжный_номер ID_автора
978-5-01-000001-1 1
978-5-01-000002-8 1
978-5-01-000003-5 2
978-5-01-000004-2 2
978-5-01-000005-9 3
978-5-01-000006-6 3
978-5-01-000007-3 4
978-5-01-000007-3 5
978-5-01-000008-0 4
978-5-01-000008-0 5

После этого таблица Книга принимает вид:

Книжный_номер Название Год_издания Издательство
978-5-01-000001-1 Мастер и Маргарита 1967 АСТ
978-5-01-000002-8 Собачье сердце 1987 АСТ
978-5-01-000003-5 Преступление и наказание 1866 Азбука
978-5-01-000004-2 Идиот 1868 Азбука
978-5-01-000005-9 Война и мир 1869 Эксмо
978-5-01-000006-6 Анна Каренина 1877 Эксмо
978-5-01-000007-3 Двенадцать стульев 1928 АСТ
978-5-01-000008-0 Золотой телёнок 1931 АСТ

Таблица Читатель после 1НФ

Номер_билета Фамилия Имя Отчество Телефон Адрес Дата_регистрации
1 Иванов Иван Иванович +7-900-100-01-01 г. Москва, ул. Ленина, д. 1, кв. 2 2024-01-10
2 Петрова Анна Сергеевна +7-900-100-01-02 г. Москва, ул. Мира, д. 5, кв. 10 2024-01-12
3 Сидоров Пётр Алексеевич +7-900-100-01-03 г. Москва, ул. Гагарина, д. 7, кв. 3 2024-01-15
4 Иванова Мария Ивановна +7-900-100-01-04 г. Москва, ул. Ленина, д. 1, кв. 5 2024-01-18
5 Смирнов Олег Викторович +7-900-100-01-05 г. Москва, ул. Пушкина, д. 9, кв. 8 2024-01-20

Таблица Сотрудник после 1НФ

Табельный_номер Фамилия Имя Отчество Должность Телефон
10 Иванов Пётр Сергеевич библиотекарь +7-900-200-01-01
11 Смирнова Ольга Ивановна библиотекарь +7-900-200-01-02
12 Кузнецов Дмитрий Андреевич администратор +7-900-200-01-03

Результат 1НФ: все атрибуты атомарны, повторяющихся групп нет.

---

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

Требование: таблица в 1НФ, неключевые атрибуты полностью зависят от всего первичного ключа.

Проверка:

Таблица Первичный ключ Частичные зависимости Результат
Читатель Номер_билета нет соответствует 2НФ
Книга Книжный_номер нет соответствует 2НФ
Автор ID_автора нет соответствует 2НФ
Книга_Автор Книжный_номер + ID_автора неключевых атрибутов нет соответствует 2НФ
Экземпляр Инвентарный_номер нет соответствует 2НФ
Сотрудник Табельный_номер нет соответствует 2НФ
Выдача Номер_выдачи нет соответствует 2НФ

Вывод: частичных зависимостей нет. Дополнительные таблицы на этапе 2НФ не требуются.

---

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

Требование: таблица во 2НФ и отсутствуют транзитивные зависимости неключевых атрибутов от первичного ключа.

Проверка:

Таблица Проверка Результат
Читатель Номер_билета → Фамилия, Имя, Отчество, Телефон, Адрес, Дата_регистрации; транзитивных зависимостей нет 3НФ
Книга Книжный_номер → Название, Год_издания, Издательство; Издательство не определяет другие неключевые атрибуты 3НФ
Автор ID_автора → Фамилия, Имя, Отчество 3НФ
Книга_Автор составной ключ, неключевых атрибутов нет 3НФ
Экземпляр Инвентарный_номер → Книжный_номер, Состояние, Местоположение 3НФ
Сотрудник Табельный_номер → Фамилия, Имя, Отчество, Должность, Телефон 3НФ
Выдача Номер_выдачи → все атрибуты выдачи 3НФ

Вывод: после устранения нарушений 1НФ схема соответствует 3НФ. Транзитивных зависимостей не выявлено.

---

Задача 4. Сравнение исходной и нормализованной схем

Исходная схема Нормализованная схема Что изменилось
Книга содержит Автор Автор вынесен в отдельную таблицу Устранён многозначный атрибут
Связь книга–автор хранится строкой Добавлена Книга_Автор Реализована связь M:M
Читатель.ФИО Фамилия, Имя, Отчество Устранён составной атрибут
Сотрудник.ФИО Фамилия, Имя, Отчество Устранён составной атрибут
Экземпляр без изменений Уже соответствовала 1НФ–3НФ
Выдача без изменений Уже соответствовала 1НФ–3НФ

Итоговый состав БД в 3НФ — 7 таблиц:

1. Читатель
2. Книга
3. Автор
4. Книга_Автор
5. Экземпляр
6. Сотрудник
7. Выдача

Итоговые ключи

Таблица Первичный ключ Внешние ключи
Читатель Номер_билета —
Книга Книжный_номер —
Автор ID_автора —
Книга_Автор Книжный_номер, ID_автора Книжный_номер → Книга, ID_автора → Автор
Экземпляр Инвентарный_номер Книжный_номер → Книга
Сотрудник Табельный_номер —
Выдача Номер_выдачи Номер_билета → Читатель, Инвентарный_номер → Экземпляр, Табельный_номер → Сотрудник

---

Задача 5. Вывод

В ходе выполнения работы схема БД «Библиотека» была проанализирована на избыточность и приведена к 3НФ.

Что дала нормализация:

1. Устранено повторение данных об авторах: автор хранится один раз в таблице Автор.
2. Реализована корректная связь «многие ко многим» между книгами и авторами через Книга_Автор.
3. Устранены составные атрибуты ФИО — заменены на атомарные Фамилия, Имя, Отчество.
4. Уменьшена вероятность аномалий вставки, обновления и удаления.
5. Схема соответствует 1НФ, 2НФ и 3НФ.
6. Итоговая схема содержит 7 таблиц и готова к реализации в реляционной СУБД: PostgreSQL, MySQL, SQLite и др.

Итог: нормализованная схема устраняет основные виды избыточности, обеспечивает целостность данных и соответствует требованиям 3-й нормальной формы.