Любицкий Ю.В. Базы данных
Подождите немного. Документ загружается.
таблицы, ключевые атрибуты сущности – первичным ключом таблицы. Для
каждого поля определяется допустимый тип данных и другие ограничения
целостности. Названия сущности и таблицы, атрибутов и полей могут совпадать,
если используемая СУБД не накладывает на них никаких ограничений (табл.
7.1):
Таблица 7.1
Магазины
Название Адрес Специализация
Светлый Мира, 14 Хозяйственные товары
Восток Запарина, 2 Продовольственные товары
Факел Фрунзе, 13 Хозяйственные товары
Если между двумя сущностями имеется связь «один к одному», а класс
принадлежности связи для обеих сущностей является обязательным, обе
сущности можно объединить в одну таблицу. Первичным ключом таблицы
может быть первичный ключ любой сущности. Например, имеются связанные
сущности Директор и Магазин. В каждом магазине есть директор, и каждый
директор руководит только одним магазином. Таблица, включающая атрибуты
сущностей Директор и Магазин, может выглядеть следующим образом (табл.
7.2):
Таблица 7.2
Магазины
Название Адрес Специализация Директор ИНН
Светлый Мира, 14 Хозяйственные товары Деев О.И. 27213456
Восток Запарина, 2 Промышленные товары Стогов П.И. 27243212
Факел Фрунзе, 13 Хозяйственные товары Репина О.Г. 27231217
Когда между сущностями имеется связь «один к одному», а класс
принадлежности связи для одной сущности является обязательным, а для другой
необязательным, для каждой сущности формируется отдельная таблица. К
таблице, сущность которой имеет обязательный класс принадлежности,
добавляется в качестве поля ключ таблицы с необязательным классом
принадлежности.
71
Рассмотрим связь между сущностями Магазин и Автомобиль.
Предположим, лишь некоторым магазинам («Светлый», «Восток») принадлежит
автомобиль (только один). У других магазинов («Факел») автомобиля нет (класс
принадлежности связи для сущности Магазин является необязательным).
Каждый автомобиль является собственностью некоторого магазина (класс
принадлежности связи для сущности Автомобиль является обязательным).
Таблица с информацией о магазинах будет идентична табл. 7.1, а таблица с
информацией об автомобилях будет иметь следующий вид (табл. 7.3):
Таблица 7.3
Автомобили
Номер Марка Водитель Адрес магазина
Х 123 МН ЗИЛ-130 Андреев Р.С. Мира, 14
Х 234 РТ ГАЗ-66 Реутов С.П. Запарина, 2
При связи между сущностями «один ко многим» в процессе формирования
таблиц решающую роль играет класс принадлежности сущности, находящейся
со стороны «много». Если он не является обязательным, следует создать три
таблицы. Две из них будут соответствовать каждой сущности, ключи сущностей
станут первичными ключами этих таблиц. Третья таблица будет связующей, в
нее должны входить первичные ключи связываемых таблиц.
В ситуации, когда класс принадлежности сущности, находящейся со стороны
«много», обязателен, достаточно создать две таблицы. Ключ таблицы,
находящейся со стороны «один», должен быть добавлен в таблицу,
находящуюся со стороны «много».
Рассмотрим связь «один ко многим» между сущностями Магазин и
Работник (см. рис. 15). Класс принадлежности связи для сущности Работник
является обязательным. Таблица с информацией о магазинах будет иметь вид,
идентичный табл. 7.1, таблица с информацией о работниках будет включать
ключ таблицы Магазин (табл. 7.4):
Таблица 7.4
Работники
ИНН ФИО Должность Адрес Адрес магазина
27212367 Фомин Л.М. продавец Боровая, 16 Мира, 14
27233319 Цыпин Л.Е. продавец Мира, 33 Запарина, 2
27254322 Гейт Ф.П. грузчик Осиновая, 5 Фрунзе, 13
27265413 Ревва С.Р. кассир Мира, 67 Мира, 14
72
Если между двумя сущностями имеется связь «многие ко многим»,
независимо от класса принадлежности связей этих сущностей, необходимо
сформировать три таблицы. Две таблицы соответствуют связываемым
сущностям, ключи этих сущностей становятся первичными ключами таблиц.
Третья таблица является связующей, в нее должны входить первичные ключи
обеих сущностей.
Рассмотрим связь «многие ко многим» между сущностями Продавец и
Товар. Таблица Продавцы может иметь структуру, совпадающую со
структурой таблицы Работники (см. табл. 7.4), таблица Товары будет иметь вид
(табл. 7.5):
Таблица 7.5
Товары
Артикул Название Цена, руб.
100 Туфли 5 000
200 Сапоги 7 000
300 Костюм 10 000
400 Костюм 8 000
Таблица Продажи позволяет связать таблицы Продавец и Товары (табл.
7.6). В результате по данным, хранящимся в трех связанных таблицах, можно
получить информацию о том, какие товары продает конкретный продавец.
Таблица 7.6
Продажи
ИНН Артикул
27212367 100
27212367 300
27233319 100
27233319 400
Рассмотренные правила преобразования ER-модели в логическую модель
базы данных достаточно просты, наглядны и позволяют получить хорошие
результаты. Более подробные сведения о данном процессе приводятся в книге [ 3
].
73
7.4. Нормализация отношений
Нормализация отношений
обеспечивает эффективность структур данных в
реляционной БД [ 12 ].
Этот процесс уменьшает избыточность данных (хранение одинаковых
данных в нескольких местах). В результате более рационально используется
внешняя память, уменьшается вероятность нарушения согласованности данных.
Нормализация представляет собой действия по последовательному
преобразованию исходной (ненормализованной) таблицы в нормализованные
отношения в первой нормальной форме (1НФ), 2НФ, 3НФ, нормальной форме
Бойса-Кодда (НФБК), 4НФ, 5НФ [ 2 ].
Основные свойства нормальных форм:
каждая следующая нормальная форма улучшает свойства предыдущей
нормальной формы;
при переходе к следующей нормальной форме свойства предыдущих
нормальных форм сохраняются.
Первая нормальная форма (1НФ)
Рассмотрим таблицу, в которой содержится информация о поставках товаров
торговому предприятию (табл. 7.7):
Таблица 7.7
Товары
Название
товара
Артикул Количество Цена, руб. Дата
поставки
Поставщик Способ
доставки
Костюм 500 100 10 000
10.12.05
Янтарь а/т
Сапоги 200 75 5 000 Факел ж/д
Туфли 100 120 4 000
11.12.05
Янтарь а/т
Костюм
500 100 10 000
300
50
5 000
12.12.05400
4 000
Остон ж/д
Туфли 100 100 Янтарь а/т
Такие таблицы нельзя включать в реляционную базу данных, так как для них
не соблюдается требование неделимости (атомарности) значений данных,
расположенных на пересечении любых строки и столбца (см. п. 1.2).
Приведем исходную таблицу к виду (табл. 7.8):
В данном параграфе рассматриваются фундаментальные проблемы теории
реляционных баз данных. Поэтому вместо терминов «таблица», «запись» и «поле»
используются соответственно понятия «отношение», «кортеж» и «атрибут».
74
Таблица 7.8
Товары
Название
товара
Артикул Количество Цена, руб. Дата
поставки
Поставщик Способ
доставки
Костюм 500 100 10 000 10.12.05 Янтарь а/т
Сапоги 200 75 5 000 10.12.05 Факел ж/д
Туфли 100 120 4 000 11.12.05 Янтарь а/т
Костюм 500 100 10 000 11.12.05 Янтарь а/т
Костюм 300 50 5 000 12.12.05 Янтарь а/т
Костюм 400 50 4 000 12.12.05 Остон ж/д
Туфли 100 100 4 000 12.12.05 Янтарь а/т
В результате будет получено отношение в первой нормальной форме.
Отношение находится в первой нормальной форме, если на пересечении
каждого столбца и каждой строки находятся только элементарные
(неделимые) значения атрибутов.
Вторая нормальная форма (2НФ).
Полученное отношение соответствует правилам реляционной теории, но
очевидно, что в нем содержится много избыточной информации (см. табл. 7.8).
Для этого отношения весьма трудоемкими являются операции обновления
данных. Например, при изменении названия какого-нибудь поставщика следует
просмотреть все кортежи отношения и внести необходимые исправления. Если
отношение состоит из нескольких тысяч кортежей, велика вероятность
возникновения ошибки. Сведения о заключении нового договора о поставках
некоторого товара (известны его название, артикул, цена и т. д.) можно будет
внести в базу данных только после того, как поступит первая партия товаров.
При удалении из отношения единственного кортежа с информацией о поставке,
сделанной поставщиком (например, возвращена партия бракованных товаров),
теряются все сведения об этом поставщике.
Причиной перечисленных проблем является функциональная зависимость
некоторых атрибутов отношения только от части составного первичного ключа.
Такая зависимость называется неполной функциональной.
По определению атрибут Y отношения R функционально зависит от
атрибута X отношения R, когда каждое значение X в отношении R в
каждый момент времени связано только с одним значением Y (атрибут X
функционально определяет атрибут Y) [ 2 ].
75
Составным первичным ключом отношения Товары являются поля Артикул
и Дата поставки. Атрибуты Название товара и Цена, руб. функционально
зависят только от атрибута Артикул, входящего в состав составного ключа. Для
устранения этой неполной функциональной зависимости можно разделить
исходное отношение на два отношения (получить его проекции) (табл. 7.9 и
7.10):
Таблица 7.9
Номенклатура товаров
Название
товара
Артикул Цена, руб. Поставщик Способ
доставки
Костюм 500 10 000 Янтарь а/т
Сапоги 200 5 000 Факел ж/д
Туфли 100 4 000 Янтарь а/т
Костюм 300 5 000 Янтарь а/т
Костюм 400 4 000 Остон ж/д
Таблица 7.10
Поставки
Артикул Количество Дата
поставки
500 100 10.12.05
200 75 10.12.05
100 120 11.12.05
500 100 11.12.05
300 50 12.12.05
400 50 12.12.05
100 100 12.12.05
Разделение отношения Товары на отношения Номенклатура товаров и
Поставки позволяет существенно уменьшить избыточность данных.
Значительно проще и надежнее теперь выполняются операции обновления
данных. Например, при изменении названия поставщика соответствующие
исправления необходимо внести только в отдельные кортежи отношения
Номенклатура товаров. Число исправляемых кортежей будет намного меньше,
чем в отношении Товары, так как оно равно количеству разных товаров, а не
общей численности партий товаров, поступающих от данного поставщика.
Информацию о заключении нового договора о поставках товаров можно вносить
76
в отношение Номенклатура товаров, не дожидаясь момента, когда поступит
первая партия товара. Удаление второго кортежа из отношения Поставки не
помешает сохранить в базе данных информацию о том, что торговым
предприятием заключен договор с организацией Факел о поставке сапог с
артикулом 200, ценой 5 000 рублей с доставкой этих товаров железнодорожным
транспортом (второй кортеж отношения Номенклатура товаров).
Разделение отношения Товары на отношения Номенклатура товаров и
Поставки не приводит к потере информации. Все необходимые сведения могут
быть получены из этих отношений после их связывания по ключевым полям.
В результате выполненных действий будет получено отношение во второй
нормальной форме.
Отношение находится во второй нормальной форме, если оно
находится в первой нормальной форме, и каждый неключевой атрибут (т.
е. не являющийся составной частью первичного ключа) функционально
полно зависит от первичного ключа.
Третья нормальная форма (3НФ)
Первичным ключом отношения Номенклатура товаров является атрибут
Артикул. Ключевой атрибут функционально связан со всеми не ключевыми
атрибутами. Первичный ключ является простым, следовательно,
рассматриваемое отношение находится во второй нормальной форме. Тем не
менее прослеживается некоторая избыточность данных. Например, информация
о том, что организация Янтарь доставляет товары только железнодорожным
транспортом, повторяется в отношении Номенклатура товаров трижды.
Это является следствием того, что имеется функциональная зависимость
между неключевыми атрибутами: атрибут Способ доставки функционально
зависит от атрибута Поставщик (см. табл. 7.9). Такие зависимости называются
транзитивными (переходными).
Для устранения транзитивной зависимости в отношении Номенклатура
товаров, данное отношение необходимо разделить на два отношения (табл. 7.11
и 7.12):
Таблица 7.11
Договоры
Название
товара
Артикул Цена, руб. Поставщик
Костюм 500 10 000 Янтарь
77
Сапоги 200 5 000 Факел
Туфли 100 4 000 Янтарь
Костюм 300 5 000 Янтарь
Костюм 400 4 000 Остон
Таблица 7.12
Доставка
Поставщик Способ
доставки
Янтарь а/т
Факел ж/д
Остон ж/д
В результате выполненных действий исключена избыточность данных –
каждый факт хранится в базе данных только один раз. Облегчается выполнение
операций обновления данных. Например, если организация Янтарь переходит
на доставку грузов водным транспортом, достаточно внести необходимые
изменения только в один кортеж отношения Доставка (см. табл. 7.12).
Отношения Договоры и Доставка находятся в третьей нормальной форме.
Отношение находится в третьей нормальной форме, если оно
находится во второй нормальной форме и каждый его не ключевой атрибут
непосредственно (не транзитивно) зависит от первичного ключа.
В большинстве случаев достижение третьей нормальной формы считается
достаточным для реальных проектов баз данных [ 12 ], однако в теории
нормализации существуют нормальные формы высших порядков (НФБК, 4НФ,
5НФ), некоторые из которых связаны уже не с функциональными зависимостями
между атрибутами отношений, а отражают более тонкие вопросы смыслового
содержания предметной области [ 4 ]. Подробную информацию о нормальных
формах высших порядков можно найти в книгах [ 1, 2, 4 – 6, 11, 14 ].
Уровень нормализации отношения определяется смысловым содержанием
составляющих его данных. Невозможно по схеме отношения (его структуре) или
абстрактно рассматриваемым данным оценить, в какой нормальной форме
находится отношение. Для решения этой задачи необходимо идентифицировать
первичный и вероятные ключи отношения и выполнить анализ всех
зависимостей между данными.
78
7.5. Автоматизированные технологии проектирования баз данных
Средства автоматизации проектирования и разработки информационных
систем, в том числе и банков данных, появились относительно недавно (первая
версия CASE-системы фирмы Oracle была представлена в 1989 г.) [ 3 ].
Термин «CASE» (Computer Aided Software Engineering) дословно
переводится как разработка программного обеспечения с помощью компьютера.
В CASE-системы входят программные средства, позволяющие выполнять анализ
предметной области и построение ее модели, проектирование баз данных,
разработку приложений, генерацию кодов, тестирование и т.д.
CASE-системы, применяемые для проектирования баз данных, могут не
зависеть от СУБД или встраиваться в них. Независимые системы не входят в
состав конкретной СУБД и обычно поддерживают несколько форматов баз
данных через интерфейс ODBC. В качестве примеров таких систем можно
привести S-Designor (PowerDesignor) (фирмы SDP, Powersoft), Erwin
(LogicWorks), Silverrun (Computer Systems Advisers Inc.) [ 15 ]. Встроенные
CASE-системы в основном поддерживают формат баз данных СУБД, составной
частью которой они являются. Пример встроенной системы – Designer/2000
(СУБД Oracle одноименной фирмы) [ 15 ].
Рассмотрим некоторые CASE-системы.
S-Designor (PowerDesigner). Эта система позволяет с помощью графических
средств в некоторой степени автоматизировать процесс проектирования
реляционных баз данных. Система работает с СУБД Oracle, MS Access, Ingress,
Informix, Sybase, MS SQL Server и т. д.
Предварительно создается концептуальная модель базы данных в виде ER-
диаграммы (см. рис. 15). В рамках этого процесса можно задать некоторые
ограничения целостности для полей таблиц (область допустимых значений,
значение по умолчанию и т. д.).
На основе концептуальной модели базы данных строится концептуальная
схема (логическая модель) базы данных. Имеющиеся сущности преобразуются в
таблицы, идентификаторы сущностей становятся ключами таблиц. При наличии
между двумя сущностями связи «многие ко многим», автоматически создается
дополнительная таблица, связывающая исходные таблицы (см. пример в п. 7.3).
База данных с помощью системы может быть создана двумя способами. В
рамках первого из них необходимо подключиться к работающему серверу СУБД
через интерфейс ODBC. Другой способ – формирование текстовых файлов
79
(пакетов) SQL-операторов по созданию структуры БД. База данных создается
после обработки этих файлов СУБД [ 15 ].
В версии PowerDesigner 6.0 реализовано проектирование многомерных
информационных моделей для хранилищ данных (см. Заключение) [ 3 ].
Erwin. Данная система в целом близка по функциональным возможностям
системе S-Designor (PowerDesigner), но она менее универсальна и
поддерживается меньшим числом СУБД. В системе имеются ограничения на
размеры модели (не более 500 сущностей и 1500 атрибутов) [ 3 ].
Silverrun. Система работает с СУБД Oracle, Informix, Ingress, MS SQL Server
и т. д. С помощью системы можно строить два вида моделей: функциональные
(в виде диаграмм потоков данных) и информационные (диаграммы типа
«сущность – связь», применяемые для создания схем баз данных).
Система включает встроенную экспертную систему, приводящую неполную
и некорректную исходную информацию к виду, необходимому для построения
реляционной базы данных [ 15 ].
Designer /2000 . Эта система является встроенной в СУБД Oracle.
Система реализует моделирование и анализ деятельности организации,
разработку концептуальных (функциональной и информационной) моделей
предметной области, проектирование приложений и создание программ [ 15 ].
Процессы моделирования поддерживаются графическими редакторами и
средствами мультимедиа, включая звук, видео и анимацию [ 15 ]. Результатом
моделирования базы данных является формирование ее структуры.
Определяются набор необходимых таблиц, состав полей каждой таблицы,
ключевые поля и индексы, ограничения целостности базы данных.
Для создания программ используются генераторы программного кода
(серверной части и клиентских частей), позволяющие автоматизировать этот
процесс и значительно сократить время разработки.
Опыт применения CASE-систем для проектирования баз данных позволяет
сделать следующие выводы [ 3 ]:
1. CASE-системы позволяют повысить качество создаваемых
информационных систем и программ, облегчают и ускоряют их разработку.
2. Наиболее полезными CASE-системы являются на начальных стадиях
проектирования.
3. Современные CASE-системы ориентированы на квалифицированного
пользователя, так как для их использования необходимы знания теории баз
данных и принципов их проектирования.
80