Инфологическое проектирование БД – фундамент информационной системы

Проектирование базы данных – самый трудный и ответственный этап во всем процессе разработки БД. Проект базы данных – это фундамент будущего программного комплекса. Если проект точен и выверен, работа с БД будет удобной и без конфликтов, необходимость внесение дополнений в БД не потребует кардинальных изменений в остальной программе.

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

Решение задач информационно-логического (инфологического) проектирования БД определяется спецификой задач предметной области и наиболее важной здесь является про­блема структуризации данных.

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

Структурный анализ предметной области – это начало проектирования БД. В результате него определяется вся совокупность данных разрабатываемой базы данных. Одни и те же данные могут группироваться в таблицы (отношения) различными способами.

Аномалии ненормализованной базы данных

Группировка атрибутов в отношениях должна быть рациональной, такой чтобы в таблицах были сведены к минимуму или отсутствовали:

• избыточность данных;

• аномалии обновления;

• аномалии удаления;

• аномалии ввода.

Достигается это нормализацией отношений. Использование ненормализованных таблиц может привести к нарушению целостно­сти данных (противоречивости информации) в базе данных.

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

Например, имеем отношение Сотрудник = (Табельный_№, ФИО, ГодРождения, Пол, Отдел, Должность, Номер_рабочей_комнаты, ФИО_ребенка). Для сотрудников, у которых есть дети, их личные данные будут повторяться столько раз, сколько у сотрудника детей.

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

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

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

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

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

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

Например, в вышеприведенном примере невозможно добавить в БД информацию о структуре организации (отделах, должностях) до ввода данных о сотруднике в соответствующем отделе на соответствующей должности, либо необходимо добавлять записи с «пустыми» значениями (NULL) данных о сотруднике, которые потом необходимо будет удалять, просматривая таблицу при наступлении соответствующих событий.

Нормализация БД – аппарат исключения избыточности и аномалий базы данных

Что­бы свести к минимуму возможность появления аномалий БД использу­ется нормализация.

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

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

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

Нормализация отношений — формальный аппарат ограничений на фор­мирование отношений (таблиц), который позволяет устранить дублирова­ние, обеспечивает непротиворечивость хранимых в базе данных, уменьшает трудозатраты на ведение (ввод, корректировку) базы данных.

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

Нормализация отношений информационной модели предметной области является механизмом создания логической модели реляционной базы данных.

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

• группировка атрибутов в отношении предметной области;

• распределение атрибутов по отношениям базы данных.

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

Перечень наиболее важных требований:

1. Первичные ключи отношений должны быть минимальными (требование минимальности первичных ключей).

2. Число отношений базы данных должно по возможности давать наименьшую избыточность данных (требование надежности данных).

3. Число отношений базы данных не должно приводить к потере производительности системы (требование производительности системы).

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

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

6. Разброс времени реакции на различные запросы к базе данных не должен быть большим (требование производительности системы).

7. Данные должны правильно отражать состояние предметной области базы данных в каждый конкретный момент времени (требование актуальности данных).

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

Функциональная и многозначная зависимости

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

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

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

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

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

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

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

​

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

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

​

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

Таблица находится в третьей нормальной форме (3НФ), если она удовлетворяет определению 2НФ и не одно из ее неключевых полей не зависит функционально от любого другого неключевого поля.

​

Нормальная форма Бойса-Кодда (НФБК) (усиленная 3НФ)

Таблица находится в нормальной форме Бойса-Кодда (НФБК), если и только если любая функциональная зависимость между его полями сводится к полной функциональной зависимости от возможного ключа.

​

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

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

Отношение находится в четвертой нормальной форме (4НФ), если оно находится в 3НФ или НФБК и все независимые многозначные ФЗ разнесены в отдельные отношения с одним и тем же ключом. Иными словами, 4НФ применяется при наличии в отношении более чем одной многозначной ФЗ и требует, чтобы отношение не содержало независимых многозначных ФЗ.

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

​

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

Отношение находится в пятой нормальной форме (5НФ), если оно находится в 4НФ и удовлетворяет зависимости по соединению относительно своих проекций. 5НФ называют также нормальной формой с проецированием соединений. Она используется для разрешения трех и более отношений, которые связаны более чем тремя ФЗ по типу "многие-ко-многим".

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

Нормализация как процесс декомпозиции таблиц

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

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

• 1НФ - все атрибуты отношения простые;

• 2НФ - отношение находится в 1НФ и не содержит частичных ФЗ;

• 3НФ - отношение находится во 2НФ и не содержит транзитивных ФЗ от ключа;

• НФБК - отношение находится в 3НФ и не содержит ФЗ ключей от неключевых атрибутов;

• 4НФ, применяется при наличии более чем одной многозначной ФЗ - отношение находится в НФБК или 3НФ и не содержит независимых многозначных ФЗ;

• 5НФ - отношение находится в 4НФ и не содержит ФЗ по соединению.

Обычно применяется нормализация базы данных до НФБК, в случаях достаточно сложных зависимостей между сущностями может быть проведена нормализация до 4НФ и 5НФ.

Нормализация гарантирует целостность данных

Почему нормализация схем отношений важна для проектирования реляционных баз данных? Многочисленные испытания показали, что нормализация схем отношений дает наилучший результат при моделировании предметной области с использованием реляционной модели данных; при этом не вводится большого числа ограничений, не искажаются данные. Таким образом, нормализация отношений является методом удаления из отношения ФЗ, которые приводят к аномалиям модификации данных. Иными словами, нормализация отношений помогает проектировать реляционную базу данных, которая не содержит избыточных данных и гарантирует их целостность.

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

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

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

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

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

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

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

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

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

8. Указать ограничения целостности проектируемой базы данных и дать (если это необходимо) краткое описание полученных таблиц и их полей.

ER-диаграмма (диаграмма «сущность-связь»)

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

Чаще всего модель представляется в виде диаграммы «сущность – связь» (entity – relationship) или ER-диаграммы. Процесс построения ER-диаграммы называется ER-моделированием.

Модель «сущность-связь» предложена американским исследователем в области баз данных Питером Ченом в 1976 году. С тех пор она расширялась и модифицировалась как самим Ченом, так и многими другими исследователями. В различных вариантах она вошла в состав многих автоматизированных средств поддержки проектирования информационных систем.

ER-диаграмма позволяет графически представить все элементы логической модели согласно простым, интуитивно понятным, но строго определенным правилам.

Схема базы данных в виде ER-модели включается в проектную документацию (спецификация, техническое задание и т.п.).

Сущности и атрибуты

Сущность - это класс однотипных объектов, информация о которых должна быть учтена в модели.

Атрибут сущности - это именованная характеристика, являющаяся некоторым свойством сущности.

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

Ключи

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

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

Идентификатор сущности называют первичным ключом (primary key). Первичный ключ (primary key) – это атрибут или группа атрибутов, однозначно идентифицирующая экземпляр сущности. Выбор первичного ключа может оказаться непростой задачей, решение которой в состоянии повлиять на эффективность будущей ИС. В одной сущности могут оказаться несколько атрибутов или наборов атрибутов, претендующих на роль первичного ключа. Такие претенденты называются потенциальными ключами (candidate key). Ключи могут быть сложными, т.е. содержащими несколько атрибутов.

Каждая сущность должна иметь, по крайней мере, один потенциальный ключ. Многие сущности имеют только один потенциальный ключ. Такой ключ становится первичным. Некоторые сущности могут иметь более одного возможного ключа. Тогда один из них становится первичным, а остальные – альтернативными ключами. Альтернативный ключ (Alternate Key) – это потенциальный ключ, не ставший первичным.

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

Домены

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

Отношения (связи)

Отношение (связь) сущностей на ER-диаграмме изображается линией, соединяющей эти сущности. Связь представляет собой взаимоотношение между двумя или более сущностями. Каждая связь реализуется через значения атрибутов сущностей. При создании связи в одной из сущностей, называемой дочерней сущностью, создается новый атрибут, называемый внешним ключом (Foreign Key, FK).

Существуют различные типы связей: идентифицирующая связь (identifying relationship) и неидентифицирующая связь (non-identifying relationship), связи "один ко многим" и "многие ко многим". С типами связей связывают и различные типы сущностей.

Различают два типа сущностей: зависимые (Dependent entity) и независимые (Independent entity). Тип сущности определяется ее связью с другими сущностями. Идентифицирующая связь устанавливается между независимой (родительский конец связи) и зависимой (дочерний конец связи) сущностями.

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

Связь "многие ко многим" (many-to-many relationship) может быть создана только на уровне концептуальной или логической модели. На уровне физической (или уже на уровне логической) модели она должна быть разрешена путем введения промежуточной (ассоциативной) сущности.