# Лекция 9. Проектирование баз данных

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

Основные понятия реляционной модели:

База данных - долговременное самодокументированное хранилище данных. Самодокументация = схема данных, хранящаяся в БД.

Система управления базами данных (СУБД) - ПО доступа к данным. Обеспечивает:

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

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

Оператор SQL - предложение SQL для манипуляции данными (выборка, изменение, добавление, удаление).

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

Виды ограничений:

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

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

Внешний ключ - ссылка из другой таблицы на возможный ключ.

Пример:

Первичные ключи: persID в 1-ой таблице, compID - во 2-ой. Внешний ключ - столбец employer 1-ой таблицы.

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

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

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

Реляционная схема данных и объектная модель оперируют разными понятиями, из‑за чего необходима специальная работа по объектно-реляционному отображению. Отображение возможно в обе стороны: в прямую (от классов к таблицам) и в обратную (от таблиц к классам). В лекции мы будем говорить о прямом отображении, но обратное отображение подразумевается.

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

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

Отображение классов

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

Пример:

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

Отображение бинарных ассоциаций:

- «1 к 1му» - возможны различные решения, лучше создать общую таблицу для 2х классов. Столбцы - совокупность атрибутов. Первичный ключ - любой ID (первого или второго класса). Достигается максимально возможная экономия: одна таблица представляет оба класса и ассоциацию между ними.
- «1 к 0..1» - внешний ключ добавляется к таблице необязательного класса.

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

«0..1 к 0..1» - рекомендуется отдельная таблица для связи. Ее столбцы - внешние ключи для таблиц классов, связанных ассоциацией. Основной ключ - комбинация этих столбцов.

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

«* к *» - для ассоциации заводится отдельная таблица. Ее столбцы - внешние ключи для таблиц классов, связанных ассоциацией. Основной ключ - комбинация этих столбцов.

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

- «1 к 1..*» - к таблице класса у полюса «1..*» добавляется столбец - внешний ключ для таблицы класса у полюса «один».
- «1 к 0..*» - как «1 к 1..*».

Пример:

«0..1 к *» - применяются те же решения, что и в «0..1 к 0..1».

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

Отображение классов связанных N-арной ассоциацией: Требуется таблица для хранения связи. Например, в случае тернарной (N=3) связи формируются четыре таблицы, по одной для каждого класса и одна для связи. Таблица связи будет иметь среди своих атрибутов ключи каждой из 3х других таблиц, все её столбцы - её первичный ключ.

Пример:

Отображение классов-ассоциаций:

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

Пример:

Отображение квалифицированных ассоциаций:

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

Пример:

Отображение обобщения (наследования):

Стратегии:

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

Какой именно способ выбрать диктуют соображения эффективности (скорость в обмен на объем памяти). Рассмотрим на примере. Пусть класс Person - абстрактный.

При использовании 1-го подхода будут созданы 4 таблицы. В первой будут храниться значения атрибутов, специфичных для персон, во второй - для студентов, в третьей - для сотрудников, в четвертой - для работающих студентов. У таблиц Person, Student, Employee будет дополнительный столбец - тип, в котором будет храниться реальный тип объекта. Для каждого экземпляра класса Student будут две записи, одна в таблице студентов, вторая - в таблице персон. Для экземпляра StudentEmployee - четыре записи, по одной в каждой таблице. При чем у всех их будет одинаковое значение первичного ключа - идентификатора. Рассматривая запись из таблицы персон можно узнать реальный тип текущего объекта и по значению идентификатора найти в других таблицах значения дополнительных атрибутов этого объекта. При таком подходе между таблицами есть ограничения целостности, например, если удаляется запись о персоне, следует удалить связанные записи из других таблиц.

Рис. Пример применения стратегии «Для каждого класса своя таблица».

При втором подходе используется общая разреженная таблица. В ней также для каждой записи хранится ее тип. Неудобство состоит в том, что для любой записи о персоне есть риск «залезть» в поля, принадлежащие не классу объекта, а его подклассу.

Рис. Пример применения стратегии «Для всей иерархии одна таблица».

Третий подход позволяет по сравнению с первой стратегией сэкономить одну таблицу - Person. Поскольку класс Person абстрактный, то экземпляров у него нет, значит, значения атрибутов персон можно хранить в таблицах непосредственных потомков этого класса - Student и Employee. Для каждого студента или сотрудника будет храниться единственная запись в соответствующей таблице. Для StudentEmployee - три записи, по одной в каждой из трёх таблиц. Некоторое неудобство состоит в том, что атрибуты персон у каждого такого объекта хранятся дважды, нужно следить, чтобы значения, лежащие там, совпадали. Для выдачи всех персон (т. е. студентов, сотрудников и работающих студентов) в БД будет создан запрос, объединяющий записи таблиц Student и Employee. При объединении следует исключить дубли, возникающие для каждого объекта StudentEmployee. Как и в первом подходе, в каждой записи хранится реальный тип объекта.

Рис. Пример применения стратегии «Таблицы только для конкретных классов».

Четвертый путь дает две таблицы (студентов и служащих) и два объединяющих запроса (один для персон, второй для студентов-служащих). То, что в предыдущем случае хранилось в таблице StudentEmployee, теперь помещается в одну из двух оставшихся таблиц, которая становится разреженной. Например, в таблице студентов предусмотрены столбцы для хранения атрибутов классов Person, Student, StudentEmployee, в отдельном столбце хранится реальный тип объекта. В таблице Employee - Person, Employee. Для каждого экземпляра StudentEmployee будут храниться две записи, по одной в каждой таблице, с одинаковыми значениями первичного ключа.

Рис. Пример применения стратегии «Таблицы только для различных конкретных классов».

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

Таблица изображается как класс со стереотипом <<table>>. SQL-запросы также изображаются классами со стереотипом <<view>> (на диаграмме отсутствуют). Столбцы таблиц представлены атрибутами классов-таблиц. Используются стереотипы <<column>> (обычный столбец) <<PK>> (столбец, входящий в первичный ключ), <<FK>> (столбец, входящий во внешний ключ), <<PK,FK>> (столбец, входящий в первичный и во внешний ключ помечается двумя стереотипами). «Операции» таблиц моделируют ограничения или хранимые процедуры. Связи между таблицами моделируются как ассоциации между классами. Стереотипы связей <<identifying>> (идентифицирующая), <<non-identifying>> (не идентифицирующая). Связь является идентифицирующей, если первичный ключ связанной таблицы включает в себя её внешний ключ. В остальных случаях она не идентифицирующая. Для отображения ограничений целостности связь может моделироваться композицией. Направления связей не указывают, так как связи между таблицами всегда двунаправлены.

Рассмотрим примеры.

В системе обработки заказов есть два устойчивых класса: Order и OrderItem:

Так как мощности композиции «1 к 1..*», дополнительная таблица не нужна. Схема БД, полученная при объектно-реляционном отображении, выглядит так:

Согласно схеме БД, каждая запись о заказе состоит из 6 столбцов, один из которых является первичным ключом (<<PK>>). Столбцы для хранения статических и выводимых атрибутов не заводятся, их можно вычислять запросами. Как "операция" таблицы TableOrder моделируется ограничение первичного ключа. Позиции заказа хранятся в отдельной таблице TableOrderItem. В этой таблице 5 столбцов, один из которых является частью первичного ключа (<<PK>>), а другой - и частью первичного ключа, и внешним ключом (<<FK>>). В таблицу добавлены ограничения первичного и внешнего ключа и ограничение индекса. Связь между таблицами идентифицирующая, так как часть первичного ключа входит во внешний ключ. Одному объекту - экземпляру класса Order - будут соответствовать одна запись в таблице TableOrder и связанные с ней записи в таблице TableOrderItem.

Другой пример из системы регистрации на курсы. Диаграмма с устойчивыми классами:

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

Согласно схеме БД, каждая запись о студенте состоит из 3 столбцов, один из которых является первичным ключом (<<PK>>). Как «операция» таблицы TableStudent моделируется ограничение первичного ключа. Классификация студента хранится в отдельной таблице TableClassification. В этой таблице единственный служебный столбец, являющийся и первичным, и внешним ключом (<<PK,FK>>). В таблицу добавлены ограничения первичного и внешнего ключа, ограничение индекса и ограничение уникальности, определяющее, что с каждой записью классификации связана ровно одна запись из одной из двух оставшихся таблиц. Эти две таблицы для подклассов. Помимо собственных столбцов в них есть служебный, являющийся и первичным и внешним ключом (два стереотипа <<PK>>, <<FK>>). В каждой из двух таблиц добавлены 3 «операции»-ограничения: индекс, первичный и внешний ключ. Все связи идентифицирующие, так как всюду первичный ключ входит во внешний.

Одному объекту - экземпляру класса Student - будут соответствовать три записи:

- запись в таблице TableStudent;
- связанная с ней запись в таблице TableClassification;
- запись в одной из двух оставшихся таблиц.

Можно упростить схему, убрав таблицу TableClassification и связав таблицу TableStudent с таблицами TableFullTimeClassification и TablePartTimeClassification напрямую.

## Литература к лекции 9

- Рамбо Дж., Блаха М. UML 2.0. Объектно-ориентированное моделирование и разработка. 2-е изд.: Пер. с англ. - СПб.: Питер, 2007. - Глава 19.
- Мацяшек Л. А. Анализ и проектирование информационных систем с помощью UML 2.0 - СПб.: Вильямс, 2008 - § 10.9.
- Фаулер М. Архитектура корпоративных программных приложений. - М.: Вильямс, 2007.
- Вендров А. М. Проектирование программного обеспечения экономических информационных систем. 2-е изд. - М.: Финансы и статистика, 2005. - Глава 4.

#