Занятие 4. Нормализация базы данных

(Продолжительность занятия 25 минут)

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

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

Процесс нормализации

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

Анализ элементов и отношений

Анализ элементов БД и отношений между ними позволяет построить структуру реляционной базы данных на основе идентификации объектов данных и связей между ними (рис. 6.16).

Рис. 6.16 Анализ элементов БД и отношений между ними

Вот что следует выявить при анализе:

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

Отношения позволяют связать таблицы средствами оператора SQL Join. Например, для связи таблиц Customers и Orders служит поле CustomerID.

Результат анализа — модель «элементы-отношения», которая логически представляет данные и связи между ними и служит основой физической реализации базы данных. Нормализация обычно является составной частью процесса анализа.

Создание отношений

В этом упражнении Вы создадите новую нормализованную базу данных.

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

> Создание новой базы данных

1. Создайте новую базу данных, используя Microsoft Access. Назовите ее Col-ledge.mdb и сохраните в папке WA\Practice\Ch06
2. Создайте таблицы и поля на основе приведенной ниже информации.

3. Сохраните базу данных

> Создание отношений

1. В меню Tools щелкните команду Relationships.
2. Добавьте все три таблицы и щелкните кнопку Close.
3. Перетащите поле Faculty ID таблицы Faculty Information на поле Faculty Instructor таблицы Class Information.

   По умолчанию в результате этой операции создается отношение «один ко многим». Это вполне разумно - ведь преподаватель может читать несколько разных курсов.

4. Щелкните кнопку Create.
5. Сохраните изменения и закройте Access.

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

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

Правила нормализации

В соответствии с правилами проектирования баз данных - правилами нормализации — каждая таблица должна описывать объекты одного типа: людей, места события или вещи. Существуют три формы нормализации базы данных (рис! 6.17), каждая из которых определяет состояние данных в БД.

Рис. 6.17 Нормальные формы

• Первая нормальная форма: запрещает повторяющиеся группы или множественные столбцы значений.
• Вторая нормальная форма: запрещает неключевому полю зависеть от части составного первичного ключа — разрешена только зависимость от первичного ключа в целом.
• Третья нормальная форма: запрещает неключевому полю зависеть от другого неключевого поля.

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

Ссылочная целостность

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

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

Процесс денормализации

Чрезмерная нормализация порождает проблемы: множество связей между таблицами вызывают избыточную нагрузку на ресурсы сервера при выполнении таких операций, как запросы. Выборочная денормализация позволяет более эффективно использовать процессорные ресурсы, что увеличивает производительность. Возможно несколько вариантов денормализации (рис. 6.18).

Рис. 6.18 Варианты денормализации

Введение избыточности

Если в результате нормализации связей становится слишком много, попробуйте искусственно ввести избыточность на уровне атрибута (столбца) или объекта (таблицы) следующим образом:

• добавьте дублирующие атрибуты (столбцы) в объекты (таблицы) базы данных;
• добавьте в таблицы БД производные атрибуты — например, агрегаты, максимальные величины и т.д.

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

Переопределение атрибутов

Переопределение атрибутов уменьшает объем данных одного вида. Вот как это сделать:

• добавьте производные атрибуты (столбцы) в таблицы БД;
• составной ключ, участвующий во множестве отношений, часто лучше заменить производным ключом меньшей сложности. Для этого годятся большие ключи и текстовые поля. Последнее лучше переопределить или дополнить абстрактным полем длиной не более 255 символов.

Этот способ уменьшает размер столбцов таблицы, снижая затраты ресурсов сервера во время запросов и других операций с БД.

Переопределение объектов

Переопределите объекты БД (таблицы), чтобы уменьшить влияния постороннего атрибута (столбца) или строки. Вот несколько вариантов денормализации.

• Разделить объект по атрибутам (столбцам) на два, чтобы отделить часто используемые данные.

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

• Разделить объект на два по строкам.

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

Резюме

После разработки реляционная база данных должна быть нормализована. Оптимальная реляционная БД обычно конструируется на основе анализа элементов и отношений между ними с последующей нормализацией.

Анализ элементов БД и отношений между ними позволяет выработать структуру реляционной базы данных. Цель нормализации базы данных — разработка хорошо организованной, оптимизированной и логичной схемы БД до начала ее физической реализации.

Чрезмерная нормализация БД иногда вызывает избыточную нагрузку на ресурсы сервера при выполнении таких операций, как запросы. Разумная выборочная денормализация позволяет повысить производительность БД благодаря более эффективному использованию ресурсов.