3.1.4. Логическое проектирование базы

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

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

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

Еще одним приемом, улучшающим структуру БД, является создание справочников и классификаторов данных. Для столбцов, значения которых принадлежат фиксированному ограниченному множеству или носят условно постоянный характер, создаются справочники и классификаторы данных в виде отдельных таблиц. Например, цвет рам в компании по установке окон и дверей выбирается из фиксированного и ограниченного множества. Создание таблицы - справочника возможных цветов, состоящего из столбцов кода и названия цвета, позволит сжать информацию в базе, заменив во всех таблицах наименование цвета его коротким кодом и исключить ошибки пользователя при вводе цвета, заменив ввод данных выбором из предлагаемого набора. При создании базы необходимо использовать общероссийские классификаторы. Например, общероссийский классификатор форм собственности (ОКФС), Общероссийский классификатор административно-территориального деления (ОКАТО), Общероссийский классификатор органов государственной власти и управления (ОКОГУ), Общероссийский классификатор специальностей по образованию (ОКСО). Кроме них существуют и активно используются отраслевые и ведомственные классификаторы и классификаторы отдельных предприятий. После создания справочника или выбора готового классификатора вносятся изменения в структуры информационных таблиц путем замены имен и доменов полей, вошедших в справочники, их кодами.

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

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

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

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

Затем в логическую структуру БД вносятся связи, заданные между сущностями концептуальной схемы. Способ представления связи в реляционной модели данных зависит от ее типа. Связь типа «один к одному» при обязательном участии каждого объекта в связи означает, что экземпляры связанных сущностей могут появляться только вместе – парами. Например, договор и его смета в схеме, представленной на рис. 3.9. В таком случае обычно нецелесообразно разделять эти сущности по отдельным таблицам. Следует образовать одну таблицу с полным набором столбцом и первичный ключ одной из таблиц считать ключом объединяющей таблицы.

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

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

соответствует логическая схема, представленная следующей ER диаграммой:

Рис.3.10. Представление связи «многие ко многим» ER диаграммой

Рис. 3.11 содержит пример ER диаграммы, соответствующей концептуальной схеме, приведенной на рис. 3.9. Обратите внимание на то, что связь между сущностью «Заявка» и сущностью «Договор», определенная в рис. 3.9 как связь точно одного экземпляра заявки с нулем или одним экземпляром договора, вынужденно заменена связью «один ко многим», так как реляционная модель не поддерживает необходимую связь. Таким образом, СУБД, используя свойства заданной связи, не допустит появления в БД договоров без соответствующей заявки, но не обнаружит создание более одного договора по одной заявке.

Риc. 3.11. ER диаграмма для процесса приема и исполнения заказа

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

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