Этапы проектирования базы данных и их процедуры

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

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

Таблица находится в той или иной нормальной форме, если она удовлетворяет определенному набору требований.

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

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

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

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

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

Предположим, что сначала все заказы клиентов были сведены в одну таблицу ЗаказИнфо:

ЗаказИнфо

Номер заказа	Код клиента	Город	Улица	Вес заказа	Дата заказа	Код валюты	Наименование валюты
	АА	Минск	Правды 11		01.02.12		Бел рубль
	АА	Минск	Правды 11		01.02.12		Бел рубль
	АС	Пуховичи	Широкая 1		10.03.12		Росс рубль
	АС	Пуховичи	Широкая 1		5.03.12		Росс рубль
	АС	Пуховичи	Широкая 1		12.03.12		Евро
	АС	Пуховичи	Широкая 1		10.03.12		Росс рубль
	АС	Пуховичи	Широкая 1		15.04.12		Росс рубль
	АС	Пуховичи	Широкая 1		15.04.12		Доллар США
	АС	Пуховичи	Широкая 1		15.04.12		Росс рубль
	АВ	Мюнхен	Лейбница 8		20.04.12		Евро
	АВ	Мюнхен	Лейбница 8		20.04.12		Евро
	АД	Минск	Захарова 20		08.05.12		Доллар
	АС	Пуховичи	Широкая 1		15.05.12		Росс рубль
	АС	Пуховичи	Широкая 1		15.05.12		Росс рубль

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

Проблемы такой таблицы:

· Узнаем адрес клиента, только в том случае если он что- то заказал

· При удалении записи о продукте одновременно удаляются все сведения о самом заказе и о клиенте

· Если сменил адрес, например, то его надо менять в каждой записи

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

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

1-й шаг

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

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

ЗаказИнфо1

Номер заказа	Код клиента	Город	Улица	Вес заказа	Дата заказа	Код валюты	Наименование валюты
	АА	Минск	Правды 11		01.02.12		Бел рубль
	АС	Пуховичи	Широкая 1		10.03.12		Росс рубль
	АВ	Мюнхен	Лейбниц 8		20.04.12		Евро
	АД	Минск	Захарова 20		08.05.12		Доллар

В исходной таблице поле Код клиента определяется как внешний ключ

2-й шаг

В исходной таблице определяем поля, которые зависят только от первичного ключа, т.е. их значения не меняются при одинаковом значении записи первичного ключа. Очевидно, что это поля Город и Улица Только эти поля остаются в таблице ЗаказИнфо1, остальные поля удаляются. Таблица ЗаказИнфо1 переименуем в таблицу Клиенты. Из исходной таблицы ЗаказИнфо эти же поля удаляются и исходная таблица ЗаказИнфо перименовываем в таблицу ЗаказИнфо2:

Клиенты

Код клиента	Город	Улица
АА	Минск	Правды 11
АС	Пуховичи	Широкая 1
АВ	Мюнхен	Лейбниц 8
АД	Минск	Захарова 20

ЗаказИнфо2

Код клиента	Номер заказа	Вес заказа	Дата заказа	Код валюты	Наименование валюты
АА			01.02.12		Бел рубль
АА			01.02.12		Бел рубль
АС			10.03.12		Росс рубль
АС			05.03.12		Росс рубль
АС			12.03.12		Евро
АС			10.03.12		Росс рубль
АС			15.04.12		Росс рубль
АС			15.04.12		Доллар США
АС			15.04.12		Росс рубль
АВ			20.04.12		Евро
АВ			20.04.12		Евро
АД			08.05.12		Доллар США
АС			15.05.12		Росс рубль
АС			15.05.12		Росс рубль

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

Клиенты ЗаказИнфо2

Код клиента (ПК)		Код клиента (ВК)
Город		Номер заказа
Улица		Вес заказа
		Дата заказа
		Код валюты
		Наименование валюты

Во второй нормальной форме можно также заметить аномалии, в частности в таблице ЗакзаИнфо2:

· наименование валюты необходимо повторять при каждом заказе

· при удалении заказа может быть удалено и наименование валюты

Вывод: необходимо преобразовать таблицу ЗаказИнфо2, чтобы устранить указанную аномалию.

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

1-й шаг

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

Банк

Код валюты	Наименование валюты
	Бел рубль
	Росс рубль
	Доллар США
	Евро

2- й шаг

Из таблицы ЗаказИнфо2 удаляется поле Наименование валюты и таблица ЗаказИнфо2 переименовывается в Таблицу Заказы

Заказы

Код клиента	Номер заказа	Вес заказа	Дата заказа	Код валюты
АА			01.02.12
АА			01.02.12
АС			10.03.12
АС			05.03.12
АС			12.03.12
АС			10.03.12
АС			15.04.12
АС			15.04.12
АС			15.04.12
АВ			20.04.12
АВ			20.04.12
АД			08.05.12
АС			15.05.12
АС			15.05.12

3-й шаг

Объединяются Таблицы Клиенты, Заказы и Банк с помощью первичных и внешних ключей,

Структура базы данных в третьей нормальной форме:

Клиенты Заказы Банк

Код клиента (ПК)		Код клиента (ВК)		Код валюты (ВК)
Город		Код валюты (ВК)		Наименование валюты
Улица		Номер заказа
		Дата Заказа
		Вес заказа

Преимущества нормальных форм таблиц: устранение избыточности таблиц; независимость записей в таблице с первичным ключом от записей в таблице с соответствующим внешним ключом, т.е. записи в таблице с внешним ключом (detail -таблицы) могут быть изменены (удалены) без нарушений в master – таблицы.

Проектирование базы данных осуществляется в три этапа:

1) концептуальное проектирование;

2) логическое проектирование;

3) физическое проектирование.

Цель этапа концептуального проектирования – создание концептуальной модели данных исходя из представлений пользователей о предметной области. Для ее достижения выполняется ряд последовательных процедур.

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

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

3. Создание ER-модели предметной области. Для представления сущностей и связей между ними используются ER-диаграммы. На их основе создается единый наглядный образ моделируемой предметной области – ER-модель предметной области.

4. Определение атрибутов и их документирование. Выявляются все атрибуты, описывающие сущности созданной ER-модели. Каждому атрибуту присваивается осмысленное имя, понятное пользователям. О каждом атрибуте в словарь данных помещаются следующие сведения:

· имя атрибута и его описание;

· тип и размерность значений;

· значение, принимаемое для атрибута по умолчанию (если такое имеется);

· может ли атрибут иметь Null-значения;

· является ли атрибут составным, и если это так, то из каких простых атрибутов он состоит. Например, атрибут «Ф.И.О. клиента» может состоять из простых атрибутов «Фамилия», «Имя», «Отчество», а может быть простым, содержащим единые значения, как-то «Сидоров Евгений Михайлович». Если пользователь не нуждается в доступе к отдельным элементам «Ф.И.О.», то атрибут представляется как простой;

· является ли атрибут расчетным, и если это так, то как вычисляются его значения.

5. Определение значений атрибутов и их документирование. Для каждого атрибута сущности, участвующей в ER-модели, определяется набор допустимых значений и ему присваивается имя. Например, атрибут «Тип счета» может иметь только значения «депозитный», «текущий», «до востребования», «карт-счет». Обновляются записи словаря данных, относящиеся к атрибутам, – в них заносятся имена наборов значений атрибутов.

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

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

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

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

2. Определение набора таблиц исходя из ER-модели и их документирование. Для каждой сущности ER-модели создается таблица. Имя сущности – имя таблицы. Осуществляется формирование структуры таблиц на основании правил, изложенных в лекции 10. Устанавливаются связи между таблицами посредством механизма первичных и внешних ключей. Структуры таблиц и установленные связи между ними документируются.

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

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

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

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

· обязательные данные. Выясняется, есть ли атрибуты, которые не могут иметь Null-значений;

· ограничения для значений атрибутов. Определяются допустимые значения для атрибутов;

· целостность сущностей. Она достигается, если первичный ключ сущности не содержит Null-значений;

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

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

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

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

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

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

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

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

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

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

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

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

Дата добавления: 2015-05-26