Реализация ограничений целостности реляционной базы данных

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

Поддержка целостности реляционной БД рассматривается в 3—х аспектах.

1 Целостность таблицы. Обязательно должны поддерживаться:

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

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

Эти ограничения реализуются в командах создания и модификации таблиц. Например, в языке SQL это команды Create Table, Alter Table. В этих командах для описания полей — первичных ключей используется конструкция Primary Key, для описания полей – уникальных ключей конструкция Unique, обязательность значений полей задается конструкцией Not Null.

2 Ссылочная целостность. Каждая таблица проектируемой БД должна быть связана с другими посредством соответствующих первичных и внешних ключей, т.е. быть либо родительской (главной) по отношению к другим таблицам, либо дочерней (подчиненной), либо той и другой для разного уровня связей. Назначение внешнего ключа — связывать каждую строку дочерней таблицы с соответствующей строкой родительской таблицы. Значение внешнего ключа может иметь и пустое значение (Null), если он реализует необязательную связь, выявленную в предметной области. В качестве значения внешнего ключа может выступать значение и любого уникального (потенциального) ключа. Чтобы в физическом проекте реализовать поддержку ссылочной целостности, необходимо знать ситуации, когда она может быть нарушена:

1) вставка новой строки в дочернюю таблицу. В этом случае значение атрибута внешнего ключа новой строки должно соответствовать конкретному значению, присутствующему в одной из строк родительской таблицы, либо должно быть равно пустому значению (Null). В противном случае целостность будет нарушена;

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

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

а) No Action – удаление строки из родительской таблицы запрещено, если в дочерней таблице есть хотя бы одна ссылающаяся на неё строка;

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

в) Set Null – при удалении строки из родительской таблицы во всех ссылающихся на неё строках дочерней таблицы в атрибутах внешнего ключа записывается пустое значение (Null);

г) Set Default – при удалении строки родительской таблицы значение атрибутов внешнего ключа ссылающейся на неё строки дочерней таблицы автоматически замещаются значениями по умолчанию, определенными при создании дочерней таблицы;

д) No Check – при удалении строки из родительской таблицы никаких действий по сохранению ссылочной целостности не предпринимается;

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

Связи между таблицами (ссылочная целостность) могут быть заданы либо путем явного описания внешних ключей в структурах таблиц (что является более предпочтительным, как и любое другое явное описание), либо ссылочная целостность может поддерживаться с помощью триггеров. Например, в среде СУБД InterBase связь между двумя таблицами можно определить в команде Create Table при помощи конструкции Foreign Key, задающей явно поле – внешний ключ, ссылающийся на соответствующее поле — первичный ключ (конструкция References). В этом случае СУБД InterBase запрещает изменять значение первичного ключа, если на нее ссылаются какая—либо строка из дочерней таблицы и удалять запись в родительской таблице, если на неё есть ссылающаяся запись из дочерней таблицы. Таким образом связь, описанная в команде Create Table, блокирует каскадные изменения и удаления в родительской и дочерней таблицах. По умолчанию СУБД InterBase использует стратегию No Action.

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

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

3 Декларативные ограничения данных. Так называют ограничения реляционной базы данных, объявленные предметной областью и выявленные в ходе её анализа. Задача проектировщика БД — адекватно отобразить их в БД.

Самые распространенные ограничения предметной области – это ограничения на свойства объекта предметной области, далее атрибута отношения или поля таблицы:

— обязательность значения поля;

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

Такие ограничения рекомендуется задавать на уровне домена в командах Create Domain, Alter Domain. Также они могут быть заданы в командах создания и модификации таблиц — Create Table, Alter Table при описании поля таблицы.

Кроме ограничений предметной области, которые могут быть явно отображены на всех этапах проектирования БД на уровне моделей данных (например, использование семантических возможностей ER—диаграммы), существует ряд ограничений, которые выявлены, но не отражены в моделях данных, а описаны на естественном языке проектировщика. Например, это такие ограничения:

— если статус человека «не состоит в браке», то его следующим статусом может быть «в браке», но не в коем случае «разведен (а)»;

— если статус студента был «в академическом отпуске», то следующий статус может быть только «вышел из академического отпуска», но не в коем случае «отчислен»;

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

— общее количество ставок, занимаемых работающим человеком, не может превышать 1,5 единиц;

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

Такие ограничения называют ограничениями перехода. Ранее рассмотренные ограничения можно отнести к ограничениям состояния.

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

Проектирование индексов

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

Индексы необходимы, если:

— часто производится поиск в БД по определенному полю (полям) таблицы;

— часто выполняется операция объединения таблиц по значению поля (полям);

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

Не рекомендуется строить индексы по полям или группам полей, если:

— поля редко используются для выполнения операций поиска, объединения, сортировки;

— значения полей часто меняют свои значения;

— значения полей содержат небольшое число вариантных значений;

— значения полей имеют большие размеры;

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

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

Различают следующие типы индексов:

— простые, строятся на основе только одного поля таблицы;

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

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

При создании индексов следует поддерживать следующие рекомендации:

— нельзя создавать для таблицы несколько индексов, содержащих одинаковые, но расположенные в другом порядке поля, в случае необходимости индексы следует сокращать. Исключение составляют таблицы, разбивающие связь "многие_ко_многим";

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

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

Создание индекса может быть реализовано командой Create Index.

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

Для оптимального использования индексов в конкретной СУБД необходимо пользоваться соответствующей технической документацией.

Создание БД

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

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

— количество выполненных операций в БД (добавление, обновление, удаление, чтение строк таблиц) за заданный интервал времени;

— время ответа – временной промежуток, необходимый для выполнения определенного количества операций в БД;

— объем дискового пространства, необходимого для размещения файлов БД.

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

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

Подготовка среды хранения

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

1 Процессор. Выполняет пользовательские процессы, управляет задачами других системных ресурсов. У него должна быть высокая тактовая частота. Для организации сервера БД может быть использовано несколько процессоров одновременно.

2 Оперативная память. Чем больше её объем, тем быстрее работают приложения баз данных. Рекомендуют планировать объем, чтобы в процессе работы системы выполнялось условие: всегда есть в наличии свободными 5 процентов от всего объема памяти.

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

— файлы операционной системы должны быть отделены (располагаться физически на разных носителях) от файлов базы данных;

— основные файлы БД должны быть отделены от индексных файлов;

— журнал восстановления должен быть отделен от остальной части БД.

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

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

1 Определение функциональных характеристик транзакций, которые будут выполняться в базе данных.

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

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

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

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

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

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

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

Генерация схемы БД

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

База данных может быть создана различными способами – визуально в режиме диалога с СУБД, выполнением соответствующих программных кодов (SQL—скриптов), автоматической генерацией, на основе построенной в инструментальной среде CASE — средства модели предметной области.

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

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