Описание основных приемов нормализации базы данных

Tsql теория > Описание основных приемов нормализации базы данных
28.05.2018 14:14:10



Статья:

Описание нормализации

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


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

Что такое «несогласованные зависимости»? Пользователь, которому нужно узнать, например, адрес определенного клиента, вполне обоснованно будет искать его в таблице Customers (клиенты), но искать в ней сведения о зарплате сотрудника, который работает с этим клиентом, не имеет смысла. Зарплата сотрудника связана с сотрудником (зависит от него), поэтому эти сведения следует хранить в таблице Employees (сотрудники). Несогласованные зависимости могут затруднять доступ к данным, так как путь к данным при этом может отсутствовать или быть неправильным. 

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

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

В описаниях ниже приведены соответствующие примеры. 

Первая нормальная форма

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

  • Каждый столбец в строке должен быть атомарным, т.е. столбец может содержать одно и только одно значение для заданной строки.
  • Каждая строка в таблице обязана содержать одинаковое количество столбцов. Учитывая обязательную атомарность столбцов, следует, что все строки в таблице должны иметь одинаковое количество значений.
  • Все строки в таблице, в общем, должны быть уникальны. Значения в столбцах могут дублироваться, но строки, взятые целиком — не могут.

 

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

Что произойдет при добавлении третьего поставщика? Добавление третьего поля нежелательно, так как для этого нужно изменять программу и таблицу, поэтому данный способ плохо адаптируется к динамическому изменению числа поставщиков. Вместо этого можно поместить все сведения о поставщиках в отдельную таблицу Vendors (поставщики) и связать товары с поставщиками с помощью кодов товаров или поставщиков с товарами с помощью кодов поставщиков.
Первичный ключ — один или несколько столбцов, уникально идентифицирующих строку в таблице. Значения в столбце, объявленном как первичный ключ, не может дублироваться в нескольких строках. В свою очередь, если первичным ключом является связка из нескольких столбцов, запрет на дублирование действует на данные, взятые из столбцов в целом. В записи, первичный ключ обозначаем символами «PK», через дефис после типа (если я не прав, поправьте пожалуйста, но я всегда пользовался таким способом).
Внешний ключ — один или более столбцов в таблице, значения которых соответствуют значениям некоторых столбцов в другой таблице (как правило, ее первичным ключам). Внешние ключи нужно стараться использовать везде и всегда, когда между двумя таблицами существует взаимосвязь. Технически, современные системы поддерживают автоматический контроль ссылочной целостности при использовании внешних ключей.

Вторая нормальная форма

Два правила второй нормальной формы говорят о том, что:

  • Таблица обязана соответствовать первой нормальной форме.
  • Все столбцы, не входящие в полный первичный ключ, должны зависеть от полного первичного ключа
  • Создайте отдельные таблицы для наборов значений, относящихся к нескольким записям.
  • Свяжите эти таблицы с помощью внешнего ключа.
Записи могут зависеть только от первичного ключа таблицы (составного ключа, если необходимо). Возьмем для примера адрес клиента в системе бухгалтерского учета. Этот адрес необходим не только таблице Customers, но и таблицам Orders, Shipping, Invoices, Accounts Receivable и Collections. Вместо того чтобы хранить адрес клиента как отдельный элемент в каждой из этих таблиц, храните его в одном месте: или в таблице Customers, или в отдельной таблице Addresses.

Третья нормальная форма

Третья норма данных расширяет две предыдущие, неся в себе два правила:

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

Например, в таблицу Employee Recruitment (наем сотрудников) можно включить адрес кандидата и название университета, в котором он получил образование. Однако для организации групповой почтовой рассылки необходим полный список университетов. Если сведения об университетах будут храниться в таблице Candidates, составить список университетов при отсутствии кандидатов не получится. Таким образом, создайте вместо этого отдельную таблицу Universities и свяжите ее с таблицей Candidates при помощи ключа — кода университета. 

Исключение. Выполнять нормализацию баз данных до третьей нормальной формы теоретически желательно, но не всегда практично. Например, для устранения всех возможных зависимостей между полями таблицы Customers придется создать отдельные таблицы для хранения сведений о городах, почтовых индексах, торговых представителях, категориях клиентов и любых других сведений, которые могут дублироваться в нескольких записях. С теоретической точки зрения нормализация желательна. Однако значительное увеличение числа маленьких таблиц может привести к снижению производительности СУБД или исчерпанию памяти и числа дескрипторов открытых файлов.


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

Другие формы нормализации

Кроме описанных нормальных форм, существует четвертая нормальная форма, которую также называют нормальной формой Бойса-Кодда (BCNF), и пятая нормальная форма, но на практике они применяются редко. Несоблюдение этих правил может привести к ухудшению архитектуры базы данных, но на функциональности это сказаться не должно.
Существует еще 4НФ и 5НФ, а также промежуточная между 3НФ и 4НФ - нормальная форма Бойса—Кодда (NFBC/НФБК).
Но не все так гладко и пушисто. Зачастую, чтобы извлечь информацию из нормализованной базы данных, приходится конструировать очень сложные запросы, которые, главным образом, из-за большого количества соединений таблиц, работают очень медленно.

Пример нормализации таблицы

Ниже приведен пример нормализации таблицы с вымышленными данными о студентах. 
  1. Таблица до нормализации: 

    Student# Advisor Adv-Room Class1 Class2 Class3
    1022 Петров 412 101-07 143-01 159-02
    4123 Иванов 216 201-01 211-02 214-01
  2. Первая нормальная форма: устранение повторяющихся групп

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

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

    Student# Advisor Adv-Room Class#
    1022 Петров 412 101-07
    1022 Петров 412 143-01
    1022 Петров 412 159-02
    4123 Иванов 216 201-01
    4123 Иванов 216 211-02
    4123 Иванов 216 214-01
  3. Вторая нормальная форма: устранение избыточных данных

    Обратите внимание на то, что в приведенной выше таблице каждое значение Student# сопоставлено с несколькими значениями Class#. Значения Class# функционально не зависят от значений Student# (первичный ключ), а это означает, что данное отношение не нормализовано до второй нормальной формы.

    Вторую нормальную форму представляют две следующих таблицы. 

    Таблица Students:

    Student# Advisor Adv-Room
    1022 Петров 412
    4123 Иванов 216


    Таблица Registration:

    Student# Class#
    1022 101-07
    1022 143-01
    1022 159-02
    4123 201-01
    4123 211-02
    4123 214-01
  4. Третья нормальная форма: устранение данных, не зависящих от ключа

    В последнем примере значения Adv-Room (номер кабинета научного руководителя) функционально зависят от атрибута Advisor. Решить эту проблему можно, переместив данный атрибут из таблицы Students в таблицу Faculty (факультет):

    Таблица Students:

    Student# Advisor
    1022 Петров
    4123 Иванов


    Таблица Faculty:

    Name Room Dept
    Петров 412 42
    Иванов 216 42