Реляционный подход к проектированию: основные концепции, пример.

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

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

Теория нормализации использует свойства реляционных таблиц:

1.Атомарность: на пересечении одной строки и столбца должно стоять неделимое, без потери смысла, значение.

2.Уникальность картежей: не допускается дублирование. (Primary key)

3.Порядок строк и столбцов несущественен

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

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

4) Пусть A и B две совокупности атрибутов отношения R. Говорят, что A->B или, что то же самое, A функционально определяет B, если в любой момент времени каждому набору значений из A соответствует ровно один набор значений из B. Функциональные зависимости бывают разные (частичная, транзитивная, многозначная, зависимость по соединению), но только полная функциональная зависимость не ключевых атрибутов от ключа является желательной функциональной зависимостью и поддерживается нормализованной БД. Все остальные являются избыточными и должны быть устранены в ходе нормализации.

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

Пусть A и B некоторые совокупности атрибутов отношения R. И пусть A->B, и пусть B->C. Говорят, что A транзитивноопределяет C или что то же самое, C транзитивно зависитот A, если B функционально зависит от A, C функционально зависит от B и при этом обратные зависимости отсутствуют.

Пусть A,B,C некоторый набор атрибутов отношения R. Если A → → B и A → → C и при этом они не зависимы между собой, то имеют место многозначные зависимости. Каждому набору из A соответствуют несколько наборов из B.

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

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

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

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

6) Отношение находится во 2NF если оно находится в первой нормальной форме и в нем отсутствуют частичные функциональные зависимости. Но, переходя ко второй форме, всё еще остаются аномалии, так как существуют транзитивные зависимости.

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

Внешний ключ– это ключ, являющийся атрибутом в какой-то другой внешней таблице.

7) Отношение находятся в 3NF если оно находится во второй нормальной форме и в нем отсутствуют транзитивные функциональные зависимости.

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

8) Отношение находится в BCNF если оно находится в 3NF и в нем отсутствуют зависимости атрибутов первичного ключа от не ключевых атрибутов.

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

9) Отношение находится в 4NF если оно находится в BCNF и в нем отсутсвуют многозначные зависимости, не являющиеся функциональными зависимостями. Т.е., если в отношении существует многозначная зависимость между A и B, то все остальные атрибуты уже ф-но зависят от A. Таким образом стремимся к полной ФЗ, достигается это построением проекций.

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

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

11) На практике зачастую достаточно построить схему предметной области до 3NF, при этом для того чтобы отношение находилось в 1NF нужно устранить повторяющиеся группы ненормализованных отношений (универсальных) путем включения в первичный ключ исходного отношения первичного ключа повторяющейся группы как его составной части. Для того чтобы привести к 2NF, нужно выделить в отдельные отношения путем построения проекций те не ключевые атрибуты, которые зависят от некоторой части первичного ключа вместе с этой частью ключа(так устраняются РЗ). В исходном отношении остаются только те не ключевые атрибуты, которые зависят от первичного ключа в целом. Чтобы получить 3NF, нужно выделить в отдельное отношение те не ключевые атрибуты, между которыми есть ФЗ в составе некоторых транзитивных зависимостей. Определить в этой проекции первичный ключ, а в исходном отношении оставить только те не ключевые атрибуты, которые полностью зависят от первичного ключа плюс от «вырезанных атрибутов» остается только первичный ключ нового отношения для связи.

Не забывать про наличие вложенных ФЗ, которые нужно устранять последовательно.

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

1) Возможный набор значений потенциально неизменяем (например, дни недели, месяца)

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

Если условия соблюдены, то такие группы представляются как атрибуты отношения, а не значения.