42320

Базы данных реляционных и объектно-реляционных СУБД

Лабораторная работа

Информатика, кибернетика и программирование

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

Русский

2013-10-29

1.19 MB

9 чел.

PAGE  25

     Лабораторная работа № 2.

   «Базы данных реляционных и объектно-реляционных СУБД»

   

Введение

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

1. Основные понятия реляционной модели данных

 Термин «реляционный» (от английского relationотношение) указывает на то, что такая модель хранения данных построена на взаимоотношении составляющих ее частей, которые удобно представлять в виде двумерных таблиц, которые называются отношениями. Кодд показал, что набор отношений (таблиц) может быть использован для хранения данных об объектах реального мира и моделирования связей между ними. То есть, реляционная модель данных представляет информацию в виде совокупности взаимосвязанных таблиц, которые принято называть отношениями или реляциями.

 Основными понятиями реляционной модели данных являются:

тип данных;

домен;

атрибут;

кортеж;

ключ;

связь отношений.

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

Таблица 1. Пример отношения СТУДЕНТЫ реляционной базы данных

 Тип данных

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

целочисленные;
вещественные;

строковые;

специализированные типы данных для денежных величин;

специальные типы данных для временных величин (дата и/или время);

типы двоичных объектов (данный тип не имеет аналога в языках программирования; обычно для его обозначения используется аббревиатура BLOBBinary Large Object).

В рассматриваемом примере (Табл. 1) используются три типа данных — строковый (столбцы «Имя» и «Специальность»), временной тип (столбец «Дата_рождения») и целочисленный тип («Курс» и «№_студенческого_билета»).

Домен

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

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

 Например, предположим, что в БД, кроме таблицы СТУДЕНТЫ (Табл. 1), есть ещё таблица ПРЕПОДАВАТЕЛИ, в которой содержатся анкетные данные преподавателей. В таблице ПРЕПОДАВАТЕЛИ есть столбцы «ФИО» и «№_УДОСТВЕРЕНИЯ». Допустим, что столбец «Имя» таблицы СТУДЕНТЫ и  столбец «ФИО» таблицы ПРЕПОДАВАТЕЛИ имеют одинаковые типы данных, максимальную длину, в обоих столбцах используется кириллица и смысл данных в этих столбцах одинаков (фамилия и инициалы человека). Допустим, также, что столбцы «№_студенческого_билета» в таблице СТУДЕНТЫ и «№_УДОСТОВЕРЕНИЯ» в таблице ПРЕПОДАВАТЕЛИ тоже имеют одинаковые типы данных и смысл (числовой номер). В этом случае, перед тем как в БД будут созданы  таблицы СТУДЕНТЫ и ПРЕПОДАВАТЕЛИ можно создать два домена: один с именем FNSN и второй с именем №UD. В описании домена FNSN указать: тип данных – строковый, максимальная длина – 25 символов, кодовая страница – 1251. В описании домена №UD указать: тип данных – целочисленный. Тогда при описании структуры таблиц СТУДЕНТЫ и ПРЕПОДАВАТЕЛИ вместо того, чтобы давать полное определение столбцу ИМЯ в таблице СТУДЕНТЫ и столбцу ФИО в таблице преподаватели (тип данных, максимальная  длина, кодовая страница), в том и другом случае достаточно вместо типа данных указать имя домена FNSN. Аналогично можно поступить со столбцами «№_студенческого_билета» таблицы СТУДЕНТЫ и «№_УДОСТОВЕРЕНИЯ» таблицы ПРЕПОДАВАТЕЛИ, указав вместо их типов данных имя домена №UD.

Необходимо отметить, что понятие домена используется далеко не во всех СУБД. Однако в таких широко используемых объектно-реляционных СУБД как Oracle и InterBase доменами можно пользоваться.

 Атрибуты, схема отношения, схема базы данных

 Столбцы (колонки или поля) отношения (таблицы) называют атрибутами, им присваиваются имена, по которым к ним затем производится обращение.

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

 Схема отношения СТУДЕНТЫ (структура таблицы СТУДЕНТЫ) приведена в табл. 2:

Таблица 2. Структура отношения СТУДЕНТЫ.

Имя столбца

Тип данных

Максимальная длина

№_студенческого билета

UD

-

Имя

FNSN

-

Дата_рождения

DATE

-

Курс

SMALLINT

-

Специальность

VARCHAR(30)

30

Примечание: для типов данных DATE и SMALLINT максимальная длина значений опеределена

      «по умолчанию» самим типом данных. Тип данных столбца №_студенческого _билета и

      максимальная длина значений определены в описании домена №UD. Тип данных столбца

      Имя определён в описании домена FNSN.

 Степень отношения — это число его атрибутов. Отношение степени один называют унарным, степени два — бинарным, степени три — тернарным,..., а степени п — n-арным. Степень отношения СТУДЕНТЫ равна пяти, то есть оно является 5-арным.

 Схемой базы данных (структурой БД) называется множество именованных схем отношений (структур её таблиц).

Кортеж

 Кортеж, соответствующий данной схеме отношения, представляет собой множество пар {имя атрибута, значение}, которое содержит одно вхождение каждого имени атрибута, принадлежащего схеме отношения, в одной и той же строке отношения. «Значение» является допустимым значением домена данного атрибута (или типа данных, если понятие домена не поддерживается). Тем самым степень кортежа, то есть число элементов в нём, совпадает со степенью соответствующей схемы отношения. Иными словами, кортеж — это набор именованных значений заданных типов.

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

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

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

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

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

Таблица 3.

Файл (File)

Таблица (Table)

Отношение (Relation)

Сущность

Запись (Record)

Строка (Row)

Кортеж (Tuple)

Экземпляр сущности

Поле (Field)

Столбец (Column)

Атрибут (Attribute)

Атрибут

Описание однотипных столбцов

Домен (Domain)

Общая совокупность однотипных значений

Количество полей

Количество столбцов

Степень отношения

Количество атрибутов (свойств сущности)

Количество записей

Количество строк

Мощность отношения

Количество экземпляров сущности

 Однако, со временем, особенно с появлением объектно-реляционных БД, эти соответсвия были размыты (особенно, для первых двух столбцов Таблицы 3). Поэтому, выражения «строка таблицы» и «запись таблицы» используются равнозначно, также как и выражения «столбец таблицы» и «поле таблицы» (реже «колонка таблицы» или «атрибут таблицы»).

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

Пустые значения

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

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

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

 Для обозначения пустых значений полей используется специальное слово NULL.

 Ключи отношения

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

 Более строго определить понятие первичного ключа можно следующим образом:

если R - отношение с атрибутами А1, А2,..., Аn, то множество атрибутов К= (Аi, Aj, ..., Ak) отношения R является первичным ключом этого отношения тогда и только тогда, когда удовлетворяются два независимых от времени условия:

 уникальность: в произвольный момент времени никакие два различных кортежа отношения R не

            имеют одного и того же набора значений для атрибутов Аi, Aj, ..., Ak;

 минимальность: ни один из атрибутов Аi, Aj, ..., Ak не может быть исключен из К без нарушения

            уникальности.

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

 Кроме первичного ключа (присутствие которого обязательно) отношение может иметь и другие ключи (см. ниже).

 В зависимости от количества атрибутов, входящих в ключ, различают простые и сложные (или составные) и частичные ключи:

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

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

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

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

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

 Естественный ключ — ключ, в который включены значимые атрибуты и который, таким образом, содержит значимую информацию.

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

 В рассматриваемом нами примере в качестве первичного ключа отношения СТУДЕНТЫ можно рассматривать атрибут №_СТУДЕНЧЕСКОГО_БИЛЕТА. Причем данный ключ будет естественным, так как он несёт вполне определенную информацию.

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

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

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

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

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

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

В любой из таблиц может оказаться несколько различных наборов атрибутов, которые можно
выбрать в качестве ключа. Такие наборы называются
потенциальными или альтернативными ключами.  Если потенциальные ключи являются альтернативой первичному ключу таблицы, то на них  распространяются требования уникальности и минимальности (неизбыточности).

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

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

 NOT NULL — при данном ограничении ни один из атрибутов, входящих в состав вторичного

ключа, не может принимать значение NULL.

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

 Перекрывающиеся ключи — это сложные ключи, которые имеют один или несколько общих столбцов.

Связанные отношения

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

 Для рассмотрения связанных отношений воспользуемся рассмотренным ранее примером — отношением СТУДЕНТЫ. Данное отношение может быть связано с другим отношением — УСПЕВАЕМОСТЬ, в котором содержатся сведения об успеваемости студентов по разным предметам. Фрагмент такого отношения может иметь вид, приведенный в табл. 4.

Таблица 4. Фрагмент отношения УСПЕВАЕМОСТЬ, связанного с отношением СТУДЕНТЫ

 Атрибут «№_студенческого_билета» таблицы УСПЕВАЕМОСТЬ содержит идентификатор студента (в данном примере в качестве такого идентификатора используется номер студенческого билета). Причём, значения в столбце «№_студенческого_билета» таблицы УСПЕВАЕМОСТЬ могут повторяться (условие уникальноси значений для этого столбца не установлено). Если нужно узнать имя студента, имеющего номер студенческого билета 22991380, получившего по высшей математике 3 и не сдававшего философию, то следует поискать номер студенческого билета 22991380,  находящийся в соответствующих строках столбца «№_студенческого_билета» таблицы УСПЕВАЕМОСТЬ, в поле «№_студенческого_билета» таблицы СТУДЕНТЫ и в найденной строке прочесть значение поля «Имя». Заметим, что в данном случае однозначно идентифицировать студента по имени можно только потому, что для столбца «№_студенческого_билета» таблицы СТУДЕНТЫ выполняется условие уникальности. Таким образом, связь между таблицами СТУДЕНТЫ и УСПЕВАЕМОСТЬ устанавливается по атрибуту «№_студенческого_билета».

 При рассмотрении связанных таблиц важное значение имеет понятие внешнего ключа. Рассмотрим его более подробно.

Внешние ключи отношения

 В базах данных одни и те же имена атрибутов часто используются в разных отношениях. В рассматриваемом примере атрибут «№_студенческого_билета» присутствует как в отношении СТУДЕНТЫ, так и в отношении УСПЕВАЕМОСТЬ. В этом примере атрибут «№_студенческого_билета» отношения УСПЕВАЕМОСТЬ иллюстрирует понятие внешнего ключа (foreign key).

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

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

 Так же как и любые другие ключи, внешние ключи могут быть простыми либо составными.

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

 Атрибуты внешнего ключа таблицы не обязательно должны иметь те же имена, что и атрибуты первичного ключа связанной с ней другой таблицы, которым они соответствуют. Например, в нашем примере можно было дать атрибуту «№_студенческого_билета» таблицы УСПЕВАЕМОСТЬ другое имя, например «Студенческий_билет». Однако все параметры атрибутов внешнего ключа таблицы (тип, максимальная длина) должны полностью совпадать с аналогичными параметрами первичного ключа связанной с ней другой таблицы.

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

 Рассмотрим чуть более сложный пример связей между таблицами в базе данных. В этих таблицах хранится информация о книгах и их авторах. На рис. 1 приведена структура таблиц и показаны связи
между ними. Таблица
TITLEAUTHOR содержит идентификационные номера книг, их авторов, а также некоторую другую информацию. Информация о названиях книг, их тиражах, ценах, описаниях книг и т. д. хранится в таблице TITLES. Информация об именах и фамилиях авторов, их адресах, телефонах и данные о контрактах с ними хранятся в таблице AUTHORS.

Рис. 1 Связывание таблиц

Таблица TITLEAUTHOR содержит два столбца, используемых в качестве внешних ключей. Имена этих столбцов au_id и title_id (напротив них в структуре таблицы стоит значок ключа). В таблицах TITLES и AUTHORS выбрано по одному столбцу в качестве первичного ключа. Имена этих столбцов, соответственно, title_id и au_id. Установлена связь столбца title_id таблицы TITLEAUTHOR (внешний ключ таблицы TITLEAUTHOR) со столбцом title_id таблицы TITLES (первичный ключ таблицы TITLES) и связь столбца au_id таблицы TITLEAUTHOR (внешний ключ таблицы TITLEAUTHOR) со столбцом au_id таблицы AUTHORS (первичный ключ таблицы AUTHORS).

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

2.  Условия целостности данных

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

 Важнейшими ограничениями целостности данных являются:

категорийная целостность (целостность объектов, целостность сущностей);

ссылочная целостность.

 Ограничение категорийной целостности заключается в следующем. Кортежи отношения представляют в базе данных элементы определенных объектов реального мира или, в соответствии с терминологией реляционных СУБД, сущностей, или, что то же самое, категорий. Например, строка таблицы СТУДЕНТЫ представляет конкретного студента. Первичный ключ таблицы однозначно определяет каждый кортеж и, следовательно, каждый элемент категории. Таким образом, для извлечения данных, содержащихся в строке таблицы, или для манипулирования этими данными необходимо знать значение ключа для этой строки. Поэтому строка не может быть занесена в базу данных до тех пор, пока не будут определены все атрибуты ее первичного ключа. Это правило называ-
ется
правилом категорийной целостности и кратко формулируется следующим об-
разом:
никакой атрибут первичного ключа строки базового отношения не может быть пустым (не может содержать значения NULL). Под базовым отношением имеется в виду именованное отношение (отношение, которому было присвоено имя), которое не является производным от других отношений, а является совершенно самостоятельным. Базовое отношение не может быть связано с другими отношениями базы данных, но с ним могут быть связаны другие отношения базы данных. В предыдущем примере (см. рис.1) два базовых отношения: AUTHOR и TITLES.

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

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

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

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

 Второй подход (называемый также каскадным удалением) состоит в том, что при удалении кортежа из отношения, на которое ведёт ссылка, из ссылающегося отношения автоматически удаляются все ссылающиеся кортежи. То есть, удаление из таблицы СТУДЕНТЫ строки, в поле «№_студенческого_билета» которой имеется значение 23980282 приведёт к тому, что все строки таблицы УСПЕВАЕМОСТЬ, имевшие значение поля «№_студенческого_билета» 23980282 будут автоматически удалены.

 Возможен и третий вариант, когда при удалении кортежа, на который имеются ссылки, во всех ссылающихся  кортежах значение внешнего ключа автоматически становится неопределённым (NULL) или принимает оговоренное заранее значение «по умолчанию». При этом теряется информация о ссылочной принадлежности соответствующих строк таблицы с внешним ключом. Например, удаление из таблицы СТУДЕНТЫ строки, в поле «№_студенческого_билета» которой имеется значение 23980282 приведёт к тому, что во всех строках таблицы УСПЕВАЕМОСТЬ, имевших значение поля «№_студенческого_билета» 23980282, это значение будет заменено на NULL. При этом теряется информация о ссылочной принадлежности строк таблицы УСПЕВАЕМОСТЬ, имевших в столбце «№_студенческого_билета» значение 23980282.

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

 

 В общем случае поведение системы, при котором изменение или удаление значений первичного ключа однозначно запрещается при наличии ссылающихся на него значений внешних ключей, называется ограничением (restrict). Тогда как отображение изменений или удалений значений первичного ключа на соответствующих значениях внешнего ключа называется каскадированием (cascading). Установлением (relation) называется метод, при котором удаление или изменение значений первичного ключа вызывает установку соответствующих значений внешнего ключа в NULL. Во всех развитых объектно-реляционных СУБД (Oracle 7 и выше, Microsoft SQL Server 2000, InterBase 6 и выше, DB2 и т.п.) можно выбрать один из указаных выше способов поддержания ссылочной целостности для каждой отдельной ситуации определения внешнего ключа. Конечно, для принятия такого решения необходимо анализировать требования конкретной прикладной области.

 Примечание: следует помнить, что существуют реляционные СУБД, в которых не выполняются ограничения ссылочной целостности. Это, как правило, ранние разработки локальных реляционных СУБД — FoxPro версии 2.6 и ниже, версии dBase для DOS, Сlipper и Paradox для DOS.

Типы связей между таблицами (реляционные отношения между таблицами)

 В существующих объектно-реляционных СУБД реально могут использоваться несколько типов связи между таблицами реляционной базы данных. При установлении связи между двумя таблицами одна из них являться главной (master) или родительской, а вторая — подчиненной (detail) или дочерней (зависимой). Механизм установления связей между таблицами основан на использовании первичных и внешних ключей таблиц. В подчиненной таблице всегда есть внешний ключ, значения которого должны (или могут) точно совпадать со значениями первичного ключа таблицы, являющейся главной по отношению к данной таблице. Говорят, что каждая связь может иметь одну из двух модальностей — модальность «Может» и модальность «Должен». Модальность «Может» означает, что экземпляр одной сущности может быть связан с одним или несколькими экземплярами другой сущности, а может быть и не связан ни с одним экземпляром (нежёсткая связь). Модальность «Должен» означает, что экземпляр одной сущности должен быть обязательно связан не менее чем с одним экземпляром другой сущности (жёсткая связь). Причём изменение или удаление записи главной таблицы (если эти действия не запрещены) приведёт к изменению множества связанных с ней внешним ключом записей подчиненной таблицы (если такие есть), а изменение или удаление текущей записи в подчиненной таблице не вызовет никаких изменений в главной таблице.

 Один к одному(1:1). Связь типа «один к одному» означает, что в каждый момент времени каждому экземпляру сущности А соответствует 0 или 1 экземпляров сущности В. Говоря на языке конкретной реализации сущностей в виде таблиц, если с конкретной строкой первой таблицы связано в каждый момент времени  ноль строк или одна строка второй таблицы, то между ними
установлено отношение «один к одному». Говоря по другому, отношение один к одному имеет место, когда одной записи родительской таблицы соответствует (или может соответствовать) одна запись в дочерней таблице.

 В качестве примера отношения «один к одному» можно привести связь человека и номера паспорта. Каждый человек может иметь только один паспорт, и в то же время паспорт принадлежит только одному человеку. В то же время, паспорт не может существовать сам по себе, хотя человек может не иметь паспорта (рис. 2).

Id

Фам.

Пол

1

Иванов

М

2

Петров

М

3

Мохова

Ж

Id

№пасп.

Дата рожд.

1

123456

23.02.1967

2

856432

30.07.1970

3

937508

17.04.1961

                    Таблица 1                                                                                     Таблица 2 

                                   a). Жёсткая связь

Id

Фам.

Пол

1

Иванов

М

2

Петров

М

3

Мохова

Ж

Id

№пасп.

Дата рожд.

1

123456

23.02.1967

3

937508

30.07.1970

                    Таблица 1                                                                                     Таблица 2

                                             б). Нежёсткая связь

 

    Рис. 2 Пример жёсткой и нежёсткой связи типа «один к одному»

 

 В этом примере первичный ключом Таблицы 1 является атрибут Id (идентификатор человека). Между таблицами установлена связь «один к одному», при которой родительской таблицей является Таблица 1, а дочерней – Таблица 2.  Внешним ключом Таблицы 2 является такой же атрибут Id. Внешний ключ Таблицы 2 ссылается на первичный ключ Таблицы 1.  В этом примере для того, чтобы установить связь «один к одному», не нарушая условий ссылочной целостности, надо для внешнего ключа Таблицы 2 установить свойства UNIQUE и NOT NULL (для первичного ключа Таблицы 1 свойства UNIQUE и NOT NULL устанавливаются автоматически при объявлении аттрибута Id первичным ключом Таблицы 1) и указать, что внешний ключ Id Таблицы 2 будет ссылаться именно на первичный ключ Id Таблицы 1.

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

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

 Один ко многим (1:М). Связь типа «один ко многим» означает, что одному представителю сущности А соответствуют 0, 1 или несколько экземпляров сущности В. При этом сущность А называется родительской сущностью, а сущность В — дочерней. Говоря на языке конкретной реализации сущностей в виде таблиц, если конкретная строка первой таблицы может быть связана с нулем, одной или несколькими строками второй таблицы, но строка второй таблицы связана с единственной строкой первой таблицы, то между ними установлено отношение «один ко многим».

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

 Ещё одним примером может служить связь рассмотренных ранее таблиц СТУДЕНТЫ (Табл. 1) и УСПЕВАЕМОСТЬ (Табл. 2), находящихся в одной базе данных  БД (рис. 3).

 

№_студенческого

билета

Имя

Дата

рождения

Курс

23980282

Алексеев Д.А.

12.03.1982

2

22991380

Яковлев Н.В.

25.12.1979

4

22657879

Михайлов В.В.

29.02.1979

5

24356783

Афанасьев А.В.

19.08.1983

1

24350283

Кузнецов В.И.

03.10.1982

1

231125681

Смирнов А.Д.

26.03.1981

3

№_студенческого

билета

Предмет

Оценка

23980282

Высшая математика

4

23980282

Философия

5

22991380

Высшая математика

3

22991380

Философия

NULL

22657879

Общая физика

5

24356783

Общая физика

NULL

                  Таблица СТУДЕНТЫ                                           Таблица УСПЕВАЕМОСТЬ    

                                             Рис. 3 Пример связи «один ко многим»

В этом примере таблица СТУДЕНТЫ главная (родительская). Её первичный ключ состоит из одного атрибута «№_студенческого_билета». Таблица УСПЕВАЕМОСТЬ является подчинённой (дочерней). Её внешний ключ состоит из одного атрибута «№_студенческого билета». Внешний ключ дочерней таблицы ссылается на первичный ключ родительской таблицы. В результате, одной строке в таблице СТУДЕНТЫ (главной) могут соответствовать одна или несколько строк в таблице УСПЕВАЕМОСТЬ (подчинённой).

 Многие ко многим (M:N). Связь типа «многие ко многим» означает, что каждый экземпляр первой сущности может быть связан с нулем, одной или несколькими экземплярами второй сущности и каждый экземпляр второй сущности может быть связан с нулем, одной или несколькими экземплярами первой сущности. Или на языке конкретной реализации сущностей в виде таблиц: если каждой строке в первой таблице соответствует ноль, одна или несколько строк во второй таблице, и наоборот, то между ними установлено отношение «многие ко многим». Фактически, если между двумя таблицами А и В установлена связь типа «многие ко многим»,  это означает, что таблица А является главной по отношению к таблице В (таблица В - подчинённая) и, одновременно таблица В является главной по отношению к таблице А (таблица А – подчинённая). Со стороны таблицы А поддерживается связь с таблицей В типа «один ко многим» и, одновременно, со стороны таблицы В поддерживается связь с таблицей А типа «один ко многим».

 В качестве примера отношения «многие ко многим» можно рассмотреть связь служащего и проекта. Каждый служащий может одновременно работать над несколькими проектами, и в то же время в каждом проекте могут участвовать несколько служащих (см. Рис.4)

Id_S

Имя

Id_p1

Id_p2

Id_p3

1

Алексеев Д.А.

P1

P2

NULL

2

Яковлев Н.В.

P2

P3

P4

3

Михайлов В.В.

NULL

P3

NULL

4

Афанасьев А.В.

P1

P2

P4

5

Кузнецов В.И.

P2

NULL

P3

Id_p

Проект

Id_SP1

Id_SP2

Id_SP3

Id_SP4

Id_SP5

P1

Салют

1

4

NULL

NULL

NULL

P2

Молния

1

2

4

5

NULL

P3

Гром

2

3

5

NULL

NULL

P4

Вектор

2

4

NULL

NULL

NULL

                                                           Таблица ПРОЕКТЫ       

                Таблица СЛУЖАЩИЕ                                         

                                                

                        Рис. 4 Пример связи «многие ко многим»          

                                                                      

 В таблице СЛУЖАЩИЕ хранятся данные о служащих, например, отдела. Строка каждого служащего в поле «Id_S» содержит его уникальный идентификатор служащего в списке отдела, а в полях «Id_p1», «Id_p2», «Id_p3» - уникальные идентификаторы проектов, в которых он участвует. В таблице ПРОЕКТЫ хранятся данные о проектах, над которыми работают сотрудники отдела. Каждый служащий не может участвовать более, чем в трёх проектах одновременно. В каждом проекте одновременно могут участвовать до пяти служащих. Строка каждого проекта в поле «Id_p» содержит его уникальный идентификатор проекта в списке проектов, а в полях «Id_SP1», «Id_SP2», «Id_SP3», «Id_SP4», «Id_SP5» - уникальные идентификаторы сотрудников отдела, которые участвуют в его разработке. Связь «многие ко многим» установлена благодаря тому, что, с одной стороны атрибут Id_S таблицы СЛУЖАЩИЕ является первичным ключом этой таблицы и на него ссылаются внешние ключи таблицы ПРОЕКТЫ («Id_SP1», «Id_SP2», «Id_SP3», «Id_SP4», «Id_SP5»), а с другой стороны, одновременно, атрибут Id_p является первичным ключом таблицы ПРОЕКТЫ и на него ссылаются внешние ключи таблицы СЛУЖАЩИЕ («Id_p1», «Id_p2», «Id_p3»).

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

Id_S

Имя

1

Алексеев Д.А.

2

Яковлев Н.В.

3

Михайлов В.В.

4

Афанасьев А.В.

5

Кузнецов В.И.

N

Id_S

Id_p

1

1

P1

2

1

P2

3

2

P2

4

2

P3

5

2

P4

6

3

P3

7

4

P1

8

4

P2

9

4

P4

10

5

P2

11

5

P3

 Табл. СОТРУДНИКИ

Id_p

Проект

P1

Салют

P2

Молния

P3

Гром

P4

Вектор

                                                     Табл. РАБОТА   

   Табл. ПРОЕКТЫ

    Рис. 5 Преобразование типа связи «многие ко многим» в связи «один ко многим»

Между собой могут быть связаны и более, чем две таблицы (что обычно и бывает на практике). Одна и та же таблица может быть главной по отношению к одной таблице и подчиненной по отношению к другой. Или у одной главной таблицы может находиться в подчинении не одна, а несколько таблиц. Одна подчинённая таблица может управляться несколькими главными для неё таблицами (см. пример на рис.5 и рассмотренный ранее пример на рис.3, [3], стр.36). Таким образом, у главной таблицы может быть несколько подчиненных, и у подчинённой таблицы может быть несколько главных таблиц.

3. Основные свойства отношений и таблиц, понятие СУБД

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

1). Отсутствие упорядоченности кортежей

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

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

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

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

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

Понятие логический номер записи, связано с возможностью сортировки строк в таблице по какому-либо атрибуту, его части или по совокупности атрибутов. (См. ниже, Индексы.). Сортировка – это упорядочивание строк таблицы, в соответствии с порядком нарастания или убывания значений аргумента, части аргумента или совокупности аргументов, указанных в качестве индексного выражения. В результате сортировки создаётся или  временный список кортежей (в ОЗУ компьютера), упорядоченных в соответствии с заданным условием и/или отображение совокупности корежей таблицы на экране монитора, в виде упорядоченного в соответствии с заданным условием набора строк видимого пользователю образа таблицы (с этим образом пользователь и работает как с реальной таблицей). Порядок следования строк в отсортированной таблице, называемый логическим порядком строк таблицы, в общем случае не совпадает с реальным физическим порядком следования строк в таблице. При использовании различных условий сортировки логический порядок следования строк в таблице для пользователя будет изменяться, хотя реальный, физический порядок порядок записей в таблице, будет оставаться неизменным. Номера строк в той последовательности, в которой эти строки видны пользователю в отсортированной по какому-то индексу таблице (а, точнее, в её образе) – называются логическими номерами строк (записей). Логические номера записей невидимы для пользователя, также как и их физические номера. Логический номер записи в отсортированной таблице, которую пользователь видит на экране, и с которой работает приложение в данный момент, в общем случае не совпадает с её физическим номером. Пользователь на экране монитора видит таблицу в отсортированном состоянии и работает с ней. Прикладные программы (приложения) СУБД работают со смещениями номеров строк таблицы относительно логического номера текущей записи таблицы, или относительно записи, имеющей первый логический номер в таблице (TOP), или относительно записи, имеющей последний логический номер в таблице (BOTTOM).

2). Отсутствие упорядоченности атрибутов

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

3). Атомарность значений атрибутов

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

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

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

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

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

Свойства таблиц реляционной базы данных

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

1). Каждая таблица имеет уникальное имя.

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

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

4). Имена столбцов (атрибутов) таблицы должны быть различны, а значения в каждом столбце должны быть однотипными.

5). Значения атрибутов должны быть атомарными, а следовательно элементами столбцов отношения не могут быть другие отношения. Множественные значения полей недопустимы. Иначе говоря, в каждой позиции таблицы на пересечении строки и столбца всегда должно быть в точности одно значение или NULL.

6). Должны соблюдаться условия ссылочной целостности для внешних ключей таблиц.

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

4. Индексы

 Индекс (index) — это структура, связанная с таблицей или представлением (view) и предназначенная для оптимизации поиска информации в этой таблице или представлении. В объектно-реляционных БД индекс таблицы определяется по содержимому одного или нескольких столбцов таблицы. В реляционных БД индекс может определяться и по части содержимого столбца. (В реляционных же БД выражение, связывающее атрибуты, по которым определяется индекс, или определяющее правило выделения части атрибута, по которой определяется индекс, называется индексным выражением.) Столбцы, по содержимому которых созданы индексы называются индексированными столбцами. Это означает, что одна таблица может иметь множество индексов. Индекс содержит отсортированные в определённом порядке (по нарастанию или по убыванию) значения индексированного столбца (или столбцов) со ссылками на физические номера соответствующих этим значениям строк в таблице или представлении. То есть, индекс предоставляет информацию о точном физическом расположении данных в таблице. При добавлении в таблицу
новых записей, изменении или удалении существующих записей индекс модифицируется. Когда необходимо найти какое-то значение в индексированном столбце, то поиск осуществляется именно в индексе. Повышение производительности достигается за счёт того, что данные в индексе уже  отсортированны (порядок сортировки определяет пользователь в момент создания индекса, получая, соответственно
нарастающий или убывающий индекс). Это даёт возможность используя специальные методы быстрого поиска осуществить очень быстрое нахождение в индексе первого его значения, удовлетворяющего поставленным условиям поиска (без последовательного просмотра всех предыдущих значений индекса). Остаётся только считать соответствующую найденому значению индекса ссылку на физический номер строки таблицы, содержащей данное значение и, по этому номеру, считать всю строку целиком из таблицы. Затем, в индексе следующее по порядку значение проверяется на выполнение условий поиска. Если оно этим условиям удовлетворяет, то по соответствующей ему ссылке на физический номер строки таблицы,  эта строка считывается из таблицы. И так далее, пока не встретится первое значене индекса не удовлетворяющее условиям поиска.  В этом случае выборка прекращается.

Рассмотрим пример индекса. На рис. 6 показан фрагмент таблицы СТУДЕНТЫ и нарастающего индекса, построенного по полю «Имя» данной таблицы. При выполнении поиска по имени студентов, фамилии которых начинаются на букву «М», СУБД очень быстро, не перебирая все строки индекса подряд, находит в индексе первое значение, начинающееся на букву «М» (Михайлов А.И.) и считывает из БД строку, имеющую физический номер 1000. Затем, просматривается следующая строка индекса. Она тоже удовлетворяет условиям поиска, поэтому из БД считывается строка с физическим номером 3. Следующая строка индекса не отвечает условиям поиска. Поскольку из определения индекса следует, что ни одна из последующих строк индекса не будет удовлетворять условиям поиска, то поиск на этом прекращается. Пользователь видит выбранные в соответствии с заданным условием поиска строки таблицы, отсортированные в порядке нарастания значения атрибута «Имя».


         Индекс по полю «Имя»                      Фрагмент таблицы СТУДЕНТЫ

                            Рис. 6 Поиск информации в таблице с помощью индекса

 В итоге, использование индексов существенно уменьшает время выборки данных (поиска данных).

 Такой метод доступа к записям таблицы называется индексно-последовательным, потому что:

поиск ведётся по индексу, а не по таблице;

доступ начинается с первой строки, удовлетворяющей условию запроса или его части;

строки в индексе, начиная с первой найденной, просматриваются последовательно.

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

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

 Обычно индексы автоматически (неявно) создаются по ключам  таблиц, участвующим в установлении связей между таблицами (по первичным ключам и по внешним ключам, при помощи которых устанавливается связь). Достаточно определить первичный и внешние ключи таблицы при описании схемы (структуры) таблицы и соответствующие индексы будут автоматически созданы. Эти индексы невидимы для пользователя (для него они не имеют имени), он не может производить с ними никаких действий. Можно сказать, что практическая реализация ключей в реляционных и объектно-реляционных СУБД осуществляется с помощью индексов. Ключ – это абстрактное понятие. Практически, каждый ключ физически реализуется с помощью соответствующего индекса. Любому определённому при создании таблицы ключу в БД всегда соответствует свой неявно созданный индекс.  С другой стороны, в любое время приложение автоматически, или по запросу пользователя, может явно создать индекс по любому полю любой таблицы БД (это можно сделать при помощи специальных операторов), по которому ещё не был создан индекс. Явно созданные индексы имеют собственные имена и пользователь может  производить с ними определённые действия (отключать их, включать, удалять). Отключение индексов может потребоваться, например, перед осуществлением потокового (пакетного) добавления  (изменения, удаления) большого количества строк таблицы, поскольку добавление (изменение, удаление) каждой строки вызывает перестройку (и физическую перезапись) всех индексов таблицы, что значительно замедляет скорость работы приложения. После окончания пакетного добавления строк, отключённые индексы могут быть в любое время включены. При включении каждый индекс будет один раз пересчитан (и физически перезаписан). Такой порядок выполнения пакетных операций сэкономит массу времени. Нельзя забывать, что неявно определённые индексы нельзя отключить (удалить), поскольку они обеспечивают выполнение условий ссылочной целостности, поддерживая механизм связи между таблицами.

 Выводы:

1). Ключи физически реализуются при помощи индексов. Когда говорят о первичных ключах и внешних ключах, «по умолчанию» подразумевают, что индексы по ним есть.

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

 

 Различают несколько типов индексов. Наиболее часто выделяют три типа:

 простые;
 составные;
 уникальные.

 Простые индексы представляют собой простейший и вместе с тем наиболее распространенный тип индекса. Простой индекс строится на основе только одного столбца реляционной таблицы (индекс, приведенный на рис.6, является простым).

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

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

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

 Сформулированные условия оптимальности часто противоречат друг другу, так что между ними следует находить разумный компромисс

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

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

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

  столбцы, данные в которых подвержены частому изменению;

  столбцы, содержащие большое количество пустых значений;

  столбцы, содержащие небольшое количество уникальных значений;

  небольшие таблицы;

  поля большого размера.

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

5. Нормализация данных

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

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

1). Цели нормализации

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

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

 избыточность данных;

 аномалии обновления;

 аномалии удаления;

 аномалии ввода.

Чтобы проиллюстрировать проблемы, возникающие при работе с ненормализованными базами данных, рассмотрим в качестве примера таблицу СОТРУДНИКИ, содержащую информацию о сотрудниках некой организации. Структура этой таблицы приведена на рис.7.

   Рис.7 Структура ненормализованной таблицы СОТРУДНИКИ

1.1 Избыточность данных

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

 1.2 Аномалии обновления

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

 1.3 Аномалии удаления

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

 1.4 Аномалии ввода

 Аномалии ввода возникают при добавлении в таблицу новых записей и обычно возникают, когда для некоторых полей таблицы заданы ограничения NOT NULL. В таблице, рассматриваемой в качестве примера, имеется поле «Рейтинг», в котором содержится информация об уровне квалификации сотрудника, устанавливаемом по результатам его работы. При приеме на работу нового сотрудника установить уровень его квалификации невозможно, так он еще не выполнял никаких работ в организации. Если для этого поля задать ограничение NOT NULL, то в таблицу нельзя будет ввести информацию о новом сотруднике. Это и называется аномалией ввода.

Очевидно, что избыточность, а также аномалии обновления, удаления и ввода крайне нежелательны. Чтобы свести к минимуму возможность появления такого рода аномалий, и используется нормализация таблиц БД.

2). Нормальные формы

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

 В теории реляционных баз данных обычно выделяется следующая последователь-
ность нормальных форм:

 первая нормальная форма (First Normal Form - 1NF);

 вторая нормальная форма (Second Normal Form - 2NF);

 третья нормальная форма (Third Normal Form - 3NF);

 нормальная форма БойсаКодда (Boice-Codd Normal Form - BCNF);

 четвертая нормальная форма (Forth Normal Form - 4NF);

 пятая нормальная форма, или нормальная форма проекции-соединения (Fifth Normal Form - 5NF или PJ/NF).

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

 Основные свойства нормальных форм:

 каждая следующая нормальная форма в некотором смысле лучше предыдущей;

 при переходе к следующей нормальной форме свойства предыдущих нормальных свойств сохраняются.

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

Наиболее важные на практике нормальные формы отношений основываются на
фундаментальном в теории реляционных баз данных понятии
функциональной зависимости. Функционально зависимым считается такой атрибут, значение которого однозначно определяется значением другого атрибута. Функционально зависимые атрибуты обозначаются следующим образом: X—>Y. Эта запись означает, что если два кортежа в таблице имеют одно и то же значение атрибута X, то они имеют одно и то же значение атрибута Y. Атрибут, указываемый в левой части, называется детерминантом. (Первичный ключ таблицы всегда является детерминантом).

2.1 Первая нормальная форма

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

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

 Атомарность (неделимость) поля означает, что содержащиеся в нём значения не должны делиться на более мелкие. Например, для выполнения этого ограничения в таблице на рис.7 имеется три отдельных поля: Имя, Фамилия и Отчество. Если бы для хранения имени, фамилии и отчества сотрудника было бы отведено только одно поле, это было бы ошибкой с точки зрения обеспечения атомарности хранимых данных.

 Повторяющимися называются поля (атрибуты) содержащие одинаковые по смыслу значения. Например, если бы в таблице, изображённой на рис.7 вместо одного поля «Зарплата» было бы несколько полей: «Зарплата начальника», «Зарплата инженера 1-ой категории», «Зарплата инженера 2-ой категории», «Зарплата секретаря» и т. п., это было бы нарушением требования неповторяемости полей таблицы.

2.2 Вторая нормальная форма

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

 Чтобы перейти от первой нормальной формы ко второй, нужно выполнить следу-
ющие шаги:

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

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

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

 В нашем примере для приведения таблицы СОТРУДНИКИ ко второй нормальной форме ее следует разделить на две таблицы. Первичный ключ исходной таблицы составной. Он состоит из двух атрибутов — «Код сотрудника» и «Должность», поскольку в одинаковом сочетании значения этих атрибутов не могут повторяться ни для одной строки таблицы (хотя значения каждого из них могут повторяться в разных строках таблицы). Заметим, что все личные данные о сотрудниках зависят только от атрибута «Код сотрудника». Атрибуты, соответствующие этим данным, мы и выделим в качестве одной из таблиц, которую назовем ФИЗИЧЕСКИЕ ЛИЦА. Информацию же о должностях и их оплате вынесем в другую таблицу, которой присвоим имя СОТРУДНИКИ. Схема приведения таблицы ко второй нормальной форме приведена на рис.8.

                            ФИЗИЧЕСКИЕ ЛИЦА

                         СОТРУДНИКИ

     Рис.8 Приведение таблицы ко второй нормальной форме

 Полученные две таблицы связаны между собой по полю «Код физического лица», которое является первичным ключом для таблицы ФИЗИЧЕСКИЕ ЛИЦА и внешним ключом для таблицы СОТРУДНИКИ. Данное поле отсутствовало в исходной таблице и было добавлено при проведении нормализации.

 2.3 Третья нормальная форма

Рассмотрим таблицу СОТРУДНИКИ, полученную после приведения исходной таблицы ко второй нормальной форме. Для этой таблицы существует функциональная связь между полями «Код сотрудника» и «Зарплата». Однако эта функциональная связь является транзитивной.

Функциональная зависимость атрибутов X и Y отношения R называется транзитивной, если существует такой атрибут Z, что имеются функциональные зависимости X->Z и Z->Y, но отсутствует функциональная зависимость Z—>X.

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

 Чтобы перейти от второй нормальной формы к третьей, нужно выполнить следующие шаги:

1).  Определить все поля (или группы полей), от которых зависят другие поля.

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

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

   Приведём рассматриваемую в качестве примера базу данных к третьей нормальной форме. Для этого разделим таблицу СОТРУДНИКИ на две — СОТРУДНИКИ и ДОЛЖНОСТИ (рис.9).

                                                                                СОТРУДНИКИ

                                                                             ДОЛЖНОСТИ

     Рис. 9 Приведение базы данных к третьей нормальной форме

 Обратите внимание, что мы опять добавили в таблицы СОТРУДНИКИ и ДОЛЖНОСТИ новый атрибут — «Код должности», который является первичным ключом для отношения ДОЛЖНОСТИ и внешним ключом для отношения СОТРУДНИКИ. Для таблицы ДОЛЖНОСТИ – это искусственный ключ. Можно было бы и не вводить в таблицы атрибут «Код должности». В этом случае атрибут «Должность» остался бы в таблице СОТРУДНИКИ, и он выполнял бы функции первичного ключа в таблице «ДОЛЖНОСТИ» и внешнего ключа в таблице СОТРУДНИКИ. Добавление новых атрибутов при нормализации позволяет получить таблицы с простыми первичными ключами, что облегчает выполнение операции связывания таблиц. В данном случае искусственный ключ «Код должности» - это цифра. Операции над цифрами, в частности операции сравнения и поиска, выполняются гораздо быстрее для цифр, чем для текстовых строк, хранящихся в полях атрибута «Должность».

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

 На рис.10 изображена схема рассматриваемой в качестве примера базы данных, приведённой к третьей нормальной форме:

         ФИЗИЧЕСКИЕ ЛИЦА          СОТРУДНИКИ             ДОЛЖНОСТИ

 Рис.10 Структура базы данных, приведенной к третьей нормальной форме

 Однако, если внимательно приглядеться, то можно заметить, что заработная плата сотрудника реально зависит не только от занимаемой человеком должности, но и от разряда этой должности. Причём, в данном случае, «Должность» и «Разряд» это, по отношению к «Зарплате», независимые друг от друга атрибуты в том смысле, что атрибут «Зарплата» зависит от сочетания этих элементов и в это сочетание атрибуты «Должность» и «Разряд» входят на равных правах. Поэтому таблицу ДОЛЖНОСТИ надо разбить на три таблицы – ДОЛЖНОСТЬ и РАЗРЯД и ЗАРПЛАТА. В таблице ДОЛЖНОСТЬ аргумент «Код должности» остаётся её первичным искусственным ключом, на который ссылается одноимённый внешний ключ таблицы СОТРУДНИКИ. В таблицу РАЗРЯД надо ввести дополнительный атрибут «Код разряда», который будет являться первичным искусстенным ключом для таблицы РАЗРЯДЫ. В таблицу ЗАРПЛАТА надо ввести дополнительные атрибуты «Код должности» и «Код разряда», сочетание которых будет являться для таблицы ЗАРПЛАТА искусственным первичным ключом. В то же время в таблице ЗАРПЛАТА атрибут «Код должности» будет являться внешним ключом, ссылающимся на первичный ключ таблицы ДОЛЖНОСТЬ, а атрибут «Код разряда» будет являться внешним ключом, ссылающимся на первичный ключ таблицы РАЗРЯДЫ. В таблицу СОТРУДНИКИ надо добавить атрибут «Код разряда», который будет являться для неё внешним ключом, ссылающимся на одноимённый первичный ключ таблицы РАЗРЯДЫ. Окончательная схема базы данных приведена на рис.11.

 

Рис.11 Окончательная структура базы данных, приведенной к третьей нормальной форме

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

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

6. Транзакции, блокировки, журналирование

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

 Транзакция в объектно-ориентированыых СУБД представляет собой последовательный набор команд языка SQL (одна или несколько команд), образующих логически завершенный блок, который выполняется как единое целое. В транзакцию может быть включено как несколько команд (или только одна команда), так и несколько тысяч команд. Независимо от количества команд в транзакции, либо все они будут выполнены, либо ни одна из них не выполнится. Если все команды транзакции успешно выполнены, то транзакция считается успешно выполненной и СУБД фиксирует (COMMIT) все изменения данных произведённые этой транзакцией (то есть заносит эти данные во внешнюю память). Если хотя бы одна из команд транзакции по какой-то причине не выполняется, то происходит откат (ROLLBACK) транзакции. При откате транзакции система возвращается в состояние, в котором она была до начала транзакции. Информация о первоначальном состоянии системы хранится в журнале транзакций. Именно благодаря журналу транзакций и возможно восстановление системы в исходное состояние.

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

 Управление транзакциями, блокировки

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

 Обычно объектно-реляционные СУБД поддерживают три режима определения транзакций:

явный;

автоматический;

подразумеваемый.

 Явные транзакции (Expicit Transaction) определяются и управляются непосредственно (явно) пользовательским приложением (пользователем) при помощи команд языка SQL. В различных СУБД набор средств явного управления транзакциями различен. Например, в Transact-SQL MS SQL Server пользователь может определить начало и конец транзакции, прерывание транзакции при определённых условиях (откат), промаркировать свою транзакцию в журнале транзакций и т.п. В случае явных транзакций вся ответственность за их правильную организацию, их взаимодействие с другими, одновременно выполняемыми транзакциями (явными, автоматическими или неявными) и т.п. полностью лежит на пользователе, определяющем эти транзакции. Чем большее количество команд входит в одну транзакцию, тем быстрее работает СУБД с базами данных. Поэтому этот режим – потенциально самый быстрый по скорости работы с СУБД, но он же и самый ненадёжный в плане сохранения целостности БД.

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

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

   пользователь явно не укажет команду отката или конца транзакции;

   не встретится команда, входящая в список команд, автоматически завершающих предыдущую транзакцию, перед началом своего выполнения (CREATE, DELETE, DROP и т. п.)

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

 При выполнении транзакции система управления базами данных должна придерживаться определённых правил обработки набора команд, входящих в транзакцию. В частности, существуют четыре правила, известные как требования ACID, гарантирующие правильность и надёжность работы системы. ACID — это аббревиатура от Atomicity, Consistency, Isolation и Durability. Рассмотрим подробнее каждое из требований.

  Atomicity — атомарность. Это требование говорит о том, что выполняемые в транзакции изменения будут либо выполнены все, либо не будут выполнены вовсе.

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

  Isolationизолированность. Изменения данных, выполняемые одной транзакцией, не должны зависеть от изменений, выполняемых другой транзакцией, то есть изменения данных различными транзакциями должны быть изолированными. В противном случае возможны «мертвые» блокировки, в результате чего работа обеих транзакций будет блокирована. Транзакция видит данные либо в состоянии, которое было до начала работы другой транзакции, либо в
состоянии
после того, как работа второй транзакции была завершена. Одна транзакция не может просмотреть промежуточное состояние данных, изменяемых другой транзакцией. Если транзакция читает несколько раз одни и те же данные, то она должна видеть их каждый раз в том состоянии, в котором они были при первом обращении. Например, если первая транзакция выбирает
строки данных, соответствующие определенному логическому условию, то другая транзакция не должна вставлять строки, соответствующие этому логическому условию. Такое поведение известно как «
упорядочиваемость» или «сериализуемость» (serializability).

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

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

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

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

 Если пользователю необходимо обратиться к ресурсам, размещённым в разных базах данных он должен использовать распределённые транзакции (Distributed Transaction). На самом деле распределённая транзакция представляет собой несколько отдельных транзакций, выполняемых локально в каждой базе данных, к которой обращается пользователь. В СУБД поддерживающих распределённые транзакции, управление ими (фиксацию и откат) осуществляет специальный программный компонент – менеджер транзакций (Transaction Manager), который взаимодействует с менеджерами ресурсов (Resource Manager) баз данных, участвующих в распределённой транзакции, координируя их действия. Менеджер ресурсов каждой базы данных отвечает за фиксацию и откат локальной транзакции в конкретной базе данных. Откат любой локальной транзакции вызывает откат всей распределённой транзакции.

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

 Журналирование

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

 Аппаратные сбои обычно подразделяются на два вида:

мягкие сбои связаны с внезапной остановкой работы компьютера. Они обычно являются следствием внезапного выключения питания или «зависания» операционной системы (что особенно характерно для операционных систем Windows);

жёсткие сбои характеризуются потерей информации на носителях внешней памяти.

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

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

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

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

 Во всех случаях придерживаются стратегии «упреждающей» записи в журнал (так называемого протокола Write Ahead LogWAL). Несколько утрированно можно сказать, что эта стратегия  заключается в том, что запись об изменении любого объекта базы данных должна быть занесена в журнал до того, как будет выполнено и зафиксировано изменение этого объекта. Если в СУБД корректно соблюдается протокол WAL, то с помощью журнала можно решить все проблемы восстановления базы данных после любого сбоя.

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

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

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

Задание

Выберите из приведённой ниже таблицы номера вопросов, соответствующие номеру вашей подгруппы (бригады). В файле «Вопросы к LabRab1_2.doc» найдите вопросы, имеющие  выбранные вами номера. Занесите в отчёт только правильные варианты ответов на эти вопросы.

Номер подгруппы

(бригады)

Номера вопросов в файле Вопросы к LabRab1_2.doc

1

1, 7, 13, 19, 25, 31, 37

2

2, 8, 14 ,20 ,26 ,32 ,38

3

3, 9, 15, 21, 27, 33, 39

4

4, 10, 16, 22, 28, 34, 40

5

5, 11, 17, 23, 29, 35, 41

6

6, 12, 18, 24, 30, 36, 42

7

1, 8, 15, 22, 25, 36, 41

8

2, 7, 14, 21, 28, 35, 42

9

3, 10, 17, 24, 30, 32, 37

10

5, 12, 15, 19, 26, 33, 38

11

6, 11, 16, 21, 26, 31, 40

12

4, 11, 18, 23, 28, 33, 39

13

1, 7, 17, 19, 31, 39, 42

14

1, 11, 16, 19, 30, 35,41

15

2, 12, 17, 24, 28, 30, 37

Примечание: правильных или неправильных вариантов ответа на каждый вопрос может быть

    несколько.


 

А также другие работы, которые могут Вас заинтересовать

25848. Документ как специальный носитель информации 27.5 KB
  Документы используются в различных областях деятельности отраслях знаний сферах жизни и являются объектом исследования многих научных дисциплин поэтому содержание понятия документ многозначно и зависит от того в какой отрасли и для каких целей он используется. По мнению специалистов – документоведов документ представляет собой результат отображения фактов событий предметов явлений объективной действительности и мыслительной деятельности человека. Документ изготавливается на специальном материале бумаге фотопленке и т.
25849. Документооборот и его организация 28 KB
  Организация работы с документами организация документооборота хранения и использования документов в текущей деятельности учреждения. Документооборот является важным звеном в организации делопроизводства в организации учреждении так как он определяет не только инстанции движения документов но и скорость движения документов. В делопроизводстве документооборот рассматривается как информационное обеспечение деятельности аппарата управления его документирования хранения и использования ранее созданных документов.
25850. Должностная инструкция бухгалтера 39 KB
  Инструкция раскрывает основные должностные обязанности бухгалтера его права и ответственность а также требования к квалификации. Предложенная типовая должностная инструкция бухгалтера может служить основой для разработки должностной инструкции содержащей более конкретный перечень должностных обязанностей бухгалтера с учетом особенностей предприятия организации производства труда и управления конкретного участка который ведет бухгалтер а также прав и ответственности бухгалтера. Должностная инструкция в которой четко сформулированы...
25851. Аудиторская палата России 38 KB
  Специалистами – членами палаты разработан и используется Кодекс профессиональной этики аудиторов. Однако практика работы за последние три года показала что реализация Палатой решений принимаемых на общероссийских конференциях сталкивается с серьезными трудностями по ряду причин к важнейшим из которых относятся: невозможность в силу правового статуса ассоциации принимать в ряды ее членов аудиторские фирмы и аудиторов; отсутствие в регионах структурных подразделений Аудиторской палаты России; недостаточный уровень взаимодействия с...
25852. Бухгалтерский аутсорсинг 29.5 KB
  Успешно осуществляющиеся на западе аутсорсинговые услуги сегодня находят своего потребителя и в Российской Федерации. Передача части работ на аутсорсинг позволяет сократить издержки так как зачастую услуги аутсорсера стоят намного дешевле чем содержание собственного персонала. По договору аутсорсинга как правило передаются такие функции как: поддержка информационных систем локальных сетей webсайта; защита информации; администрирование компьютерных сетей; разработка внедрение и последующее обслуживание корпоративных программных...
25853. Бухгалтерский аутсорсинг 39.5 KB
  Бухгалтерский аутсорсинг от outsource внешний источник или средства замена наемного труда услугами сторонней компанииспециалиста; передача компании бухгалтерской фирме функции ведения бухгалтерского учета бухгалтерское сопровождение В России бухгалтерский аутсорсинг появился сравнительно недавно 78 лет назад. Основными заказчиками бухгалтерского аутсорсинга еще недавно были иностранные организации работающие в Российской Федерации а также совместные предприятия. Однако сейчас все больше и российских организаций пользуются...
25854. Взаимосвязь финансового и управленческого учета 26 KB
  Аналитическая информация управленческого учета используется исключительно для внутреннего управления. Такое подразделение бухгалтерского учета можно объяснить тем что в принципе вся система бухгалтерского учета является составной частью управленческой системы организации. Общепринятые принципы финансового учета могут действовать также и в управленческом учете поскольку руководители предприятий в своей деятельности не могут руководствоваться исключительно непроверяемыми субъективными оценками и мнениями.
25855. Место и роль финансового анализа в управлении финансами банка 25 KB
  В современном коммерческом банке финансовый анализ представляет собой не просто элемент финансового управления а его основу поскольку финансовая деятельность является преобладающей в банке. Содержание место и роль финансового анализа в банковском бизнесе во многом зависят от специфики деятельности кредитных учреждений связанной с производством услуг финансового характера посредничеством между экономическими агентами; высокой степенью зависимости от клиентской базы; возможностью отсрочки неплатежеспособности по своим обязательствам путем...
25856. Методы определения рейтинга банка 23 KB
  проблемные – банки кот имеют до трех недостатков: убыток на отчетную дату несоблюдение норматива достаточности капитала на отчетную дату недовзнос в фонд обязательных резервов.Генеральный коэффициент надежности уставный капитал активы приносящие доход норматив 1.Мгновенной ликвидности наиболее ликвидные активы обязательства до востребования норматив 2.Кросскоэффициент сумма обязательств активы приносящ доход норматив 3.