Нормализация

Неэффективное использование памяти

Таблицы базы данных предназначены для хранения данных, необходимых для выполнения некоторых задач приложения. Любая таблица состоит из столбцов или полей, каждое из которых содержит определенный род сведений (например, фамилию заказчика), и строк или записей, содержащих наборы данных о конкретных лицах, предметах или других объектах.

Чтобы поупражняться в разработке приложений, предположим, что мы решили создать совершенно новую базу данных (с именем Книги) для заказа книг, не проводя предварительного анализа задач и определения объектов. Скорее всего в этом случае мы поместим все данные, необходимые для учета заказчиков и их заказов, в единственную таблицу Заказчики и заказы.

При таком подходе сразу обнаруживаются четыре недостатка.

В записи каждого нового заказа нам придется создавать дубликаты полей с данными о заказчике. Многократное сохранение в базе данных одних и тех же сведений приводит к неэффективному использованию памяти, к тому же при этом возрастает вероятность ошибок ввода.

Невозможно заранее предугадать, сколько книг будет включено в конкретный заказ. Поэтому нам придется резервировать в записи место для размещения максимально возможного числа книг, что приведет к неэффективному использованию памяти. Если предусмотренное максимальное число книг окажется недостаточным для некоторых заказов, нам придется переделывать весь проект. Кроме того, если в дальнейшем потребуется выяснить, какие книги были проданы конкретным заказчикам, нам придется производить поиск по всем полям Название книги каждой записи.

Использование памяти для хранения в базе данных сведений, которые можно вычислить при печати отчета. Например, если потребуется общая стоимость заказа, то нет необходимости хранить это значение в поле Общая стоимость таблицы.

Наличие в таблице поля, содержащего несколько элементов данных, значительно затрудняет поиск и сортировку по этим элементам. В данном примере использование составного поля, включающего адрес, город, страну и почтовый индекс заказчика, вообще делает невозможной сортировку по почтовому индексу, поскольку он может оказаться почти в любом месте этого поля.

Для решения описанных выше проблем (впрочем, полное устранение дублирующихся данных не всегда желательно) используется процесс, называемый нормализацией, позволяющий существенно сократить объем хранимой информации и устраняющий аномалии в организации хранения данных. Нормализация позволяет организовать поля данных в группы таблиц. Лежащая в основе нормализации математическая теория довольно сложна, но для практического применения ее можно сформулировать в виде довольно простых правил.

Правило 1: уникальность полей

Неэффективное использование памяти является основным недостатком ненормализованных таблиц, поэтому удаление избыточных полей из таблиц является одним из решений этой проблемы.

Правило 1: каждое поле таблицы должно представлять уникальный тип информации.

Это правило означает, что необходимо избавиться от повторяющихся полей и разделить составные поля на отдельные элементы данных. В нашем примере составные поля адреса нужно разделить на простые поля и включить в проект еще несколько таблиц для устранения повторения информации о книгах. В таблицы, созданные для повторяющихся данных, следует включить «ключевую» информацию из основной таблицы, чтобы можно было установить связи между новыми таблицами и исходной. Одно из возможных решений приведено на рис. 8.7.

Заказчики и заказы

Дата заказа

Имя заказчика

Адрес заказчика

Город заказчика

Район или область заказчика

Почтовый индекс заказчика

Страна заказчика

Телефон заказчика

Общая стоимость

Название магазина

Адрес магазина

Город магазина

Район или область магазина

Почтовый индекс магазина

Телефон магазина

Содержание заказа

Дата заказа

Имя заказчика

Название книги

Количество книг

Цена

Скидка

Розничная цена

Книги

Название книги

Предполагаемая цена

Год издания

Порядковый номер издания

Количество страниц

Аннотация

Диск

Книги и авторы

Название книги

Фамилия автора

Имя автора

Отчество автора

Сведения об авторе

Адрес электронной почты автора

Рис. 8.7 Проект базы данных Книги, в котором удалены
избыточные поля

Эти таблицы намного проще, поскольку теперь можно обрабатывать единственную запись на каждую заказанную книгу. К тому же не нужно резервировать в записи заказа дополнительные места для хранения большого числа книг. Если потребуется найти книгу с наибольшей ценой, достаточно просмотреть отдельную таблицу Книги, в которой основные сведения о каждой книге записываются только один раз.

Однако в некотором смысле проблема дублирования данных даже расширилась, поскольку теперь в каждой записи таблицы Содержание заказа повторяются поля Дата заказа и Имя заказчика. Потенциально длинное поле Название книги в таблицах Книги, Книги и авторы и Содержание заказа также избыточно. Но эти «повторяющиеся» данные необходимы для поддержания связей между таблицами. Разрешить проблему поможет следующее правило нормализации.

Правило 2: первичные ключи

База данных хорошо спроектирована в том случае, если каждая запись в любой таблице однозначно идентифицируется. Это означает, что значение некоторого поля (или нескольких полей) не повторяется ни в одной записи в таблице. Такой идентификатор называется первичным ключом (или просто ключом).

Правило 2: каждая таблица должна иметь уникальный идентификатор, или первичный ключ, который может состоять из одного или нескольких полей.

Всегда, когда это возможно, в качестве первичного ключа следует использовать самые простые данные, имеющие «естественные» уникальные значения. Почти всем публикуемым в мире книгам присваивается относительно короткий (12 символов) код ISBN (International Standard Book Number), который однозначно идентифицирует каждую книгу. Это делает поле ISBN хорошим «естественным» первичным ключом таблицы Книги. Хотя при использовании кода ISBN в трех таблицах кажется, что создаются повторяющиеся поля, на самом деле в этом случае значительно уменьшается общий объем хранимых данных. Длинное поле Название книги сохраняется в таблице Книги только один раз для каждой книги, а не в каждой записи заказа. Таким образом, дублируется только небольшой элемент данных, поле ISBN, что позволяет связать таблицы, содержащие данные о заказах, авторах и книгах. Реляционные СУБД предоставляют специальные средства, поддерживающие такую технику проектирования и позволяющие легко собирать в единое целое данные, хранящиеся в связанных таблицах.

При создании новой таблицы Access всегда предлагает определить для нее первичный ключ. Для многих таблиц нам придется создать искусственный первичный ключ. В таком случае Access добавляет к каждой записи поле, в которое записывается содержимое счетчика записей. Приложение Книги, по всей вероятности, должно генерировать уникальный номер или код для каждого создаваемого заказа. Access предоставляет специальный тип данных, так называемый счетчик, которой генерирует уникальный номер для каждой новой записи таблицы. В таблице Содержание заказа комбинация кода заказа, генерируемого Access, и ISBN книги, по-видимому, должна быть уникальной для каждой записи (пользователю нет смысла создавать в этой таблице несколько записей для конкретного названия книги в одном заказе). Вариант проекта базы данных после добавления первичных ключей представлен на рис. 8.8.

Заказчики и заказы

Код заказа

Дата заказа

Имя заказчика

Адрес заказчика

Город заказчика

Район или область заказчика

Почтовый индекс заказчика

Страна заказчика

Телефон заказчика

Общая cтои-мость

Название магазина

Адрес магазина

Город магазина

Район или область магазина

Почтовый индекс магазина

Телефон магазина

Содержание заказа

Код заказа

ISBN

Количество книг

Цена

Скидка

Розничная цена

Книги

ISBN

Название книги

Предпола гаемая цена

Год издания

Порядковый номер издания

Количество страниц

Аннота ция

Диск

Книги и авторы

ISBN

Фамилия автора

Имя автора

Отчество автора

Сведения об авторе

Адрес электронной почты автора

Без заливки показаны ПЕРВИЧНЫЕ КЛЮЧИ

Рис. 8.8 Таблицы базы данных Книги, с определенными в них первичными ключами

Правило 3: функциональная зависимость

После определения первичных ключей для каждой таблицы нужно проверить, чтобы все данные, включенные в таблицы, относились к соответствующим объектам. Другими словами, следует убедиться, что каждое поле функционально зависит от первичного ключа, который идентифицирует исходный объект таблицы.

Правило 3: для каждого значения первичного ключа значения в столбцах данных должны относиться к объекту таблицы и полностью его описывать.

Это правило используется двояко. Во-первых, в таблице не должно быть данных, не относящихся к объекту, определяемому первичным ключом. Например, хотя в каждом заказе требуется информация о заказчике, но заказчик является самостоятельным объектом, и данные о нем должны находиться в соответствующей таблице. Подобным образом автор может написать не одну книгу, так что полностью оправдано создание таблицы для данных об авторах отдельно от книг. Во-вторых, данные в таблице должны полностью описывать объект. Например, получателем заказанных книг не обязательно может быть заказчик (возможно, это подарок). Включение данных о получателе в таблицу Заказы делает ее более полной. Результат применения этого правила приведен на рис. 8.9.

Правило 4: независимость полей

Последнее правило позволяет проверить, не возникнут ли проблемы при изменении данных в таблицах.

Правило 4: вы должны иметь возможность изменять значения любого поля (не входящего в первичный ключ) без воздействия на данные других полей.

Давайте снова обратимся к таблице Заказы, представленной на рис. 8.9. При применении второго и третьего правил мы оставили сведения о магазине в таблице Заказы, поскольку казалось, что они нужны для полного описания заказа. Обратите внимание, что изменение названия магазина не влияет на другие поля записи. Но если несколько заказов содержат неправильное название магазина, то придется изменять много записей. Кроме того, если в заказе неверно указан магазин, то нельзя заменить название магазина без изменения его адреса и телефона в той же записи.

Заказчики

Код заказчика

Имя заказчика

Адрес заказчика

Город заказчика

Район или область заказчика

Почтовый индекс заказчика

Страна заказчика

Телефон заказчика

Заказы

Код заказа

Дата заказа

Имя получателя

Адрес получателя

Город получателя

Район или область получателя

Почтовый индекс получателя

Страна получателя

Код заказчика

Общая стоимость

Название магазина

Адрес магазина

Город магазина

Район или область магазина

Почтовый индекс магазина

Телефон магазина

Содержание заказа

Код заказа

ISBN

Количество книг

Цена

Скидка

Розничная цена

Книги

ISBN

Название книги

Предпола гаемая цена

Год издания

Порядковый номер издания

Количество страниц

Аннота ция

Диск

Книги и авторы

ISBN

Код автора

Порядковый номер автора

Авторы

ISBN

Фамилия автора

Имя автора

Отчество автора

Сведения об авторе

Адрес электронной почты автора

Без заливки показаны ПЕРВИЧНЫЕ КЛЮЧИ

Рис. 8.9 Проект базы данных Книги с дополнительными таблицами, обеспечивающими функциональную зависимость от первичных ключей таблиц

Поля Название магазина, Адрес магазина и Телефон магазина не являются независимыми друг от друга. На самом деле поля Адрес магазина, Город магазина, Район или область магазина и Телефон магазина функционально зависят от поля Название магазина (см. правило 3). Хотя это и не очевидно, но Название магазина описывает не заказ, а совсем другой объект. Таким образом, скрупулезное применение четвертого правила просто помогает определить изменения, которые следовало бы внести в проект еще при использовании предыдущих правил. В нашем же случае это приводит к выделению отдельной таблицы Магазины, как показано на рис.8.10.

Теперь при неправильном написании названия магазина достаточно исправить его один раз в таблице Магазины. Необходимо отметить, что в качестве первичного ключа таблицы Магазины вместо поля Название магазина (длина которого может составлять 40-50 символов) используется более короткое поле Код магазина (примерно 5 цифр), что позволяет сократить размер связующих данных в таблице Заказы.

Обратите внимание, что поле Общая стоимость удалено из таблицы Заказы, а из таблицы Содержание заказа исчезли поля Цена и Розничная цена. Дело в том, что цена книги редко меняется, поэтому нет смысла вносить цену в две таблицы: Книги и Содержание заказа. С помощью запроса, построенного на основе таблиц Книги и Содержание заказа, можно легко узнать цену любой книги и вычислить ее розничную цену. Поле Общая стоимость удалено из таблицы Заказы потому, что любое изменение цены, количества заказанных экземпляров и скидки влияет на общую стоимость заказа. Ее значение лучше вычислить после заполнения всего заказа и даже, может быть, в отчете, предназначенном для печати заказа.

Другим (не столь строгим) способом проверки независимости полей является просмотр записей на наличие повторяющихся данных. В предыдущем варианте проекта название, адрес, район или область, почтовый код и телефон конкретного магазина могло появиться во многих записях заказов. Теперь же, когда у нас есть отдельная таблица Магазины, для исправления адреса магазина достаточно изменить только одно поле в одной записи таблицы Магазины. Если в заказе неправильно указан магазин, нужно только изменить поле Код магазина в таблице Заказы, и проблема будет решена.

Необходимо обратить внимание, что для полного описания объектов таблиц в них включены дополнительные поля.

Заказчики

Код заказчика

Имя заказчика

Адрес заказчика

Город заказчика

Район или область заказчика

Почтовый индекс заказчика

Страна заказчика

Телефон заказчика

Заказы

Код заказа

Код заказчика

Дата заказа

Имя получателя

Адрес получателя

Город получателя

Район или область получателя

Почтовый индекс получателя

Страна получателя

Код магазина

Содержание заказа

Код заказа

ISBN

Количество книг

Скидка

Магазины

Общая стоимость

Название магазина

Адрес магазина

Город магазина

Район или область магазина

Почтовый индекс магазина

Телефон магазина

Книги

ISBN

Название книги

Предпола гаемая цена

Год издания

Порядковый номер издания

Количество страниц

Аннота ция

Диск

Книги и авторы

ISBN

Код автора

Порядковый номер автора

Авторы

ISBN

Фамилия автора

Имя автора

Отчество автора

Сведения об авторе

Адрес электронной почты автора

Без заливки показаны ПЕРВИЧНЫЕ КЛЮЧИ

Рис. 8.10 Проект базы данных Книги после применения всех правил нормализации

Дата добавления: 2014-01-05