Мишель Пуле
Внутренняя гармония базы данных

(Статья приводится с некоторыми сокращениями. Все выделения цветом внесены редактором сайта. АИ)

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

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

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

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

Затем я просматриваю внешний уровень, который включает приложения, работающие с базой данных, чтобы убедиться, что конфликты из-за общих ресурсов или блокировок не вызывают снижения производительности или нарушения целостности базы данных. Я использую этот установленный порядок, поскольку изменить порядок выполнения заданий или просто переписать какое-либо приложение так, чтобы оно работало более эффективно, легче, чем перестроить структуру рабочих файлов. Я выясняю, является ли база данных базой с захваченными транзакциями (captures transactions), как это бывает в e-commerce, или устроена как хранилище для накопления и анализа больших объемов данных (warehouse), выполняется ли анализ тенденций, или база представляет собой гибрид из двух предыдущих.

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

Диагностика возможных трудностей, связанных с нормализацией

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

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

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

Должным образом нормализованная база данных (по крайней мере, в третьей нормальной форме – 3NF) не спровоцирует появления вышеперечисленных проблем при вставке, удалении и модификации данных. Краткий обзор можно найти во врезке "Когда нормализация базы данных не нужна". По мере того как уровень нормализации снижается от третьего до второго или даже до первого, приходится учитывать возможность нарушения целостности данных, что может произойти в результате процесса денормализации. Для сохранения целостности данных нужно написать компенсирующие процедуры в каждой программе, которая производит модификацию данных в таблицах.

Нормализация базы данных Cellar

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

(См. Приложение. Контрольный список для оценки схемы)

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

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

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

Создать физическую структуру. На Рисунке 3 изображена диаграмма, которая была создана мной с помощью утилиты Enterprise Manager. На диаграмме показаны: пять таблиц - CELLAR, TASTEWINE, TASTING, TASTEGROUP и VISITOR, связи между таблицами, поля и их типы, уникальные ключевые поля каждой таблицы. Я могу сделать из этой схемы вывод, что таблицы CELLAR и TASTING связаны между собой по принципу “многие-ко-многим”, M:N (можно пробовать один сорт вина в процессе разных дегустаций и за одну дегустацию попробовать более одного сорта вина). Еще я могу сделать вывод, что таблицы TASTING и VISITOR связаны по принципу “многие-ко-многим” (на дегустации может присутствовать много приглашенных, и каждый из приглашенных может попробовать более чем один сорт вина). Таблицы TASTEWINE и TASTEGROUP являются связанными таблицами. На первый взгляд, база выглядит вполне нормализованной.

Перечислить таблицы с неопределенными значениями. Физическая схема и описание клиентом назначения базы данных Cellar помогли мне понять назначение большей части таблиц. Таблица TASTING содержит список дат и мест, где происходили дегустации вин (или будут происходить). Таблица VISITOR - это список участников дегустации и их телефонные номера. Таблицы TASTEWINE и TASTEGROUP являются связанными таблицами. Только об одной таблице я ничего не могу сказать, это таблица CELLAR. Она содержит список типов вин, их названия, изготовителя, год изготовления и стоимость. Но как эти поля связаны с первичным ключом, полем Bin? При дальнейшем изучении вопроса я выяснила, что число в поле Bin представляет собой контейнер в винном погребе. Каждый контейнер содержит один сорт вина. Сорт вина определяется как вино, произведенное одним производителем в один год. На Рисунке 4, где можно видеть часть таблицы CELLAR, данная структура показана (каждой строке таблицы соответствует свой сорт вина).

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

Найти скрытые массивы. Скрытые массивы - это список значений, которые хранятся как группа простых атрибутов в единственной таблице. Члены этого списка или группы есть отдельные столбцы в таблице, каждый из которых имеет свое имя. Пример скрытого массива - это дни недели, разные телефонные номера (домашний, рабочий и т. д.) и различные адреса (оплаты, отгрузка и т. д.). Другими словами, скрытый массив - это многозначный атрибут.

Перечислить поля с непонятным назначением. В таблице CELLAR поле YearProduced -целочисленное четырехзначное поле, которое содержит год производства вина. Чтобы понять, что записано в этом поле, надо хотя бы приблизительно представлять себе процесс производства вина. Вино выдавливается в тот год, когда происходит сбор урожая винограда. В северном полушарии сбор урожая происходит от сентября до ноября, в зависимости от местности. В южном полушарии урожай собирают в марте, апреле или мае. Знание этих фактов поможет вычислить возраст и пригодность вина. К моменту написания этой статьи вино, произведенное в северном полушарии в 2000 году, будет иметь возраст 1,5 года, а если его произвели в тот же год в южном полушарии, то 2 года.

Поле Ready также определено неоднозначно. Известно, что вино не пьют сразу после приготовления. Необходимо выждать некоторый период времени, от 6 месяцев, если это молодое Божоле, до многих лет, если это лучшие сорта французского красного вина. Таким образом, поле с именем Ready - другое целочисленное четырехразрядное поле, которое указывает, когда вино будет пригодно для питья.

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

Найти недействительные данные. Я всегда визуально проверяю данные клиентов на предмет, не являются ли они недействительными, то есть заводил ли клиент в базу ошибочные или некорректные данные. Если это так, то их надо выявить. Недействительные данные могут появляться, когда таблицы не имеют первичных ключей или уникальных натуральных ключей индекса, и тогда пользователи могут завести дублирующие записи. Некорректные данные также имеют место быть, когда не работает связь “один-ко-многим”, позволяя появляться в дочерней таблице записям, которые не связаны с записью в родительской таблице. Иногда я применяю специальные запросы (особенно те, которые используют внешние объединения) для поиска недействительных данных и показываю затем результаты этих запросов клиентам.

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

Если обнаружится, что есть таблицы с избыточными данными, это нужно исследовать более подробно. На Рисунке 3 поле с типом datetime присутствует в трех таблицах TASTING, TASTEGROUP и TASTEWINE. Все эти поля указывают на тот момент, когда происходила дегустация. Такое повторение - большая ошибка, связанная с созданием избыточных данных. Тем не менее, поле tdate в таблице TASTING – это первичное ключевое поле. Поле Tgdate в таблице TASTEGROUP и поле twdate в таблице TASTEWINE - части первичного ключевого поля для соответствующих таблиц. Поля tgdate и twdate - также внешние ключи и части принудительной связи между таблицами.

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

В таблице CELLAR, которая уже была в моем списке на модернизацию, я нашла одно транзитивное отношение. Комментарии, описывающие вкусовые качества вин, не будет зависеть от места хранения вина. Дата, когда вино будет готово, также не будет зависеть от места хранения. Комбинация полей Wine, Producer и YearProduced определяет поле Ready, то есть дату, когда оно будет готово. Многие вещи определяют вкусовые качества вина: сорт вина, производитель и когда оно было изготовлено. В поле Comments записывается информация о вкусовых качествах вина. Таким образом, комбинация полей Wine, Producer и YearProduced определяет значения полей Ready и Comments. Эта комбинация есть транзитивная взаимосвязь в таблице CELLAR. Когда обнаруживается транзитивное отношение, необходимо развести связанные поля по разным таблицам и затем связать эти две таблицы друг с другом. Я добавляю это предложение в список на изменение.

Найти неполные функциональные зависимости. Полная функциональная зависимость означает, что каждое поле, которое не является ключом в таблице, целиком зависит от первичного ключа. Если первичный ключ построен по нескольким столбцам, то всякое поле, которое не является ключевым, должно зависеть от полного первичного ключа, а не от одной его части. Любая таблица, содержащая неполную функциональную зависимость, зависит от первичного ключа в первой нормальной форме 1NF. На Рисунке 1 показан пример таблицы в первой нормальной форме 1NF с встроенным массивом (контрольным списком). Эта таблица не имеет первичного ключа. Нужно разложить ее во вторую нормальную форму, затем в третью нормальную форму, следуя описанию в статье “Четыре грани целостности”.

Предполагаемые изменения в базах данных. Я переработала пакетные файлы для базы Cellar, которые представлены в Web-листинге 3. Я предлагаю разделить таблицу CELLAR на две таблицы, WINETYPE и WINESTORAGE. Таблица WINETYPE содержит информацию в виде списка вин в погребе, производителя, года изготовления, времени, когда вино будет готово, и комментария к каждому вину. Я идентифицировала каждую запись в таблице the WINETYPE первичным ключом WineID. Связанная с таблицей WINETYPE таблица WINESTORAGE содержит свои записи на каждый номер хранилища; запись включает в себя поле WineID, количество бутылок и стоимость за каждую бутылку. Таблица WINESTORAGE связана с таблицей WINETYPE принудительной связью из-за того, что я ограничила хранение информации о вине в таблице WINESTORAGE только теми винами, описание которых есть в таблице WINETYPE.

Я также полагаю, что существует необходимость разделить таблицу VISITOR на две: PERSON и PERSONPHONE. Таблица PERSON содержит список всех лиц, участвовавших в дегустации вина. Я идентифицировала каждую персону ключевым полем VID. Таблица PERSONPHONE содержит номера и типы телефонов. Если человек имеет три телефона (домашний, рабочий и сотовый), тогда записи в таблице VISITOR будет соответствовать три записи в таблице PERSONPHONE. Таким образом, связь между этими таблицами будет “один-ко-многим” и в то же время она будет принудительной, поскольку я хочу, чтобы любая запись номера телефона в таблице PERSONPHONE обязательно имела связанную запись в таблице PERSON.

Код, представленный в Web-листинге 3, также содержит SQL-предложения, которые используются для перемещения данных из таблицы CELLAR в таблицы WINETYPE и WINESTORAGE и из таблицы VISITOR в PERSON и PERSONPHONE. Я могу создать новую таблицу в процессе написания программ для связывания старых таблиц. Но мне необходимо загрузить данные в новые таблицы, в то время как старые должны быть закрыты для доступа, чтобы не потерять те данные, которые могут быть модифицированы во время перегрузки.

Спроектировать представления для поддержания существующих приложений. Код, показанный в Web-листинге 3, содержит сценарии создания представлений Cellar и Visitor, которые будут виртуальными копиями таблиц CELLAR и VISITOR. Перед тем как создать новые представления, мне необходимо переименовать таблицы, и я это должна сделать тогда, когда доступ к таблицам будет отключен.

В представлении Cellar таблицы WINETYPE и WINESTORAGE объединены, как если бы они находились в старой таблице CELLAR. Тем не менее, в представлении Visitor, чтобы вновь создать массив телефонных номеров, которые хранились в таблице VISITOR, оператору объединения приходится поворачивать данные, чтобы вывести поля HomePhone, WorkPhone и CellPhone одной записью для каждой персоны.

Выполнить изменения. Если клиент согласен с моими предложениями по нормализации базы данных Cellar, и администратор базы данных предоставит мне терминал и время для работы, в течение которого не будет вестись работа с таблицами, я применяю код из Web-листинга 3. После того, как изменения сделаны, я могу представить схему базы, как показано на Рисунке 5.

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

Новая жизнь

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


Приложение

Контрольный список для оценки схемы

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

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

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

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

Найти скрытые массивы. Я ищу скрытые массивы (списки из значений, содержащихся в таблицах ). Таблица, которая содержит список величин, - это первая нормальная форма (1NF) или ненормализованная. Надо отметить такую таблицу для последующей декомпозиции.

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

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

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

Найти транзитивные зависимости внутри таблиц. Я анализирую каждую таблицу на предмет транзитивных зависимостей (наличие второй нормальной формы 2NF). Если присутствует одна или две транзитивные зависимости, я намечаю эту таблицу для декомпозиции до третьей нормальной формы.

Найти неполные функциональные зависимости. Если в таблице есть неполная функциональная зависимость (существует 1NF данных), я намечаю эту таблицу для декомпозиции до третьей нормальной формы (3NF).

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

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

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

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

 


Источник:

Мишель Пуле Внутренняя гармония базы данных Windows & .NET Magazine/RE // Издательство "Открытые системы" (http://www.osp.ru/)
Полный текст статьи:
http://www.osp.ru/win2000/sql/projtips/51tips10.htm
(Статья приводится с некоторыми сокращениями. Все выделения цветом внесены редактором сайта. АИ).

Мишель Пуле - обладает сертификатами MCIS и MCP. Входит в число основателей консалтинговой компании Mount Vernon Data Systems, штат Колорадо, США. В университете Дэнвера в качестве адъюнкта преподает программирование и проектирование баз данных. С ней можно связаться по адресу: mapoolet@sqlmag.com.

 

 

Hosted by uCoz