close

Вход

Забыли?

вход по аккаунту

?

BurakovLatipova

код для вставкиСкачать
МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ
Федеральное государственное автономное образовательное учреждение
высшего профессионального образования
САНКТ-ПЕТЕРБУРГСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ
АЭРОКОСМИЧЕСКОГО ПРИБОРОСТРОЕНИЯ
М. В. Бураков, Р. Р. Латыпова
БАЗЫ ДАННЫХ И ЯЗЫК SQL
Учебное пособие
Санкт-Петербург
2014
УДК 004.6.(075)
ББК 32.81я73
Б91
Рецензенты:
доктор экономический наук, профессор В. А. Плотников;
кандидат технических наук, доцент А. А. Мартынов
Б91
Утверждено
редакционно-издательским советом университета
в качестве учебного пособия
Бураков, М. В.
Базы данных и язык SQL: учеб. пособие / М. В. Бураков, Р. Р. Латыпова. – СПб.: ГУАП, 2014. – 120 с.
ISBN 978-5-8088-0948-2
Рассматриваются принципы и механизмы обработки данных и
знаний в информационных системах. Приводятся основные понятия, математические основы и организация реляционных баз данных, описываются основные конструкции и принципы использования языка SQL. Рассматриваются основные принципы построения
систем обработки знаний и экспертных систем.
Издание предназначено для бакалавров, обучающихся по направлению «Управление в технических системах», а также студентов других специальностей, изучающих дисциплины «Информационное обеспечение систем управления» и «Системы с искусственным интеллектом».
УДК 004.6.(075)
ББК 32.81я73
ISBN 978-5-8088-0948-2
© Бураков М. В., Латыпова Р. Р., 2014
© Санкт-Петербургский государственный
университет аэрокосмического
приборостроения, 2014
ГЛАВА 1. БАЗЫ ДАННЫХ. ОБЩИЕ ПОНЯТИЯ
1.1. Задачи информационных систем
С самого начала развития вычислительной техники образовались два основных направления ее использования – выполнение
численных расчетов и разработка информационных систем.
В самом широком смысле информационная система представляет собой программный комплекс, функции которого состоят в поддержке надежного хранения информации в памяти компьютера,
выполнении специфических для данного приложения преобразований информации и/или вычислений, предоставлении пользователям удобного и легко осваиваемого интерфейса. Обычно объемы
информации, с которыми приходится иметь дело таким системам,
достаточно велики, а сама информация имеет достаточно сложную
структуру. Классическими примерами информационных систем
являются банковские системы, системы резервирования авиационных или железнодорожных билетов, мест в гостиницах и т. д.
Первые информационные системы появились в 1950-х годах.
Они были предназначены для обработки счетов и расчета зарплаты
и реализовывались на электромеханических бухгалтерских счетных машинах. Это приводило к некоторому сокращению затрат и
времени на подготовку бумажных документов.
В 1960-е годы происходит изменение отношения к информационным системам. Информация стала применяться для периодической отчетности по многим параметрам. Для этого организациям
требовалось компьютерное оборудование широкого назначения,
способное обслуживать разнообразные функции.
В 1970-х – начале 1980-х годов информационные системы начинают широко использоваться в качестве средства управленческого
контроля, поддерживающего и ускоряющего процесс принятия решений. Принятие решения – акт целенаправленного воздействия
на объект управления, основанный на анализе ситуации, определении цели, разработке программы достижения этой цели.
К концу 1980-х годов концепция использования информационных систем вновь изменяется: они становятся стратегическим
источником информации и используются на всех уровнях организации любого профиля. Информационные системы помогают организации достичь успеха в своей деятельности, создавать новые
товары и услуги, находить новые рынки сбыта, партнеров, организовывать выпуск продукции по низкой цене и т. д.
3
Структура управления любой организации традиционно делится на три уровня: операционный, функциональный и стратегический. Чем сложнее задача, тем более высокий уровень управления
требуется для ее решения (рис. 1.1).
Стратегическому уровню соответствует долгосрочное планирование, тактическому – среднесрочное, операционному – краткосрочное.
Простых задач, требующих немедленного (оперативного) решения, возникает значительно большее количество, чем задач тактического и, тем более, стратегического уровня.
На уровне операционного управления (нижнем уровне) большой
объем занимают учетные задачи, такие как:
– учет количества произведенной продукции;
– учет затрат времени, сырья и материалов при выполнении отдельных производственных операций;
– учет произведенной продукции;
– бухгалтерский учет и т. д.
На операционном уровне находятся исполнители и менеджеры
низшего звена (бригадиры, инженеры, ответственные исполнители, мастера, нормировщики, техники, лаборанты и т. п.).
Тактический (средний) уровень управления обеспечивает решение задач, требующих предварительного анализа информации,
подготовленной на операционном уровне. На тактическом уровне
большое значение приобретает такая функция управления, как
анализ. Объем решаемых задач уменьшается, но возрастает их
сложность. При этом не всегда удается выработать нужное решение
оперативно, требуется дополнительное время на анализ, осмысле-
Рост сложности
решаемых
задач
Стратегический
Динамика
принятия
решений
Тактический
Операционный
Рис. 1.1. Пирамида уровней управления
4
ние, сбор недостающих сведений и т. п. Управление связано с некоторой задержкой от момента поступления информации до принятия решений и их реализации, а также от момента реализации
решений до получения реакции на них.
На тактическом уровне работают менеджеры среднего звена и
специалисты (начальники служб, отделов, цехов, начальник смены, участка, научные сотрудники и т. п.).
Стратегический (верхний) уровень обеспечивает выработку
управленческих решений, направленных на достижение долгосрочных стратегических целей организации. Поскольку результаты принимаемых решений проявляются спустя длительное время,
особое значение на этом уровне имеет такая функция управления,
как стратегическое планирование. Прочие функции управления на
этом уровне в настоящее время разработаны недостаточно полно.
На стратегическом уровне управления находятся менеджеры
высшего звена руководства организации (руководители фирм и их
заместители).
При создании или классификации информационных систем возникают проблемы, связанные с формальным – математическим и
алгоритмическим – описанием решаемых задач. Чем точнее математическое описание задачи, тем выше возможности компьютерной обработки данных и тем меньше степень участия человека в
процессе ее решения.
Различают три типа задач, для решения которых создаются информационные системы (рис. 1.2):
1) формализуемые (структурированные);
2) неформализуемые (неструктурированные);
3) частично формализуемые.
Неформализуемые
задачи
Формализуемые
задачи
Частично
формализуемые
задачи
Рис. 1.2. Классификация задач в информационной системе
5
Формализуемая задача – это задача, где известны все ее элементы и взаимосвязи между ними. Содержание такой задачи можно
выразить в форме математической модели, имеющей точный алгоритм решения. Подобные задачи обычно приходится решать многократно, и они носят рутинный характер. Целью использования
информационной системы для решения структурированных задач
является полная автоматизация их решения, т. е. сведение роли человека к нулю.
Например, задача расчета заработной платы. Это структурированная задача, где полностью известен алгоритм решения. Рутинный характер этой задачи определяется тем, что расчеты всех начислений и
отчислений просты, но объем их велик, поскольку они должны многократно повторяться ежемесячно для всех категорий работающих.
Классические базы данных ориентированы на решение формализуемых задач.
Неформализуемая задача – та, в которой невозможно выделить
элементы и установить между ними связи. При решении неформализуемых задач возникают трудности из-за невозможности создания точного математического описания. Здесь большое значение
могут иметь эвристические соображения человека на основе опыта
и косвенной информации из разных источников.
На практике о большинстве задач можно сказать, что известна
лишь часть их элементов и связей между ними. Такие задачи называются частично формализуемыми. В этих условиях можно создать информационную систему. Получаемая в ней информация анализируется человеком, который будет играть определяющую роль.
Такие информационные системы являются автоматизированными,
так как в их функционировании принимает участие человек.
Для решения неформализуемых и частично формализуемых задач разрабатываются экспертные системы, или системы обработки
знаний.
1.2. История развития баз данных
В настоящее время базы данных (БД) – наиболее массовая область информационных технологий.
Всякая программа для ЭВМ является моделью некоторой предметной области. База данных – это также модель взаимосвязей
между объектами реального мира и описанием этих объектов.
В настоящее время наиболее распространены реляционные БД,
исторически им предшествовали иерархические и сетевые БД.
6
Первые БД, появившиеся в 60-е годы, были предназначены для
планирования выпуска продукции. Очень часто возникала потребность определить, сколько требуется комплектующих для выпуска
того или иного вида продукции. Таким задачам соответствует древовидная (иерархическая) структура.
Например, при выпуске автомобиля получается структура, показанная на рис. 1.3.
Каждому прямоугольнику на рис. 1.3 соответствует запись в БД.
Между записями существуют отношения «предок – потомок».
Для получения доступа к данным в иерархической БД можно указать номер записи, а также выполнить ряд действий:
1) найти дерево по признаку;
2) перейти «вниз» к первому потомку;
3) перейти «в сторону» к следующему потомку;
4) перейти «вверх» к предку;
5) вставлять и удалять записи.
Таким образом, по записям можно перемещаться, переходя
вниз, вверх или в сторону.
Иерархические БД имеют следующие достоинства:
1) структура БД проста для понимания;
2) отношения «предок – потомок» позволяют моделировать высказывания типа «А часть В» или «А владеет В»;
3) записи можно оптимально размещать на носителе информации, т. е. предки возле потомков, тем самым сокращается время доступа.
Автомобиль
...
Двигатель
Левая дверь
Ручка
...
Корпус
...
...
Правая дверь
...
Стекло
Колеса
...
Крыша
Замок
Рис. 1.3. Пример иерархической структуры
7
Самая известная из таких БД – это IMS (Information management
system) фирмы IBM (1968). Эта СУБД все еще активно эксплуатируется на больших ЭВМ.
Сетевые БД (здесь не имеются в виду сети ЭВМ) позволяют описать те случаи, когда одна запись может участвовать в нескольких
отношениях «предок – потомок», т. е. иметь несколько предков
(рис. 1.4). Такие отношения в сетевой модели называют множествами.
Официальный стандарт сетевых БД был предложен в 1971 г., он
получил название CODASYL.
Доступ к данным в сетевой модели напоминает доступ к данным
в иерархической модели. Программа может выполнять следующие
действия:
1) найти запись по ее номеру (признаку);
2) перейти к первому потомку в конкретном множестве;
3) перейти «в сторону» от потомка к потомку в конкретном множестве;
4) перейти «вверх» от потомка к предку в другом множестве;
5) вставлять и удалять записи.
Сетевые БД отличаются большей гибкостью, так как позволяют
описать более сложные структуры данных.
Но иерархические и сетевые БД имеют общий недостаток:
структура данных описывается жестко на этапе проектирования.
При перестройке структуры нужно перестраивать всю БД.
Кроме того, иерархические и сетевые БД требовали участия специалиста-программиста для реализации запросов. Это вызывало
задержки при эксплуатации БД.
Поэтому такие БД сейчас имеют меньшее распространение, чем
реляционные БД. В переводе «relation» означает «отношение». Математический аппарат, который используется в таких БД, позволяет описывать таблицы и операции над ними.
Сотрудник 1
Проект 1
Сотрудник 2
Проект 2
Сотрудник N
Проект K
Рис. 1.4. Пример сетевой организации данных
8
Теоретический фундамент реляционных БД заложил Эдгар
Кодд, разработавший в 70-е годы математический аппарат теории
отношений. Реляционная модель является теорией, но фактически
ни одна из современных БД не придерживается на все 100 % положений этой теории. То есть пользователь должен учитывать теоретические рекомендации, но имеет возможности для их нарушения.
При математическом описании понятию «таблицы» соответствует понятие «отношение», столбцу – атрибут и строке – кортеж.
При практической разработке строки называют записями, а
столбцы – полями. То есть запись – это набор полей, содержащих
связанную информацию. Поле – это элемент данных в БД. Поле
должно иметь имя и тип.
База данных – набор связанных таблиц, обычно идентифицируемых с помощью каталога, содержащего эти таблицы, или с помощью
псевдонима, дающего имя БД.
По отношению к пользователю реляционные БД поддерживают
два основных принципа:
1) данные для пользователя представляются в виде таблиц;
2) пользователь имеет в своем распоряжении операторы, позволяющие получить новые таблицы из старых.
При построении реляционных БД используется несколько простых правил:
1) все значения данных состоят из простых типов данных. Отсутствуют сложные типы, такие как массивы, указатели, векторы и т. д.;
2) все данные отображаются в виде двумерных таблиц (отношений). Каждая таблица содержит некоторое число строк (кортежей)
и один или несколько столбцов (атрибутов);
3) после ввода данных можно сравнивать значения в различных
столбцах и соотносить строки (в том числе и для разных таблиц);
4) все операции определяются только логикой, а не положением
строки в таблице;
5) поскольку определить строку по ее положению в таблице
нельзя, бывает необходимо иметь специальное поле в каждой строке – первичный ключ;
6) каждое значение в столбце должно быть атомарной величиной, т. е. содержать только одно значение.
Таким образом, таблица – это основа реляционной БД. Это логическая структура, физическое представление может быть каким
угодно.
Кроме того, реляционные БД используют еще ряд объектов.
К ним относятся:
9
1) формы: позволяют ограничить объем информации, отображаемой на экране, и представить ее в оптимальном виде. Формы используются для просмотра данных и ввода их в таблицы. С помощью мастера форм можно легко создать форму, поместив в нее поля
исходной таблицы в соответствии с одним из шаблонов. С помощью
конструктора можно создать форму любой степени сложности;
2) отчеты: используются для отображения информации из БД.
Они также могут строиться с использованием мастера или конструктора. В отчете можно сгруппировать поля исходной таблицы,
добавить вычисляемые поля, сделать нужное оформление;
3) формы и отчеты иногда называют конструкторскими объектами. Они могут включать элементы управления, такие как надписи,
прямоугольники, линии, рисунки, выключатели, флажки и т. п.;
4) запросы: это средства извлечения информации из БД. Данные
могут извлекаться из нескольких таблиц одновременно, т. е. связи
между таблицами могут устанавливаться в момент исполнения запроса. Это упрощает разработку БД;
5) макросы: предназначены для выполнения часто исполняемых операций. Каждый макрос содержит одну или несколько макрокоманд. Каждая макрокоманда выполняет определенное действие (открытие формы, печать отчета и т. п.).
6) CASE-средства: это программы для разработки структуры БД
в виде диаграмм и автоматической генерации БД на их основе.
Для построения запросов к реляционным БД был разработан
язык SQL (Structured Query Language) – язык структурированных
запросов. SQL получил характер промышленного стандарта. Его
поддерживают все современные БД. При переходе с одной БД на
другую разработчик имеет дело с одним и тем же языком SQL. Это
позволяет не вникать в детали низкоуровневого доступа к данным,
а учитывать только логическое описание БД. SQL является языком
более высокого уровня, чем обычные языки программирования.
Операторы SQL выполняются на уровне множеств. Этот язык
является декларативным, т. е. пользователь описывает, что ему
нужно получить, но описывает и алгоритм, при помощи которого
это можно сделать. Процедура получения решения строится без
участия пользователя.
1.3. Локальные и серверные базы данных
В задачах обработки информации, основанных на системах баз
данных, существуют два варианта расположения данных: локальный
10
и удаленный. Соответственно, существуют «локальные», или «персональные» БД, а также промышленные, или серверные, БД.
К локальным БД относятся Paradox, dBase, Access, FoxPro и др.
БД Access занимает особое положение, потому что входит в состав
распространенного пакета Microsoft Office. Локальные данные, как
правило, располагаются на жестком диске компьютера, на котором
работает пользователь, и находятся в монопольном ведении этого
пользователя. Пользователь при этом работает автономно, не завися
от других пользователей и никоим образом не влияя на их работу.
К серверным БД относятся Oracle, Sybase, SQL Server и др. Удаленные данные располагаются вне компьютера пользователя – на
файловом сервере сети или на специально выделенном для этих целей компьютере.
Всего можно выделить три архитектуры серверных БД:
1) архитектура «файл – сервер»;
2) архитектура «клиент – сервер»;
3) многозвенная архитектура.
При работе с локальными БД режим однопользовательский.
В стандартной файл-серверной архитектуре данные, располагаясь на файл-сервере, являются, по сути, пассивным источником.
На компьютере пользователя запускается копия приложения. При
этом, поскольку обработка данных осуществляется на компьютере
пользователя, по сети перегоняется вся необходимая для этой обработки информация, хотя интересующий пользователя объем данных может быть намного меньше пересылаемого. Например, если
пользователя интересуют все работники какого-либо предприятия,
участвующие в конкретном проекте, его приложение «получит»
сначала всех работников и все проекты из базы данных и только
после этого произведет требуемую выборку.
Кроме того, вся ответственность за получение, обработку, а также за поддержание целостности БД лежит на пользователе. Данные, с которыми работает пользователь, время от времени обновляются из реальной БД, расположенной на файл-сервере. При этом
изменения, которые вносит один пользователь, могут быть какоето время неизвестны другим пользователям. Поэтому возникает
проблема блокировки одновременного доступа к данным разных
пользователей.
Исторически на персональных компьютерах использовался
именно этот подход как более простой в освоении. Однако большой
объем перегоняемых по сети данных быстро «забивает» сеть уже
при небольшом числе пользователей, существенно ограничивая
11
возможности роста. Этот основной и самый существенный недостаток заставил искать способы уменьшения нагрузки на сеть.
В архитектуре «клиент – сервер» для обработки данных выделяется специальное ядро – так называемый сервер баз данных,
который принимает на себя функции обработки запросов пользователей, именуемых теперь клиентами. Сервер баз данных представляет собой программу, выполняющуюся, как правило, на мощном
компьютере. Приложения-клиенты посылают с рабочих станций
запросы на выборку (вставку, обновление, удаление) данных. При
этом сервер выполняет всю «грязную» работу по отбору данных,
отправляя клиенту только требуемую «выжимку». Если приведенный выше пример перестроить с учетом клиент-серверной архитектуры, то приложение-клиент «получит» от сервера в качестве
результата список только тех работников, которые участвуют в заданном проекте.
Такой подход обеспечивает решение трех важных задач:
1) уменьшение нагрузки на сеть;
2) уменьшение требований к компьютерам-клиентам;
3) повышение надежности и сохранение логической целостности базы данных.
Действительно, теперь сервер БД (в случае реляционных баз
данных называемый SQL-сервером) возвращает клиентскому приложению только «выжимку» того, что он просмотрел в базе, а она
(выжимка), в общем случае, действительно составляет малую часть
от общего объема. Поэтому в сети не наблюдается резкого увеличения нагрузки при возрастании числа клиентов. Клиентские же
приложения могут выполняться на менее мощных (по сравнению
с сервером) компьютерах благодаря тому, что им практически не требуется выполнять никакой дополнительной обработки полученных
от сервера результатов запроса (хотя, конечно, это не запрещено).
Побочным эффектом уменьшения нагрузки на сеть является повышение скорости выполнения приложений клиентов. Кроме того,
система легче масштабируется – легче и дешевле заменить один
сервер на более мощный, чем десятки рабочих станций.
Но наиболее важным результатом перехода в архитектуру «клиент – сервер» является гарантированное сохранение логической целостности базы данных, т. е. система становится более устойчивой
и более защищенной. Достигается это благодаря возможности переложить заботу о сохранении целостности базы на сервер. Для этого «хорошие» серверы обладают большим набором встроенных механизмов, защищающих систему от неверных действий клиентов.
12
Среди этих механизмов можно назвать такие, как ограничения целостности, декларативная ссылочная целостность, триггеры, виртуальные таблицы (представления), авторизация пользователей и др.
1.4. Основные понятия реляционных баз данных
При работе с таблицами часто используют два представления: собственно таблица и структура таблицы. Пример приведен на рис. 1.5.
Таблица может иметь первичный ключ, под которым понимается поле или набор полей, однозначно идентифицирующих запись.
В таблице не должно быть записей с одним и тем же значением
первичного ключа.
Например, если рассматривается таблица «Студенты», то в качестве первичного ключа нельзя использовать фамилию, имя или
дату рождения, поскольку эта информация не уникальна.
В общем случае в качестве первичного ключа выгоднее использовать семантически незначащее (не несущее смысловой нагрузки)
поле (счетчик), с помощью которого каждая запись получает уникальный номер.
Первичный ключ является разновидностью более общего понятия потенциального ключа, т. е. ключа, который может быть выбран в качестве первичного.
Между двумя и более таблицами БД могут существовать отношения подчиненности. Это означает, что для каждой записи главной таблицы (родительской, или мастер-таблицы (master)) может
существовать одна или несколько записей в подчиненной (или детальной (detail)) таблицы.
Таблица «Студенты»
Номер
Фамилия
Имя
Рост
Вес
Структура таблицы «Студенты»
Поле
Тип поля
Номер
Счетчик
Фамилия
Текстовый
Имя
Текстовый
Рост
Числовой
Вес
Числовой
Рис. 1.5. Пример описания таблицы и ее структуры
13
Связывание таблиц выполняется для устранения избыточности
информации.
Существуют три разновидности связей между таблицами:
1) связь «один ко многим» (или «многие к одному»);
2) связь «один к одному»;
3) связь «многие ко многим».
Связываемые поля не обязательно должны иметь одинаковые
имена, но они должны иметь одинаковые типы данных.
Отношение «один ко многим» является самым распространенным, оно моделирует иерархию данных.
Рассмотрим пример, когда одной записи в родительской таблице соответствует несколько записей в дочерней таблице (рис. 1.6).
В этом примере одной записи в родительской таблице «Товары» соответствует несколько записей в дочерней таблице «Отпуск товаров».
Отношение «один к одному» применяется тогда, когда стремятся
сократить объем информации в одной таблице или защитить часть
информации от доступа. Но здесь приходится выполнять больше
операций чтения при извлечении связанных данных. В этом случае
одной записи в главной таблице соответствует одна запись в подчиненной таблице (рис. 1.7).
Товары
Товар
Ед.
изм.
Сахар
кг
500
Мыло
кг
100
…
Отпуск товаров
Цена
…
1
Товар
Дата
Кол-во
Сахар
10.01.14
10
Сахар
11.01.14
8
Сахар
15.11.13
100
f
…
Рис. 1.6. Связь «один ко многим»
№
Фамилия
Имя
№
Адрес
№ паспорта
1
Иванов
Иван
1
…
…
2
Петров
Петр
2
…
…
…
…
…
…
…
…
1
1
Рис. 1.7. Связь «один к одному»
14
Такие связи также могут быть жесткими и нежесткими.
Рассмотрим отношение «многие ко многим». В этом случае возможны два варианта:
1) записи в родительской таблице соответствует более одной записи в дочерней таблице;
2) записи в дочерней таблице соответствует более одной записи в
родительской таблице.
Пример приведен на рис. 1.8.
Здесь имеется в виду, что один преподаватель читает разные
курсы, а один и тот же курс могут читать разные преподаватели.
Любая связь «многие ко многим» может быть заменена на одну
или более связей «один ко многим». Для этого нужно ввести промежуточную таблицу (рис. 1.9).
При связывании таблиц необходимо обеспечивать целостность
данных, которая может быть нарушена при изменении полей связи.
Преподаватель
№
Фамилия
1
Лекции
№
Курс
Латыпова
1
Программирование
2
Бураков
2
ТАУ
3
Салова
3
Метрология
Зайцев
4
Полякова
4
Электроника
f
f
Рис. 1.8. Связь «многие ко многим»
Читает_курс
Преподаватель
№
Фамилия
1
f
Лекции
Пре
Кур
№
Курс
п.1
с1
1
Программ.
f
1
1
Латыпова
2
Бураков
2
2
2
ТАУ
3
Салова
3
1
3
Метрол
Зайцев
огия
4
Полякова
4
2
4
Электроника
1
4
2
3
Рис. 1.9. Преобразование связи «многие ко многим»
15
Рассмотрим наиболее часто встречающуюся связь «один ко многим». Пример приведен на рис. 1.10.
Эти две таблицы связаны по общему полю «Товар». Таблица
«Товары» является главной, таблица «Отпуск товаров» – подчиненной. Потеря связей возможна в двух случаях:
1) изменяется значение в поле связей главной таблицы без изменения значений полей связи в соответствующих записях дочерней
таблицы. Например, если вместо товара «Сахар» в таблице «Товары» написать «Песок», то все записи в дочерней таблице для «Сахар» потеряют связь и не будут иметь единицы измерения и цены;
2) изменяется значение поля связи одной из записей дочерней
таблицы без изменения значения поля связи в соответствующих
записях главной таблицы. Например, если вместо товара «Сахар»
в одной из записей таблицы «Отпуск товаров» написать «Песок»,
то будут недостоверными записи об отпуске товара «Сахар». Запись
в дочерней таблице для «Песок» не будет иметь связи и, следовательно, единицы измерения и цены.
Таким образом, в обоих случаях возникает нарушение целостности БД.
Действия, нарушающие ссылочную целостность БД, должны
блокироваться.
Для сохранения ссылочной целостности может использоваться
также механизм каскадных изменений:
1) синхронные изменения поля связи в дочерней таблице при
внесении изменений в поле связи главной таблицы;
2) синхронные удаления поля связи в дочерней таблице при удалении поля связи главной таблицы.
Разрешение или запрещение каскадных изменений реализуются при описании связей между таблицами БД.
Обычно в СУБД для реализации ссылочной целостности в дочерней таблице создают внешний ключ, ссылающийся на родительскую таблицу, и указывают вид каскадных воздействий.
Товары
Отпуск товаров
Товар
Ед. изм.
Цена
Товар
Дата
Сахар
кг
500
Сахар
10.01.99
10
Мыло
кг
100
Сахар
11.01.00
8
…
…
…
Сахар
15.11.01
100
Рис. 1.10. Пример связывания таблиц
16
Кол-во
Внешний ключ создается в дочерней таблице. В него входят
поля связи дочерней таблицы. Для связей типа «один ко многим»
внешний ключ должен совпадать по составу полей с первичным
ключом главной таблицы или с частью первичного ключа (в этом
случае нормализация таблиц БД выполнена не полностью).
При определении первичного и внешнего ключей СУБД автоматически строит индексы. Индекс, соответствующий внешнему
ключу, строится для обеспечения связей родительской и дочерней
таблиц.
Индексы обеспечивают механизм быстрого доступа к данным
в таблицах. Индексы хранят значения индексных полей (по которым построен индекс) и указатель на запись в таблице.
Использование индексов позволяет использовать не просто последовательный, а индексно-последовательный доступ.
При последовательном доступе просматриваются все записи таблицы – от первой до последней, что неэффективно.
При индексно-последовательном доступе указатель в индексе
устанавливается на первую строку, соответствующую условиям
запроса (или его части), и считывается запись из таблицы по хранящемуся на нее в индексе указателю. Далее последовательно считываются остальные записи, удовлетворяющие условиям запроса.
Таким образом, во втором случае поиск ведется по индексу, а не
по самой таблице. Таблица может быть неупорядочена, а небольшой по объему индексный файл может быть легко отсортирован.
Например, рассмотрим табл. 1.1.
Структура индексов по каждому из четырех полей показана
в табл. 1.2.
Если несколько товаров имеют одно и то же наименование, то
достаточно найти в индексе, построенном по столбцу «Наименование», первую запись, а затем повторить чтение подряд для всех товаров с этим наименованием. То же самое касается даты и т. д.
Таблица 1.1
Отпуск товара
Номер
1
2
3
4
Дата
06.01.14
02.01.14
03.01.14
08.01.14
Товар
Спички
Мыло
Мука
Спички
Количество
2
100
5000
10
17
Таблица 1.2
Индексированные поля таблицы
По дате прихода
номер
дата
записи
02.01.14
03.01.14
06.01.14
08.01.14
2
3
1
4
По наименованию
номер
товар
записи
Мука
Мыло
Спички
Спички
3
2
1
4
По количеству
номер
количество
записи
5000
100
10
2
3
2
4
1
Индексы наиболее выгодны для статичных таблиц, по которым
часто выполняются запросы.
1.5. Нормализация таблиц БД
При создании БД необходимо выполнить анализ предметной области, для которой разрабатывается БД. Процесс разработки БД
является циклическим, т. е. на разных этапах происходят возвраты на более ранние этапы с целью коррекции. Субъективные взгляды разработчика всегда могут найти отражение в БД, но есть ряд
объективных требований, соблюдение которых всегда может принести пользу. К таким требованиям относится нормализация БД.
Процесс нормализации позволяет устранить избыточность данных
и ускорить доступ к ним.
В основе нормализации лежит одна основная идея: поля таблицы должны зависеть только от ключа таблицы, и ни от чего другого.
Если это не так, то следует разбить таблицу на отдельные таблицы.
Общие требования нормализации формулируются в виде пяти
нормальных форм (НФ), к которым последовательно приводятся
таблицы БД. На практике наиболее часто применяются только первые три НФ.
Рассмотрим 1-ю нормальную форму (1НФ).
Таблица в 1НФ должна удовлетворять следующим требованиям:
1) в таблице не должно быть повторяющихся записей;
2) каждое поле таблицы должно быть неделимым (атомарным),
т. е. на пересечении строки и столбца должен быть атомарный объект;
3) в таблице должны отсутствовать повторяющиеся группы полей.
Рассмотрим пример нормализации таблицы «Продажи», в которой содержится 19 полей (табл. 1.3).
В табл. 1.3 каждое поле неделимое, и никакое поле не является
уникальным.
18
Таблица 1.3
Продажи
Номер
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Поле
Фамилия
Имя
Отчество
Телефон
Факс
Индекс
Страна
Город
Адрес
Название предприятия
Руководитель предприятия
Web-сайт предприятия
E-mail предприятия
Код товара
Дата заказа
Заказано
Дата продажи
Продано
Цена
Категория товара
Наименование товара
Тип поля
Текст
Текст
Текст
Текст
Текст
Текст
Текст
Текст
Текст
Текст
Текст
Текст
Текст
Числовой
Дата/время
Числовой
Дата/время
Числовой
Денежный
Числовой
Текстовый
Таблица с такой структурой может иметь повторяющиеся группы полей, в которых будут записаны данные об одном и том же покупателе (поля с 1 по 11). Чтобы привести таблицу к 1НФ, она разбивается на две таблицы «Клиенты» и «Заказы», находящиеся в
отношении «один ко многим» (рис. 1.11).
Поскольку ни одно из полей исходной таблицы не было уникальным, здесь в качестве первичного ключа таблицы «Клиенты»
лучше ввести новое поле – «Код клиента». Это поле будет внешним
ключом в таблице «Заказы» (рис. 1.11).
В таблице «Заказы» ни одно поле не является уникальным, поэтому в качестве первичного ключа можно добавить поле «Код заказа» или использовать комбинацию трех полей – «Код клиента»,
«Код заказа» и «Дата заказа» в качестве составного ключа (если
один клиент делает один заказ в день).
Рассмотрим второй вариант – таблица с составным первичным
ключом. Такая таблица должна удовлетворять требованиям 2-й
нормальной формы (2НФ).
19
«Клиенты»
1
1
Код клиента
2
Фамилия
3
Имя
4
Отчество
5
Телефон
6
Факс
7
Индекс
8
Страна
9
Город
10
Адрес
11
Название фирмы
12
Руководитель фирмы
13
Web-сайт фирмы
14
E-mail фирмы
«Заказы»
f
1
Код клиента
2
Код товара
3
Дата заказа
4
Заказано
5
Дата продажи
6
Продано
7
Цена
8
Категория
9
Наименование
товара
Рис. 1.11. Разбиение со связью «один ко многим»
Таблица находится во 2НФ, если удовлетворены два условия:
1) таблица удовлетворяет условиям 1НФ;
2) любое неключевое поле однозначно идентифицируется полным набором ключевых полей.
Очевидно, что в таблице «Заказы» второе условие не выполняется, поскольку поля «Категория» и «Наименование товара» зависят только от поля «Код товара». Чтобы привести таблицу ко 2НФ,
нужно выделить эти поля в отдельную таблицу с наименованием
«Товар» (рис. 1.12).
Рассмотрим далее таблицу «Клиенты». Здесь нет составного
ключа, поэтому требования 2НФ выполнены автоматически и требуется рассматривать третью нормальную форму (3НФ).
Таблица находится в 3НФ, если она удовлетворяет условиям
2НФ и ни одно из неключевых полей таблицы не идентифицируется
с помощью другого неключевого поля.
20
«Заказы»
«Товар»
1
Код клиента
2
Код товара
1
Код товара
3
Дата заказа
2
Категория
4
Заказано
3
Наименование товара
5
Дата продажи
6
Продано
7
Цена
1
1
Рис. 1.12. Разбиение со связью «один к одному»
Иначе говоря, все неключевые поля должны быть независимы.
Если какие-то поля зависят не от ключа, а от другого неключевого
поля, то такие поля должны быть выделены в отдельную таблицу.
В таблице «Клиенты» неключевые поля «Руководитель фирмы», «Web-сайт фирмы» и «E-mail фирмы» определяются неключевым полем «Название фирмы», поэтому необходимо создать новую таблицу с названием «Фирма» (рис. 1.13).
Таким образом, в рассмотренном примере из одной исходной таблицы в соответствии с требованиями нормализации было получено
четыре таблицы.
В целом нормализация дает не только преимущества, но и имеет
некоторые недостатки.
Главное достоинство состоит в том, что после нормализации в таблицах нет избыточных данных, поэтому экономится память ЭВМ
(хотя появляются поля связи, присутствующие одновременно у
главной и подчиненной таблиц).
«Клиенты»
1
Код клиента
...
...
11
Название фирмы
1
«Фирма»
1
1
Название фирмы
2
Руководитель фирмы
3
Веб-сайт фирмы
4
E-mail фирмы
Рис. 1.13. Устранение зависимости неключевых полей
21
В качестве недостатков могут быть названы следующие:
1) чем шире предметная область, тем больше получается набор
таблиц после нормализации. Для крупного предприятия БД может
содержать сотни взаимосвязанных таблиц, что превышает возможности человеческого восприятия. Поэтому функционирование БД
может стать трудно объяснимым;
2) при считывании связанных данных необходимо объединять
записи в связанных таблицах, что приводит к необходимости выполнения поисковых операций. В сложных БД это может вызывать
временные издержки.
Таким образом, нормализация является желательной, но в сложных БД могут появляться другие критерии, мешающие полной нормализации таблиц БД.
Вопросы для самопроверки
1. Каковы два основных направления использования вычислительной
техники?
2. Что такое информационная система?
3. Когда появились первые информационные системы?
4. Когда информационные системы стали использоваться для поддержки принятия решений?
5. Какие задачи могут решать современные информационные системы?
6. Сколько уровней можно выделить в структуре управления организацией?
7. Какие задачи решаются на операционном уровне управления?
8. Какие задачи решаются на тактическом уровне управления?
9. Какие задачи решаются на стратегическом уровне управления?
10. Что такое формализуемые и неформализуемые задачи?
11. Какие задачи решают базы данных?
12. Какие задачи решают экспертные системы?
13. Что такое иерархические базы данных?
14. Что такое сетевые базы данных?
15. Что такое реляционные базы данных?
16. Каковы основные достоинства реляционных БД?
17. Как называются элементы таблицы реляционной БД?
18. Какие принципы поддерживают реляционные БД по отношению к
пользователю?
19. Какие принципы используются при построении реляционных БД?
20. Что такое формы в реляционной БД?
21. Что такое отчеты в реляционной БД?
22. Что такое запросы в реляционной БД?
23. Что такое макросы в реляционной БД?
24. Какую роль играют CASE-средства при разработке реляционных БД?
22
25. С какой целью был разработан и используется язык SQL?
26. Чем отличаются локальные БД от серверных?
27. Какие существуют архитектуры серверных БД?
28. Чем отличается архитектура «файл – сервер» от архитектуры «клиент – сервер»?
29. Что такое первичный ключ?
30. Что такое потенциальный ключ?
31. Какие типы связей возможны между таблицами реляционной БД?
32. Какие типы связей наиболее распространены в реляционной БД?
33. В каких случаях возможно нарушение ссылочной целостности?
34. Какие механизмы могут быть использованы для обеспечения ссылочной целостности?
35. Что такое внешний ключ?
36. Зачем используется индексирование полей таблицы?
37. Зачем выполняется нормализация таблиц БД?
38. Каким требованиям должна соответствовать таблица в 1НФ?
39. Что такое составной первичный ключ?
40. Каким требованиям должна соответствовать таблица в 2НФ?
41. В каком случае требования 2НФ выполняются автоматически?
23
ГЛАВА 2. МЕТОДОЛОГИЯ ПРОЕКТИРОВАНИЯ
2.1. Концептуальное проектирование баз данных
Существуют два подхода к проектированию реляционных БД.
При первом подходе сразу создается реляционная схема БД, состоящая из определений реляционных таблиц. Затем эти таблицы
нормализуются. Такой подход применяется для сравнительно простых задач.
Второй подход основан на концептуальной (семантической) модели данных, создаваемой на этапе концептуального проектирования. Затем эта модель механически преобразуется в реляционную
модель, и нормализация при этом гарантируется. Такой подход
применяется при проектировании достаточно сложных БД. Рассмотрим подробнее основные приемы концептуального проектирования.
Всякая БД – это модель предметной области (ПО), отражающая
черты, существенные для решаемой задачи. При этом происходит
процесс отображения объектов реального мира в элементы модели.
При построении концептуальных моделей в настоящее время наиболее часто применяется объектно-ориентированный подход, т. е.
главными в концептуальной модели являются объекты и отношения
между ними. Часто также используется термин «ER-диаграммы»
(от «Essence – Relation», т. е. модели «сущность – связь»).
Объект – это обозначение множества вещей одного типа: автомобилей, книг, домов и т. д.
Каждый объект принимает значения на определенной области
определения (объектном множестве). Например, объект «дерево»
определяется с помощью множества «сосна», «береза», «тополь» и т. п.
Иногда выделяют лексические объектные множества и абстрактные объектные множества.
Под лексическим объектным множеством понимается такое
объектное множество, которое можно напечатать. Если же объектное множество напечатать нельзя, то его называют абстрактным.
Например, объект «ИМЯ» определяется с помощью лексического объектного множества, а для объекта «ЧЕЛОВЕК» объектное множество будет абстрактным, поскольку напечатать человека
нельзя. У человека есть идентификаторы: имя, номер паспорта и
т. д., но это не сам человек.
Когда концептуальная модель получает внутреннее компьютерное представление, то элементы лексических объектов становятся
24
строками символов, а элементы абстрактных объектов представляются внутренними номерами – идентификаторами. Их называют
еще суррогатным ключом. Такой ключ не имеет смысла вне системы.
Один объект может быть частью другого объекта. В этом случае
одно объектное множество оказывается надмножеством, а другое –
подмножеством (конкретизацией).
Связь между элементами двух объектных множеств называется
отношением (рис. 2.1).
Прямоугольник означает объектное множество, его составляют
отдельные элементы.
Отношение может иметь имя. Отношение также является объектным множеством.
Например, пусть есть два объектных множества:
Студенты = {Иванов, Петров, Сидоров}.
Оценки = {3, 4, 5}.
С помощью этих множеств можно сформулировать отношение:
Экзаменационная оценка = ({Иванов, 3}, {Петров, 4}, {Сидоров, 5}).
Если отношение рассматривается как объектное множество, то
его называют составным объектным множеством.
Рассмотрим составное множество «семейная пара». Его можно
определить как отношение с именем «состоит в браке» между объектными множествами «мужчина» и «женщина» (рис. 2.2).
ЧЕЛОВЕК
(надмножество)
Отношение
Мужчина
(конкретизация)
Рис. 2.1. Пример отношения
Мужчина
Состоит
в браке
Женщина
Рис. 2.2. Составное объектное множество
25
Для отношения вводится понятие мощности. Мощность отношения – максимальное количество элементов одного объектного множества, связанных с одним элементом другого объектного множества. Для приведенного выше примера мощность отношения равна
0 либо 1 в обоих направлениях. Рассмотрим отношение «Экзаменационная оценка» (рис. 2.3).
Здесь с одним студентом связывается только одна оценка, но
одну и ту же оценку могут получить многие студенты.
Часто рассматривается максимальная мощность отношения, но
в общем случае отношение может не иметь конкретной максимальной мощности.
Мощность отношения включения всегда одна и та же. Каждый
элемент надмножества связан с одним или нулем элементов подмножества. Каждый элемент подмножества связан ровно с одним
элементом надмножества. Например, всякий мужчина непременно
является человеком, но человек может быть не только мужчиной,
но и женщиной (рис. 2.4).
Максимальная мощность, равная 1 в одном из направлений,
устанавливает соотношение «один к одному» или «много к одному»
между множествами.
Если в каком-то направлении мощность равна 1, то в этом направлении отношение называется функциональным.
1
f
Студент
Оценка
Рис. 2.3. Пример отношения с мощностью «один ко многим»
ЧЕЛОВЕК
1
0 ^1
МУЖЧИНА
Рис. 2.4. Пример отношения включения
26
1
Преподаватель
f
Дипломник
Студент
Рис. 2.5. Пример функционального отношения
Пример приведен на рис. 2.5.
В направлении от студента к преподавателю это отношение
является функциональным, т. е. у студента один руководитель.
В другом направлении это отношение не функционально, так как
один преподаватель руководит несколькими студентами.
Если максимальная мощность отношения равна 1 в обоих направлениях, то это отношение «один к одному».
Если максимальная мощность в одном направлении равна 1, а
в другом – многим, то это отношение «один ко многим».
Если максимальная мощность в обоих направлениях равна многим, то это отношение «многие ко многим».
С каждым объектом обычно связывается несколько атрибутов,
т. е. характеристик объекта.
Атрибут объекта – функциональное отношение одного объектного множества к другому объектному множеству. Иначе говоря,
значение атрибута однозначно определяется для каждого объекта
или не определено, т. е. максимальная мощность отношения со стороны атрибута равна 1 или 0.
Пример: у каждого человека есть только одна дата рождения и
только один номер паспорта.
Если значение некоторого атрибута объекта не меняется, то его
можно использовать в качестве ключа.
Ключ – значение, которое всегда можно использовать для однозначного определения элемента объектного множества.
Например, номер паспорта может использоваться в качестве
ключа, а дата рождения – нет.
Важным является понятие «наследования». Если объект является конкретизацией другого объекта, то он наследует все атрибуты и отношения обобщенного объекта.
Например, рассмотрим объект «ЧЕЛОВЕК» с атрибутами «НОМЕР ПАСПОРТА», «ДАТА РОЖДЕНИЯ» и отношением «ЖИВЕТ В».
Если ввести объект «СТУДЕНТ» как конкретизацию объекта
«ЧЕЛОВЕК», то «СТУДЕНТ» наследует атрибуты и отношения
объекта «ЧЕЛОВЕК» (рис. 2.6).
27
Номер
паспорта
Дата
рождения
1
f
ЧЕЛОВЕК
ЖИВЕТ В
МОСКВА
СТУДЕНТ
Рис. 2.6. Пример наследования
Если в каком-то отношении участвуют два объектных множества, то оно называется бинарным. Отношения между N объектными множествами называются N-арными.
С помощью отношений высокого порядка описываются составные объекты. Составной объект – отношение, рассматриваемое как
объектное множество.
Отношения могут обладать атрибутами и участвовать в других
отношениях.
Например, пусть нужно рассматривать объем продаж отдельных товаров некоторой фирмы в отдельных странах. Получается
конструкция, показанная на рис. 2.7.
Два объектных множества образуют показанный пунктиром составной объект, с которым связан атрибут «Количество», зависящий и от товара, и от страны.
Если нужно учитывать количество товара, проданного в конкретной стране за конкретный день, то можно использовать трехстороннее отношение (рис. 2.8).
Рассмотрим концептуальную модель для задачи «Фруктовый
сад» (рис. 2.9).
Товар
Продан в
Страна
Количество
Рис. 2.7. Пример составного объекта с атрибутом
28
Дата
Товар
Страна
Продан в
Количество
Рис. 2.8. Пример трехстороннего отношения с атрибутом
Владелец
1
1
САД
Год гибели
Год посадки
1
f
1 v0
ДЕРЕВО
f
f
f
1
ВИД
1
СОРТ
f
Рис. 2.9. Пример концептуальной модели
Объект «САД» описывает отдельные экземпляры садов. У него
есть атрибут «ВЛАДЕЛЕЦ», которому он принадлежит.
Каждый сад связан с объектным множеством «ДЕРЕВО». Каждое дерево растет в одном саду, но в саду множество деревьев.
Каждое дерево имеет атрибут «ГОД ПОСАДКИ», но может еще
не иметь атрибута «ГОД ГИБЕЛИ».
Каждое дерево всегда может быть только одного вида (яблоня,
груша), но в саду много деревьев одного и того же вида.
Каждый сорт относится только к одному виду (джонатан – сорт
яблок), но один вид имеет множество сортов.
Если считать, что дерево может нести несколько сортов, то между сортами и деревьями в оба направления отношение «один ко
многим».
При описании той или иной предметной области может использоваться понятие концептуальных объектов, которые отличаются
от физических объектов тем, что означают тип предмета, а не сам
реальный предмет.
29
Концептуальная
книга
1
f
Имеет издание
Издание
Рис. 2.10. Пример концептуального объекта
Концептуальная
книга
1
f
Издание
Имеет издание
1
Содержится в
f
Физическая книга
1
1
Инвентарный №
Рис. 2.11. Взаимосвязь концептуального и физического объекта
Например, есть концептуальный объект – книга Н. В. Гоголя
«Мертвые души». Этому объекту может соответствовать физическое объектное множество, состоящее из книг с одним и тем же
автором и названием, изданных в разные годы разными издательствами (рис. 2.10).
Множество справа оказывается зависимым объектным множеством, поскольку каждый его элемент должен быть связан с каким-то
элементом левого объектного множества.
Издание здесь не является атрибутом концептуальной книги,
но может входить в более широкое описание объекта «физическая
книга» (рис. 2.11).
Инвентарный номер является атрибутом физической книги. Эту
модель можно расширять и дальше, если учитывать язык издания,
переводчика, стоимость и т. д.
Сложные БД проектируются по частям, затем эти части соединяются вместе за счет образования новых отношений.
2.2. Логическое проектирование
Логическое проектирование БД – это процесс конструирования
общей информационной модели на основе отдельных моделей пользователей, которая является независимой от особенностей реально
используемой СУБД и других физических условий.
30
На данном этапе выполняются следующие действия:
1) удаление связей типа «многие ко многим»;
2) удаление сложных связей;
3) удаление рекурсивных связей;
4) удаление связей с атрибутами;
5) удаление множественных атрибутов;
6) перепроверка связей типа «1:1»;
7) удаление избыточных связей.
Рассмотрим эти действия подробнее.
1. Удаление связей типа «многие ко многим».
Если в концептуальной модели присутствуют связи «многие ко
многим» (M:N), то их следует устранить путем определения некоторой промежуточной сущности. Связь типа M:N заменяется двумя связями типа «один ко многим» (1:M), устанавливаемыми со
вновь созданной сущностью. Пример показан на рис. 1.8; 1.9.
2. Удаление сложных связей. Сложной называется связь, существующая между тремя и более типами сущностей. Если в концептуальной модели присутствует сложная связь, ее следует устранить
с помощью промежуточной сущности. Сложная связь заменяется
необходимым количеством бинарных связей типа 1:M, устанавливаемых со вновь созданной сущностью. Например, тройная связь
«Сдается внаем» (изображается ромбом) отражает отношения, существующие между оформляющим аренду работником компании,
земельным участком и арендатором (рис. 2.12).
Эту сложную связь можно упростить путем введения новой сущности и определения бинарных связей между нею и каждой из исходных сущностей сложной связи.
В нашем примере связь «Сдается внаем» можно устранить посредством введения новой слабой сущности с именем «Соглашение». Вновь созданная сущность будет связана с исходными сущностями тремя новыми бинарными связями (рис. 2.13).
Арендатор
M:N
Сдается
внаем
M:N
Земельный
участок
M:N
Работник
компании
Рис. 2.12. Сложная связь
31
1:M
Арендатор
M:1
Соглашение
Земельный
участок
M:1
Работник
компании
Рис. 2.13. Упрощение сложной связи
3. Удаление рекурсивных связей. Рекурсивными называются
такие связи, в которых сущность некоторого типа взаимодействует
сама с собой. Если концептуальная модель содержит рекурсивные
связи, они должны быть устранены посредством определения некоторой промежуточной сущности. Например, для отображения
ситуации, когда один из работников руководит группой других работников, может быть установлена рекурсивная связь типа «один
ко многим» (1:М).
4. Удаление связей с атрибутами. Если в концептуальной модели присутствуют связи, имеющие собственные атрибуты, они должны быть преобразованы путем создания новой сущности. Например, рассмотрим ситуацию, когда требуется фиксировать количество рабочих часов, отработанных временным персоналом каждого
из отделений предприятия. Связь «Работает в» имеет атрибут с именем «Отработано часов». Преобразуем связь «Работает в» в сущность
с именем «Распределение по отделам», которой назначим атрибут
«Отработано часов», после чего создадим две новых связи типа 1:М.
5. Удаление множественных атрибутов. Множественными называют атрибуты, которые могут иметь одновременно несколько
значений для одного и того же экземпляра сущности. Если в концептуальной модели присутствует множественный атрибут, его
следует преобразовать путем определения новой сущности. Например, для отображения ситуации, когда одно и то же отделение компании имеет несколько телефонных номеров, в концептуальной
модели был определен множественный атрибут «Телефонный номер», относящийся к сущности «Отделение компании». Этот множественный атрибут следует удалить, определив новую сущность
«Телефон», имеющую единственный простой атрибут «Телефонный номер», и создав новую связь типа 1.
6. Перепроверка связей типа 1:1. В процессе определения сущностей могли быть созданы две различные сущности, которые, на
32
самом деле, представляют один и тот же объект в предметной области приложения. Например, могли быть созданы две сущности
«Отдел» и «Департамент», которые, на самом деле, представляют
один и тот же тип объекта. Другими словами, имя «Отдел» является синонимом имени «Департамент». В подобном случае следует
объединить эти две сущности в одну. Если первичные ключи объединяемых сущностей различны, нужно выбрать один из них в качестве первичного, а другой указать как альтернативный ключ.
7. Удаление избыточных связей. Связь является избыточной,
если одна и та же информация может быть получена не только через нее, но и с помощью другой связи. Всегда следует стремиться
создавать минимальные модели данных, и поэтому, если избыточная связь не является очевидно необходимой, ее следует удалять.
Установить, что между двумя сущностями имеется больше одной
связи, довольно просто. Однако из этого еще не следует, что одна из
двух связей обязательно является избыточной, поскольку обе они
могут представлять различные объединения, реально существующие в организации.
При устранении избыточности доступа большое значение имеют временные показатели. Например, рассмотрим ситуацию, когда необходимо смоделировать связи между сущностями «Мужчина», «Женщина» и «Ребенок». Очевидно, что между сущностями
«Мужчина» и «Ребенок» имеется два пути доступа: один – через
непосредственную связь «Является отцом» – и другой – через связи «Женат на» и «Является матерью». На первый взгляд, кажется,
что связь «Является отцом» избыточна. Однако это утверждение
может оказаться ошибочным по двум причинам. Во-первых, отец
может иметь детей от предыдущего брака, а мы моделируем только
текущий брак отца (через связь 1:1). Во-вторых, отец и мать могут
быть вообще не женаты, или отец может быть женат на женщине,
которая не является матерью данного ребенка (или же мать может
быть замужем за мужчиной, который не является отцом ребенка).
1
Мужчина
Женат
1
Женщина
1
1
Является
отцом
М
Ребенок
М
Является
матерью
Рис. 2.14. Пример неизбыточных связей
33
Поэтому все существующие взаимоотношения не могут быть смоделированы без использования связи типа «Является отцом»
(рис. 2.14).
2.3. Физическое проектирование
Фаза, последующая за логическим проектированием, называется фазой физического проектирования. При логическом проектировании не принимаются во внимание специфические функциональные возможности целевой БД и прикладных программ, однако учитываются особенности выбранной модели хранения данных.
Результатом логического проектирования являются глобальная
логическая модель данных и комплект описывающей ее сопроводительной документации. В совокупности эти результаты – исходная
информация для фазы физического проектирования БД и предоставляют ее разработчику все необходимое для принятия решений,
направленных на достижение максимальной эффективности создаваемого проекта. Образно говоря, при логическом проектировании
разработчик сосредотачивается на том, что надо сделать, тогда как
при физическом проектировании он ищет способ, как это сделать.
В каждом случае требуется наличие различных навыков. Так,
специалист по физическому проектированию баз данных должен
ясно представлять, как та или иная СУБД функционирует в компьютерной системе, а также хорошо знать все функциональные
возможности целевой СУБД. Поскольку функциональные возможности различных СУБД достаточно сильно отличаются друг от
друга, физическое проектирование всегда тесно связано с особенностями конкретной выбранной системы. Однако этап физического проектирования базы данных не является совершенно изолированным от других – как правило, между логическим и физическим
проектированием имеется постоянная обратная связь, часто охватывающая и разработку пользовательских приложений. Например, решения, принятые на этапе физического проектирования с
целью повышения производительности системы, могут влиять на
структуру ее логической схемы. Методология физического проектирования БД включает четыре основных этапа:
1) разработка таблиц БД и установка необходимых ограничений
целостности данных;
2) выбор схемы хранения данных и определение методов доступа
к таблицам БД. Как правило, каждая СУБД предоставляет несколько альтернативных вариантов схемы хранения данных. Исключе34
нием являются лишь настольные СУБД для платформы IBM PC, в
которых чаще всего используется фиксированная схема хранения
информации. С точки зрения пользователя, организация внутренней структуры хранения помещенных в таблицы данных должна
быть совершенно прозрачна – пользователь должен иметь возможность получать доступ к любой таблице и отдельным ее строкам без
необходимости указания способа хранения этих данных. Это означает, что СУБД должна обеспечивать полную независимость физического хранения данных от их логической организации. Только в
этом случае внесение изменений в физическую организацию БД не
окажет никакого влияния на работу пользователей. Отображение
логической модели данных на структуру их физической организации определяется внутренней схемой БД;
3) проектирование системы защиты БД от несанкционированного доступа. Сюда относится принятие решений о методах реализации каждой локальной логической модели данных, а также о мерах контроля доступа к отдельным таблицам базы;
4) организация процессов мониторинга созданной системы, задача которого состоит в выявлении и устранении любых проблем,
связанных с производительностью приложений и вытекающих из
особенностей реализации проекта. Здесь же осуществляется реализация новых и изменяющихся требований.
Вопросы для самопроверки
1. Какие два подхода можно использовать при проектировании реляционных БД?
2. В чем заключается идея концептуального проектирования БД?
3. Как определяется понятие «объект» в концептуальной модели?
4. Что такое лексическое объектное множество?
5. Что такое абстрактное объектное множество?
6. Как выполняется компьютерное представление лексического объекта?
7. Как выполняется компьютерное представление абстрактного объекта?
8. Как называется объект, являющийся частью другого объекта?
9. Может ли отношение между объектами быть объектным множеством?
10. Что такое мощность отношения?
11. Чему равна мощность отношения включения?
12. Какое отношение является функциональным?
13. Что такое атрибут объекта?
14. В чем заключается принцип наследования между объектами?
15. Какой объект называется составным?
16. Могут ли отношения обладать атрибутами?
17. Что такое концептуальный объект?
35
18. В чем заключается процесс логического проектирования БД?
19. Как выполняется удаление связей «многие ко многим»?
20. Как выполняется удаление сложных связей?
21. Как выполняется удаление рекурсивных связей?
22. Как происходит удаление связей с атрибутами?
23. Как выполняется удаление множественных атрибутов?
24. Как происходит перепроверка связей «один к одному»?
25. Как происходит удаление избыточных связей?
26. В чем заключается процесс физического проектирования БД?
27. Какие этапы включает методология физического проектирования БД?
36
ГЛАВА 3. РЕЛЯЦИОННАЯ АЛГЕБРА
Рассмотрим математическое описание понятий и операций, которые были выше введены неформально. Основное назначения реляционной алгебры заключается в записи выражений, которые, в
свою очередь, позволяют описать действия пользователя, работающего с БД.
В табл. 3.1 приведено соответствие математических терминов и
определений, используемых при проектировании БД.
Реляционная алгебра, предложенная Э. Коддом, состоит из 8 реляционных операторов, разбитых на две группы:
1) обычные операции над множествами: объединение, пересечение, вычитание и декартово произведение;
2) специальные операции: выборка, проекция, соединение и деление.
Рассмотрим первую группу операций, они соответствуют обычным теоретико-множественным операциям.
Пересечение – операция, которая возвращает кортежи, принадлежащие одновременно двум отношениям. Пример приведен на
рис. 3.1, где каждое отношение обозначено прямоугольником.
Таблица 3.1
Термины, используемые при проектировании БД
Формальный термин
Отношение
Кортеж
Кардинальное число
Атрибут
Степень
Первичный ключ
Домен
A
Неформальный термин
Таблица
Строка или запись
Количество строк
Столбец или поле
Количество столбцов
Уникальный идентификатор
Совокупность допустимых значений
Aˆ B
B
Рис. 3.1. Операция пересечения
37
A‰ B
Рис. 3.2. Операция объединения
Объединение – операция, которая возвращает кортежи, принадлежащие хотя бы одному из двух определенных отношений (рис. 3.2).
Вычитание – операция, которая возвращает кортежи, которые
принадлежат первому из двух определенных отношений и не принадлежат второму (рис. 3.3).
Декартово произведение – эта операция возвращает отношение,
содержащее всевозможные кортежи, которые являются сочетанием двух кортежей, принадлежащих, соответственно, двум определенным отношениям (рис. 3.4).
A–B
A
B
Рис. 3.3. Операция вычитания
А
В
А, Х
u
Х
=
А, У
У
С
В, Х
В, У
С, Х
С, У
Рис. 3.4. Операция декартова произведения
38
Однако в реляционной алгебре эти операции выполняются при
условии определенных ограничений.
При выполнении операций объединения, пересечения и вычитания отношения должны быть совместимыми по типу.
Два отношения совместимы по типу, если они имеют идентичные заголовки, а именно:
1) если каждое отношение имеет одно и то же множество атрибутов;
2) если соответствующие атрибуты объединены на одном и том
же домене.
Пример: пусть даны два отношения А и В, приведенные на рис. 3.5.
Первые три реляционные операции для этих отношений показаны на рис. 3.6 (где union и intersect – обозначения объединения
и пересечения).
Заметим, что дублирующие кортежи исключаются, так как
множество не может иметь повторяющихся элементов.
Для выполнения операции декартова произведения отношения
не должны иметь общих имен атрибутов.
Итоговое отношение имеет заголовок, являющийся сцеплением
заголовков исходных отношений, а тело состоит из множества всех
кортежей, представляющих собой сцепление кортежа a из отношения А, и кортежа b из отношения В.
Таким образом, кардинальное число результата равняется произведению кардинальных чисел А и В, а степень результата равна
сумме степеней А и В.
А:
В:
№
Фамилия
Имя
Группа
1
Иванов
Иван
3801
2
Петров
Петр
3801
3
Сидоров
Иван
3802
№
Фамилия
Имя
Группа
1
Иванов
Иван
3801
2
Рыжов
Карп
3801
Рис. 3.5. Пример отношений, совместимых по типу
39
A union B:
А intersect В:
A–B:
№
Фамилия
Имя
Группа
1
Иванов
Иван
3801
2
Петров
Петр
3801
3
Сидоров
Иван
3802
4
Рыжов
Карп
3801
№
Фамилия
Имя
Группа
1
Иванов
Иван
3801
№
Фамилия
Имя
Группа
1
Петров
Петр
3801
2
Сидоров
Иван
3802
Рис. 3.6. Выполнение объединения, пересечения и вычитания
На рис. 3.7 показаны два отношения А и В и их декартово произведение (times).
А:
№
Фамилия
1
Бураков
2
Латыпова
А times B:
В:
№
Марка
1
Reno
2
Chery
№
Фамилия
Марка
1
Бураков
Reno
2
Бураков
Жигули
Chery
3
Латыпова
Reno
4
Латыпова
Chery
Рис. 3.7. Выполнение операции декартова произведения
40
Рассмотрим далее специальные реляционные операторы.
Выборка – эта операция, которая возвращает все кортежи из
определенного отношения, удовлетворяющие определенным условиям.
Эту операцию обозначают RESTRICT или SELECT (не путать с
оператором SQL). Вместо термина «выборка» употребляют также
термин «ограничение».
Просто «выборка» – это сокращение от -выборки, где под  понимается любой скалярный оператор сравнения: <,>,= и т. п. Например:
A where (X  Y).
Такая запись означает, что из отношения А формируется отношение с таким же заголовком и телом, содержащим множество
всех кортежей А, для которых проверка условия (X  Y) дает значение «истина».
Атрибуты Х и Y должны быть определены на одном и том же домене, а оператор  должен иметь смысл для этого домена. Пример
приведен на рис. 3.8.
Проекция – эта операция возвращает отношение, содержащее
все кортежи (называемые подкортежами) определенного отношения после исключения из него некоторых атрибутов. Дублирующие подкортежи также исключаются.
Рассмотрим пример (рис. 3.9, где операция проекции обозначается квадратной скобкой).
А:
А where Рост > 200:
№
Фамилия
Рост
1
Иванов
156
2
Козлов
178
3
Петров
190
4
Сабонис
220
№
Фамилия
Рост
1
Сабонис
220
Рис. 3.8. Иллюстрация операции -выборки
41
На рис. 3.9 показаны проекции исходного отношения по атрибутам «Город» и «Страна». Если атрибутов в проекции больше одного, то они указываются в квадратной скобке через запятую.
Соединение – эта операция возвращает отношение, кортежи которого являются сочетанием двух кортежей (принадлежащим, соответственно, двум определенным отношениям), имеющих общее
значение для одного или нескольких общих атрибутов этих двух
отношений. Дублирующие подкортежи исключаются.
Пример показан на рис. 3.10, где join – операция соединения.
Выражения А join B и B join А эквивалентны, т. е. выполняется
условие коммутативности. Выполняется также свойство ассоциативности:
(А join B) join С = А join (B join С).
Если отношения не имеют общих атрибутов, то операция соединения в этом случае эквивалентна декартову произведению:
А join B = А times B.
А:
Страна
Город
США
Бостон
Россия
Псков
Россия
Пенза
Англия
Лондон
Англия
Перт
Страна
Город
Бостон
А [город]:
А [страна]:
США
Псков
Россия
Пенза
Англия
Лондон
Перт
Рис. 3.9. Иллюстрация операции проекции
42
А:
А1
В1
А2
В1
А3
В2
А join B:
В:
В1
С1
В2
С1
В3
С2
А1
В1
С1
А2
В1
С1
А3
В2
С1
Рис. 3.10. Иллюстрация операции соединения
Деление – это операция для двух отношений – бинарного и унарного. Она возвращает отношение, содержащее все значения одного
атрибута бинарного отношения, которые соответствуют (в другом
атрибуте) всем значениям в унарном отношении (рис. 3.11).
Итак, всего было рассмотрено 8 реляционных операций. Из них
только 5 операций – выборка, проекция, произведение и вычитание – являются примитивными в том смысле, что ни одна из них
не выражается через другие. Операции соединения, пересечения и
деления можно определить через другие операции. Например, операция соединения может быть определена как проекция выборки
произведения. Но на практике поддерживаются именно 8 операций, так как они используются чаще.
Результат каждой операции над отношением также является отношением. Это реляционное свойство называется свойством замкА
Х
Х
В:
А:
В
Х
С
У
А
У
А
Z
У
А dividery B:
А
Рис. 3.11. Иллюстрация операции деления
43
нутости. Из него следует вывод, что результат одной операции может использоваться в качестве исходных данных для другой, т. е.
можно записывать вложенные выражения, в которых операнды
сами являются выражениями, как это имеет место в обычной алгебре (там замкнуты не отношения, но числа). Это позволяет преобразовывать сложные отношения в более простые. Набор из 8-ми
основных операций Кодда обычно дополняют еще двумя операциями: расширение и подведение итогов.
Расширение – эта операция позволяет выполнять скалярные
вычисления, с ее помощью из существующего отношения создается новое отношение, содержащее дополнительный атрибут, значения которого получаются путем скалярных вычислений. Операция
расширения (extend) позволяет выполнять «горизонтальные» вычисления (построчные). Пример приведен на рис. 3.12.
Подведение итогов (summarize) – эта операция аналогична операции расширения, но позволяет выполнять вычисления не «горизонтально», а «вертикально».
При выполнении подведения итогов исходное отношение перегруппируется в множество кортежей с одинаковыми значениями
атрибута, и для каждого множества в результат помещается один
кортеж.
А:
№
Фамилия
Зарплата
1
Иванов
1000
2
Козлов
2000
3
Петров
3000
Extend А add (Зарплата*0.13) as Налог:
№
Фамилия
Зарплата
Налог
1
Иванов
1000
130
2
Козлов
2000
260
3
Петров
3000
390
Рис. 3.12. Пример операции расширения
44
А:
№
Футболист
Голы
1
Иванов
1
2
Петров
2
3
Иванов
3
4
Петров
1
5
Сидоров
5
6
Пеле
10
Summarize A by (Футболист)
add SUM(Голы) as Счет:
№
Футболист
Голы
1
Иванов
4
2
Петров
3
3
Сидоров
5
4
Пеле
10
Рис. 3.13. Пример операции подведения итогов
Кардинальное число результата равно кардинальному числу
проекции отношения А по атрибутам A1, A2, …An, а степень результата равна степени такой проекции плюс 1. Пример операции подведения итогов показан на рис. 3.13.
Реляционная модель, кроме реляционной алгебры, включает
операции реляционного присвоения. Обычно используются три
операции: вставка, удаление и обновление.
Операция вставки INSERT. В общем виде:
INSERT source INTO target,
где source и target – совместимые по типу отношения. Значение
source вычисляется, и все кортежи результата вставляются в отношение target. Например:
INSERT (S THERE city = «London») INTO TEMP,
где S и TEMP – отношения, совместимые по типу.
Операция обновления UPDATE: все кортежи в результирующем
отношении обновляются в соответствии с указанным оператором
присвоения. Например:
UPDATE P WHERE color=»Red» city:= «Paris».
45
В этом примере по атрибуту COLOR проверяется условие обновления для атрибута CITY (WHERE – «где»).
Операция удаления – DELETE. Например:
DELETE S WHERE status<20.
Здесь в отношении S удаляются все кортежи, в которых значение атрибута status < 20.
Вопросы для самопроверки
1. Какое практическое значение имеет реляционная алгебра?
2. Какой математический термин соответствует таблице?
3. Какой математический термин соответствует строке таблицы?
4. Какой математический термин соответствует столбцу таблицы?
5. Какой математический термин соответствует количеству строк таблицы?
6. Какой математический термин соответствует количеству столбцов
таблицы?
7. Как называется совокупность допустимых значений атрибута?
8. Какие реляционные операции использует реляционная алгебра?
9. Какие ограничения наложены в реляционной алгебре на операции
объединения, пересечения и вычитания?
10. Какие ограничения наложены в реляционной алгебре на операцию
декартова произведения?
11. Какие отношения являются совместимыми по типу?
12. Почему при выполнении реляционных операций исключаются дублирующие подкортежи?
13. Чему равно кардинальное число декартова произведения двух отношений?
14. Чему равна степень декартова произведения двух отношений?
15. Как выполняется реляционная операция выборки?
16. Как выполняется реляционная операция проекции?
17. Как выполняется реляционная операция соединения?
18. Как выполняется реляционная операция деления?
19. Как выполняется реляционная операция расширения?
20. Как выполняется реляционная операция подведения итогов?
46
ГЛАВА 4. ОТНОШЕНИЯ И НОРМАЛЬНЫЕ ФОРМЫ
4.1. Понятие домена
Данные, которые в реляционной модели не имеют внутренней
структуры, часто называют скалярами. Например, если реляционная модель работает с названиями городов, то любое название – это
скаляр, не разложимый на отдельные слоги или буквы. Скалярные
значения называют также атомарными.
Домен – это именованное множество скалярных значений одного типа. Конкретное значение атрибута отношения может браться
из одного домена.
Основное назначение домена заключается в ограничении сравнений. Операции между разными атрибутами имеют смысл только
тогда, когда атрибуты определены на одинаковых доменах. В противном случае, в любой реальной системе последует сообщение об
ошибке, т. е. целостность атрибутов автоматически поддерживается.
В простейших случаях каждый домен представляет собой один
из поддерживаемых системой типов данных. Например, следующая запись на реляционном языке:
CREATE DOMAIN NAME CHAR(20),
– означает определение домена как строки текста длиной до 20 символов.
4.2. Понятие отношения
Следует различать переменную отношения, т. е. именованный
объект, который принимает то или иное значение, и само значение
отношения.
Например, выражение на реляционном языке:
CREATE BASE RELATION NAME,
– создает переменную базового отношения с именем NAME, значение которой в любой момент времени будет определенным. Например, в разные моменты времени может изменяться кардинальное
число.
Базовое отношение – это такое отношение, которое получило
название при проектировании БД и стало ее частью. Существуют
также производные отношения, которые вводятся через базовые.
Отношение определяется на множестве доменов (не обязательно
различных) и содержит заголовок и тело.
47
Заголовок содержит фиксированное множество атрибутов, т. е.
множество пар <имя атрибута – имя домена>. Количество атрибутов дает степень отношения.
Отношение первой степени называется унарным, второй степени –
бинарным, третьей степени – тернарным, n-й степени – n-арным.
Тело содержит множество кортежей, каждый из которых состоит из множества пар <имя атрибута – значение атрибута>. Количество кортежей дает кардинальное число, или мощность отношения.
Таким образом, отношение – это абстрактный тип объекта, в то
время как таблица – это конкретное изображение этого абстрактного объекта.
Отношения имеют свойства:
1) нет одинаковых кортежей;
2) кортежи не упорядочены сверху вниз;
3) атрибуты не упорядочены слева направо;
4) все значения атрибутов атомарные.
Первое и второе свойства обосновываются тем, что тело отношения
является математическим множеством, а множество в математике по
определению не содержит одинаковых элементов и не упорядочено.
Третье свойство – следствие того, что заголовок отношения также является множеством.
Четвертое свойство является следствием того, что домены содержат только атомарные значения. Это свойство является необходимым для того, чтобы таблица была представлена в 1НФ.
С точки зрения реляционной модели, все отношения находятся
в 1НФ. Использование доменов с неатомарными значениями недопустимо.
4.3. Потенциальные ключи
Первичный ключ является частью более общего понятия потенциального ключа. Рассмотрим определение: пусть U – некоторое отношение, тогда K – потенциальный ключ для U, т. е. подмножество
множества атрибутов U, обладающее следующими свойствами:
1) свойство уникальности, т. е. нет двух различных кортежей
в отношении U с одинаковым значением K;
2) свойство неизбыточности, т. е. никакое из подмножеств K не
обладает свойством уникальности.
Так как кортежи отношения уникальны, то, по крайней мере,
комбинация всех атрибутов будет обладать свойством уникальности и может служить потенциальным ключом.
48
На практике отношение может иметь несколько потенциальных
ключей. Если ключ состоит из нескольких атрибутов, то он составной, если из одного атрибута, то простой. Из нескольких потенциальных ключей один должен быть выбран в качестве первичного, а
остальные будут альтернативными.
Потенциальные ключи определяются как множества, поэтому
при ссылке на них указываются скобки: {S#}. Иногда просто обозначают S#.
Потенциальные ключи обеспечивают основной механизм адресации на уровне кортежей в реляционной системе. Это аналог адресации оперативной памяти в компьютере.
4.4. Внешние ключи
Пусть U – некоторое базовое отношение, тогда внешний ключ
FК в U – это подмножество множества атрибутов U такое, что:
1) существует базовое отношение U1 с потенциальным ключом СК;
2) каждое значение FK в текущем отношении U всегда совпадает
со значением CK некоторого кортежа в текущем значении U1.
Например:
DEPT {DEPT#, DNAME, BUDGET} PRYMARY KEY {DEPT#}.
Это описание отношения DEPT с первичным ключом D.
EMP {E#, ENAME, D#, SALARY} PRYMARY KEY{E#}
FOREGN KEY{D#} REFERENCES DEPT.
Это описание отношения EMP с первичным ключом E и внешним ключом D, соответствующим первичному ключу отношения
DEPT.
Таким образом, значение внешнего ключа представлено ссылкой к кортежу, содержащему значение потенциального ключа.
Отсюда возникает правило ссылочной целостности: БД не должна
содержать несогласованных значений внешних ключей, т. е. не
должно быть значения внешнего ключа, которому не соответствует
потенциального ключа в целевом отношении.
Для обеспечения ссылочной целостности используют два приема:
1) можно запретить удаление потенциального ключа, на который ссылается внешний ключ (команда RESTRICTED);
2) каскадирование операций удаления потенциального ключа,
когда одновременно удаляются записи, в которых есть ссылки на
этот внешний ключ (CASCADES).
49
Аналогично можно запретить или каскадировать обновление
потенциального ключа.
4.5. Функциональные зависимости
Распознавание функциональных зависимостей (ФЗ) – это процесс выяснения смысла тех или иных данных.
Пусть U – это отношение, а X  Y – произвольные подмножества
множества атрибутов отношения U. Тогда Y функционально зависимо от Х (обозначается X  Y) тогда и только тогда, когда каждое
значение множества X связано в точности с одним значением множества Y.
Более простое определение: X и Y функционально зависимы,
если любые два кортежа отношения U при одинаковых Х имеют
также и одинаковые Y.
Функциональная зависимость тривиальна, когда правая часть
является подмножеством левой части ФЗ (детерминанта). Например: {S#, P#}  S#.
Функциональная зависимость транзитивна, если
A  B, B  C и A  C.
В качестве примера рассмотрим три отношения, приведенные
табл. 4.1–4.3.
Таблица 4.1
«Поставщики»
S#
SNAME
STATUS
CITY
S1
Иванов
20
Москва
S2
Дюма
30
Париж
S3
Ватсон
40
Лондон
Таблица 4.2
«Детали»
P#
50
PNAME
COLOR
WEIGHT
CITY
P1
Гайка
Красный
12
Москва
P2
Болт
Синий
14
Москва
P3
Гайка
Белый
15
Лондон
P4
Шайба
Черный
23
Париж
Таблица 4.3
«Поставки»
CITY
S#
S1
S1
S1
S2
S3
Москва
Москва
Москва
Париж
Лондон
P#
P1
P2
P3
P3
P4
SNAME
S#
STATUS
PNAME
P#
COLOR
WEIGHT
CITY
CITY
QTY
200
300
100
100
100
CITY
S#
QTY
P#
Рис. 4.1. Функциональные зависимости трех отношений
Для табл. 4.1–4.3 ФЗ можно изобразить графически (рис. 4.1,
где стрелка начинается с потенциального ключа соответствующего
отношения).
В последнем отношении справедливо:
S#  CITY.
Если X – потенциальный (первичный) ключ отношения U, то все
атрибуты Y отношения U должны быть функционально зависимы
от X.
Если отношение U удовлетворяет функциональной зависимости
А  В и А не является потенциальным ключом, то U функционально избыточно.
4.6. Нормализация отношений
Процедуру нормализации можно описать как процедуру исключения стрелок, которые не начинаются с потенциального ключа.
51
Первые три НФ были определены Коддом. Бойс усовершенствовал 3НФ, после чего она стала называться НФ Бойса–Кодда.
Процедура нормализации предполагает разбиение или декомпозицию данного отношения на другие отношения, причем декомпозиция должна быть обратимой, т. е. не приводить к потерям информации. Вопрос о том, происходят ли потери информации, тесно
связан с понятием функциональной зависимости.
Теорема: пусть U{A, B, C} является отношением с атрибутами A,
B, C. Если А  В, то U равно соединению его проекций {A, B} и {A, C}.
Например, рассмотрим таблицу «Поставщики». Здесь: U{S#,
SNAME, STATUS, CITY}, поэтому U может быть разбито на: U1{S#,
SNAME, STATUS} и U2{S#, CITY}.
Функциональная зависимость называется неприводимой слева,
если она не является функционально избыточной. Например (для
таблицы «Поставки»):
{S#,P#}  CITY.
Здесь атрибут P# является функционально избыточным и ФЗ
можно переписать в виде:
{S#}  CITY.
В этом случае атрибут CITY неприводимо зависим от S#.
Вторая нормальная форма
Отношение находится во 2-й НФ тогда и только тогда, когда оно
находится в 1-й НФ и каждый неключевой атрибут неприводимо
зависим от первичного ключа.
Всякое отношение, которое находится в 1-й НФ и не находится
во 2-й НФ, всегда можно свести к эквивалентному набору отношений, находящихся во 2-й НФ.
Рассмотрим пример. Пусть определено отношение:
FIRST{S#,STATUS,CITY,P#,QTY} PRIMARY KEY{S#,P#}.
И пусть существует ФЗ:
{S#}  CITY, {S#}  STATUS, CITY  STATUS.
Тогда отношению FIRST соответствует диаграмма ФЗ, показана
на рис. 4.2.
Здесь неключевые атрибуты не все взаимонезависимы, так как
STATUS зависит от CITY, и не все неприводимо зависимы, так как
CITY и STATUS зависят только от атрибута S#.
Поэтому следует разбиение, показанное на рис. 4.3.
SECOND{S#, STATUS, CITY}.
52
S#
CITY
P#
STATUS
QTY
Рис. 4.2. Функциональные зависимости отношения FIRST
CITY
S#
QTY
S#
STATUS
P#
Рис. 4.3. Отношение FIRST после декомпозиции
SP{S#, P#, QTY}.
SECOND и SP – две проекции отношения FIRST, которое является соединением отношений SECOND и SP по S#.
Третья нормальная форма
Отношение находится в 3-й НФ тогда и только тогда, когда оно
находится во 2-й НФ и каждый неключевой атрибут нетранзитивно
зависит от первичного ключа.
Рассмотрим отношение SECOND (рис. 4.4).
В этом отношении зависимость STATUS от S# функциональная
и неприводимая, но она также транзитивна (показано пунктиром),
так как
S#  CITY, CITY  STATUS и S#  STATUS.
Такое положение вызывает ряд проблем. Например, если значение STATUS для города изменилось, то нужно искать все корCITY
S#
STATUS
Рис. 4.4. Пример транзитивной зависимости
53
тежи для этого города и менять там значение. Поэтому отношение
SECOND преобразуется в два отношения (рис. 4.5).
Эти отношения находятся в 3-й НФ. Обновления в каждой из
проекций могут выполняться независимо (при соблюдении условий целостности).
Возможна и другая декомпозиция (рис. 4.6).
Здесь также обе проекции находятся в 3НФ, но такой вариант
менее желателен, поскольку здесь теряется семантика ФЗ.
Нужно заметить, что уровень нормализации данного отношения
определяется семантикой, а не конкретными значениями данных
в некоторой момент времени. Глядя на таблицу, нельзя доказать,
находится или она в 3НФ, можно лишь высказать предположение
и подтвердить его данными.
Нормальная форма Бойса–Кодда
Нормальная форма Бойса-Кодда (НФБК) является уточнением
3НФ: отношение находится в НФБК тогда и только тогда, когда
каждая нетривиальная и неприводимая слева ФЗ обладает потенциальным ключом в качестве детерминанта.
Более простое определение: отношение находится в НФБК тогда и только тогда, когда детерминанты являются потенциальными
ключами.
Рассмотрим пример:
S{S#, NAME#, STATUS, CITY} CANDIDATE KEY {S#}
CANDIDATE KEY {NAME#}.
Здесь S# и NAME# – потенциальные ключи и выполняются
требования 3НФ, поскольку все детерминанты являются потенциальными ключами (рис. 4.7).
Здесь выполняются требования НФБК.
S#
CITY
CITY
STATUS
Рис. 4.5. Устранение транзитивной зависимости
S#
CITY
S#
STATUS
Рис. 4.6. Вариант устранения транзитивной зависимости
54
S#
STATUS
NAME#
CITY
Рис. 4.7. Отношение в НФБК
Рассмотрим другой пример:
SSP{S#, SNAME, P#, QTY} CANDIDATE KEY {S#, P#}
CANDIDATE KEY {SNAME, P#}.
Данное отношение не находится в НФБК, потому что содержит
два детерминанта S# и SNAME (они определяют друг друга), которые не являются потенциальными ключами этого отношения. Поэтому данное отношение следует разбить на две проекции:
SS{S#, SNAME} и SP{S#, P#, QTY}, или
SS{S#, SNAME} и SP{SNAME, P#, QTY}.
Оба эти варианта находятся в НФБК.
Четвертая НФ
Рассмотрим ненормализованное отношение «дисциплина – преподаватель – учебник», заданное табл. 4.4.
Предполагается, что каждый курс может преподаваться любым
преподавателем из соответствующей группы с использованием
всех учебников.
Функциональные зависимости здесь не заданы, поэтому единственная операция нормализации здесь заключается в выравнивании (табл. 4.5).
Таблица 4.4
Неформализованное отношение
Дисциплина
Физика
Математика
Преподаватель
Белов
Краснов
Белов
Учебник
Основы механики
Принципы оптики
Векторный анализ
Алгебра
Геометрия
55
Таблица 4.5
Отношение после выравнивания кортежей
Дисциплина
Преподаватель
Физика
Физика
Физика
Физика
Математика
Математика
Математика
Белов
Белов
Краснов
Краснов
Белов
Белов
Белов
Учебник
Основы механики
Принципы оптики
Основы механики
Принципы оптики
Векторный анализ
Алгебра
Тригонометрия
Это отношение находится в НФБК, поскольку является «полностью ключевым», но характеризуется значительной избыточностью. Например, для добавления информации о новом преподавателе математики необходимо создать три новых кортежа – по одному для каждого учебника.
Чтобы такой ситуации не возникало, нужно, прежде всего, разделить все независимые повторяющиеся группы. В примере нужно
разбить исходное отношение на два отношения (табл. 4.6; 4.7).
Эта декомпозиция не может быть выполнена на основе ФЗ (так
как их нет). Здесь требуется новый тип зависимости – многозначная зависимость.
Многозначная зависимость (МЗ) – это обобщение ФЗ в том смысле, что каждая ФЗ является МЗ, т. е. ФЗ – это МЗ, в которой множеТаблица 4.6
Первая многозначная зависимость
Физика
Физика
Дисциплина
Белов
Краснов
Преподаватель
Таблица 4.7
Вторая многозначная зависимость
Дисциплина
Физика
Физика
Математика
Математика
Математика
56
Учебник
Основы механики
Принципы оптики
Векторный анализ
Алгебра
Тригонометрия
ство зависимых значений, соответствующее заданному значению
детерминанта, всегда является одноэлементным множеством.
В приведенном примере можно выделить две МЗ:
Дисциплина  Преподаватель.
Дисциплина  Учебник.
Первая зависимость означает, что хотя для каждого курса не
существует одного преподавателя, но каждый курс имеет вполне
определенное множество преподавателей.
Дадим еще одно определение МЗ: пусть A, B, C – произвольные
подмножества множества атрибутов отношения R. Тогда В многозначно зависит от А (А  В) тогда и только тогда, когда множество значений В, соответствующее заданной паре (значение А, значение С) отношения R, зависит только от А, но не зависит от С.
Для данного отношения R{A, B, C} МЗ (А  В) выполняется
только тогда, когда также выполняется МЗ (А  С). Таким образом, многозначные зависимости всегда образуют связанные пары
и поэтому их обычно представляют вместе в символическом виде:
А  ВЅС.
В приведенном примере:
Дисциплина  Преподаватель Ѕ Учебник.
Таким образом, 4-я НФ требует устранения МЗ.
Существуют также 5НФ и т. д., но эти случаи не имеют большого практического значения.
В целом, можно сказать, что НФ являются соображениями здравого смысла, которые можно описать формально.
Вопросы для самопроверки
1. Какие данные в реляционной модели называют скалярными или атомарными?
2. Что такое домен?
3. Чем отличается переменная отношения от его значения?
4. Что такое базовое отношение?
5. Как называется количество атрибутов отношения?
6. Какими свойствами обладает отношение?
7. Что такое потенциальный ключ отношения?
8. Что такое внешний ключ отношения?
9. Как формулируется правило ссылочной целостности?
10. Какие два приема можно применять для обеспечения ссылочной
целостности?
57
11. Что такое функциональная зависимость?
12. Что такое тривиальная функциональная зависимость?
13. Что такое транзитивная функциональная зависимость?
14. Как называется левая часть функциональной зависимости?
15. Где начинается стрелка при графическом изображении функциональной зависимости?
16. В каком случае отношение является функционально избыточным?
17. Какое определение можно дать процедуре нормализации отношения?
18. Каким способом выполняется нормализация отношения?
19. Что такое неприводимая слева функциональная зависимость?
20. Когда отношение находится во 2-й НФ?
21. Когда отношение находится в 3-й НФ?
22. Чем отличается НФ Бойса–Кодда от 3-й НФ?
23. Что такое многозначная зависимость?
24. В чем заключается требование 4-й НФ?
58
ГЛАВА 5. ТРЕБОВАНИЯ К РЕЛЯЦИОННОЙ СУБД
Как уже упоминалось, существует несколько сотен реляционных
СУБД для мейнфреймов и персональных компьютеров. К сожалению, некоторые из них, строго говоря, не соответствуют определению
реляционной модели. В частности, некоторые поставщики традиционных вариантов СУБД, основанных на сетевой и иерархической
моделях данных, реализуют в своих продуктах только некоторые
черты реляционных систем, чтобы иметь основания заявить об их
принадлежности к реляционным системам. Озабоченный тем, что
потенциальные возможности и смысл реляционного подхода искажаются, Кодд предложил 12 правил определения реляционных систем
(а точнее, 13, если учитывать фундаментальное правило 0). Эти правила образуют своего рода эталон, по которому можно определить принадлежность СУБД к разряду действительно реляционных систем.
На протяжении многих лет предложенные Коддом правила вызывали массу нареканий у заинтересованных лиц и специалистов.
Одни сочли их не более чем чисто теоретическими упражнениями.
Другие заявили, что их продукты уже удовлетворяют многим этим
правилам, если не всем. Эта дискуссия способствовала росту понимания пользователями и сообществом разработчиков СУБД важнейших свойств действительно реляционных СУБД. Чтобы подчеркнуть особое значение этих правил, они были разделены на пять
функциональных групп:
1) фундаментальные правила;
2) структурные правила;
3) правила целостности;
4) правила управления данными;
5) правила независимости от данных.
Фундаментальные правила (правила 0 и 12). Образно выражаясь, правила 0 и 12 являются «лакмусовой бумажкой», которая
позволяет определить принадлежность системы к реляционным
СУБД. Если система не удовлетворяет этим правилам, то ее не следует считать реляционной.
Правило 0 – фундаментальное правило: любая система, которая
рекламируется или представляется как реляционная СУБД, должна управлять базами данных исключительно с помощью ее реляционных функций.
Это правило означает, что СУБД не должна прибегать к любым
нереляционным операциям для выполнения таких видов работ,
как определение данных и манипулирование ими.
59
Правило 12 – правило запрета обходных путей. Если реляционная
система имеет низкоуровневый язык (с последовательной построчной
обработкой), он не может быть использован для отмены или обхода
правил и ограничений целостности, составленных на реляционном
языке более высокого уровня (с обработкой сразу нескольких строк).
Это правило гарантирует, что все попытки доступа к базе данных контролируются СУБД таким образом, что целостность БД не
может быть нарушена без ведома пользователя или администратора БД. Это, однако, не исключает возможностей использования
языка низкого уровня с интерфейсом последовательной построчной обработки.
Структурные правила (правила 1 и 6). Фундаментальным структурным понятием реляционной модели является отношение. Кодд
утверждает, что реляционная СУБД должна поддерживать работу
с несколькими структурными элементами, включая отношения, домены, первичные и внешние ключи. Для каждого отношения (таблицы) базы данных должен быть определен первичный ключ.
Правило 1 – представление информации. Вся информация в реляционной БД представляется в явном виде на логическом уровне
и только одним способом – в виде значений в таблицах.
Согласно этому правилу, вся информация, даже метаданные в
системном каталоге, должна храниться в виде отношений и управляться с помощью тех же функций, которые используются для
работы с данными. Упоминание в этом правиле логического уровня означает, что такие физические конструкции, как индексы, не
должны быть представлены в модели и пользователь не должен
явно ссылаться на них в операциях извлечения данных, даже в тех
случаях, когда они существуют.
Правило 6 – обновление представления. Все представления, которые являются теоретически обновляемыми, должны быть обновляемы и в данной системе.
Это правило касается исключительно представлений. Данное
правило гласит, что если представление является теоретически
обновляемым, то СУБД должна уметь выполнять подобные обновления. На самом деле, ни одна существующая система не поддерживает это требование, поскольку все еще не определены условия
идентификации всех теоретически обновляемых представлений.
Правила целостности (правила 3 и 10). Кодд предложил два
правила поддержки целостности данных. Поддержка целостности
данных является важным критерием оценки пригодности системы для практических нужд. Чем больше ограничений целостности
60
может поддерживаться самой СУБД, а не отдельными ее приложениями, тем выше гарантии качественности данных.
Правило 3 – систематическая обработка неопределенных значений (NULL). Неопределенные значения (задаваемые с помощью
определителя NULL), т. е. значения, отличные от пустой строки
или строки пустых символов, а также от нуля или любого другого конкретного значения, поддерживаются для систематического
представления отсутствующей или неприемлемой информации,
причем независимо от типа данных.
Правило 10 – независимость ограничений целостности. Специфические для данной реляционной СУБД ограничения целостности
должны определяться на языке реляционных данных и храниться
в системном каталоге, а не в прикладных программах.
Кодд особо подчеркивает, что сведения об установленных ограничениях целостности данных должны храниться в системном каталоге, а не инкапсулироваться в отдельных прикладных программах или пользовательских интерфейсах. Хранение ограничений
целостности в системном каталоге предоставляет важное преимущество централизованного управления и приведения их в действие.
Правила манипулирования данными (правила 2, 4, 5 и 7). Идеальная СУБД должна поддерживать 18 функций управления данными. Они определяют полноту языка запросов (здесь термин «запрос» включает и операции вставки, обновления и удаления). Правила манипулирования данными определяют способ применения
функций управления данными. Строгое следование этим правилам
позволяет изолировать пользователя и прикладные программы от
физического и логического механизмов реализации средств манипулирования данными.
Правило 2 – гарантированный доступ. Для всех и каждого элемента данных (т. е. его атомарного значения) реляционной базы
данных должен быть гарантирован логический доступ на основе
комбинации имени таблицы, значения первичного ключа и значения имени столбца.
Правило 4 – динамический интерактивный каталог, построенный по правилам реляционной модели. Описание базы данных
должно представляться на логическом уровне таким же образом,
как и обычные данные, что позволит санкционированным пользователям использовать для обращений к этому описанию тот же реляционный язык, что и при обращении к данным.
Это правило указывает на то, что должен существовать только один язык, предназначенный для манипулирования как мета61
данными, так и обычными данными, причем в СУБД для организации хранения системной информации должна использоваться
только одна логическая структура – отношения.
Правило 5 – исчерпывающий язык данных. Реляционная система может поддерживать несколько языков и различные режимы
работы с терминалами. Однако должен существовать, по крайней
мере, один язык, операторы которого позволяли бы выражать все
следующие конструкции:
1) определение данных;
2) определение представлений;
3) команды манипулирования данными (доступные как интерактивно, так и из программ);
4) ограничения целостности;
5) авторизация пользователей;
6) организация транзакций (запуск, фиксация и откат).
Следует отметить, что новый стандарт ISO для языка SQL обеспечивает выполнение всех этих функций таким образом, что любой удовлетворяющий этому стандарту язык автоматически будет
удовлетворять и этому правилу.
Правило 7 – высокоуровневые операции вставки, обновления
и удаления. Способность обрабатывать базовые или производные
отношения (т. е. представления) как единый операнд должна относиться не только к процедурам извлечения данных, но и к операциям вставки, обновления и удаления данных.
Правила независимости от данных (правила 8, 9 и 11). Кодд
определяет три правила независимости данных от приложений,
которые эти данные используют. Строгое соблюдение этих правил
гарантирует, что пользователи и разработчики будут защищены от
необходимости вносить тотальные изменения в приложения при
каждой реорганизации базы данных на низком уровне.
Правило 8 – физическая независимость от данных. Прикладные
программы и средства работы с терминалами должны оставаться
логически незатронутыми при внесении любых изменений в способы хранения данных или методы доступа к ним.
Правило 9 – логическая независимость от данных. Прикладные
программы и средства работы с терминалами должны оставаться
логически незатронутыми при внесении в базовые таблицы любых
не меняющих информацию изменений, которые теоретически не
должны затрагивать прикладное программное обеспечение.
Правило 11 – независимость от распределения данных. Язык
манипулирования данными в реляционной СУБД должен позво62
лять прикладным программам и запросам оставаться логически
неизменными, независимо от того, как хранятся данные – физически централизованно или в распределенном виде.
Независимость от распределения данных означает, что прикладная программа, осуществляющая доступ к СУБД на отдельном
компьютере, должна без каких-либо модификаций продолжать работать и в том случае, когда данные в сети будут перенесены с одного компьютера на другой. Другими словами, для конечного пользователя должна быть создана иллюзия того, что данные централизованно хранятся на единственном компьютере, а ответственность
за перемещение и поиск данных среди (возможно) нескольких мест
их хранения должна возлагаться исключительно на систему. Обратите внимание, что здесь не сказано о том, что реляционная СУБД
должна непременно поддерживать работу с распределенной базой
данных. Здесь просто имеется в виду, что язык запросов должен
оставаться неизменным и в том случае, когда возможность работы
с распределенными данными реализуется в некоторой СУБД.
Вопросы для самопроверки
1. С какой целью были предложены правила определения реляционных систем (РС)?
2. На какие группы разделяются правила определения РС?
3. Как формулируются фундаментальные правила определения РС?
4. С какими структурными элементами должна поддерживать работу
РС?
5. Какие способы представления информации должна использовать РС?
6. Как понимается независимость ограничений целостности?
7. Может ли РС обрабатывать отсутствующую информацию?
8. Как понимается физическая независимость данных в РС?
9. Как понимается логическая независимость данных в РС?
10. Как понимается независимость от распределения данных в РС?
63
ГЛАВА 6. ЯЗЫК SQL
6.1. Общие сведения
Язык SQL разработан в 1970 г. компанией IBM. В настоящее время это стандартный язык СУБД. Вернее, подъязык, так как в настоящее время SQL (обычно читается как «сиквэл») не содержит тех
средств, которые необходимы для создания полноценных программ
(нет команд присваивания, команд организации циклов и т. п.).
Язык SQL существует в виде одной из трех прикладных реализаций:
1) интерактивный, или автономный SQL. Он дает возможность пользователям непосредственно извлекать информацию из
БД или записывать информацию в БД;
2) статический SQL – это записанный заранее, а не создаваемый во время выполнения программы код SQL. Существуют две
версии статического SQL:
а) встроенный SQL – здесь код SQL включается в код исходного
текста программы, написанной на другом языке. Например, программа, написанная на языке Си, использует SQL при обращении к БД;
б) модульный SQL больше соответствует концепции объектноориентированного программирования. Здесь модули SQL компонуются с модулями других языков;
3) динамический SQL. Код SQL генерируется приложением во
время исполнения. Динамический SQL заменяет статический тогда, когда код SQL нельзя определить заранее.
Принципиальных отличий между всеми тремя реализациями нет.
Язык SQL использует трехзначную логику. Помимо значений
«истинно» и «ложно» (TRUE и FALSE), он имеет третье значение – «неизвестно» (UNKNOWN). Для обозначения пространства
в столбце, где отсутствует какой-либо элемент, используется метка
NULL. Если при сравнении было получено NULL, то получается
логическое значение UNKNOWN.
В коммерческую продажу SQL был выпущен в 1979 г. компанией ORACLE. В 1986 г. был объявлен официальным стандартом,
но при этом многие важные функции языка, например, уничтожение объектов и отмена привилегий, в стандарте описаны не были
и оставлялись на усмотрение разработчика. Поэтому в 1992 г. появился усовершенствованный стандарт SQL92 (в 5 раз больший по
объему). Однако ни один из коммерческих продуктов полностью
не соответствует стандарту. Поэтому полная переносимость прило64
жений (чтобы любое приложение могло работать с любой СУБД) в
настоящее время не поддерживается, но изменения для переноса
могут быть незначительными.
Таблица 6.1
Основные операторы SQL
Обработка данных (DML – data manipulation language)
SELECT
Считывание данных из БД
INSERT
Добавление новых строк
DELETE
Удаление строки
UPDATE
Обновление данных в БД
Определение данных (DDL – data definition language)
CREATE TABLE
Создание таблицы в БД
DROP TABLE*
Удаление таблицы из БД
ALTER TABLE*
Изменение структуры таблицы
CREATE VIEW
Создание нового представления
DROP VIEW*
Удаление представления из БД
CREATE INDEX*
Создание индекса для столбца
DROP INDEX
Удаление индекса столбца
CREATE SYNONYM*
Определение синонима имени таблицы
Обработка данных (DML – data manipulation language)
COMMENT*
Комментарий для таблицы или столбца
DROP SYNONYM*
Удаление синонима
LABEL*
Заголовок таблицы или столбца
Управление доступом
Предоставляет пользователю
GRANT
права доступа
REVOKE
Отменяет право доступа
Управление транзакциями
COMMIT
Завершает текущую транзакцию
ROLLBACK
Отменяет текущую транзакцию
Программный SQL
DECLARE
Определяет курсор для запроса
EXPLAIN*
Описывает доступ к данным для запроса
OPEN
Открывает курсор для чтения данных
FETCH
Считывает строку рзультатов запроса
CLOSE
Закрывает курсор
Готовит оператор к динамическому
PREPARE*
исполнению
EXECUTE*
Динамически выполняет оператор SQL
DESCRIBE*
Описывает подготовленный запрос
* – поддерживается не всеми версиями SQL.
65
Одно из новшеств стандарта SQL92 заключается в определении
понятий пользователя, схемы и сеанса, которые создают контекст
для операторов SQL.
Субъект или приложение, создающее SQL-операторы, называется SQL-агентом. Установление связи с СУБД определяет начало
сеанса.
Данные содержатся в таблицах, которые сгруппированы в схемы, а схемы – в каталоги. Каталоги могут быть сгруппированы
в кластеры. Кластер содержит все таблицы, к которым имеется доступ в данном сеансе. В любой момент времени может существовать
несколько сеансов, из них всегда один будет активным, а остальные – пассивными.
Приоритет исполнения SQL-операторов устанавливается в соответствии с привилегиями пользователя.
Такой подход особенно эффективен в архитектуре «клиент – сервер», когда сервер обрабатывает запросы от множества пользователей.
Основные задачи, решаемые SQL:
1) организация интерактивных запросов к БД. Пользователь
вводит команды SQL в свои программы, которые обеспечивают отображение данных в удобной форме;
2) программирование операций с БД. В прикладную программу
вставляются операторы SQL для организации ввода данных в БД и
других операций;
3) администрирование БД. Администратор БД определяет
структуру БД и порядок доступа к ней;
4) организация работы в системе «клиент – сервер» с помощью SQL;
5) организация работы в распределенных БД, когда взаимодействуют несколько вычислительных систем;
6) разработка «шлюзовых» программ, используемых при доступе из СУБД одного типа к СУБД другого типа.
В SQL используется всего около 30 операторов и около 300 ключевых слов (табл. 6.1).
6.2. Определение структур данных в SQL
В SQL поддерживаются три основных типа данных:
1) символьные строки;
2) числовые значения;
3) дата и время.
Символьные строки могут быть постоянной длины:
CHARACTER(n).
66
Или переменной длины:
CHARACTER VARYING(n).
В обоих случаях n означает максимальный размер строки. Если
строка имеет фиксированную длину, то свободные места заполняются пробелами, а при переменной длине длина строки равна числу
использованных позиций.
Числовые значения определяются почти так же, как в языках программирования (BIT(n), INTEGER и т. п.). Часто используется тип:
NUMBER(n),
т. е. целое десятичное или действительное десятичное, где n – число знаков.
Запись:
DECIMAL(4,1),
– означает, что число содержит всего 4 знака, из них 1 – после запятой. Общая длина числа называется точностью, а количество
знаков после точки – масштабом.
Типы данных дата и время соответствуют описываемым объектам.
Язык SQL поддерживает нулевой тип данных, под которым понимается отсутствие значения. Нулевое значение используется и
при создании таблиц, и при выполнении запросов. Для указания
нулевого значения можно использовать ключевое слово NULL или
одиночные кавычки.
6.3. Работа с таблицами в SQL
Выше уже упоминалось понятие схемы: это совокупность объектов БД, которые относятся к некоторому пользователю. Администратор БД регистрирует пользователя (речь идет о распределенной
БД) и присваивает пользователю имя и пароль. Если пользователь
получил имя USER1, то его таблицы будут иметь вид
USER1.tblN.
Но сам пользователь может указывать только имя таблицы. Если
же пользователь обращается к таблицам другого пользователя,
то он должен указывать полное имя:
USER2.tblN.
Такой механизм позволяет пользователям иметь одинаковые
имена таблиц.
67
Рассмотрим создание таблиц.
При создании таблицы используется оператор CREATE TABLE.
При описании полей можно использовать операторы NULL (по
умолчанию) и NOT NULL. Во втором случае необходимо вводить в
столбец какое-то значение.
В общем виде таблица создается оператором:
CREATE TABLE имя таблицы (имя_поля тип_данных [not null],
имя_поля тип_данных [not_null],... имя_поля тип_данных [not null]).
Например:
CREATE TABLE Студенты (фамилия char(20) not null, имя
char(10) not null,... рост number(3) not null, вес number(3) not null,
ТЕЛЕФОН CHAR(20)).
Так как не все имеют телефон, то значение этого столбца по
умолчанию может быть нулевым.
Для задания размеров таблицы при ее создании можно использовать оператор STORAGE. Например:
STORAGE (INITIAL 3K NEXT 2K).
Здесь указана величина первоначальной и дополнительной памяти, которая выделяется при превышении первоначального размера.
Модификация структуры существующей таблицы происходит с
помощью оператора ALTER TABLE. Он позволяет добавлять и удалять столбцы, изменять их определения, добавлять и удалять ограничения. Базовый синтаксис оператора:
ALTER TABLE имя_таблицы [modify] [column имя_столбца]
[null | not null] [restrict | cascade] [drop] [constraint имя_ограничения] [add] [column] определение_столбца.
Например:
ALTER TABLE Студенты modify Фамилия varchar[30].
Здесь изменяется атрибут столбца Фамилия.
Таблица может быть создана на основе существующей таблицы.
При создании новой таблицы из существующей используется следующий синтаксис:
CREATE TABLE имя_новой_таблицы AS
SELECT [* | столбец1, столбец2] FROM
имя_таблицы [where].
68
Оператор SELECT выбирает столбцы из указанной таблицы.
Пример добавления столбца:
ALTER TABLE Студенты ADD Ср_балл number(4).
Для удаления таблицы используется простой оператор:
DROP TABLE имя_таблицы [restrict / cascade].
Restrict и cascade служат для обеспечения целостности. Restrict
служит для сообщения о возможных ошибках, а cascade удаляет
все ссылки на таблицу.
Оператор ALTER TABLE не может удалить существующий столбец. В SQL вообще нет оператора для удаления столбца. Поэтому
для этого нужно сохранить данные таблицы, потом стереть таблицу, воссоздать новое определение и загрузить сохраненные данные.
При создании таблиц одно из полей используется в качестве первичного ключа. Например:
CREATE TABLE Студенты (Номер number(5) NOT NULL,
Фамилия char(20),... Телефон char(10) NULL),
PRIMARY KEY (Номер).
Первичный ключ не может содержать значение NULL.
Для столбца таблицы может быть установлено ограничение
UNIQUE. В этом случае каждая строка должна содержать в этом
столбце уникальное значение.
Определение внешнего ключа отличается тем, что для связи, которую внешний ключ создает с таблицей-предком, может вводиться имя. Например:
CREATE TABLE Отметки (Номер number(5) NOT NULL, предмет char(50), отметка number(1)), FOREIGN KEY Номер_студента
(Номер)
REFERENCES Студенты.
В этом примере «Номер» является внешним ключом, создающим связь с таблицей-предком «Студенты», а сама связь получила
имя «Номер_студента».
К определению внешнего ключа может быть добавлено правило удаления: ON DELETE CASCADE, или ON DELETE SET NULL,
или правило обновления такого же вида.
С помощью оператора ALTER TABLE можно изменять или добавлять первичные и внешние ключи, а также удалять их. Синтаксис
такой же, как в операторе CREATE TABLE. Удаление делается так:
ALTER TABLE Студенты DROP PRIMARY KEY Номер.
69
Как уже отмечалось, пользователь при ссылке на «чужие» таблицы обязан указывать их полные номера. Но в сложной БД полное
имя может оказаться очень длинным, поэтому в ряде БД вводится
понятие «синонима», что позволяет заменить полное имя таблицы.
Например:
CREATE SYNONYM Студенты
FOR ГУАП.КАФ31.ГР3701.Студенты.
Это упрощает написание SQL-запросов. Удаляется синоним легко:
DROP SYNONYM Студенты.
С помощью оператора CREATE можно обеспечивать индексирование строк таблицы. Например:
CREATE INDEX фам ON Студенты(Фамилия).
С помощью оператора CREATE возможно также создание представлений. Представлениями в SQL называют запрос на чтение,
которому присвоили имя, а затем сохранили в БД. Общая форма:
CREATE VIEW Имя AS Запрос.
Польза от представлений заключается в следующем:
1) они позволяют разным пользователям БД видеть ее поразному, что дает возможность контролировать доступ к данным;
2) доступ к данным упрощается, так как пользователь получает
представление о структуре данных в виде, наиболее отвечающем
решаемой им задаче.
Представление можно назвать «виртуальной» таблицей.
Когда СУБД встречает в операторе SQL ссылку на представление, она отыскивает его определение, сохраненное в БД. Затем запрос пользователя преобразуется в эквивалентный запрос к реальным таблицам. Представления допускают обновления и удаления.
Наряду с достоинствами, представления имеют и недостатки:
1) увеличивается время выполнения запроса;
2) обновление данных в сложном представлении может быть
очень трудоемким. Поэтому сложные представления доступны,
как правило, только для чтения.
6.4. Запросы в SQL
Запросы позволяют:
1) формировать сложные критерии для выбора записей из одной
или нескольких таблиц;
70
2) указывать поля, которые должны быть отображены для выбранных записей;
3) выполнять вычисления с использованием выбранных данных.
Язык SQL, прежде всего, должен реализовывать запросы на чтение. Оператор SELECT является наиболее мощным в SQL. Пример
простого оператора SELECT:
SELECT Фамилия FROM Студенты
WHERE (Рост > 200) AND (Вес > 150).
Здесь SELECT указывает список столбцов, которые должны
быть возвращены оператором; FROM указывает имя таблицы, из
которой считываются данные; WHERE указывает условия поиска.
Результатом SQL-запроса на чтение всегда является таблица, и
эту таблицу можно сохранить в БД.
Гибкость запросов на чтение обеспечивается тем, что они могут
содержать вычисляемые столбцы. В вычислениях можно использовать операции сложения, вычитания, умножения и деления. При
этом столбцы, над которыми производятся эти операции, должны
содержать числовые, а не текстовые данные. Например:
SELECT Рост, Вес, (Рост – Вес) FROM Студенты.
В результате этого запроса к двум столбцам «Рост» и «Вес» будет
добавлен третий столбец («Рост – Вес»), в котором будут записаны
соответствующие значения.
Если требуется получить значение всех столбцом какой-либо таблицы, то используется символ (*).
Если в списке возвращаемых столбцов запроса на чтение указывается первичный ключ, то каждая строка результатов запросов
будет уникальной. Если первичного ключа нет, то результаты запроса могут содержать повторяющиеся значения. Для исключения
повторений используется ключевое слово DISTINCT. Например:
SELECT DISTINCT Имя FROM Студенты.
Условия поиска в SQL могут быть самыми разнообразными,
в том числе:
1) сравнение;
2) проверка на принадлежность диапазону значений;
3) проверка на членство во множестве;
4) проверка на соответствие шаблону;
5) проверка на равенство значению NULL.
Пример сравнения был показан выше. Такие запросы допускают использование операторов <, >, >=, <=, <>, =.
71
При выполнении сравнений следует учитывать, что в результат
попадают только те строки, для которых условие поиска имеет значение TRUE, а проверка условия может дать три значения: TRUE,
FALSE и NULL.
Для проверки на принадлежность диапазону значений используется ключевое слово BETWEEN. Например:
SELECT Фамилия, Имя FROM Студенты
WHERE Вес BETWEEN 60 AND 80.
Эта операция допускает инвертирование:
… NOT BETWEEN 60 AND 80.
При проверке на членство во множестве используется ключевое
слово IN. Например:
SELECT Фамилия, Имя FROM Студенты
WHERE Имя IN (Петр, Иван).
Здесь также можно использовать отрицание.
При проверке на соответствие шаблону используется ключевое
слово LIKE. Оно применяется при поиске каких-то интересующих
значений. В простейшем случае найти в таблице заданный текст
можно простым сравнением. Например:
SELECT Фамилия, Имя FROM Студенты
WHERE Имя = «Оля».
В более общем случае объект поиска может быть известен неточно. Например – «Оля», или «Ольга», или «Оленька». Здесь необходим оператор LIKE:
SELECT Фамилия, Имя FROM Студенты
WHERE Имя LIKE «Ол%».
Знак % совпадает с последовательностью из 0 или более символов. Можно использовать также знак подчеркивания «_» для обозначения любого отдельного символа (в некоторых реализациях
могут применяться соответственно * и ?).
Проверка на равенство значению NULL. Например, если мы хотим найти студента, у которого нет телефона:
SELECT Фамилия FROM Студенты
WHERE Телефон IS NULL.
Здесь также допускается инверсия: IS NOT NULL.
72
Знак = при проверке на NULL использовать нельзя, так как
NULL – это не значение, а его отсутствие.
Еще пример:
SELECT Фамилия FROM Студенты
WHERE (Рост – Вес) < 100 IS UNKNOWN.
При помощи такого запроса можно отыскать строки, в которых операция сравнения невыполнима, так как одно из значений
(«Рост» или «Вес») неизвестно (равно NULL).
Строки результата запроса можно отсортировать при помощи
ключевого слова ORDER BY. Например:
SELECT Фамилия, Пол FROM Студенты
ORDER BY Пол, Фамилия.
Здесь сначала будут указаны фамилии всех женщин, а затем –
всех мужчин в алфавитном порядке.
Таким образом, общий алгоритм выполнения однотабличного
запроса следующий:
1) берется таблица, указанная в операторе FROM;
2) если есть предложение WHERE, то применяется заданное условие для каждой строки таблицы и оставляются только те строки,
для которых оно выполняется;
3) вычисляется для каждой строки значение каждого возвращаемого поля;
4) если указано ключевое слово DISTINCT, то удаляются все повторяющиеся поля;
5) если указано ключевое слово ORDER BY, то сортируются результаты запроса.
Результаты нескольких запросов могут быть объединены с помощью оператора UNION. Использование этого оператора позволяет формулировать сложные условия по частям. Чтобы это было
допустимым, должен выполняться ряд требований:
1) в таблицах должно быть одинаковое число столбцов;
2) типы данных в столбцах таблиц должны совпадать. Имена
столбцов не обязательно должны быть одинаковыми;
3) ни одна из таблиц не должна быть отсортирована с помощью
оператора ORDER BY.
Простой пример:
SELECT Фамилия FROM Студенты WHERE Рост > 190 UNION;
SELECT Фамилия FROM Студенты WHERE Рост < 160.
73
6.5. Внесение изменений в базы данных
Изменение информации при помощи SQL заключается в использовании запросов на добавление, изменение и обновление. Для
этого служат операторы INSERT, DELETE и UPDATE. Они более
просты, чем оператор SELECT:
INSERT – добавляет новые строки в таблицу;
DELETE – удаляет строки из таблицы;
UPDATE – обновляет данные в таблице.
Рассмотрим подробнее оператор INSERT. Этот оператор бывает
однострочным и многострочным. Например, если в группе появился новый студент:
INSERT INTO Студенты (Фамилия, Имя) VALUES (‘Иванов’,’Иван’).
Тип данных каждого вводимого значения должен соответствовать типу данных столбца, куда данные вводятся, иначе возникнет
ошибка.
Если в операторе INSERT опущен список столбцов, то считается, что заполняются все столбцы таблицы:
INSERT INTO Оценки VALUES (8, ‘Физика’, 2).
Если значение каких-то столбцов неизвестно, то нужно явно
указать значение NULL.
Многострочный INSERT: здесь значения новых строк в явном
виде не задаются. Источником новых строк служит запрос на чтение. Например:
INSERT INTO Большие_Студенты (Фамилия, Имя)
SELECT Фамилия, Имя FROM Студенты WHERE
Рост > 190.
Реальные СУБД, кроме команд INSERT, имеют средства пакетной загрузки, которые позволяют с высокой скоростью загрузить
большие объемы информации из файлов таблицы. В SQL такой тип
загрузки не рассматривается.
Оператор DELETE работает так же, как INSERT, т. е. он может
быть однострочным и многострочным. При помощи этого оператора можно удалить все строки:
DELETE FROM Студенты.
Таблица очищается, но не уничтожается.
При удалении можно использовать вложенные запросы. Допустим, мы хотим удалить все сведения об успеваемости исключенного студента:
74
DELETE FROM Оценки WHERE Номер_студента =
(SELECT Номер FROM Студенты WHERE Фамилия =
’Алексеев’).
Из таблицы «Оценки» удаляются все оценки, полученные студентом Алексеевым.
Оператор UPDATE обновляет значения одного или нескольких
столбцов в выбранных строках таблицы. Предложение WHERE отбирает строки таблицы. Предложение SET указывает, какие столбцы обновляются, и задает для них новые значения.
Например, если необходимо перевести всех дипломников преподавателя Иванова преподавателю Петрову, то получается:
UPDATE Дипломники SET Руководитель = 8
WHERE Руководитель = 5.
Здесь имеется в виду, что 5 и 8 – это, соответственно, номера преподавателей Иванова и Петрова в таблице «Дипломники».
Если одновременно нужно записать на Петрова еще и дипломников преподавателя Сидорова (с номером 2), то получается команда:
UPDATE Дипломники SET Руководитель = 8
WHERE Руководитель IN (5,2).
Оператор UPDATE можно применять без условия. В этом случае
обновляются все записи:
UPDATE Дипломники SET Руководитель = 8.
В операторе UPDATE можно использовать вложенные запросы
точно так же, как это было показано для оператора DELETE.
Особенность операторов INSERT, DELETE и UPDATE заключается в том, что они могут обращаться только к одной таблице.
6.6. Многотабличные запросы на чтение
Многотабличные запросы позволяют объединить данные нескольких таблиц. Пусть имеются две таблицы – 6.2 и 6.3.
Таблицы связаны отношением «один к одному» по полям «Номер» и «Номер_студента».
Процесс формирования пар строк путем сравнения содержимого
соответствующих столбцов называется объединением таблиц. Например:
SELECT Фамилия, Имя, Отчество, Адрес, Телефон;
FROM Студенты, Координаты WHERE Номер = Номер_Студента.
75
Таблица 6.2
Студенты
Номер
1
2
...
Фамилия
Имя
Отчество
...
...
...
Таблица 6.3
Координаты
Номер_студента
1
...
Адрес
Телефон
...
...
Здесь в условии поиска связываются соответствующие столбцы двух таблиц. Но связь «один к одному» является сравнительно
мало распространенной. Более распространена связь «один ко многим» (табл. 6.4; 6.5).
Очевидно, столбец «Номер_студента» является внешним ключом в таблице «Оценки».
SELECT Фамилия, Оценка FROM Студенты, Оценки
WHERE Номер = Номер_Студента AND Предмет = ’
Физика’.
Данные можно читать не только из двух, но и большего числа таблиц. Например, если есть три связанные таблицы (табл. 6.6–6.8).
Таблица «Дипломники» содержит два внешних ключа.
Таблица 6.4
Студенты
Номер
1
2
...
Фамилия
Имя
Отчество
Таблица 6.5
Оценки
Номер_студента
1
2
...
76
Предмет
Оценка
Таблица 6.6
Студенты
Номер_студента
1
2
...
Фамилия_студента
Имя
....
Таблица 6.7
Преподаватели
Номер_преподавателя
Фамилия_преподавателя
1
2
...
Таблица 6.8
Дипломники
Студент
Руководитель
1
..
Тема_диплома
Пример запроса к этим таблицам:
SELECT Фамилия_студента, Тема_диплома, Фамилия_руководителя FROM Студенты, Преподаватели, Дипломники
WHERE (Номер_Студента = Студент) AND
(Номер_преподавателя = Руководитель) AND
(Тема_диплома LIKE ‘Систем%’).
В SQL нет жесткого требования, чтобы все таблицы были связаны отношением «предок-потомок». Любые два столбца в двух таблицах могут быть связанными, если они имеют общие типы данных. Например, пусть имеются две таблицы (табл. 6.9; 6.10).
Таблица.6.9
Студенты
Фамилия
Дата_рождения
Таблица 6.10
Праздники
Дата
Праздник
77
SELECT Фамилия, Праздник
FROM Студенты, Праздники
WHERE Дата_рождения = Дата.
Можно считать, что в этом примере реализовано отношение
«многие ко многим», если считать, что на один день приходится
несколько праздников.
Объединение таблиц возможно не только по равенству ключевых полей или значений столбцов, но и по неравенству. Например:
SELECT DISTINCT Фамилия FROM Студенты, Оценки
WHERE Оценка > 4.
Таким образом, можно узнать, кто получил в сессию пятерки.
В приведенных примерах имена столбцов уникальные, но вообще таблицы могут содержать одинаковые имена столбцов – в этом
случае необходимо указывать их полные имена. Например:
SELECT Студенты.*, Тема_диплома FROM Студенты, Дипломники
WHERE Номер_Студента=Студент.
Здесь выбираются все поля таблицы «Студенты».
В языке SQL нет ограничений на количество таблиц, участвующих в запросе, но конкретные программные продукты чаще всего
позволяют указывать до 8-ми таблиц в одном запросе.
Объединение таблиц в запросе является подмножеством декартова произведения таблиц (при указании условий объединения),
т. е. условие WHERE позволяет отсеять ненужные записи.
Оператор WHERE оставляет в запросе только те строки, для которых условие истинно. Если же для строки условие имеет значение FALSE или NULL, то она отбрасывается. Поэтому в запросах
на объединение возможны ситуации, когда данные «пропадают».
Например, если создать запрос:
SELECT Фамилия_студента, Тема_диплома
FROM Студенты, Дипломники,
а некоторые студенты еще не имеют темы диплома, то мы получим
полный список студентов, но некоторые строки будут иметь значение
NULL в столбце «Тема_диплома». Потери информации не происходит.
Рассмотрим другой запрос:
SELECT Фамилия_студента, Фамилия_руководителя
FROM Студенты, Преподаватели, Дипломники
WHERE (Номер_Студента=Студент) AND (Номер_преподавателя=Руководитель).
78
Если, допустим, в группе из 20 студентов 4 еще не имеют руководителя, то получим только 16 записей.
При необходимости сохранения информации в запросе на объединения можно использовать так называемое внешнее объединение
таблиц (ранее рассмотренные объединения были внутренними).
6.7. Внешнее объединение таблиц
Рассмотрим две простые таблицы (табл. 6.11; 6.12).
Внутреннее объединение таблиц выполняется, например, запросом:
SELECT * FROM Студенты, Студентки;
WHERE Студенты.Хобби = Студентки.Хобби.
В итоге выполнения запроса получится табл. 6.13.
Таблица 6.11
Студенты
Фамилия
Иванов
Петров
Сидоров
Волков
Кошкин
Хобби
Рыбалка
Охота
NULL
Рыбалка
Чтение
Таблица 6.12
Студентки
Фамилия
Семенова
Липунова
Зайцева
Гусева
Медведева
Хобби
Вязание
NULL
Вышивание
Рыбалка
Охота
Таблица 6.13
Запрос
Студенты.
Фамилия
Иванов
Петров
Волков
Студенты. Хобби
Рыбалка
Охота
Рыбалка
Студентки.
Фамилия
Гусева
Медведева
Гусева
Студентки. Хобби
Рыбалка
Охота
Рыбалка
79
Сидоров и Липунова не имеют общего хобби, потому что значение NULL не равно никакому другому значению, даже значению
NULL.
Если же мы хотим вывести список значений студентов и студенток, имеющих одно и то же хобби, включая тех, у кого нет пары, то
необходимо внешнее соединение таблиц.
SELECT * FROM Студенты, Студентки;
WHERE Студенты.Хобби *=* Студентки.Хобби.
Здесь *=* означает внешнее соединение (так обозначается в SQL
Server).
В итоге получается табл. 6.14.
Очевидно, первые три строки таблицы такие же, как при внутреннем объединении таблиц. Затем идут две несвязанные строки таблицы «Студенты» и три несвязанные строки таблицы «Студентки».
Таким образом, внешнее объединение таблиц сохраняет информацию. В рассмотренном примере внешнее объединение симметричное (*=*). Возможно также левое и правое внешнее объединение таблиц (*= и =*).
Левое внешнее объединение включает все несвязанные строки
первой (левой) таблицы с расширением NULL, но не включает несвязанные строки второй (правой) таблицы. Для последнего примера это означает, что останутся первые пять строк.
Правое внешнее объединение, наоборот, не включает несвязанные строки первой (левой) таблицы, но включает несвязанные
строки второй (правой) таблицы с расширением NULL. Для последнего примера это означает, что останутся первые три и последние
три строки.
Таблица 6.14
Итоговая таблица
Студенты.
Фамилия
Иванов
Петров
Волков
Кошкин
Сидоров
NULL
NULL
NULL
80
Студенты.
Хобби
Рыбалка
Охота
Рыбалка
Чтение
NULL
NULL
NULL
NULL
Студентки.
Фамилия
Гусева
Медведева
Гусева
NULL
NULL
Семенова
Липунова
Зайцева
Студентки.
Хобби
Рыбалка
Охота
Рыбалка
NULL
NULL
Вязание
NULL
Вышивание
Внешнее объединение можно использовать не только с оператором =, но и с другими операторами сравнения (*>=).
В СУБД Oracle и SQL Base используются несколько иные обозначения. Левое внешнее объединение: WHERE Col1 = Col2(+). Правое
внешнее объединение: WHERE Col1(+) = Col2 (т. е. + находится на
другой стороне). Oracle и SQL Base не поддерживают полное внешнее объединение.
6.8. Итоговые запросы на чтение
Предыдущие запросы на чтение могут быть названы детальными,
они выводят информацию, используя отдельные записи.
В противоположность этому, существуют запросы, в которых
нужно вывести всего несколько значений, но обработав большое
количество записей. Такие запросы называют итоговыми. Для реализации этих запросов служат агрегатные (статистические) функции. Всего их шесть:
1) AVG( ) – вычисление среднего всех значений в столбце;
2) SUM( ) – сумма всех значений столбца;
3) MIN( ) – максимальное значение в столбце;
4) MAX( ) – минимальное значение в столбце;
5) COUNT( ) – подсчитывает количество значений в столбце;
6) COUNT(*) – подсчитывает количество строк в таблице результатов запроса.
Например, чтобы узнать среднюю оценку, можно использовать
запрос:
SELECT AVG(Оценка) FROM Оценки.
При использовании операторов AVG и SUM данные в столбце
должны содержать числовой тип.
При использовании функций MIN и MAX столбец может содержать не только числовые данные, но и строковые данные, а также
данные в формате «Дата/время».
Если используются строки, то результат операций MIN и MAX
может зависеть от используемых в компьютере таблиц кодировки (в коде ASCII цифры идут раньше букв, а прописные – раньше
строчных).
Пример использования функции COUNT:
SELECT COUNT(Фамилия) FROM Студенты WHERE Рост > 190.
Очевидно, тип данных, с которым работает COUNT, может быть
произвольным.
81
Использование COUNT(*) позволяет сделать запрос более удобочитаемым. Кроме того, функция COUNT( ) игнорирует значение
NULL (так же как AVG( ), MAX( ), MIN( ), SUM( )), а функция
COUNT(*) позволяет учесть значения NULL.
Функция COUNT( ) возвращает значение 0, если в таблице все
строки с NULL.
Функция COUNT(*) возвращает значение 0, если в таблице
строк нет.
С помощью ключевого слова DISTINCT можно перед применением к столбцу агрегатной функции удалить из него все повторяющиеся значения.
Например, для подсчета количества различных имен студентов
можно использовать запрос:
SELECT COUNT(DISTINCT Имя) FROM Студенты.
Иногда полезно использовать при выполнении запроса промежуточные итоги, а не только окончательные итоги. Это можно сделать, используя предложение GROUP BY. Например:
1) если мы хотим узнать среднюю оценку студентов:
SELECT AVG(Оценка) FROM Оценки;
2) если мы хотим узнать среднюю оценку для каждого студента:
SELECT Фамилия, AVG(Оценка) FROM Студенты, Оценки
GROUP BY Студенты.
Здесь групповые итоги подводятся по множеству предметов.
6.9. Условия поиска групп
Предложение WHERE используется для отбора отдельных
строк в запрос. Для отбора групп строк можно использовать предложение HAVING.
Например, если мы хотим узнать, какова средняя оценка студента из числа тех студентов, у кого средняя оценка больше четырех, то можно использовать запрос:
SELECT Фамилия, AVG(Оценка) FROM Студенты,Оценки
GROUP BY Студенты HAVING AVG(Оценка) > 4.
Сначала в этом запросе происходит разделение оценок на группы по студентам, затем предложение HAVING исключает все группы, где средний балл меньше 4, потом происходит подсчет среднего
балла для оставшихся групп.
82
На практике предложение HAVING всегда должно включать
хотя бы одну агрегатную функцию, потому что иначе его можно заменить на WHERE, т. е. HAVING вычисляется не для отдельных
строк, а для групп строк.
Как правило, HAVING используется совместно с GROUP BY, но
это не обязательно, и если этого нет, то считается, что группа состоит из всех строк.
6.10. Вложенные запросы на чтение
Механизм вложенных запросов позволяет использовать результаты одного запроса в качестве составной части другого запроса. Отсюда и пошло название «структурированный язык запросов». При
использовании этого механизма облегчается написание операторов
SELECT, поскольку весь запрос разбивается на части (собственно
запрос и вложенные запросы).
Вложенным, или подчиненным, называется запрос, содержащийся в предложении WHERE или HAVING другого оператора
SQL. Например:
SELECT Фамилия FROM Студенты
WHERE Рост > (SELECT AVG(Рост) FROM Студенты).
Таким образом, можно узнать фамилии студентов, рост которых
выше среднего. Другой пример:
SELECT Фамилия FROM Студенты
WHERE Номер = (SELECT Номер_студента FROM Оценки
WHERE (Предмет=’Физика’) AND (Оценка<4)).
Таким образом можно узнать фамилии студентов, которые плохо
знают физику.
При помощи ключевого слова IN можно проверить равенство
значения с группой значений, возвращаемых вложенным запросом. Пусть мы хотим получить список дипломников преподавателя
Иванова:
SELECT Фамилия FROM Студенты
WHERE Номер_студента IN
(SELECT Студент FROM Преподаватели, Дипломники
WHERE (Фамилия_преподавателя = ’Иванов’) AND
(Номер_преподавателя = Руководитель)).
Вложенный запрос возвращает номера дипломников Иванова,
а основной запрос проверяет, какие фамилии соответствуют этому
множеству номеров.
83
Если мы знаем номер Иванова в графе «Руководитель» таблицы
«Дипломники», то запрос упрощается:
SELECT Фамилия FROM Студенты
WHERE Номер_студента IN
(SELECT Студент
FROM Дипломники WHERE Руководитель = 10).
При помощи ключевого слова EXISTS можно узнать, содержится
ли во вложенном запросе (т. е. его результате) хотя бы одна строка.
Например:
SELECT Фамилия FROM Студенты
WHERE EXISTS
(SELECT Номер_Студента FROM Оценки WHERE Номер =
Номер_Студента AND Оценка=2).
Таким образом можно узнать, есть ли хотя бы один студент, получивший в сессию двойку.
Можно также использовать ключевые слова ANY и ALL.
В проверке ANY проверяется условие «любой» или «хотя бы
один».
Допустим, имеются две таблицы – 6.15; 6.16.
Если мы хотим получить список студентов, которых нельзя отнести к числу великих людей, то необходим запрос:
SELECT Фамилия FROM Студенты
WHERE IQ > ANY (SELECT IQ FROM Великие_люди).
Таблица 6.15
Великие_люди
Номер
Фамилия
IQ
1
2
3
Горбачев
Ельцин
Буш
62
25
48
Таблица 6.16
Студенты
84
Номер
Фамилия
IQ
1
2
3
Иванов
Петров
Сидоров
50
60
90
Если же мы хотим получить список наиболее умных студентов,
то получается:
SELECT Фамилия FROM Студенты
WHERE IQ > ALL (SELECT IQ FROM Великие_люди).
Итак: вложенный запрос является запросом внутри запроса. Во
вложенном запросе можно использовать внешние ссылки на таблицы любого запроса, внутри которого он находится. Такая ссылка
связывает вложенный запрос с «текущей» строкой данного запроса. Если вложенный запрос содержится в предложении WHERE, то
его результат используется для отбора отдельных строк, иначе, при
использовании HAVING, его результаты используются для отбора
группы строк.
Вложенные запросы могут иметь несколько уровней вложенности.
6.11. Целостность данных в SQL
В реляционных БД несколько условий целостности:
1) обязательность данных: реализуется при создании таблиц
оператором CREATE TABLE указанием NOT NULL. Если таблица
уже создана, то изменить это условие нельзя;
2) проверка на правильность: у каждого столбца таблицы есть
свой домен, т. е. тип данных, а может быть и диапазон допустимых
значений. Тип вводимых данных контролируется при выполнении
INSERT и UPDATE. В СУБД SQL Server, например, пользователь
может определять правила проверки вводимых данных, но в языке
SQL такие правила не описываются;
3) целостность таблицы: обеспечивается уникальностью первичного ключа для каждой записи. Можно также объявить условие уникальности данных (UNIQUE) в операторе CREATE TABLE.
Такие столбцы должны быть объявлены как NOT NULL;
4) ссылочная целостность: внешний ключ должен соответствовать значению какого-либо первичного ключа (недопустима ситуация, например, когда мы хотим добавить значение оценки для студента, номера которого нет в таблице «Студенты»).
В отличие от первичных ключей, внешние ключи в реляционных БД могут содержать значение NULL.
Ссылочная целостность обеспечивается правилами RESTRICT и
CASCADE;
5) деловые правила: позволяют учитывать дополнительные
ограничения на информацию, обрабатываемую БД. Например,
85
если в таблице описываются заказы на товар, то можно запретить
прием заказов, если их объем превысит наличие товара на складе.
В стандарте SQL написание этих правил осуществляется при помощи прикладных программ. Но в СУБД Sybase (а позднее и в других СУБД) появилось понятие триггер, которое позволило включать деловые правила в саму СУБД. С любым событием, которое
может вызвать изменение таблицы, пользователь может связать
действие. Под действием понимается последовательность операторов SQL.
Триггеры являются мощным механизмом в СУБД, но их использование несет и ряд трудностей, связанных с усложнением логики
работы БД.
Общий вид триггера:
CREATE TRIGGER имя_триггера ON имя_таблицы
FOR оператор AS действие;
6) непротиворечивость: в БД могут происходить несколько изменений одновременно, но так, чтобы в каждый момент времени
информация оставалась непротиворечивой. Эта задача реализуется
с помощью механизма обработки транзакций.
Транзакция – это несколько последовательных операторов SQL,
которые рассматриваются как одно целое (либо все операторы будут выполнены успешно, либо ни один из них не будет выполнен).
В SQL обработка транзакций реализована с помощью двух операторов: COMMIT и ROLLBACK.
Оператор COMMIT сообщает об успешности окончания транзакции. Он информирует СУБД о том, что все операторы выполнены успешно и противоречия в БД не возникли. Изменения в БД
становятся постоянными.
Оператор ROLLBACK сообщает о неуспешном окончании транзакции. Он информирует СУБД о том, что пользователь не хочет
завершать транзакцию. СУБД должна отменить все изменения,
внесенные в БД при выполнении транзакции. Возможность отката
обеспечивается за счет информационной избыточности.
Операторы COMMIT и ROLLBACK редко используются в интерактивном режиме, так как в интерактивном режиме чаще всего применяются запросы на чтение, а не на изменение. Во многих
интерактивных СУБД по умолчанию установлен режим «автоматического выполнения», когда оператор COMMIT автоматически
выполняется после каждого оператора SQL, вводимого пользователем, т. е. каждый оператор является отдельной транзакцией.
86
6.12. Обеспечение безопасности БД
Можно выделить четыре задачи, возникающие при обеспечении
целостности БД:
1) нужно разрешать доступ к конкретной таблице только определенным пользователям;
2) нужно разрешать изменять данные в таблице только определенным пользователям, а всем остальным – доступ только для чтения;
3) нужно иметь возможность делать доступными только отдельные столбцы таблицы;
4) работать с таблицей с помощью интерактивного SQL нужно
разрешать только отдельным пользователям, остальные должны
использовать прикладные программы (т. е. ограниченный набор
возможностей).
В SQL имеются два принципа защиты данных:
1) действующими лицами в БД являются пользователи. Все операции в БД происходят от их имени. Действие может быть выполнено или нет в зависимости от привилегий пользователя. Каждый
пользователь имеет свой идентификатор;
2) объектами защиты в БД могут быть таблицы и представления, а также формы, прикладные программы и БД в целом.
В БД существуют системный каталог – совокупность специальных таблиц, которые автоматически генерируются при создании
БД и хранят всю информацию, необходимую для ее функционирования. Например, если необходимо выполнить двухтабличный
оператор SELECT, то БД должна проверить:
1) существуют ли указанные таблицы;
2) есть ли у пользователя права доступа к каждой из таблиц;
3) существуют ли столбцы, указанные в запросе, к каким таблицам следует отнести неполные имена столбцов, определить, правильно ли обрабатываются данные с учетом их типа.
Системные таблицы доступны для чтения пользователям, хотя
администратор БД может ограничить к ним доступ. Важное достоинство системного каталога заключается в возможности простого
построения программ формирования запросов для неподготовленных пользователей. Это происходит примерно так же, как в системе ACCESS, когда пользователь при формировании запроса на
чтение выбирает таблицы, столбцы в них и условия отбора записей
при помощи мыши, а его действия автоматически преобразуются в
операторы SQL.
87
Системных таблиц обычно бывает 5 групп:
1) таблицы: описание всех таблиц БД, их имен, владельцев,
числа столбцов, их типа и т. п.;
2) столбцы: описание всех столбцов БД, т. е. имени столбца,
имени таблицы, к которой он принадлежит, типа данных, разрешены ли значения NULL и т. п.;
3) пользователи: в каталоге описывается каждый зарегистрированный пользователь БД, его имя, пароль и т. п.;
4) представления: описывается каждое представление в БД, его
имя, имя владельца, запрос, на основании которого оно построено
и т. д.;
5) привилегии: приводятся имена тех, кто предоставил привилегии, и тех, кому они предоставлены, указываются сами привилегии, объект их действия.
Для представления пользователю привилегий используется оператор GRANT. Например, для предоставления пользователю Иванову права на выборку и добавления данных в таблицу «Студенты»
можно применить такую конструкцию:
GRANT SELECT, INSERT ON Студенты FROM Иванов.
Оператор REVOKE, напротив, отменяет привилегии, ранее присвоенные пользователю. Он имеет следующий вид:
REVOKE SELECT, INSERT ON Студенты FROM Иванов.
Если пользователь сам создал таблицу, то он автоматически получает все привилегии, а другие пользователи могут получить права доступа к таблице только с помощью GRANT.
Если же создается представление с помощью оператора CREATE
VIEW, то здесь все привилегии автоматически могут не быть обеспечены, так как какие-то таблицы могут быть защищены от изменения. С помощью представлений можно выбирать только отдельные строки или столбцы исходных таблиц.
Оператор GRANT может использоваться в расширенном виде.
Например, если преподаватель Иванов создал таблицу «Оценки»,
то он может дать Петрову право пользоваться этой таблицей:
GRANT SELECT ON Оценки TO Петров.
Но если Петров захочет дать эту таблицу Сидорову, то у него ничего не выйдет: он не владелец таблицы. Если Иванов хочет, чтобы
Петров стал источником данных для других пользователей, он может использовать конструкцию:
88
GRANT SELECT ON Оценки TO Петров WITH GRANT OPTION.
Обратная операция, т. е. отмена привилегий, требует указания
способа отмены для других пользователей. При этом используются
ключевые слова RESTRICT и CASCADE. Например:
REVOKE SELECT ON Оценки TO Петров CASCADE.
Таким образом, отменяется доступ для Петрова и всех, кто получил доступ от него. Если же использовать конструкцию
REVOKE SELECT ON Оценки TO Петров RESTRICT,
то оператор REVOKE выполнится неуспешно в том случае, если
имеются привилегии, производные от удаляемой.
В стандарте SQL2 оператор может использоваться более детально:
REVOKE GRANT OPTION FOR SELECT ON Оценки
FROM Петров CASCADE.
Таким образом, отменяется право Петрова на предоставление
доступа другим, хотя сам он права доступа не теряет.
6.13. Программный SQL
Как уже отмечалось, существуют две версии SQL:
1) автономный (интерактивный);
2) встроенный (программный).
Для выяснения отличий встроенного SQL от автономного рассмотрим порядок выполнения операторов SQL. Он включает 5 этапов:
1) синтаксический анализ – проверка правильности составления оператора;
2) проверка правильности параметров – есть ли в БД указанные
таблицы, есть ли привилегии доступа к ним;
3) оптимизация оператора SQL, т. е. поиск наиболее быстрого
варианта выполнения оператора (сначала объединить таблицы, а
потом искать данные, или наоборот, и т. п.);
4) генерация плана выполнения оператора, т. е. набора инструкций для ЭВМ;
5) собственно выполнение оператора.
Наиболее трудоемким является третий этап – оптимизация.
При использовании интерактивного SQL миновать стадию оптимизации нельзя. Пользователь должен ждать, пока СУБД обработает
его запрос (без оптимизации в сложных случаях это ожидание может быть намного большим).
89
Программный SQL позволяет существенно сократить эти издержки, так как оптимизация происходит не во время выполнения, а во время компиляции.
Рассмотрим особенности программного SQL. При его использовании операторы SQL вставляются в исходный текст программы на
обычном языке программирования (C, Pascal и т. д.). Перед компиляцией исходный текст программы подается на вход специального препроцессора SQL, который преобразует текст SQL в набор
команд для СУБД. Таким образом, операторы SQL используются
для пересылки данных из БД в программу.
Программный SQL использует несколько основных принципов:
1) операторы SQL смешиваются с операторами базового языка
программирования (C, Pascal и т. д.) в исходном тексте программы;
2) встроенные операторы могут ссылаться на переменные базового языка программирования, т. е. SQL может использовать данные из программы, а программа может обрабатывать полученные с
помощью SQL данные;
3) для обеспечения построчной обработки результатов запроса
во встроенный SQL добавляется несколько новых операторов, которых нет в интерактивном SQL.
Процесс компиляции программы, использующей встроенный
SQL, показан на рис. 6.1.
Препроцессор – это специальная программа, которая просматривает исходную программу и обрабатывает встроенные операторы
SQL, а также функции синтаксического анализа и оптимизации.
Для каждого языка нужен свой препроцессор.
На выходе препроцессора получаются два файла. Под блоком
«Текст без SQL» понимается текст программы, в котором все операторы SQL заменены на вызовы библиотечных функций для работы с БД.
Компилятор на схеме показан обычный, он не имеет отношения
к СУБД.
Компоновщик создает исполняемую программу.
Препроцессор создает также модуль запросов к БД. Этот модуль
содержит копии всех операторов БД, встроенных в программу. Затем модуль поступает в специальную программу BIND. Эта программа исследует операторы SQL, анализирует, проверяет и оптимизирует их и для каждого оператора создает план исполнения.
В итоге получается объединенный план выполнения для всех операторов.
Таким образом, получается два исполняемых компонента: исполняемая программа и модуль запросов. Использование модуля запро90
Исходный текст
программы
Препроцессор
Библиотека
СУБД
Текст
без SQL
Модуль
запросов к БД
Компилятор
BIND
Объектный
код
План
выполнения
Компоновщик
Исполняемая
программа
БД
Рис. 6.1. Компиляция программы со встроенным SQL
сов обеспечивает переносимость приложений. При запуске программы оба компонента работают совместно. Происходит это так:
1) исполняемая программа загружается обычным способом, и
начинается выполнение ее команд;
2) если встречается библиотечная функция СУБД, то происходит запрос соответствующего оператора в плане выполнения. СУДБ
выполняет эту часть плана и возвращает управление программе.
Операторы SQL в программе начинаются со специального спецификатора. В большинстве версий SQL он имеет вид:
EXEC SQL < операторы SQL >.
Затем идет ограничитель, например, END SQL.
Если в итоге выполнения запроса получается набор строк, т. е.
таблица, то программный SQL должен обеспечить для прикладной
программы возможность построчного чтения этой таблицы. Для
этого вводится понятие курсора.
91
Под курсором понимается указатель набора записей. Вводится также несколько новых SQL-операторов, которых нет в автономном SQL:
1) DECLARE CURSOR – определяет запрос и связывает с ним
имя курсора;
2) OPEN – дает команду СУБД на начало выполнения запроса
и создание таблицы результатов запроса. Курсор устанавливается
перед первой строкой таблицы результатов;
3) FETCH – продвигает курсор на первую строку таблицы результатов и считывает данные в базовую переменную прикладной
программы. Последующие операторы FETCH считывают данные
строка за строкой;
4) CLOSE – прекращает доступ к таблице результатов и ликвидирует связь между курсором и этой таблицей.
Таким образом, понятие курсора похоже на понятие имени файла
в обычных языках программирования, но открытие курсора – более
трудоемкая операция, так как приводит к выполнению запроса.
Программа может открыть несколько курсоров и обрабатывать
несколько таблиц результатов запросов параллельно.
Более подробное рассмотрение этих вопросов требует привлечения прикладных программ (текстов) на каком-либо языке программирования.
Таким образом, с помощью встроенного SQL можно заранее описать схему доступа к данным в БД и жестко описать ее в программе.
Такой вариант программного SQL называется статическим.
Существует также динамический программный SQL. Его использование необходимо в тех случаях, когда невозможно заранее
предусмотреть все операции доступа к данным и запросы должны
формулироваться динамически.
Динамический SQL отличается тем, что встроенный оператор
SQL не записывается в исходный текст программы. Вместо этого программа формирует текст оператора во время выполнения в
одной из своих областей данных, а затем предает этот оператор в
СУБД для выполнения «на ходу».
Динамический SQL менее производителен, чем статический.
6.14. Функционирование распределенных БД
На большом предприятии данные могут храниться в разнообразных вычислительных системах, объединенных в глобальную сеть,
которая может включать несколько локальных вычислительных
сетей из множества персональных компьютеров.
92
Возможны ситуации, когда вычислительная сеть включает разнородное оборудование (например, компьютеры IBM PC и
Macintosh используют разную кодировку чисел). На отдельных
уровнях вычислительной сети могут использоваться разные СУБД.
На персональных ЭВМ – Access, Paradox и т. д, на сервере – SQL
Server и т. п.
Поэтому к распределенной БД предъявляется ряд требований:
1) БД должна быть «прозрачна» для пользователя. Независимо
от реального нахождения данных, они должны быть для конкретного пользователя как бы локальными;
2) СУБД должна работать с любыми данными для любой компьютерной платформы;
3) логика работы СУБД должна быть одинакова в сети любой
конфигурации;
4) целостность и безопасность данных не должны зависеть от
структуры сети.
Однако в настоящее время СУБД не вполне удовлетворяют этим
требованиям по ряду причин:
1) производительность работы локальных сетей, из которых состоит распределенная СУБД, ограничена (хотя и непрерывно растет).
Поэтому доступ к удаленным данным не может быть мгновенным;
2) обеспечение целостности данных при распределенных транзакциях требует использования специальных протоколов, что также вносит задержки;
3) обычные правила оптимизации для операторов SQL в сети
не вполне справедливы. Например, полное сканирование (просмотр) локальной таблицы может оказаться более эффективным,
чем поиск по индексу в удаленной таблице. При оптимизации нужно учитывать параметры сети (быстродействие).
В целом распределенные БД в своем развитии прошли 4 этапа:
1) удаленный запрос: здесь отдельный оператор SQL обращается
к одной удаленной БД. Каждый оператор представляет собой транзакцию;
2) удаленная транзакция: отдельный оператор или группа операторов SQL выполняются для одной удаленной БД. Здесь пользователь передает в СУБД последовательность операторов, а СУБД гарантирует, что они будут выполнены как единое целое. Например,
SQL Server поддерживает механизм удаленных транзакций, когда
пользователь работает на персональном компьютере, а БД расположена на сервере («клиент – сервер»). Часто удаленные транзакции
реализуются с помощью специальных шлюзовых программ;
93
3) распределенная транзакция: каждый оператор транзакции обращается к одной удаленной БД, но транзакция в целом может обращаться к нескольким БД. СУБД должна гарантировать целостность
выполнения транзакции. Реализуется распределенная транзакция
с помощью механизма двухфазного выполнения. СУБД SQL Server
поддерживает этот механизм, но его реализация достаточно трудна;
4) распределенный запрос: отдельный оператор SQL может обращаться к нескольким удаленным БД. Транзакция из нескольких операторов может обращаться к нескольким БД. На этом уровне предъявляются жесткие требования к программам оптимизации операторов SQL. Здесь требуется учитывать скорость передачи данных в
сети. Необходимо решать, какая именно СУБД будет выполнять оператор SQL. Это очень сложные проблемы, поэтому в настоящее время
этот уровень полностью еще не реализован ни в одной СУБД.
Таким образом, реализация распределенных запросов характеризует тенденции развития СУБД.
Рассмотрим подробнее механизм двухфазного выполнения распределенных транзакций. Этот механизм полностью скрыт от программиста, однако его понимание позволяет лучше представить
работу БД.
Рассмотрим БД, состоящую из трех частей – А, В и С. Пользователь находится в системе А, ему необходимо добавить данные в
систему С и обновить данные в системе В.
При однофазном выполнении распределенной транзакции характерны две ситуации:
1) пользователь выполнил необходимые операции, послал
COMMIT в системы В и С и получил подтверждение. Эту ситуацию
иллюстрирует рис. 6.2;
2) пользователь выполнил необходимые операции, послал
COMMIT в системы В и С, но получил подтверждение только от одной из систем по причине какого-либо рода ошибки (рис. 6.3).
Система В
Система А
UPDATE
INSERT
UPDATE
Система С
INSERT
COMMIT
COMMIT
OK
(завершение)
COMMIT
OK
Рис. 6.2. Корректное выполнение однофазной транзакции
94
Система В
Система А
Система С
UPDATE
COMMIT
INSERT
UPDATE
COMMIT
INSERT
ERROR
COMMIT
OK
(завершение)
NO
Рис. 6.3. Некорректное выполнение однофазной транзакции
Система В
UPDATE
GET READY
GET READY
Система С
INSERT
UPDATE
INSERT
COMMIT
YES
ROLLBACK
ROLLBACK
Система А
OK
GET READY
ROLLBACK
NO
(отмена)
ERROR
ROLLBACK
OK
Рис. 6.4. Двухфазное выполнение транзакции
В этом случае из-за ошибки в системе С транзакция пользователя, состоящая из двух операторов, оказалась частично незавершенной. В системе С изменений не произошло, а в В они произошли и их уже нельзя отменить. Это может нарушить логику работы
СУБД. Двухфазное исполнение позволяет избежать такого рода
ошибок (рис. 6.4).
При двухфазном исполнении до завершения COMMIT система А
посылает дополнительное сообщение GET READY в системы В и С.
В свою очередь, системы В и С сообщают о своей готовности к завершению транзакции. Если хотя бы одна из систем не готова, то
транзакция отменяется. Таким образом, двухфазное исполнение
гарантирует целостность распределенных транзакций, но при его
исполнении растет нагрузка на сеть.
Вопросы для самопроверки
1. Сколько существует версий языка SQL и какие это версии?
2. Какую логику использует язык SQL?
3. Что означает логическое значение UNKNOWN?
95
4. Что обозначает метка NULL?
5. Какие основные задачи решает язык SQL?
6. Сколько операторов и ключевых слов использует язык SQL?
7. Какие операторы SQL используются для обработки данных?
8. Какие операторы SQL используются для определения данных?
9. Какие операторы SQL используются для управления доступом?
10. Какие операторы SQL используются для управления транзакциями?
11. Какие операторы использует программный SQL?
12. Какие типы данных использует язык SQL?
13. Каким образом создаются таблицы в SQL?
14. Как выполняется модификация таблиц?
15. Как описывается первичный ключ таблицы?
16. Как описывается внешний ключ таблицы?
17. Что понимается под синонимом в SQL?
18. Что понимается под представлением в SQL?
19. Какой оператор является самым мощным в SQL?
20. Назовите основные функции запросов.
21. Что является результатом запроса в SQL?
22. Как исключить повторения в результатах запроса?
23. Какие условия поиска можно использовать при выполнении запросов?
24. Может ли запрос возвращать вычисляемые столбцы?
25. Каким образом можно сортировать результаты запроса в SQL?
26. Какие операторы SQL служат для внесения изменений в БД?
27. Что такое внутреннее связывание таблиц в SQL запросе?
28. Зачем используется внешнее объединение таблиц?
29. Что такое детальные запросы на чтение?
30. Что такое итоговые запросы на чтение?
31. Какие агрегатные функции могут быть использованы в итоговом запросе на чтение?
32. Зачем используются условия поиска групп?
33. Как выполняются вложенные запросы на чтение?
34. Какие условия целостности поддерживает SQL?
35. Что понимается под транзакцией?
36. Какие операторы SQL служат для обработки транзакций?
37. Как обеспечивается безопасность БД в SQL?
38. Как используется программный SQL?
39. Какие специальные операторы содержит программный SQL?
40. Чем отличается динамический программный SQL от статического?
41. Какие требования предъявляются к распределенной БД?
42. Какие этапы прошли распределенные БД в своем развитии?
43. Что такое двухфазное выполнение транзакций?
96
ГЛАВА 7. ПЕРСПЕКТИВЫ РАЗВИТИЯ БД
И ОБРАБОТКА ЗНАНИЙ
7.1. Перспективы развития СУБД
Несмотря на все свои достоинства, реляционные СУБД имеют
ряд ограничений. Они идеально походят для таких традиционных
приложений, как системы резервирования билетов или мест в гостиницах, а также банковских систем, но их применение в системах автоматизации проектирования, интеллектуальных системах
обучения и других системах, основанных на знаниях, часто является затруднительным.
Первое из ограничений заключается в том, что в нетрадиционных приложениях в базе данных появляются сотни таблиц, над
которыми постоянно выполняются трудоемкие операции соединения, необходимые для воссоздания сложных структур данных,
присущих предметной области.
Другим серьезным ограничением реляционных систем являются
их относительно слабые возможности в описании семантики приложения, поскольку реляционные СУБД обеспечивают лишь возможности
формулирования и поддержки ограничений целостности данных.
С теоретической точки зрения, более широкими возможностями, чем реляционные БД, обладают объектно-ориентированные
БД. В таких БД поддерживаются следующие основные принципы:
1) в качестве значения столбца отношения разрешается указывать кортеж произвольного пользовательского отношения;
2) инкапсуляция: данные и процедуры манипуляции данными
позволяют пользователю присоединять к отношению процедуры,
определяемые значениями столбцов;
3) наследование (данных, столбцов);
4) элементами отношений являются множества, а не только одиночные элементы;
5) домен атрибута может иметь произвольный тип;
6) свойства отношений расширяются: к атрибутам и ограничениям добавляются методы;
7) любое отношение расширяется системным атрибутом, уникальным идентификатором объекта. Идентификатор сохраняется
как значение атрибута класса, домен которого – произвольный класс;
8) классы организуются в иерархию;
9) разрешается рекурсивно наследовать свойства класса всем
его подклассам. Унаследованные свойства можно модифицировать
в подклассах;
97
10) допускается доступ к атрибутам и методам класса посредством сообщений, использующих явно определенный набор интерфейсов класса;
11) связи между файлами могут храниться непосредственно
в БД – записи могут указывать на записи.
Однако объектно-ориентированные БД пока не получили широкого распространения.
Для обеспечения расширенных возможностей реляционных БД
разрабатываются новые подходы, некоторые из которых приведены ниже.
Виртуализация ресурсов. В настоящее время в большинстве организаций для каждого приложения выделяется своя группа компьютеров и свой набор дисков для хранения БД. В реальной жизни
нагрузка на приложения постоянно изменяется, и возникает ситуация, когда одни компьютеры сильно перегружены, в то время как
другие недогружены. Решением является виртуализация ресурсов.
При этом все диски объединяются в один большой виртуальный
диск, на котором располагаются все данные всех приложений, что
позволяет на ходу увеличивать виртуальный диск, добавляя к нему
новые физические диски. Аналогично происходит объединение
множества компьютеров в один виртуальный компьютер, на котором одновременно работает множество приложений, мощность которого можно легко увеличивать, добавляя новые компьютеры, и
возможности перераспределения вычислительных ресурсов между
приложениями по мере необходимости позволяют резко повысить
гибкость вычислительной системы, снизить ее стоимость и увеличить эффективность использования оборудования.
Управление циклом жизни информации. Информация имеет
свой цикл жизни. Сначала она поступает в систему и является активной, к ней нужно обеспечить быстрый доступ. Затем информация начинает устаревать, переходит в разряд менее активной, и ее
можно переместить на более медленные носители. Затем информация переходит в разряд исторической, и здесь можно использовать
сжатие данных. Поддержание жизненного цикла информации, как
правило, сегодня реализуется на уровне приложений. Это сложно,
негибко, усложняет приложения и снижает качество управления
информацией. Поэтому сегодня наметилась тенденция реализации
механизма информационного хранения на уровне самой СУБД. Администраторы или разработчики описывают различные области
хранения данных с разными характеристиками и правила разделения данных одного типа на группы (например, по мере устаре98
вания). Группы привязываются к областям хранения, и при изменении характеристик данных они автоматически перемещаются в
другую область. При этом меняются характеристики этих данных.
Самоуправление и самодиагностика. Усложнение СУБД требует все более опытных администраторов для их обслуживания.
Однако человек может испытывать трудности в условиях постоянно меняющейся нагрузки СУБД, изменения режима работы приложений и т. д. Единственное решение этих проблем – сделать СУБД
самоуправляемыми. Основная идея здесь проста. Во время работы
СУБД постоянно собирает информацию о своей работе, анализирует,
принимает решения и либо автоматически их реализует, либо информирует администратора о проблеме, рекомендует ему последовательность действий для решения проблемы и после одобрения
выполняет их. В фоновом режиме СУБД ищет и исправляет сбойные блоки и файлы, сверяет контрольные суммы и т. д. Продолжается совершенствование оптимизаторов SQL-запросов, они учитывают все больше важных факторов.
Автоматическое тестирование изменений. В процессе работы
приходится постоянно менять параметры работы СУБД и структуры
данных, создавать новые объекты в БД, менять архитектуру вычислительной системы, выполнять рекомендации службы технической
поддержки, опытных коллег, руководства и т. д. Но любые из этих
действий могут привести к ухудшению или остановке работы. Единственная возможность решить эту проблему – позволить заранее протестировать любые из перечисленных изменений на тестовой системе
и оценить последствия. Для этого используется тестовая копия БД.
Включение измерения времени в СУБД. Для борьбы с человеческими ошибками, аудита, а также для восстановления старых
версий данных и отчетов надо иметь в СУБД возможность «откатываться» в прошлое. Часто требуется быстро и просто «откатить»
всю базу, отдельные транзакции или восстановить удаленные объекты БД и ошибочно измененные пользователем данные. Иногда
надо посмотреть, как изменялись данные в течение времени. Для
выполнения этих операций в СУБД встраиваются измерение времени и механизм для быстрого воссоздания старого состояния базы,
объекта или запроса.
Поддержка новых типов данных. По мере развития информационных технологий появляются приложения, работающие с новыми типами данных. Сегодня большинство СУБД умеет работать
не только с алфавитно-цифровой информацией, но и с текстами,
аудио, видео, XML.
99
Совершенствование механизмов сжатия. Для разных данных
должны автоматически применяться различные алгоритмы сжатия или не применяться вообще. Администратор должен иметь
возможность выбирать различные уровни сжатия. Сжатие должно
быть прозрачно для приложения.
Традиционные алгоритмы сжатия позволяют сжать данные
в 2 раза. Некоторые специфические виды сжатия позволяют увеличить степень сжатия на порядок, при этом сильно замедляется
выполнение операций с данными. Администратор должен иметь
возможность для различных данных, различных таблиц и даже
частей таблиц выбирать те варианты сжатия и хранения, которые
наиболее подходят с точки зрения их использования. Этот подход
будет очень полезен пользователям больших БД, вынужденным постоянно увеличивать свои системы хранения.
Совершенствование методов защиты данных. Очевидно, что
средства защиты данных от несанкционированного доступа очень
важны и будут продолжать совершенствоваться. В последнее время
появилась тенденция выноса механизма управления пользователями из отдельных СУБД и приложений в единую централизованную
систему организации, что упрощает управление пользователями в
масштабах организации и позволяет управлять ими в автоматическом режиме. Кроме того, интенсивно совершенствуются средства
защиты данных внутри БД. Сегодня администратор может видеть
и изменять все данные в БД. Новые средства защиты дают возможность ему выполнять все операции по администрированию БД, не
позволяя ему видеть и менять данные. Также ему можно будет
ограничить набор разрешенных операций.
Облачные вычисления (Cloud Computing). Сегодня начинают бурно развиваться так называемые облачные вычисления. Эта технология очень привлекательна для пользователей, так как они легко
и недорого могут запросить через Интернет и получить во временное
пользование некоторый сервис для хранения и обработки данных.
Например, можно заказать компьютер с заданными параметрами
и работать с ним из своего офиса, хотя физически и оборудование,
и программное обеспечение располагаются и обслуживаются удаленно. Примерами Cloud Computing являются сервисы хранения,
данных и вычислительные сервисы. СУБД должны уметь работать
в такой среде и использовать ее для создания и хранения файлов.
Работа в среде Cloud Computing предъявляет повышенные требования к СУБД с точки зрения защиты данных и разграничения
доступа.
100
Машины баз данных. В последнее время широко развивается
направление хранилищ данных. Поскольку объемы данных огромны и продолжают расти, а специальные алгоритмы традиционных
СУБД уже не могут обеспечить хорошее время отклика для задач
анализа и построения корпоративной отчетности, появляются специальные вычислительные системы с массивно-параллельной архитектурой. Такие архитектуры позволяют распараллелить обработку данных и вынести часть обработки с уровня СУБД на уровень
ячеек хранения, которые сами имеют свои небольшие компьютеры.
Обычно при росте объема БД проблемным местом становятся каналы передачи данных между сервером БД и устройствами хранения. Возможность увеличить количество и пропускную способность
таких каналов, а также снизить объем передаваемых данных за
счет выполнения части операций по просмотру и выборке данных на
уровне ячеек хранения позволяет снять проблему каналов передачи.
Таким образом, перспективные СУБД должны опираться на более совершенную элементную базу, иметь более совершенную программную организацию, обладать гибкими и удобными интерфейсами для программистов, пользователей и администратора БД.
В современных условиях появляется потребность в обеспечении информационного обслуживания мобильного пользователя.
Теперь нужно иметь возможности ведения БД как на центральной
вычислительной системе, так и на портативном компьютере. При
этом необходимо иметь средства загрузки/выгрузки выбранных
данных с центральной в портативную ЭВМ, а также средства обеспечения согласованности информации в обеих базах.
7.2. Обработка знаний
В настоящее время существует множество БД, которые очень полезны для учета кадров, продукции, бухгалтерских задач и т. п. Но
эти задачи относятся к числу рутинных (операционных), они соответствуют уровню работников низшего звена – клерков, продавцов.
Руководители среднего и высшего звена крупного предприятия
или фирмы решают качественно иные задачи. Им необходимо организовывать аналитическую обработку информации для решения
таких проблем, как:
1) определение и анализ тенденций;
2) анализ возможностей;
3) анализ конкурентоспособности и т. д.
Системы поддержки принятия решения (СППР) должны обеспечивать возможность анализа зависимостей между различными
101
параметрами, которые могут варьироваться в широких пределах.
Например, зависимость объема продаж от региона, времени, категории товара и т. п.
Представление данных в виде таблиц реляционной БД не совсем
удобно для целей анализа. Основоположник реляционной модели
Ф. Кодд указал в этой связи на невозможность «объединять, просматривать и анализировать данные с точки зрения множественности измерений, т. е. самым понятным для аналитиков способом».
Измерение – это последовательность значений одного из анализируемых параметров. Например, для параметра «время» – это
последовательность календарных дней, для параметра «регион» –
список городов, для параметра «категория товара» – соответственно список товаров.
Множественность измерений предполагает представление данных в виде многомерной модели. По измерениям в многомерной
модели откладывают параметры, относящиеся к анализируемой
предметной области.
Введенное Ф. Коддом понятие многомерного концептуального
представления обозначает множественную перспективу, состоящую из нескольких независимых измерений, вдоль которых могут
быть проанализированы определенные совокупности данных. Соответственно, многомерный анализ – это одновременный анализ по
нескольким измерениям.
Каждое измерение может быть представлено в виде иерархической структуры. Например, измерение «Исполнитель» может
иметь иерархические уровни: «Предприятие – подразделение – отдел – служащий».
На пересечении осей измерений располагаются данные, количественно характеризующие анализируемые меры. Таким образом, многомерную модель можно представить в виде гиперкуба
(рис. 7.1).
Над гиперкубом могут выполняться следующие операции:
1) срез – подмножество многомерного массива данных, соответствующее единственному значению одного или нескольких элементов измерений, не входящих в это подмножество. Если рассматривается трехмерная модель, то срез представляет собой двухмерную
проекцию куба;
2) вращение – изменение положения измерений. Например, перестановка местами строк и столбцов таблицы;
3) консолидация и детализация. Консолидация – переход от
детализированного представления к агрегированному, детализа102
Измерение 1
Измерение 2
Измерение 3
Мера
(ячейка)
Рис. 7.1. Многомерная модель данных
ция – обратная операция. Например, при рассмотрении измерения
«Регион» детальное представление может рассматривать отдельные города, а агрегированное – субъекты федерации.
Извлечение знаний из данных. Многомерный анализ может
быть полезен при построении гипотез о существующих зависимостях в предметной области, однако в общем случае требуется автоматизация процессов обработки в СППР.
Задача выявления скрытых зависимостей в разнородной операционной информации часто трактуется как задача извлечения знаний из данных. В англоязычной литературе используется словосочетание «data mining», т. е. «разработка данных». Этот термин
подчеркивает, что данные и знания находятся в таком же соотношении, как нефть и продукт ее перегонки – бензин.
Операционные данные, которые могут быть использованы для
извлечения знаний в СППР, можно разделить на несколько групп:
1) примитивные операционные данные без временной привязки
(где?, что? и сколько?);
2) предметно-ориентированные данные с историей их применения во времени. Эти данные управляются запросами, ориентированы на проведение анализа и могут носить уникальный характер;
3) события, т. е. целая совокупность данных, связанных общей
целью. Например, событие «выпуск нового изделия»;
4) данные, получаемые с помощью моделирования.
Можно сказать, что обычные БД направлены на объекты деятельности – товары, деньги, комплектующие и т. д., а СППР
103
работают с объектами, необходимыми для анализа, планирования
и принятия решений. СППР должна учитывать недостоверность и
разрозненность информации.
Если рассматривать уровень крупного предприятия, то здесь
есть отдельные работники – кассир, бухгалтер, начальник отдела
кадров. Каждый работник может работать со своей БД. Разные БД
могут быть организованы по-разному.
Современная методика крупных информационных систем
предполагает создание так называемых хранилищ данных (Data
Warehouse) – зависящая от времени коллекция данных, предназначенная для принятия управленческих решений (рис. 7.2).
Хранилище данных собирает операционные данные, согласовывает их и объединяет в предметно-ориентированный формат. Эти
данные не предназначены для модификации, и новые данные там
не создаются.
Предметная ориентация означает, что данные объединены в категории и хранятся в соответствии с теми объектами, которые они
описывают, а не в соответствии с источниками их получения.
Информация в хранилище данных оптимизирована для быстрого выполнения запросов и ориентирована на принятие решений,
которые способны повлиять на решение стратегических задач (рост
прибыли, расширение рынка и т. п.).
Под извлечением знаний понимается поиск значимых для пользователя закономерностей, а также формирование решений как
цепочки шагов, направленных на достижение поставленной цели.
Такая цепочка имеет вид: данные (наборы чисел и символов) 
информация (описание закономерностей)  знания (значимые для
пользователя закономерности)  решения (цепочка шагов для достижения поставленной цели).
Источник
данных 1
Извлечение
данных
Хранилище
данных
Извлечение
знаний
Хранилище знаний
Источник
данных N
Рис. 7.2. Процесс извлечения знаний из данных
104
Существует несколько методов для выявления и анализа знаний:
1) ассоциация – если несколько событий связаны друг с другом.
Например, если при покупке ПК в магазине 75 % покупателей покупают еще и принтер, то можно продавать их вместе с некоторой
скидкой и тем самым увеличить размер продаж. Таким образом,
здесь речь идет о выявлении корреляций между различными атрибутами элементов данных;
2) классификация: например, в бизнесе – это выявление потенциально неустойчивых клиентов и принятие мер для их закрепления;
3) кластеризация – выявление групп клиентов, близких по какому-либо признаку. Например, на какие группы делятся клиенты
банка? Какая группа наиболее стабильна? Каковы черты типичного представителя каждой группы?;
4) прогнозирование – оценивание будущих значений процессов,
а также оценивание вероятностей гипотез. При этом могут использоваться качественные понятия нечеткой логики.
Системы извлечения знаний должны уметь обрабатывать большое количество неструктурированной текстовой информации.
Средства извлечения знаний – это нейронные сети и нечеткие
системы, а также статистические методы и средства визуализации
данных и процессов.
При классификации и кластеризации данных используются
нейронные сети Кохонена и Гроссберга. Сеть Кохонена считалась
единственным примером алгоритма автоматической классификации, но сейчас известны новые более мощные комбинированные
средства (алгоритм, лежащий в основе пакета CubiCalk).
Наиболее сложные пакеты (например, фирмы Prism Nestor)
включают все известные виды интеллектуальных программ: экспертные системы, нечеткую логику, нейронные сети, теорию хаоса,
что позволяет функционировать в условиях неполноты и противоречивости информации.
Основными потребителями систем обработки знаний являются
военно-промышленные комплексы, а также банковские и финансовые структуры. Есть не только дорогие пакеты, но и пакеты, доступные массовому пользователю.
Например, система IDIS (фирмы Intelligence Ware): она используется, в частности, Федерацией атлетов США для выявления факторов, влияющих на спортивные показатели. Основное назначение
пакета IDIS – построение корреляционных зависимостей между
элементами данных при отсутствии первоначальных гипотез. Пакет использует оригинальный алгоритм многомерного анализа и
105
строит наборы правил типа: «Если а принадлежит А, а в принадлежит В, то х принадлежит Х с вероятностью Р». Входные данные
для пакета представляются в форматах Dbase или Paradox или любой БД, способной обрабатывать SQL-запросы.
Сообщается об успешном использовании IDIS в медицинской
диагностике, менеджменте и для анализа краткосрочных займов.
При большом количестве записей БД ресурсов персонального
компьютера оказывается недостаточно, и требуется использовать
многопроцессорные рабочие станции.
7.3. Экспертные системы
Экспертная система (ЭС) – это программный продукт, манипулирующий знаниями для принятия эффективных решений в достаточно узкой предметной области (ПО).
К основным видам деятельности ЭС относят следующие:
интерпретация (например, интерпретация информации от
датчиков для описания состояния объекта);
прогнозирование – определение вероятного будущего состояния объекта по его текущему состоянию;
диагностика – выявление причин неправильного функционирования по результатам наблюдения;
планирование – определение последовательности действий
для достижения заданного результата;
мониторинг – сравнение результатов наблюдения с контрольными точками плана;
комплексные задачи управления и обучения.
В настоящее время известны успешные примеры использования ЭС в самых различных областях. Например, в медицине ЭС
оказывают помощь в диагностике и лечении инфекционных заболеваний, а также при обучении врачей. В геологии ЭС служат для
интерпретации данных геологоразведки с целью выявления возможных месторождений полезных ископаемых.
Большое количество ЭС были разработаны для военных приложений. Известно, что несколько ЭС было разработано по заказу Министерства обороны США для целеуказания и распределения боевых
средств. Задача заключается в нанесении противнику наибольшего
урона при имеющихся ограничениях. Необходимо оптимально распределить средства поражения по целям. При этом возникает очень
большое число вариантов. Например, для поражения авианосца противника можно использовать или подводную лодку, или бомбарди106
ровщики, или ракеты. Можно использовать и то, и другое, и третье,
но при этом не будут поражены другие цели. Кроме того, такая ЭС
должна оперативно обрабатывать огромное количество оперативной информации, поступающей от спутников и радиолокационных
станций. Эти задачи очень сложны для человека, и использование
ЭС позволяет резко сократить время принятия решений.
Острую необходимость в привлечении методов ИИ (искусственный интеллект) испытывает современная боевая и гражданская
авиация. Здесь можно выделить несколько сложных задач:
автоматическая диагностика всех систем самолета;
обработка разнородной навигационной информации, поступающей от датчиков разного типа, и представление ее в форме, наиболее удобной для человека;
планирование боевой задачи и определение тактики ведения
боя. В сложной боевой обстановке экипаж может оказаться не в
состоянии контролировать поток поступающей информации. ЭС
должна выполнять сжатие информации и представление ее в форме, удобной для восприятия;
контроль самочувствия летчика. При выполнении фигур высшего пилотажа летчик может из-за перегрузок временно терять
сознание. Существуют ЭС, которые отслеживают такие ситуации
по частоте мигания глаза летчика, которую контролирует специальный датчик. Как только возникает угрожающая ситуация, ЭС
отдает управление автопилоту.
Конечно, все эти задачи могут решаться одновременно. Возможны разные варианты организации взаимодействия нескольких ЭС.
Или они могут выдавать сообщения пилоту независимо, или ЭС может строиться иерархически.
Например, в России на истребителе СУ-37 применяются двигатели с поворотными соплами, что позволяет выполнять уникальные маневры. Для управления полетом разработана цифровая система управления с элементами ИИ. Без нее летчик не мог бы вести
воздушный бой. В кабине летчика имеется специальное табло отказов, которое не только сообщает о неисправностях, но и выдает
рекомендации по их устранению.
Бомбардировщик СУ-34 оснащен системой активной безопасности с элементами ИИ. Она позволяет отслеживать рельеф местности и выполнять фигуры высшего пилотажа на малых высотах
и максимальной скорости (до 1400 км/ч). Благодаря этому степень
заметности самолета для ПВО противника примерно такая же, как
у крылатой ракеты.
107
В настоящее время выделяют два поколения ЭС, или статические и динамические (гибридные) ЭС.
Системы 1-го поколения – это статические ЭС, в которых исходная информация не меняется в процессе решения задачи. Например, при медицинской диагностике набор симптомов служит для
вывода решения о заболевании пациента, и здесь может быть использована статическая ЭС.
Системы 2-го поколения – это динамические ЭС, в которых информация может меняться в процессе решения задачи. Здесь необходимо учитывать фактор времени при принятии решения, а также иметь модель динамики предметной области.
Таким образом, если ЭС 1-го поколения являются особого рода программами, то ЭС 2-го поколения представляют собой сложные программные комплексы, тесно взаимодействующие с аппаратурой.
Структура ЭС 1-го поколения включает в себя базу знаний, рабочую память, механизм вывода и интерфейс пользователя.
База знаний (БЗ) аккумулирует сведения о предметной области. Механизм вывода обеспечивает обработку знаний. Интерфейс
пользователя обеспечивает представление знаний в форме, удобной
для человека.
Рабочую память называют также базой данных (но это не то же
самое, что реляционная база данных); она хранит исходные и промежуточные данные решаемой задачи.
Хотя все компоненты ЭС функционально важны, довольно давно замечено, что мощность ЭС обуславливается, в первую очередь,
мощностью БЗ и возможностями ее модификации, и лишь во вторую очередь – используемым механизмом вывода.
Базе знаний можно поставить в соответствие долговременную
память человека, а рабочая память (база данных) соответствует
кратковременной памяти, в которой находятся данные, получаемые с помощью органов чувств.
Механизм вывода ЭС (или интерпретатор правил) просматривает рабочую память, может добавлять в нее новые факты, а также вызывать срабатывание того или иного правила. Как правило,
в ЭС информация из рабочей памяти последовательно сопоставляется с посылками всех правил БЗ. Если сопоставление оказывается успешным для нескольких правил, то образуется конфликтное
множество, из которого интерпретатор правил по тому или иному
принципу выбирает одно правило.
Механизм вывода ЭС – это небольшая по объему подпрограмма.
108
При заполнении фактами рабочей области ЭС вступает в диалог с пользователем. Обычно этот диалог элементарен, и, отвечая
на вопросы ЭС, пользователь может односложно говорить «да» или
«нет». Но некоторые ЭС используют развитые средства общения на
естественном языке, которые применяют грамматический разбор
ответов пользователя.
В качестве инструмента разработки ЭС могут быть использованы:
универсальные процедурные языки программирования (Паскаль, Си и др.);
языки программирования искусственного интеллекта (Пролог, Лисп и др.);
специализированные средства автоматизации проектирования и модификации ЭС;
«пустые» ЭС, которые могут работать с БЗ конкретной архитектуры, но относящейся к произвольной ПО (так как методы работы со знаниями в значительной степени инвариантны по отношению к ПО).
В разработке ЭС могут принимать участие несколько человек:
эксперт в ПО, который может знать все о ПО, но ничего об ЭС
и компьютерах;
инженер по знаниям (когнитолог), выбирающий инструмент
для разработки ЭС, определяющий способ представления знаний и
программирующий механизм вывода решений;
программист, который квалифицированно создает все остальные компоненты ЭС, обеспечивая ее функционирование в конкретной операционной системе.
На практике все эти функции может совмещать один человек,
но следует помнить, что разработка ЭС может требовать нескольких человеко-лет.
Для описания знаний в ЭС чаще всего используются правила, и
ЭС переживает несколько стадий существования:
демонстрационный прототип, призванный показать применимость технологии ЭС в данной ПО, опираясь на 50–100 правил БЗ;
исследовательский прототип, решающий все требуемые задачи, но требующий проверки и отладки. Он имеет 200–500 правил;
действующий прототип с 500–1000 правилами, работа которого проверена, но не вполне приемлема по некоторым показателям
(например, времени выработки решений);
промышленная стадия существования ЭС, когда она готова
к работе и полностью удовлетворяет своих разработчиков;
109
коммерческая стадия, когда ЭС удовлетворяет достаточный
контингент потенциальных потребителей.
Гибридные ЭС. В последние годы все большее распространение
получают динамические ЭС, обрабатывающие оперативную информацию в ходе вывода заключений. Их называют также интегрированными (гибридными), или активными ЭС, или ЭС реального
времени. Это интеллектуальные системы весьма высокого уровня
сложности, использующие разнообразные, в том числе динамические, модели предметной области.
Выделяют три разновидности ЭС реального времени:
1)«псевдослучайного времени», когда обрабатывается медленно меняющаяся информация;
2)«мягкого» реального времени, когда реакция должна измеряться десятыми долями секунды;
3)«жесткого» реального времени, когда реакция должна измеряться миллисекундами.
Первые ЭС реального времени появились в середине 80-х годов
прошлого века. Рассмотрим некоторые известные приложения:
промышленные ЭС для управления технологическими процессами, в частности, в металлургической промышленности, на
цементных заводах;
ЭС NASA/SPACE Shuttle (США) – управление 38 двигателями
космического корабля «Шаттл», для чего обрабатывается информация от 16 000 датчиков;
ЭС Camunsa (Испания) – автоматизированный интеллектуальный гараж в Барселоне, разработанный к Олимпиаде 1992 г. Как
сообщается, гараж не требует присутствия людей и позволяет разместить 800 машин на пространстве, где раньше помещалось не более 300 машин;
ЭС IBM MOM – система контроля за выпуском печатных плат
для повышения производительности и окупаемости;
ЭС Forsmark Nuclear Plant (Швеция) – система обеспечения безопасности и моделирования событий для ядерной электростанции;
ЭС для управления производственными процессами в самой
большой частной пекарне США (Mrs. Baird’s Bekery).
В деле создания гибридных ЭС есть мировой лидер – американская фирма Gensym, которая выпустила инструментальное средство –
систему G2 в 1988 г., нашедшее удачные применения в ряде областей.
В частности, упомянутая система Space Shuttle, а также система управления станциями слежения за спутниками построены на базе G2.
110
Как и всякая сложная система, G2 сочетает в себе многие механизмы и подходы. В частности, для представления данных и знаний широко используется объектно-ориентированный подход с механизмами наследования и инкапсуляции.
Разработчик может использовать правила, которые могут быть
общими для целого класса объектов или относиться к конкретному
экземпляру объекта.
Правило может активизироваться различными способами:
когда изменяются условия, для которых правило должно срабатывать;
когда правило вызывают другие правила.
Это обычные для ЭС способы, кроме них, могут также использоваться другие способы, например:
правило может активизироваться через заданный временной
интервал;
правило возбуждается, если пользователь внес какие-то изменения на экране дисплея;
если изменилась структура объектов в задаче (действиями
пользователя или иным путем) и др.
Пользователь имеет в своем распоряжении Паскале-подобный
язык для представления процедурных знаний. Он имеет специфические особенности для работы в реальном времени, такие как
оператор ожидания наступления события, оператор разрешения/
запрещения прерывания данной процедуры, операторы задания
параллельной или последовательной обработки и т. д.
Система имеет максимально дружественный интерфейс, который основывается на использовании нескольких текстовых и графических редакторов.
Текстовый редактор построен таким образом, что исключается
ввод синтаксически неправильных конструкций. Система на каждом шаге подсказывает, что конкретно она ждет от пользователя.
Графический интерфейс позволяет пользователю получить наглядное представление о процессе, с которым идет работа. При этом
возможна не только мультипликация, похожая на реальный процесс,
но и произвольная абстракция (когнитивная компьютерная графика).
Для описания знаний используется графическое представление
объектов и связей между ними.
Важная особенность G2 заключается в ее переносимости с одной
компьютерной платформы на другую. Достигается это за счет того,
что БЗ хранится в виде обычного текстового файла (ASCII-код), который понимают все компьютеры.
111
Сообщается, что в настоящее время все 25 самых крупных компаний мира используют G2. Всего продано более 5000 копий. Помимо упомянутых приложений в космосе, могут быть названы такие области, как нефтяная промышленность, финансовая система,
медицина, судостроение, сельское хозяйство, оборона и т. д.
В любом приложении система призвана сохранять и использовать знания наиболее талантливых и квалифицированных сотрудников в интеллектуальных системах реального времени, что должно повысить качество принимаемых решений, увеличить безопасность и надежность.
Одно из главных достоинств G2 заключается в возможности объединить уже существующие средства автоматизации производства
в комплексную систему, охватывающую весь цикл производства.
Для хранения знаний предусмотрены два типа файлов:
1) базы знаний (БЗ);
2) библиотеки знаний (БиЗ).
База знаний относится к конкретному приложению, храня определение его объектов, правил, процедур, функции, действия и т. п.
Библиотека знаний содержит общие знания для более чем одного приложения и хранит общие правила.
Знания разбиваются на систему классов, модулей и рабочих пространств.
Понятие класса базируется на объектно-ориентированной технологии. Все объекты, с которыми работает система, являются экземплярами того или иного класса. Обычно каждому объекту соответствует объект реального мира в данном приложении.
Модуль – это часть приложения, реализованного на базе G2. БЗ
состоит из нескольких модулей. Это позволяет параллельно работать нескольким группам разработчиков.
Рабочие пространства используются при работе приложения,
позволяя эффективно использовать рабочую память и вводить
ограничения на доступ пользователя к системе. Рабочее пространство определяет независимый сегмент БЗ, который может быть активирован или деактивирован.
Рассмотрим подробнее понятие объекта. Обычно каждому объекту соответствует объект реального мира в данном приложении.
Между объектами может быть два вида взаимосвязи: связи и отношения.
Связь имеет графическое представление, ей может соответствовать, например, электрическая связь или трубопровод. Связи вводятся при разработке ЭС.
112
Отношения устанавливаются динамически после выполнения
правил или процедур ЭС.
Объекты имеют атрибуты, т. е. существенные в данной задаче
свойства объекта, описанные в таблице атрибутов объекта. Элемент таблицы представляет собой пару «атрибут – значение».
Различают простые атрибуты, а также переменные и параметры.
Переменная – это измеряемая характеристика объекта реального мира, поэтому она имеет такие специфические черты, как время
жизни и источник данных. Источником данных для переменной
могут быть механизм вывода ЭС или внешний источник данных.
С переменными могут быть ассоциированы алгоритмы имитационного моделирования, после применения которых и получаются
переменные. Для параметров последний способ не применяется.
При ссылке в правиле или процедуре на переменную или параметр можно использовать не только текущее значение, но также:
– значение в заданный момент времени;
– среднее значение за заданный временной интервал;
– интеграл по заданному интервалу времени;
– max или min значение на интервале;
– отклонение, скорость и другие производные от переменной
значения.
Нечеткие логические переменные являются в G2 одним из базовых типов данных, а нейронные сети используются для решения
задач многокритериального адаптивного управления.
Машина вывода выполняет рассуждения на основании знаний,
а также данных, поступающих от двух источников: подсистемы
имитационного моделирования и данных от внешних источников:
датчиков и т. п. Способы возбуждения правил описаны выше (всего
9 способов возбуждения правил).
В качестве действий, выполняемых правилом, могут быть:
– присвоение значения атрибуту, параметру или переменной;
– посылка информации внешнему объекту или оператору системы;
– запуск процедуры;
– создание или удаление экземпляра объекта;
– порождение или удаление задачи (подзадачи);
– управление графической информацией на экране дисплея;
– активизация группы правил.
Система реального времени должна реагировать на критические
изменения в окружающем мире или в ходе управляемого процесса.
Для этого введен специальный тип правил, которые начинаются с
113
ключевого слова «whenever» («как только»). Эти правила имеют
наибольшую степень важности.
Приложение на базе G2 управляет одновременно множеством
задач, поэтому G2 имеет компонент, называемый планировщиком.
Этот компонент напоминает операционную систему реального времени. Планировщик работает циклически, причем время каждого
цикла ограничено, с тем чтобы система никогда не «зависала».
В течение одного цикла планировщик формирует очередь текущих задач, обслуживает источники данных (не более 0,1 с на каждый), выполняет задачи из очереди задач (не более чем по 0,2 с на
каждую, если задача не успела выполниться, то она откладывается
на следующий цикл) и обслуживает сообщения из сети (обычно в
G2 более одного пользователя), после чего происходит переход к новому циклу и т. д.
Возможны самые различные конфигурации G2 на базе одной
или нескольких ЭВМ. Разработка приложений ведется на базе архитектуры «клиент – сервер».
Вопросы для самопроверки
1. Какие ограничения присущи реляционным БД?
2. На каких принципах строятся объектно-ориентированные БД?
3. В чем заключается принцип виртуализации ресурсов БД?
4. Как происходит управление циклом жизни информации?
5. Почему необходимы самоуправление и самодиагностика СУБД?
6. Зачем требуется автоматическое тестирование изменений в СУБД?
7. Почему необходимо включение измерения времени в СУБД?
8. В чем заключается задача совершенствования механизмов сжатия
информации?
9. Что такое облачные вычисления?
10. Что такое машины баз данных?
11. Какие цели преследует аналитическая обработка информации?
12. Что такое многомерная модель представления данных?
13. Какие операции можно выполнять над многомерной моделью в виде
гиперкуба?
14. Что такое разработка данных?
15. Какие группы данных используются для извлечения данных?
16. Что такое хранилище данных?
17. Какие методы используются для извлечения и анализа данных?
18. Что такое экспертная система?
19. В каких сферах деятельности применяют экспертные системы?
20. Для каких задач используют экспертные системы в военной сфере?
21. На какие поколения делятся экспертные системы?
22. Чем отличаются статические экспертные системы от динамических?
114
23. Из каких частей состоит статическая экспертная система?
24. Как описываются знания в экспертной системе?
25. Какие могут быть использованы инструменты для разработки статических экспертных систем?
26. Через какие стадии проходит разработка статических экспертных
систем?
27. Какие существуют разновидности экспертных систем реального
времени?
28. В каких сферах деятельности применяются экспертные системы реального времени?
29. Какие способы активизации правил используются в экспертных системах реального времени?
30. Как хранятся знания в экспертных системах реального времени?
31. Как описываются объекты реального мира в экспертных системах
реального времени?
32. Какие способы активизации правил возможны в экспертных системах реального времени?
33. Как работает планировщик в экспертных системах реального времени?
115
Заключение
Традиционная БД – компьютеризованная система ведения записей. Она может использоваться в этом базовом качестве для простых приложений или входить в состав сложнейших интеллектуальных систем обработки информации.
Перспективы развития информационных систем безграничны,
они постоянно расширяются вслед за задачами, которые возникают
в процессе развития человечества. Перечислим некоторые из них:
1) база данных системы наблюдения Земли. Их назначение – сбор
информации, необходимой для исследователей, занятых изучением
долгосрочных тенденций состояния атмосферы, океанов, земной поверхности. Информация в больших объемах поступает со спутников,
она должна интегрироваться с уже существующей информацией, а
также с данными из других источников (зарубежные спутники, наземные станции наблюдения) и накапливаться в базе данных;
2) электронная коммерция. Система электронной коммерции
предполагает сетевое взаимодействие огромного числа участников
торговых сделок. Такая система должна иметь высоконадежные средства распределенной аутентификации и перевода денежных сумм;
3) информационная система здравоохранения. Врачу необходим доступ к множеству источников информации. Например, истории болезни одного пациента могут находиться в разных больницах, клиниках, страховых учреждениях. Точно так же существует
множество систем и баз данных, предоставляющих информацию о
лекарствах, лечебных процедурах, диагностических средствах. Записи лечащего врача, результаты обследований, информация о счетах за лечение, договора медицинского страхования для каждого
пациента должны фиксироваться в электронной форме и оставаться доступными для последующего использования;
4) электронные публикации. Здесь подразумевается хранение
книг и статей в электронном виде и оперативная доставка их потребителям. Но само понятие публикации существенно расширяется – документ может являться гипертекстом, содержащим графические, аудио- или видео- включения. Происходит сближение
издательской и образовательной сфер. Лекций, читаемые для небольшого числа студентов, будут заменены на «образовательные
продукты» – электронные документы, состоящие из текстовых,
аудио-, видео- и других компонентов и включающие элементы интерактивного тренинга. Такой продукт сможет удовлетворить потребности огромного числа студентов;
116
5) коллективное проектирование. Сложные проекты, например, в области самолетостроения, реализуются объединенными
усилиями нескольких независимых компаний. Время жизни информации, относящейся к подобным проектам, может измеряться
десятилетиями, поскольку она необходима для поддержки, модификации и развития. Конструкторские решения, прежде чем стать
физической реальностью, могут проходить стадии компьютерного
моделирования. Эволюция конструкторских схем приводит к разрастанию информационной конфигурации, которая должна отражать текущее состояние разработки, экспериментальные версии,
историческое развитие. Для разных сфер конструирования характерно использование разнородных инструментальных систем, основанных на разных моделях и системах обозначений;
6) Интернет. Всемирная компьютерная сеть является средством реализации всех перечисленных выше задач, но она развивается и сама по себе, требуя все более совершенных механизмов,
как для хранения информации, так и для ее контекстного поиска
на узлах сети при нечетко заданных условиях.
Для углубленного ознакомления с проблематикой разработки
БД и интеллектуальных систем на их основе могут быть рекомендованы издания [1 – 12].
117
Рекомендуемая литература
1. Дейт К., Дж. Введение в системы баз данных. 6-е изд. К.; М.,
СПб.: Вильямс, 2000. 848 с.
2. Хансен Г., Хансен Д. Базы данных. Разработка и управление.
М.: Бином, 2000. 704 с.
3. Ульман Дж., Видом Дж. Введение в системы баз данных. М.:
Лори. 2000. 374 с.
4. Мейер М. Теория реляционных баз данных. М.: Мир, 1987. 608 с.
5. Райордан Р. Основы реляционных баз данных. М.: Русская
редакция, 2001. 384с.
6. Грабер М. Введение в SQL. – М.: Лори, 1996. 379 с.
7. Грабер М. Справочное руководство по SQL. М.: Лори, 1997. 291 с.
8. Хомоненко А. Д., Цыганков В. М., Мальцев М. Г. Базы данных. СПб.: КОРОНА-принт, 2002. 672 с.
9. Ланде Д. В. Поиск знаний в Internet. Профессиональная работа. М.: Вильямс, 2005. 272 с.
10. Барсегян А. А., Куприянов М. С., Степаненко В. В., Холод И. И. Методы и модели анализа данных: OLAP и Data Mining.
СПб.: БХВ-Петербург, 2004. 336 с.
11. Построение экспертных систем: пер. с англ. / под ред. Ф. Хейеса-Рота, Д. Уотермана, Д. Ленната. М.: Мир, 1987. 441 с.
12. Попов Э. В., Фоминых И. Б., Кисель Е. Б., Шапот М. Д. Статические и динамические экспертные системы. М.: Финансы и статистика, 1996. 320 с.
118
ОГЛАВЛЕНИЕ
Глава 1. Базы данных. Общие понятия .........................................
1.1. Задачи информационных систем ........................................
1.2. История развития баз данных ............................................
1.3. Локальные и серверные базы данных ..................................
1.4. Основные понятия реляционных баз данных ........................
1.5. Нормализация таблиц БД ..................................................
Вопросы для самопроверки..........................................................
Глава 2. Методология проектирования .........................................
2.1. Концептуальное проектирование баз данных........................
2.2. Логическое проектирование ...............................................
2.3. Физическое проектирование ..............................................
Вопросы для самопроверки..........................................................
Глава 3. Реляционная алгебра .....................................................
Вопросы для самопроверки..........................................................
Глава 4. Отношения и нормальные формы ....................................
4.1. Понятие домена ...............................................................
4.2. Понятие отношения ..........................................................
4.3. Потенциальные ключи ......................................................
4.4. Внешние ключи ...............................................................
4.5. Функциональные зависимости ...........................................
4.6. Нормализация отношений .................................................
Вопросы для самопроверки..........................................................
Глава 5. Требования к реляционной СУБД ....................................
Вопросы для самопроверки..........................................................
Глава 6. Язык SQL .....................................................................
6.1. Общие сведения ...............................................................
6.2. Определение структур данных в SQL ...................................
6.3. Работа с таблицами в SQL ..................................................
6.4. Запросы в SQL..................................................................
6.5. Внесение изменений в базы данных ....................................
6.6. Многотабличные запросы на чтение ....................................
6.7. Внешнее объединение таблиц .............................................
6.8. Итоговые запросы на чтение ..............................................
6.9. Условия поиска групп .......................................................
6.10. Вложенные запросы на чтение ..........................................
6.11. Целостность данных в SQL ...............................................
6.12. Обеспечение безопасности БД ...........................................
6.13. Программный SQL ..........................................................
6.14. Функционирование распределенных БД ............................
Вопросы для самопроверки..........................................................
Глава 7. Перспективы развития БД и обработка знаний..................
7.1. Перспективы развития СУБД .............................................
7.2. Обработка знаний .............................................................
7.3. Экспертные системы .........................................................
Вопросы для самопроверки..........................................................
Заключение ..............................................................................
Рекомендуемая литература .........................................................
3
3
6
10
13
18
22
24
24
30
34
35
37
46
47
47
47
48
49
50
51
57
59
63
64
64
66
67
70
74
75
79
81
82
83
85
87
89
92
95
97
97
101
106
114
116
118
119
Учебное издание
Бураков Михаил Владимирович
Латыпова Рамиля Рамисовна
БАЗЫ ДАННЫХ И ЯЗЫК SQL
Учебное пособие
Редактор В. А. Черникова
Компьютерная верстка С. Б. Мацапуры
Сдано в набор 30.06.14. Подписано к печати 17.11.14.
Формат 6084 1/16. Бумага офсетная. Усл. печ. л. 7,0.
Уч.-изд. л. 7,4. Тираж 100 экз. Заказ № 541.
Редакционно-издательский центр ГУАП
190000, Санкт-Петербург, Б. Морская ул., 67
Документ
Категория
Без категории
Просмотров
0
Размер файла
385 Кб
Теги
burakovlatipova
1/--страниц
Пожаловаться на содержимое документа