18057

Базы данных, учебное пособие

Книга

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

Базы данных Допущено Учебно-методическим объединением вузов по образованию в области автоматизированного машиностроения УМО АМ в качестве учебного пособия для студентов высших учебных заведений обучающихся по направлениям подготовки бакалавров и магистров.

Русский

2015-01-15

1.41 MB

17 чел.

Базы данных

Допущено Учебно-методическим объединением вузов по образованию в области автоматизированного машиностроения (УМО АМ) в качестве учебного пособия для студентов высших учебных заведений, обучающихся по направлениям подготовки бакалавров и магистров «Технология, оборудование и автоматизация машиностроительных производств», дипломированных специалистов «Конструкторско-технологическое обеспечение машиностроительных производств», «Автоматизированные технологии и производства»

г. Набережные Челны

2004

УДК 681.3

Ахмадеев И.А., Хайруллин А.Х., Юрасов С.Ю. Базы данных. Учебное пособие. Под общей редакцией профессора Ахмадеева И.А. по дисциплине «Базы данных».  Набережные Челны: Камский государственный политехнический институт, 2004, -  237  с.

Учебное пособие посвящено вопросам проектирования  баз данных, специальным вопросам при работе с базами данных, а также реализации программного обеспечения для работы с базами данных с использованием СУБД Access, языка программирования Visual Basic for Application (VBA) и системы SQL Server.

Илл – 39,  Таб. – 15,    Лит – 14 наименований.

Рецензенты:

кандидат технических наук, доцент  Гумеров А.Ф.

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

Печатается по решению научно – методического совета Камского государственного политехнического института.                      

Камский государственный

политехнический институт, 2004 год

Введение

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

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

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

Третья часть посвящена разработке приложений для работы с базами данных с использованием СУБД Access, языка VBA   и  системы Microsoft SQL – сервер.

Часть 1. Проектирование баз данных

1.1. Некоторые  понятия  и  определения

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

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

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

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

Понятие данные в концепции баз данных – это набор конкретных значений, параметров, характеризующих объект, условие, ситуацию или любые другие факторы. Примеры данных: Иванов Иван Иванович, 100 рублей и т. д. Данные не обладают определенной структурой, данные становятся информацией тогда, когда пользователь задает им определенную структуру, т. е. осознает их смысловое содержание. Например, если мы выпишем столбиком набор чисел: 534322, 523498, 453478, 796475 и т.д., то это данные, но не информация. Если теперь против каждого набора мы запишем название организации, эти данные превратятся в информацию, которую можно использовать. Таким образом, информация – это используемые данные.

1. 2. Модели данных

Центральным понятием в области баз данных является понятие модели данных. Модель данных является  ядром любой базы данных.

Модель данных – совокупность структур данных и операций их обработки.

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

Среди множества моделей данных выделим иерархические, сетевые, реляционные и комбинированные модели данных.

1.2.1. Иерархическая модель данных

Иерархическая модель данных является наиболее простой. Исторически она появилась первой  и именно эту модель поддерживает первая из зарегистрированных промышленных СУБД  IMS фирмы IBM.

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

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

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

Уровень 1                                 А

Уровень 2                В1             В2              В3

Уровень 3       С1          С2   С3         С4

Уровень 4   D1    D2  D3    D4     D5      D6 …….

…………

Рис. 1. Графическое изображение иерархической структуры БД.

К основным понятиям иерархической структуры относятся: уровень, элемент (узел), связь.

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

К каждой записи БД существует только один (иерархический) путь от корневой записи. Например, как видно из рис.2.1. для записи Д5 путь проходит через записи А, В2 и С4.

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

1.2.2. Сетевая модель данных

Стандарт сетевой модели впервые был определен в 1975 году организацией CODASYL (Conference of Data System Languages), которая определила базовые понятия модели и формальный язык описания.

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

Графическое изображение сетевой структуры может быть представлено  в виде рис.2.

                      А                            В

       С                            D                            Е

  

                      F                             G                          L

Рис.2 Сетевая модель данных

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

Иерархические и сетевые модели данных относятся к так называемым теоретико-графовым моделям.

1.2.3. Реляционная модель данных

 Основные определения

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

Теоретической основой этой модели стала теория отношений, основу которой заложили два логика — американец Чарльз Содерс Пирс (1839-1914) и немец Эрнст Шредер (1841-1902). В руководствах по теории отношений было показано, что множество отношений замкнуто относительно некоторых специальных операций, то есть образует вместе с этими операциями абстрактную алгебру. Это важнейшее свойство отношений было использовано в реляционной модели для разработки языка манипулирования данными, связанного с исходной алгеброй. Американский математик Э. Ф. Кодд в 1970 году впервые сформулировал основные понятия и ограничения реляционной модели, ограничив набор операций в ней семью основными и одной дополнительной операцией. Предложения Кодда были настолько эффективны для систем баз данных, что за эту модель он был удостоен престижной премии Тьюринга в области теоретических основ вычислительной техники.

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

Отношением R называют подмножество декартова произведения DD2х... xDn множеств d1,D2, ..., Dn (n  1), необязательно различных. Исходные множества D1, D2, ..., Dn называют в модели доменами.

R D1xD2x...xDn,

где D1xD2x ...xDn— полное декартово произведение.

Полное декартово произведение — это набор всевозможных сочетаний из n  элементов каждое, где каждый элемент берется из своего домена. Например, имеем три домена: D1 содержит три фамилии, D2 — набор из двух учебных дисциплин и D3 — набор из трех оценок. Допустим, содержимое доменов следующее:

- D1 = {Иванов, Крылов, Степанов};

- D2 = {Теория управления, Базы данных};

- D3 = {3, 4, 5}

Тогда полное декартово произведение содержит набор из 18 троек, где первый элемент — это одна из фамилий, второй — это название одной из учебных дисциплин, а третий — одна из оценок.

<Иванов, Теория управления,3>;<Иванов, Теория управления, 4>;<Иванов, Теория управления,5>;<Крылов, Теория управления, 3>;<Крылов, Теория управления,4>;<Крылов, Теория управления, 5>; <Степанов, Теория управления, 3>; <Степанов Теория управления, 4>;<Степанов, Теория управления.5>; <Иванов, Базы данных, 3>; <Иванов, Базы данных, 4> ; <Иванов, Базы данных, 5>; <Крылов, Базы данных, 3>; <Крылов, Базы данных, 4>; <Крылов, Базы данных,5>;<Степанов, Базы данных,3>; <Степанов, Базы данных. 4>; <Степанов, Базы данных,5>.

Отношение R моделирует реальную ситуацию и оно может содержать, допустим, только 5 строк, которые соответствуют результатам сессии (Крылов экзамен по «Базам данных» еще не сдавал):

<Иванов, Теория управления ,4>;<Крылов, Теория управления, 5>; <Степанов, Теория управления, 5>; <Иванов, Базы данных,3>; <Степанов, Базы данных,4>;

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

R

Фамилия

Дисциплина

Оценка

Иванов

Теория управления

4

Иванов

Базы данных

3

Крылов

Теория управления

5

Степанов

Теория управления

5

Степанов

Базы данных

4

Данная таблица обладает рядом специфических свойств:

1.В таблице нет двух одинаковых строк.

2.Таблица имеет столбцы, соответствующие атрибутам отношения.

3.Каждый атрибут в отношении имеет уникальное имя.

4.Порядок строк в таблице произвольный.

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

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

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

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

R1

Дисциплина

Фамилия

Оценка

Теория управления

Крылов

5

Теория управления

Степанов

5

Теория управления

Иванов

4

Базы данных

Иванов

3

Базы данных

Степанов

4

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

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

Sr = (A1, А2, Аn), Ai Di.

Если атрибуты принимают значения из одного и того же домена, то они называются  - сравнимыми, где — множество допустимых операций сравнения, заданных для данного домена. Например, если домен содержит числовые данные , то для него допустимы все операции сравнения, тогда = {=, <>,>=, <=,<,>}. Однако и для доменов, содержащих символьные данные, могут быть заданы не только операции сравнения по равенству и неравенству значений. Если для данного домена задано лексикографическое упорядочение, то он имеет также полный спектр операций сравнения.

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

Sri = (А1, А2, ..., Аn) — схема отношения R1.

Sr2 = (В11, В12,..., В1n) — схема отношения R2 после упорядочения имен атрибутов.

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

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

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

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

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

Типы связей между отношениями

Все отношения в БД должны быть связаны между собой. Различают связи нескольких типов, для которых введены следующие обозначения: один к одному (1: 1), один ко многим (1: N), многие к одному (N : 1) (перевернутая связь один ко многим) и многие ко многим (N : M).

Рассмотрим эти связи на примере. Дана совокупность отношений, отображающих учебный процесс в ВУЗе:

СТУДЕНТ (Номер, Ф.И.О., пол, Дата рождения, Группа)

СЕССИЯ (Номер, Оценка1, Оценка2, Оценка3, Оценка4, Средний балл)

СТИПЕНДИЯ (Средний балл, Размер стипендии)

ПРЕПОДАВАТЕЛЬ (Код преподавателя, Ф.И.О.).

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

Примером связи 1:1 может служить связь между отношениями СТУДЕНТ и СЕССИЯ: каждый студент имеет определенный набор экзаменационных оценок в сессию.

При связи один ко многим (1:N) одному кортежу отношения А соответствует 0,1 или более кортежей отношения В, но каждый кортеж отношения В связан не более чем с одним кортежем отношения А.

Примером связи 1:N служит связь между отношениями СТИПЕНДИЯ и СЕССИЯ. Установленный размер стипендии по результатам сдачи сессии может повторяться многократно для различных студентов. Примером  же связи N:1 является связь между отношениями СЕССИЯ и СТИПЕНДИЯ.

Связь многие ко многим (N:M) предполагает, что в каждый момент времени одному кортежу отношения А соответствует 0,1 или более кортежей отношения В и наоборот. Примером данного типа связи служит связь между отношениями СТУДЕНТ и ПРЕПОДАВТЕЛЬ. Один студент обучается у многих преподавателей, один преподаватель обучает многих студентов.

1.3. Классификация баз данных

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

Централизованная база данных хранится в памяти одной вычислительной системы. Если эта вычислительная система является компонентом сети ЭВМ, возможен распределенный доступ к такой базе. Такой способ использования БД часто применяется в локальных сетях ПК.

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

По способу доступа к данным БД разделяются на БД с локальным доступом и БД с удаленным (сетевым) доступом.

Системы централизованных баз данных с сетевым доступом предполагают различные архитектуры подобных систем:

файл-сервер;

клиент-сервер.

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

Клиент-сервер. В этой концепции подразумевается, что помимо хранения централизованной базы данных центральная машина (сервер базы данных) должна обеспечивать выполнение основного объема обработки данных. Запрос на данные, выдаваемый клиентом (рабочей станцией), порождает поиск и извлечение данных на сервере. Извлеченные данные (но не файлы) транспортируются по сети от сервера к клиенту. Спецификой архитектуры клиент-сервер является использование языка запросов SQL (Structured Query Language).

1.4. Цели проектирования баз данных

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

1. Возможность хранения всех необходимых данных в БД.

2. Исключение избыточности данных.

3. Сведение числа хранимых в БД отношений к минимуму.

4. Нормализация отношений для упрощения  решения  проблем, связанных с обновлением и удалением данных.

Цель 1: Возможность хранения всех необходимых данных в БД.

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

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

Цель 2:  Исключение избыточности данных.

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

С – Н                                                         С - Н

Слж#  

Начк

Слж#  

Начк

125   

Иванов

125

Иванов

138  

Петров

138

Петров

195  

Петров

195

-

200

Иванов

200

-

                а)                                                             б)

Рис.3. Дублирование данных, не являющихся избыточными

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

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

            С-Н-Т                                                  С-Н-Т  

Слж#  

Начк

Нтел

Слж#  

Начк

Нтел

125

Иванов

3051

125

Иванов

3051

138

Петров

2222

138

Петров

2222

195

Петров

2222

195

Петров

-

200

Иванов

3051

200

Иванов

-

                а)                                                       б)  

 С – Н                                            Н - Т

Слж#  

Начк

Начк

Нтел

125

Иванов

Иванов

3051

138

Петров

Петров

2222

195

Петров

200

Иванов

                                   в)

Рис.4. Исключение избыточных данных

На рис.4,б приведен пример того, как будет выглядеть отношение С-Н-Т в случае замещения дублированных телефонных номеров "нулями".

Данный метод  устранения  избыточности  неудовлетворен по двум причинам.  Во-первых, пустых полей в БД следует избегать, так как необходимы дополнительные усилия при программировании, направленные  на  определение  реальных  значений  "нулей".  В рассматриваемом  случае чтение третьего кортежа <195, Петров> отношения не позволяет  узнать  телефонный  номер  Петрова. Пользователь  должен уметь находить в отношении другой кортеж, для  которого  значение  атрибута  Начк  является  Петров,   а значение  атрибута  Нтел не является нулевым.  Во-вторых,  что более важно,  отношение,  представленное на  рис.4,б,  имеет структуру,   чреватую  возникновением  серьезных  проблем  при удалении  информации.  Если  служащий   с   номером   Слж#=125 увольняется с предприятия и кортеж <125,  Иванов,  3051> будет удален  из  отношения  произойдет  утеря  телефонного   номера Иванова,  поскольку нигде более в отношении он не представлен.

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

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

Как следует из рис.4,в,  служащий с номером  125  теперь может быть  удален  из отношения С-Н без потери номера телефона бывшего начальника этого служащего, хранимого в отношении Н-Т.

Цель 3: Сведение числа хранимых в БД отношений к минимуму.

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

Цель 4: Нормализация отношений.

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

Цели 3 и 4 противоречат друг другу, поэтому здесь требуется взаимный  компромисс.  Это  будет  частью завершающего этапа проектирования.

1.5. Проектирование баз данных с использованием универсального отношения

Рассматриваемый метод проектирования называют декомпозиционным методом.

1.5.1. Универсальное отношение

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

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

Имена и условия,  связанные с атрибутами, хранение которых предполагается, определим следующим образом:

Сном: Номер студента.  Целое значение, уникальное для каждого студента института.

Сфам: Фамилия  студента.  Каждый студент имеет только одну  фамилию, но возможно,  что одну фамилию носят несколько студентов.

Кном: Номер комнаты в общежитии.  В  одной  комнате  может проживать более одного студента.

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

Дисц: Название дисциплины.  В БД будут храниться данные о дисциплинах, по которым проведена итоговая аттестация студента.

Семестр: Институтский   семестр.    Представляет    собой семестр, в котором данная дисциплина была завершена студентом.

Возможно,  что студент  изучал  одну  и  ту  же  дисциплину  в различных семестрах.

Оценка: Оценка за дисциплину. Оценка, полученная студентом за определенную дисциплину в данном семестре.

    На рис.5 представлена таблица с данными, которые должны хранится  в БД.  Хотя на рисунке приведен пример в виде таблицы данных,  которые могут  храниться  в  БД  в  некоторый момент времени, указанная таблица отношением не является.

    УСПЕВАЕМОСТЬ

Сном

Сфам

Кном

Тном

Дисц.

Сем.

Оценка

111

Серов

120

2135

ВМ

3

2

ТМ

2

4

Физика

3

5

222

Перов

211

3257

ВМ

3

4

Химия

1

5

ВТ

4

4

333

Иванов

301

3589

ТМ

2

5

110

Поляков

201

3290

ВМ

1

4

Рис.5. Данные для размещения в БД

Для иллюстрации того, почему таблица на рис.5 не является отношением, выделим одну "строку" из таблицы (рис.6).

111

Серов

120

2135

ВМ

3

2

ТМ

2

4

Физика

3

5

    Рис.6. Одна "строка" таблицы, приведенной на рис.5.

На этом рисунке значения четырех полей Сном,  Сфам, Кном и Тном - атомарные (атомарным называется неделимое значение, а не множество, или  кортеж,  значений  из некоторых доменов),  в то время как значения в полях Дисциплина,  Семестр и Оценка - множественные.

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

   УСПЕВАЕМОСТЬ

Сном

Сфам

Кном

Тном

Дисц.

Сем.

Оценка

111

Серов

120

2135

ВМ

3

2

111

Серов

120

2135

ТМ

2

4

111

Серов

120

2135

Физика

3

5

222

Перов

211

3257

ВМ

3

4

222

Перов

211

3257

Химия

1

5

222

Перов

211

3257

ВТ

4

4

333

Иванов

301

3589

ТМ

2

5

110

Поляков

201

3290

ВМ

1

4

Рис.7. Данные из таблицы, приведенной на рис.5, помещенные в корректное отношение.

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

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

1.5.2. Проблемы, вызываемые использованием универсального отношения

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

Проблема вставки

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

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

    УСПЕВАЕМОСТЬ

Сном

Сфам

Кном

Тном

Дисц.

Сем.

Оценка

111

Серов

120

2135

ВМ

3

2

111

Серов

120

2135

ТМ

2

4

111

Серов

120

2135

Физика

3

5

222

Перов

211

3257

ВМ

3

4

222

Перов

211

3257

Химия

1

5

222

Перов

211

3257

ВТ

4

4

333

Иванов

301

3589

ТМ

2

5

110

Поляков

201

3290

ВМ

1

4

444

Белов

401

5452

                             а)

111   Серов   

444   Белов

                              б)

Рис.8. а- результат вставки записи с пустыми полями;               б- ошибочный результат выполнения запроса, вызванный наличием пустых полей.

Пустые символьные строки представляются пробельными полями (Дисциплина и Семестр), в то время как нулевые численные значения в поле Оценка интерпретируются  как 0.

Рис.8,б иллюстрирует возможные последствия появления 0.0 при отсутствии информации. Получен ответ на запрос "Выдать список Сном и Сфам всех студентов,  получивших  хотя  бы  одну оценку ниже 3.0". В число таких студентов попал Белов, хотя он не закончил изучение ни одной дисциплины.

Проблемы обновления

В отношении УСПЕВАЕМОСТЬ большое число  избыточных  данных. Она характеризуется как явной, так и неявной избыточностью. Явная избыточность заключается в том, что фамилия данного студента, номер комнаты и номер телефона могут появляться в отношении несколько раз.

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

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

Проблемы удаления

В экземпляре     отношения     УСПЕВАЕМОСТЬ     (рис.8) присутствует только один кортеж,  в  котором  Сном=110.  Этот кортеж   соответствует   студенту   с   фамилией   Поляков.

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

1.5.3. Нормальная форма Бойса -Кодда

Приведенное на рис.7 универсальное отношение находится в первой нормальной форме (1НФ).

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

Функциональные зависимости

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

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

Функциональная зависимость (ФЗ)  определяется  следующим образом:

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

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

      А -> В  - математическая форма записи

     - диаграмма или графическая форма записи

Рис.9. Два  возможных способа записи того, что атрибут В функционально зависит от атрибута А.

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

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

В качестве  примера  вновь  обратимся   к   атрибутам   в отношении УСПЕВАЕМОСТЬ (рис.7). И, в частности, к подробному объяснению того,  как эти атрибуты связаны между собой. После изучения   описаний   атрибутов могут   быть   выведены  ФЗ, приведенные на рис.10.

      Сном -> Сфам

      Сном -> Кном

      Кном -> Тном

      Тном -> Кном

      Сном -> Тном

      Сном, Дисциплина, Семестр -> Оценка

                                 а)

                                 б)

Рис.10. Различные способы представления ФЗ,  существующих между атрибутами отношения УСПЕВАЕМОСТЬ

Соображение, приведшие  к ФЗ, изображенным на рис.10  в деталях обсуждаются ниже.

1. Номера   студентов   являются   уникальными.   Каждому студенту назначается номер Сном,  причем все номера  различны. Таким  образом,  если  известны Сном,  то с ним связана только одна фамилия Сфам:  Сном -> Сфам. Обратное не является верным, поскольку несколько студентов могут иметь одинаковые фамилии.

2. Каждый студент прикреплен к одной  комнате  общежития, но  в  одной  комнате  может проживать более чем один студент. Таким образом,  Сном -> Кном является верным, а Кном -> Сном  - нет.

3. Поскольку в  каждой  комнате  только  один  телефон  и каждый  телефон,  в  свою  очередь,  имеет  уникальный  номер, получаем Кном -> Тном и Тном -> Кном.  Данная ситуация  обычно обозначается  в  виде  Кном <--> Тном,  и говорят,  что Сном и Тном  взаимозависимы.

4. Поскольку  в каждой комнате только один телефон и этот телефон имеет  уникальный  номер,  следовательно  только  один телефонный  номер  может  быть связан с данным студентом,  или иначе Сном -> Тном.

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

Возможный ключ и детерминант

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

Детерминант. Если  А  ->  В  есть  ФЗ  и В не зависит функционально от любого подмножества  А,  то  говорят,  что  А представляет собой детерминант В.

Из рис.10 можно заключить,  что  отношение  УСПЕВАЕМОСТЬ имеет  только  один  возможный ключ,  а именно набор атрибутов <Сном, Дисциплина, Семестр >. Этот вывод получен путем нахождения минимального  набора  значений  атрибутов,  которые,  если они известны, определяют значение всех других атрибутов кортежа. С помощью ФЗ,  представленных на рис.10, легко видеть, что один номер Сном  определяет Сфам, Кном, Тном и для определения оценки должен  быть  известен весь набор Сном,  Дисциплина и Семестр.

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

Детерминантами в отношении  УСПЕВАЕМОСТЬ  являются  левые части  всех  ФЗ  в  отношении.  Детерминантами  здесь являются <Сном, Дисциплина, Семестр >,   <Сном>,    <Кном>    и    <Тном>.

Детерминанты  заключены  в  угловые  скобки для того,  чтобы в данном случае выделить четыре различных  детерминанта. Следует заметить, что взаимные зависимости дают два детерминанта.

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

Отношение находится в НФБК,  если каждый детерминант отношения является возможным ключом.

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

      Возможные ключи                            Детерминанты

   <Сном,Дисц,Семестр>                 <Сном,Дисц,Семестр>

                                                                     <Сном>

                                                                     <Тном>

                                                                     <Кном>

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

Общий подход к декомпозиции

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

1. Разработка универсального отношения для БД.

2. Определение всех ФЗ между атрибутами отношения.

3. Определение того,  находится ли отношение в НФБК. Если да,  проектирование завершается;  если нет,  отношение  должно быть разложено на два отношения.

4. Повторение шагов 2 и 3 для каждого  нового  отношения, полученного    в   результате   декомпозиции.   Проектирование завершается, когда все отношения будут находится в НФБК.

Предложенный алгоритм  не  определяет,  как  осуществлять декомпозицию  отношения,  не  приведенного  к  НФБК,  на   два отношения.  Это осуществляется с помощью ФЗ следующим образом.

Пусть отношение R(A,B,C,D,E,...)  не  приведено  к  НФБК. Определяется ФЗ,  например,  С -> D, про которую известно, что она является причиной того, что отношение R не находится в НФБК (С  является детерминантом,  но не является возможным ключом).

Создается два новых отношения:  R1(A,B,C,E,...) и R2(C,D), где зависимостная  часть  ФЗ  была  выделена  из  R  и опущена при формировании отношения R1 и ФЗ была использована полностью при формировании   отношения   R2.  Теперь  необходимо  проверить, находятся ли в НФБК отношения R1 и R2.  Про отношение  R2(C,D) говорят,  что  одно  является  проекцией  отношения R.  Этот тип декомпозиции   называется  декомпозицией   без   потерь    при естественном  соединении.  Указанный  метод декомпозиции может быть  использован  на  шаге  3  приведенного  выше   алгоритма проектирования.

В качестве   примера   использования   метода    выполним декомпозицию   отношения   УСПЕВАЕМОСТЬ.   Обращаясь  вновь  к детерминантам  и  возможным  ключам  отношения   УСПЕВАЕМОСТЬ, видим,  что  имеются три детерминанта,  не являющихся возможными ключами: <Сном>, <Тном> и <Кном>

Кандидатами среди ФЗ для осуществления проекции являются: Сном -> Кном; Сном -> Тном; Кном -> Тном и Тном -> Кном.

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

Простым правилом выбора ФЗ  для  проекции  может  служить поиск  "цепочки" вида А -> В -> С с последующим использованием для проекции крайней правой зависимости.  В нашем случае такой "цепочкой"  является  Сном  ->  Кном -> Тном и "конец цепочки" Кном -> Тном порождает первую  проекцию.  Полученные  в  итоге отношения  R1  и  R2  приведены  на рис.11 вместе со связанными с ними ФЗ.

R1(Cном, Дисц, Семестр, Сфам, Кном, Оценка)

    Возможные ключи                    Детерминанты

<Сном, Дисц, Семестр>       < Сном, Дисц, Семестр >

                                               < Сном >

R2(Кном, Тном)

    Возможные ключи                    Детерминанты

   < Кном >                                     < Кном >

   < Тном >                                      < Тном >

Рис.11. Отношения R1 и R2 получены  в результате проекции Кном <-> Тном   из отношения УСПЕВАЕМОСТЬ.

Отношение R2(Кном, Тном)  находится в НФБК,  поскольку все его  детерминанты  являются  возможными  ключами и оно   не нуждается    более    в    декомпозиции. Первичным ключом отношения R2 могут быть как Кном, так и Тном.  Отношение R1 не находится  в  НФБК, т.к.   детерминант   <Сном>   не  является  возможным  ключом.

Следовательно отношение R1 необходимо  подвергнуть дальнейшей декомпозиции.

Детерминант <Сном> имеет два зависимых от  него  атрибута Сном -> Сфам, Сном -> Кном, что можно рассматривать в качестве единичной ФЗ с составной правой частью Сном -> Сфам, Кном.

Проекция отношения  R1,  порождаемая  этой  ФЗ приводит к получению отношений R3 и R4,  показанных на рис.12.  Эти  два отношения  находятся  в НФБК и вместе с отношением R2 могут использоваться при формировании БД для  учета успеваемости.

                   R3(Сном, Дисц, Семестр, Оценка)

   Возможные ключи                          Детерминанты

<Сном,Дисц.,Семестр>             < Сном,Дисц.,Семестр >

                   R4(Сном, Сфам, Кном)

   Возможные ключи                          Детерминанты

            <Сном>                                          <Сном>

Рис.12. Отношение R3 и R4 полученные в результате проекции

На  рис.13 представлен   окончательный   вид   отношений   для  такой  БД (названной УСПЕХИ),  а также  экземпляры  каждого  отношения  с данными,   совпадающими   с   использованными   для  исходного отношения УСПЕВАЕМОСТЬ.  Заметим,  в частности, что в процессе декомпозиции   автоматически   произошло  разбиение  исходного отношения УСПЕВАЕМОСТЬ на три логических компонента: R2,  в котором  содержится информация о комнатах и телефонах;  R3,  в котором  содержится  информация  об  учебных   дисциплинах   и полученных   студентами  оценках;  R4,  в  котором  содержится информация о студентах.  Такое логическое  разбиение  является прямым   результатом  использования  в  процессе  декомпозиции заложенной в ФЗ информации,  детализирующей то,  как различные атрибуты в исходном отношении соотносятся друг с другом.

R2(Кном, Тном)

R3(Сном, Дисц, Семестр, Оценка)

R4(Сном, Сфам, Кном)

                         а)

       УСПЕХИ

R3                                                                        R2

Сном

Дисц

Сем

Оценка

Кном

Тном

111

ВМ

3

2

120

2135

111

ТМ

2

4

211

3257

111

Физика

3

5

301

3589

222

ВМ

3

4

201

3290

222

Химия

1

5

401

5452

222

ВТ

4

4

R4

333

ТМ

2

5

Сном

Сфам

Кном

110

ВМ

1

4

111

Серов

120

222

Перов

211

333

Иванов

301

110

Поляков

201

444

Белов

401

                                              б)

Рис.13. а - база данных УСПЕХИ;  б - экземпляр БД в котором используются данные, приведенные на рис.8,а

Анализ исходных аномалий

Зададимся вопросом: присутствует ли в  БД  УСПЕХИ  те  же аномалии,  которые характеризовали отношение УСПЕВАЕМОСТЬ, или декомпозиция автоматически привела к их устранению?  Для того, чтобы  подтвердить  устранение  аномалий, а именно эта цель ставилась в первую очередь при осуществлении  декомпозиции, рассмотрим,  опираясь  на  приведенную  на  рис.13  БД УСПЕХИ, проблемы вставки, удаления и обновления.

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

Обновление. В исходном отношении УСПЕВАЕМОСТЬ  попытка изменить номер телефона Серова, проживающего в комнате 120 может привести  к  тому,  что  в  БД  появится  два   различных телефонных номера,  установленного в комнате 120. В БД УСПЕХИ телефонные  номера  располагаются  в  отношении  R2  и  каждая комната  может  иметь  только  один телефонный номер.  Следует помнить,  что Кном является первичным  ключом  отношения R2,  а  значения  первичного ключа по определению должны быть уникальными.  Для изменения  телефонного  номера  Серова следует в кортеже отношения R2, в котором Кном=120, изменить номер Тном.  При этом будет изменен номер  телефона  для  всех студентов,  живущих  в этой комнате.  Таким образом,  исходная аномалия обновления устраняется с помощью НФБК-проектирования.

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

Удаление. Когда отношение УСПЕВАЕМОСТЬ  использовалось  в качестве БД, удаление кортежа включавшего значения Сном=110 и Дисц.  ВМ,  привело к исчезновению из БД студента  с  номерами 110. Этого  не может произойти в случае БД УСПЕХИ,  поскольку информация  о  студентах  общего  характера  разнесена  в  два различных отношения (R3 и R4).  При исключении из  отношения R3 кортежа <110,ВМ,1,4>, общая информация о студенте, хранимая в R4, останется.

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

1.5.4.  Возможные потери ФЗ при декомпозиции

Ранее было  сказано,  что  в  процессе проектирования  с помощью   проекции  декомпозицию  следует  осуществлять  путем поиска цепочек ФЗ,  а именно:  A -> B -> C  с  последующим проецированием,  порождаемым ФЗ,  замыкающей цепочки. В данном случае это ФЗ В -> С.

Отступим от  этого правила и в качестве ФЗ для композиции возьмем А -> В. Если исходное отношение имеет  вид  R(A,B,C),то полученные  в  результате  отношения  будут R1(A, В) и R2(A, С).

Хотя оба эти отношения находятся в НФБК, со всей отчетливостью обнаруживается следующая проблема.      Ни отношение R1(А,В),  ни R2(A,C)  не  содержит ФЗ  В -> С,  которая является ФЗ исходного отношения.

Эта зависимость   утеряна   в  процессе  декомпозиции.  С практической точки зрения это означает,  что если  приведенные выше  отношения R1 и R2 будут использованы для создания БД, то нельзя будет утверждать, что некорректные связи между В и С не будут привнесены в БД.

Проблема в   данном  примере  возникает  из-за  проекции, порожденной ФЗ,  зависимостная  часть  которой  сама  является детерминантом другой ФЗ. При использовании правила цепочки эта проблема не возникает.

Таким образом,  следует избегать выбора ФЗ, зависимостная  часть  которой  сама  -  целиком  или  частично является детерминантом другой ФЗ.

Другим случаем   возможной   потери   ФЗ    в    процессе декомпозиции является ситуация, в которой один атрибут зависит от двух различных детерминантов.  Пусть для отношения R(A,B,C) определены зависимости, показанные на рис.14.

           А

                     В      R(A, B,C)

           С

Рис.14. Два детерминанта с одним и тем же зависимым атрибутом.

Это отношение не находится в НФБК,  т.к.  имеется  только один  возможный  ключ  <A,C>,  в  то  время как детерминантами являются <А> и <C>.  Правило цепочек  здесь  не  приемлемо  по причине  их  отсутствия.  Кроме  того,  если  одна из ФЗ будет выделена обычным способом, то другая будет потеряна. Например, при  выделении  из R(A, B,C) зависимости А -> В будут получены отношения R1(A,C) и R2(A,B),  ни  одно  из  которых  не  будет содержать  зависимости  С -> В.  Наоборот,  при первоочередном выделении С -> В будет утеряна зависимость А -> В.

Таким образом, возникла ситуация, обязывающая проектировщика найти способ разбиения отношения  R(A,B,C)  на два, не приводящей к утере ни одной ФЗ.  Этот путь не соответствует  стандартному  методу  декомпозиции,  но может  привести  к лучшему результату.  Единственное что может сделать проектировщик, столкнувшись с указанной ситуацией, это проверить  три возможных набора отношений и оценить, какой из трех наиболее соответствует нуждам пользователя. В частности, полученные  в  последнем случае отношения необходимо проверить на предмет возникновения  проблем  в  случае  соединения  двух итоговых  отношений  при  поиске  данных на этапе эксплуатации окончательного варианта базы.

Второй метод  разбиения  отношения, основан  на  подходе  к проектированию,   отличном   от  декомпозиции,  тем  не  менее используется  многими  проектировщиками.  В  основе   подхода, называемого  некоторыми МЕТОДОМ СИНТЕЗА,  лежит утверждение (в своей простейшей форме),  что необходимо все ФЗ с одинаковыми детерминантами выделять в группы и каждой группе отводить свое собственное отношение.  Получаемые отношения проверяются на их соответствие НФБК.  В последнем примере были две зависимости с различными детерминантами.  Согласно методу синтеза каждой  ФЗ следует  выделить  ее  отношение  -  R1(A,B) и R2(C,B).  Метод синтеза может быть использован как  самостоятельно,  так  и  в сочетании  с  методом  декомпозиции.

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

1.5.5. Избыточные функциональные зависимости

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

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

Приемы удаления избыточных ФЗ

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

Если A -> B и B -> C, то A -> C - транзитивная зависимость.

Здесь следует подчеркнуть два момента:

1. Транзитивная   зависимость   A  ->  C,  приведенная  в определении выше, является вполне корректной зависимостью.

2. Если  A  ->  B,  B  ->  C  и A -> C входят в набор ФЗ, то A -> C является избыточной и ее использование в процессе    проектирования    не   требуется. Действительно, транзитивная зависимость A -> C  причинит  больше  вреда,  чем пользы  при  проектировании,  и ее следует исключить из набора перед началом проектирования.

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

Вид первый формулируется следующим образом: Если А  ->  В,  то  А,Z  ->  В  является  корректной,   но избыточной  ФЗ.  Атрибут  Z  был добавлен к  детерминанту А без привнесения   какой-либо   новой    информации    в    процесс проектирования.  (Здесь А,В и Z - атрибуты,  каждый из которых может быть составным).

Второй вид  возникает  в случае добавления к обоим частям данной ФЗ одного и того же атрибута с целью формирования новой зависимости:

Если А ->  В,  то  А,Z  ->  B,Z  является  корректной,  но избыточной ФЗ.

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

Рассмотрим вкратце еще три приема удаления  избыточных ФЗ.

Два наиболее  простых  для  понимания   приема   удаления связаны   с   объединением   и   декомпозицией   ФЗ,   которые определяются следующим образом:

 Объединение ФЗ: если А -> В и А -> С, то А -> В,С.

Декомпозиция ФЗ: если А -> В,С, то А -> В и А -> С.

Пятая разновидность избыточности называется псевдотранзитивностью.

Если X -> Y и Y,W -> Z, то X,W -> Z является избыточной в силу псевдотранзитивности. Этот тип избыточности возникает в тех  случаях,  когда  в получаемых ФЗ обнаруживаются детерминанты. При обнаружении псевдотранзитивной зависимости ее необходимо удалить.

Минимальное покрытие

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

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

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

Эта процедура  завершается,  как  только  не останется ни одной избыточной ФЗ.  Оставшийся  набор  является  минимальным покрытием.

Модернизированный алгоритм проектирования БД

С учетом изложенного алгоритм декомпозиционного проектирования БД  включает следующие этапы:

1. Построение универсального отношения для БД.

2.Определение всех  ФЗ,  существующих  между  атрибутами универсального отношения.

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

4. Использование ФЗ из минимального покрытия для декомпозиции  универсального  отношения в набор НФБК -отношений.

5.Определение того, находятся  ли  полученные  отношения в НФБК. Если да, то   проектирование    завершается, если    нет, то отношения не  находящиеся  в НФБК должны быть разложены на два отношения.

5.Повторение шага 5 для каждого нового отношения, полученного в результате декомпозиции. Проектирование завершается, когда все отношения будут находиться в НФБК.

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

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

1.6. Метод  ER - проектирования

Если число атрибутов проектируемой БД превышает 20, декомпозиционный метод проектирования становится излишне громоздким. В этом случае следует обратить внимание на другие методы.  Один из  таких методов называется "сущность-связь" или ER - методом. Он отличается от метода декомпозиции тем,  что ФЗ  привлекаются не на начальном, а на конечном этапе проектирования.

1.6.1. Сущности и связи

Представление о  методе можно получить с помощью специально подобранного примера.  Предположим,  что проектируется БД, предназначенная для хранения информации о преподавателях института и о тех дисциплинах,  которые они читают.  Двумя  главными объектами,  или сущностями, представляющими в данном случае интерес,  являются ПРЕПОДАВАТЕЛЬ и ДИСЦИПЛИНА.  Эти две  сущности соотносятся  с помощью связи ЧИТАЕТ,  что позволяет нам сказать ПРЕПОДАВАТЕЛЬ ЧИТАЕТ ДИСЦИПЛИНА.

Это утверждение  может  быть  представлено  графически.

ПРЕПОДАВАТЕЛЬ     ЧИТАЕТ      ДИСЦИПЛИНА

                       П1                                                Д1

                       П2                                                Д2

                       П3                                                Д3

                       П4                                               Д4

Рис. 15. Пример диаграммы ЕR - экземпляров.

Графическое изображение, приведенное на рис.15, называют диаграммой ЕR – экземпляров. Она  показывают,  какую  в  точности дисциплину  читает каждый преподаватель.  В этом примере каждый преподаватель идентифицируется номером  преподавателя  (нп),  и каждая дисциплина - номером дисциплины (нд).

Рис.16. называется диаграммой ЕR - типа и содержит ту  же общую информацию, которая содержится на рис.15.

                              1                                        1

 

нп….                                                                    нд….

Рис.16. Пример диаграммы  ЕR - типа

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

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

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

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

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

Тесно связано с предыдущими третье важное понятие,  обсуждавшиеся ранее, а именно атрибут. Атрибут, есть свойство сущности.  Например,  атрибутами,  могущими быть свойствами  сущности ДИСЦИПЛИНА,  являются: номер дисциплины, семестр в котором она преподавалась,  предыдущая дисциплина, на которую она базируется, число часов на дисциплину и т.д. Атрибутами сущности ПРЕПОДАВАТЕЛЬ  являются: номер - преподавателя, ученая степень, ученое звание, стаж работы и т. д.

Возвращаясь к рис. 15 и 16 отметим, что на диаграмме ER  - экземпляров названия всех сущностей помещены над  экземплярами этих сущностей и в них использованы прописные буквы, в то время как каждый экземпляр сущности идентифицируется  значениями атрибута.  Так ДИСЦИПЛИНА является сущностью,  а Д1 - конкретным экземпляром сущности. Связь также именуется, и  ее  название,  составленное из прописных букв,  размещается над экземплярами связи,  при этом экземпляр каждой отдельной  связи специфицируется линией между теми двумя экземплярами сущностей, которые эта связь соединяет.  Экземпляр связи между Д2  и  П3, например,  означает, что преподаватель с номером П3 читает дисциплину с номером Д2.

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

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

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

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

1.6.2. Степень связи

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

     ПРЕПОДАВАТЕЛЬ     ЧИТАЕТ      ДИСЦИПЛИНА

                        П1                                                Д1

                        П2                                                Д2

                        П3                                                Д3

                        П4                                                Д4

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

     ПРЕПОДАВАТЕЛЬ     ЧИТАЕТ      ДИСЦИПЛИНА

                 П1                                                    Д1

                 П2                                                    Д2

                 П3                                                    Д3

                                                                          Д4

б) Степень связи равна 1:1 и класс принадлежности сущности ПРЕПОДАВАТЕЛЬ является обязательным.

     ПРЕПОДАВАТЕЛЬ     ЧИТАЕТ      ДИСЦИПЛИНА

                П1                                                       Д1

                П2                                                      Д2

                П3                                                      Д3

                П4

в) Степень связи равна 1:1 и класс принадлежности сущности ДИСЦИПЛИНА является обязательным.

     ПРЕПОДАВАТЕЛЬ     ЧИТАЕТ      ДИСЦИПЛИНА

                 П1                                                      Д1

                 П2                                                      Д2

                 П3                                                       Д3

                 П4                                                       Д4

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

Рис. 17.  Различные  классы принадлежности для случая

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

Рис. 17  иллюстрирует все возможные формы диаграммы ER - экземпляров,  которые могли бы  существовать  между  сущностями ПРЕПОДАВАТЕЛЬ  и  ДИСЦИПЛИНА в том случае,  когда степень связи равна 1:1. Каждая диаграмма представляет собственный набор возможных  правил  функционирования  учебного заведения. Только одна из этих диаграмм может быть истинной для заведения в каждый момент времени. Перечень правил, которых следует придерживаться для соответствия каждой диаграмме, представленной на рис. 17 формулируется следующим образом:

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

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

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

Рис. 17, г. Каждый преподаватель читает только одну дисциплину,  и каждая дисциплина читается только одним преподавателем.

Тот факт, что каждый экземпляр сущности, расположенный как в левой,  так и в правой частях диаграммы,  связывается  максимально с одним экземпляром сущности, расположенным в противоположной части диаграммы, дает основание определить каждую из диаграмм экземпляров,  приведенных на рис. 17, как имеющую степень связи 1:1.

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

Рис.18 иллюстрирует возможность более компактной  формы представления информации, приведенной на рис.17.

                               1                                      1

 нп….                                                                 нд ….

а)

                               1                                      1

 нп….                                                                нд

б)

                                1                                     1

 нп….                                                           нд

в)

          1                                      1

 нп….                                                           нд

г)

Рис. 18.  Диаграммы ER-типа,  соответствующие

диаграммам экземпляров, приведенных на рис.17.

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

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

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

СЛУЧАЙ 1.  Каждый преподаватель может читать одновременно несколько дисциплин,  но  каждая  дисциплина читается не более чем одним преподавателем.

СЛУЧАЙ 2.  Каждый  преподаватель  читает  не  более одной дисциплины, но каждая дисциплина может читаться сразу несколькими преподавателями.

СЛУЧАЙ 3.  Каждый преподаватель  может  читать  несколько дисциплин и  каждая дисциплина может читаться несколькими преподавателями.

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

1.6.3. Переход от диаграмм   ER – типа к отношениям

Связь ЧИТАЕТ,  существующая между сущностями ПРЕПОДАВАТЕЛЬ и ДИСЦИПЛИНА называется бинарной, поскольку она связывает только  две  сущности.  Бинарные  связи встречаются наиболее часто.

Рассмотрим,  как с использованием диаграммы ER-типа можно получить отношения, служащие основой построения БД.

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

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

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

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

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

Предварительные отношения для бинарных связей степени 1:1

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

При попытке определить как много отношений необходимо  для размещения  информации,  содержащейся в бинарных связях степени 1:1,  подобных приведенным на диаграммах ER-типа (см. рис.18), простейшим решением, на которое можно надеяться, является необходимость одного отношения. Пусть это отношение называется ПРЕПОДАВАТЕЛЬ  и все атрибуты помещаются в это одно отношение.  На рис.19 приведен экземпляр такого отношения в том случае, когда класс  принадлежности является обязательным для обеих сущностей (см.  рис.17, г и 18,г).  В этом отношении сущность ПРЕПОДАВАТЕЛЬ была дополнена двумя типичными атрибутами:  фамилия преподавателя (пфам) и телефон-преподавателя  (птел).  Один  атрибут добавлен к сущности ДИСЦИПЛИНА:  название дисциплины.

ПРЕПОДАВАТЕЛЬ (нп, пфам, птел, нд, название).

         ПРЕПОДАВАТЕЛЬ

нп

пфам

птел

нд

название

П1

Кулаков

523298

Д1

ВМ

П2

Быков

536554

Д2

ТМ

П3

Пухова

426521

Д3

ВТ

П4

Ахмеров

348654

Д4

Физика

Рис.19. Экземпляр единичного отношения, в котором

содержатся данные в соответствии с рис.17,г и 18, г.

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

Ключ сущности ПРЕПОДАВАТЕЛЬ был избран  в  качестве  первичного ключа для отношения,  но также может быть использован ключ сущности ДИСЦИПЛИНА.

Итак, можно  сформулировать первое правило генерации отношения.

ПРАВИЛО 1. Если степень бинарной связи 1:1 и класс принадлежности обеих сущностей является  обязательным,  то  требуется только  одно отношение,  первичным ключом этого отношения может быть ключ любой из двух сущностей. 

Если степень связи равна 1:1 и класс принадлежности одной сущности является обязательным,  а другой - не обязательным, то одного отношения недостаточно.  На рис.20 приведен экземпляр отношения в том случае,  когда  класс  принадлежности  сущности ПРЕПОДАВАТЕЛЬ является обязательным, а сущности ДИСЦИПЛИНА – не обязательным (см.  рис. 17,б и 18,б).  В этом случае пробелы появляются во всех кортежах,  содержащих информацию о дисциплинах не читаемых ни одним из преподавателей. Пробелы обозначаются знаками "-".

     ПРЕПОДАВАТЕЛЬ (нп, пфам, птел, нд, название)

ПРЕПОДАВАТЕЛЬ

нп

пфам

птел

нд

название

П1

Кулаков

523298

Д1

ВМ

П2

Быков

536554

Д2

ТМ

П3

Пухова

426521

Д3

ВТ

--

--

--

Д4

Физика

Рис.20. Экземпляр единичного отношения, в котором содержатся данные в соответствии с рис. 17,б и 18,б.

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

     ПРЕПОДАВАТЕЛЬ (нп, Пфам, Птел, нд, )

     ДИСЦИПЛИНА (нд ,название)

         ПРЕПОДАВАТЕЛЬ                          ДИСЦИПЛИНА

нп

пфам

птел

нд

нд

название

П1

Кулаков

523298

Д1

Д1

ВМ

П2

Быков

536554

Д2

Д2

ТМ

П3

Пухова

426521

Д3

Д3

ВТ

Рис.21. Экземпляры двух отношений, в которых содержатся   данные, приведенные на рис.17,б и 18,б.

Итак, второе правило генерации отношений.

ПРАВИЛО 2.  Если  степень бинарной связи равна 1:1 и класс принадлежности одной сущности является обязательным, а другой - необязательным,  то  необходимо построение двух отношений.  Под каждую сущность необходимо выделение одного отношения, при этом ключ сущности должен служить первичным ключом для соответствующего отношения.  Кроме того,  ключ сущности, для которого класс принадлежности является не обязательным, добавляется в качестве атрибута в отношение,  выделенное для сущности  с  обязательным классом принадлежности.

Если отклониться от правила 2 и добавить в отношение ДИСЦИПЛИНА в качестве атрибута ключ сущности ПРЕПОДАВАТЕЛЬ (нп), а в отношение ПРЕПОДАВАТЕЛЬ ничего не добавлять, то в этом случае в отношении ДИСЦИПЛИНА появятся пробелы во всех кортежах в поле нп, содержащих информацию о дисциплинах, которые не читаются.

Воспользовавшись этим правилом в  ситуации,  описанной  на рис.17,в и 18,в, где класс принадлежности сущности ДИСЦИПЛИНА является обязательным,  а сущности ПРЕПОДАВАТЕЛЬ -  необязательным, получим следующие отношения:

    ПРЕПОДАВАТЕЛЬ (нп, пфам, птел).

    ДИСЦИПЛИНА (нд, название, нп).

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

   ПРЕПОДАВАТЕЛЬ

нп

пфам

птел

нд

название

П1

Кулаков

523298

Д1

ВМ

П2

Быков

536554

--

--

П3

Пухова

426521

Д3

ВТ

--

--

--

Д4

Физика

    а) Использование одного отношения

            ПРЕПОДАВАТЕЛЬ                        ДИСЦИПЛИНА

нп

пфам

птел

нд

нд

название

П1

Кулаков

523298

Д1

Д1

ВМ

П2

Быков

536554

--

Д2

ТМ

П3

Пухова

426521

Д3

Д3

ВТ

    б) Использование двух отношений

        ПРЕПОДАВАТЕЛЬ                            ДИСЦИПЛИНА      

нп

пфам

птел

нд

название

П1

Кулаков

523298

Д1

ВМ

П2

Быков

536554

Д3

ВТ

П3

Пухова

426521

Д4

Физика

                                         ЧИТАЕТ

нп

нд

П1

Д1

П3

Д3

    в) Использование трех отношений

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

Проблемы возникают везде,  за исключением случая использования трех отношений. В этом случае отношение ПРЕПОДАВАТЕЛЬ содержит информацию о всех преподавателях, отношение ДИСЦИПЛИНА - обо всех дисциплинах,  а отношение ЧИТАЕТ содержит номера  дисциплины  только тех,  которые читаются,  и номера преподавателей только тех, которые читают лекции по дисциплинам.

Итак, третье правило генерации отношений.

ПРАВИЛО 3.  Если степень бинарной связи равна 1:1 и класс принадлежности ни одной сущности не является обязательным, то необходимо использовать три отношения:  по одному для каждой сущности, ключи которых служат в качестве  первичных ключей в  соответствующих отношениях,  и одного для связи. Среди своих атрибутов отношение, выделяемое связи, будет иметь по одному ключу сущности от каждой сущности.

Предварительные отношения для бинарных связей степени 1:N.

На рис.23 показан экземпляр отношения ПРЕПОДАВАТЕЛЬ,  для случая, когда каждый преподаватель может читать более чем одну дисциплину и нет дисциплин, которые не читаются и имеются преподаватели, которые ничего не читают. Кроме того, каждая дисциплина читается только одним преподавателем. Т.е. рассматривается случай, когда класс принадлежности сущности ПРЕПОДАВАТЕЛЬ является необязательным, а сущности ДИСЦИПЛИНА – обязательным, а степень связи 1:N.

ПРЕПОДАВАТЕЛЬ

нп

пфам

птел

нд

название

П1

Кулаков

523298

Д1

ВМ

П1

Кулаков

523298

Д2

ТМ

П1

Кулаков

523298

Д3

СМ

П2

Быков

536554

Д4

ХИМИЯ

П2

Быков

536554

Д5

Физика

П3

Пухова

426521

Д5

ВТ

П4

Ахмеров

348654

--

--

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

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

Решить все эти проблемы, вне зависимости от класса принадлежности 1 - связной сущности, можно согласно следующему правилу.

ПРАВИЛО 4.  Если  степень бинарной связи равна 1:n и класс принадлежности n - связной сущности является  обязательным,  то достаточным является использование двух отношений, по одному на каждую сущность, при условии, что ключ сущности каждой сущности служит в качестве первичного ключа для соответствующего отношения. Дополнительно ключ 1 - связной сущности должен быть добавлен как атрибут в отношение, отводимое n - связной сущности. 

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

  

      ПРЕПОДАВАТЕЛЬ                        ДИСЦИПЛИНА                  

нп

пфам

птел

нд

название

нп

П1

Кулаков

523298

Д1

ВМ

П1

П2

Быков

536554

Д2

ТМ

П1

П3

Пухова

426521

Д3

СМ

П1

П4

Ахмеров

348654

Д4

ХИМИЯ

П2

Д5

Физика

П2

Д5

ВТ

П3

Рис. 24.  Данные,  приведенные на рис.23 после их разнесения по двум отношениям с помощью ПРАВИЛА 4.

На рис.25,а показан экземпляр отношения ПРЕПОДАВАТЕЛЬ для случая степени связи   1:n с необязательным классом принадлежности обоих сущностей.

ПРЕПОДАВАТЕЛЬ

нп

пфам

птел

нд

название

П1

Кулаков

523298

Д1

ВМ

П1

Кулаков

523298

Д2

ТМ

П1

Кулаков

523298

Д3

СМ

П2

Быков

536554

Д4

ХИМИЯ

П2

Быков

536554

Д5

Физика

--

--

--

Д5

ВТ

П4

Ахмеров

348654

--

--

                                   а)

           ПРЕПОДАВАТЕЛЬ                        ДИСЦИПЛИНА                  

нп

пфам

птел

нд

название

нп

П1

Кулаков

523298

Д1

ВМ

П1

П2

Быков

536554

Д2

ТМ

П1

П4

Ахмеров

348654

Д3

СМ

П1

Д4

ХИМИЯ

П2

Д5

Физика

П2

Д5

ВТ

--

                                                 б)

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

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

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

Если применить ПРАВИЛО 4 к этому случаю и сформировать два отношения (см. рис. 25,б), то все проблемы будут решены за исключением одной: не исчезнут пробелы в полях номера преподавателя в новом отношении ДИСЦИПЛИНА  во всех  тех местах,  где дисциплина не читается.

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

ПРАВИЛО 5. Если степень бинарной связи равна 1:n и  класс принадлежности  n-связной сущности является необязательным,  то необходимо формирование трех отношений:  по одному  для  каждой сущности,  причем  ключ каждой сущности служит первичным ключом соответствующего отношения, и одного отношения для связи. Отношение, выделенное для связи должна иметь среди своих атрибутов ключ сущности от каждой сущности.

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

           ПРЕПОДАВАТЕЛЬ                        ДИСЦИПЛИНА                  

нп

пфам

птел

нд

название

нп

П1

Кулаков

523298

Д1

ВМ

П1

П2

Быков

536554

Д2

ТМ

П1

П4

Ахмеров

348654

Д3

СМ

П1

Д4

ХИМИЯ

П2

Д5

Физика

П2

ЧИТАЕТ

нп

нд

П1

Д1

П1

Д2

П1

Д3

П2

Д4

П2

Д5

Рис.26.Размещение  данных в  трех отношениях с помощью ПРАВИЛА 5.

В отношении ЧИТАЕТ нд является первичным ключом в силу существующей связи типа n:1 между нд и нп.

Предварительные  отношения  для  бинарных  связей степени N:M

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

ПРАВИЛО 5. Если  степень  бинарной связи равна N:M, то для хранения данных необходимо три отношения:  по одному для каждой сущности, причем ключ каждой сущности используется в качестве первичного ключа соответствующего отношения, и одного отношения для связи. Последнее отношение должно иметь в числе своих атрибутов ключ сущности каждой сущности.

Для случая, когда степень связи равна N:M и ни один класс принадлежности не является обязательным экземпляры отношений соответствуют рис. 26.

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

1.6.4. Дополнительные  конструкции,  используемые  в ER  - методе

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

Необходимость связей более высокого порядка

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

Сущностями в  данном  случае  будут  РАБОЧИЙ  и  СТАНОК и связь между ними ОБСЛУЖИВАЕТ. Типичные диаграммы ER-экземпляров и ER-типа приведены на рис.27.

    РАБОЧИЙ          ОБСЛУЖИВАЕТ         СТАНОК

           Р1                                                            С1

           Р2                                                С2

          Р3                                                             С3

          Р4            С4

               С5

                                         а)

                               1                                     1               

рфам ….                                                                сном

б)

Рис.27. Диаграммы  ER-экземпляров  (а) и ER-типа (б)

для рассматриваемого примера.

При составлении диаграмм учитывалось, что все рабочие имеют работу, фамилии рабочих уникальны, некоторые станки рабочими не обслуживаются.

Т.к. степень связи равна 1:1 и  класс принадлежности одной сущности является обязательным,  а другой - нет, то используется правило 2 при генерации предварительных  отношений. В результате получаем отношения: РАБОЧИЙ (рфам .... сном) и  СТАНОК  (сном ....).

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

    РАБОЧИЙ (рфам, нцех, тстав, сном),

    СТАНОК  ( сном, стип, дтип, мдет).

На рис.28  для  обоих  отношений  изображены диаграммы ФЗ, позволяющие заключить, что каждое отношение находится в НФБК.

                       сном                        стип

рфам               нцех         сном       мдет

                       тстав                       дтип

                  а)                               б)

Рис.28. Диаграмма  ФЗ  для  отношений  (а) РАБОЧИЙ

и (б) СТАНОК в примере ER-проектирования.

На рис.29  приводятся  типичные  экземпляры   отношений, используемых при создании БД.

    РАБОЧИЙ                                      СТАНОК

рфам

нцех

тстав

сном

сном

стип

дтип

мдет

Р1

3

500

С1

С1

Токар.

М1

Р2

3

400

С3

С1

Токар

М2

Р3

3

300

С2

С2

Фрез.

М1

Р4

3

200

С4

С3

Свер.

М1

С4

Шлиф

М3

Рис.29. Типичные экземпляры отношений РАБОЧИЙ и СТАНОК.

Эта же БД будет проанализирована при различных предположениях далее в этой теме.

В основе рассмотренного примера лежит  необходимость хранения информации о рабочих; станках, обслуживаемых рабочим;  типах деталей, обрабатываемых на этих станках. Предположим,  что нам необходимо знать, какой-тип детали предпочитает изготавливать тот или иной рабочий. Если посмотреть на диаграмму экземпляров,  изображенную на рис.27, а то может показаться естественным заключение о том,  что раз рабочий Р1 обслуживает  станок С1,  на котором обрабатываются детали 2Т и 1Т (см. рис. 29), то следовательно Р1  любит изготавливать детали 2Т и 1Т. Это может быть правдой, а может и не быть.

Если связь между сущностями РАБОЧИЙ и  ДЕТАЛЬ  существует, то эта связь, назовем ее ПРЕДПОЧИТАЕТ, должна быть представлена на диаграмме ER - экземпляров,  как это показано на рис.30.

Пунктирные линии использованы с целью выделения экземпляров связи ПРЕДПОЧИТАЕТ.  Кроме того, во избежание путаницы приведены только несколько экземпляров.

                                              

Рис. 30. Диаграмма ER - экземпляров в случае,  когда рабочие отдают предпочтение некоторым типам  деталей.

На диаграмме ER – типа связь ПРЕДПОЧИТАЕТ должна соединять сущности РАБОЧИЙ и ДЕТАЛЬ.

Из диаграммы экземпляров следует, что рабочий Р1  обслуживает станок С1, что на станке С1 обрабатывается два типа деталей, а также то, что Р1  предпочитает изготавливать деталь 1Т.  В силу того, что связь между сущностями РАБОЧИЙ и ДЕТАЛЬ имеет степень n:1 и,  поскольку каждый рабочий обслуживает только  один станок,  то при построении отношения,  отражающего ситуацию, приведенную на рис.30, единственным изменением, которое  требуется  ввести в отношения,  приведенные на рис. 29, является добавление в  отношение  РАБОЧИЙ  атрибута дтип из отношения СТАНОК (этот атрибут указывает тип детали, изготавливать который предпочитает данный рабочий).  

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

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

Рабочий Р1 обслуживает два станка – С1 и С2.

Рабочий Р2 обслуживает только станок С2.

Рабочий Р1 предпочитает  изготавливать  деталь  1Т на станке С1.

Рабочий Р1 предпочитает  изготавливать  деталь  2Т на станке С2.

Рабочий Р2 предпочитает  изготавливать  деталь 2Т на станке С2.

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

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

Рабочий                         Станок                                Деталь

Р1                                       С1                                         1Т

Р2                                        С2                                        2Т

Рис.31. Диаграмма ER  - экземпляров (а) и ER - типа (б)   в случае,  когда все связи имеют степень  m:n.

Проблема возникает из того факта,  что в данном случае все связи имеют степень m:n и,  следовательно, отсутствует уникальный путь, соединяющий вместе три экземпляра сущности единственным образом.  Причину возникновения подобной проблемы можно понять, обратив внимания на то, что каждое из утверждений "Рабочий Р1 предпочитает  изготавливать  деталь  1Т  на станке С1" и "Р1  предпочитает изготавливать деталь 2Т на станке С2" связывает вместе три информационные единицы. Та же информация не может быть заключена в  выражениях  "Р1  обслуживает станки С1 и С2";  "На станках С1 и С2 обрабатываются детали 1Т и 2Т; " Р1 предпочитает изготавливать  детали 1Т и 2Т."

Суть в том,  что информационные триплеты нельзя предоставить в виде набора из трех бинарных связей. Правильная  модель приведена на рис. 32 и требует использования трехсторонних связей.

Рабочий                                     Станок                       Деталь

                                                        С1

  Р1                                                      

                                                                                            1Т

                                                       С2

  Р2                                        

                                                           

                                                                                             2Т

                                              а)

                                 n

                  m       сном…                                  k

рфам…..                                                                 дтип….

                                                    б)

Рис. 32. Диаграммы ER - экземпляров (а) и ER - типа (б) в случае трехсторонней связи Р_С_Д.

Предварительные отношения для трехсторонних связей

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

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

(Аналогично, когда связь n-сторонняя, требуется n + 1 предварительное отношение).

Если применять  это правило к данным,  приведенным на рис. 32, то будут получены предварительные отношения:

РАБОЧИЙ (рфам .,......),

СТАНОК  (сном .,.....),

ДЕТАЛЬ  ( дтип .,......),

Р_С_Д  (рфам, сном, дтип,...).

Первичный ключ  для  Р_С_Д  не может быть определен до тех пор,  пока не будут распределены все другие атрибуты. Если воспользоваться  всеми  теми  атрибутами, которые приведены на рис.29, то  атрибуты  будут распределены  следующим образом:  отношению РАБОЧИЙ назначаются атрибуты нцех, и тстав; отношению СТАНОК будет назначен атрибут стип;  отношению ДЕТАЛЬ назначается атрибут мдет.  Отношению Р_С_Д  не  получит  никаких  "других"  атрибутов. Первичный  ключ  для  Р_С_Д будет составным < рфам,сном> в том случае,  если каждый рабочий предпочитает изготавливать  на станке только один тип детали.  Если число предпочитаемых рабочим типов детали равно двум или более для  какого-либо  станка, тогда все три атрибута отношения Р_С_Д будут составлять первичный ключ.

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

    Р_С_Д                                                            СТАНОК

рфам

стип

дтип

сном

стип

Р1

С1

С1

Р1

С2

С1

Р2

С2

С2

Р3

С3

С3

Р4

С4

С4

РАБОЧИЙ                                                    ДЕТАЛЬ

рфам

нцех

тстав

дтип

мдит

Р1

3

500

М1

Р2

3

400

М2

Р3

3

300

М3

Р4

3

250

Рис. 33. Экземпляры отношений, полученные на основании диаграмм, приведенных на рис.32.

Использование ролей

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

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

Диаграмма  ER  - типа для этого случая показана  на рис.34. Ключом сущности для каждой сущности является табельный номер каждого служащего.

                   1                                                     n

                                    РУКОВОДИТ

мастер# ....                                                      сборщик# ....

Рис. 34. Возможный вариант ER - модели предприятия.

Предполагается, что ни один  мастер  не  руководит  другим мастером,  ни один мастер не является сборщиком и ни один сборщик не является мастером.

Поскольку связь  РУКОВОДИТ  имеет  степень 1:n и класс принадлежности каждой сущности является обязательным, то в соответствии с общим правилом будут построены два предварительных отношения:

МАСТЕР  (мастер#,......),

СБОРЩИК (сборщик#,....., мастер#).

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

Предположим, что другими, представляющими интерес атрибутами являются:

слфам - фамилия (и имя) служащего,

ртел  - номер служебного телефона мастера (сборщик служебного телефона не имеет),

дтел  - номер домашнего телефона служащего,

сладрес  - домашний адрес служащего,

тстав - почасовая тарифная ставка сборщика,

оклад - месячный оклад мастера.

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

МАСТЕР (мастер#,оклад,ртел,...),

СБОРЩИК (сборщик#, тстав,...мастер#).

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

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

мфам - фамилия (и имя) мастера,

сбфам - фамилия (и имя)сборщика,

мдтел - номер домашнего телефона мастера,

сбдтел - номер домашнего телефона сборщика,

мадрес - домашний адрес мастера,

сбадрес - домашний адрес сборщика.

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

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

                                 

                               служ#,….

                        1                                                    n

     _мастер# .,...               РУКОВОДИТ                  

мастер#,…..                                                          сборщик#,….

Рис. 35.  Использование  ролей  в ER - модели.

На рисунке СЛУЖАЩИЙ представляет  собой  сущность,  ключом которой является табельный номер. Объекты данной сущности могут играть роль либо мастера,  либо сборщика.  Два ролевых набора  МАСТЕР и СБОРЩИК - соединяются связью РУКОВОДИТ.  Стрелки, идущие от СЛУЖАЩИЙ как к сущности МАСТЕР,  так и к сущности  СБОРЩИК,  указывают на то, что сущность СЛУЖАЩИЙ является исходной, а сущности МАСТЕР и СБОРЩИК - ролями.

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

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

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

В рассматриваемом случае получены следующие  три предварительных отношения:

СЛУЖАЩИЙ (служ#,.....),

МАСТЕР   (мастер#,.......),

СБОРЩИК  (сборщик#,.....,мастер#).

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

СЛУЖАЩИЙ (служ#, слфам, дтел, сладрес),

МАСТЕР   (мастер#, оклад, ртел),

СБОРЩИК  (сборщик#, тставка, мастер#).

Отношение, полученное из исходной сущности СЛУЖАЩИЙ, содержит информацию общую для всех служащих.  Отношения, полученные  из ролей,  содержат специфическую для исполняемой роли информацию. Каждое, порождаемое ролью отношение связано с отношением,  источником  порождения  которого послужила исходная сущность,  через атрибут из общего домена (в данном примере -  табельный номер).

При рассмотрении диаграммы,  показанной на рис.35 видно, что связь РУКОВОДИТ соединяет две роли одной исходной сущности.

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

1.6.5. Последовательность проектирования БД при  использовании ER- метода

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

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

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

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

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

6. Определение для каждого  отношения  межатрибутных  ФЗ, с помощью которых проверяется соответствие отношений НФБК.

7. Отношения не находящиеся  в  НФБК подвергаются декомпозиции с последующей проверкой их на нахождение в НФБК.

8. П.7 повторяется до тех пор пока все отношения не будут находится в НФБК.

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

1.6.6. Проверка отношений на завершающейся фазе проектирования

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

1. Составляются списки  ФЗ  для  каждого  отношения.  Одна и та же ФЗ не должна появляться более  чем  в  одном  отношении. Если таковые имеются, то их необходимо удалить.  

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

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

R1(A,B)

R2(B,C,Y,Z)

R3(A,B,K)

Отношение R1 является избыточным,  т.к.  все его атрибуты присутствуют в отношении R3.

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

R1(A,C,X)

R3(D,K,F)

R5(D,E,G,H)

R7(A,B,D)

R8(A,B,E,G)

Отношение R8   является   избыточным,   т.к.   применение операции СОЕДИНЕНИЕ к R5 и R7 (общим атрибутом  является  D) даст отношение R9(A,B,E,D,G,H), которое содержит все атрибуты, присутствующие в R8.

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

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

1.7.  Другие нормальные формы

 Существует 6 нормальных форм, в которых может находится отношение: первая (1НФ), вторая (2НФ), третья (3НФ), НФБК, четвертая (4НФ) и пятая (5НФ). О     первой нормальной и НФБК мы уже говорили. Следует отметить,  что если отношение находится в N - форме, то оно находится во всех формах до  N-1.

Таким образом, если отношение находится в НФБК, то оно находится и в 1НФ и во 2НФ и в НФБК. Однако для полноты картины рассмотрим 2НФ и 3НФ.

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

Рассмотрим отношение, моделирующее сдачу текущей сессии студентами, живущими в общежитии.

R(Сном, Сфам, Кном, Тном, Дисциплина, Оценка).

Здесь первичным ключом являются атрибуты <Сном, Дисциплина>, а атрибуты Сфам, Кном и Тном зависят только от части первичного ключа Сном, т.е. в данном случае в отношении имеется неполная ФЗ. Для приведения данного отношения ко второй нормальной форме необходимо разбить его на два отношения : R1(Сном, Сфам, Кном, Тном) и R2(Сном, Дисциплина, Оценка).

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

Отношение находится в третьей нормальной форме тогда, когда оно находится во второй нормальной форме и не содержит транзитивных зависимостей.

Рассмотрим полученное в предыдущем примере отношение R1(Сном, Сфам, Кном, Тном). Это отношение находится во второй нормальной форме, однако в нем имеется транзитивная зависимость Сном -> Тном.

Для приведения этого отношения к третьей нормальной форме необходимо его разбить на два отношения R3(Сном, Сфам, Кном) и R4(Сном, Тном). При таком представлении отношений аномалия обновления не исчезла.

Рассмотрим 4НФ и 5НФ.

Ранее рассматривались случаи ФЗ, когда каждому значению детерминанта соответствовало только одно значение зависимого от него атрибута. Однако на практике можно встретить случаи, когда одному значению некоторого атрибута соответствует устойчиво постоянное множество значений другого атрибута.

Рассмотрим конкретную ситуацию. Пусть дано отношение, которое моделирует предстоящую сдачу экзаменов в сессии. Допустим оно имеет вид: R(Группа, Сном, Дисциплина).

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

R

Группа

Сном

Дисциплина

1409

111

БД

1409

111

АПП

1409

112

БД

1409

112

АПП

1410

222

БД

1410

222

АПП

….

1410

223

БД

1410

223

АПП

В данном отношении существуют две многозначные зависимости: Группа ->> Сном и Группа ->> Дисциплина.

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

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

В общем случае в отношении R( А, В, С) существует многозначная зависимость А ->> В в том случае, когда существует многозначная зависимость А ->>С.

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

Отношение R(А, В, С) можно спроецировать без потерь в отношение R1(А, В) и R2(А, С) в том случае, когда существует многозначная зависимость А ->> В|C (что равнозначно наличию двух зависимостей А ->> В и А ->>С).

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

Отношение R находится в четвертой нормальной форме в том случае, если в случае существования многозначной зависимости А ->> В все остальные атрибуты R функционально зависят от А.

В рассмотренном примере можно произвести декомпозицию исходного отношения в два отношения: R1( Группа, Сном) и R2(Группа, Дисциплина)

R1                                                   R2

Группа

Сном

Группа

Дисциплина

1409

111

1409

БД

1409

112

1409

АПП

1410

222

1410

БД

1410

223

1410

АПП

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

Последней нормальной формой является пятая нормальная форма, которая связана с анализом нового вида зависимостей, зависимостей «проекция соединения» (projectjoin зависимости, обозначаемые как PJ – зависимости).

5НФ редко используется на практике. В большой степени она является теоретическим исследованием. Очень тяжело определить само наличие зависимостей «проекция соединения», потому что утверждение о наличии такой зависимости делается для всех возможных состояний БД, а не только для текущего экземпляра отношения R. Однако знание о возможном наличии подобных зависимостей, даже теоретически, необходимо.

1.8. Контрольные вопросы

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

1.8.2. Какие модели используются для представления данных?

1.8.3. Перечислите признаки, по которым классифицируются базы данных.

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

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

1.8.6. Какие приемы используются для удаления избыточных ФЗ?.

1.8.7. В каких случаях при декомпозиции отношений происходит потеря ФЗ?

1.8.8. Назовите правила перехода от диаграммы ER – типа к набору отношений при различных степенях связи и классах принадлежности сущностей.

1.8.9. Каков алгоритм проектирования базы данных при использовании ER – метода?

1.8.10. Какие проверки необходимо осуществить на завершающей фазе проектирования базы данных?

1.8.11. Какие отношения находятся во второй, третьей и четвертой нормальной формах?

Часть 2. Специальные аспекты работы с базами данных

2.1. Защита данных в базе

2.2.1. Общие вопросы защиты данных

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

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

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

Можно выделить  уровни доступа к БД для различных категорий пользователей; 1) неограниченный доступ ко всем отношениям в БД и их  объектам; 2) неограниченный доступ к группе отношений и их объектам;  3) ограниченный доступ к группе отношений и их объектам.

На уровне отношения различают следующие уровни доступа:

1)  неограниченный доступ ко всему отношению для всех типов операций;

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

3)  доступ к любой части отношения, но без права  изменения его содержимого;

4)  доступ к любой части отношения,  но с  правом  изменения  значений только для атрибутов A1, А2, . . Аn;

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

6)  доступ только  к одному кортежу  отношений без права изменения содержимого этого кортежа;

7) неограниченный доступ только к атрибутам A1, А2, . . An  отношения для всех типов операций и запрет доступа к остальным атрибутам отношения;

8) доступ только к атрибутам А1, А2, . . Аn  отношения без права изменения их значений и запрет доступа  к  остальным атрибутам отношения;

9)  доступ только к атрибутам A1,A2,…An отношения с правом  изменения значений только для атрибутов A1..Ap,  где (A1 . . Ap)(A1, А2, ... An) и запрет доступа к остальным атрибутам отношения;

10) доступ в соответствии с пунктами 1,3,4,5,6,7,8,9, но с ограничением по интервалу времени (с t1 no t2);

11) доступ в соответствии с пунктами 1,3,4,5,6,7,8,9, но с разрешением изменения значений  атрибутов  только  в случае выполнения условий F1, F2, . . Fn соответственно для значений атрибутов A12,..An (например, если значение атрибута не превышает некоторой величины Z);

12) разрешение права применения вычислительных операторов  (суммирование, вычитание  и т.п.) к атрибутам А12,..Аn, без права доступа к этим атрибутам или изменения их значений.

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

Поскольку большинство  СУБД  работает  под управлением операционных систем ЭВМ, для защиты данных в БД широко применяются средства защиты, представляемые операционными системами.

Рассмотрим основные методы и приемы зашиты данных.

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

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

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

Аксиома безопасности. Если комбинация атрибутов А доступна (запрещена) пользователю Х в зависимости от условия В, то каждая подкомбинация А также доступна (запрещена) пользователю Х по условию В.

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

1. Все ли отношения, упомянутые в запросе, доступны пользователю Х ?

2. Все ли объекты, упомянутые в запросе, доступны пользователю Х ?

3. Все ли комбинации атрибутов, упомянутые в запросе, доступны пользователя X?

4. Задано ли квалифицирующее выражение, которое ограничивает для пользователя Х  диапазон значений атрибутов,  и если да, то лежат ли их значения внутри диапазона, доступного для X?

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

Защита данных при статической обработке. Кроме обычных проблем предотвращения несанкционированного доступа к БД  для статических процедур существуют свои специфические проблемы защиты данных.

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

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

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

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

2.2.2. Реализация защиты данных в различных системах

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

Эти два подхода отличаются следующими свойствами:

- В случае избирательного управления некоторый пользователь обладает различными правами (привилегиями или полномочиями) при работе с данными объектами. Разные пользователи могут обладать разными правами доступа к одному и тому же объекту. Избирательные права характеризуются значительной гибкостью.

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

Для реализации избирательного принципа предусмотрены следующие методы:

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

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

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

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

- Пользователю может быть назначена одна или несколько ролей.

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

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

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

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

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

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

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

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

Управление доступом в SQL

В стандарте SQL определены два оператора: GRANT и REVOKE предоставления и отмены привилегий соответственно.

Оператор предоставления привилегий имеет следующий формат:

GRANT {<список действий> | ALL PRIVILEGES }

ON <имя_объекта>

ТО {<имя_пользователя> | PUBLIC }

[WITH GRANT OPTION ]

Здесь список действий определяет набор действий из обще допустимого перечня действий над объектом данного типа.

Параметр ALL PRIVILEGES указывает, что разрешены все действия из допустимых для объектов данного типа.

<имя_объекта> — задает имя конкретного объекта: таблицы, представления, хранимой процедуры, триггера.

<имя_пользователя> или PUBLIC определяет, кому предоставляются данные привилегии.

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

Рассмотрим пример, пусть у нас существуют три пользователя с абсолютно уникальными именами user1, user2 и user3. Все они являются пользователями одной БД.

Userl создал объект Tab1, он является владельцем этого объекта и может передать права на работу с эти объектом другим пользователям. Допустим, что пользователь user2 является оператором, который должен вводить данные в Tab1 (например, таблицу новых заказов), а пользователь user3 является менеджером отдела, который должен регулярно просматривать введенные данные.

Для объекта типа таблица полным допустимым перечнем действий является набор из четырех операций: SELECT, INSERT, DELETE, UPDATE. При этом операция обновления может быть ограничена несколькими столбцами.

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

GRANT {[SELECT][.INSERT][.DELETE][.UPDATE

(<список  столбцов>)]}  ON <имя_таблицы>

TO {<имя_пользователя> | PUBLIC }

[WITH GRANT  OPTION ]

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

GRANT INSERT

ON Tab1

TO user2

GRANT SELECT

ON Tab1

TO user3

Эти назначения означают, что пользователь user2 имеет право только вводить новые строки в отношение Tab1, а пользователь user3 имеет право просматривать все строки в таблице Tab1.

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

GRANT SELECT, UPDATE (SENA)

ON Tab1

TO user3

Если наш пользователь user1 предполагает, что пользователь user4 может его замещать в случае его отсутствия, то он может предоставить этому пользователю все права по работе с созданной таблицей Tab1.

GRANT ALL PRIVILEGES

ON Tab1

TO user4

WITH GRANT OPTION

В этом случае пользователь user4 может сам назначать привилегии по работе с таблицей Tab1 в отсутствие владельца объекта пользователя user1. Поэтому в случае появления нового оператора пользователя user5 он может назначить ему права на ввод новых строк в таблицу командой:

GRANT INSERT

ON Tab1

TO user5

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

GRANT SELECT, UPDATE, DELETE

ON Tab1

TO user4

WITH GRANT OPTION

то пользователь user4 не сможет передать полномочия на ввод данных пользователю user5, потому что эта операция не входит в список разрешенных для него самого.

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

Так как представления могут соответствовать итоговым запросам, то для этих представлений недопустимы операции изменения, и, следовательно, для таких представлений набор допустимых действий ограничивается операцией SELECT. Если же представления соответствуют выборке из базовой таблицы, то для такого представления допустимыми будут все 4 операции- SELECT, INSERT, UPDATE и DELETE.

Для отмены ранее назначенных привилегий в стандарте SQL определен оператор REVOKE. Оператор отмены привилегий имеет следующий синтаксис:

REVOKE {<список операций>| ALL PRIVILEGES}

ON <имя_объекта>

FROM {<список пользователей | PUBLIC }

(CASCADE | RESTRICT }

Параметры CASCADE или RESTRICT определяют, каким образом должна производиться отмена привилегий. Параметр CASCADE отменяет привилегии не только пользователя, который непосредственно упоминался в операторе GRANT при предоставлении ему привилегий, но и всем пользователям, которым этот пользователь присвоил привилегии, воспользовавшись параметром WITH GRANT OPTION.

Например, при использовании операции

REVOKE ALL PRIVILEGES

ON Tab1

TO user4 CASCADE

будут отменены привилегии и пользователя user5, которому пользователь user4 успел присвоить привилегии.

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

REVOKE ALL PRIVILEGES

ON Tab1

TO user4  RESTRICT

не будет выполнена, потому что пользователь user4 передал часть своих полномочий пользователю user5.

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

Поэтому корректным будет следующее использование оператора REVOKE

REVOKE INSERT

ON Tab1

TO user2, user4 CASCADE

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

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

Если необходимо изменить это условие, то после создания хранимой процедуры необходимо записать оператор REVOKE

REVOKE EXECUTE

ON COUNT_EX

TO PUBLIC CASCADE

И теперь можно назначить новые права пользователю user4

GRANT EXECUTE

ON COUNT_EX

TO user4

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

GRANT CREATE TABLE ALTER TABLE DROP TABLE

ON DB_LIB

TO user1

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

В некоторых СУБД пользователь может получить права создавать БД. Например, в MS SQL Server системный администратор может предоставить пользователю main_user право на создание своей БД на данном сервере. Это может быть сделано следующей командой:

GRANT CREATE DATABASE

ON SERVER_0

TO main_user

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

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

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

Реализация системы защиты в MS SQL Server

Система управления базами данных Microsoft SQL Server 2000 имеет разнообразные средства защиты данных.

Система безопасности базируется на пользователях и учетных  записях. Каждый пользователь проходит два этапа проверки системой безопасности. На первом этапе пользователь идентифицируется с помощью регистрационного, или логическом имени и пароля, которые хранятся на сервере SQL Server или в домене Windows NT/2000 в виде учетной записи. Таким образом, пользователь проходит аутентификацию. Если данные введены правильно, пользователь подключается к SQL Server. Подключение к SQL Server, или регистрация, не дает автоматического доступа к базам данных. Для каждой базы данных сервера учетная запись должна отображаться в пользователя базы дачных. На втором этапе на основе прав, выданных человеку (или приложению) как пользователю базы данных, его учетная запись получает доступ к соответствующей базе данных. В разных базах данных одной и той же учетной записи могут соответствовать одинаковые или разные имена пользователя базы данных с разными правами доступа.

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

На уровне сервера система безопасности оперирует следующими понятиями:

- аутентификация;

- учетная запись;

- встроенные роли сервера.

На уровне базы данных используются следующие понятия:

- пользователь базы данных;

- фиксированная роль базы данных;

- пользовательская роль базы данных;

- роль приложения.

Компоненты структуры безопасности. Фундаментом системы безопасности SQL Server 2000 являются учетные записи, пользователи, роли и группы.

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

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

К средствам Transact-SQL, используемым для управления системой безопасности, относится хранимая процедура sp_addlogin. С помощью этой процедуры можно создать новую учетную запись SQL Server.

Хранимая процедура sp_grantlogin позволяет разрешить доступ к SQL Server для пользователя или группы Windows NT.

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

При создании базы данных определяются два стандартных пользователя: dbo и guest. Если учетная запись пользователя явно не отображается в пользователя  базы данных, пользователю предоставляется неявный доступ с использованием гостевого имени guest. Обычно пользователю guest   предоставляется минимальный доступ в режиме «только чтение».

Для обеспечения максимальной безопасности можно удалить пользователя guest из всех  баз данных, кроме системных баз данных master и tempdb.

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

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

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

SQL Server позволяет передавать права владения от одного пользователя другому. Чтобы удалить владельца объекта из базы данных, сначала необходимо удалить все объекты, которые он создал, или передать права на их владение другому пользователю.

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

В SQL Server реализованы два вида стандартных ролей: на уровне сервера и на уровне баз данных. При установке SQL Server 2000 создается 9 фиксированных ролей сервера и 9 фиксированных ролей базы данных. Эти роли нельзя удалить. Кроме того, нельзя модифицировать права их доступа.

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

В роль базы данных можно включать:

- пользователей SQL Server;

- роли SQL Server;

- пользователей Windows NT;

- группы Windows NT, которым предварительно предоставлен доступ к нужной базе данных.

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

Кроме указанных выше ролей существует еще одна — public. Эта роль имеет специальное назначение, поскольку ее членами являются все пользователи, имеющие доступ к базе данных. Нельзя явно установить членов этой роли, потому что все пользователи и так автоматически являются ее членами. Рекомендуется использовать эту роль для предоставления минимального доступа пользователям, для которых права доступа к объектам не определены явно. Если в базе данных разрешен пользователь guest, то установленный для роли public доступ будут иметь все пользователи, получившие доступ к SQL Server. Роль public имеется во всех базах данных, включая системные базы данных master, tempdb, msdb, model, и не может быть удалена.

Роли приложения. Система безопасности SQL Server реализована на самом низком уровне — уровне базы данных. Это наилучший, наиболее действенный метод контроля деятельности пользователей независимо от приложений, используемых ими для подключения к SQL Server.

Отличия между стандартными ролями и ролями приложения фундаментальны. Роль приложения не имеет членов, то есть пользователи SQL Server или Windows NT не могут быть добавлены в эту роль. Роль активизируется, когда приложение устанавливает соединение. Пользователь, работающий в это время с приложением, не является членом роли — только лишь его приложение использует установленное соединение.

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

Приложение может быть спроектировано так, чтобы пользователь, работающий с ним, для активизации роли приложения должен был вводить пароль. Однако чаще всего пароль вводится самим приложением незаметно для пользователя. Дополнительно для обеспечения максимальной безопасности пароль может быть зашифрован перед своей отправкой к SQL Server по сети.

Перед установлением соединения с использованием роли приложения пользователю сначала нужно получить доступ к SQL Server.

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

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

Шифрование данных. Шифрование — это метод, используемый SQL Server для изменения данных до нечитабельной формы. Шифрование гарантирует, что ценная конфиденциальная информация не будет просмотрена кем бы то ни было. Можно скопировать данные, но нельзя будет с ними ничего сделать. Для просмотра данных авторизированными пользователями используется дешифрирование.

SQL Server позволяет шифровать следующие данные:

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

- пароли учетных записей SQL Server или ролей приложения;

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

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

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

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

SQL Server обеспечивает ограничение доступа к файлам системы.

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

Права в базе данных SQL Server можно разделить на три категории:

- права на доступ к объектам баз данных;

- права на выполнение команд Transact-SQL;

- неявные права (разрешения).

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

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

Важно быть осторожным с предоставлением разрешений на доступ к данным. Необходимо внимательно контролировать права доступа, выдаваемые пользователю как непосредственно, так и через членство в группах Windows NT и ролях SQL Server. Особенно это касается больших систем с тысячами пользователей и десятками групп.

Права на доступ к объектам баз данных. Работа с данными и выполнение хранимых процедур требуют наличия класса доступа, называемого правами на доступ к объектам баз данных. Под объектами подразумеваются таблицы, столбцы таблицы, представления, хранимые процедуры. Права на доступ к объектам баз данных контролируют возможность выполнения пользователями, например, команд SELECT, INSERT, UPDATE и DELETE для таблиц и представлений. Таким образом, если пользователю необходимо добавить новые данные в таблицу, ему следует предоставить право INSERT (вставка записей в таблицу). Предоставление же пользователю права EXECUTE разрешает ему выполнение каких-либо хранимых процедур и функций.

Для различных объектов применяются разные наборы прав доступа к ним:

- SELECT, INSERT, UPDATE, DELETE, REFERENCES - эти права могут быть применены для таблицы или представления;

-  SELECT и UPDATE — эти права могут быть применены к конкретному столбцу таблицы или представления;

-  INSERT и DELETE — эти права применяются для таблицы или представления;

- EXECUTE — это право применяется только к конкретным хранимым процедурам и функциям, разрешая пользователю их выполнение.

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

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

Для управления разрешениями пользователя на доступ к объектам базы данных  используется команда GRANT, которую мы рассматривали ранее.

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

Права на исполнение команд Transact-SQL. Этот класс прав контролирует возможность создания объектов в базе данных, создания самой базы данных и выполнения процедуры резервного копирования.

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

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

Аналогичная ситуация получается и с ролями сервера.

Для конкретного действия, контролируемого разрешением, возможны три варианта состояния доступа: предоставление, запрещение и неявное отклонение.

Для запрещения пользователю доступа к объектам базы данных используется команда DENY.

Параметры данной команды аналогичны параметрам команды GRANT. Использование параметра CASCADE позволяет отзывать права не только у данного пользователя, но также и у всех тех пользователей, кому он предоставил данные права.

Для неявного отклонения доступа к объектам базы данных можно использовать команду REVOKE, синтаксис которой:

REVOKE [GRANT  OPTION FOR]

{ALL | PRIVLEGES}

<колонки_таблицы>

…………….

Параметры имеют смысл, аналогичный параметрам команд GRANT и DENY. Параметр GRANT OPTION FOR используется, когда необходимо отозвать право, предоставленное параметром WITH GRANT OPTION команды GRANT. Пользователь при этом сохраняет разрешение на доступ к объекту, но теряет возможность предоставлять это разрешение другим пользователям.

Неявное отклонение доступа позволяет более гибко конфигурировать систему безопасности.

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

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

2.2. Обеспечение целостности данных

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

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

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

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

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

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

<имя атрибута>IS  NULL и <имя атрибута> IS NOT NULL.

Если в данном кортеже (в данной строке) указанный атрибут имеет неопределенное значение, то предикат IS NULL принимает значение TRUE (Истина), а предикат IS NOT NULLFALSE (Ложь), в противном случае предикат IS NULL принимает значение FALSE а предикат IS NOT NULL принимает значение TRUE.

В стандарте SQL2 появилась возможность сравнивать не только конкретные значения атрибутов с неопределенным значением, но и результаты логических выражений сравнивать с неопределенным значением, для этого введена специальная логическая константа UNKNOWN. В этом случае операция сравнения выглядит так. Логическое выражение> IS {TRUE | FALSE | UNKNOWN}

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

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

В третьих, это поддержка ссылочной целостности (Declarative Referential Integrity, DRI).

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

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

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

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

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

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

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

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

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

Ограничения можно определять на двух уровнях:

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

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

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

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

Размещение ограничений в базе данных имеет следующие преимущества:

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

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

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

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

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

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

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

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

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

В Access ограничение Unique инициируется установкой значения "Да (Совпадения не допускаются)" для свойства Индексированное поле либо установкой значения "Да" для свойства Уникальный индекс.

Ограничения Primary Key. Ограничение Primary Key гарантирует, что каждая строка в таблице будет уникально идентифицирована значением в столбце или наборе столбцов первичного ключа. Ограничение по первичному ключу объединяет черты ограничения Unique и ограничения Not Null.

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

Некоторые СУБД (такие, как Access) могут автоматически поддерживать полную ссылочную целостность после создания ограничений Foreign Key и Primary Key. В других базах данных (таких, как SQL Server ранних версий) необходимо определить обработку ссылочной целостности отдельно (обычно в триггере). Однако в любом случае, чтобы установить в базе данных правила ссылочной целостности, необходимо определить ограничения Primary Key и Foreign Key.

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

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

В отношении один –ко - многим внешний ключ — столбец в подчиненной таблице, которая содержит идентификатор строки в главной таблице. Значение в столбце внешнего ключа равно значению в столбце первичного ключа в другой таблице.

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

Кроме рассмотренных ограничений целостности существуют:

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

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

3. Значения, которые принимает некоторый атрибут, должны быть ограничены заданным диапазоном.

4. Для  некоторого  атрибута (или комбинации атрибутов) может существовать конечный,  небольшой по размеру набор  допустимых  значений (например,  по атрибуту ОБРАЗОВАНИЕ может быть только значения НАЧАЛЬНОЕ, НЕПОЛНОЕ СРЕДНЕЕ, СРЕДНЕЕ, НЕПОЛНОЕ ВЫСШЕЕ, ВЫСШЕЕ).

5. Значение некоторого атрибута должны удовлетворять определенному формату.  

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

7. Множество значений некоторого столбца отношения  является подмножеством  значений другого  столбца  этого отношения.

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

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

2.3. Организация параллельных процессов обработки данных

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

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

Однако как  только  пользователи  получают  возможность выполнять операции изменения одних и тех же данных в  БД,  ситуация  изменяется. Вернемся к системе продажи билетов. Например, три различных оператора, находясь в различных транспортных агенствах города,  одновременно запросили один билет до Москвы на 1 февраля,  в поезде N 351, в купейном вагоне.  В этом случае будет независимо друг от  друга  выполнено  три процесса  (транзакции). Транзакция - это разовое выполнение некоторой программы (программа может быть сложной прикладной,  выраженной на одном из языков программирования, а может быть реализацией простого запроса, выраженного на языке запросов системы).

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

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

Блокировки. Решение задачи защиты данных при параллельном доступе заключается во введение блокировок для доступа к  данным.  Прежде  чем прочитать некоторое данное X, транзакция T1 обязана его заблокировать: УСТАНОВИТЬ - БЛОКИРОВКУ X.  Блокировка предотвращает  доступ  к  этому данному другой транзакции Т2.  Транзакция Т2 должна ждать,  пока транзакция T1 не закончит работу с данным Х и не разблокирует его: СНЯТЬ -БЛОКИРОВКУ X.  Если некоторая транзакция пытается блокировать уже блокированный элемент,  она становится в очередь на ожидание, пока блокировка с этого элемента данных не будет снята.

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

Модели блокировок. Для блокировки могут использоваться простая модель и модель с блокировкой для чтения и записи. Рассмотрим эти модели.

Простая модель. В данной модели не вводится различие блокировок для операции чтения или операции записи элемента X. Установление блокировки предотвращает доступ к элементу X от других транзакций как по операции чтения, так и по операции записи до тех пор, пока этот элемент не будет разблокирован.

Модель с блокировками для чтения и записи. В данной  модели  имеется  два вида доступа к элементу X: доступ только для чтения, доступ для чтения и записи. Соответственно различают два типа команд блокировки.

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

2. Команда блокировки элемента Х по записи. Команда блокировки по записи соответствует команде блокировки в простой  модели,  т.е.  предотвращается доступ  к  элементу Х от других транзакций по чтению и по записи. Если некоторая транзакция установила блокировку элемента Х  по записи, то никакая другая транзакция не сможет его заблокировать ни по записи, ни по чтению. Блокировка по чтению и блокировка по записи снимается одной командой; СНЯТЬ - БЛОКИРОВКУ X. В модели допускается, что транзакция может вначале устанавливать блокировку элемента X  по  чтению, а затем блокировку элемента X по записи.

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

Бесконечные ожидания могут, при определенных условиях,  появится  в любой системе с параллельным выполнением процессов.  Например, элемент Х заблокирован выполняющейся транзакцией  T1.  Поступившая  в  систему транзакция Т2, которой необходима работа с этим элементом, переходит в состояние ожидания.  В момент разблокировки элемента Х транзакцией T1, в систему поступает транзакция Т3, которой также требуется элемент Х и перехватывает инициативу по его блокировке и т.д. В таких условиях не  исключена возможность,  что транзакция Т2 будет все время находится в состоянии ожидания. Чтобы избежать бесконечного ожидания, система блокировок должна регистрировать все поступившие запросы и предоставлять им возможность блокировок требуемых элементов по правилу “первый вошел - первый обслуживается”.

Для пояснения тупиковой ситуации рассмотрим алгоритм двух программ П1 и П2.

Программа П1

1 шаг - войти в программу;

2 шаг - установить блокировку А;

3 шаг - читать А;

4 шаг - установить блокировку В;

5 шаг - читать В;

5 шаг - выполнить совместную обработку А и В;

7 шаг - писать А;

8 шаг - снять блокировку с А;

9 шаг - писать В;

10 шаг - снять блокировку с В;

11 шаг - выйти из программы.

Программа П2

1 шаг - войти в программу;

2 шаг - установить блокировку В;

3 шаг - читать В;

4 шаг - установить блокировку А;

5 шаг - читать А;

5 шаг - установить блокировку С;

7 шаг - читать С;

8 шаг - выполнить совместную обработку А, В и С;

9 шаг - писать В;

10 шаг - снять блокировку  с В;

11 шаг - писать А;

12 шаг - с н я т ь  б л о к и р о в к у   с  А;

13 шаг - писать С;

14 шаг - снять блокировку с А;

15 шаг - выйти из программы.

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

Существуют следующие подходы к разрешению тупиковых ситуаций.

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

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

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

2.4. Восстановление БД

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

2.4.1. Уровни восстановления.

Укрупнено можно выделить три уровня восстановления;

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

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

3. Длительное  восстановление. При разрушении базы данных в результате дефекта на диске осуществляют восстановление с помощью копии базы данных.  Затем воспроизводят результаты выполненных с момента снятия копии логических элементов работы и возвращают систему в состояние на момент разрушения.

2.4.2. Восстановление и логический элемент работы

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

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

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

Требования к ЛЭР

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

2. Необходимо, чтобы ЛЭР допускал возможность возврата в первоначальное состояние.

3. Необходимо  иметь возможность   воспроизведения  процесса  выполнения  ЛЭР.

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

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

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

Для повышения  эффективности выполнения отката ЛЭР записи системного журнала группируют по всем объектам, подлежащим изменению. По  окончании  ЛЭР копию  можно  отбросить.

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

При двухстраничном способе нет необходимости фиксировать в журнале каждое изменение значений изменяемых объектов.  Начальное  значение изменяемого объекта  можно  зафиксировать  в журнале только один раз -при первоначальном его изменении ЛЭР.  Необходимость записи  в  журнал появляется также при прохождении точки фиксации.

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

2.4.3. Промежуточное восстановление

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

Копия

Контрольная точка 1-го типа  Контрольная точка 2-го типа

                                                              Откат

                                                                   Раскрутка

Рис.36.Промежуточное и длительное восстановление.

Существует  два типа  контрольных  точек.

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

2. Контрольная  точка  устанавливается  при наличии выполняющихся ЛЭР. В данном случае в контрольной точке фиксируют  состояние  рабочих областей всех выполняющихся ЛЭР. При появлении сбоя осуществляется откат в состояние контрольной точки.  Для ЛЭР, который выполнялся в момент установления контрольной  точки,  откат  осуществляется  до его начального состояния и далее аналогично п.1.

2.4.4. Длительное восстановление

Если при возникновении сбоя БД разрушается, операция отката ЛЭР в начальное состояние становится бессмысленной. Для восстановления БД после таких сбоев периодически делают копии всей БД. С помощью такой копии воспроизводят состояние БД на момент снятия копии, а затем, используя системный журнал, осуществляют раскрутку до последней перед сбоем контрольной точки (рис.4.1).

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

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

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

2.5.1. Теоретико-множественные операции реляционной алгебры

Объединением двух отношений называется отношение, содержащее множество кортежей, принадлежащих либо первому, либо второму исходным отношениям, либо обоим отношениям одновременно.

Пусть заданы два отношения R1 = { r1 } , R2 = {r2}, где r1 и r2 — соответственно кортежи отношений R1  и R2, то объединение

R3 = R1 U R2 = { r r R1 v r R2}. Здесь r — кортеж нового отношения, v -операция логического сложения «ИЛИ».

Пересечением отношений называется отношение, которое содержит множество кортежей, принадлежащих одновременно и первому и второму отношениям R1 и R2.

R4 = R1 Ç R2 ={ г | r Î R1Ù r Î R2 }  здесь Ù — операция логического умножения (логическое «И»).

 Разностью отношений R1 и R2 называется отношение, содержащее множество кортежей, принадлежащих R1 и не принадлежащих R2:

R5 = R1 \ R2 = { r | r Î R1 Ù r Ï R2}

Следует отметить, что операции Объединение и Пересечение являются коммутативными операциями, то есть результат операции не зависит от порядка аргументов в операции. Операция же Разности является принципиально несимметричной операцией, то есть результат операции будет различным для разного порядка аргументов. Кроме перечисленных трех теоретико-множественных операций в рамках реляционной алгебры определена еще одна теоретико-множественная операция — расширенное декартово произведение. Эта операция не накладывает никаких дополнительных условий на схемы исходных отношений, поэтому операция расширенного декартова произведения, обозначаемая R1 Ä R2, допустима для любых двух отношений. Но прежде чем определить саму операцию, введем дополнительно понятие конкатенации, или сцепления, кортежей

Сцеплением, или конкатенацией, кортежей с = <c1,c2 , ..., c n> и q = <q1,q 2, …, q m> называется кортеж, полученный добавлением значений второго в конец первого. Сцепление кортежей с и q обозначается как (с , q).

(с, q) =  < c1,  c 2,….. c n ,  q 1, q 2, …. , q m>

Здесь n — число элементов в первом кортеже с, m — число элементов во втором кортеже q.

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

Расширенным декартовым произведением отношения R1 степени n со схемой Sri = (A1, А2, .. , An) и отношения R2 степени m со схемой Sr2 = (В1, В2, . , Вm) называется отношение R3 степени n+m  со схемой    SR3 = (А1, А2,. , An, В1, В2, .., Вm), содержащее кортежи, полученные сцеплением каждого кортежа r отношения R1 с каждым кортежем q отношения R2. То есть если R1  = { r }, R2 = { q }, то R1 Ä R2 = {(r, q) ½r Î R1 Ù q Î R2}.

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

(R1 U R2) \ (R1 \ R2) \ (R2 \ R1).

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

2.5.2. Специальные операции реляционной алгебры

Первой специальной операцией реляционной алгебры является горизонтальный выбор, или операция фильтрации, или операция ограничения отношений, или выборка.

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

R[a(r)]  = {r | r Î R Ù a(r) = "Истина"}, где a(r) - условие.

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

Следующей специальной операцией является операция проектирования или проекции.

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

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

Следующей специальной операцией реляционной алгебры является операция соединения.

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

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

Отношение, полученное в результате соединения двух отношений является подмножеством декартова произведения этих отношений. В полученном отношении ряд кортежей исходных отношений может отсутствовать.

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

Вариантом операции соединения является операция условного соединения.

Пусть R = { r }, Q = { q } — исходные отношения,

SR, SQ  — схемы отношений R и Q соответственно.

SR = (A1, А2, ... , Аk); SQ = (B1, В2, ... , Вm), где Аi, Bj  — имена атрибутов в схемах отношений R и Q соответственно.

При этом полагаем, что заданы наборы атрибутов А и В

А Í { Аi }i=1,k; В Í { Bj } j=1,m

и эти наборы состоят из q-сравнимых атрибутов.

Тогда соединением отношений R и Q при условии b будет подмножество декартова произведения отношений R и Q, кортежи которого удовлетворяют условию b, рассматриваемому как одновременное выполнение условий:

- ri qi Вi : i =l,k, где k — число атрибутов, входящих в наборы А и В, а qi — конкретная операция сравнения.

- Ai qi Bi D; qi — i-й предикат сравнения, определяемый из множества допустимых на домене Di  операций сравнения.

R [ b ]  Q = { (r,q) ½ (r, q) ½rqi q.Bi  = «Истина», i =1,k}

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

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

2.6. Контрольные вопросы

2.6.1. Что понимается под безопасностью и секретностью данных?

2.6.2. Назовите уровни доступа к базе данных и к отношениям.

2.6.3. Как осуществляется управление доступом к данным?

2.6.4. Каким образом реализована система зажиты данных в MS SQL Server?

2.6.5. Что понимается под целостностью данных?

2.6.6. Назовите аспекты поддержки целостности в реляционной модели данных.

2.6.7. Какие типы ограничений используются в базе данных?

2.6.8. В чем особенности параллельных процессов обработки данных?

2.6.9. Какие уровни восстановления баз данных существуют и их особенности?

2.6.10. Какие операции реляционной алгебры относятся к теоретико-множественным?

2.6.11. Перечислите специальные операции реляционной алгебры.

Часть 3. Разработка приложений для работы с базами данных

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

3.1. Краткий обзор СУБД

Для использования на ПК, совместимых с IBM PC, большое распространение получили так называемые dBASE – подобные СУБД. Известно, по крайней мере, три семейства таких СУБД (dBASE, FoxPro и Clipper), однако версий оригинальных систем и их адаптированных вариантов гораздо больше. Отличаясь, друг от друга используемыми командными языками, все эти СУБД используют одни и те же оперативные файлы с расширением .dbf, формат которых стал на некоторое время своеобразным стандартом баз данных.

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

Однако в целом системы, построенные на основе файлов .dbf, следует считать устаревшими. Многие механизмы реляционных БД в dBASE – подобных системах либо не поддерживаются, либо создаются пользователями и программистами «кустарным» способом.

Большую популярность до сего времени имеют и другие СУБД ( с другим форматом файлов) – Paradox, Clarion, dv_Vista и т.д. Cледует подчеркнуть, что перечисленные системы ведут родословную от MS DOS, однако нынче почти все они усовершенствованы и имеют версии для Windows.

Среди современных реляционных систем наиболее популярны СУБД для WindowsAccess фирмы Microsoft, Approach фирмы Lotus, Paradox фирмы Borland.

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

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

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

Среди ведущих производителей таких систем фирмы Oracle, Centura, Sybase, Informix, Microsoft и другие.

Следующее направление в развитии СУБД это объекто – реляционные и объектные СУБД. Объектно – реляционные базируются на реляционных СУБД со встроенной поддержкой объектной ориентации. К таким СУБД относятся DB2 и Oracle фирм IBM и Oracle соответственно, Jllustra фирмы Informix, Ingres компании Computer Associates.

Среди объектных СУБД можно назвать Objectvity фирмы Objectvity, Ontos DB фирмы Ontos, ObjectStore фирмы ObjectDesing, Poet компании Poet Software GmbH, O2 фирмы О2 Technology, Itasca фирмы Ibex Computing, UniSQL фирмы UniSQL, Jasmine компании Computer Associates, ODB-Jupiter фирмы НПЦ «Интелтек Плюс» и другие.

3.2. СУБД Access

3.2.1. Вводные замечания

СУБД Access входит в состав пакета Microsoft Office и хорошо интегрирована с другими программными продуктами Microsoft.

Кроме того, она хорошо зарекомендовала себя при использовании в сети.

Несомненным достоинством Access является также то, что эта программа создавалась сразу для Windows и поэтому не содержит в себе ограничений, накладываемых MS DOS.

Access – интегрированная среда проектирования, работа в которой базируется на манипулировании объектами и их атрибутами.

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

  •  объектно – ориентированная модель разработки Access позволяет использовать стандартную методологию при работе со всеми объектами от таблиц, запросов и представлений на сервере базы данных до форм, текстовых полей и других объектов внешнего интерфейса;
  •  с применением прогрессивных стандартов OLE – автоматики (Object Linking and Embedding – связывание и внедрение объектов) и компонентной объектной модели (COM) можно быстро строить приложения из различных компонентов других приложений и интерфейсов;
  •   развитые механизмы свойств помогают легко связывать формы и отчеты (объекты внешнего интерфейса) с таблицами и запросами (объектами данных);
  •  стандартного вида приложения и компоненты приложений быстро создаются с использованием мастеров и шаблонов.

В Access используется объектно – ориентированное программирование (ООП). В основе ООП лежит идея «упакованной функциональности». Прикладная программа строится из объектов с некоторыми свойствами и некоторых операций, которые эти объекты могут выполнять.

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

Объекты приложения Access могут быть вполне «осязаемыми» для пользователя, как, например, окна диалога, командные кнопки, текстовые поля и т.д. Объекты могут также  представлять собой некоторые категории, которые непосредственно не являются частью пользовательского интерфейса и которые нельзя «потрогать» курсором мыши. Это, например, рабочие области, соединения и наборы записей и т.д.

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

В Access объект – это комбинация программного кода и данных, воспринимаемая как единица, которой можно каким – либо образом манипулировать. Например, объектом – текстовым полем можно манипулировать, введя в него данные, изменяя цветовое оформление, устанавливая шрифты и т.д.

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

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

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

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

Стандартные методы Access подразделяются на две категории:

  •  процедуры, реагирующие на стандартные события;
  •  методы, вызываемые явно в программном коде разработчика.

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

Разработчик может также создавать свои собственные пользовательские процедуры, которые ассоциируются с объектом и решают какие – либо задачи в период выполнения приложения.

Примечание. Свойства и методы называют также интерфейсом объекта.

Важнейшее понятие ООП – класс. Класс обычно описывается как шаблон или проект, из которого впоследствии будет создан объект. Каждый объект в этом случае является экземпляром класса.

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

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

  •  функции и процедуры в классе (модуле класса) Access соответствуют методам и свойствам объекта;
  •  пользовательский доступ к текущему состоянию объекта (т.е. к полям экземпляра) только через эти методы и свойства.

Программы также должны взаимодействовать с этими данными только через свойства и методы объектов. Хранение данных в виде локальных (Private) – переменных – единственный способ придания объекту свойств и поведения «черного ящика» (или, как иногда говорят, скрывания данных). Скрывание данных – критическое условие для многократного использования объекта и надежности приложения.

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

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

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

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

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

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

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

Для работы почти с каждым объектом в Access используется общая методология. Независимо от вида объекта для управления им разработчик определяет или изменяет одно из свойств или вызывает один из методов этого объекта.

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

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

Существует много способов управления в Microsoft Access: система меню, панели инструментов, контекстные меню, использование указателя мыши и комбинаций «горячих клавиш». Многие средства ввода команд просто дублируют друг друга. Наиболее удобными считаются  применение контекстного меню и мыши.

При работе в сети Access разрешает 255 одновременных обращений и может обрабатывать файлы размером до 2 Гбайт.

3.2.2. Создание базы данных

Сразу после запуска Access открывает окно. В нем пользователю предлагается выбрать один из следующих вариантов: создать новую базу данных, запустить мастер по созданию базы данных, открыть существующую базу данных. Если поставить переключатель в положение Новая база данных и нажать кнопку ОК, то откроется окно Файл новой базы данных, в котором нужно указать папку, куда будет записываться создаваемый файл, тип файла из списка и имя файла. Сохранить файл базы данных нужно прежде, чем приступить к созданию таблиц. Поскольку базы данных быстро увеличиваются в размерах, в качестве  носителя для базы следует использовать жесткий диск или сменный диск большого размера. После сохранения базы откроется окно База данных. Оно содержит семь вкладок: таблицы, формы, отчеты , запросы, макросы, модули.Visual Basic, страницы доступа к данным.

3.2.3. Создание и работа с таблицами

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

Если в БД создана хотя бы одна таблица, то в данном окне доступны кнопки Открыть и Конструктор.

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

Режим таблицы. В этом режиме на экране появляется заготовка таблицы. Поля таблицы вначале имеют имена Поле1, Поле2 и т.д. присвоенные по умолчанию, но их можно переименовать, присвоив им новые имена.

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

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

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

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

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

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

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

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

Существуют две команды сортировки: «по возрастанию» и «по убыванию». Они производят сортировку всего столбца, независимо от того, какой его фрагмент выделен.

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

Команда  Добавить объект доступна только в том случае, когда поле таблицы имеет тип «Поле объекта 0LЕ». Внедрение объектов в таблицу — это уже высший пилотаж в конструировании баз данных.

Если в таблице имеется поле типа «Гиперссылка», пункт Гиперссылка позволяет вставлять в базу данных ссылки на самые различные документы и файлы .

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

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

Его удобно использовать, например, для восстановления исходного порядка следования записей после операций сортировки. Для этого надо упорядочить значения поля-счетчика по возрастанию. Аналогичная сортировка по убыванию позволит перевернуть таблицу «вверх ногами», чтобы быстро посмотреть, что находится в ее конце.

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

Режим «Конструктор». Для запуска Конструктора после нажатия кнопки Создать в окне Новая таблица надо выбрать опцию Конструктор.

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

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

Первоначально бланк в верхней части окна пуст. Для создания таблицы необходимо, как минимум, заполнить графы «Имя поля» и «Тип поля». Графа «Описание» не является обязательной и предназначена для текста подсказки, который выводится в нижней строке экрана во время работы с таблицей.

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

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

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

Рассмотрение типа данных полей ограничим из-за ограниченности объема пособия  типами Гиперссылка , Мемо и Мастер подстановок.

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

Поле МЕМО. Поля этого типа подобны текстовым, но могут содержать до 55535 символов. Вкладка свойств поля МЕМО такая, как у поля логического типа за исключением последней строки, где вместо строки Индексированное поле используется строка Пустые строки.

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

Мастер подстановок. Последний элемент в списке типов полей — Мастер подстановок — не является названием типа, а представляет собой команду для запуска Мастера, позволяющего создать связь между таблицами. Для пояснения работы Мастера рассмотрим небольшой пример. Допустим, в базе данных имеются две таблицы, каждая из которых имеет по два поля типа «Счетчик» и «Числовое». Содержимое таблиц показано на рис. 3.1. Для облегчения понимания существа дела в одну из них — Таблица1 — введены числа, начинающиеся с цифры 1, а в другую — Таблица2 — числа, начинающиеся с цифры 2. Имена полей также снабжены соответствующими цифрами. Необходимо выполнить подстановку поля Числа1 из Таблица1 в Таблица2.

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

Код

Числа1

Текст 1

Код2

Числа2

10

115

Телевизоры

20

221

11

115

Ведра

21

222

12

117

Гвозди

22

223

13

118

Кабель

23

224

14

119

Обувь

24

225

15

120

Магнитолы

25

225

15

121

Мебель

(Счетчик)

Рис.37. Исходные данные для операции подстановки

В первую очередь необходимо создать новое поле в Таблице2, использовав конструктор, назвав его Подстановка. Как только из списка выбрана строка Мастер подстановки, этот М