3.2.2. Проектирование схем реляционных баз данных

В реляционных СУБД при проектировании схемы реля­ционной базыданных можно выделить следующую последо­вательностьпроцедур:

а) определение перечня таблиц и их связей;

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

в)определение и установление индексов (индексирования) для полей в таблицах;

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

д)установление ограничений целостности по полям таб­лиц и связям;

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

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

3.2.2.1. Проектирование и создание таблиц

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

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

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

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

Правильность определения ключа таблицы проверяется эм­пирически по возможным ситуациям совпадения у различных кортежей значений ключа. Во многих случаях выбор ключа яв­ляется нетривиальной задачей. Какое поле, к примеру, выбрать ключевым для таблицы «Сотрудники»? Напрашивается состав­ной ключ из полей «Фамилия», «Имя», «Отчество», однако в конкретных жизненных ситуациях имеется вероятность их со­впадения. Можно добавить в состав ключа еще поле «Год рож­дения», но и при этом все равно сохранится, хотя и несколько снизится, вероятность совпадения. Альтернативным вариантом ключа может быть «№ паспорта», если ситуации с наличием у одного лица нескольких паспортов полностью исключаются. Если в банке данных ограничиться только сотрудниками дан­ной организации, то отработанным вариантом ключа может быть табельный номер сотрудника — «Таб.№».* На практике распространенным приемом при проектировании таблиц явля­ется искусственное введение в качестве ключа параметра, являющегося аналогом табельного номера — внутреннего учет­ного номера экземпляра (записи) соответствующего объекта.

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

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

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

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

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

Рис. 3.2. Пример реализации связи «Один-к-одному» в реляцион­ных СУБД

Связи типа «Многие-ко-многим»в реляционных СУБД в большинстве случаев реализуются через создание двух связей«Один-ко-многим»,которые связывают исходные таблицы с третьей общей (связной) таблицей. Ключ связной таблицы со­стоит, по крайней мере, из двух полей, которые являются поля­ми внешнего ключа для связываемых отношением «Многие-ко-многим» исходных таблиц. Пример реализации связи типа «Многие-ко-многим», выражающей отношение «Согласование» между таблицами «Документы» и «Сотрудники», приведен на рис. 3.3.

Еще одним важным параметром при проектировании таб­лиц является определение необходимости индексированиятех или иныхполейтаблиц. Определение и установление индексов полей таблиц базы данных является, как уже отмечалось, важ­ным средством создания условий эффективной обработки дан­ных. Индексирование полей (создание индексных массивов) существенно повышает скорость поиска и доступа к записям базы данных. Однако при этом соответственно замедляется ввод и добавление данных из-за необходимости переупорядочения индексных массивов при любом обновлении, удалении или до­бавлении записей. Поэтому при проектировании таблиц следу­ет тщательно проанализировать, насколько часто при последу­ющей эксплуатации банка данных потребуется поиск или вы­борка строк-записей таблицы по значениям тех или иных полей, исходя из функций и задач АИС. На основе такого анализа и определяются те поля таблицы, для которых необходимо со­здать индексы. К примеру, в таблице «Сотрудники» базы дан­ных по документообороту поле «ФИО» целесообразно опреде­лить индексируемым, так как, очевидно, довольно часто будет требоваться доступ к записям таблицы именно по значению этого поля. А вот поле «№ кабинета» вряд ли целесообразно индексировать, так как исходя из задач и функций АИС по документообороту, можно прогнозировать, что задачи отбора, группирования и прочей обработки записей сточки зрения раз­мещения сотрудников по кабинетам будут достаточно редки­ми.

Рис. 3.3. Реализация связей «Многие-ко-многим» в реляционных СУБД

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

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

В большинстве СУБД вопросы внутреннего устройства ин­дексных массивов остаются скрытыми и недоступными как для конечных пользователей, так и для проектировщиков. Допус­кается только лишь качественное определение режима индек­сирования — без повторов значений индексируемых полей* и с возможностью таких повторов, что, очевидно, определяет раз­ные типы индексных массивов — Б-деревья или инвертирован­ные списки.**

* Так называемый уникальный индекс. Автоматически устанавливается для клю­чевых полей.

** Как правило, данные вопросы в документации по СУБД не отражаются.

При определении параметров полей таблиц важное значе­ние имеет также выделение полей с перечислительным (сло­варным, списковым) характером значений.Значения таких по­лей определяются из некоторого унифицированного списка-сло­варя.К примеру, поле «Образование» является не просто текстовым, а по сути текстово-списочным, так как набор его всевозможных значений составляет унифицированный спи­сок—«Начальное», «Среднее», «Среднеспециальное», «Сред­нетехническое», «Высшее». Некоторые СУБД обеспечивают возможность при проектировании таблиц построения и при­вязки к соответствующим полям таких списков-словарей.

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

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

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

Динамические словари в большинстве случаев реализуют­ся через создание дополнительных одностолбцовых таблиц, строки которых являются источником списка значений для по­лей других таблиц. Привязка подобных словарных таблиц в ка­честве источника значений для полей других таблиц осуществ­ляется также через специальные механизмы конкретной СУБД. Такие таблицы в дальнейшем доступны пользователям банка данных. Соответственно обновление, добавление или удаление записей в таблицах-словарях позволяет изменять словарный базис для полей соответствующих таблиц. В некоторых СУБД дополнительно может также устанавливаться режим ограниче­ния значенийсловарно-списочных полей только установленнымспискомзначений. Установление такого режима целесообраз­но в тех случаях, когда нужно исключить, в принципе, даже случайный (ошибочный) выход значений поля за пределы спис­ка. Так, например, в случае поля «Оценка» значения могут быть только из списка «Неудовлетворительно», «Удовлетворитель­но», «Хорошо», «Отлично».

Одним из важных в практическом плане этапов проекти­рования таблиц является установление ограничений целостности по полям и связям.Как уже указывалось, в исходном виде в реляционной модели данных основным ограничением по значению полей является требование уникальности таблич­ных строк-кортежей, что проявляется в требовании уникально­сти значений ключевых полей. Дополнительно в реляционных СУБД могут устанавливаться требования уникальности значе­ний и по другим (не ключевым) полям через создание для них индексов в режиме без повторов (UNIQUE),а также установле­ния режима обязательного заполнения в строках-кортежах оп­ределенных полей (режим NOT NULL).

Вместе с тем современные СУБД могут предоставлять и более развитые возможности установления ограничений цело­стности данных. Можно определять допустимые диапазоны зна­чений полей (например, значение поля «Оклад» не может быть меньше величины минимального размера оплаты труда), а так­же относительные соотношения значений по определенным по­лям таблицы (например, значение поля «Количество» в табли­це «Товары» не может быть меньшим значения поля «Мин_за-пас», значение поля «Дата_исполнения» в таблице «Заказы» не может быть позднее поля «Дата_размещения» плюс определен­ное количество дней, скажем 7 дней, исходя из того требова­ния, что любой заказ клиента должен быть исполнен в течение максимум 7 дней). Такие ограничения целостности данных от­ражают ту часть правил и особенностей предметной области АИС,которая не формализуется в рамках реляционной модели данных.*

* Ввиду данного обстоятельства в некоторых англоязычных источниках такие ог­раничения целостности называют «правилами бизнеса».

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

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

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

* Соответственно в этом случае СУБД различает «пустые» (NULL)и «неопреде­ленные» значения полей.

В заключение отметим, что разделение процесса проекти­рования таблиц на этапы а, б, в, г и д является условным, а сам процесс предварительного проектирования (создания) таблиц, как будет показано в параграфе 4.1, реализуется специальными инструкциями языка SQL.