Поскольку многозначность атрибутов в рамках реляционной модели данных недопустима, то многозначные атрибуты выносятся в отдельные дополнительные отношения. В нашем примере многозначным является атрибут Телефон у типов сущности Отделение, Поставщик, Покупатель. Перенесем его в отдельное отношение (рис. 4).
Рис. 4. Дополнительный тип сущности Телефон
Частичное участие в связи сущности Телефон объясняется тем, что не все телефоны относятся к отделениям, часть из них – это телефоны поставщиков и покупателей.
4.5.2. Построение скорректированной ER-модели
Все изменения, сделанные на предыдущем этапе, отображаются в обобщенной ER-модели (рис. 5).
4.5.3. Определение набора отношений
На данном этапе полученная ранее ER-модель отображается в набор отношений базы данных. При этом типы сущности преобразуются в отношения, а типы связи представляются с помощью механизма первичных и внешних ключей: первичные ключи родительских отношений включаются в дочерние отношения в качестве внешних ключей. Какие отношения будут выступать родительскими, а какие – дочерними зависит от типа связи между ними.
Для связей типа один-ко-многим родительским будет отношение, представляющее единичную сторону связи. Для связей типа один-к-одному родительским будет отношение, соответствующее сущности с частичным участием. Если степени участия обеих сущностей в такого типа связи одинаковы, то выбор родительского отношения произволен и может определяться какими-либо другими соображениями.
Рис. 5. Скорректированная ER-модель
Связи типа суперкласс-подклассы могут отображаться различными способами:
|
|
¾ отдельные отношения создаются, как для суперкласса, так и для всех подклассов (в этом случае отношение, представляющее суперкласс, будет родительским);
¾ отношения создаются только для каждого из подклассов;
¾ создается единое отношение, включающее в себя и суперкласс и подклассы.
Выбор конкретного варианта определяется многими причинами и не является однозначным.
В нашем примере специализацию сущности Работник представим единым отношением. Причем в связях управляет и отвечает за, имеющих тип один-к-одному, данное отношение будет выступать родительским, поскольку участие сущности Работник в этих связях – частичное.
Сущности Поставщик и Покупатель представим отдельными отношениями (если бы по постановке задачи Поставщики и Покупатели не были функционально разграничены, то целесообразней было бы создать единое отношение для всех контрагентов).
Для всех создаваемых отношений должны быть указаны первичные и внешние ключи. При этом первичные ключи отношений, соответствующих слабым сущностям, частично или полностью определяются на основе атрибутов, импортированных из родительских отношений в качестве внешних ключей.
В соответствии с изложенными правилами и соображениями для рассматриваемого примера можно предложить следующий набор отношений:
Ø Отделение (Номер, Адрес, Таб. № руководителя),
ПК – Номер,
ВК – Таб. № руководителя ссылается на Работник (Таб. №);
Ø Склад (Номер, Номер отделения, Таб. № заведующего),
ПК – Номер, Номер отделения,
ВК – Номер отделения ссылается на Отделение (Номер),
|
|
ВК – Таб. № заведующего ссылается на Работник (Таб. №);
Ø Работник (Таб. №, ФИО, Пол, Дата рождения, Адрес, Дата приема, Дата назначения, Номер отделения, Номер склада),
ПК – Таб. №,
ВК – Номер отделения ссылается на Отделение (Номер),
ВК – Номер склада, Номер отделения ссылается на Склад (Номер, Номер отделения);
Ø Товар (Наименование, Цена, Единица измерения),
ПК – Наименование, Цена;
Ø Поставщик (Наименование, Адрес),
ПК – Наименование, Адрес;
Ø Покупатель (Наименование, Адрес),
ПК – Наименование, Адрес;
Ø Телефон (Номер, Номер отделения, Наименование поставщика, Адрес поставщика, Наименование покупателя, Адрес покупателя),
ПК – Номер,
ВК – Номер отделения ссылается на Отделение (Номер),
ВК – Наименование поставщика, Адрес поставщика ссылается на Поставщик (Наименование, Адрес),
ВК – Наименование покупателя, Адрес покупателя ссылается на Покупатель (Наименование, Адрес);
Ø Наличие (Номер склада, Номер отделения, Наименование товара, Цена, Количество),
ПК – Номер склада, Номер отделения, Наименование товара, Цена,
ВК – Номер склада, Номер отделения ссылается на Склад (Номер, Номер отделения),
ВК – Наименование товара, Цена ссылается на Товар (Наименование, Цена);
Ø Накладная на покупку (Номер склада, Номер отделения, Номер, Дата, Наименование поставщика, Адрес поставщика),
ПК – Номер отделения, Номер,
ВК – Номер склада, Номер отделения ссылается на Склад (Номер, Номер отделения),
ВК – Наименование поставщика, Адрес поставщика ссылается на Поставщик (Наименование, Адрес);
Ø Накладная на продажу (Номер отделения, Номер склада, Номер, Дата, Наименование покупателя, Адрес покупателя),
|
|
ПК – Номер отделения, Номер,
ВК – Номер склада, Номер отделения ссылается на Склад (Номер, Номер отделения),
ВК – Наименование покупателя, Адрес покупателя ссылается на Покупатель (Наименование, Адрес);
Ø Строка накладной на покупку (Номер отделения, Номер накладной, Наименование товара, Цена, Количество),
ПК – Номер отделения, Номер накладной, Наименование товара, Цена,
ВК – Номер отделения, Номер накладной ссылается на Накладная на покупку (Номер отделения, Номер),
ВК – Наименование товара, Цена ссылается на Товар (Наименование, Цена);
Ø Строка накладной на продажу (Номер отделения, Номер накладной, Наименование товара, Цена, Количество),
ПК – Номер отделения, Номер накладной, Наименование товара, Цена,
ВК – Номер отделения, Номер накладной ссылается на Накладная на продажу (Номер отделения, Номер),
ВК – Наименование товара, Цена ссылается на Товар (Наименование, Цена).
4.5.4. Проверка отношений на соответствие требованиям нормализации
Полученные на предыдущем этапе отношения должны быть проверены на соответствие требованиям НФБК. Эта проверка преследует две цели. Во-первых, соответствие отношений требованиям НФБК гарантирует отсутствие в проектируемой базе данных проблем обновления. А, с другой стороны – это средство контроля самого процесса проектирования. Любые найденные отступления от требований нормализации будут свидетельством либо неадекватности самой ER-модели, либо ошибок ее преобразования в набор отношений.
Замечание 1. Нормализация в рассматриваемой методике проектирования служит лишь средством проверки, а не основой процесса проектирования.
Замечание 2. Соответствие отношений НФБК на данном этапе является обязательным требованием. Отступления от НФБК в целях повышения производительности допустимы лишь на последующих этапах физического проектирования.
Для проверки следует выписать для каждого отношения все функциональные зависимости и убедиться в том, что:
1) в отношении нет многозначных атрибутов (требование 1НФ);
2) нет атрибутов, частично зависимых от первичного ключа (2НФ);
3) нет атрибутов, транзитивно зависимых от первичного ключа (3НФ);
4) все детерминанты являются потенциальными ключами (НФБК).
Например, для отношения:
Отделение (Номер, Адрес, Таб. № руководителя),
ПК – Номер,
имеем следующие функциональные зависимости (если предположить, что по одному адресу не могут располагаться два отделения):
Номер 
Адрес, Таб. № руководителя;
Адрес Номер, Таб. № руководителя;
Таб. № руководителя Адрес, Номер.
Среди атрибутов отношения Отделение нет многозначных, нет атрибутов, частично зависимых от первичного ключа (и быть не может, т.к. первичный ключ состоит из одного атрибута), нет атрибутов транзитивно зависимых от первичного ключа. А также легко заметить, что все атрибуты выступают детерминантами в функциональных зависимостях и при этом являются потенциальными ключами. Таким образом, отношение Отделение удовлетворяет всем требованиям НФБК.
Аналогичная проверка должна быть выполнена и для всех остальных отношений.
4.5.5. Проверка модели на возможность выполнения всех транзакций
Полученная в ходе проектирования ER-модель, а, следовательно, и сформированный на ее основе набор отношений, должны обеспечивать выполнение всех предусмотренных постановкой задачи транзакций. Для проверки данного обстоятельства следует выписать из постановки задачи и обозначить все предполагаемые транзакции. Например, так:
Т1 – открытие нового отделения;
Т2 – открытие нового склада;
Т3 – закрытие склада;
…
Далее, на ER-модель наносится путь выполнения каждой транзакции (рис. 6). Например, открытие нового отделения предполагает ввод информации об этом отделении в отношение Отделение и выбор его руководителя из работников, перечисленных в отношении Работник. Открытие нового склада происходит в соответствующем отделении и предполагает выбор заведующего среди работников. Закрытие склада предполагает предварительную проверку, что в Наличии нет записей, относящихся к данному складу, и отмену приписки к нему работников. И т.д.
Рис. 6. Пути выполнения транзакций
Если после нанесения путей выполнения всех транзакций на модели останутся области, не охваченные ни одной транзакцией, то возникает вопрос о необходимости типов сущности и связи, расположенных в этих областях. Скорее всего, модель может быть за их счет упрощена. Напротив, если для выполнения какой-либо транзакции невозможно указать хотя бы один путь, то это свидетельство неполноты модели и необходимости ее доработки.
4.5.6. Построение окончательного варианта ER-модели
Все уточнения, сделанные на предыдущих трех этапах, должны быть отражены в окончательном варианте ER-модели.
4.5.7. Определение требований поддержки целостности данных
Как отмечалось уже выше, основное назначение ограничений целостности данных состоит в том, чтобы не допустить занесение в базу данных противоречивой информации.
Замечание. На данном этапе не рассматриваются возможности той или иной СУБД по поддержке целостности данных, а определяются ограничения, которые должны быть впоследствии обеспечены средствами той СУБД, которая будет выбрана для физической реализации БД.
Предполагается задание следующих ограничений целостности.
1) Обязательные данные. Это атрибуты, для которых не допустимо отсутствие значений: номер отделения, его адрес, номер склада, таб. номер работника, его ФИО, дата рождения, … (см. таблицу атрибутов).
2) Ограничения доменов (см. таблицу доменов).
3) Целостность сущностей. Все атрибуты входящие в первичные ключи должны быть объявлены как NOT NULL (обратите внимание, что это требование относится к атрибутам первичных, но не внешних ключей).
4) Ссылочная целостность. Для всех внешних ключей должны быть указаны варианты работы механизма каскадных изменений на случай удаления и изменения родительских записей. Выбор делается из следующего набора вариантов:
¾ NO ACTION – удаление или изменение родительской записи недопустимо при наличии ссылающихся на нее дочерних записей;
¾ CASCADE – каскадное удаление или изменение дочерних записей;
¾ SET NULL – при удалении родительской записи в полях внешних ключей дочерних записей значения удаляются;
¾ SET DEFAULT – при удалении родительской записи в полях внешних ключей дочерних записей проставляются значения по умолчанию;
¾ NO CHECK – ссылочная целостность не отслеживается.
Это полный набор возможных вариантов. В конкретных СУБД некоторые из них могут отсутствовать.
Наиболее часто используется сочетание варианта NO ACTION при удалении и CASCADE при изменении родительской записи. Например:
Склад (Номер, Номер отделения, Таб. № заведующего),
ПК – Номер, Номер отделения,
ВК – Номер отделения ссылается на Отделение (Номер) при удалении NO ACTION при изменении CASCADE,
ВК – Таб. № заведующего ссылается на Работник (Таб. №) при удалении SET NULL при изменении CASCADE;
5) Бизнес-правила – ограничения, обусловленные конкретной предметной областью, конкретной постановкой задачи. Например: работник не может заведовать несколькими складами одновременно, поставщик не может быть покупателем и наоборот, каждый работник должен относиться к некоторому отделению и т.п.
4.6. Физическое проектирование
Физическое проектирование – это процесс создания базы данных средствами выбранной СУБД и разработки приложений.
Вначале физического проектирования на основе всей информации, собранной в процессе концептуального и логического проектирования, с учетом предполагаемого объема хранимой информации, количества одновременно работающих приложений-клиентов, имеющегося бюджета и др. факторов делается выбор конкретной СУБД, в рамках которой будет выполняться физическая реализация базы данных.
Далее средствами выбранной СУБД создаются таблицы и реализуются ограничения, определяется набор вторичных индексов, задаются правила и права доступа к базе данных, разрабатываются серверные и клиентские части приложений. Большое внимание при этом уделяется скорости выполнения наиболее часто используемых запросов.
В целях повышения скорости выполнения запросов и общей производительности системы достаточно часто приходится использовать приемы, предполагающие отклонение от требований нормализации. При этом следует помнить, что всякий отход от требований нормальных форм до НФБК включительно создает почву для проблем обновления. Поэтому одновременно должны предприниматься адекватные меры по исключению возможности появления такого рода проблем, то есть процесс денормализации (введения избыточности) должен быть контролируемым.
4.6.1. Введение контролируемой избыточности
Поскольку строгое следование требованиям нормализации может существенно снизить производительность выполнения запросов в создаваемой базе данных, на этапе физического проектирования могут использоваться приемы денормализации для повышения скорости выполнения наиболее критичных запросов. Рассмотрим некоторые из этих приемов.
1. Замена естественных первичных ключей суррогатными ключами на основе семантически незначащих атрибутов. Первичные ключи отношений, создаваемых на этапе логического проектирования, определяются на основе атрибутов, входящих в эти отношения и имеющих конкретный физический смысл (поэтому они и называются естественными ключами). К сожалению, размер таких ключей может быть достаточно большим и, соответственно, их включение в дочерние отношения в качестве внешних ключей будет существенно увеличивать объем последних. Кроме того, всякое изменения значений атрибутов первичных ключей должно дублироваться механизмом каскадных изменений в соответствующих внешних ключах.
Например, в отношении Товар первичный ключ включает в себя длинный символьный атрибут Наименование. Соответственно, этот атрибут будет импортирован в составе внешних ключей в отношения Товар и Строка накладной на…, что существенно увеличит их объем. Помимо того, по определениям первичного и внешних ключей будут созданы индексы, которые заранее могут быть названы «плохими», поскольку они из-за своего большого объема не ускорят выполнение операций поиска и сортировки, а только замедлят операции изменения в своих таблицах.
Поэтому представляется целесообразным добавить в отношение Товар еще один искусственный, ничего физически не значащий атрибут – условный номер товара (задаваемый автоматически генерируемым целым числом) и на его основе определить первичный ключ. Это, конечно, приведет к некоторой избыточности в таблице товаров, но зато позволит в значительно большей степени сократить объем дочерних таблиц. Кроме того, не будет каких-либо причин для изменения значений такого атрибута и, следовательно, для работы механизма каскадных изменений. А создаваемые по определениям первичного и внешних ключей индексы будут реально ускорять выполнение операций поиска и сортировки.
Отношение Товар при этом будет иметь вид:
Товар (Усл. №, Наименование, Цена, Единица измерения),
ПК – Усл. №,
а, например, отношение Наличие:
Наличие (Номер склада, Номер отделения, Усл. № товара, Количество),
ПК – Номер склада, Номер отделения, Усл. № товара,
ВК – Номер склада, Номер отделения ссылается на Склад (Номер, Номер отделения),
ВК – Усл. № товара ссылается на Товар (Усл. №).
2. Дублирование атрибутов родительских отношений в дочерних отношениях. Может использоваться в тех случаях, когда скорость выполнения часто используемых запросов оказывается недостаточной из-за необходимости построения соединений больших по объему таблиц. Поскольку соединение строится для получения значений Lookup-полей из родительского отношения, то для исключения такого рода соединений можно заранее скопировать соответствующие атрибуты в дочерние отношения.
Например, если первичный ключ в отношении Товар построен по атрибутам Наименование и Цена, то эти атрибуты будут включены в дочернее отношение Наличие в качестве внешнего ключа. При извлечении же информации о наличии товаров придется построить соединение достаточно больших таблиц товаров и наличия для получения значения одного лишь атрибута Единица измерения, тогда как наименование и цена соответствующих товаров уже имеются в полях внешнего ключа. В этой ситуации может оказаться целесообразным скопировать в отношение Наличие не только атрибуты первичного ключа отношения Товар, но и небольшой по размеру атрибут Единица измерения. Объем дочернего отношения увеличится незначительно, а необходимость соединения больших по объему таблиц отпадет.
Замечание. Еще раз обратим внимание на тот факт, что такие приемы влекут за собой не только увеличение избыточности хранимой информации, но и создают почву для проблем обновления. Так в приведенном примере в случае изменения единицы измерения для какого-либо товара, аналогичные изменения должны быть продублированы во всех строках таблицы наличия, относящихся к этому товару. Причем механизм каскадных изменений здесь не поможет, поскольку атрибут Единица измерения не входит в первичный ключ. Соответствующие действия должны быть реализованы программно.
3. Введение групп повторяющихся атрибутов. Для приведения построенной модели БД в соответствие с возможностями реляционной модели данных в процессе логического проектирования многозначные атрибуты выносятся в отдельные отношения. А это означает, что при извлечении необходимой информации придется строить соединения с этими дополнительными отношениями и тратить на это дополнительное время. Если же количество различных значений многозначного атрибута известно заранее и невелико, то вместо создания отдельного отношения можно в исходном отношении создать группу повторяющихся атрибутов и тем самым избавиться от дополнительного соединения таблиц.
Например, если заранее известно, что для отделений, поставщиков и покупателей будут указываться не более двух телефонов, то вместо создания отношения Телефон можно в соответствующие таблицы включить по два повторяющихся атрибута: Телефон1 и Телефон2.
На практике для повышения производительности могут использоваться и другие приемы денормализации.
4.6.2. Создание таблиц и реализация ограничений
На этом этапе средствами выбранной СУБД определяются домены, создаются таблицы и реализуются ограничения целостности данных. При этом используются соответствующие результаты концептуального и логического проектирования (см. главу 3).
Обратите также внимание на следующие замечания.
Замечание 1. Ссылочная целостность может обеспечиваться как внутренними средствами СУБД, так и с помощью дополнительных триггеров, если СУБД не поддерживает все необходимые варианты работы механизма каскадных изменений или использование собственных средств СУБД приводит к созданию «плохих» индексов.
Замечание 2. Для обеспечения уникальности альтернативных ключей следует строить по их атрибутам уникальные индексы. Например, если в отношении Товар естественный первичный ключ заменить на суррогатный (по условному номеру товара), то сочетание атрибутов Наименование и Цена станет альтернативным ключом и для него следует обеспечить уникальность с тем, чтобы не допустить повторного добавления в таблицу одного того же товара.
Замечание 3. Для реализации бизнес-правил могут использоваться ограничения доменов, ограничения в определениях столбцов и таблиц, триггеры и хранимые процедуры. Реализация бизнес-правил в клиентских приложениях нежелательна, поскольку потребует дублирования для всех приложений и не будет играть свою роль при непосредственной работе с базой данных помимо приложений.
4.6.3. Создание вторичных индексов
Для повышения производительности работы системы наряду с первичными индексами, автоматически создаваемыми СУБД по определениям первичных и внешних ключей, могут определяться дополнительные вторичные индексы. При этом следует помнить, что, ускоряя выполнение запросов, операций поиска и сортировки, дополнительные индексы замедляют операции обновления информации и, помимо того, требуют соответствующих мероприятий по поддержке своей полезности (см. главу 3).
Дополнительные замечания.
Замечание 1. Не следует создавать вторичные индексы:
¾ по длинным символьным полям;
¾ по полям, имеющим небольшое количество различных значений;
¾ для небольших по объему таблиц;
¾ для ускорения запросов возвращающих значительную часть (20 и более %) строк таблицы.
Замечание 2. Для всех проектируемых запросов и особенно тех, которые наиболее критичны в плане производительности всей системы, необходимо проверять план их выполнения на предмет использования индексов (используются ли индексы вообще, правильно ли они подобраны и нет ли необходимости в создании дополнительных индексов). В некоторых случаях приходится «подсказывать» оптимизатору запросов СУБД более эффективный план.
4.6.4. Разработка серверных и клиентских частей приложений
(см. главы 2 и 3)