CC BY
15
УДК 004.652.4
В. С. ЗЫКИН
Омский государственный технический университет, г. Омск
ИНСТРУМЕНТАЛЬНАЯ СРЕДА ФОРМИРОВАНИЯ ВНЕШНИХ КЛЮЧЕЙ НА СХЕМЕ РЕЛЯЦИОННОЙ БАЗЫ ДАННЫХ_
В ра боте рассматривается оригинальное программное обеспечение для автоматизации построения неизбыточного множества ссылочных ограничений на данные (внешних ключей). Эти ограничения позволяют регламентировать бизнес-правила в использовании информации н а предприятии, которая хранится в реляционной ба зе данных и обслуживается системой управления базами данных. Разработанное программное обеспечение автоматически ищет возможные варианты ссылочных ограничений, оставляя за пользователем право принятия или отклонения этих ограничений. В автоматическом режиме определяются и удаляются избыточные ссылочные ограничения целостности. Ключевые слова: базы данных, ссылочные ог раничения целостности, внешние ключи. Работа выполнена при финансовой поддержке РФФИ, проект № 15-41-04436-р_сибирь_а.
Данные, хранимые в базе данных (БД), должны удовлетворять правилам, которые приняты за основу информационной политики компании [1]. Ограничения целостности на данные являются основой реализации такой политики. Кроме того, они оказывают существенное влияние на процесс интеграции схем нескольких БД при слиянии компаний или расширении сферы их деятельности [2].
Ссылочные ограничения целостности на данные (referential integrity) являются одним из основных видов ограничений в БД, которые позволяют сохранить структурную ее целостность. В большинстве существующих систем управления базами данных (СУБД) поддерживается такой вид ограничений и задаются эти ограничения в виде связей (relationship) на схеме БД. При проектировании схемы БД ссылочные ограничения задаются проектировщиком «вручную» по мере формирования очередных отношений (relation) БД. Однако в настоящее время стали широко использоваться средства автоматизации проектирования схем БД. При этом связи между автоматически сформированными отношениями на схеме БД по-прежнему необходимо формировать либо корректировать вручную. Следствием сказанного является то, что проблема автоматизации построения корректного и неизбыточного набора ссылочных ограничений целостности является актуальной.
Формирование схемы БД [3, 4] начинается с исследования зависимостей: функциональных, многозначных, соединения и включения. Первые три вида зависимостей используются для проектирования сущностей (отношений) БД, что позволяет реализовать принцип независимости данных и, как следствие, устойчивость проекта БД при последующей модернизации. Последний вид зависимостей (включения) являются теоретической основой для ссылочных ограничений целостности.
Путь U={A1, A2,..., An} — множество атрибутов, определенных в БД, [R] — множество атрибутов на которых определено отношение R, [RJcU, R = (R1,
R2.....Rk) — БД, 5= {[RJ, [ R 2]..... [ RJ} — схема БД, на
которой пока еще не определены ссылочные ограничения целостности. Значения атрибутов, по которым связаны отношения, заимствуются из главных отношений (справочников) в подчиненные отношения. Между ними устанавливается связь 1:М либо 1:1 по направлению от главного отношения к подчиненному. Рассмотрим формальное определение зависимостей включения (inclusion dependencies) [5]:
Определение 1. Пусть [RJ и R] — схемы отношений (не обязательно различные), VcRJ и Wc[R], |V| = |W|, тогда соотношение Ri[V]iRj[W] называется зависимостью включения.
В определении 1 |V| — мощность множества V, Ri[V]=PV(Ri) — проекция отношения Rj по атрибутам V. Далее будем предполагать, что условие V= W является необходимым для установления связи. Такие зависимости включения называются типизированными (typed) [6, 7].
Обозначим: PK(R) или просто PK(i) — первичный ключ отношения R; L(i, j, X) — связь 1:1 либо 1:M от Ri к RjT установленная по множеству атрибутов X, где R( главное отношение, а R. подчиненное отношение; L1(i, j, X) — связь 1:1 от R( к R; LM(i ,j, X) — связь 1:M от R( к Rj. В отношении R( может существовать множество альтернативных первичных ключей и соответствующих им связей, в которых Rj будет главным или подчиненным.
Определение 2. Между отношениями R( и R. допустима связь L1(i, j, X), если X = PK(R) = PK(R) и для любых реализаций Rt и R, выполнено PX(Rj.)cPX(Ri).
Определение 3. Между отношениями Rj и Rj допустима связь LM(i, j, X), если PK(R)^PK(R) и PK(Ri)c[Rj].
В определении 3 отношение Rj может содержать неопределенные значения для атрибутов, не принадлежащих первичному ключу. Заметим, что определения 2 и 3 соответствуют типизированным зависимостям включения. Ограничение целостности, задаваемое связью LM(i,j,X), не подразумевает выполнение
условия KX(R])iKX(Ri), где X=[RJn[Rj], поскольку атрибуты X, не принадлежащие PK(R), могут принимать неопределенные значения, тогда как в Rj им соответствуют определенные значения. Смысл ограничения в том, что неопределенное значение какого-либо атрибута в Rj может быть заменено только тем определенным значением, которое есть в R.. Поиск связей, соответствующих определениям 2 и 3, достаточно просто алгоритмизуется, что позволит выявить большинство ссылочных ограничений целостности в автоматическом режиме.
В работе [8] для корректного решения рассматриваемой проблемы вводится и исследуется понятие ациклических схем БД. Введена интерпретация ациклических схем БД в виде ассоциированных гиперграфов, доказана теорема о цикличности такого гиперграфа. Представлен алгоритм автоматического построения множества всевозможных ссылочных ограничений и далее предложен алгоритм автоматического удаления избыточных ссылочных ограничений целостности. В данной работе рассмотрим практическую реализацию этих алгоритмов в среде MS Access.
Перед началом работы в текущую БД Access необходимо загрузить две формы «Создание_связей» и «Удаление_Связей». Форма «Удаление_Связей» является вспомогательной и предназначена для удаления всех установленных связей между отношениями в текущей БД. Форма «Создание_связей» является основной и предназначена для формирования неизбыточного и актуального набора связей между отношениями текущей БД.
Внешний вид формы «Создание_связей» представлен на рис. 1. В настройках внешнего вида формы удалено стандартное поле перехода по записям, поскольку форма предназначена не для работы с конкретным отношением, а для работы со всеми отношениями БД одновременно, и конкретные записи отношений в форме не анализируются.
После загрузки формы «Создание_связей» необходимо выбрать все входные отношения, на которых уже должны быть заданы первичные и альтернативные ключи. Для этого на форме присутствует элемент управления «Поле со списком». При открытии этого поля появляется список, состоящий из множества отношений исходной БД. Далее должны быть выбраны все отношения, между которыми необходимо построить ссылочные ограничения целостности (связи); каждый раз выбирается только одно отношение из списка.
После того как выбраны все необходимые отношения для составления связей, необходимо нажать элемент управления «Кнопка». На рис. 1 данная кнопка имеет подпись «Закончить выбор таблиц и сформировать связи».
При добавлении отношений в поле для дальнейшего формирования связей возможно возникновение неопределенностей. Эти неопределенности возникают в связи с тем, что подчиненное отношение не может существовать без главного, например, отношение «Расписание» не может существовать без определяющих его отношений «Предметы», «Преподаватели», «Список групп», «Неделя», «Начало занятий». В программе реализованы определения необходимости построения текущей связи, указывающие, какие отношения необходимо добавить в рассмотрение для ее корректного выполнения. Если очередная связь не соответствует семантике системы, то следует нажать кнопку «Отмена» (рис. 2), после чего связь между двумя указанными отношениями не
Рис. 1. Форма «Создание связей»
Рис. 2. Предупреждение о необходимости создания связей
будет устанавливаться. Эта функция необходима для создания ацикличных схем БД, а также для недопущения создания связей, не предусмотренных семантикой в прикладной области. Если для всех неглавных отношений на вход программы поданы их главные отношения, то программа корректно выполнит операцию создания неизбыточного набора связей, в противном случае выходом программ будет БД без связей. На рис. 2 показан пример определения семантики связи между двумя отношениями, также это сообщение напоминает о невозможности существования одного отношения без другого.
Другая форма «Удаление_связей» является вспомогательной и служит только для удаления построенных связей на схеме БД. Она становится необходима в тех случаях, когда приходится тестировать написанные алгоритмы формирования неизбыточного набора связей, а также перед применением разработанных алгоритмов над готовыми схемами БД, где связи были уже построены с ошибками, неудовлетворяющими ссылочным ограничениям целостности. Форма «Удаление_связей» содержит только один элемент управления «Кнопка», при нажатии которой в текущей БД удаляются все связи. Аналогично форме «Создание_связей» в текущей форме удалено стандартное поле перехода по записям.
Рассмотрим основные принципы работы программного обеспечения, что необходимо знать при организации взаимодействия с другими приложениями. При открытии формы «Создание_связей» происходит инициализация глобальных переменных: dbs, strt-ab, strrel, str_tab. Переменная dbs имеет тип Database и соответствует текущей открытой БД, над которой применяются алгоритмы создания связей, в ней хранятся все отношения с их первичными
ключами, а также все установленные связи. Остальные три переменные имеют одинаковый тип String, в них хранятся строки, содержащие служебную информацию. Переменная strtab служит для хранения названий отношений в элементе управления «Поле со списком», где присутствуют названия всех отношений для рассматриваемой БД. Значения переменной strrel соответствуют всем связям, построенным в данной БД, и имеют следующую структуру: имя подчиненного отношения, далее, после запятой, указывается имя главного отношения, и далее, через запятую, указываются имена атрибутов, участвующих в данной связи. Переменная str_tab служит для хранения имен отношений, на которые будут накладываться связи, этот список можно будет увидеть в элементе управления «Поле».
Процедура Form_Open соответствует событию открытия формы, в представленном случае это есть форма «Создание_связей», в нее можно передавать значения целочисленных переменных для выявления кода ошибки при выполнении этой процедуры. Вызов Form_Open главным образом служит для создания списка имен отношений в элементе управления «Поле со списком». В теле данной процедуры объявляется 2 переменные: fld и tbf. Переменная fld имеет тип Control и используется для хранения данных, находящихся в элементе управления «Поле со списком». Переменная tbf имеет тип TableDef и служит для хранения одного отношения и используется для перебора всех отношений в исходной БД. При выполнении рассматриваемой процедуры переменной dbs присваивается значение текущей БД. Затем в цикле формируется список имен отношений, который помещается в текстовую переменную strtab, далее задается значение fld как элемента управления поля со списком и задается его свойство RowSource как значение переменной strtab.
После выбора очередного отношения из списка отношений необходимо добавить его имя в элемент управления «Поле» и включить в рассмотрение для дальнейшего создания связей на отношениях. Для этого и выполняется процедура SpisokTablic_After-Update. Внутри данной процедуры объявляется две переменные txt и tab 1. Переменная txt имеет тип ComboBox и используется для извлечения текущего имени отношения. Переменная tabl имеет тип String и используется для сохранения полученного имени отношения, которое необходимо добавить к существующему списку отношений.
В процедуре SpisokTablic_AfterUpdate используется следующая последовательность операций: в переменную txt записывается значение, отображаемое на элементе управления «Поле со списком», после чего оно преобразуется в текстовую переменную, избавленную от пустых символов, и дописывается в глобально объявленную переменную str_tab. В окончании процедуры осуществляется перенос значения переменной str_tab в элемент управления «Поле» для дальнейшей работы со списком отношений.
Процедура Связи_СНск является основной в разрабатываемой программе. Из нее вызываются все вспомогательные функции и процедуры, служащие для создания связей. Внутри процедуры кроме служебных переменных объявляются переменные pdi и pdj текстового типа String и используются для хранения текущих i-го и j-го отношений, между которыми и проверяется необходимость установки одной из возможных связей. Кроме того, в данной процедуре используется текстовое значение msgs, которое служит для формирования текстового сообщения
о необходимости создания той или иной связи, в нее поступают значения тех отношений, которые не могут существовать по отдельности. Еще одна переменная, используемая в данной процедуре, — fldp, которая имеет тип Field и используется для определения атрибутов каждого отношения.
Как уже было ранее упомянуто, процедура Связи_СНск является основной при создании связей на схеме БД. В теле данной функции присутствует многомерный цикл, основная цель которого просмотреть зависимости включения между каждой парой отношений. В процессе выполнения цикла осуществляется записать в переменную strrel значения всех связей, присутствующих на входной схеме БД. Каждый раз при этом выдается сообщение о необходимости создания той или иной связи вместе с напоминанием о невозможности существования одного отношения без другого. После выполнения цикла из функции Связи_СНск вызываются две последующие процедуры min_rel и gen_rel, служащие для формирования неизбыточного набора связей и для непосредственной записи в БД.
После создания полного набора связей на схеме БД необходимо избавиться от избыточных связей, наличие которых необязательно, но возможно. Для реализации этой цели реализована функция min_rel, которая вызывается после проверки всех пар отношений на выполнение условия включения. В теле процедуры min_rel реализован алгоритм построения замыкания множества отношений для каждой пары отношений, связанных условием ссылочной целостности [8]. Для текущей пары отношений в переменной типа String формируется список отношений, попадающих в замыкание главного отношения рассматриваемой пары в соответствии с условием ссылочной целостности. При этом условие ссылочной целостности для текущей пары не учитывается и будет считаться избыточным, если подчиненное отношение текущей пары попадет в замыкание. Логическая переменная pod, имеющая тип Boolean, используется для определения необходимости дальнейшего построения замыкания при добавлении очередного отношения в замыкание.
При реализации построения связей на схемах БД так же используются вспомогательные функции. Они используются только с целью получения удобства при создании программного комплекса.
Из рассмотрения предыдущего материала следует, что в основе работы программного обеспечения лежит обработка символьных строк различной структуры и содержания. Стандартных функций VBA для этой цели недостаточно. Поэтому была разработана функция str_tek, которая служит для последовательной выборки необходимых подстрок в текущей строке.
Еще одна вспомогательная функция — sub_set, которая возвращает одно логическое значение. На вход функции sub_set поступает два аргумента текстового типа. Функция выдает истинное значение в случае, когда второй аргумент является подстрокой первого аргумента, и, соответственно, функция возвращает ложное значение, если второй входной аргумент не присутствует в первом. В структуре функции sub_set присутствует бесконечный цикл, из которого программа выходит при условии конца главной строки или при условии конца вспомогательной строки. Отличие рассмотренных процедур от стандартных функций VBA в учете структуры обрабатываемых строк.
Кроме перечисленных универсальных программ, которые без труда могут быть переписаны для ра-
Рис. 3. Результирующая схема БД
боты с другой СУБД, реализован ряд функций, использующих специфические возможности Access. Такие функции при замене СУБД должны быть полностью переработаны под новые возможности.
Функция str_key разработана с целью определения значения первичного ключа в заданном отношении, следовательно, необходимо задать аргументом для данной функции имя отношения, в котором будет выполнен поиск первичного ключа. Входной аргумент выглядит в виде строковой переменной с именем отношения, на выходе — строка из списка атрибутов первичного ключа отношения. Тело функции состоит из тройного цикла, первый из которых проходит по всем таблицам всей БД и ищет таблицу, заданную аргументом рассматриваемой функции str_key. Второй цикл проходится по всем атрибутам текущей таблицы, третий — по каждому символу атрибута. В теле данного тройного цикла в выходную переменную посимвольно записываются значения ключевых атрибутов.
После работы алгоритма построения неизбыточного набора связей была получена общая схема БД, со всеми ее компонентами, представленными в виде строковых переменных strtab — список отношений и strrel — список ссылочных ограничений целостности с их атрибутами. Данный метод представления структуры БД в виде строковых переменных используется из-за большой трудоемкости работы с БД напрямую с использованием запросов и, кроме того, существенно облегчает перенос программного обеспечения на другую платформу. Процедура gen_rel под управлением информации из указанных строк формирует фактические связи между отношениями текущей БД, используя при этом методы встроенного языка программирования VBA для вызова SQL-команды! ALTER TABLE.
Результат работы программного обеспечения показан на рис. 3. В общей сложности для представленной БД было установлено 12 связей, соответству-
ющих ссылочным ограничениям целостности. В результате работы процедуры Связи_СИск было выявлено, что связь между отношениями «Список групп» и «Оценки» является избыточной. Эта связь автоматически была удалена из схемы БД.
Библиографический список
1. Gоmez-Lоpez M. T., Gasca R. M., Pеrez-Alvarez J. M. Com-pliance validation and diagnosis of business data constraints in business processes // Information Systems. 2015. Vol. 48. P. 26 — 43.
2. Visser J. Coupled Transformation of Schemas, Documents, Queries, and Constraints // Electronic Notes in Theoretical Computer Science. 2008. Vol. 200. № 3. P. 3-23.
3. Мейер, Д. Теория реляционных баз данных: моногр. М.: Мир, 1987. 608 с.
4. Ульман, Дж. Основы систем баз данных / пер. с англ. М. Когаловского, В. Когутовского; под ред. М. Когалов-ского. М.: Финансы и статистика, 1983. 334 с.
5. 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.
6. Missaoui R., Godin R. The Implication Problem for Inclusion Dependencies: A Graph Approach // SIGMOD Record. 1990. Vol. 19. № 1. P. 36-40.
7. Levene M., Vincent M. W. Justification for Inclusion Dependency Normal Form // IEEE Transactions on Knowledge and Data Engineering. 2000. Vol. 12. № 2. P. 281-291.
8. Зыкин, В. С. Ссылочная целостность данных в корпоративных информационных системах // Информатика и ее применение. 2015. Т. 9. № 3. С. 119-127.
ЗЫКИН Владимир Сергеевич, аспирант, старший преподаватель кафедры прикладной математики и фундаментальной информатики. Адрес для переписки: vszykin@mail.ru
Статья поступила в редакцию 09.01.2017 г. © В. С. Зыкин
