Автоматизация формирования связей на схеме базы данных Текст научной статьи по специальности «Компьютерные и информационные науки»

ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ

Вестн. Ом. ун-та. 2010. №4. С. 150-155.

УДК 519.72 С. В. Зыкин

Омский филиал Института математики им. С.А. Соболева СО РАН

АВТОМАТИЗАЦИЯ ФОРМИРОВАНИЯ СВЯЗЕЙ НА СХБМБ БАЗЫ ДАННЫХ*

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

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

Введение

Ссылочные ограничения целостности на данные (referential integrity) являются одним из основных видов ограничений в базах данных (БД), которые поддерживаются большинством существующих систем управления базами данных (СУБД). Для этого в СУБД используются связи (relationship), установленные на схеме БД. Значения атрибутов, по которым связаны отношения, заимствуются из главных отношений (справочников) в подчиненные отношения. Между ними устанавливается связь 1:М либо 1:1 по направлению от главного отношения к подчиненному. Формальным основанием для установления связей являются зависимости включения (inclusion dependencies) [1]:

Определение 1. Пусть R$Al, .Am] И Rj[Bl, Bp] - схемы отношений (не обязательно различные), V с {Ai, .Am} и №с {Bi, ВР}, \V\ = \W\, тогда объект i?i[V] с RfiWf\ называется зависимостью включения, если jrvfRi) с 7iw(Rj).

В определении | V\ - мощность множества V, nv(Ri) - проекция отношения Ri по атрибутам V.

Рассмотрим практические аспекты использования зависимостей включения. Будем предполагать, что условие V = W является необходимым для установления связи. Такой вид зависимостей включения называется типизированными (typed) [2; 3]. Это дополнительное ограничение вполне согласуется с общепринятым свойством связей на схеме БД: связи отражают количественное соотнесение кортежей в отношениях и не обладают какой-либо семантикой. Необходимость связывания различных по смыслу атрибутов, скорее всего, является признаком потери какой-либо функциональной зависимости для связываемых атрибутов либо, как в примере 1.1 [3], оставшаяся зависимость включения после удаления избыточных зависимостей установлена для атрибутов-синонимов. То есть решение семантических проблем на предварительном этапе проектирования схемы базы данных в

* Работа выполнена по проекту РФФИ № 09-07-00059-а.

© С.В. Зыкин, 2010

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

Необходимо отметить, что в алгоритмах нормализации отношений [4; 5] независимо от подхода к нормализации (декомпозиционный или синтетический) отсутствуют правила для формирования отношений Яг и Я/, когда Яг и Я/ имеют совпадающие первичные ключи и между ними устанавливается связь 1:1. Рассмотрим пример: А1 - «Табельный номер сотрудника», Ая - «ФИО сотрудника», Аз -«Дата увольнения сотрудника». Существуют функциональные зависимости: А^Ая и А1—>Аз. Области определения этих зависимостей различны: первая зависимость определена для всего множества сотрудников, а вторая только для уволенных. Это и должно явиться основанием для построения декомпозиции: И^А1, А2] и ЩА.1, Аз]. Между Яг и Я/ устанавливается связь 1:1, где Яг - главное отношение, Я, - подчиненное. Заметим, что такая декомпозиция решает проблему заведомо неопределенных значений: в объединенном отношении R\Al, Ая, Аз] значение атрибута Аз будет иметь неопределенное значение для всех не уволенных сотрудников. В различных публикациях можно найти множество аргументов, обосновывающих необходимость использования связи 1:1. Это и удобство при создании приложений, и эффективное использование дискового пространства, и т. д. Поскольку связи являются компонентами схемы БД, то приведенные аргументы нарушают принцип независимости данных. Тогда как понятие области определения характеризует свойства собственно данных, наряду с различными видами зависимостей. Оно не зависит от места хранения и способа использования данных и, следовательно, не нарушает принцип независимости данных.

Ациклические схемы баз данных

Введем обозначения: РЩИг), или просто РЩ), - множество атрибутов, являющихся первичным ключом в отношении Яг, заметим, что в отношении Яг может существовать несколько альтернативных первичных ключей; - связь 1:1 от Яг

к Я/, где Яг - главное отношение; Ьш(1^) -связь 1:М от Яг к Я/, где Яг - главное отношение; - связь 1:1 либо 1:М от Яг к Я/, где Яг - главное отношение.

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

Правило 1. Пусть отношение Яг состоит из множества атрибутов PKfRi), V и W. имеющих друг с другом пустое пересечение. Если функциональные зависимости PK(Ri)->Ai для всех атрибутов Аге V в отношении Яг имеют совпадающие области определения, то отношение Яг декомпозируется на два отношения: RAPK(Ri), V] и Ri[PK(Ri), Щ.

Определение 2. Между отношениями Яг и Rj существует связь Li(iJ), если PK(Rt) = PK(Rj) и для любых реализаций Яг и Rj, выполнено nx(Rj) с ftxfRi), где X = ЯгпЯу.

Определение 3. Между отношениями Яг и Rj существует связь LntfiJ), если PKfRi) Ф PKfRj) и PKfRi) с Rj.

Заметим, что определения 1 и 2 соответствуют частному случаю типизированных зависимостей включения, которые поддерживается СУБД за счет создания внешних ключей (foreign key). Ограничение целостности, задаваемое связью LmfiJ), подразумевает nvfRj) с m(Ri), где V = RtnRj. Далее за каждой связью Llj) неявно закрепим набор атрибутов, на которых она определена: V = ЯгпЯ/, и будем эти атрибуты использовать в алгоритмах.

В схемах БД [6], кроме рассмотренных, используются следующие связи: 1) «one or more mandatory»; 2) «one and only one»; 3) «zero or one optional»; 4) «zero or many optional». Поскольку связи на схеме БД являются парными, то связь 1 является частным случаем связи 1:М со стороны второго отношения, связь 2 является частным случаем связи 1:1 со стороны обоих отношений, связь 3 совпадает со связью 1:1 со стороны второго отношения, связь 4 совпадает со связью 1:М со стороны второго отношения. Другие варианты связей, имеющие тип связи 3 со стороны первого отношения, противоречат определению зависимости включения и не являются ограничением ссылочной целостности данных. Такие связи будем далее игнорировать. Особый случай представляет вариант связи 1:М, который допускает наличие кортежей с неопределенными значениями общих атрибутов во втором отношении, когда они не являются ключевыми. Поскольку неопределенное значение не равно определенному значе-

нию, то этот вариант связи противоречит зависимости включения, но является ограничением целостности. Оставим такую связь в рассмотрении, назвав ее расширенной связью.

Сформулируем условие ацикличности для совокупности отношений по аналогии с условием ацикличности зависимостей включения [7].

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

{Rm( 1), Rm(2], •••, Rm(s)}c9l (1)

такое, что выполнено:

L(m(l),m(2)), L(m(2),m(3)), ...,

L(m(s),m(l)), (2)

s > 1, в противном случае совокупность отношений Ш будем называть циклической. Последовательность (2) может содержать расширенные связи.

В работе «On the Desirability of Acyclic Database Schemes» [7] рассмотрено несколько различных условий ацикличности Ш и доказана их эквивалентность. Одно из условий, рассмотренное также в работе «Теория реляционных баз данных» [4], устанавливает, что совокупность Ш является ациклической, если ацикличен ассоциированный с ней гиперграф. Поскольку предполагается, что в 9J отсутствуют отношения, для которых [Щ £ т при i Ф j, то гиперграф будет редуцированным.

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

Доказательство. Подмножество отношений (1) будет ребрами в ассоциированном гиперграфе. Ребра связывают совокупности атрибутов - узлы гиперграфа. Выполнение условия (2) гарантирует, что Rm(if Г) Rm(i+1) Ф 0, i = 1, 2, S, ГДе

m(s+l) = т(1). Следовательно, подмножество (1) вместе со связываемыми узлами образуют блок [4] в общем гиперграфе. Действительно, этот блок не имеет множества сочленения: удаление любого ребра из блока оставляет его связным. Следовательно, общий гиперграф для SH будет циклическим. Конец доказательства.

Исследование соотношения условий ацикличности в «On the Desirability of Acyclic Database Schemes» [7] с определением 4 продолжим рассмотрением примера 13.15 из работы «Теория реляционных баз данных» [4]: Ш= {Ri, R2, R3}, где

Rl[ABq, Л>[Б£>], 1^3[СП]. Дополним этот пример функциональными зависимостями {ВС-^А, С-»£>А которые могли

быть использованы при построении 91. Ассоциированный с Ш гиперграф является циклическим, тогда как по определению 4 совокупность 91 является ациклической, поскольку для нее существуют только две связи: Ьш(2,1) Ьм(3,1). Таким образом, класс ациклических схем баз данных по определению 4 шире, чем по условиям в [7].

Формирование неизбыточного набора связей

Далее будем предполагать, что совокупность Ш является ациклической в соответствии с определением 4.

Определение 5. Связь является

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

Заметим, что под определение 5 не попадают избыточные связи, являющиеся следствием взаимодействия функциональных зависимостей и зависимостей включения [1; 3], поскольку для типизированных зависимостей включения такое взаимодействие удаляется на этапе проектирования схем отношений БД. По этой же причине между парой отношений может быть установлена только одна связь (другая может быть только циклической). Далее будем рассматривать только такие множества Я.

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

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

т = /Л, К2, Rq}.

• По правилу 1 проектировщиком осуществляется разбиение R^ на отношения R^,m^i), j = 1, 2, иг, и определяются включения яv(R^,mlj)) с яv(R^,mlц). Результирующее множество отношений обозначим Ш = &1, R2, Rk}. При синтетическом подходе к построению Ш области определения функциональных зависимостей можно было учесть на первом этапе.

• По определениям 2 и 3 между сформированными отношениями устанавливаются связи Ьш(1^) и Ьф$. Осуществляется удаление избыточных связей.

• Уточняется местоположение и количество исходящих связей Lm{ij) для совокупности отношений, полученных по правилу 1 из отношения Ri.

Рассмотрим детали реализации и обоснования перечисленных этапов.

Если связи Lm{ij) могут быть установлены простым алгоритмом перебора по определению 3, то для установления связи Li(iJ) необходимо вмешательство проектировщика схемы БД. При пустой БД на этапе проектирования включение Jiv(Rj) с JivfRi), где V - первичный ключ отношений Ri и Rj, не может быть установлено алгоритмически. При определении включения между Ri и Rj проектировщику БД достаточно ответить на вопрос: «Как соотносятся области значений атрибутов Rin Rj в отношениях Ri и Rj?», предоставив выбор одного из трех вариантов: «1. Значения в Ri являются подмножеством значений в Rj по множеству атрибутов V», «2. Значения в Rj являются подмножеством значений в Ri по множеству атрибутов V», «3. Не выполнены условия 1-го и 2-го вариантов». При выборе первого варианта отношение Rj становится главным для Ri, второго - Ri становится главным для Rj, третьего - отсутствие связи между Ri и Rj. Допустим, что этот выбор реализуется функцией Ch(Ri,Rj,V), имеющая значения LifiJ), Li(j,i) и 0.

В соответствии с определениями 2 и 3 алгоритм формирования связей для множества SR, где L - множество всех связей, может быть записан в следующем виде.

L = 0 do i = 1 to k for each PK(R) in Ri do j = i +1 to k for each PK(Rj) in Rj if PK(R) = PK(Rj) then V=PK(R)

if с thenL=L\j Л,(i,j)

if fty(Rj) с &v(Rj) then L = Lvj L](j,i) if 7Ci {R) = Tt\ (R) then L = Lvj Ch(RnRn V) else

if PK(Rj) с Rj then L=Lvj LM(i,j) if PK(Rj) с Ri then L = Lvj LM(j,i) endif endfor enddo endfor enddo

Сложность данного алгоритма является полиномиальной по времени: 0(к2).

В работе «The Implication Problem for Inclusion Dependencies» [2] без доказательства сформулировано условие избыточной зависимости включения. Докажем аналогичное условие для связей, в том числе и расширенных, опираясь на реализации отношений, а не на выводимость.

Теорема 2. Связь Llj) является избыточной, если существуют связи:

L(i,m(l)), L(m(l),m(2)), ..., L(m(p),j) (3)

и

PK(i) с Rm(s), S = 2,3,...,p, (4)

где m - массив номеров отношений.

Доказательство. Заметим, что условия PK(i) С Rj И PK(i) С Rm(l) являются следствием наличия связей Llj) и L(l,m(1)) соответственно.

1. Пусть выполнены условия (3) и (4). Поскольку множество атрибутов РЩг) присутствует в каждом отношении цепочки:

Ri, Rm(1), Rm(2), •••, Rmp), Rj, (5)

то по определению оно участвует во всех связях последовательности (3), следовательно, m>K(i)(Ri) э npK(i)(Rmfi)) э ... 2

Э m>K(i)(Rm(p)) з TTpK(i)(Rj). Данная последовательность включений гарантирует, что в Rj не появятся значения PK(l), не содержащиеся в других отношениях цепочки

(5). Это является более сильным ограничением, чем задает связь L(lj), и следовательно, она избыточна. Пусть связь L(m{p)J) в последовательности (3) является расширенной, а связь Llj) не является расширенной. Другие связи в последовательности (3) не могут быть расширенными, так как общие атрибуты во вторых отношениях должны быть ключевыми. В отношении Rj не появится кортеж с пустыми значениями всех атрибутов PK(i), поскольку данное ограничение устанавливается внутри Rj. Поэтому связь L(iJ) может быть удалена.

2. Предположим, что в цепочке (5) имеется отношение Rm(s), которое не содержит полностью множество атрибутов PK(l). Тогда при переходе ОТ Rmjs-ll К Rm(s) снимаются ограничения на атрибуты PK(i)-[Rm{s], обусловленные последовательностью связей (3). Следовательно, эти атрибуты могут принимать произвольные значения, в том числе и не принадлежащие проекции npK(i)(Ri). Поэтому последо-

вательность (3) не является основанием для удаления связи цш

3. Если выполнено условие (4), но отсутствует последовательность (3), то и ограничения на PK(i) в Rj, обусловленные этой последовательностью, отсутствуют. Конец доказательства.

Следствие. Если существует (3) и р = 1, то связь L(iJ) избыточна.

Замечание. Последовательность нескольких связей не может быть избыточна. Допустим, что существуют последовательности (3) и L(i,v), L(vJ), v Ф tn(s), s = l,2,...,p. Удаление связей L(l,v) И L(vj) независимо от условия (4) означало бы снятие ограничений с отношения Rv, накладываемых связью L(l,v).

Поиск избыточных связей с использованием соотношений (3) и (4) будет экспоненциальной задачей. Поскольку связи являются реализацией типизированных зависимостей включения, то к поиску избыточных связей применим алгоритм построения замыкания отношений [2], являющийся полиномиальным по времени. Рассмотрим адаптацию этого алгоритма для удаления избыточных связей без использования графа зависимостей включения. Пусть PK(Ri) - ключ отношения Ri, соответствующий связи цш for each L(i,j) in L l = 1 m(l) = i

iterations = true do while iterations for each L(v,w) in L where L(i,j) Ф L(v,w) substitution = false

if v e ml\ and РЩЩ c: Rw then if w = j then L = L - L(ij) exit do else

if w g m\ 1, I\ then

1 = 1 + 1 m(l) = w

substitution = true endif endif endif endfor

if not substitution then iterations = false enddo endfor

Пусть \L\ - количество связей в исходном множестве L. С учетом того, что

на каждой итерации (цикл do while) к массиву т присоединяется не менее одного номера отношения (их к штук), рассмотренный алгоритм будет иметь полиномиальную сложность: O(\L\2k2). Дополнительная степень при к обусловлена выполнением операций: v е т[1, ..., 2] и

w £ т[1, ..., 2].

Рассмотрим необходимость выполнения четвертого этапа. Очевидно, что применение правила 1 не приведет к появлению циклических связей, если этих связей не было изначально. После удаления избыточных связей группа отношений, сформированных по правилу 1 из отношения Ri, в общем случае распадется на связанные последовательности Rm(i), Rmf2), • • •, Rmfp), где Li(m(l),m(2)), ...,

Li(m(p-l),m(p)) - оставшиеся связи. Эти последовательности могут иметь общие узлы (отношения). Если исходное отношение Ri в соответствии с определением 3 имело связь LuifiJ), то связи Lm(m(l)J), I = 1, ... р, также будут иметь место. Алгоритм удаления избыточных связей оставит исходящие связи Lm(mfp)J), т. е. исходящие связи из последних отношений последовательностей. Однако в исходном отношении Ri для неключевых атрибутов, помещенных после применения правила 1 В отношения Rm(2), Rm(3), ..., Rm(ph НвЯВНО предполагалось расширение их доменов неопределенным значением. После декомпозиции области возможных значений первичного ключа в этих отношениях могут существенно сократиться, и исходящие связи Lim(m(p)J) будут задавать более жесткие ограничения, чем отношение Ri. То есть сократится область определения связи. Следовательно, связи необходимо «передвинуть» к тем отношениям последовательностей, которые соответствуют реальным ограничениям. Количество таких связей регламентирует следующее очевидное свойство, являющееся следствием теоремы 2.

Свойство 1. Последовательность отношений Rmfl), Rm(2), ..., Rm(ph МвЖДУ КОТОРЫМИ установлены связи Li(m(1),m(2)), ..., Li(m(p- l),tn(p)), и произвольное отношение Rj могут иметь не более одной связи Lm(m(l)J).

Таким образом, связей, установленных из группы отношений полученных из Rl по правилу 1, может быть не больше, чем соответствующих последовательностей Rmfl), Rm(2), ..., Rmfp). ДоПОЛНИТвЛЬНаЯ

проблема заключается в том, что все эти связи в совокупности задают конъюнктивное ограничение на значения атрибутов РЩЪ) в отношении Л]. Однако может потребоваться дизъюнктивное ограничение для какой-либо группы связей. В базах данных не используются связи, устанавливаемые для трех и более отношений и позволяющие задавать ограничения в виде нормальных форм ДНФ или КНФ. Схема БД в этом случае была бы гиперграфом (не надо путать с гиперграфом в работах [4; 7], поскольку связей там вообще нет). В нашем случае для упомянутой группы связей придется создать фиктивное отношение с атрибутами РЩЛг), которое объединит эти связи и будет встроено во все соответствующие этим связям последовательности. Заметим, что, пользуясь определением выводимости зависимостей включения без учета их области определения, невозможно получить аналогичное преобразование.

Заключение

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

конкретных БД. Если же проектировщик дополнит связи на схеме БД, не удовлетворяющие определениям 2 и 3, то тогда ему будет необходимо перейти к построению нормальной формы IDNF [3]. Если же проектировщик захочет усилить ограничения, используя связи [6], то для этого будет достаточно подправить (усилить) свойства существующих связей.

ЛИТЕРАТУРА

[1] Casanova M., Fagin R., Papadimitriou C. Inclusion Dependencies and Their Interaction with Functional Dependencies // Journal of Computer and System Sciences. 1984. № 28(1). P. 29-59.

[2] Missaoui R., Godin R. The Implication Problem for Inclusion Dependencies: A Graph Approach // SIGMOD Record. 1990. V. 19. № 1. P. 36-40.

[3] Levene M., Vincent M.W. Justification for Inclusion Dependency Normal Form // IEEE Transactions on Knowledge and Data Engineering. 2000. V. 12. № 2. P. 281-291.

[4] Мейер Д. Теория реляционных баз данных. М. : Мир, 1987. 608 с.

[5] Ульман Дж. Основы систем баз данных. М. : Финансы и статистика, 1983. 334 с.

[6] Garmany J., Walker J., Clark T. Logical Database Design Principles. New York: Auerbach Publications, 2005. 180 p.

[7] Beeri C., Fagin R., Maier D., Yannakakis M. On the Desirability of Acyclic Database Schemes // ACM. 1983. V. 30. № 3. P. 479-513.