Контрольная работа: Нормализация таблиц в реляционной модели базы данных
Каждое поле таблицы должно представлять уникальный тип информации. Это правило означает, что необходимо избавиться от повторяющихся полей и разделить составные поля на отдельные элементы данных. Правила построения первой нормальной формы требуют, чтобы все таблицы данных были плоскими и не содержали повторяющихся данных в различных строках. Под плоской понимается таблица, имеющая только два измерения: длина (число записей или строк) и ширина (число полей или столбцов). Ее ячейки не могут содержать больше одного значения. Если хотя бы одна ячейка таблицы содержит больше одного значения, для представления ее содержимого уже требуется третье измерение - глубина. Плоские таблицы и плоские файлы баз данных очень похожи тем, что имеют только два измерения. Однако в плоском файле содержится лишь одна таблица и не накладываются ограничения на содержимое ее ячеек.
Примером ненормализованных данных могут выступать данные, представленные в таблице 1.1 Таким способом обычно составляется расписание движения транспорта.
Таблица 1.1 - Ненормализованные данные
| Судно | Название | Рейс | Погрузка | Прибытие из | Прибытие | Порт | Отправление | Прибытие | Порт | Отправление |
| 526 | Japan Bear | 9203W | 5/31/92 | SFO | 6/6/92 | HNL | 6/8/92 | 7/15/92 | OSA | 7/18/92 |
| 603 | Korea Bear | 9203W | 5/05/92 | OAK | 6/19/92 | OSA | 6/21/92 | 6/25/92 | INC | 6/28/92 |
| 531 | China Bear | 9204W | 6/20/92 | LAX | 7/10/92 | PAP | 7/11/92 | 8/28/92 | SYD | 9/2/92 |
| 528 | Japan Bear | 9204W | 8/20/92 | SFO | 8/27/92 | HNL | 8/29/92 | 9/30/92 | OSA | 10/2/92 |
Поскольку суда останавливаются во многих портах, столбцы Прибытие, Порт и Отправление повторяются для каждой остановки. Такая структура записи данных не подходит для реляционной базы данных. запись приведенной информации не соответствует требованиям первой нормальной формы, поскольку содержит повторяющуюся группу столбцов. Эту таблицу необходимо разделить на две: Порты и рейсы судов, не содержащие повторяющихся групп, как показано в таблицах 1.2 и 1.3
Таблица 1.2 - Таблица “Рейсы судов”
| Судно | Название | Рейс | Погрузка | Прибытие из |
| 528 | Japan Bear | 9203W | 5/31/92 | SFO |
| 603 | Korea Bear | 9203W | 6/5/92 | OAK |
| 531 | China bear | 9204W | 6/20/92 | LAX |
| 528 | Japan bear | 9204W | 8/20/92 | SFO |
Таблица 1.3 - Таблица “Порты”
| Прибытие | Порт | Отправление |
| 6/6/92 | HNL | 6/8/92 |
| 6/19/92 | OSA | 6/21/92 |
| 7/10/92 | PAP | 7/11/92 |
| 8/27/92 | HNL | 8/29/92 |
| 7/15/92 | OSA | 7/18/92 |
| 6/25/92 | INC | 6/28/92 |
| 8/28/92 | SYD | 9/2/92 |
| 9/30/92 | OSA | 10/2/92 |
Теперь необходимо установить связь между таблицами Порты и Рейсы судов. В столбце рейс указывается текущий год, номер рейса за этот год, а также направление рейса (например, 9204W- это четвертый рейс за 1992 год в западном направлении). Таким образом, для связи между таблицами следует применять поля Судно и Рейс. Использовать какой-либо один из этих способов недостаточно, поскольку одно судно может делать несколько рейсов в течение года, а в одном направлении могут отправляться сразу несколько судов. Поскольку для удовлетворения требований первой нормальной формы придется создать новую таблицу Порты, необходимо отсортировать ее столбцы в порядке значимости. Первыми, как правило, размещаются столбцы, используемые для установки связи. При этом они располагаются в той последовательности, в какой они входят в составной первичный ключ. Данные показаны в таблице 1.4
Таблица 1.4 - Таблица “Порты”
| Судно | Рейс | Порт | Прибытие | Отправление |
| 528 | 9203W | HNL | 6/6/92 | 6/8/92 |
| 603 | 9203W | OSA | 6/19/92 | 6/21/92 |
| 531 | 9204W | PAP | 7/10/92 | 7/11/92 |
| 528 | 9204W | HNL | 8/27/92 | 8/29/92 |
| 528 | 9203W | OSA | 7/15/92 | 7/18/92 |
| 603 | 9203W | INC | 6/25/92 | 6/28/92 |
| 531 | 9204W | SYD | 8/28/92 | 9/2/92 |
| 528 | 9204W | OSA | 9/30/92 | 10/2/92 |
Теперь необходимо определить ключевые поля таблицы Порты, что дает возможность точно идентифицировать ее записи. Обязательно необходимо создать первичный ключ, поскольку от этой таблицы могут зависеть многие другие. Необходимо добавить столбцы Судно и рейс, так как они обеспечивают связь с данными таблицы Рейсы судов, также добавить поле Порт для создания совершенного уникального ключа (столбы Судно и Рейс могут содержать повторяющиеся значения). Комбинации Судно+Рейс+Порт представляет собой составной первичный ключ, значение которого однозначно идентифицирует запись. Значения этого ключа не повторяются, поскольку учтена возможность дважды делать остановку в одном порту (придвижении туда и обратно). Так, если судно возвращается с востока, рейс помечается суффиксом “Е".
1.3 Вторая нормальная форма
Для приведения таблиц ко второй нормальной форме необходимо обеспечить полную зависимость столбцов, которые не являются ключевыми, от первичного ключа, а если этот ключ составной, то от каждого его элемента. Под полной зависимостью понимается возможность однозначного определения значения каждого неключевого поля с помощью значения первичного ключа. Если для однозначного определения используется составной первичный ключ, то это правило применяется к каждому значению из полей, входящих в составной ключ. Всегда, когда это возможно, в качестве первичного ключа следует использовать самые простые данные, имеющие “естественные" уникальные значения. Перед переходом ко второй нормальной форме необходимо привести данные к первой нормально форме. В процессе создания второй нормальной формы большая часть повторяющихся данных, оставшихся в таблице после приведения ее к первой нормальной форме, будет удалена.
Для создания в таблице Рейсы судов однозначного ключа придется использовать составной ключ (Судно+Рейс). Поскольку номер и название судна могут повторяться. Поля Судно и Название не зависят от первичного ключа, так как полем Рейс ничего не определяется. Название судна указывается в каждом рейсе. Так, например, название JapanBear появляется дважды. Все эти недостатки нарушают правила второй нормальной формы. Возникает необходимость разбиения таблицы Рейсы судов еще на две: Рейсы и Суда. Каждый корабль описывается одной строкой в таблице суда, а одна строка таблицы Рейсы описывает рейс одного судна (с целью упрощения построения базы данных восточные и западные направления рассматриваются как отдельные рейсы). Как и в таблице Порты, для установления соответствия между рейсами и судами необходимо создать ключ, поэтому необходимо добавить поле номеров судов в таблицу Рейсы. Таблицы Суда и Рейсы показаны в таблицах 1.5 и 1.6
Таблица 1.5 - Таблица “Суда”
| Судно | Название |
| 528 | Japan Bera |
| 603 | Korea Bear |
| 531 | China bear |
Таблица 1.6 - Таблица “Рейсы”
| Судно | Рейс | Погрузка | Прибытие из |
| 528 | 9203W | 5/31/92 | SFO |
| 603 | 9203W | 6/5/92 | OAK |
| 531 | 9204W | 6/20/92 | LAX |
| 528 | 9204W | 8/20/92 | SFO |
После определения первичных ключей для каждой таблицы можно проверить, чтобы все данные, включенные в таблицы, относились к соответствующим объектам. Другими словами, следует убедиться, что каждое поле функционально зависит от первичного ключа, который идентифицирует исходный объект таблицы. Для каждого значения первичного ключа значения в столбцах данных должны относиться к объекту таблицы и полностью его описывать. Это правило используется двояко. Во-первых, в таблице не должно быть данных, не относящихся к объекту, определяемому первичным ключом. Во-вторых, данные в таблице должны полностью описывать объект.
1.4 Третья нормальная форма
В третьей нормальной форме столбцы, не являющиеся ключевыми, зависят от первичного ключа таблицы и не зависят от всех остальных столбцов. Должна быть возможность изменять значения любого поля (не входящего в первичный ключ) без воздействия на данные других полей. Прежде чем перейти к третьей нормальной форме, необходимо привести свои данные к первой, а затем - ко второй.
Таблицы суда и Рейсы уже представлены в третьей нормальной форме, поскольку они содержат повторяющихся групп столбцов и существует зависимость неключевых столбцов этих таблиц от ключевых. В таблице Порты нет повторяющихся групп полей, а неключевые поля Прибытие и Отправление зависят от составного ключа (Судно + Рейс + Порт) и не зависят друг от друга. (Дата прибытия никак не влияет на дату отправления, поскольку интервал между ними определяется временем погрузки товаров на судно, доступностью стоянок, погрузочных кранов, а также погодными условиями) Таким образом, таблица Порты удовлетворяет требованиям первой, второй и третьей нормальных форм.
Например, необходимо ввести в базу данных еще и служащих, членов команд кораблей (капитанов, инженеров и т.п.). Для этого необходимо добавить в таблицу Суда коды служащих, являющиеся первичными ключами таблицы Служащие, показанные в таблице 1.7
Таблица 1.7 - Таблица с транзитивным отношением между судами и служащими команды.
| Судно | Название | Капитан | Старший помощник | Первый помощник |
| 528 | Japan Bear | 01023 | 01155 | 01367 |
| 603 | Korea Bear | 00955 | 01203 | 00823 |
| 531 | China Bear | 00721 | 00912 | 01251 |
Такая таблица нарушает одно из правил третьей нормальной формы, поскольку никто из назначенных на какое-либо судно служащих от этого судна не зависит непосредственно (т.е. добавлямое неключевое поле не зависит от первичного ключа). Капитан, его помощник или старший помощник могут выполнять свои обязанности на любом судне. Каждого офицера можно в случае необходимости перевести на другой корабль или оставить на суше до следующего рейса.
Можно попробовать добавить столбец с номерами служащих в таблицу Рейсы. Но и это не решит всех проблем, так как судно может прибыть в порт с одним командным составом, а отбыть с совершенно другим. Кроме этого, возникает необходимость определить членов команды, остающихся на судне во время его стоянки в порту.
Все эти вопросы решаются с помощью таблицы Команды, приведенной в таблице 1.8 повторяющиеся значения полей Порт (порт отправления) и Отправление в (порт прибытия) создают записи для тех членов команды, которые несут ответственность за судно во время его пребывания в порту. Таблица Команды относится к категории связующих таблиц, поскольку все поля связаны с первичными ключами основных таблиц (Суда, Рейсы, Порты и Служащие) или являются частью этих таблиц.
Таблица 1.8 - Таблица “Команды”
| Судно | Рейс | Порт | Отправление в | Капитан | Старший помощник | Первый помощник |
| 528 | 9203W | SFO | HNL | 01023 | 01156 | 01367 |
| 528 | 9203W | HNL | HNL | 01023 | 01156 | 01367 |
| 528 | 9203W | HNL | OSA | 01023 | 01156 | 01367 |
| 528 | 9203W | OSA | OSA | 01023 | 01156 | 01367 |
| 528 | 9203W | OSA | INC | 01023 | 01156 | 01367 |
Теперь все таблицы являются плоскими, не содержат повторяющейся информации, за исключением данных, используемых в ключах. Все эти таблицы удовлетворяют требованиям первой, второй и третьей нормальной форм.
1.5 Четвертая нормальная форма
Чтобы база данных находилась в четвертой нормальной форме, необходимо, чтобы независимые элементы данных, между которыми существует связь типа многие-ко-многим, не хранились в одной таблице. Таблица команды не находится в четвертой нормальной форме, поскольку связи многие-ко-многим установлены между ее полем судно и полями с кодами членов команды.
Многие разработчики приложений баз данных игнорируют четвертую и пятую нормальные формы в своих программных продуктах, поскольку считают их весьма специфическими. Результатом этого зачастую является создание базы данных неправильной структуры, хотя это совсем не означает, что она не будет функционировать.
1.6 Пятая нормальная форма
Пятая нормальная форма требует обеспечения возможности точного восстановления исходной таблицы из таблиц, на которых она основана. Построение пятой нормальной формы требует удовлетворения требований третьей нормальной формы и, при наличии связей многие-ко-многим, соответствия правилам четвертой.