Определение и установление индексов. (вопрос 50)

Проектирование и создание схем таблиц. (вопросы 44-49)

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

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

Традиционные СУБД поддерживают ограниченный набор простых типов полей (Таблица 5.1).

Современные СУБД оперируют и с более сложными типами полей, такими как массивы, «вложенные» таблицы и т.п.

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

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

Таблица 5.1. Наиболее часто встречающиеся типы полей.

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

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

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

5.2.1. ER - диаграммы с типом связи между таблицами «Один -к- одному». (вопрос 44)

Реляционная модель организации данных по признаку множественности обеспечивает лишь два типа связей отношений между таблицами «Один -ко- многим» и «Один -к- одному». Реляционная модель не может непосредственно отражать связи типа «Многие-ко-многим», что объективно снижает её возможности при отражении сложных предметных областей. Однако это не является непреодолимой проблемой.

Общие правила генерации таблиц из ER-диаграмм можно получить, опираясь на такие понятия как класс принадлежности сущности и степень отношения (связи).

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

Рассмотрим эти понятия на примере данных рис. 5.1.

Пример диаграммы ER-экземпляров.

Рис. 5.1. ER - диаграмма, соответствующая диаграмме ER - экземпляров.

На рис. 5.2 представлены возможные классы принадлежностей для степени связи 1:1, на рис. 5.3 – ER - диаграммы, соответствующие диаграммам ER - экземпляров рис. 5.2.

а) Степень связи 1:1.

(сущность Автор, сущность Книга).

Класс принадлежности обеих сущностей не является обязательным.

б) Степень связи 1:1.

Класс принадлежности сущности Автор является обязательным.

в) Степень связи 1:1.

Класс принадлежности сущности Книга является обязательным.

г) Степень связи 1:1.

Класс принадлежности является обязательным для обеих сущностей.

Рис. 5.2. Возможные классы принадлежностей для степени связи 1:1.

а) Не требуется участия в связи всех экземпляров обеих сущностей

б) Экземпляры сущности Автор обязательно должны участвовать в связи, а сущности Книга – не обязательно

в) Требуется участие в связи всех экземпляров сущности книга и неучастие некоторых экземпляров сущности Автор

г) Обязательное участие в связи всех экземпляров обеих сущностей

НА-номер автора, НК – номер книги – ключи сущностей ER – диаграммы.

Рис. 5.3. ER- диаграммы соответствующие диаграммам ER-экземпляров с рис. 5.2.

Единицы в обеих частях связей рис.5.3. свидетельствуют о степени связи 1:1. В диаграммах ER-типа под блоком сущности выписан ключ этой сущности: НА (номер автора) для сущности автор и НК (номер книги) для сущности книга. Многоточие означает другие атрибуты сущности не входящие в ключи.

5.2.2. Правила генерации таблиц из ER-диаграмм со связями степени 1:1. (вопрос 45)

При определении количества таблиц нужных для размещения информации, содержащейся в бинарных связях степени 1:1, приведенных на ER-диаграммах рис. 5.3, можно предположить, что требуется одна таблица.

Пусть эта таблица называется Автор, и все атрибуты помещены в неё. На рис. 5.4. приведен пример, где класс принадлежности является обязательным для обеих сущностей (рис. 5.2(г), 5.3(г)).

Сущность Автор дополнена двумя типичными атрибутами:

Фамилия-имя-автора (афам) и телефон автора (ател).

Рис. 5.4. Отдельная таблица, где содержатся данные, приведенные на рис. 5.2(г) и 5.3(г).

В данном случае требуется всего одна таблица. Так как степень связи 1:1, и класс принадлежности является обязательным как для сущности Автор, так и для сущности Книга, гарантируется однократное появление каждого значения НА и каждого значения НК в любом экземпляре отношения. Это значит, что таблица не будет содержать ни пустой информации, ни повторяющихся групп избыточных данных. В качестве ключа выбран ключ сущности Автор, но мог бы быть выбран ключ сущности Книга.

Первое правило генерации отношений (таблиц).

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

Это самый простой случай. Усложнение задачи связано с тем, что класс принадлежности одной сущности является обязательным, а другой – необязательным.

На рис.5.5 приведена таблица для случая, когда класс принадлежности сущности Автор является обязательным, а сущности Книга – необязательным

(рис.5.2 б) и рис.5.3 б)).

Рис. 5.5. Отдельная таблица, где содержатся данные, приведенные на рис.5.2 б) и 5.3 б).

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

Способ исключения «пробелов» состоит в использовании двух таблиц вместо одной. Каждая таблица будет содержать информацию об одной сущности. Кроме того, ключ сущности, класс принадлежности которой является необязательным, необходимо поместить в качестве атрибута в таблицу, содержащую информацию о сущности, класс принадлежности которой является обязательным. Этот вариант приведен на рис. 5.6.

Рис. 5.6. Таблицы, где содержатся данные рис. 5.2(б), рис. 5.3(б).

Второе правило генерации отношений (таблиц).

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

Используя это правило для ситуации, представленной на рис. 5.2 (в), рис. 5.3 (в), где класс сущности Книга является обязательным, а сущности Автор – необязательным, получим два отношения для двух таблиц: Автор (НА, афам, ател.), Книга (НК, кназ, НА).

Последний случай, когда степень бинарной связи 1:1, класс принадлежности ни одной из сущностей, не является обязательным.

В этом случае единственное решение заключается в формировании 3-х таблиц: по одной для каждой сущности и одной для связи между ними.

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

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

Рис. 5.7. Возможные варианты таблиц для случая бинарной связи степени 1:1, когда ни один из классов принадлежности не является обязательным.

Третье правило генерации отношений (таблиц).

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

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

5.2.3. ER - диаграммы с типом связи между таблицами «Один - ко- многим». (вопрос 46)

Для случая бинарных связей степени 1:n требуется применение двух правил. Определяющим фактором выбора и использования одного из двух правил, является класс принадлежности n-связной сущности; класс же принадлежности 1-связной сущности не влияет на конечный результат.

На рис. 5.8 показаны различные диаграммы ER-экземпляров в случае, когда каждый автор может писать одновременно несколько книг, но каждая книга пишется не более чем одним автором. На рис. 5.9 представлены эквивалентные им ER-диаграммы.

Рис. 5.8. Примеры диаграмм экземпляров для случая степени связи 1:n.

Рис. 5.9. ER-диаграммы, соответствующие диаграммам ER-экземпляров с

рис. 5.8.

На рис. 5.10 приведена таблица Книга, содержащая данные, приведенные на рис.5.8(в) и 5.9(в). Этот случай отображает степень связи 1:n с обязательным классом n-связной сущности Книга и с необязательным классом принадлежности 1-связной сущности Автор.

Рис. 5.10. Использование одной таблицы для бинарной связи типа 1:n в случае, когда класс принадлежности n-связной сущности является обязательным, а 1-связной – необязательным.

5.2.4. Правила генерации таблиц из ER-диаграмм со связями 1: n. (вопрос 47)

Анализ рис. 5.10 показывает, во-первых, что если автор не пишет книгу, то поля таблицы, относящиеся к книге, отмечены «пробелами».

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

Замечание. Если бы класс принадлежности 1-связной сущности Автор был бы обязательным, то исчезли бы пробелы. Однако повторения данных в полях атрибутов Автора сохранились бы.

Четвертое правило генерации отношений (таблиц).

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

На рис. 5.11 приведен пример двух таблиц, построенных на основании сформулированного 4-го правила и содержащих информацию единой таблицы рис. 5.10.

Теперь рассмотрим случай, где степень связи 1:n с необязательным классом принадлежности обеих сущностей (рис. 5.8 (а), рис 5.9(а)).

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

Рис. 5.11. Данные таблиц рис. 5.10 после их разнесения по двум таблицам.

Использование двух таблиц также не приводит к успеху, так как останутся пробелы в полях «НА» в новой таблице Книга для книг, которые не пишутся.

Разрешить все эти проблемы позволяет пятое правило.

Пятое правило генерации отношений (таблиц).

Если степень бинарной связи равна 1:n и класс принадлежности n-связной сущности является необязательным, то необходимо формирование трех таблиц – по одной для каждой сущности, причем ключ каждой сущности служит первичным ключом соответствующей таблицы, и одной таблицы для связи. Связь должна иметь среди своих атрибутов ключ сущности для каждой сущности.

На рис. 5.12 приведены три таблицы, построенные на основе этого правила, и содержащие исходную информацию о Книге и Авторе.

Следует заметить, что в этом случае отсутствуют какие-либо пробелы, а также исключены повторяющиеся данные.

Здесь НК является первичным ключом, поскольку между НК и НА существует связь типа 1:n.

Рис. 5.12. Реализация правила 5.

5.2.5. Предварительные таблицы для бинарных связей степени «многие – ко - многим». (вопрос 48)

При бинарной связи m:n, вне зависимости от класса принадлежности, как первой, так и второй сущности, требуется создание трех таблиц на основании правила 6.

5.2.6. Правила генерации таблиц со связями m:n. (вопрос 49)

Шестое правило генерации отношений (таблиц).

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

Данные диаграмм ER-экземпляров для случая степени связи m:n и когда ни один класс принадлежности сущности не является обязательным, согласно правилу 6, может быть отображен набором таблиц рис. 5.12, построенных по пятому правилу. Отличие состоит лишь в том, что таблица «Пишет» целиком является ключом.

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

Важным параметром при проектировании таблиц является индексирование полей таблиц. Установление индексов полей (создание индексных массивов) существенно повышает скорость поиска и доступа к записям базы данных. Однако при этом соответственно замедляется ввод и добавление данных из-за необходимости переупорядочения индексных массивов при обновлении, удалении или добавлении записей. Поэтому при проектировании таблиц нужно проанализировать, насколько часто при эксплуатации банка данных потребуется поиск или выборка строк-записей таблицы по значениям тех или иных полей, исходя из функций и задач АИС.

По результатам анализа определяются те поля и таблицы, для которых необходимо создать индексы. К примеру, в таблице “Сотрудники” базы данных по документообороту поле “ФИО” целесообразно определить индексируемым, поскольку именно по значению этого поля наиболее часто будет требоваться доступ к записям. Практика использования индексов в базе данных позволяет заключить, что если в одной таблице установлено более 10 индексов, то это означает, что-либо недостаточно продумана структура базы данных (таблицы), либо недостаточно обоснованно определены вопросы обработки данных исходя из задач АИС.

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