CК Раскрыть особенности внешнего (финансового) анализа

Теоретическая часть. Реляционная модель данных Когда в предыдущих разделах мы говорили об основных понятиях реляционных баз данных, мы не опирались на какую-либо конкретную реализацию. Эти рассуждения в равной степени относились к любой системе, при построении которой использовался реляционный подход. Другими словами, мы использовали понятия так называемой реляционной модели данных. Модель данных описывает некоторый набор родовых понятий и признаков, которыми должны обладать все конкретные

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

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

реляционного подхода: структурной части, манипуляционной части и целостной части. В структурной части модели фиксируется, что единственной структурой данных, используемой в реляционных БД, является нормализованное n-арное отношение. По сути дела, в предыдущих двух разделах этой лекции мы рассматривали именно понятия и свойства структурной составляющей реляционной модели. В манипуляционной части модели утверждаются два фундаментальных механизма манипулирования реляционными

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

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

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

Второе требование называется требованием целостности по ссылкам и является несколько более сложным. Очевидно, что при соблюдении нормализованности отношений сложные сущности реального мира представляются в реляционной БД в виде нескольких кортежей нескольких отношений. Например, представим, что нам требуется представить в реляционной базе данных сущность ОТДЕЛ с атрибутами ОТД_НОМЕР (номер отдела), ОТД_КОЛ (количество сотрудников) и

ОТД_СОТР (набор сотрудников отдела). Для каждого сотрудника нужно хранить СОТР_НОМЕР (номер сотрудника), СОТР_ИМЯ (имя сотрудника) и СОТР_ЗАРП (заработная плата сотрудника). Как мы вскоре увидим, при правильном проектировании соответствующей БД в ней появятся два отношения: ОТДЕЛЫ (ОТД_НОМЕР, ОТД_КОЛ) (первичный ключ - ОТД_НОМЕР) и СОТРУДНИКИ (СОТР_НОМЕР,

СОТР_ИМЯ, СОТР_ЗАРП, СОТР_ОТД_НОМ) (первичный ключ - СОТР_НОМЕР). Как видно, атрибут СОТР_ОТД_НОМ появляется в отношении СОТРУДНИКИ не потому, что номер отдела является собственным свойством сотрудника, а лишь для того, чтобы иметь возможность восстановить при необходимости полную сущность ОТДЕЛ. Значение атрибута СОТР_ОТД_НОМ в любом кортеже отношения

СОТРУДНИКИ должно соответствовать значению атрибута ОТД_НОМ в некотором кортеже отношения ОТДЕЛЫ. Атрибут такого рода называется внешним ключом, поскольку его значения однозначно характеризуют сущности, представленные кортежами некоторого другого отношения (т.е. задают значения их первичного ключа). Говорят, что отношение, в котором определен внешний ключ, ссылается на соответствующее отношение, в котором такой же атрибут является первичным ключом.

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

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

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

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

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

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

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

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

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

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

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

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

нормальная форма (1NF); вторая нормальная форма (2NF); третья нормальная форма (3NF); нормальная форма Бойса-Кодда (BCNF) (правильнее было бы считать эту нормальную форму третьей, однако по историческим причинам третья ступень оказалась занятой к моменту изобретения BCNF, из-за чего она и получила нестандартное название); четвертая нормальная форма (4NF); пятая нормальная форма, или нормальная форма проекции-соединения (5NF или

PJ/NF). Основные свойства нормальных форм: каждая следующая нормальная форма в некотором смысле улучшает свойства предыдущей; при переходе к следующей нормальной форме свойства предыдущих нормальных форм сохраняются. В основе классического процесса проектирования лежит метод нормализации, который опирается на декомпозицию (на основе проекции) отношения, находящегося в предыдущей нормальной форме, в два или более отношения, удовлетворяющих требованиям следующей нормальной формы.

Наиболее важные на практике нормальные формы отношений основываются на фундаментальном в теории реляционных баз данных понятии функциональной зависимости. Для дальнейшего изложения нам потребуются несколько определений. Определение 1: Функциональная зависимость (functional dependence - FD). В отношении R атрибут Y функционально зависит от атрибута X (X и Y могут быть составными атрибутами, т.е. реально состоять из нескольких атомарных атрибутов)

в том и только в том случае, если каждому значению X соответствует в точности одно значение Y: R.X -> R.Y. Замечание: Термин "функциональная зависимость" не случаен, поскольку в точности соответствует математическому понятию функции. Определение 2: Полная функциональная зависимость. Функциональная зависимость R.X -> R.Y называется полной, если атрибут

Y не зависит функционально от любого точного подмножества X (точным подмножеством множества X называется любое его подмножество, не совпадающее с X). Определение 3: Транзитивная функциональная зависимость. Функциональная зависимость R.X -> R.Y называется транзитивной, если существует такой атрибут Z, что имеются функциональные зависимости R.X ->

R.Z и R.Z -> R.Y. Определение 4: Возможный ключ (alternative key). Возможным ключом отношения называется его атомарный или составной атрибут, значения которого полностью функционально определяют значения всех остальных атрибутов отношения. Определение 5: Неключевой атрибут. Неключевым атрибутом называется любой атрибут отношения, не входящий в состав первичного ключа. Определение 6: Взаимно независимые атрибуты.

Два или более атрибута называются взаимно независимыми, если ни один из этих атрибутов не является функционально зависимым от других атрибутов. Вторая нормальная форма Рассмотрим следующий пример схемы отношения: СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ (СОТР_НОМЕР, СОТР_ЗАРП, ОТД_НОМЕР, ПРО_НОМЕР, СОТР_ЗАДАН) Первичный ключ: СОТР_НОМЕР, ПРО_НОМЕР

Функциональные зависимости: СОТР_НОМЕР -> СОТР_ЗАРП СОТР_НОМЕР -> ОТД_НОМЕР ОТД_НОМЕР -> СОТР_ЗАРП СОТР_НОМЕР, ПРО_НОМЕР -> СОТР_ЗАДАН Как видно, хотя первичным ключом является составной атрибут СОТР_НОМЕР, ПРО_НОМЕР, атрибуты СОТР_ЗАРП и ОТД_НОМЕР функционально зависят от части первичного ключа, т.е. атрибута СОТР_НОМЕР. В результате, мы не сможем вставить в отношение

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

сотрудника, или получим несогласованный результат. Такие неприятные явления называются аномалиями схемы отношения. Они устраняются путем нормализации. Определение 7. Вторая нормальная форма. Отношение R находится во второй нормальной форме (2NF) в том и только в том случае, когда находится в 1NF и каждый неключевой атрибут полностью зависит от первичного ключа.

Можно произвести следующую декомпозицию отношения СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ в два отношения СОТРУДНИКИ-ОТДЕЛЫ и СОТРУДНИКИ-ПРОЕКТЫ: СОТРУДНИКИ-ОТДЕЛЫ (СОТР_НОМЕР, СОТР_ЗАРП, ОТД_НОМЕР) Первичный ключ: СОТР_НОМЕР Функциональные зависимости: СОТР_НОМЕР -> СОТР_ЗАРП СОТР_НОМЕР -> ОТД_НОМЕР

ОТД_НОМЕР -> СОТР_ЗАРП СОТРУДНИКИ-ПРОЕКТЫ (СОТР_НОМЕР, ПРО_НОМЕР, СОТР_ЗАДАН) Первичный ключ: СОТР_НОМЕР, ПРО_НОМЕР Функциональная зависимость: СОТР_НОМЕР, ПРО_НОМЕР -> СОТР_ЗАДАН Каждое из этих двух отношений находится в 2NF, и в них устранены отмеченные выше аномалии (легко проверить, что все упомянутые выше операции выполняются без проблем).

Третья нормальная форма Рассмотрим еще раз отношение СОТРУДНИКИ-ОТДЕЛЫ, находящееся в 2NF. Заметим, что функциональная зависимость СОТР_НОМЕР СОТР_ЗАРП является транзитивной; она является следствием (в смысле математической логики) функциональных зависимостей СОТР_НОМЕР ОТД_НОМЕР и ОТД_НОМЕР СОТР_ЗАРП. Другими словами, заработная плата сотрудника на самом деле является характеристикой

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

Чтобы согласованным образом изменить заработную плату отдела, мы будем вынуждены предварительно найти все кортежи, описывающие сотрудников этого отдела. Т.е. в отношении СОТРУДНИКИ-ОТДЕЛЫ по-прежнему существуют аномалии. Их можно устранить путем дальнейшей нормализации. Определение 8. Третья нормальная форма. Отношение R находится в третьей нормальной форме (3NF) в том

и только в том случае, если находится в 2NF и каждый неключевой атрибут нетранзитивно зависит от первичного ключа. Эквивалентным определением (доказательство эквивалентности мы оставляем читателю) является следующее: Определение 9. Третья нормальная форма (альтернативное определение). Отношение R находится в третьей нормальной форме (3NF) в том и только в том случае, если все неключевые атрибуты R взаимно независимы и полностью зависят от первичного ключа.

Можно произвести декомпозицию отношения СОТРУДНИКИ-ОТДЕЛЫ в два отношения СОТРУДНИКИ и ОТДЕЛЫ: СОТРУДНИКИ (СОТР_НОМЕР, ОТД_НОМЕР) Первичный ключ: СОТР_НОМЕР Функциональная зависимость: СОТР_НОМЕР -> ТД_НОМЕР ОТДЕЛЫ (ОТД_НОМЕР, СОТР_ЗАРП) Первичный ключ: ОТД_НОМЕР Функциональная зависимость:

ОТД_НОМЕР -> СОТР_ЗАРП Каждое из этих двух отношений находится в 3NF и свободно от отмеченных аномалий. На практике третья нормальная форма схем отношений является достаточной в большинстве случаев и приведением к третьей нормальной форме процесс проектирования реляционной базы данных обычно заканчивается. Однако иногда полезно продолжить процесс нормализации. Нормальная форма Бойса-Кодда Рассмотрим следующий пример схемы отношения:

СОТРУДНИКИ-ПРОЕКТЫ (СОТР_НОМЕР, СОТР_ИМЯ, ПРО_НОМЕР, СОТР_ЗАДАН) Возможные ключи (обратите внимание, что на этой стадии нормализации во внимание принимаются существование возможных ключей): СОТР_НОМЕР, ПРО_НОМЕР СОТР_ИМЯ, ПРО_НОМЕР Функциональные зависимости: СОТР_НОМЕР -> СОТР_ИМЯ СОТР_НОМЕР -> ПРО_НОМЕР СОТР_ИМЯ ->

СОТР_НОМЕР СОТР_ИМЯ -> ПРО_НОМЕР СОТР_НОМЕР, ПРО_НОМЕР -> СОТР_ЗАДАН СОТР_ИМЯ, ПРО_НОМЕР -> СОТР_ЗАДАН В этом примере мы предполагаем, что личность сотрудника полностью определяется как его номером, так и именем (это снова не очень жизненное предположение, но достаточное для примера). Независимо от того, какой из возможных ключей выбран в качестве первичного ключа, эта схема находится в 3NF. Однако тот факт, что имеются функциональные зависимости атрибутов

отношения от атрибута, являющегося частью первичного ключа, приводит к аномалиям. Например, для того, чтобы изменить имя сотрудника с данным номером согласованным образом, нам потребуется модифицировать все кортежи, включающие его номер. Определение 10. Детерминант. Детерминантом называется любой атрибут, от которого полностью функционально зависит некоторый другой атрибут. Определение 11. Нормальная форма Бойса-

Кодда. Отношение R находится в нормальной форме Бойса-Кодда (BCNF) в том и только в том случае, если каждый детерминант является возможным ключом. Замечание: Легко заметить, что если в отношении имеется только один возможный ключ (являющийся первичным ключом), то это определение становится эквивалентным определению третьей нормальной формы. Очевидно, что это требование не выполнено для отношения

СОТРУДНИКИ-ПРОЕКТЫ. Можно произвести его декомпозицию к отношениям СОТРУДНИКИ и СОТРУДНИКИ-ПРОЕКТЫ: СОТРУДНИКИ (СОТР_НОМЕР, СОТР_ИМЯ) Возможные ключи: СОТР_НОМЕР СОТР_ИМЯ Функциональные зависимости: СОТР_НОМЕР -> СОТР_ИМЯ СОТР_ИМЯ -> СОТР_НОМЕР СОТРУДНИКИ-ПРОЕКТЫ (СОТР_НОМЕР, ПРО_НОМЕР, СОТР_ЗАДАН)

Возможный ключ: СОТР_НОМЕР, ПРО_НОМЕР Функциональные зависимости: СОТР_НОМЕР, ПРО_НОМЕР -> СОТР_ЗАДАН Возможна альтернативная декомпозиция, если выбрать за основу СОТР_ИМЯ. В обоих случаях получаемые отношения СОТРУДНИКИ и СОТРУДНИКИ-ПРОЕКТЫ находятся в BCNF, и им не свойственны отмеченные аномалии. Четвертая нормальная форма Рассмотрим пример следующей схемы отношения:

ПРОЕКТЫ (ПРО_НОМЕР, ПРО_СОТР, ПРО_ЗАДАН) Отношение ПРОЕКТЫ содержит номера проектов, для каждого проекта - список сотрудников, которые могут выполнять проект, и список заданий, предусматриваемых проектом. Сотрудники могут участвовать в нескольких проектах, и разные проекты могут включать одинаковые задания. Каждый кортеж отношения связывает некоторый проект с сотрудником, участвующим в этом проекте, и заданием,

который сотрудник выполняет в рамках данного проекта (мы предполагаем, что любой сотрудник, участвующий в проекте, выполняет все задания, предусмотренные этим проектом). По причине сформулированных выше условий единственным возможным ключом отношения является составной атрибут ПРО_НОМЕР, ПРО_СОТР, ПРО_ЗАДАН, и нет никаких других детерминантов. Следовательно, отношение ПРОЕКТЫ находится в BCNF.

Но при этом оно обладает недостатками: если, например, некоторый сотрудник присоединяется к данному проекту, необходимо вставить в отношение ПРОЕКТЫ столько кортежей, сколько заданий в нем предусмотрено. Определение 12. Многозначная зависимость. В отношении R (A, B, C) существует многозначная зависимость (multi-valued dependence - MVD) R.A ->-> R.B в том и только в том случае, если множество значений

B, соответствующее паре значений A и C, зависит только от A и не зависит от С. В отношении ПРОЕКТЫ существуют следующие две многозначные зависимости: ПРО_НОМЕР ->-> ПРО_СОТР ПРО_НОМЕР ->-> ПРО_ЗАДАН Нетрудно показать, что в общем случае в отношении R (A, B, C) существует многозначная зависимость R.A ->->

R.B в том и только в том случае, когда существует многозначная зависимость R.A ->-> R.C. Поэтому далее мы употребляем обозначение A ->-> B | C в том смысле, что одновременно существуют MVD A ->-> B и A ->-> C. Дальнейшая нормализация отношений, подобных отношению ПРОЕКТЫ, основывается на следующей теореме: Теорема

Фейджина Отношение R (A, B, C) можно спроецировать без потерь в отношения R1 (A, B) и R2 (A, C) в том и только в том случае, когда существует MVD A ->-> B | C. Под проецированием без потерь понимается такой способ декомпозиции отношения, при котором исходное отношение полностью и без избыточности восстанавливается путем естественного соединения полученных отношений. Определение 13. Четвертая нормальная форма.

Отношение R находится в четвертой нормальной форме (4NF) в том и только в том случае, если в случае существования многозначной зависимости A "" B все остальные атрибуты R функционально зависят от A. В нашем примере можно произвести декомпозицию отношения ПРОЕКТЫ в два отношения ПРОЕКТЫ-СОТРУДНИКИ и ПРОЕКТЫ-

ЗАДАНИЯ: ПРОЕКТЫ-СОТРУДНИКИ (ПРО_НОМЕР, ПРО_СОТР) ПРОЕКТЫ-ЗАДАНИЯ (ПРО_НОМЕР, ПРО_ЗАДАН). Оба эти отношения находятся в 4NF и свободны от отмеченных аномалий. Пятая нормальная форма Во всех рассмотренных до этого момента нормализациях производилась декомпозиция одного отношения в два. Иногда это сделать не удается, но возможна декомпозиция в большее число отношений, каждое из которых обладает лучшими свойствами. Рассмотрим, например, отношение

СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ (СОТР_НОМЕР, ОТД_НОМЕР, ПРО_НОМЕР) Предположим, что один и тот же сотрудник может работать в нескольких отделах и в каждом отделе принимать участие в нескольких проектах. Первичным ключом этого отношения является полная совокупность его атрибутов, отсутствуют функциональные и многозначные зависимости. Поэтому отношение находится в 4NF. Однако в нем могут существовать аномалии, которые можно устранить

путем декомпозиции в три отношения. Определение 14. Зависимость соединения. Отношение R (X, Y, Z) удовлетворяет зависимости соединения * (X, Y, Z) в том и только в том случае, когда R восстанавливается без потерь путем соединения своих проекций на X, Y, Z. Определение 15. Пятая нормальная форма. Отношение R находится в пятой нормальной форме (нормальной форме проекции-соединения -

PJ/NF) в том и только в том случае, когда любая зависимость соединения в R следует из существования некоторого возможного ключа в R. Введем следующие имена составных атрибутов: СО = {СОТР_НОМЕР, ОТД_НОМЕР} СП = {СОТР_НОМЕР, ПРО_НОМЕР} ОП = {ОТД_НОМЕР, ПРО_НОМЕР} Предположим, что в отношении СОТРУДНИКИ-

ОТДЕЛЫ-ПРОЕКТЫ существует зависимость соединения: * (СО, СП, ОП) На примерах можно легко показать, что при вставках и удалениях кортежей могут возникнуть проблемы. Их можно устранить путем декомпозии исходного отношения в три новых отношения: СОТРУДНИКИ-ОТДЕЛЫ (СОТР_НОМЕР, ОТД_НОМЕР) СОТРУДНИКИ-ПРОЕКТЫ (СОТР_НОМЕР, ПРО_НОМЕР) ОТДЕЛЫ-ПРОЕКТЫ (ОТД_НОМЕР,

ПРО_НОМЕР) Пятая нормальная форма - это последняя нормальная форма, которую можно получить путем декомпозиции. Ее условия достаточно нетривиальны, и на практике 5NF не используется. Заметим, что зависимость соединения является обобщением как многозначной зависимости, так и функциональной зависимости. Семантическое моделирование данных, ER-диаграммы Широкое распространение реляционных СУБД и их использование в самых разнообразных приложениях показывает,

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

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

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

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

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

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

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

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

они более мощны, а в некоторых - более слабы). Семантическая модель Entity-Relationship (Сущность-Связи) Далее мы кратко рассмотрим некоторые черты одной из наиболее популярных семантических моделей данных - модель "Сущность-Связи" (часто ее называют кратко ER-моделью). На использовании разновидностей ER-модели основано большинство современных подходов к проектированию

баз данных (главным образом, реляционных). Модель была предложена Ченом (Chen) в 1976 г. (оригинальная статья Чена опубликована в третьем номере нашего журнала). Моделирование предметной области базируется на использовании графических диаграмм, включающих небольшое число разнородных компонентов. В связи с наглядностью представления концептуальных схем баз данных ER-модели получили широкое распространение в системах

CASE, поддерживающих автоматизированное проектирование реляционных баз данных. Среди множества разновидностей ER-моделей одна из наиболее развитых применяется в системе CASE фирмы ORACLE. Ее мы и рассмотрим. Точнее говоря, мы сосредоточимся на структурной части этой модели. Основные понятия и определения Основными понятиями ER-модели являются сущность, связь и атрибут. Сущность - это реальный или представляемый объект, информация

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

АЭРОПОРТ с примерными объектами Шереметьево и Хитроу: Каждый экземпляр сущности должен быть отличим от любого другого экземпляра той же сущности (это требование в некотором роде аналогично требованию отсутствия кортежей-дубликатов в реляционных таблицах или требованию идентифицируемости объектов (object identity) в объектно-ориентированных системах). Связь - это графически изображаемая ассоциация, устанавливаемая между двумя сущностями.

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

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

правилам связывания. В изображенном ниже примере связь между сущностями БИЛЕТ и ПАССАЖИР связывает билеты и пассажиров. При том конец сущности с именем "для" позволяет связывать с одним пассажиром более одного билета, причем каждый билет должен быть связан с каким-либо пассажиром. Конец сущности с именем "имеет" означает, что каждый билет может принадлежать только одному пассажиру, причем пассажир не обязан иметь хотя бы один билет.

Лаконичной устной трактовкой изображенной диаграммы является следующая: Каждый БИЛЕТ предназначен для одного и только одного ПАССАЖИРА; Каждый ПАССАЖИР может иметь один или более БИЛЕТОВ. На следующем примере изображена рекурсивная связь, связывающая сущность ЧЕЛОВЕК с ней же самой. Конец связи с именем "сын" определяет тот факт, что у одного отца

может быть более чем один сын. Конец связи с именем "отец" означает, что не у каждого человека могут быть сыновья. Лаконичной устной трактовкой изображенной диаграммы является следующая: Каждый ЧЕЛОВЕК является сыном одного и только одного ЧЕЛОВЕКА; Каждый ЧЕЛОВЕК может являться отцом для одного или более ЛЮДЕЙ ("ЧЕЛОВЕКОВ"). Атрибутом сущности является любая деталь, которая служит для уточнения,

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

Как и в реляционных схемах баз данных, в ER-схемах вводится понятие нормальных форм, причем их смысл очень близко соответствует смыслу реляционных нормальных форм. Заметим, что формулировки нормальных форм ER-схем делают более понятным смысл нормализации реляционных схем. Мы приведем только очень краткие и неформальные определения трех первых нормальных форм. В первой нормальной форме ER-схемы устраняются повторяющиеся атрибуты или группы атрибутов, т.е. производится

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

Мы остановились только на самых основных и наиболее очевидных понятиях ER-модели данных. К числу более сложных элементов модели относятся следующие: Подтипы и супертипы сущностей. Как в языках программирования с развитыми типовыми системами (например, в языках объектно-ориентированного программирования), вводится возможность наследования типа сущности, исходя из одного или нескольких супертипов. Интересные нюансы связаны с необходмостью графического

изображения этого механизма. Связи "many-to-many". Иногда бывает необходимо связывать сущности таким образом, что с обоих концов связи могут присутствовать несколько экземпляров сущности (например, все члены кооператива сообща владеют имуществом кооператива). Для этого вводится разновидность связи "многие-со-многими". Уточняемые степени связи. Иногда бывает полезно определить возможное количество экземпляров сущности,

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

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

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

В данной базе данных необходимо хранить следующую информацию: Информация о месте и времени приобретения товара, эта информация необходима для того, чтобы всегда можно было быстро найти поставщика и обратиться к нему в случае каких-либо проблем с товаром; Информация о составе товара; Информация о сотрудниках, работающих и отвечающих за товар, а также расписание их работы; Информация о перемещении товара со склада;

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

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

Персонал Информация о людях, работающих на данной организации, такая как ФИО, должность, день рождения, оклад и их расписание. Расписание Расписание работы персонала. Местоположение Физическое положение товара, наименование склада и дата поступления. А также история перемещения товара. Покупатель В данной сущности будет хранится информация о покупателе,

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

Таблица 2. Атрибуты. Сущность Атрибут Описание Товар Код товара Код товара. Наименование Некоторое текстовое обозначение. Состав Дата приобретения Дата поступления данной части, дата приобретения партии состоит из даты приобретения и какой то части. Поставщик Наименование поставщика, у которого был приобретен товар. Дата Дата ветеринарного освидетель-ствования. Ветеринарное освидетельствование

Дата Дата прохождения. Код товара Наименование товара прошедшее вет.освидетельствование Персонал Ф.И.О. Фамилия, имя и отчество сотрудника Должность Должность сотрудника. Табельный номер. День рождения Таб № ДР ЗП Заработная плата сотрудника, согласно должности. Расписание Таб № Таб. № сотрудника Код товара Код товара за который несет ответственность сотрудник

Время День День недели. Начало Время начала работы сотрудника Конец Время окончания работы сотрудника Местоположение Дата перемещения Дата прибытия товара на склад Склад Склад, на котором находится товар Покупатель Наименование покупателя Некоторое текстовое пояснение Код покупателя Код покупателя

Характеристики Юридический адрес, ИНН, Связь (relationship) - это ассоциация, установленная между несколькими сущностями. Таблица 3. Связи. Сущность Связь Сущность Описание Состав принадлежит Товар Каждый товар состоит из нескольких частей. Персонал Работает с Товар С определенным товаром может работать несколько человек: экспедиторы, грузчики. Товар находится Местоположение Товар не находится статически на одном складе, он может перемещаться

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

Работает по Расписание Данная связь позволяет просмотреть расписание работы конкретного человека. Диаграммы сущность – связь. Состав - принадлежит – товар. Каждый товар состоит из нескольких частей. Эта связь вида много к одному (n : 1) , так как «товар» всегда состоит из частей. Сущность «Состав» имеет обязательный класс принадлежности, так как «товар» обязательно состоит из «состав», в свою очередь, сущность «состав» имеет необязательный

класс принадлежности, т. к. «состав» не обязательно входят в «товар». Товар – находится – Местоположение. Товар не находится статически на одном складе, он может перемещаться по складам. Доступ к информации о его перемещениях должен осуществляться из сущности «товар». Эта связь вида много к одному (n : 1) , так как «товар» может несколько раз перемещаться по складам. Сущность «товар» имеет обязательный класс принадлежности, так как «товар» обязательно находится в каком-

то «Местоположении», в свою очередь, сущность «Местоположение» является зависимой от сущности «товар», так как в случае исчезновения товара (списывания) удаляется и история о его перемещениях. Товар – прошло –Вет.освидетельствование. Данная связь необходима для просмотра, когда товар прошло вет.свидетельство. Эта связь вида один ко многим (1 : n) , так как «товар» может несколько раз подвергаться «вет.освидетельствование». Сущность «вет.освидетельствование» имеет обязательный класс принадлежности,

так как «товар» обязательно проходит «вет.освидетельствование». Состав – прошло –Вет.освидетельствование. Данная связь необходима для просмотра, когда товар проходил вет.освидетельствование. Эта связь вида один ко многим (1 : n) , так как «состав» может несколько раз подвергаться «вет.освидетельствование». Сущность «вет.освидетельствование» имеет обязательный класс принадлежности. так как «Состав» обязательно проходит «вет.освидетельствование».

Время – принадлежит – Расписание. Эта связь вида много к одному (n : 1), так как «персонал» может работать несколько разделённых промежутков времени в течение одного дня. Сущность «Расписание» имеет обязательный класс принадлежности, так как обязательно есть промежуток времени, в течение которого человек работает. Сущность «время» также имеет обязательный класс принадлежности. Товар– продается – покупатель. Чтобы знать, какой покупатель приобретает данный товар, необходимо просматривать

сущность «Покупатель». Эта связь вида много к одному (n : 1) , так как «покупатель» представляется как множество контрагентов. Сущность «товар» имеет обязательный класс принадлежности, так как хотя бы один товар продается. В свою очередь, сущность «покупатель» имеет необязательный класс принадлежности, т. к. «товар» не обязательно покупается «покупателем». Диаграмма сущность - связь.

Построение первичного реляционного отношения. Реляционная модель данных позволяет достаточно наглядно представить таблицы базы данных. Здесь будут приведены первичные таблицы, которые в дальнейшем будут улучшены, или доказано, что они не нуждаются в дальнейшем улучшении. Товар Код.товара Наименование Составные части Код.товара Дата приобретения Физ.хар. Поставщик Код.тов. Вет.свидетельство

Код во Код товара Дата Адрес Персонал Таб. № Ф.И.О. З.П. Д.Р. Должность Расписание Код расп. Код.товара Таб. № Время Код время Код расп. День недели Начало Конец Местоположение Код мест Код.товара Дата перемещения Склад Физ.хар. Покупатель Код покупателя Наименование

Атрибут В этих отношениях больше атрибутов, чем было описано выше, так как пришлось добавить некоторые ключевые атрибуты, такие как Код во, Код мест и другие. При использовании универсального отношения возникает несколько проблем: 1.Избыточность. Данные практически некоторых столбцов многократно повторяются. Повторяются и некоторые наборы данных. 2.Потенциальная противоречивость (аномалии обновления).

Вследствие избыточности можно заменить одного проживающего или работающего другим и если не изменить фамилию в других строках то получается противоречивость. 3.Аномалии включения. В БД не может быть записан проживающего или работающего, если они не занимают какую-нибудь должность. 4.Аномалии удаления. Обратная проблема возникает при необходимости убрать одну из должностей в вузе или в общежитии, которую решили убрать из вакансий, этим мы удаляем любые сведения

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

НФ, если значения всех его атрибутов атомарно. После проведения анализа исходного отношения становится видно, что в целом отношение отвечает условиям 1 НФ. Анализируя далее отношение видим, что значение атрибута Персонал ( Должность ) не является атомарным, так как один сотрудник может занимать несколько должностей. Поэтому необходимо составить таблицу должностей. Также необходимо составить таблицу видов должностей,

так как должностей не много и они часто повторяются, а в случае изменения названия должности придется сканировать всю таблицу, чтобы изменить эти названия. В результате была изменена таблица «персонал» и добавлены таблицы «вид должности» и «занимаемые должности». Эти таблицы были приведены к следующему виду: Персонал Таб. № Фамилия Имя Отчество Вид должности Код вида должности

Должность Занимаемые должности Таб. № Код вида должности Анализируя далее отношение видим, что значение атрибута Состав ( поставщик ) необходимо вывести в отдельное отношение, так как в случае изменения названия поставщика придется сканировать всю таблицу, чтобы изменить эти названия. В результате была изменена таблица «состав» и добавлена таблица «поставщики».

Эти таблицы были приведены к следующему виду: Состав Код.товара Дата приобретения Физ.хар. Код поставщика Код.товара Поставщики Код поставщика Название поставщика Адрес ИНН Р.счет Адрес (офис) Проанализировав полученное отношение, становится видно, что оно находится в первой нормальной форме, так как значения всех атрибутов атомарно.

Вторая нормальная форма. Определение второй нормальной формы: Отношение находится во 2НФ, если оно находится в 1НФ и каждый неключевой атрибут функционально полно зависит от ключа. В полученном ранее отношении есть только один составной ключ: Занимаемые должности ( Таб. №; Код вида должности ). Однако здесь видно, что отношение функционально полно зависит от обоих ключей.

Из всего выше сказанного видно, что отношение находится во второй нормальной форме. Третья нормальная форма. Определение третьей нормальной формы: Отношение находится в 3НФ, если оно находится во 2НФ и каждый неключевой атрибут нетранзитивно зависит от первичного ключа. Последовательно проанализировав все зависимости видим, что отношение находится во второй нормальной форме, так как нет нетранзитивных зависимостей.

BCNF - нормальная форма Бойса-Кодда. Определение нормальной формы Бойса-Кодда: Отношение находится в BCNF, если оно находится во 3НФ и в ней отсутствуют зависимости атрибутов первичного ключа от неключевых атрибутов. Так как в данном отношении есть только один составной ключ, то отношение находится в нормальной форме Бойса-Кодда. Четвертая нормальная форма. Определение четвертой нормальной формы:

Отношение находится в 4NF если оно находится в BCNF и в нем отстутсвуют многозначные зависимости, не являющиеся функциональными зависимостями. Последовательно проанализировав все зависимости, видим, что отношение находится в четвертой нормальной форме, так как в нем нет многозначных зависимостей. Таким образом, мы получаем следующее отношение.