Главная » Microsoft SQL Server, Базы данных » Реляционные шаблоны – ЧАСТЬ 3

0

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

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

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

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

Принципы проектирования сущностей и атрибутов

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

?               Каждая сущность базы данных должна описывать одну “вещь”.

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

?               Каждый атрибут должен описывать свою сущность и никакую другую, связанную с ней.

Нормальные формы

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

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

Нормализованная модель базы данных имеет следующие преимущества перед ненормализованными:

?               повышенная целостность данных делает невозможным двойное хранение одних и тех же данных;

?               сокращение споров за блокировку ресурсов и улучшенная многопользовательская конкуренция;

?               меньшие размеры файлов.

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

Простота и нормализация

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

Первая нормальная форма (1НФ)

Первая нормальная форма подразумевает, что данные объединены в сущности и удовлетворяют следующим условиям.

?               Каждая единица данных представлена в скалярном атрибуте. Согласно Мерриаму- Вебстеру, скалярным называется значение, которое можно представить точкой на шкале.

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

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

Модель, в которой атрибуты повторяются (например, сущность заказа, содержащая атрибуты товар 1, товар2 и товарЗ для хранения нескольких позиций заказа), нарушает первую нормальную форму.

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

В качестве примера первой нормальной формы рассмотрим список базовых лагерей и туров базы данных туристической фирмы Cape Hatteras Adventures. В табл. 2.3 представлены данные о базовых лагерях, которые нарушают первую нормальную форму. Повторяющийся атрибут тура не является уникальным.

Таблица 2.3. Нарушение первой нормальной формы

BaseCamp

Tourl

Tour2 Tour3

Ashville

Appalachian Trail

Blue Ridge Parkway Hike

Cape Hatteras

Outer Banks Lighthouses

Freeport

Bahamas Dive

Ft. Lauderdale

Amazon Trek

West Virginia

Gauley River Rafting

Чтобы исправить эту модель и привести ее к первой нормальной форме, сведем повторяющиеся группы атрибутов тура в единый уникальный атрибут (табл. 2.4). После этого мы переместим все повторяющиеся значения в уникальные элементы. Сущность BaseCamp содержит

уникальный элемент для каждого базового лагеря, а атрибут BaseCampID (идентификатор базового лагеря) сущности Tour ссылается на первичный ключ сущности BaseCamp.

Таблица 2.4. База данных, удовлетворяющая первой нормальной форме

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

С теоретической точки зрения этот тип модели некорректен, поскольку атрибут не является скалярным значением. С практической точки зрения в нем есть следующие проблемы.

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

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

?               Бизнес-правила тяжело программировать и поддерживать.

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

Вторая нормальная форма (2НФ)

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

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

Примером базы данных, нарушающей вторую нормальную форму, является база данных телефонных номеров базовых лагерей туристической компании Cape Hatteras Adventures, добавленная в сущность BaseCampTour (табл. 2.5). Предположим, что первичный ключ представляет собой объединение атрибутов BaseCamp и Tour и что телефонные номера закреплены именно за лагерями, а не за турами.

Таблица 2.5. Нарушение второй нормальной формы

BaseCamp (ПК)

Tour (ПК)

BaseCampPhoneNumber

Ashville

Appalachian Trail

828-555-1212

Ashville

Blue Ridge Parkway Hike

828-555-1212

Cape Hatteras

Outer Banks Lighthouses

828-555-1213

Freeport

Bahamas Dive

828-555-1214

Ft. Lauderdale

Amazon Trek

828-555-1215

West Virginia

Gauley River Rafting

828-555-1216

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

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

Сущность Tour                Сущность BaseCamp

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

Таблица 2.6. Приведение ко второй нормальной форме

BaseCamp (ПК)

Tour (ПК)

BaseCamp (ПК)

PhoneNumber

Ashville

Appalachian Trail

Ashville

828-555-1212

Ashville

Blue Ridge Parkway Hike

Cape Hatteras

828-555-1213

Cape Hatteras

Outer Banks Lighthouses

Freeport

828-555-1214

Freeport

Bahamas Dive

Ft.Lauderdale

828-555-1215

Ft.Lauderdale

Amazon Trek

West Virginia

828-555-1216

West Virginia

Gauley River Rafting

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

Третья нормальная форма (ЗНФ)

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

Как и вторая нормальная форма, третья разрешается перемещением независимого атрибута в новую сущность.

Продолжая работать с примером базы данных турфирмы Cape Hatteras Adventures, назначим каждому базовому лагерю ответственного экскурсовода. Атрибут BaseCampGuide принадлежит сущности BaseCamp, однако если имя экскурсовода сопровождает какая-либо дополнительная информация, третья нормальная форма нарушается (табл. 2.7).

Таблица 2.7. Нарушение третьей нормальной формы

BaseCamp (ПК)

Сущность BaseCamp BaseCampPhoneNumber

LeadGuide

DateOfHire

Ashville

828-555-1212

Jeff Davis

1/5/1999

Cape Hateras

828-555-1213

Ken Frank

15/4/1997

Freeport

828-555-1214

Dab Smith

7/7/2001

Ft. Lauderdale

828-555-1215

Sam Wilson

1/1/2002

West Virginia

828-555-1216

Lauren Jones

1/6/2000

Атрибут DateOf Hire (дата приема на работу) описывает экскурсовода, а не базовый лагерь, следовательно, он не напрямую зависит от первичного ключа сущности. Эта зависимость является транзитивной через атрибут LeadGuide.

Сущность BaseCamp                                                       Сущность LeadGuide

BaseCamp(ПК) LeadGuide                                              LeadGuide (ПК) DateOfHire

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

Таблица 2.8. Приведение к третьей нормальной форме

Ashville

Jeff Davis

Jeff Davis

1/5/1999

Cape Hatteras

Ken Frank

Ken Frank

15/4/1997

Freeport

Dab Smith

Dab Smith

7/7/2001

Ft.Lauderdale

Sam Wilson

Sam Wilson

1/1/2002

West Virginia

Lauren Jones

Lauren Jones

1/6/2000

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

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

Нормальная форма Бойса-Кодда (BNCF)

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

Четвертая нормальная форма (4НФ)

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

В качестве примера представим следующую ситуацию.

1.              В качестве составного первичного ключа используются атрибуты BaseCamp (базовый лагерь) и LeadGuide (ответственный экскурсовод).

2.              Атрибуты Event и Guide сведены вместе в качестве первичного ключа.

3.              Так как оба ключа используют экскурсовода, все три сведены в единую сущность.

Приведенный пример нарушает четвертую нормальную форму.

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

Пятая нормальная форма (5НФ)

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

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

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

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

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

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

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

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

Реляционная алгебра

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

Реляционная алгебра содержит восемь реляционных операторов.

?               Ограничение. Возвращает строки, которые удовлетворяют заданному условию.

?               Проекция. Возвращает из набора данных заданные столбцы.

?               Произведение. Реляционное умножение возвращает все возможные комбинации данных из двух наборов.

?               Слияние. Реляционное сложение и вычитание, которое объединяет по вертикали две таблицы, помещая одну над другой и выравнивая столбцы.

?               Пересечение. Возвращает строки, общие для двух наборов данных.

?               Разность. Возвращает строки, уникальные для одного набора данных.

?               Объединение. Возвращает объединение двух таблиц по горизонтали, сопоставляя их строки по общим данным.

?               Деление. Возвращает строки, точно совпадающие в двух наборах данных.

В дополнение, в качестве метода, поддерживающего реляционную алгебру, SQL предлагает следующее.

?               Подзапросы. Это сходный с объединением, но более гибкий механизм. Результаты подзапроса могут использоваться вместо выражения, списка или набора данных во внешнем запросе.

На формальном языке реляционной алгебры:

?               таблица или набор данных называется отношением или сущностью;

?               строка называется кортежем’,

?               столбец называется атрибутом.

Дополнительная Реляционную алгебру мы применим на практике в главах 9 и 10.

информация

Резюме

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

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

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии