Краевое государственное автономное
профессиональное образовательное учреждение
�Пермский авиационный техникум им. А.Д. Швецова�
УЧЕБНОЕ ПОСОБИЕ
Самостоятельная подготовка к экзамену
по дисциплине
�БАЗЫ ДАННЫХ�
2018HYPER13 HYPERLINK \l "_Toc318194146" HYPER141. Этапы развития баз данных. Принципы их работы.HYPER13 PAGEREF _Toc318194146 \h HYPER144HYPER15
Базы данных. Предпосылки возникновения баз данных.HYPER13 PAGEREF _Toc318194147 \h HYPER144HYPER15
Основная терминология.HYPER13 PAGEREF _Toc318194148 \h HYPER145HYPER15
2. Архитектура баз данных. Процесс прохождения пользовательского запроса.HYPER13 PAGEREF _Toc318194149 \h HYPER148HYPER15
Процесс прохождения пользовательского запросаHYPER13 PAGEREF _Toc318194150 \h HYPER148HYPER15
Уровень внешних моделейHYPER13 PAGEREF _Toc318194151 \h HYPER148HYPER15
Концептуальный уровеньHYPER13 PAGEREF _Toc318194152 \h HYPER148HYPER15
База данныхHYPER13 PAGEREF _Toc318194153 \h HYPER148HYPER15
3. Пользователи баз данных.HYPER13 PAGEREF _Toc318194154 \h HYPER1410HYPER15
Администраторы данных и администраторы баз данныхHYPER13 PAGEREF _Toc318194155 \h HYPER1410HYPER15
Разработчики баз данных.HYPER13 PAGEREF _Toc318194156 \h HYPER1410HYPER15
ПользователиHYPER13 PAGEREF _Toc318194157 \h HYPER1411HYPER15
4. Модели данных. Классификация.HYPER13 PAGEREF _Toc318194158 \h HYPER1412HYPER15
Объектные модели данныхHYPER13 PAGEREF _Toc318194159 \h HYPER1412HYPER15
Модели данных на основе записейHYPER13 PAGEREF _Toc318194160 \h HYPER1412HYPER15
Физические модели данныхHYPER13 PAGEREF _Toc318194161 \h HYPER1413HYPER15
Концептуальное моделированиеHYPER13 PAGEREF _Toc318194162 \h HYPER1413HYPER15
5. Этапы разработки информационной структуры базы данныхHYPER13 PAGEREF _Toc318194163 \h HYPER1414HYPER15
6. Реляционная модель данных. Основные понятия и определения.HYPER13 PAGEREF _Toc318194164 \h HYPER1416HYPER15
Основные понятия и определения.HYPER13 PAGEREF _Toc318194165 \h HYPER1416HYPER15
Альтернативная терминологияHYPER13 PAGEREF _Toc318194166 \h HYPER1417HYPER15
Свойства отношенийHYPER13 PAGEREF _Toc318194167 \h HYPER1417HYPER15
Виды отношенийHYPER13 PAGEREF _Toc318194168 \h HYPER1417HYPER15
Основные виды связейHYPER13 PAGEREF _Toc318194169 \h HYPER1417HYPER15
7. Первичные и внешние ключи. Непротиворечивость и целостность данныхHYPER13 PAGEREF _Toc318194170 \h HYPER1418HYPER15
Средства поддержки целостности данных (см. DCL SQL и т.т.)HYPER13 PAGEREF _Toc318194171 \h HYPER1418HYPER15
8.Реляционная алгебраHYPER13 PAGEREF _Toc318194172 \h HYPER1420HYPER15
Выборка (или ограничение)HYPER13 PAGEREF _Toc318194173 \h HYPER1421HYPER15
ПроекцияHYPER13 PAGEREF _Toc318194174 \h HYPER1421HYPER15
Декартово произведениеHYPER13 PAGEREF _Toc318194175 \h HYPER1421HYPER15
ОбъединениеHYPER13 PAGEREF _Toc318194176 \h HYPER1421HYPER15
РазностьHYPER13 PAGEREF _Toc318194177 \h HYPER1421HYPER15
Операция соединенияHYPER13 PAGEREF _Toc318194178 \h HYPER1421HYPER15
ПересечениеHYPER13 PAGEREF _Toc318194179 \h HYPER1421HYPER15
ДелениеHYPER13 PAGEREF _Toc318194180 \h HYPER1421HYPER15
9.Принципы нормализации. Описание предметной области. Нормальные формы.HYPER13 PAGEREF _Toc318194181 \h HYPER1423HYPER15
10.Модель �Сущность-связь�. ER - модель. Типы связей: �один к одному�, �многие к одному�, �один ко многим�, �многие ко многим�HYPER13 PAGEREF _Toc318194182 \h HYPER1426HYPER15
11.Язык SQL, его достоинства. Классификация операторов SQLHYPER13 PAGEREF _Toc318194183 \h HYPER1428HYPER15
Успех SQL принесли следующие его достоинства:HYPER13 PAGEREF _Toc318194184 \h HYPER1428HYPER15
Классификация операторов SQLHYPER13 PAGEREF _Toc318194185 \h HYPER1429HYPER15
12.Типы данных SQL. Оператор выбора SELECTHYPER13 PAGEREF _Toc318194186 \h HYPER1433HYPER15
Скалярные операторыHYPER13 PAGEREF _Toc318194187 \h HYPER1435HYPER15
Оператор выбора SELECT. Формирование запросов из базы данныхHYPER13 PAGEREF _Toc318194188 \h HYPER1435HYPER15
Примеры запросовHYPER13 PAGEREF _Toc318194189 \h HYPER1438HYPER15
SELECT fam FROM kadr WHERE pol='M'HYPER13 PAGEREF _Toc318194190 \h HYPER1438HYPER15
13.Агрегатные функции, вложенные запросы в операторе выбора.HYPER13 PAGEREF _Toc318194191 \h HYPER1439HYPER15
TWorkHYPER13 PAGEREF _Toc318194192 \h HYPER1439HYPER15
14.Операторы манипулирования даннымиHYPER13 PAGEREF _Toc318194193 \h HYPER1442HYPER15
Команда INSERTHYPER13 PAGEREF _Toc318194194 \h HYPER1442HYPER15
Команда UPDATEHYPER13 PAGEREF _Toc318194195 \h HYPER1443HYPER15
Команда DELETEHYPER13 PAGEREF _Toc318194196 \h HYPER1443HYPER15
Работа с триггерамиHYPER13 PAGEREF _Toc318194197 \h HYPER1444HYPER15
15.Модели "Клиент-сервер" в технологии баз данныхHYPER13 PAGEREF _Toc318194198 \h HYPER1446HYPER15
Работа технологии "клиент-сервер"HYPER13 PAGEREF _Toc318194199 \h HYPER1447HYPER15
16.Модели транзакций. Свойства. Способы завершенияHYPER13 PAGEREF _Toc318194200 \h HYPER1449HYPER15
Поддержка транзакцийHYPER13 PAGEREF _Toc318194201 \h HYPER1449HYPER15
Улучшенные модели транзакцийHYPER13 PAGEREF _Toc318194202 \h HYPER1450HYPER15
Модель вложенных транзакцийHYPER13 PAGEREF _Toc318194203 \h HYPER1451HYPER15
Эмуляция механизма вложенных транзакций с помощью точек сохраненияHYPER13 PAGEREF _Toc318194204 \h HYPER1451HYPER15
ХроникиHYPER13 PAGEREF _Toc318194205 \h HYPER1452HYPER15
Модель многоуровневых транзакцийHYPER13 PAGEREF _Toc318194206 \h HYPER1452HYPER15
Динамическая реструктуризацияHYPER13 PAGEREF _Toc318194207 \h HYPER1453HYPER15
Модели рабочих потоковHYPER13 PAGEREF _Toc318194208 \h HYPER1454HYPER15
17.Журнал транзакций. Восстановление после сбоев.HYPER13 PAGEREF _Toc318194209 \h HYPER1455HYPER15
Назначение атрибутов пользователейHYPER13 PAGEREF _Toc318194210 \h HYPER1455HYPER15
Контроль сеансов доступа к даннымHYPER13 PAGEREF _Toc318194211 \h HYPER1455HYPER15
Уровни защиты БДHYPER13 PAGEREF _Toc318194212 \h HYPER1455HYPER15
Виды привилегийHYPER13 PAGEREF _Toc318194213 \h HYPER1455HYPER15
Привилегии доступа к объектамHYPER13 PAGEREF _Toc318194214 \h HYPER1456HYPER15
Методы восстановленияHYPER13 PAGEREF _Toc318194215 \h HYPER1456HYPER15
Метод восстановления с использованием отложенного обновленияHYPER13 PAGEREF _Toc318194216 \h HYPER1457HYPER15
Метод восстановления с использованием немедленного обновленияHYPER13 PAGEREF _Toc318194217 \h HYPER1458HYPER15
18.Защита информации в базах данныхHYPER13 PAGEREF _Toc318194218 \h HYPER1459HYPER15
Контрмеры – компьютерные средства контроляHYPER13 PAGEREF _Toc318194219 \h HYPER1459HYPER15
19.Архитектура СУБД. Перспективы развития баз данных и СУБДHYPER13 PAGEREF _Toc318194220 \h HYPER1461HYPER15
Традиционная двухуровневая архитектура "клиент-сервер"HYPER13 PAGEREF _Toc318194221 \h HYPER1461HYPER15
Трехуровневая архитектураHYPER13 PAGEREF _Toc318194222 \h HYPER1461HYPER15
СУБД для хранилища данныхHYPER13 PAGEREF _Toc318194223 \h HYPER1462HYPER15
Требования к СУБД для хранилища данныхHYPER13 PAGEREF _Toc318194224 \h HYPER1462HYPER15
Параллельные СУБДHYPER13 PAGEREF _Toc318194225 \h HYPER1464HYPER15
Интерактивная аналитическая обработка данных (OLAP)HYPER13 PAGEREF _Toc318194226 \h HYPER1464HYPER15
Литература, рекомендуемая при самоподготовкеHYPER13 PAGEREF _Toc318194227 \h HYPER1466HYPER15
HYPER151. Этапы развития баз данных. Принципы их работы.
1-й Этап. Файлы и файловые системы.
Важным шагом развития информационных систем является переход к использованию централизованных систем управления файлами.
2-й Этап. Базы данных на больших ЭВМ.
В 1968 году была введена в эксплуатацию 1-я промышленная СУБД.
Особенности: 1) Все СУБД базируются на мощных мультипрограммных ОС, поэтому в основном поддерживается работа с централизованными БД в режиме распределения доступа;
2) Функции управления распределения ресурсов в основном осуществляется ОС;
3) Поддерживает языки низкого уровня манипулирования данных;
4) Значительная роль отводится администрированию данными;
5) Проводятся теоретические работы по оптимизации запросов и управления распределения доступом в централизованную БД;
6) Вводится понятие трансакции – параллельное выполнение нескольких операций;
7) Результаты научных исследований открыто обсуждаются.
Результаты теоретических исследований активно внедрялись в коммерческие СУБД. Этот этап заканчивается тем, что появляются первые языки высокого уровня для работы с реляционной моделью данных.
3-й Этап. Эпоха ПК.
Особенности: 1) Все СУБД были рассчитаны на создание БД с монопольным доступом;
2) Большинство СУБД имели развитый и удобный пользовательский интерфейс. Существовал интерактивный режим работы с БД, как в рамках описания БД, так и в рамках проектных запросов. Кроме того, большинство СУБД предлагали развитый и удобный инструментарий для разработки готовых приложений.
3) Во всех СУБД поддерживался только внешний уровень представления реляционной модели, т.е. внешний вид структурных данных.
4) во многих СУБД отсутствовали средства поддержки ссылочной и структуры целостности БД.
5) Сравнительно скромные требования к железу со стороны СУБД.
4-й Этап. Распределение БД.
В этом этапе возникают задачи связанные с обработкой трансакции – последовательных операций БД, перевод ее из одного непротиворечивого состояния в другое.
Особенности:
1) Практически все СУБД обеспечивают поддержку полной реляционной модели:
структура целостности;
языковой целостности;
ссылочной целостности.
2) Многие СУБД могут работать на компьютерах с разной архитектурой и под разными ОС. При этом для пользователя доступ к данным практически неразличим.
3) Потребовалось развитие средств администрирования БД, с реализацией общей концепции защиты БД.
4) Появляются языки и стандарты этих языков SQL89 и SQL92.
Базы данных. Предпосылки возникновения баз данных.
Очевидно, что для таких целей больше всего подходят файлы со стандартным доступом (чтобы была возможность обрабатывать эти данные еще и другими программами). Чем больше у нас сохраняется данных тем, дольше доступ к ним, их бы еще и отсортировать и упорядочить. Все это стало предпосылкой разработки баз данных (БД) и системы управления базами данных (СУБД).
Другой предпосылкой создания БД считают необходимость таких хранилищ данных и математических алгоритмов, чтобы время обработки меньше всего не зависело от размерности задачи.
И, наконец, необходимость применения групповых операций или произвольных структурированных запросов сыграла немаловажную роль в создании и развитии БД.
Важнейшие требования к базам данных:
Способность накопления данных не должна быть ограничена количеством данных и слабо зависеть от способов их получения, а также объемов физических накопителей.
Должна обеспечиваться целостность данных и надежность их хранения.
Возможность выполнения с данными стандартных логических и реляционных операций.
Минимальный уровень избыточности.
Контроль непротиворечивости хранимой информации.
Эффективность использования ресурсов компьютера.
Использование стандартных механизмов сжатия данных
Возможность преобразования данных, импорт и экспорт данных.
Резервное копирование и создание точек восстановления данных (откатов).
Возможность многопользовательской работы и установление прав доступа.
Поддержка одновременного изменения информации в связанных базах данных (репликация).
Основная терминология.
Одной из основных задач информатизации является хранение и обработка больших массивов данных об объектах и явлениях реального мира. Такие массивы данных вместе с программными и аппаратными средствами для их обработки называются информационными системами (ИС).
Как известно, ядром любой ИС является база данных. Понятие БД применяют для связанной между собой информации, организованной определенным образом, как правило, в виде таблиц. По сути, БД – подобие электронной картотеки в компьютере из одного или нескольких файлов. С точки зрения назначения БД отображает состояние объектов и их отношений в рассматриваемой области и должна удовлетворять потребности некой сферы деятельности в надежном хранении и обработке информации. С точки зрения состава БД представляет собой именованную совокупность, набор данных об объектах. Эти объекты связаны между собой логически, т.к. относятся к одной и той же сфере. В курсе �Базы данных� рассматриваются БД, которые имеют четкую структуру (структурированы) в отличие от документальных (слабоструктурированных) баз данных. Однако современные БД позволяют хранить также неструктурированные блоки информации (текстовые, графические, мультимедийные фрагменты).
База данных (БД) - совокупность объектов, предназначенных для хранения и обработки структурированной информации. БД это не только набор объектов удовлетворяющих основным требованиям, это и набор методов реляционной алгебры.
Основные задачи ИС, работающей с БД:
Поддержка надежного хранения информации,
Преобразование информации и вычисления,
Предоставление пользователям удобного, легкого в освоении интерфейса.
Наиболее важные операции с БД:
Добавление новой информации в файлы БД,
Добавление новых файлов,
Изменение информации в файлах,
Поиск информации в БД,
Удаление информации из файлов БД,
Удаление файлов из БД.
Надежное хранение и обработку информации в БД обеспечивают системы управления базами данных (СУБД) – совокупность языковых и программных средств, предназначенных для создания, ведения и совместного использования БД многими пользователями. СУБД позволяют разрабатывать и модифицировать структуры БД, а также приложения внутри БД.
Функции СУБД:
Управление данными в БД (хранение, извлечение, обновление данных и служебной информации);
Управление данными в памяти компьютера. Эта функция связана с большим объемом информации, например, для ускорения работы СУБД используется буферизация данных в ОП;
Управление транзакциями. Транзакция - последовательность операций по изменению данных, поступившая от одного из пользователей БД, которая может быть утверждена либо отменена. Именно использование транзакций позволяет корректно обрабатывать ошибочные ситуации, возникающие при одновременной работе с БД нескольких пользователей. При переходе к многопользовательским сетевым БД использование транзакций необходимо. Механизм транзакций реализован только в современных СУБД;
Управление изменениями в БД и протоколирование. Эта функция связана с надежностью хранения данных и возможностью восстановления информации в аварийных ситуациях;
Поддержка системного каталога – описания данных (структуры). Описания данных называются также словарем данных, а сами элементы называются метаданными (т.е. данными о данных). Именно наличие описаний обеспечивает независимость между программами и данными.
Поддержка языков программирования. Для работы непосредственно с записями БД СУБД используют SQL – структурированный язык запросов, содержащий, в частности, язык описания данных, язык манипулирования данными.
Большинство современных БД предполагает использование методов реляционной алгебры, основным объектом которой является таблица, поэтому наиболее употребительны термины:
Поле - самостоятельный элемент структуры, обладающий именем и типом.
Структура - это представление данных в виде набора элементов (информационных полей) каждый из которых может, в свою очередь, быть самостоятельной структурой.
Таблица - двухмерное представление структурированных данных, состоящее из столбцов (доменов) и строк (картежей).
Строка - базовый элемент таблицы. Под строками обычно понимаются отдельные записи Столбец - базовый элемент таблицы. Обладает одним из стандартных типов.
Запись- минимальный объем данных, который может быть добавлен, считан или удален из таблицы. Состоит из набора полей, Располагается в таблице построчно.
Домен- под ним обычно подразумевается столбец таблицы, но имеется и другзначение - поименованная структура, определяющая тип столбца таблиц. Упрощенно - имя типа столбца.
Картеж - набор информационных полей. Упрощенно - строка или запись таблицы.
Ограничение - условие, налагающееся на столбец таблицы (или домен), несоблюдение которого приводит к возникновению ошибочной ситуации. Именно с помощью ограничений реализуется требование целостности данных.
Ключ - поле, на значение которого наложено ограничение.
Первичный ключ - поле или набор полей, на которые наложено ограничение уникальности Внешний ключ - поле, на которое наложено ограничение соответствия другому полю. Составной ключ - набор полей, комбинации значений которых не могут повторяться. Операция - действие над объектом БД одного из трех стандартных типов:
Операция изменения требований - это действия по созданию, удалению или применению ранее разработанных ограничений к столбцам таблицы или доменам.
Операция изменения структуры - действия по созданию, модификации или удаления структуры информационной таблицы либо других объектов БД.
Операция изменения данных - действия по добавлению, изменению, удалению или выборке данных из ранее сформированных наборов данных.
Одногруппные - действия, результатом которых является значение. Множественные - действия, результатом которых является набор данных или таблица.
Диаграмма (ERD) - схема или графическое представление структуры БД в виде сущностей и связей.
Сущность (Еntily) - элемент диаграммы, наделенный характеристиками (поляки). Каждая сущность соотносится с другими сущностями посредством связей.
Связь (Relation) - элемент ЕR-диаграммы, определяющий отношение между сущностями. Связь может отсутствовать либо быть определенного типа:
(1:1) - один к одному (технические ограничения СУБД)
(1:М) - один ко многим (классический вариант)
(N:М) - многие ко многим (ненормализованная модель)
Диаграммер - программа для разработки ERD произвольной БД. После разработки схемы БД преобразует ее в SQL код, который выполняется СУБД, в результате чего формируется новая БД.
Нормализация - процесс преобразования структуры БД (обычно ERD) к виду, удовлетворяющему максимальному числу классических условий (к третьей нормальной форме).
Триггер - элемент БД, относящийся к таблице, срабатывающий при возникновении определенного события (например, при удалении записи).
Каскадирование - механизм циклического удаления, обновления или вставки записей в связанных таблицах. Реализуется посредством триггеров.
Индекс - объект БД, относящийся к полю (или набору полей) таблицы, задающий порядок расположения записей в зависимости от значения указанного поля. Существует два способа упорядочивания данных: по возрастанию и по убыванию.
Генератор - элемент БД (встречается только в Inter Base - является аналогом "счетчика" Microsoft Access), хранящий определенное числовое значение. Характеризуется значением и шагом инкрементации. Упрощенно - переменная числового типа, которая используется для заполнения данными первичного ключа таблицы.
Запрос - основная операция реляционной алгебры, попытка получения данных удовлетворяющих условиям выборки. Запрос формируется на языке SQL либо в табличном виде. Большинство современных СУБД позволяют разрабатывать текст запроса с помощью "мастеров".
Мастер - встроенная программа, входящая в пакет СУБД, позволяющая разработчику визуально разрабатывать и модифицировать объекты открытой БД, не требуя от него глубоких специальных знаний.
Транзакция - последовательность операций по изменению данных, поступившая от одного из пользователей БД, которая может быть утверждена (метод "Commit") либо отменена (метод "RollBack"). Именно использование транзакций позволяет корректно обрабатывать ошибочные ситуации, возникающие при одновременной работе с БД нескольких пользователей. Целостность локальных БД можно соблюсти с помощью исключительно механизма ограничений. Но при переходе к многопользовательским сетевым БД использование транзакций необходимо. Механизм транзакций реализован только в самых современных СУБД.
Откат - механизм отмены всех инструкций, формирующих транзакцию. Обычно выполняется по причине невозможности выполнения одной из них.
2. Архитектура баз данных. Процесс прохождения пользовательского запроса.
В процессе научных исследований посвященных тому, как должна выглядеть БД предлагались различные способы реализации. Самым жизненным оказалась 3-х уровневая модель БД:
Уровень внешних моделей – самый верхний уровень, где каждая модель имеет своё видение данных. Этот уровень определяет точку зрения на БД отдельных приложений, причём каждое приложение ведёт и обрабатывает только те данные, которые ему необходимы.
Например: Система распределения работ использует сведения о квалификации сотрудников, но ее интересуют сведения об окладе, дом телефоне, разряде и т.д. И наоборот, имея эти сведения используются в подсистеме отдела кадров.
Концептуальный уровень – центральное или управляющее звено. Здесь БД представлена в наиболее общем виде, который определяет данные, используемые всеми приложениями. Практически концептуальный уровень отражает обобщённую модель предметной области, для которой и создавалась база данных. Как любая модель, концептуальная модель отображает только те данные, которые с точки зрения обработки являются существенными.
Физический уровень. Это данные, расположенные в файлах или страничных структурах или на внешних носителях.
Основное назначение 3-х уровневой архитектуры – независимость работы в БД от данных, позволяющая каждому уровню существовать независимо от изменений, происходящих на более низких уровнях.
Логическая независимость от данных означает защищенность внешних схем от изменений в концептуальной схеме. Работа конечных пользователей не изменится при появлении в концептуальной схеме новых сущностей, атрибутов, связей, если только эти изменения не касаются их непосредственно.
Физическая независимость от данных означает защищенность концептуальной схемы от изменений в физической схеме. Может быть выбрана другая СУБД, может измениться файловая система, устройства хранения информации, индексирование и т.п., но при этом концептуальная модель не изменится.
Процесс прохождения пользовательского запроса
2
13
124
11БМД
5
1086
9
7
Пользователь посылает запрос на получение данных из БД;
Анализ прав пользователя и внешней модели данных: соответствие данного пользователя, подтверждение или запрет на доступ к запрашиваемым данным;
3,4) В случае запрета на доступ к данным, СУБД сообщит пользователю об этом (12) и прекратит процесс обработки данных. В противном случае СУБД определит часть концептуальной модели, которую затрагивает запрос пользователя;
5) СУБД получает информацию о запрошенной части концептуальной модели;
6) СУБД запрашивает информацию о местоположении данных на физическом уровне;
7) В СУБД возвращается информация о местоположении данных в терминах ОС;
8) СУБД подаёт запрос ОС на предоставление необходимых данных, используя средства ОС;
9) ОС осуществляет перекачку информации из устройств хранения данных и пересылает её в системный буфер;
10) ОС оповещает СУБД об окончании пересылки данных;
11) СУБД выбирает из доставленной информации, находящейся в системном буфере, только то, что необходимо пользователю и пересылает эти данные в рабочую область пользователя.
12) Подтверждение окончания обработки данных (успешное или не успешное).
3. Пользователи баз данных.
В этом разделе мы рассмотрим упомянутый выше пятый компонент среды СУБД - ее пользователей. Среди них можно выделить четыре различные группы: администраторы данных и баз данных, разработчики баз данных, прикладные программисты и конечные пользователи.
Администраторы данных и администраторы баз данных
База данных и СУБД являются корпоративными ресурсами, которыми следует управлять так же, как и любыми другими ресурсами. Обычно управление данными и базой данных предусматривает управление и контроль за СУБД и помещенными в нее данными. Администратор данных, или АД, отвечает за управление данными, включая планирование базы данных, разработку и сопровождение стандартов, бизнес-правил и деловых процедур, а также за концептуальное и логическое проектирование базы данных. АД консультирует и дает свои рекомендации руководству высшего звена, контролируя соответствие общего направления развития базы данных установленным корпоративными целями.
Администратор базы данных, или АБД, отвечает за физическую реализацию базы данных, включая физическое проектирование и воплощение проекта, за обеспечение безопасности и целостности данных, за сопровождение операционной системы, а также за обеспечение максимальной производительности приложений и пользователей. По сравнению с АД, обязанности АБД носят более технический характер, и для него необходимо знание конкретной СУБД и системного окружения. В одних организациях между этими ролями не делается различий, а в других важность корпоративных ресурсов отражена именно в выделении отдельных групп персонала с указанным кругом обязанностей.
Разработчики баз данных.
В проектировании больших баз данных участвуют два разных типа разработчиков: разработчики логической базы данных и разработчики физической базы данных. Разработчик логической базы данных занимается идентификацией данных (т.е. сущностей и их атрибутов), связей между данными и устанавливает ограничения, накладываемые на хранимые данные. Разработчик логической базы данных должен обладать всесторонним и полным пониманием структуры данных организации и ее бизнес-правил. Бизнес-правила описывают основные характеристики данных с точки зрения организации. Ниже приводятся примеры типичных бизнес-правил:
Любой сотрудник не может отвечать одновременно более чем за десять сдаваемых в аренду или продаваемых объектов недвижимости.
Любой сотрудник не имеет права продавать или сдавать в аренду свою собственную недвижимость.
Доверенное лицо не может выступать одновременно и как покупатель, и как продавец недвижимости.
Для эффективной работы разработчик логической базы данных должен как можно раньше вовлечь всех предполагаемых пользователей базы данных в процесс создания модели данных. Работа разработчика логической базы данных обычно делится на два этапа.
Концептуальное проектирование базы данных, которое совершенно не зависит от таких деталей ее воплощения, как конкретная целевая СУБД, приложения, языки программирования или любые другие физические характеристики.
Логическое проектирование базы данных, которое проводится с учетом особенностей выбранной модели данных: реляционной, сетевой, иерархической или объектно-ориентированной.
Разработчик физической базы данных получает готовую логическую модель данных, занимается ее физической реализацией, в том числе:
преобразованием логической модели данных в набор таблиц и ограничений целостности данных;
выбором конкретных структур хранения и методов доступа к данным, обеспечивающих необходимый уровень производительности при работе с базой данных;
проектированием любых требуемых мер защиты данных.
Многие этапы физического проектирования базы данных в значительной степени зависят от выбранной целевой СУБД, а потому может существовать несколько различных способов воплощения требуемой схемы. Следовательно, разработчик физической базы данных должен разбираться в функциональных возможностях целевой СУБД и понимать достоинства и недостатки каждого возможного варианта воплощения. Разработчик физической базы данных должен уметь выбрать наиболее подходящую стратегию хранения данных с учетом всех существующих особенностей их использования. Если концептуальное и логическое проектирование базы данных отвечает на вопрос "что?", то физическое проектирование отвечает на вопрос "как?". Для решения этих задач требуются разные навыки работы, которыми чаще всего обладают разные люди.
Прикладные программисты.
Сразу после создания базы данных следует приступить к разработке приложений, предоставляющих пользователям необходимые им функциональные возможности. Именно эту работу и выполняют прикладные программисты. Обычно прикладные программисты работают на основе спецификаций, созданных системными аналитиками. Как правило, каждая программа содержит некоторые операторы, требующие от СУБД выполнения определенных действий с базой данных - например, таких как извлечение, вставка, обновление или удаление данных. Как уже упоминалось в предыдущем разделе, эти программы могут создаваться на различных языках программирования третьего или четвертого поколения.
Пользователи
Пользователи являются клиентами базы данных - она проектируется, создается и поддерживается для того, чтобы обслуживать их информационные потребности. Пользователей можно классифицировать по способу использования ими системы.
Наивные пользователи обычно даже и не подозревают о наличии СУБД. Они обращаются к базе данных с помощью специальных приложений, позволяющих в максимальной степени упростить выполняемые ими операции. Такие пользователи инициируют выполнение операций базы данных, вводя простейшие команды или выбирая команды меню. Это значит, что таким пользователям не нужно ничего знать о базе данных или СУБД. Например, чтобы узнать цену товара, кассир в супермаркете использует сканер для считывания нанесенного на него штрих-кода. В результате этого простейшего действия специальная программа не только считывает штрих-код, но и выбирает на основе его значения цену товара из базы данных, а также уменьшает значение в другом поле базы данных, обозначающем остаток таких товаров на складе, после чего выбивает цену и общую стоимость на кассовом аппарате.
Опытные пользователи. С другой стороны спектра находятся опытные конечные пользователи, которые знакомы со структурой базы данных и возможностями СУБД. Для выполнения требуемых операций они могут использовать такой язык запросов высокого уровня, как SQL. А некоторые опытные пользователи могут даже создавать собственные прикладные программы.
4. Модели данных. Классификация.
Модель данных - интегрированный набор понятий для описания данных, связей между ними и ограничений, накладываемых на данные в некоторой организации.
Модель является представлением "реального мира" объектов и событий, а также существующих между ними связей. Это некоторая абстракция, в которой акцент делается на самых важных и неотъемлемых аспектах деятельности организации, а все второстепенные свойства игнорируются. Таким образом, можно сказать, что модель данных представляет саму организацию. Модель должна отражать основные концепции, представленные в таком виде, который позволит проектировщикам и пользователям базы данных обмениваться конкретными и недвусмысленными мнениями об их понимании роли тех или иных данных в этой организации. Модель данных можно рассматривать как сочетание трех указанных ниже компонентов.
Структурная часть, т.е. набор правил, по которым может быть построена база данных.
Управляющая часть, определяющая типы допустимых операций с данными (сюда относятся операции обновления и извлечения данных, а также операции изменения структуры базы данных).
Набор ограничений поддержки целостности данных (необязательно), гарантирующих корректность используемых данных.
В литературе предложено и опубликовано достаточно много моделей данных. Они подразделяются на три категории: объектные (object-based) модели данных, модели данных на основе записей (record-based) и физические модели данных. Первые две используются для описания данных на концептуальном и внешнем уровнях, а последняя - на внутреннем уровне.
Объектные модели данных
При построении объектных моделей данных используются такие понятия как сущности, атрибуты и связи. Сущность - это отдельный элемент (сотрудник, место или вещь, понятие или событие) организации, который должен быть представлен в базе данных. Атрибут - это свойство, которое описывает некоторый аспект объекта и значение которого следует зафиксировать, а связь является ассоциативным отношением между сущностями. Ниже перечислены некоторые наиболее общие типы объектных моделей данных:
Модель типа "сущность-связь", или ER-модель.
Семантическая модель.
Функциональная модель.
Объектно-ориентированная модель.
В настоящее время ER-модель стала одним из основных методов концептуального проектирования баз данных. Объектно-ориентированная модель расширяет определение сущности с целью включения в него не только атрибутов, которые описывают состояние объекта, но и действий, которые с ним связаны, т.е. его поведение. В таком случае говорят, что объект инкапсулирует состояние и поведение.
Модели данных на основе записей
В модели на основе записей база данных состоит из нескольких записей фиксированного формата, которые могут иметь разные типы. Каждый тип записи определяет фиксированное количество полей, каждое из которых имеет фиксированную длину. Существует три основных типа логических моделей данных на основе записей:
реляционная модель данных (relation data model),
сетевая модель данных (network data model)
иерархическая модель данных (hierarchical data model).
Иерархическая и сетевая модели данных были созданы почти на десять лет раньше реляционной модели данных, а потому их связь с концепциями традиционной обработки файлов более очевидна.
Реляционная модель данных основана на понятии математических отношений. В реляционной модели данные и связи представлены в виде таблиц, каждая из которых имеет несколько столбцов с уникальными именами.
В реляционной модели данных единственное требование состоит в том, чтобы база данных с точки зрения пользователя выглядела как набор таблиц. Однако такое восприятие относится только к логической структуре базы данных, т.е. ко внешнему и концептуальному уровням архитектуры ANSI/SPARC. Оно не относится к физической структуре базы данных, которая может быть реализована с помощью разнообразных структур хранения.
Сетевая модель данных. В сетевой модели данные представлены в виде коллекций записей, а связи - в виде наборов. В отличие от реляционной модели, связи здесь явным образом моделируются наборами, которые реализуются с помощью указателей. Сетевую модель можно представить как граф с записями в виде узлов графа и наборами в виде его ребер. Поддеревья (потомки) в этой модели могут иметь любое число корневых элементов (предков).
Иерархическая модель данных является ограниченным подтипом сетевой модели. В ней данные также представлены как коллекции записей, а связи - как наборы. Однако в иерархической модели узел может иметь только одного родителя. Иерархическая модель может быть представлена как древовидный граф с записями в виде узлов (которые также называются сегментами) и множествами в виде ребер. В этой модели множество данных имеет структуру инвертированного дерева (древовидный граф).
Основанные на записях (логические) модели данных используются для определения общей структуры базы данных и высокоуровневого описания ее реализации. Их основной недостаток заключается в том, что они не дают адекватных средств для явного указания ограничений, накладываемых на данные. В то же время в объектных моделях данных отсутствуют средства указания их логической структуры, но за счет предоставления пользователю возможности указать ограничения для данных, они позволяют в большей мере представить семантическую суть хранимой информации.
Большинство современных коммерческих систем основано на реляционной парадигме, тогда как самые первые системы баз данных строились на основе сетевой или иерархической модели. При использовании последних двух моделей от пользователя требуется знание физической организации базы данных, к которой он должен осуществлять доступ, в то время как при работе с реляционной моделью независимость от данных обеспечивается в значительно большей степени. Следовательно, если в реляционных системах для обработки информации в базе данных принят декларативный подход (т.е. они указывают, какие данные следует извлечь), то в сетевых и иерархических системах - навигационный подход (т.е. они указывают, как их следует извлечь).
Физические модели данных
Физические модели данных описывают то, как данные хранятся в компьютере, представляя информацию о структуре записей, их упорядоченности и существующих путях доступа. Физических моделей данных не так много, как логических, а самыми популярными среди них являются обобщающая модель (unifying model) и модель памяти кадров (frame memory).
Концептуальное моделирование
Как показывает изучение трехуровневой архитектуры СУБД, концептуальная схема является "сердцем" базы данных. Она поддерживает все внешние представления, а сама поддерживается средствами внутренней схемы. Однако внутренняя схема является всего лишь физическим воплощением концептуальной схемы. Именно концептуальная схема призвана быть полным и точным представлением требований к данным некоторого предприятия. В противном случае определенная часть информации о предприятии будет упущена или искажена, в результате чего могут возникнуть трудности при попытках полной реализации одного или нескольких внешних представлений.
Концептуальное моделирование, или концептуальное проектирование, базы данных это процесс конструирования модели использования информации на некотором предприятии. Этот процесс не зависит от таких подробностей, как используемая СУБД, прикладные программы, языки программирования или любые другие вопросы физической организации информации. Подобная модель называется концептуальной моделью данных. Концептуальные модели в литературе иногда также называют логическими моделями. Концептуальная модель не зависит от любых деталей реализации, тогда как при разработке логической модели предполагается знание типа базовой модели представления данных в выбранной целевой СУБД.
5. Этапы разработки информационной структуры базы данных
Реляционная БД может быть спроектирована вручную или с использованием средств автоматизированного проектирования. При проектировании БД разрабатывается ее информационная структура, а затем приложения пользователей, т.е. программы обработки информации. Этапам разработки информационной структуры базы данных соответствуют следующие этапы жизненного цикла БД и приложений.
Планирование разработки БД. Цель – максимально эффективно реализовать в дальнейшем все этапы жизненного цикла БД и приложений, оптимизировать объем работы, используемые ресурсы, стоимость. На этом этапе производится системный анализ предметной области (структурированность информации, динамичность изменений, характер использования).
Определение требований к системе, диапазона действия приложений, состава пользователей и областей применения.
Сбор и анализ требований пользователей к системе, опрос, наблюдения, изучение документов, анкеты, использование опыта.
Концептуальное проектирование, системный анализ объектов и связей:
Создается абстрактная модель, где собрана вся информация о предметной области (документы, инструкции, справки и т.п., подготовлено неформализованное техническое задание). На основании этого создается упрощенная корпоративная модель данных.
Создается тематическая модель - подразделяется на наборы данных неупорядоченная информация предметной области. Каждый набор данных – логически связанный, непротиворечивый блок информации. Выявляются общие данные для различных таких блоков – подзадач предметной области. Выявляются типовые запросы, типовые процедуры обработки.
Создается сущностная модель. Все наборы данных преобразуются в абстрактные объекты – сущности, определяется система атрибутов. Сущности получают имена и описания. Между логически зависимыми сущностями устанавливаются связи.
Строится ER – диаграмма. Состав сущностей уточняется, они наделяются точными характеристиками, определяются типы связей между ними. Задаются первичные и внешние ключи.
Производится нормализация ER – диаграммы. Обычно нормализация проводится до третьей нормальной формы. При этом количество сущностей увеличивается (декомпозиция отношений).
Создание логической модели для различных групп пользователей (внешних схем), правил целостности информации. Выбор целевой СУБД должен быть произведен до логического проектирования, при этом определяется тип модели данных: иерархическая, реляционная, объектная и т.п., логика работы СУБД.
Создание физической модели. Отображение построенной модели данных в модель данных выбранной СУБД, проектирование структур данных и связей. Формируются SQL – скрипты, выполнение которых приводит к созданию физической БД. Скрипт создается вручную, программами автоматизированного проектирования или встроенными мастерами СУБД. При этом используются языки описания и модификации данных конкретных СУБД.
После разработки информационной структуры базы данных ее жизненный цикл продолжается созданием приложений, разработкой интерфейса пользователя, прикладных программ. При этом пользователю могут предоставляться для опробования приложения БД, обладающие не полным набором функциональных возможностей – прототипы. После опробования могут быть повторены некоторые этапы для корректировки информационной структуры БД. Реализация приложений производится на языке целевой СУБД: MS SQL Server, Oracle, FoxPro или систем программирования Delphi, VB, C++ и т.д. Разработанные приложения проходят этапы тестирования и отладки, в результате которых может выявиться необходимость дополнительной доработки информационной структуры БД. Этот процесс может продолжаться и в ходе эксплуатации БД.
Жизненный цикл БД предполагает также конвертирование и загрузку ранее подготовленных данных, т.е. заполнение БД, а также эксплуатацию и сопровождение БД, наблюдение и поддержку работоспособности, контроль производительности, настройку, модернизацию, реорганизацию, развитие и улучшение структуры БД.
6. Реляционная модель данных. Основные понятия и определения.
Типы реляционных БД:
Многофайловая модель
Однофайловая модель
Репозитарий БД
Хотя реляционная модель данных в настоящее время является преобладающей, она реализуется несколькими способами. Эти варианты реализации модели сформировались исторически. Они отличаются уровнем строгости по отношению к данным и элементам проекта и уровнем технических требований.
Многофайловая модель. Проект состоит из отдельных разнотипных файлов, расположение которых не контролируется. Проект почти не зависим от ОС. В случае отсутствия файла сбой происходит во время выполнения кода. Компиляция проекта характера ситуации не меняет. Проекты перегружены файлами. Отследить какие файлы в проекте не используются практически невозможно. Таблицы, с которыми работает приложение, не гарантируют целостность хранения информации, поэтому называются БД весьма условно. Проблемы многопользовательской работы принципиально решаются на уровне ОС.
Однофайловая модель. Проект состоит из одного крупного файла, в котором находятся все элементы проекта. Для контроля за содержимым БД предназначен менеджер проекта (прототип репозитария). БД отличается очень высоким уровнем совместимости. При подключении нескольких пользователей (>12) скорость работы приложения резко падает.
БД на основе репозитария. Такие СУБД поддерживают возможность "открытого доступа к данным" (ODBC), которые могут находиться на любом удаленном компьютере или в другой БД. БД самостоятельно контролирует свою целостность посредством ограничений. Корректность многопользовательской работы контролируется механизмом транзакций. Проекты, созданные такими СУБД могут синхронизировать процесс обмена данными между удаленными хранилищами с помощью репликаций.
Основные понятия и определения.
Реляционная модель основана на математическом понятии отношения, физическим представлением которого является таблица.
Структура реляционных данных
Отношение - это плоская таблица, состоящая из столбцов и строк. В любой реляционной СУБД предполагается, что пользователь воспринимает базу данных как набор таблиц. Однако следует подчеркнуть, что это восприятие относится только к логической структуре базы данных, т.е. к внешнему и концептуальному уровням архитектуры ANSI-SPARC. Подобное восприятие не относится к физической структуре базы данных, которая может быть реализована с помощью различных структур хранения.
Атрибут – это поименованный столбец отношения. В реляционной модели отношения используются для хранения информации об объектах, представленных в базе данных. Отношение обычно имеет вид двумерной таблицы, в которой строки соответствуют отдельным записям, а столбцы - атрибутам. При этом атрибуты могут располагаться в любом порядке - независимо от их переупорядочивания отношение будет оставаться одним и тем же, а потому иметь тот же смысл.
Домен - это набор допустимых значений для одного или нескольких атрибутов. Домены представляют собой чрезвычайно мощный компонент реляционной модели. Каждый атрибут реляционной базы данных определяется на некотором домене. Домены могут отличаться для каждого из атрибутов, но два и более атрибутов могут определяться на одном и том же домене.
Кортеж - это строка отношения. Элементами отношения являются кортежи, или строки, таблицы. Описание структуры отношения вместе со спецификацией доменов и любыми другими ограничениями возможных значений атрибутов иногда называют его заголовком (или содержанием). Обычно оно является фиксированным, до тех пор, пока смысл отношения не изменяется за счет добавления в него дополнительных атрибутов. Кортежи называются расширением, состоянием или телом отношения, которое постоянно меняется.
Степень – степень отношения определяется количеством атрибутов, которое оно содержит. Отношение только с одним атрибутом имеет степень 1 и называется унарным отношением (или 1-арным кортежем). Отношение с двумя атрибутами называется бинарным, отношение с тремя атрибутами - тернарным, а для отношений с большим количеством атрибутов используется термин n-арный (n-аrу). Определение степени отношения является частью заголовка отношения.
Кардинальность – это количество кортежей, которое содержит отношение.
Количество содержащихся в отношении кортежей называется кардинальностью отношения. Эта характеристика меняется при каждом добавлении или удалении кортежей. Кардинальность является свойством тела отношения и определяется текущим состоянием отношения в произвольно взятый момент. И, наконец, мы подошли к определению самой реляционной базы данных.
Реляционная база данных – набор нормализованных отношений. Реляционная база данных состоит из отношений, структура которых определяется с помощью особых методов, называемых нормализацией.
Альтернативная терминология
Терминология, используемая в реляционной модели, порой может привести к путанице, поскольку помимо предложенных двух наборов терминов существует еще один - третий. Отношение в нем называется файлом (file), кортежи - записями (records), а атрибуты - полями (fields). Эта терминология основана на том факте, что физически реляционная СУБД может хранить каждое отношение в отдельном файле.
Свойства отношений
Каждое отношение имеет имя, отличное от других,
Атрибут имеет уникальное имя,
Значения атрибута берутся из одного и того же домена,
Атрибуты уникальны (нет повторяющихся столбцов),
Порядок следования атрибутов не имеет значения,
Кортежи уникальны, повторений быть не может,
Кортежи не упорядочены, порядок не имеет значения,
Значения атрибутов логически не делимы (ячейки отношений).
Виды отношений
Базовое отношение - часть БД, соответствующая некоторой сущности в концептуальной схеме, имеет собственное наименование.
Производное отношение определено через другие (базовые) с использованием средств СУБД.
Именованное отношение - переменное, определяется операторами СУБД для более удобного представления пользователя.
Представление – именованное производное отношение, выраженное через операторы СУБД, физически в БД не существует.
Результат запроса – неименованное производное отношение, не хранится, существует только, пока нужен.
Хранимое отношение – физически поддерживается в памяти компьютера, прежде всего - базовые отношения.
В реляционной БД отношения связаны друг с другом. Связь – это ассоциирование двух и более отношений. Связь позволяет по значениям одних кортежей находить другие.
Основные виды связей
�Один к одному� (1:1) Работник ( Зарплата
�Один ко многим� (1:М) Дом ( Жильцы
�Многие к одному� (М:1)Студенты ( Группа
�Многие ко многим� (М:M - множественные связи) - в реляционных БД реализуются через другие связи и отношения (например, много студентов сдает много дисциплин).
7. Первичные и внешние ключи. Непротиворечивость и целостность данных
Корректность данных в БД гарантируется набором ограничений целостности. Рассмотрение реляционных ограничений целостности требует введения нижеследующих понятий.
Ограничение - условие, налагающееся на столбец таблицы, несоблюдение которого приводит к возникновению ошибочной ситуации. Именно с помощью ограничений реализуется требование целостности данных.
Ключ (потенциальный ключ) – минимальный набор атрибутов (полей), по значениям которого можно однозначно определить требуемый кортеж. На значения ключа наложено некоторое ограничение.
Если ключ включает более одного атрибута, то он называется множественным, комбинированным или составным. Каждое отношение имеет хотя бы один потенциальный ключ.
Первичный ключ - потенциальный ключ, который выбран для уникального определения кортежей внутри отношения, т.е. ключ, на который наложено ограничение уникальности. В крайнем случае – это весь набор атрибутов, но лучше, если это – минимальный набор атрибутов (без текстов), например, идентификатор записи.
Внешний ключ – атрибут или набор атрибутов, которые соответствуют ключу другого отношения, т.е. на которые наложено ограничение соответствия другому ключу. Если отношения связаны между собой, то они должны иметь соответствующие ключи. Внешний ключ является ссылкой на первичный ключ другого отношения.
Операция - действие одного из стандартных типов над объектом БД:
Операция изменения структуры - действия по созданию, модификации или удаления структуры информационной таблицы либо других объектов БД;
Операция изменения требований - это действия по созданию, удалению или применению ранее разработанных ограничений к столбцам таблицы или доменам;
Операция изменения данных - действия по добавлению, изменению, удалению или выборке данных из ранее сформированных наборов данных.
Два основных правила целостности реляционной модели называются целостностью сущностей и ссылочной целостностью.
Для рассмотрения этих правил используется понятие определитель NULL, который указывает, что значение атрибута в настоящий момент неизвестно. Определитель NULL не следует понимать как нулевое значение или заполненную пробелами текстовую строку. Нули и пробелы представляют собой некоторые значения, а ключевое слово NULL обозначает отсутствие какого-либо значения, неопределенность. Ключевое слово NULL следует рассматривать как способ обработки неполных или необычных данных.
Правило целостности сущностей: в базовом отношении ни один атрибут первичного ключа не может содержать отсутствующих значений, т.е. NULL-значений.
Если допустить присутствие определителя NULL в любой части первичного ключа, это равноценно утверждению, что не все его атрибуты необходимы для уникальной идентификации кортежей, что противоречит определению первичного ключа.
Второе ограничение целостности касается внешних ключей.
Правило ссылочной целостности: если в отношении существует внешний ключ, то значение внешнего ключа должно либо соответствовать значению первичного ключа, на который он ссылается, либо задаваться определителем NULL.
Корпоративные ограничения целостности – дополнительные правила поддержки целостности данных, определяемые пользователями или администраторами базы данных.
Средства поддержки целостности данных (см. DCL SQL и т.т.)
Поддержка целостности данных включает средства задания ограничений, которые вводятся для защиты БД от нарушения согласованности данных. Большая часть этих ограничений задается в операторах CREATE и ALTER TABLE.
Типы ограничений поддержки целостности данных и их реализация в стандарте ISO в операторах CREATE или ALTER TABLE:
Обязательные данные: Для некоторых столбцов требуется наличие в каждой строке таблицы конкретного значения, отличного от значения NULL. Так, в таблице RABOTNIK каждый работник обязательно занимает ту или иную должность. Для задания ограничений такого типа предусмотрено использование служебного слова NOT NULL. Например,
DOLJNOST VARCHAR(10) NOT NULL.
Ограничения для доменов атрибутов: Каждый столбец имеет собственный домен - некоторый набор допустимых значений. Например, для определения пола работника достаточно всего двух значений, поэтому домен для столбца POL таблицы RABOTNIK можно определить как набор из двух строк длиной в один символ со значением либо 'М', либо 'Ж'. Можно описать его таким образом:
POL CHAR NOT NULL CHECK (POL IN (‘M’, ‘Ж’)).
Целостность сущностей: Первичный ключ таблицы должен иметь уникальное непустое значение в каждой строке таблицы. Например, каждая строка таблицы RABOTNIK должна содержать уникальное непустое значение личного (табельного) номера работника в столбце NOMER, оно будет определять работника, описываемого данной строкой таблицы. Требования поддержки целостности данных задаются с помощью фразы
PRIMARY KEY (NOMER).
В случае составного первичного ключа фраза определения первичного ключа будет иметь следующий вид:
PRIMARY KEY (FIRMA, NOMER).
Фраза PRIMARY KEY может указываться в определении таблицы только один раз. Гарантировать уникальность значений для альтернативных ключей таблицы может ключевое слово UNIQUE и ключевые слова NOT NULL.
Ссылочная целостность: Понятие ссылочной целостности означает, что если поле внешнего ключа содержит некоторое значение, то оно обязательно должно ссылаться на существующую строку в родительской таблице. Например, значение OTDEL (внешний ключ) таблицы RABOTNIK, если оно не опущено, должно связывать каждого сотрудника с конкретной строкой таблицы OTDELS, соответствующей тому отделению фирмы, в котором данный сотрудник работает. В противном случае работник окажется приписанным к несуществующему отделению компании.
Для определения внешнего ключа OTDEL в таблице RABOTNIK можно использовать, например, следующее предложение:
FOREIGN KEY(OTDEL) REFERENCES OTDELS.
При этом будет отклонена попытка создания в дочерней таблице RABOTNIK значения внешнего ключа, не соответствующего одному из уже существующих значений первичного ключа родительской таблицы OTDELS. При попытке обновить или удалить значение первичного ключа в родительской таблице в зависимости от правил поддержки ссылочной целостности, указанных во фразах ON UPDATE и ON DELETE предложения FOREIGN KEY выполняется каскадирование, занесение во внешние ключи NULL-значений, значений по умолчанию, новых значений или операция не производится вообще.
Требования данного предприятия: Обновления данных в таблицах могут быть ограничены существующими в данной организации требованиями (бизнес-правилами). Реализовать бизнес-правила предприятий можно, например, с помощью предложений CHECK и условий, записанных на языке SQL.
Реляционная алгебра
Реляционная алгебра – формальная основа реляционной модели БД - формальный, абстрактный язык описания операций для манипулирования отношениями, связанный с теорией множеств. Реляционная алгебра используется для разработки других языков более высокого уровня, так, базирующееся на реляционной алгебре и математической логике реляционное исчисление лежит в основе структурированного языка запросов SQL. Операции реляционной алгебры позволяют на основе одного или нескольких отношений создавать другое отношение. Таким образом, оба операнда и результат являются отношениями, а потому результаты одной операции могут стать исходными данными для другой операции. Это позволяет создавать вложенные выражения реляционной алгебры точно так же, как создаются вложенные арифметические выражения. Все кортежи обрабатываются одной командой без организации циклов.
Существует несколько вариантов выбора операций, которые включаются в реляционную алгебру. Первоначально Э. Кодд предложил восемь операторов, но впоследствии к ним были добавлены и некоторые другие. Пять основных операций реляционной алгебры, а именно выборка (select), проекция (projection), декартово произведение (Cartesian product), объединение (union) и разность (set difference), выполняют большинство операций извлечения данных. Из пяти основных операций можно вывести дополнительные операции: соединения (join), пересечения (intersection) и деления (division).
Операции выборки и проекции являются унарными, поскольку они работают с одним отношением. Другие операции работают с парами отношений, и поэтому их называют бинарными операциями. В приведенных ниже определениях R и S - это два отношения. Для команд реляционной алгебры предложено несколько вариантов синтаксиса. Здесь использованы общепринятые обозначения. Функции восьми операций схематически показаны на рисунке.
Выборка ПроекцияR SRxS
=
Декартово произведение
Объединение Пересечение Разность (множеств)
RR R
SSS
Деление R(S
Выборка (или ограничение)
(предикат(R) - Операция выборки работает с одним отношением R и определяет результирующее отношение, которое содержит только те кортежи (строки) отношения R, которые удовлетворяют заданному условию (предикату).
Проекция
Патр.1,...,атр.N(R) - Операция проекции работает с одним отношением R и определяет новое отношение, содержащее вертикальное подмножество отношения R, создаваемое посредством извлечения значений указанных атрибутов и исключения из результата строк-дубликатов.
Декартово произведение
RxS - Операция декартова произведения определяет новое отношение, которое является результатом конкатенации (т.е. сцепления) каждого кортежа из отношения R с каждым кортежем из отношения S. Операторы выборки и проекции извлекают информацию только из одного отношения, но возможно нужны данные из нескольких отношений, тогда предварительно применяется оператор произведения. Он умножает два отношения, что в результате приводит к созданию другого отношения, состоящего из всех возможных пар кортежей обоих отношений. Следовательно, если одно отношение имеет I кортежей и N атрибутов, а другое - J кортежей и М атрибутов, то отношение с их декартовым произведением будет содержать (IxJ) кортежей и (NxM) атрибутов. Исходные отношения могут содержать атрибуты с одинаковыми именами. В таком случае имена атрибутов будут содержать названия отношений в виде префиксов для обеспечения уникальности имен атрибутов в новом отношении.
Объединение
R(S - Объединение отношений R и S с кортежами I и J соответственно можно получить в результате их конкатенации с образованием одного отношения с количеством кортежей (I+J), если кортежи-дубликаты исключены. При этом отношения R и S должны быть совместимы по объединению, т.е. одинаковой структуры. Для получения двух совместимых по объединению отношений может быть использована операция проекции.
Разность
R-S - Разность двух отношений R и S состоит из кортежей, которые имеются в отношении R, но отсутствуют в отношении S. Причем отношения R и S должны быть совместимы по объединению.
Операция соединения
Операция соединения является производной, так как она эквивалентна операции выборки из декартова произведения двух операндов-отношений тех кортежей, которые удовлетворяют условию, указанному в предикате соединения в качестве формулы выборки. Операция соединения - одна из самых важных операций реляционной алгебры, т.к. обычно именно она интересует пользователей. Эта операция является одной из самых трудных для эффективной реализации в реляционных СУБД и часто оказывается одной из основных причин, вызывающих проблемы с производительностью, свойственные всем реляционным системам.
Пересечение
R(S - Операция пересечения определяет отношение, которое содержит кортежи, присутствующие как в отношении R, так и в отношении S. Отношения R и S должны быть совместны по объединению. Пересечение можно сформулировать и на основе оператора разности множеств: R(S=R-(R-S).
Деление
R(S - Оператор деления полезен для запросов особого типа, которые встречаются в приложениях баз данных. Предположим, что отношение R определено на множестве атрибутов A, а отношение S - на множестве атрибутов B, причем B ( A (т.е. B является подмножеством А).
Пусть C является множеством атрибутов отношения R, которые не являются атрибутами отношения S. Тогда результатом оператора деления является набор кортежей отношения R, определенных на множестве атрибутов C, которые соответствуют комбинации всех кортежей отношения S.
Пусть есть список студентов (номер, ФИО, оценка) и список учащихся на �4� и �5� (номер, оценка). В результате деления будут найдены фамилии этих успешных студентов:
RSR(SОстаток
Иванов 41 4 Иванов 2 Петров 3
Петров 33 5 Сидоров
Сидоров 5
Здесь множество атрибутов C соответствует ФИО.
Операции реляционной алгебры используются для создания представлений базы данных: сгруппированных, горизонтальных, вертикальных, объединенных.
Принципы нормализации. Описание предметной области. Нормальные формы.
Грамотная разработка структуры БД связана с необходимостью нормализации. Эффект нормализации заметен только при работе с большими системами и, следовательно, большими проблемами.
Наиболее трудоемким этапом проектирования БД является разработка структуры данных. Структура БД должна обеспечивать быстрый доступ к данным, минимальный уровень дублирования, целостность данных. В ненормализованной структуре БД могут возникнуть проблемы, связанные с избыточностью данных и аномалиями обновления. Под избыточностью данных понимают их дублирование. Избыточность данных при выполнении операций может привести к аномалиям удаления, обновления и вставки информации. При эксплуатации БД требуется периодически проверять хранимую информацию на наличие аномалий. В случае их обнаружения должен проводиться анализ и наложение дополнительных ограничений для данных.
Примером избыточности, которая приводит к аномалиям, является хранение сведений об организациях и сотрудниках в одной таблице. В этом случае при вставке сведений о новом сотруднике необходимо вводить и информацию об организации, например, адрес и телефон, хотя эта информация уже введена в записях (аномалия вставки). При удалении последнего сотрудника организации будет потеряна информация о ней, т.к. не останется записей, относящихся к этой организации (аномалия удаления). При обновлении информации об организациях, например, телефона, придется обновлять информацию для всех ее сотрудников (аномалия обновления информации).
Смысл нормализации состоит в уменьшении избыточности хранимых данных. Нормализация – представление сложных структур данных (документов предметной области) в виде двухмерных таблиц – отношений. Процесс нормализации состоит в декомпозиции отношений – разбиении таблицы на две и более с целью ликвидации дублирования данных и их возможной противоречивости. Процесс нормализации БД заключается в последовательном переводе отношений из первой нормальной формы в нормальные формы более высокого порядка, получая каждый раз таблицы с лучшими свойствами. Некоторые СУБД позволяют выполнить процесс нормализации встроенными стандартными средствами, но при этом структура данных будет расчленена на сущности по принципу строгой уникальности и может оказаться громоздкой, не эффективной и не устроить разработчика.
В теории БД существуют следующие нормальные форм:
первая нормальная форма;
вторая нормальная форма;
третья нормальная форма;
усиленная третья нормальная форма (форма Бойса - Кодда);
четвертая нормальная форма;
пятая нормальная форма.
Как правило, на начальных этапах проектирования вся информация предметной области может быть собрана и представлена в виде одной или нескольких больших таблиц. Пусть, например, дана таблица результатов сдачи сессии студентами, включающая фамилии студентов, семестр, и сданные студентом дисциплины с формами отчетности (зачет/экзамен), оценками, количеством часов, фамилиями и должностями преподавателей (см. [1], стр.149). Такая таблица вообще не является отношением, т.к. в строке каждого студента в столбцах дисциплин и преподавателей указано несколько значений, т.е. информация является делимой (подобие зачетной книжки). Это ненормализованная форма представления информации, содержащая повторяющиеся группы полей.
Преобразовать эту информацию в отношение можно, повторив многократно фамилии студентов и семестр (см. [1], стр. 150 таблица �Сессия). Необходимо также выявить сущности, �замаскированные� под атрибуты. Такое отношение находится в первой нормальной форме (1НФ): его поля содержат неделимую информацию, и в таблице нет повторяющихся групп полей. Однако при этом появился большой уровень дублирования, потенциальная противоречивость и возможность аномалий. Например, названия предметов могут быть написаны по-разному, а если изменится количество часов по какому-либо предмету, то придется искать и менять все его строки.
Для определения следующих нормальных форм введем понятие функциональной зависимости между атрибутами, т.е. связи типа �многие к одному�. Например, по Личному номеру можно определить Должность работника, но по должности мы можем определить многих работников с такой должностью. Поэтому Должность функционально зависит от Личного номера. Аналогично, атрибут Оценка функционально зависит от ключа студента и ключа, определяющего дисциплину.
Получение второй нормальной формы (2НФ) - следующий этап нормализации. 2НФ предъявляет следующие требования:
Таблица должна удовлетворять требованиям первой нормальной формы;
Любое неключевое поле должно однозначно идентифицироваться ключевыми полями.
Во 2НФ все атрибуты должны полностью функционально зависеть от ключа. Записи таблицы, приведенной к первой нормальной форме, еще не являются уникальными и содержат дублирующиеся данные. Чтобы избавиться от дублирования, надо разбить таблицы и добавить в каждую структуру таблицы поле первичного ключа.
Решение проблем устранения дублирования информации требует введения первичных ключей – идентификаторов и разделения таблицы �Сессия� на таблицы (см.[1], стр. 151):
�Студенты� (Номер, ФИО),
�Преподаватели� (Номер, ФИО),
�Дисциплины� (Номер, Название),
�Учебный план� (Номер строки, Номер дисциплины, Семестр, Количество часов, Форма отчетности, Номер преподавателя, Должность),
�Результаты сессии� (Номер студента, Номер строки учебного плана, Оценка).
Не рекомендуется назначать полем первичного ключа одно из информационных полей таблицы (например, номер паспорта или фамилию), следует соблюдать правила:
Поле первичного ключа должно быть числовым (для компактности);
Поле первичного ключа должно быть уникально в пределах таблицы;
Поле первичного ключа должно заполняться автоматически, например, с помощью встроенных средств СУБД или триггеров;
Поле первичного ключа рекомендуется называть ID (идентификатор);
При связи таблиц ссылка на данные, хранящиеся в других таблицах, происходит указанием в поле внешнего ключа таблицы значения, соответствующего полю первичного ключа другой (справочной) таблицы.
Например, таблица �Учебный план� содержит внешний ключ со ссылкой на первичный ключ таблицы �Преподаватели� - уникальный номер каждого преподавателя. В каждую таблицу добавляется дополнительный столбец (ID), значение которого численно характеризует любую запись таблицы.
Можно сгенерировать БД и в таком виде, но набор таблиц еще не обладает достоинствами реляционной модели, так как сохраняются возможности аномалий. Например, в данном случае при изменении должности преподавателя придется в таблице �Учебный план� менять это значение в каждой строке, относящейся к этому преподавателю. Поэтому преобразуем таблицы к третьей нормальной форме (3НФ), к которой предъявляются следующие требования:
Таблица удовлетворяет требованиям второй нормальной формы;
Ни одно неключевое поле не связано функциональной зависимостью с другим неключевым полем.
Т.е. все неключевые атрибуты взаимно независимы, не допускается транзитивная (через другие неключевые атрибуты) зависимость от первичного ключа. Например, в данном случае, чтобы избежать аномалии обновления должности преподавателя, следует устранить функциональную зависимость между неключевыми атрибутами Должность и Преподаватель и провести декомпозицию таблицы �Учебный план�, выделить таблицу �Кадровый состав преподавателей�.
Во многих случаях на этом нормализация заканчивается, т.к. результат дальнейшей декомпозиции отношений может быть неоднозначным в смысле оптимизации структуры БД. Проще бывает иногда организовать периодическую проверку корректности БД и устранение аномалий программными средствами.
Заметим, что более строгая форма (3НФ) форма Бойса – Кодда, учитывает возможность существования в таблице нескольких потенциальных первичных ключей и звучит так: каждый детерминант является потенциальным ключом, т.е. любая функциональная зависимость между атрибутами сводится к функциональной зависимости от потенциального первичного ключа.
Для нашего примера процесс нормализации на этом закончен.
Следующие нормальные формы учитывают возможность существования многозначных зависимостей между атрибутами, из-за которых при обратном соединении декомпозированных таблиц могут появиться лишние записи. Полной декомпозицией таблицы называется совокупность проекций, соединение которых полностью совпадает с содержимым исходной таблицы.
Пятая нормальная форма (5НФ) требует, чтобы при полной декомпозиции таблиц все проекции содержали потенциальный ключ. Если при полной декомпозиции таблиц получаются таблицы одинаковой структуры, то они объединяются. 5НФ направлена на устранение возможных ошибок при обратном соединении отношений.
Четвертая нормальная форма (4НФ) является частным случаем 5НФ и требует, чтобы примененная к каждой таблице полная декомпозиция была соединением ровно двух проекций. Чаще всего БД в 4НФ уже находится в 5НФ.
Схема нормализации
ННФ (ненормализованные данные)
Удаление повторяющихся групп путем записи в ячейки таблицы
1НФ
Полная функциональная зависимость от первичного ключа
(удаление зависимостей от части ключа)
2НФ
�Неприводимая� зависимость от первичного ключа
(удаление транзитивных зависимостей неключевых атрибутов)
3НФ
Удаление из функциональных зависимостей оставшихся аномалий
НФБК
Удаление многозначных зависимостей
4НФ
Удаление зависимостей соединения
5НФМодель �Сущность-связь�. ER - модель. Типы связей: �один к одному�, �многие к одному�, �один ко многим�, �многие ко многим�
Модель �Сущность-связь� или ER-модель представляет собой средство формализованного представления предметной области, т.е. концептуальной модели данных. Основная цель ее разработки - упрощение задачи проектирования баз данных и создание модели пользовательского восприятия данных.
Эта модель положена в основу многих коммерческих CASE - продуктов, поддерживающих автоматизированную разработку систем БД вплоть до автоматической генерации скриптов создания таблиц для выбранной СУБД. Моделирование предметной области в этом случае производится графически в виде диаграмм. При этом используются различные технологии их построения, обозначения и ограничения, например, такие как в системе Platinum Erwin/ERX.
Основные положения концепции ER-модели:
Совокупность объектов предметной области представлена как совокупность сущностей, сущности – объекты предметной области;
Каждая сущность обладает свойствами (набором атрибутов), отличающими ее от других сущностей;
Сущности классифицируются по типам (классам), имеющим независимое существование, классы могут образовывать иерархическую зависимость;
Взаимосвязи объектов представляются как связи между двумя или несколькими сущностями.
Основным понятием ER-моделирования является тип сущностей, который представляет множество объектов реального мира с одинаковыми свойствами. БД обычно содержит много разных типов сущностей. Тип сущности имеет имя, список свойств и обладает уникальным набором атрибутов. Каждый экземпляр типа сущности называется просто сущность. Сущность имеет свои собственные значения для каждого атрибута. Типы сущностей можно классифицировать как сильные и слабые. Слабый тип сущности – тип сущности существование, которого зависит от какого-то другого типа сущности. Сильный тип сущности – тип сущности существование, которого не зависит от какого-то другого типа сущности. Слабые сущности иногда называют дочерними, зависимыми или подчиненными, а сильные – родительскими, сущностями владельцами или доминантными.
Тип связи является набором ассоциаций между двумя или более типами сущностей-участников. Каждому типу связи присваивается своё имя которое должно описывать его функцию. Каждый экземпляр типа связи называется просто связь. Связи указывают на отдельные экземпляры сущностей, объединяемые ими.
Наиболее распространёнными являются бинарные связи “один к одному” (1:1), “один ко многим” (1:M), “многие ко многим” (M:N).
Связи “один к одному”(1:1). Рассмотрим бинарную связь Отделение и Зав. отделением. Это – связь 1:1. Другой пример, Староста и Группа. Такие связи возникают, например, когда сущность имеет слишком много атрибутов и в целях простоты, наглядности и т.п. она разделяется на несколько сущностей. Возможно, сущности �маскируются� под атрибуты, например, при описании сущности группа фамилию старосты можно считать свойством этой группы, но можно и заметить, что тип сущности �Старосты� является самостоятельным и может участвовать в проектировании БД как отдельный объект. Выбор зависит от целей проектирования и эффективности воплощения.
Связи “один ко многим” (1:М). Рассмотрим бинарную связь �Группа� и �Студент�. Одна группа – много студентов. Это – связь 1:М. Другой пример, �Группа� и �Специальность� (М:1). Несколько групп обучаются по одной специальности.
Связи “многие ко многим”. Рассмотрим бинарную связь �Преподаватель� и �Дисциплина�. Например, преподаватель Иванов может преподавать физику и математику, в то же время Петров также преподает математику, а Сидоров преподает физику и электронику, т.е. многие преподаватели ведут многие предметы. Это – связь М:М. Другой пример, многие товары могут рекламироваться во многих газетах. В реляционной модели данных такие связи реализуются через некоторые промежуточные сущности и сводятся к связям 1:М и М:1.
Инструмент связей позволяет представить сложные объекты в виде более простых.
ER-модели строятся в виде ER - диаграмм. ER-диаграмма сочетает в себе и функциональный и информационный подходы к представлению модели предметной области: отображает и совокупность функций и структуры и их взаимосвязи. Графическая форма компактна, наглядна, она также позволяет применить аппарат нормализации. По стандарту сущности представляются в виде прямоугольников, свойства – эллипсов, соединенных со своими сущностями, связи – ромбов. Графические элементы подписываются внутри, на схеме отмечаются типы связей 1 или М.
Пример построен по образцу (см. [1], стр. 143) для БД �Сессия� (см. [1], стр.151)
В различных СУБД используются свои обозначения при построении моделей, например, в MS Access модель �сущность-связь� представляется по команде �Схема данныхЯзык SQL, его достоинства. Классификация операторов SQL
SQL (Structured Query Language) - символизирует собой Структурированный Язык Запросов. Это - язык, который дает возможность создавать, модифицировать и удалять данные и их структуры в реляционных и постреляционных базах данных.
Мир баз данных становится все более и более единым, что привел необходимости создания стандартного языка, который мог бы использовать большом количестве различных СУБД. Стандартный язык формирования запросов позволяет разработчикам получать доступ к любым (стандартным) реляционным структурам.
Стандарт SQL определяется ANSI (Американским Национальным Институтом Стандартов) и уже давно одобрен ISO (Международной Организацией по Стандартизации). Однако большинство коммерческих программ баз данных расширяют SQL без уведомления ANSI, добавляя разные другие особенности в этот язык, которые, как они считают, будут весьма полезны.
Иногда они несколько нарушают стандарт языка, хотя хорошие идеи, имеют тенденцию развиваться и вскоре становиться стандартами "рынка" сами по себе в силу полезности своих качеств. В этом пособии излагается классический (исходный) вариант SQL соответствующий стандарту ANSI, хотя в практике чаще используются его модификации (Microsoft SQL, SQL Plus, ISQL, MySQL...). Кроме количества диалектов языка SQL он наделен еще одним признаком классификации: статический SQL и динамический SQL
Такое разделение происходит в контексте кода прикладных программ, в которых SQL-операции, реализованные как неизменяемые выражения, называются статическими, а операции, использующие передающиеся им из других фрагментов кода параметры именуются динамическими.
SQL позволяет не только создать структуру БД, но и описать правила ее функционирования: триггеры и хранимые процедуры. С помощью SQL разрабатывается и реализуется механизм проверки целостности информации. SQL это язык, ориентированный на реляционные базы данных. Его основным действием является запрос, логика шагов которого очень проста:
Рассматриваем строку таблицы;
Выполняем сверку условия - является ли эта строка нужной;
Если это так, сохраняем ее в буфере;
Переходим к следующей строке и повторяем шаг 1;
Если строк больше нет, выводим все значения сохраненные в шаге 3.
Некоторые СУБД и прикладные программы поддерживают еще одну возможность формирования запросов. Для этого используется принципиально другой язык - QBE (Question By Example) - язык запросов по образцу. Концепция этого языка очень проста - пользователь визуально формирует образец (создаёт шаблон) поиска и видит результат выполненной операции. Диалог обычно реализуется с помощью мастеров. Внешний вид шаблона - табличный.
Хотя SQL используется большинством реляционных СУБД, он не является полноценным языком программирования, в нем нет операторов проверки условий, организации циклов и т. п., он является подъязыком, предназначенным для управления базами данных, его операторы встраиваются в базовый язык программирования для работы с БД.
Успех SQL принесли следующие его достоинства:
Независимость от конкретных СУБД. Реляционную БД и программы, которые с ней работают, можно перевести с одной СУБД на другую с минимальными доработками и подготовкой персонала.
Переносимость на другие вычислительные системы: информация корпоративных систем может бать загружена в личные БД, приложения можно вначале смоделировать в экономичных персональных компьютерах, а затем перенести на дорогие много пользовательские системы.
Стандарт языка SQL (последний вариант 1992 г.) поддерживается во всем мире, используется в среде ОС UNIX, поддерживается в протоколе ODBC, т.е. фактически одобрен другими стандартами.
Реляционная основа подводит солидный теоретический фундамент и, кроме того, делает его простым и легким для изучения благодаря интуитивно понятной табличной структуре реляционных данных.
Высокоуровневая структура, напоминающая английский язык. В отличие от формальных языков программирования, таких как Pascal или С++, SQL является легким для понимания, слабо структурированным языком: его операторы напоминают английские предложения и содержат слова �пустышки�, не влияющие на смысл оператора, но облегчающие его чтение. Операторы SQL описывают данные, которые надо получить, а не способы их получения. Таблицы и столбцы в БД могут иметь длинные описательные имена и поэтому большинство операторов означает именно то, что соответствует их именам.
Интерактивные запросы обеспечивают пользователям немедленный доступ к данным. Ответы на самые сложные запросы получаются в интерактивном режиме за минуты, тогда как подготовка соответствующей программы потребует несколько дней. Данные становятся более доступными и могут помочь в принятии решений.
Программный доступ к базе данных. Одни и те же операторы SQL используются как для интерактивного, так и для программного доступа, поэтому те части программ, которые обращаются к БД можно вначале тестировать в интерактивном режиме.
Различные представления данных – Представления пользователя, содержащие только нужные ему сведения, можно строить с помощью SQL.
Полноценный язык работы с БД для создания, управления работой, изменения содержимого, совместного использования многими пользователями БД, хотя первоначально был задуман лишь для интерактивных запросов.
Динамическое определение данных, т.е. возможность динамически изменять и расширять структуру БД, даже во время обращений пользователей к ее содержимому - это преимущество перед языками статического определения данных, запрещающими доступ к БД во время изменения ее структуры. Дает возможность гибко адаптироваться к изменению требований, не прерывая работы приложений в реальном масштабе времени.
Архитектура клиент-сервер естественно поддерживается SQL, позволяя клиентским компьютерам взаимодействовать с сетевыми серверами или крупными БД на больших ЭВМ, получать доступ к корпоративным ресурсам с персональных компьютеров.
Поддержка SQL в компьютерных системах на основе UNIX. Это важно учитывая растущее значение UNIX, особенно в дорогих научных и инженерных системах.
Обработка транзакций первоначально не проводилась в реляционных БД и не требовалась от SQL , но с развитием реляционной технологии и увеличением мощности компьютеров быстродействие реляционных СУБД значительно выросло, а язык SQL может применяться в приложениях, где требуется обеспечить одновременный доступ к данным сотням пользователей с минимальным временем ожидания.
Возможность работы SQL в локальных сетях связана с поддержкой архитектуры клиент-сервер.
Классификация операторов SQL
Команды подключения к БД. Все современные БД поддерживают сетевую многопользовательскую работу. Сеанс работы начинается подключением пользователя к системе по команде CONNECT и заканчивается отключением от БД по команде DISCONNECT или аварийным завершением программы по команде EXIT. Подключение и отключение к БД происходит обычно в фоновом режиме, так как эти операторы выполняются программно. Сеансы доступа к данным могут автоматически отслеживаться администраторами БД, т.к. они связываются с учетными записями пользователей.
DDL – язык определения данных позволяет разработчику создавать различные объекты и информационные структуры. Основные команды языка:
CREATE TABLE- создание таблицы;
ALTER TABLE- модификация структуры таблицы;
DROP TABLE- удаление таблицы;
CREATE VIEW- создание представлений пользователя;
Представление является виртуальным именованным отношением, которого реально в базе данных не существует, но которое создаётся по требованию отдельного пользователя на время его работы в момент поступления этого требования.
DROP VIEW- удаление представлений пользователя;
CREATE INDEX- создание индекса;
Индекс – специальная таблица, которая используется для адресации записей по упорядоченным ключам. С этой таблицей связана процедура определения адреса записи во внешней памяти по значению ключа.
DROP INDEX- удаление индекса;
Описание созданных структур (скрипты создания) находится в системном каталоге БД.
DML - язык манипулирования данными позволяет разработчику и пользователю манипулировать данными внутри объектов БД:
INSERT- вставка (добавление в конец таблицы) новых строк;
UPDATE- обновление (модификация) записей;
DELETE- удаление записей;
Все команды манипулирования действуют только в рамках полномочий, предоставленных пользователю администратором БД.
DQL – язык запросов к данным представляет единственный оператор выбора SELECT. Его синтаксис содержит множество опций и параметров, позволяющих конструировать запросы любой сложности. Запрос - это команда, которая выполняется СУБД. Результат запроса отображается на экране компьютера или представляет собой входную информацию для выполнения другой команды, например, манипулирования данными. Все запросы в SQL состоят из одиночной команды, но в нее могут быть вложены другие. Структура этой команды обманчиво проста, потому что вы должны расширять ее так чтобы выполнить высоко сложные оценки и обработки данных.
SELECT * FROM TPol;
SELECT ФИО, Адрес FROM TAbonents;
Оператор DISTINCT
Удаление избыточных данных позволяет не выбирать из таблиц повторяющиеся группы записей. Очень часто используется, когда БД еще не приведена к нормальным формам. Аргумент DISTINCT - это "отличие".
SELECT DISTINCT Дата_установки FROM TAbonents;
Оператор ALL
При указании ALL выводятся все значения аргументов и дублирование строк вывода сохраняется. ALL - это пояснительный, а не действующий аргумент.
SELECT ALL Дата_установки FROM TAbonents;
Оператор WHERE
Наложение условий выбора реализуется с помощью ключевого слова WHERE, которое позволяет устанавливать предикаты, условие которых может быть или верным или неверным для любой строки таблицы. Команда SELECT извлекает только те строки таблицы, для которых такое утверждение верно.
SELECT * FROM TAbonents WHERE ПАСПОРТ=679237;
SELECT * FROM TAbonents WHERE (ПАСПОРТ = 679237) OR (ФИО = 'Жуйченко Женя');
SELECT * FROM TAbonents WHERE (ID = 3) AND (ФИО = 'Иванов Иван Иванович');
Операторы сравнения
Кроме равенства можно использовать другие операторы сравнения:
> Больше чем
< Меньше чем
>= Больше чем или равно
<= Меньше чем или равно
<> Не равно
SELECT * FROM TAbonents WHERE (ДАТА ВЫДАЧИ <> 6/01/1997);
Оператор NOT
Очень эффективным может быть применение логического отрицания:
SELECT * FROM TAbonents
WHERE NOT (ФИО = 'Иванов Иван Иванович');
SELECT * FROM TAbonents
WHERE NOT ((ФИО = 'Иванов Иван Иванович') AND (ID = 1));
Оператор IN
Оператор IN определяет набор (массив) значений, с элементами которого будет сравниваться текущее поле записи:
SELECT * FROM TAbonents
WHERE ФИО IN ('Иванов', 'Петров');
SELECT* FROM TAbonents
WHERE ПАСПОРТ IN (675537, 326751, 656729);
Оператор BETWEEN
Оператор BETWEEN позволяет указать, что значение должно находиться в указанном диапазоне значений (важен порядок значений!):
SELECT * FROM TAbonents
WHERE ПАСПОРТ BETWEEN 670000 AND 690000;
SELECT * FROM TAbonents
WHERE ФИО BETWEEN 'A' AND 'K';
Кстати "Кузьмин" не попадет в выборку, так как BETWEEN сравнивает строки неравной длины. Совместное использование нескольких операторов позволяет не включать границы диапазона:
SELECT * FROM TAbonents
WHERE (ПАСПОРТ BETWEEN 670000 AND 690000) AND NOT (ПАСПОРТ IN (670000, 690000));
Оператор LIKE
Оператор LIKE, который работает с полями типа CHAR или VARCHAR позволяет с помощью спец. символов указывать шаблоны похожих записей:
"_" замещает любой одиночный символ( 'b_t' -> 'bat', 'bit')
"%" замещает любую последовательность символов ('%p%t' -> 'put', 'posit')
SELECT * FROM TAbonents WHERE (АДРЕС LIKE 'ул%');
SELECT * FROM TAbonents WHERE (АДРЕС LIKE '%и%');
Если нужно найти строку, содержащую символ шаблона (процент или знак подчеркивания), следует определить одиночный символ как символ ESC. Слово ESC используется сразу перед процентом или подчеркиванием в предикате, и означает, что процент или подчеркивание будет интерпретироваться как символ значения, а не как символ маски.
SELECT * FROM TAbonents
WHERE Адрес LIKE '%а%_'ESCAPE;
Слово ESCAPE определяет % как символ ESC. Символ ESC, используемый в LIKE строке, сопровождается знаком процента, знаком подчеркивания, или знаком ESCAPE, который будет искаться в столбце, а не обрабатываться как групповой символ. Символ ESC должен быть одиночным символом и применяться только к одиночному символу сразу после него. В нашем примере символ процента (в начале) и символ процента (в окончании) обрабатываются как символы шаблона, а подчеркивание является искомым символом. Символ ESC может также использоваться самостоятельно. Для этого он вводится в строку шаблона дважды. Например, ищем строку '/_/'.
SELECT * FROM TAbonents
WHERE Адрес LIKE ' % /_ / %'ESCAPE;
Строка сравнивается с содержанием любой последовательности символов (%), после которой имеется комбинация искомая символов (/_/ ), после чего может находиться другая произвольная последовательность ( % ).
Оператор NULL
При работе с пустыми (не путать с нулевыми) значениями нолей следует использовать стандартную константу NULL ("пустой"). Она является значением любого поля, которое еще не заполнялось. Полю любого типа в качестве значения можно присвоить эту константу. Таким образом, это универсальное значение ("пустой") может являться универсальным маркером записи. На основании этого маркера происходит автоматический подсчет итогов в различных модификациях SQL (фраза - WITH ROLLUP). Но в реляционных структурах следует избегать возможности массового применения NULL. Так как это ставит под сомнение целостность данных.
Замечание! Так как NULL указывает на отсутствие значения, невозможно узнать каков будет результат любого сравнения с использованием NULL. Когда NULL сравнивается с любым значением, даже с другим таким же NULL, результат будет ни верным, ни неверным, он - неизвестен. Применение отрицания к неизвестной величине тоже ничего не дает:
NOT (неверное) - равняется верно,
NOT (неизвестное) - равняется неизвестно.
Значит, выражение типа 'ФИО = NULL' или 'ФИО IN (NULL)' будет неизвестно. Поэтому в SQL существует специальный оператор IS, который используется с ключевым словом NULL, для размещения значения NULL.
SELECT * FROM TAbonents WHERE Адрес IS NULL;
Существует два варианта записи выражения обратного действия:
SELECT * FROM TAbonents WHERE Адрес NOT NULL;
SELECT * FROM TAbonents WHERE NOT Адрес IS NULL;
DCL – язык управления доступом к данным (команды администрирования БД). В этот раздел входят команды, которые позволяют контролировать доступ пользователей к объектам БД, наделять их полномочиями.
CREATE USER- создание учетных записей;
ALTER USER - удаление учетных записей;
GRANT - привилегии доступа;
REVOKE - отмена привилегий;
ALTER PASSWORD – смена пароля;
Команды управления транзакциями. Транзакция - последовательность операций по изменению данных, которая может быть утверждена (Commit) либо отменена (RollBack). Использование транзакций позволяет корректно обрабатывать ошибочные ситуации, возникающие при одновременной работе с БД нескольких пользователей. Механизм транзакций реализован в современных СУБД.
Типы данных SQL. Оператор выбора SELECT
В языке SQL существует шесть скалярных типов данных, определённых стандартом ISO. В некоторых случаях в целях упрощения манипулирования и преобразования, а также из-за сходства основных свойств, данные типов character и bit объединяются под понятием �строковые типы данных�, а данные типов exact numeric и approximate numeric – под понятием �числовые типы данных�. Итак, перечислим шесть основных типов данных:
character - символьный;
bit – битовый
exact numeric – точные числа
approximate numeric – округлённые числа
datetime – дата/время
interval – интервал
Символьные данные (тип character)
Символьные данные состоят из последовательностей символов, входящих в определенный создателями СУБД набор символов. Поскольку наборы символов являются специфическими для различных диалектов языка SQL, перечень символов, которые могут входить в состав значений данных символьного типа, также зависит от конкретной реализации. Чаще всего используются наборы символов ASCII и EBCDIC. Для определения данных символьного типа используется следующий формат:
CHARACTER [VARYING] [length]
При определении столбца с символьным типом данных параметр length используются для указания максимального количества символов, которые могут быть помещены в данный столбец (по умолчанию принимается значение 1). Символьная строка может быть определена как имеющая фиксированную или переменную (VARYING) длину. Если строка определена с фиксированной длинной значений, то при вводе в нее меньшего количества символов значение дополняется до указанной длины пробелами, добавляемыми справа. Если строка определена с переменной длинной значений, то при вводе в нее меньшего количества символов в базе данных будут сохранены только введенные символы, что позволяет достичь определенной экономии внешней памяти. Например, столбец NIN таблицы Staff (национальный страховой номер) имеет фиксированную длину в девять символов и может быть объявлен следующим образом:
nin CHAR(30)
Столбец Address таблицы Staff имеет переменную длину значения (максимум до 30 символов).
Битовые данные (тип bit)
Битовый тип данных используется для определения битовых строк, т.е. последовательности двоичных цифр (битов), каждая из которых может иметь значение либо 0, либо 1. Для определения данных битового типа используется формат, сходный с определением символьных данных:
BIT [VARYING] [length]
Точные числа (тип exact numeric)
Тип точных числовых данных используется для определения чисел, которые имеют точное представление в компьютере. Числа состоят из цифр, необязательной десятичной точки и необязательного символа знака. Данные точного числового типа определяются значностью (precision) и длиной дробной части (scale). Значность задает общее количество значащих десятичных цифр числа, в которое входят длина целой и дробной частей, но без учета самой десятичной точки. Дробная часть указывает количество дробных десятичных разрядов числа. Например, точное число -12.345 имеет значность, равную 5 цифрам, и дробную часть длиной 3. Особой разновидностью точных чисел являются целые числа. Существует несколько способов определения данных точного числового типа:
NUMERIC [ precision [, scale] ]
DECIMAL [ precision [, scale] ]
INTEGER
SMALLINT
INTEGER может быть сокращено до INT a DECIMAL до DEC
Типы NUMERIC и DECIMAL предназначены для хранения чисел в десятичном формате. По умолчанию длина дробной части равна нулю, а принимаемая по умолчанию значность зависит от реализации. Тип INTEGER используется для хранения больших положительных или отрицательных целых чисел. Тип SMALLINT используется для хранения небольших положительных или отрицательных целых чисел. При использовании этого типа данных расход внешней памяти существенно сокращается. Максимальное абсолютное значение числа, которое может сохраняться в столбцах с типом данных SMALLINT, составляет 32 767. Для столбца Rooms таблицы Property for_Rent,, в котором сохраняются сведения о количестве комнат сдаваемого в аренду объекта, можно выбрать тип SMALLINT и объявить его следующим образом:
rooms SMALLINT
Столбец Salary таблицы Staff может быть объявлен следующим образом:
salary DECIMAL(7,2)
В этом случае максимальное значение заработной платы составит 99 999.99 фунтов стерлингов.
Округленные числа (тип approximate numeric)
Тип округленных чисел используется для описания данных, которые нельзя точно представить в компьютере, - например, действительных чисел. Округленные числа или числа с плавающей точкой представляются в научной нотации, при которой число записывается с помощью мантиссы, умноженной на определенную степень десятки (порядок), например: 10ЕЗ, +5.2Е6, -0.2Е-4. Существует несколько способов определения данных с типом округленных чисел:
FLOAT [precision]
REAL
DOUBLE PRECISION
Параметр precision задает значность мантиссы. Значность определений типа REAL и DOUBLE PRECISION зависит от конкретной реализации.
Дата и время (тип datetime)
Тип данных "дата/время" используется для определения моментов времени с некоторой установленной точностью. Примерами являются даты, отметки времени и время суток. Стандарт ISO разделяет тип данных "дата/время" на подтипы YEAR (Год), MONTH (Месяц), DAY (День), HOUR (Час), MINUTE (Минута), SECOND (Секунда), TIMEZONEJIOUR (Зональный час) и TIMEZONEJ1INUTE (Зональная минута). Два последних типа определяют час и минуты сдвига зонального времени по отношению к универсальному координатному времени (Гринвичскому времени). Поддерживается три типа полей даты/времени:
DATE
TIME [time__precislon] [WITH TIME ZONE]
TIMESTAMP [time__precislon] [WITH TIME ZONE]
Тип данных DATE используется для хранения календарных дат, включающих поля YEAR, MONTH и DAY. Тип данных TIME используется для хранения отметок времени, включающих поля HOUR, MINUTE и SECOND. Тип данных TIMESTAMP используется для совместного хранения даты и момента времени. Параметр time precision задает количество дробных десятичных знаков, определяющих точность сохранения значения в поле SECOND. Если этот параметр опускается, по умолчанию его значение для столбцов типа TIME принимается равным нулю (т.е. сохраняются целые секунды), тогда как для полей типа TIMESTAMP он принимается равным 6 (т.е. отметки времени сохраняются с точностью до миллисекунд). Наличие ключевого слова WITH TIME ZONE определяет использование полей TIMEZONE HOUR и TIMEZONE MINUTE.
Данные типа interval
Данные с типом интервала используются для представления периодов времени. Любой интервальный тип данных состоит из набора отдельных полей: YEAR, MONTH, DAY, HOUR, MINUTE и SECOND. Существует два класса данных с интервальным типом: интервалы год-месяц и интервалы день-время. В первом случае данные включают только два поля - YEAR и/или MONTH. Данные второго типа могут состоять из произвольной последовательности полей DAY, HOUR, MINUTE, SECOND. Данные интервального типа определяются следующим образом:
INTERVAL {{start_field TO end_field} single_datetime_field} start__ field = YEAR | MONTH | DAY [ HOUR | MINUTE
[(interval leading field precision)] end__field = YEAR [ MONTH | DAY | HOUR | MINUTE | SECOND
[(fractional seconds precision)] single__datetlme_field = start_field | SECOND
[(interval leading field precision
[f fractional seconds precision])]
Во всех случаях для параметра start_field необходимо указать размерность первого поля (interval leading field precision), которая по умолчанию принимается равной двум. Например:
INTERVAL YEAR(2) ТО MONTH
Это объявление описывает интервал времени, значение которого может находиться между 0 годом, 0 месяцем и 99 годом, 11 месяцем. Еще один пример:
INTERVAL HOUR TO SECOND(4)
Это объявление описывает интервал времени, значение которого может меняться от 0 часов, 0 минут, 0 секунд до 99 часов, 59 минут 59.9999 секунды. (Значность первого поля по умолчанию устанавливается равной 2, а число дробных десятичных знаков для секунд явно задано равным 4.)
Скалярные операторы
Язык SQL включает некоторое количество встроенных скалярных операторов и функций, которые могут использоваться для построения скалярных выражений, т.е. выражений, вычисление которых дает скалярный результат. Помимо обычных арифметических операторов (+, -, * и /) в языке определены и другие операторы, представленные в табл. 13.43.
BIT_LENGTH - Возвращает длину заданной строки в битах.
OCTETJLENGTH - Возвращает длину заданной строки в октетах (длина в битах, деленная на 8).
CHAR_LENGTH - Возвращает длину заданной строки в символах (или в октетах, если строка является битовой строкой). Например, результат вычисления выражения CHAR_LENGTH('Beech') равен 5.
CAST - Преобразует значение выражения, построенного из данных одного типа, в значение другого типа данных. Например, в результате вычисления выражения CAST(5E3 AS INTEGER) будет получено значение 5000.
| | - Оператор конкатенации. Соединенные этим оператором две символьные или битовые строки преобразуются в одну строку. Например, выражение fname || Iname позволяет объединить имя и фамилию работника в одну символьную строку.
CURRENT USER или USER - Функция возвращает символьную строку, представляющую собой идентификатор (или, как принято говорить, имя) текущего пользователя/
SESSION_USER - Функция возвращает символьную строку, представляющую собой идентификатор текущего сеанса SQL.
Оператор выбора SELECT. Формирование запросов из базы данных
Команда является мощным средством обработки запросов. С ее помощью из базы-источника выделяются нужные данные и пересылаются на экран или в файл-приемник. Данные могут быть извлечены из ратных баз, а также сгруппированы и упорядочены желаемым образом.
Команда имеет массу опций-возможностей. Ввиду этого сначала приведем ее предварительный синтаксис, который позволит затем лучше осознать детали.
SELECT <что выводится>
FROM <откуда (источник)> INTO <куда (получатель)>
WHERE <каким условиям должно отвечать>
GROUP BY <колонки, по которым выполняется группирование>
HAVING <условие группирования записей в одну строку >
ORDER BY <в каком порядке выведать данные>
Команда SELECT и вообще команды SQL мало зависят от текущего состояния среды FoxPro. Они сами открывают нужные им базы данных и индексные файлы. Если необходимых для выполнения команды индексов нет, они будут созданы, а по завершении команды уничтожены. Однако, конечно, лучше, чтобы применялись готовые индексы - для этого они должны быть открыты (используются только компактные индексы). Исключение составляют структурные CDX. индексы. Открытие соответствующей базы данных (например, с помощью команды SELECT) открывает и индекс. Теперь рассмотрим ее более полный формат.
SELECT
[DISTINCT] [<псевдоним>.]<выражение> [AS <колонка>]
[,[<псевдоним>.]<выражение> [AS <колонка>]...]
FROM <БД> [,<БД>...]
[[INTO <получатель> ]/[ТО FILE <файл>
[ADDITIVE]/TO PRINTER]]
[NOCONSOLE] [PLAIN] [NOWAIT]
[WHERE <условие связи>
[AND <условие связи>...]
[AND/OR <условие отбора>
[AND/OR <условие отбора>...]]]
[GROUP BY <колонка> [, <колонка>...]]
[HAVING <условие отбора> ] [ORDER BY <колонка> [ASC/DESC]
[,<колонка> [ASC/DESC]...]]
Термин "колонка" здесь очень близок к понятию "поле базы" данных, но может быть и выражением. Кроме того, вследствие выборки мы можем получить как новую базу данных, так и текстовый файл или даже только отображение на экране, т.е. колонки.
Команда SELECT допускает включение в себя других внутренних команд SELECT (формирование подзапросов).
Все примеры использования команды SELECT сгруппированы в конце раздела. Сейчас рассмотрим опции команды.
Указание результатов выборки и источников данных
SELECT [DISTINCT] [<псевдоним>.]<выражение> [AS <колонка>]
FROM <БД> [<псевдоним>] [,<БД> [<псевдоним>]...]
Здесь указывается, что и откуда берется в выборку. Перед словом FROM перечисляются отбираемые <выражения>, а после - имена баз, из которых берутся данные.
<Выражение> может быть полем записи из БД, константой (выводимой в каждой строке выборки), функцией (в том числе и ПФ) от переменных, полей и т.п. Если <выражение> является именем поля, то оно может быть составным (с включением имени базы данных или псевдонима), в особенности если выборка делается из нескольких баз, где имена полей совпадают, например, поля TAB из баз KADR.DBF и BRIG1.DBF. Псевдонимом может быть не только "официальный" псевдоним (ALIAS) базы, но и любое другое имя, которое вы ей присвоите в команде SELECT. Это задаваемое временное имя указывается в опции <псевдоним> после слова FROM. Никаких последствий за пределами команды SELECT такое назначение не имеет.
Если необходимо построить выборку из всех полей базы, вместо их перечня можно указать символ “*”.
В результате выполненной выборки получается совокупность колонок, заголовками которых могут быть имена полей. Если имена совпадают, то такие колонки получают совпадающие имена, к которым присоединяется одна из букв (по алфавиту), например ТАВ_А, TABJB и т.д. Аналогичным образом даются имена колонкам, полученным в результате вычисления выражений. Их имена состоят из слова ЕХР и последовательных чисел. Так, возможны имена ЕХР_1, ЕХР_2 и т.д. Исключения составляют выражения, использующие собственные функции SQL, например функции MINQ, MAXQ и др. Имена колонок в этом случае будут включать имена функций.
Если нас не устраивают имена, формируемые по умолчанию, можно назначить свои, указав их после слова AS в виде
<выражение> AS <новое имя колонки>
Такое переименование имеет смысл, если выборка помещается во вновь создаваемую базу данных. Имя дается по правилам, принятым в FoxPro, и должно быть задано латинскими символами.
В <выражении> могут быть использованы любые функции FoxPro. Кроме того, здесь есть еще собственные специальные арифметические функции, действующие "по вертикали". Это функции вычисления среднего, минимального и максимального значений, суммирования, а также количества записей:
AVG(<выр>), MIN(<выр>), МАХ(<выр>), SUM(<выр>), COUNT(<выр>).
Последняя функция может иметь в качестве аргумента звездочку (COUNT(*)), что означает подсчет всех записей, попавших в выборку.
Включение опции DISTINCT исключает возможность вывода одинаковых строк в выборке.
Указание объекта, куда пересылается выборка
Следующие опции задают "получатель" данных выборки. Им может быть база данных, массив, текстовый файл, экран и принтер.
Кроме того, информация может быть переслана в так называемый Курсор (будем писать с большой буквы). Курсор - это временный набор данных, который может быть областью памяти или временным файлом FoxPro (этот процесс от нас не зависит) и имеет режим "Только чтение". Данные Курсора могут быть, например, предъявлены в команде BROWSE, напечатаны, из них может быть образовано меню и т.д. Курсор может быть обработан другой командой SELECT. К колонкам Курсора надо обращаться по имени этих колонок, возможно, с префиксом - именем Курсора (через точку).
Итак:
INTO <получатель>
<Получатель> может быть одного из следующих типов:
ARRAY <массив> - задается вновь создаваемый двумерный <массив>.
CURSOR <курсор> - задается имя Курсора.
DBF/TABLE <БД> - новая база данных с указанным именем.
Слова DBF и TABLE здесь являются синонимами.
Кроме того, данные можно переслать в файл или на принтер.
ТО FILE <файл> [ADDITIVE]/TO PRINTER - выборка посылается в текстовый <файл> или на принтер. Если используется слово ADDITIVE, то выборка будет добавлена в конец существующего файла без его перезаписи.
Следующие опции имеют смысл только при выдаче на экран (команда используется без слова INTO):
NOCONSOLE - выборка не выдается на экран.
PLAIN - заголовки колонок не выдаются.
NOWAIT - не делаются паузы при заполнении экрана.
Критерий отбора данных
WHERE <условие связи> [AND <условие связи>...]
[AND/OR <условие отбора> [AND/OR <условие отбора>...]]
Здесь:
<Условие связи> - применяется в случае, если выборка делается более, чем из одной базы данных, и указывает критерий, которому должны отвечать поля из разных баз. В условии связи указываются поля из разных баз. Здесь разрешается использовать знаки отношения =, #, <, >, >=, <=, <>. Допускается задание нескольких критериев, соединенных знаком AND.
<Условие отбора> - строится аналогично, но из выражений только для одной базы, и допускается использование логических операторов OR и NOT.
Условия, кроме любых функций FoxPro, могут содержать следующие операторы SQL:
LIKE - позволяет построить условие сравнения по шаблону, где символ "_" указывает единичный неопределенный символ в строке, "%" - любое их количество. Эти символы аналогичны символам маски "?" и "*" в MS DOS. Формат оператора:
<выражение> LIKE <шаблон>
BETWEEN - проверяет, находится ли выражение в указанном диапазоне. Формат оператора: <выражение> BETWEEN <нижнее знач.> AND <верхнее знач.>
IN - проверяет, находится ли выражение, стоящее слева от слова IN, среди перечисленных справа от него (аналогично функции INLIST). Формат оператора:
<выражение> IN (<выражение>,<выражение>,...)
Все указанные операторы можно комбинировать с помощью связок OR, AND, NOT и скобок. Операторы LIKE и BETWEEN не следует путать с одноименными функциями FoxPro, которые, впрочем, тоже можно использовать.
Группированные данных
GROUP BY <колонка>[,<колонка>...] – задаются колонки, по которым производится группирование выходных данных. Все записи базы, для которых значения колонок совпадают, отображаются в выборке единственной строкой. Группирование удобно для получения некоторых сводных характеристик (сумм, количеств) группы.
HAVING <условие отбора > - опция задает критерий отбора данных в каждую сформированную в процессе выборки группу.
Сортировка
ORDER BY <колонка> [ASC/DESC][,<Колонка> [ASC/DESC]...] -опция задает упорядочение по заданной колонке/колонкам. По умолчанию сортировка выполняется по возрастанию (ASC), но может быть задана и по убыванию (DESC).
Примеры запросов
1) Выборка всех полей из базы KADR.DBF. Все колонки выборки будут иметь имена полей базы данных.
select * FROM kadr
2) Вывод минимального, максимального и среднего значений поля szar (средняя зарплата). Колонки получат имена MIN_SZAR, MAX_SZAR и AVG_SZAR.
SELECT MIN(szar),MAX(szar),AVG(szar) FROM kadr
3) Вывод фамилий работников, получающих от 300000 до 800000 рублей.
SELECT fam FROM kadr;
WHERE szar BETWEEN 300000 AND 800000
Вывод фамилий всех сотрудников, кроме работающих в подразделениях ОГМ и КБ.
SELECT fam FROM kadr WHERE podr NOT IN ('ОГМ’, ‘КБ’ )
4) Выборка названий всех подразделений (поле PODR) предприятия из базы KADR.DBF. Опция DISINST предотвращает повторный вывод одних и тех же названий, если они повторяются.
SELECT DISTINCT podr FROM kadr
5) Выборка фамилий (FAM) всех мужчин из KADR.DBF.
SELECT fam FROM kadr WHERE pol='M'
Агрегатные функции, вложенные запросы в операторе выбора.
Внутри запросов можно производить и типовые статистические вычисления (нахождение максимума, минимума, среднего значения...), что очень широко используется в практике. Все это делается с помощью агрегатных функций, которые производят действия относительно группы записей, а результате выдают одиночное значение:
COUNT- подсчет количества (не NULL значений полей) записей;
SUM- подсчет арифметической суммы всех значений поля;
AVG- усреднение всех выбранных значений данного поля;
МАХ- нахождение наибольшего из всех выбранных значений;
MIN- нахождение наименьшее из всех выбранных значений.
Агрегатные функции используются подобно именам полей в предложеные SELECT запроса, но с одним исключением, они берут имена поля как аргументы. Только числовые поля могут использоваться с SUM и AVG. COUNT, MAX, и MIN, могут использоваться и строковые поля (переход эквиваленту ASCII).
ID
Смена
Объем
День
1
Иванов
3
понедельник
2
Петров
10
понедельник
3
Сидоров
6
понедельник
4
Гришин
4
понедельник
5
Иванов
2
среда
6
Сидоров
3
среда
7
Иванов
5
пятница
8
Гришин
4
пятница
9
Иванов
5
пятница
10
Сидоров
4
среда
SELECT SUM (Объем) FROM TWork; (результат 46)
SELECT AVG (Объем) FROM TWork; (результат 4.6)
SELECT МАХ(Объем) FROM Twork
WHERE (День LIKE 'п%'); (результат 10)
SELECT МIN(Объем) FROM TWork
WHERE (Смена = 'Иванов'); (результат 2)
SELECT COUNT(ID) FROM Twork
WHERE (Смена LIKE '%ов'); (результат 8)
Если параметром COUNT является звездочка ("любая запись"), то результате подсчитываются пустые записи (NULL) и дубликаты других записей.
SELECT COUNT(*) FROM Twork
WHERE (Смена LIKE '%ов');(результат 32)
Наложение требования DISTINCT позволяет сузить расчетный диапазон. Именно совместное использование таких операторов позволяет увеличит надежность расчетов, так как в реальных БД обычно присутствуют дубликаты записей.
SELECT COUNT(DISTINCT День) FROM Twork; (результат 3)
Использование агрегатных функций позволяет производить и статистическую обработку расчетных показателей. В физических таблицах БД не принято хранить расчетные данные, так как их можно получить в результате запроса с использованием агрегатных функций.
ID
Смена
Объем
День
Цена
Брак
1
1
3
1
10
1
2
2
10
1
8
12
3
3
6
1
10
2
4
4
4
1
5
2
5
1
2
2
5
3
6
3
3
2
5
0
7
1
5
3
5
0
8
4
4
3
5
0
9
1
5
3
10
2
10
3
4
2
10
2
11
3
4
2
5
1
TFIOTDay
ID
ФИО
1
Иванов
2
Петров
3
Сидоров
4
Гришин
ID
День
1
понедельник
2
среда
3
пятница
До этого мы использовали агрегатные функции с аргументами в виде одиночных полей. Но следует знать о возможности использовать этих функций с аргументами, которые состоят из скалярных выражений, объединяющих включающих одно или более полей. Разумеется, оператор DISTINCT в таких выражениях неприменим. Например, подсчитаем на какую сумму в рублях было произведено деталей в среду.
SELECT SUM(Twork.Oбъем * Twork.Цeнa) FROM Twork, Tday WHERE (Tday.ID = Twork.День) AND (Tday.День = 'среда');
Найдем наибольшую долю брака при работе токаря Петрова.
SELECT MAX(Twork.Бpaк / (Twork.Oбъём + Twork.Брак )) FROM Twork, TFIO WHERE (TFIO.ID = Twork.Смена) AND (TFIO.ФИО = 'Петров');
Применение агрегатных функций значительно облегчает несложную статистическую обработку данных. Однако агрегатные функции работают внутри групп. Группа - это набор записей, у которых совпадает набор ключевых полей. Все остальные поля группы должны быть приведены к единому значению.
В простейших случаях группой выступает вся таблица, среди всей записей которой производится действие агрегирования.
SELECT МАХ(Брак) FROM Twork;
Если в операторе SELECT используется набор условий, то искомой группой записей является набор данных, полностью удовлетворяющий всем условиям директивы WHERE. Следовательно, группа записей, в которой происходит поиск максимального показателя отбраковки, содержит только записи с информацией о работе токаря Петрова.
SELECT МАХ(Брак) FROM Twork WHERE (Смена = 2);
Если в операторе SELECT используется агрегирование группы полученной в результате объединения данных нескольких таблиц, эта группа формируется с помощью явного указанного соответствия между первичным внешними ключами таблиц. В противном случае результирующей группой записей оказывается декартовое ("каждый с каждым") произведение записей всех самостоятельных групп, относящихся к различным таблицам.
SELECT MAX(Twork.Бpaк / (Twork.Объем + Twork.Брак )) FROM Twork, TFIO WHERE (TFIO.ID = Twork.Смена) AND (ТFIO.ФИО LIKE 'И%');
Конечно, этот запрос явно недоработан (декартовое произведение):
SELECT MAX(Twork.Бpaк / (Twork.Объем + Twork.Брак )) FROM Twork, TFIO WHERE (ТFIO.ФИО = LIKE 'И%');
На этом этапе освоения SQL мы можем формировать группы только с помощью наложения комбинации условий. В результате таких запросов получается единая группа записей, к которой применяется единственная функция агрегирования. Следовательно, вычислить минимальную месячную зарплату рабочего, прогулявшего за год максимальное количество смен невозможно. Для этого придется изучить другие возможности SQL.
Операторы манипулирования данными
Язык манипулирования данными (DML) является важной частью SQL, без которой было бы невозможно добавлять данные в таблицы, модифицировать или удалять их. Такие действия только кажутся простыми, так как все директивы предназначены для групповых операций. Все команды этого раздела подразумевают неявное использование оператора SELECT в своем синтаксисе. Только написание грамотного отсекающего запроса позволит внести корректные изменения в таблицу.
Команда INSERT
Эта директива позволяет вставлять (добавлять в конец таблицы) новые строки. В одном операторе можно добавить как одну, так и несколько новых записей. Оператор INSERT позволяет вносить данные в указанные столбцы таблицы или во все ее поля.
Например, внесем в таблицу Т1, обладающую структурой FIO, PASPORT новую запись:
INSERT INTO T1
VALUES ('Иванов И.И.', 546237);
Таким образом, мы попытались занести данные в таблицу Т1. Однако этого вполне может и не получиться, так как совсем неочевидно какой столбец таблицы мы хотим занести фамилию, а и какой номер паспорта. Следующий оператор считается более корректным:
INSERT INTO T1 (FIO, PASPORT) VALUES ('Иванов И.И.', 546237);
На этом примере мы должны уяснить, что при добавлении данных в таблицы необходимо указывать имена соответствующих столбцов. Конечно, это условие выполнять необязательно, но получившийся результат может удивить разработчика. При вставке новой строки можно заполнять данными не все поля:
INSERT INTO T1 (FIO) VALUES ('Петров П.П.');
В этом случае незаполненные в операторе INSERT столбцы заполняются NULL-значениями, что не всегда допустимо. Конечно, если на столбец наложено ограничение NOT NULL, такая директива приведет к ошибке. Если значение NOT NULL столбца предугадать в момент вставки записи невозможно, следует разработать триггер, срабатывающий при вставке новой записи и заполняющий определенные поля таблицы. Только в этом случае выполнение оператора не приведет к ошибке исполнения.
Мы рассмотрели примеры операторов добавления данных к таблице по одной записи. Однако с помощью оператора INSERT можно осуществлять ввод данных на основе запроса из другой таблицы:
INSERT INTO T1 (FIO)
SELECT FIO FROM T2;
В результате, выполнив выборку столбца FIO из таблицы Т2, весь полученный набор строк мы добавляем к таблице Т1, подразумевая что номер паспорта будет заполняться NULL.
К выполнению таких действий нужно подходить с осторожностью, так как нет полной уверенности, что данные, извлекаемые из одной таблицы, имеют соответствующую структуру (типы и размерности полей). Для того чтобы избежать возможную ошибочную ситуацию используются операторы конкатенации и преобразования:
INSERT INTO T1 (FIO)
SELECT UPPER(F)+' '+UPPER(I)+' '+UPPER(O) FROM T2;(соединяются через пробелы три строчных переменные F, I, O из таблицы Т2 и добавляются в поле FIO таблицы Т1).
При работе с разнородными данными следует быть еще внимательнее:
INSERT INTO Т4 (ALL_PASPORT)
SELECT UPPER(PASP_S) + '-' + CStr(PASP_N) FROM T5;(соединяются через тире PASP_S и PASP_N таблицы Т5, кроме того PASP_N преобразуется из числового в строчный и добавляются в поле ALL_PASPORT таблицы Т4).
Однако единых рекомендаций по выполнению таких выборок нет и быть не может, так как синтаксис функций преобразования различных реализаций SQL очень отличается. Некоторые (устаревшие) СУБД вообще не поддерживают возможности преобразования типов внутри запросов.
В ряде случаев может потребоваться заполнить добавляемую к таблице запись константами или NULL-значениями. В таком случае синтаксис оператора INSERT будет следующим:
INSERT INTO T1 (FIO, PASPORT, ADDRESS)
SELECT FIO, 123456, NULL FROM T2;
Конечно, этот оператор сработает только в одном случае, если на столбец PASPORT не наложено ограничение уникальности UNIQUE и длина этого поля не менее 6-ти знаков.
Команда UPDATE
Эта команда не изменяет общего количества записей, хранящихся в таблице. Она выполняет действие "обновление" ко всем записям, удовлетворяющих условиям вложенного подзапроса.
UPDATE T1
SET FIO = 'Иванов И.И.';
Конечно, хотя такой оператор записан в соответствии с синтаксисом SQL его действие в принципе некорректно. Дело в том, что мы заполняем поля FIO строкой 'Иванов И.И.' для всех строк таблицы Т1, а не применяем это действие к одной строке. Следующий оператор будет гораздо более корректным:
UPDATE T1
SET FIO = 'Иванов И.И.'
WHERE (PASPORT= 786324);
С помощью директивы UPDATE можно изменить значение сразу нескольких полей одной таблицы.
UPDATE T1
SET FIO= 'Прогульщик',
STATUS = 'УВОЛЕН',
PASPORT = 0 WHERE (ALL_PROGUL > 10);
Этот оператор для всех строк таблицы Т1, в которых соблюдается условие строки WHERE произведет заполнение полей FIO, STATUS и PASPORT указанными значениями.
Задумайтесь над корректностью следующего оператора UPDATE, который пытается изменить содержимое поля первичного ключа. Какие из перечисленных ниже действий принципиально некорректны?
UPDATE T1 SET ID = 100;
UPDATE T1 SET ID = 100 WHERE ID = 30;
UPDATE T1 SET ID = ID + 1;
UPDATE T1 SET ID = PASPORT;
Даже если некоторые из этих действий (при определенных условиях) не выдадут сообщение об ошибке, постарайтесь никогда не использовать подобные конструкции. Оцените корректность других операторов, изменяющих значение внешних ключей:
UPDATE T2 SET T1_ID = 100;
UPDATE T2 SET T1_ID = T1_ID+1;
UPDATE T2 SET T1_ID = (SELECT MAX(ID) FROM T1);
UPDATE T2 SET T1_ID = (SELECT MIN(ID) FROM T1) WHERE (T1_ID = (SELECT MAX(ID) FROM T1);
На основании приведенных примеров можно сделать вывод, что поле внешнего ключа таблицы можно изменять, только если при этом не возникает нарушений целостности БД.
Команда DELETE
Команда предназначена для удаления записей, удовлетворяющих условиям подзапроса, из указанной таблицы. Директива DELETE не может удалять столбцы (изменяет структуру таблицы команда ALTER TABLE) и не может удалить саму таблицу (DROP TABLE). Команда DELETE не может также удалить или очистить содержимое отдельных полей (UPDATE).
DELETE FROM T1;
DELETE FROM T1 WHERE (ID = 124;
DELETE FROM T1
WHERE (FIO LIKE '%Иванов%') or (PASPORT = 786324);
DELETE FROM T1
WHERE (OKLAD < (SELECT AVG(OKLAD) FROM T1));
Так как все операторы раздела DML можно считать "потоковыми", выполнение всех действий будет происходить не поштучно, а большими синхронными порциями (итерациями). Это происходит потому, что СУБД является не интерпретатором, а транслятором стандартных команд.
Работа с триггерами
К командам манипуляции данных можно отнести хранимые процедуры и триггеры. Триггер - это несложная откомпилированная процедура, относящаяся к определенной таблице. Триггеры срабатывают при возникновении одного из ряда событий: вставка в таблицу новой строки, обновление данных или их удаление. В этот момент включается триггер, который предназначен для обработки соответствующего события.
Триггеры обычно используются для контроля целостности данных, но они позволяют отменять транзакции и получать данные из других таблиц.
Оператор CREATE TRIGGER
Из-за серьезного расхождения в синтаксисе оператора в различных реализациях SQL приведем два наиболее часто встречающихся шаблона:
Стандарт Microsoft:
CREATE TRIGGER ИМЯ_ТРИГГЕРА ON ИМЯ_ТАБЛИЦЫ
FOR {INSERT или UPDATE или DELETE} AS
Операторы SQL RETURN;
Стандарт Oracle:
CREATE OR REPLACE TRIGGER ИМЯ_ТРИГГЕРА BEFORE или AFTER INSERT или UPDATE или DELETE ON ИМЯ_ТАБЛИЦЫ
FOR EACH ROW WHEN УСЛОВИЕ Операторы SQL;
Например, триггер, заполняющий поле первичного ключа таблицы (счетчик – в Microsoft Access), в БД InterBase сильно напоминает стандарт Microsoft:
CREATE TRIGGER TR_ID_T1 FOR T1
BEFORE INSERT AS
BEGIN
NEW.ID = GEN_ID(GEN_ID_T1, 1); END;
Однако этот триггер будет работать только, если генератор GEN_ID_T1 существует и имеет определенное значение:
CREATE GENERATOR GEN_ID_T1; SET GENERATOR GEN_ID_T1 to 1;
Так как триггер не является информационным объектом, модифицировать его нельзя. Ошибочно созданный триггер может быть только удален соответствующей директивой.
Оператор DROP TRIGGER
Эта команда позволяет удалить триггер из БД, обратившись к нему по его имени. Опасайтесь удалений триггеров, отвечающих за каскадные изменения данных - при этом теряется контроль над целостностью БД.
DROP TRIGGER ИМЯ ТРИГГЕРА;
После всего этого, была получена директива начальства: "Удалить из списка постоянных покупателей отдел ЦКБ за несвоевременность расчетов и малые объемы заказов". Подразумевается, что наше руководство отдает себе отчет в том, что будет произведено не только удаление самого покупателя, но и всех сделок, которые он совершил. Следующий оператор, разумеется, приведет к очевидной ошибке:
DELETE FROM T_Customer
WHERE (Name LIKE '%ЦКБ%');
Корректной будет лишь следующая цепочка операторов:
DELETE FROM T_Sell WHERE (Customer_ID = 2);
DELETE FROM T_Customer WHERE (ID = 2);
Если такая ситуация повторяется периодически рационально разработать триггер (BEFORE DELETE), который перед удалением записи в T_Customer: будет производить удаление всех связанных записей в T_Sell.
Подсказка: Разумеется, в теле триггера может присутствовать несколько операторов, хотя в данном случае достаточно одного. В коде триггера можно использовать следующие объекты: NEW и OLD, которые являются указателям на запись таблицы, над которой осуществляется действие.
NEW - это "новая" запись или ее новое состояние;
OLD - это "старая" запись или ее первоначальное состояние. Например, увеличим стоимость на 25 %
NEW.Cost = OLD.Cost *1,25
После того как триггер создан, удаление покупателя можно произвести одни оператором:
DELETE FROM T_Customer WHERE (Name LIKE '%ЦКБ%');
Модели "Клиент-сервер" в технологии баз данных
В зависимости от взаимного расположения программы и хранилища данных можно выделить типы БД: локальные и удаленные. Для работы с локальными БД используются "локальные" приложения, а для работы с удаленными данными - "клиент - серверные" приложения.
Локальные БД располагаются на том же компьютере, что и приложение, но тоже требуют наличия поставщика данных. Работа, как правило, осуществляется в однопользовательском режиме. Контроль за одновременным доступом к данным обычно реализуется механизмами блокировки.
При работе с локальной БД в сети возможна организация многопользовательского доступа. В этом случае файлы БД и соответствующее приложение располагается на сервере сети. Каждый клиент запускает хранящиеся на сервере приложение, в результате чего на его компьютере выполняется копия этой программы. При работе с БД на каждом компьютере клиента локально используется копия БД. Эта копия периодически обновляется, что вызывает множество проблем и конфликтных ситуаций. Такой вариант использования ресурсов сети соответствует архитектуре "файл-сервер" и считается устаревшим. Он наделен следующими недостатками:
Пользователь работает с локальной копией БД, содержимое которой обновляется при формировании нового запроса. При этом с сервера пересылается новая копия всей таблицы. В результате - резко возрастает нагрузка сети.
Изменения, сделанные одним пользователем, в течении некоторого времени являются неизвестными другим пользователям. Необходимость синхронизации данных вынуждает использовать блокировку записей, что приводит к паузам.
Работа с БД осуществляется с разных компьютеров, причем контроль доступа очень условен.
Ввиду столь явных недостатков была предложена совершенно другая архитектура, названная "клиент - сервер". В соответствии с этой архитектурой, удаленная БД размещается на сервере, а приложение, осуществляющее работу с ней на компьютере пользователя (клиенте). В результате система делится на две разнородные и обладающие разным функционалом части.
Клиентом называется не только компьютер пользователя, но и приложение, которое формирует и отсылает запрос удаленному серверу. Запрос формируется на языке SQL, который является стандартным средством доступа к реляционным данным. После получения запроса удаленный сервер направляет его SQL-серверу (серверу БД), который выполняет запрос и возвращает результат.
Наряду с появившимися проблемами по установке ПО на всех компьютерах клиентов, технология обладает рядом существенных достоинств:
Необычайно снижается трафик сети;
Повышение безопасности информации, связанное с тем, что обработка запросов всех клиентов выполняется единой программой, расположенной на сервере. Сервер устанавливает общие для всех пользователей правила работы с БД, управляя режимами доступа клиентов к данным. Он же запрещает изменение одной записи разными пользователями.
Резкое уменьшение сложности клиентских приложений за счет отсутствия в них кода, связанного с контролем за состоянием БД и разграничения доступа к ней.
Для реализации архитектуры "клиент-сервер" используются многопользовательские СУБД (Oracle, Microsoft SQL Server). Описанная архитектура является двухуровневой. Клиентское приложение в ней называют "сильным" или "толстым" клиентом.
Развитие архитектуры "клиент-сервер" привело к появлению трехуровневой архитектуры, в которой кроме сервера и приложений клиентов присутствует еще и сервер приложений. Он является промежуточным звеном между сервером БД и клиентами.
Выгоды этой технологии очевидны:
Явное упрощение клиентских приложений и их настроек;
Разгрузка сервера БД (часть функций выполняет сервер приложений);
Единое поведение всех клиентов и независимость от платформы. Такие системы, основанные на трехуровневой сетевой архитектуре, называют также распределенными (см. аналогию с распределенной БД). Однако разработка таких систем достаточно проблемна, так как требует знания технологии MIDAS (Multi-tier Distributed Application Services) - службы многоуровневых распределенных приложений. Эта технология состоит из ряда элементов:
Удаленный брокер данных (Remote Data Broker) - интерфейс обмена данными между сервером приложений и клиентом;
Брокер бизнес - объектов (Business Objects Broker) - позволяет размешать сервер приложений одновременно на нескольких компьютерах;
брокер ограничений (Constraints Broker) - распределяет ограничения между отдельными уровнями системы.
Методология MIDAS поддерживает разработку приложений на следующих технологиях межпрограммного и межкомпьютерного взаимодействия:
DCOM (Distributed Component Object Model) - модель распределенных (удаленных) объектов;
MTS (Microsoft Transaction Server) - дополнение технологии СОМ для управлениями транзакциями;
CORBA (Common Object Request Broker Architecture) – взаимодействие между объектами, расположенными на различных платформах. В заключении можно сказать, что выбор наиболее подходящей архитектуры ("локальной" - одноуровневой или "клиент-серверной" - многоуровневой) должен зависеть только от поставленной перед разработчиком задачи.
Работа технологии "клиент-сервер"
Технология "клиент-сервер" была разработана с целью устранения недостатков, имеющихся в первых двух подходах. "Клиент-сервер" означает такой способ взаимодействия программных компонентов, при котором они образуют единую систему. Как видно из самого названия, существует некий клиентский процесс, требующий определенных ресурсов, а также серверный процесс, который эти ресурсы предоставляет. При этом совсем необязательно, чтобы они находились на одном и том же компьютере. На практике принято размещать сервер на одном узле локальной сети, а клиенты - на других узлах.
В контексте базы данных клиент управляет пользовательским интерфейсом и логикой приложения, действуя как сложная рабочая станция, на которой выполняются приложения баз данных. Клиент принимает от пользователя запрос, проверяет синтаксис и генерирует запрос к базе данных на языке SQL или другом языке базы данных, который соответствует логике приложения. Затем он передает сообщение серверу, ожидает поступления ответа и форматирует полученные данные для представления их пользователю. Сервер принимает и обрабатывает запросы к базе данных, а затем передает полученные результаты обратно клиенту. Такая обработка включает проверку полномочий клиента, обеспечение требований целостности, поддержку системного каталога, а также выполнение запроса и обновление данных. Помимо этого, поддерживается управление параллельностью и восстановлением. Этот тип архитектуры обладает приведенными ниже преимуществами:
Обеспечивается более широкий доступ к существующим базам данных.
Повышается общая производительность системы. Поскольку клиенты и сервер находятся на разных компьютерах, их процессоры способны выполнять приложения параллельно. При этом настройка производительности компьютера с сервером упрощается, если на нем выполняется только работа с базой данных.
Стоимость аппаратного обеспечения снижается. Достаточно мощный компьютер с большим устройством хранения нужен только серверу - для хранения и управления базой данных.
Сокращаются коммуникационные расходы. Приложения выполняют часть операций на клиентских компьютерах и посылают через сеть только запросы к базе данных, что позволяет существенно сократить объем пересылаемых по сети данных.
Повышается уровень непротиворечивости данных. Сервер может самостоятельно управлять проверкой целостности данных, поскольку все ограничения определяются и проверяются только в одном месте. При этом каждому приложению не придется выполнять собственную проверку.
Эта архитектура весьма естественно отображается на архитектуру открытых систем.
Некоторые разработчики баз данных использовали эту архитектуру для организации средств работы с распределенными базами данных, т.е. с набором нескольких баз данных, логически связанных и распределенных в компьютерной сети. Однако, несмотря на то, что архитектура "клиент-сервер" вполне может быть использована для организации распределенной СУБД, сама по себе она не образует распределенную СУБД.
Модели транзакций. Свойства. Способы завершения
Поддержка транзакций
Транзакция – действие или серия действий, выполняемых одним пользователем или прикладной программой, которые осуществляют доступ или изменение содержимого базы данных.
�Транзакция является логической единицей работы, выполняемой в базе данных. И может быть представлена отдельной программой, являться частью алгоритма или даже отдельной командой (например, командой INSERT или UPDATE языка SQL) и включать произвольное количество операций, выполняемых в базе данных. С точки зрения базы данных, выполнение программы некоторого приложения может расцениваться как серия транзакций, в промежутках между которыми выполняется некоторая обработка данных, осуществляемая вне среды базы данных. Для иллюстрации концепции транзакции рассмотрим два отношения:
Staff (Sno, FName, LName, Address, Tel__No, Position, Sex, DOB, Salary, NIN, Bno)
Property_for_Rent (Pno, Street, Area, City, Pcode, Type, Rooms, Rent, Ono, Sno, Bno)
Простейшей транзакцией, выполняемой в подобной базе данных, может быть модификация зарплаты определенного работника, указанного его личным номером х.
Более сложная транзакция предназначена для удаления сведений о работнике, заданном его учетным номером x. В этом случае, помимо удаления соответствующего кортежа из отношения Staff, потребуется найти все кортежи отношения Property_for_Rent, описывающие объекты недвижимости, за которые отвечал данный работник, после чего назначить их некоторому другому работнику, личный номер которого будет иметь значение new_sno, Если все указанные изменения не будут внесены до конца, база данных окажется в несогласованном состоянии - за объект недвижимости будет отвечать несуществующий работник компании.
Любая транзакция всегда должна переводить базу данных из одного согласованного состояния в другое, хотя допускается, что согласованность состояния базы будет нарушаться в ходе выполнения транзакции.
Любая транзакция завершается одним из двух возможных способов. В случае успешного завершения результаты транзакции фиксируются (commit) в базе данных, и последняя переходит в новое согласованное состояние. Если выполнение транзакции не увенчалось успехом, она отменяется. В этом случае в базе данных должно быть восстановлено то согласованное состояние, в котором она находилась до начала данной транзакции. Этот процесс называется откатом (roll back) транзакции. Зафиксированная транзакция не может быть отменена. Если оказывается, что зафиксированная транзакция была ошибочной, потребуется выполнить другую транзакцию, отменяющую действия, выполненные первой транзакцией. В некоторых случаях эту транзакцию называют компенсирующей. Следует отметить, что отмененная транзакция может быть еще раз запущена позже и, в зависимости от причин предыдущего отказа, вполне успешно завершена и зафиксирована в базе данных.
Никакая СУБД не обладает внутренней возможностью установить, какие именно изменения должны быть восприняты как единое целое, образующее одну логическую транзакцию. Следовательно, должен существовать метод, позволяющий указывать границы каждой из транзакций извне, со стороны пользователя. В большинстве языков манипулирования данными для указания границ отдельных транзакций используются операторы BEGIN TRANSACTION, COMMIT и ROLLBACK (или их эквиваленты). Если эти ограничители не были использованы, вся выполняемая программа расценивается как единая транзакция. СУБД автоматически выполнит команду COMMIT при нормальном завершении этой программы. Аналогично, в случае ее аварийного завершения в базе данных автоматически будет выполнена команда ROLLBACK.
Свойства транзакций
Существуют некоторые свойства, которыми должна обладать любая из транзакций. Ниже представлены четыре основных свойства (ACID - аббревиатура, составленная из первых букв их английских названий).
Атомарность. Это свойство типа "все или ничего". Любая транзакция представляет собой неделимую единицу работы, которая может быть либо выполнена вся целиком, либо не выполнена вовсе.
Согласованность. Каждая транзакция должна переводить базу данных из одного согласованного состояния в другое согласованное состояние.
Изолированность. Все транзакции выполняются независимо одна от другой. Другими словами, промежуточные результаты незавершенной транзакции не должны быть доступны другим транзакциям.
Продолжительность. Результаты успешно завершенной (зафиксированной) транзакции должны сохраняться в базе данных постоянно и не должны быть утеряны в результате последующих сбоев.
Улучшенные модели транзакций
Протоколы обработки транзакций, которые мы обсуждали до сих пор, больше всего подходят для тех типов транзакций, которые имеют место в традиционных бизнес-приложениях, при проведении банковских операций и в системах резервирования авиабилетов. Все эти приложения характеризуются следующими особенностями:
Простые типы данных - целые числа, десятичные числа, короткие символьные строки и даты.
Небольшая продолжительность транзакций, обычно не превышающая нескольких минут или даже секунд.
Рассмотрим различные средства разработки баз данных, получивших в последнее время достаточно широкое распространение - инженерные (CAD), технологические (САМ) и программные (CASE). Все они имеют ряд общих характеристик, отличающих их от традиционных приложений баз данных.
Создаваемый проект может быть очень большим - вполне возможно, состоящим из миллионов частей, часто объединенных во множество взаимозависимых подсистем, представляющих собой отдельные проекты.
Проект не является статическим объектом и постоянно эволюционирует. При внесении в проект изменения, его следствия часто распространяются на все имеющиеся в проекте подсистемы. Динамическая природа создаваемых проектов может означать, что некоторые действия просто не могут быть предусмотрены заранее, в самом начале работы.
Выполняемые обновления могут иметь далеко идущие последствия, вызванные существующими сложными структурными взаимосвязями, функциональными зависимостями, допусками и т.д. Одно небольшое изменение вполне может оказать влияние на множество различных проектируемых элементов.
Очень часто для отдельных компонентов проекта рассматривается множество различных альтернативных вариантов, причем каждый раз требуется подготовить новую корректную версию всех его компонентов. Решение подобных задач требует наличия средств контроля версий и инструментов управления выбираемой конфигурацией.
В работу могут быть вовлечены сотни людей, работающих над несколькими параллельными версиями одного крупного проекта. Тем не менее конечный продукт должен быть согласованным и сбалансированным. Иногда такой стиль работы называют кооперативным проектированием. Кооперация требует четкого взаимодействия и полной согласованности всех разработок, выполняемых параллельно.
Некоторые из указанных выше особенностей являются причиной того, что выполняемые транзакции становятся чрезвычайно сложными, обрабатывающими множество элементов данных и имеющими очень большую продолжительность - возможно, несколько часов, дней и даже месяцев. Подобные требования вынуждают пересмотреть традиционные протоколы управления транзакциями с целью устранения следующих проблем.
С учетом фактора времени, продолжительные транзакции более чувствительны к отказам. Было бы совершенно неприемлемо отменить выполнение подобной транзакции с потенциальной потерей очень большого объема выполненной работы. Следовательно, чтобы минимизировать возможные потери, необходимо иметь средства для восстановления того состояния транзакции, которое она имела незадолго до возникновения отказа.
С учетом фактора времени, продолжительные транзакции могут получать доступ (например, устанавливать блокировки) к очень большому количеству элементов данных. Во избежание возможного взаимного влияния транзакций, все эти элементы станут недоступными другим приложениям вплоть до завершения данной транзакции. Однако крайне нежелательно, чтобы существенный объем данных оставался недоступным на протяжении достаточно продолжительного периода времени, поскольку это отражается на уровне параллельности обработки в системе.
Чем дольше выполняется транзакция, тем вероятнее возникновение ситуации взаимной блокировки, когда в системе используется протокол, допускающий подобные ошибки. Было показано, что вероятность возникновения взаимной блокировки пропорциональна четвертой степени времени выполнения транзакции (Gray, 1981).
Одним из способов организации кооперативной работы группы сотрудников является предоставление им возможности совместно использовать требуемые элементы данных. Однако традиционные протоколы управления транзакциями существенно ограничивают возможности такой кооперации, соблюдая требования изоляции незавершенных транзакций друг от друга.
Модель вложенных транзакций
Модель вложенных транзакций была предложена Моссом (Moss) в 1981 г. В этой модели транзакция рассматривается как набор связанных подзадач, называемых субтранзакциями, каждая из которых также может состоять из произвольного количества субтранзакций. В данном представлении полная транзакция представляет собой древовидную структуру или некоторую иерархию субтранзакций. В модели вложенных транзакций присутствует транзакция верхнего уровня, содержащая некоторое количество дочерних транзакций, каждая из которых, в свою очередь, может включать вложенные транзакции, и т.д. В исходном варианте, предложенном Моссом, только транзакциям листового уровня (субтранзакциям самого нижнего уровня вложения) разрешается выполнять операции в базе данных.
Для модели вложенных транзакций Моссом был предложен и протокол управления параллельностью, построенный по принципу жесткой двухфазной блокировки. Субтранзакции родительской транзакции выполняются так, как если бы они были независимыми транзакциями. Субтранзакциям разрешается устанавливать блокировку элемента, если некоторая другая транзакция, уже установившая блокировку, с которой конфликтует данная субтранзакция, является ее родительской. Когда субтранзакция завершает работу, установленные ею блокировки наследуются родительской транзакцией. При наследовании блокировки родитель получает более эксклюзивный режим блокировки, если и дочерняя, и родительская транзакции устанавливали блокировку одного и того же элемента данных.
Ниже перечислены основные преимущества модели вложенных транзакций.
Модульность. Транзакция может быть разложена на произвольное количество субтранзакций, что способствует повышению уровня параллельности обработки и расширяет возможности восстановления.
Создание дополнительного уровня детализации в механизмах управления параллельностью и восстановлением. Введение нового уровня субтранзакций, помимо уровня основных транзакций.
Достижение параллельности обработки в пределах транзакции. Субтранзакции могут выполняться в параллельном режиме.
Возможность управления восстановлением в пределах транзакции. Отмененные субтранзакции могут быть отброшены или проигнорированы без каких-либо побочных эффектов для других субтранзакций.
Эмуляция механизма вложенных транзакций с помощью точек сохранения
Точкой сохранения называется определенная точка в плоской транзакции, представляющая некоторое частично согласованное состояние, которая может быть использована как точка промежуточного рестарта транзакции в случае возникновения каких-либо проблем. Фактически, это одна из задач модели вложенных транзакций, состоящая в предоставлении некоторой единицы восстановления работы, более мелкой, чем вся транзакция в целом. В процессе выполнения транзакции пользователь может организовать точку сохранения - например, с помощью оператора SAVE WORK. При его выполнении генерируется некоторый идентификатор, который пользователь впоследствии сможет применить для отката транзакции до данного, зафиксированного состояния - например, с помощью оператора ROLLBACK WORK идентификатор точки сохранений. Однако, в отличие от модели вложенных транзакций, при использовании точек сохранения не обеспечивается какая-либо из форм параллельности обработки в пределах транзакции.
Этот оператор не является стандартным оператором языка SQL и представляет собой лишь некоторую иллюстрацию.
Хроники
Концепция хроник (sagas), которая была введена Гарсия-Молином (Garsia-Molina) и Салемом (Salem) в 1987 г., построена на использовании понятия компенсирующих транзакций. Авторы определяют хронику как последовательность (плоских) транзакций, которые могут чередоваться с прочими транзакциями. СУБД гарантирует, что либо все входящие в хронику транзакции будут успешно завершены, либо будут запущены компенсирующие транзакции, необходимые для устранения достигнутых частичных результатов. В отличие от метода вложенных транзакций, допускающего произвольный уровень вложения, метод хроник разрешает наличие единственного уровня вложения. Более того, для каждой выделенной субтранзакции должна существовать соответствующая компенсирующая транзакция, которая будет семантически аннулировать результаты, достигаемые с помощью данной субтранзакции. Таким образом, если имеется хроника, состоящая из последовательности п транзакций T1,T2,..., Тn с соответствующим набором компенсирующих транзакций С1, С2,..., Сn, то окончательный результат выполнения хроники будет определяться одной из следующих последовательностей транзакций:
Т1,Т2,..., Тn - если вся транзакция была успешно завершена.
Ti, Tg,..., Т, Ci-1,..., Са, Ci - если выполнение транзакции Ti было прекращено.
Вошедшие в хронику субтранзакции, представляют собой листовые узлы транзакции верхнего уровня. Не составит особого труда и подготовить компенсирующие транзакции, предназначенные для отмены заказа на авиабилеты, резервирования номера в гостинице и проката автомобиля.
По сравнению с обычными плоскими транзакциями, хроники нарушают требования к изолированности отдельных транзакций, открывая промежуточные результаты своего выполнения другим, выполняющимся параллельно транзакциям еще до завершения своей работы. Применение хроник оказывается достаточно эффективным в тех случаях, когда входящие в нее субтранзакции относительно независимы и могут быть подготовлены необходимые компенсирующие транзакции. В некоторых случаях предварительное определение компенсирующей транзакции может оказаться затруднительным. В этом случае может потребоваться, чтобы СУБД провела некоторое взаимодействие с пользователем, который укажет подходящий случаю способ выполнения компенсации. В других случаях определение компенсирующей транзакции может оказаться вообще невозможным. Например, может оказаться невозможным подготовить компенсационную транзакцию для транзакции, которая получает выручку из автоматического кассового аппарата.
Модель многоуровневых транзакций
Модель вложенных транзакций, требует, чтобы завершение работы субтранзакций выполнялось снизу вверх, в направлении транзакции верхнего уровня. Поэтому данную модель более точно называть моделью закрытых вложенных транзакций, дабы подчеркнуть, что свойство атомарности в транзакциях сохраняется до самого верхнего уровня. В противоположность этому, существует и модель открытых вложенных транзакций, в которой атомарность нарушается и частичные результаты выполнения субтранзакций могут быть доступны вне транзакции.
Одним из вариантов модели открытых вложенных транзакций является модель многоуровневых транзакций, в которой дерево субтранзакций является сбалансированным (Weikum, 1991; Weikum and Schek, 1991). Узлы одного и того же уровня дерева соответствуют операциям одного и того же уровня абстракции в СУБД. Ветви дерева представляют реализацию операции посредством последовательности операций на следующем уровне глубины. Уровни n-уровневой транзакции обозначаются как l0, L1,..., Ln, где l0 - самый нижний уровень дерева, a Ln - корень дерева. Методы обработки обычных плоских транзакций гарантируют, что на самом нижнем уровне (L0) конфликты будут отсутствовать. Основная концепция модели многоуровневых транзакций состоит в том, что две операции на уровне Li могут не конфликтовать, даже если их реализации на следующем, более низком уровне Li-1 конфликтуют. Используя преимущество знания информации о конфликтах на конкретном уровне, модель многоуровневых транзакций позволяет достичь более высокой степени параллельности по сравнению с моделями обработки плоских транзакций.
Динамическая реструктуризация
В начале этого вопроса обсуждались некоторые особенности приложений поддержки выполнения различных проектов - например, неопределенная продолжительность работы (от нескольких часов до месяцев), чередование с другими видами операций, неопределенность процесса обработки, не позволяющая предвидеть все аспекты работы с самого начала ее выполнения, и т.д. В обход ограничений, налагаемых основными свойствами (ACID) плоских транзакций, были предложены две новые операции: разбиение транзакции (split_transaction) и объединение транзакций (join_transaction) (Pu et al., 1988). Принцип, положенный в основу операции разбиения транзакции, состоит в разделении активной транзакции на две упорядоченные транзакции и распределении между ними выполняемых действий и используемых ресурсов (например, заблокированных элементов данных). С этого момента вновь созданные транзакции могут независимо выполняться (возможно, даже под контролем разных пользователей) и обрабатываться так, как если бы они всегда были совершенно независимыми. Подобный подход позволяет сделать промежуточные результаты транзакции доступными другим транзакциям, причем с полным сохранением их семантики - другими словами, если исходная транзакция отвечала всем ACID-требованиям, то так же поведут себя и новые транзакции.
Операция разделения транзакции может применяться только в том случае, если возможно создать две транзакции, которые будут упорядочены друг с другом и со всеми другими выполняющимися в данный момент транзакциями. Условия, которые разрешают разделение транзакции Т на две транзакции, А и В, можно определить следующим образом.
1. AWriteSet ( BWriteSet ( BWriteLast. Это условие утверждает, что если обе транзакции, А и В, выполняют запись в один и тот же элемент данных, то операция' записи транзакции В должна выполняться после операции записи транзакции А.
2. AReadSet ( BWriteSet = �. Это условие утверждает, что транзакция А не может видеть никаких результатов выполнения транзакции В.
3. BReadSet ( AWriteSet = ShareSet. Это условие утверждает, что транзакция В может видеть результаты выполнения транзакции А.
Приведенные выше условия гарантируют, что транзакция А в упорядоченном графике будет предшествовать транзакции В. Однако, если выполнение транзакции А будет прервано, выполнение транзакции В также необходимо будет отменить, поскольку она использует данные, записанные транзакцией А. Если оба множества BWriteLast и ShareSet пусты, то транзакции А и В могут быть упорядочены в любой последовательности и работа их будет совершенно независимой.
Операция объединения транзакций выполняет обратные действия по отношению к операции разделения транзакций, сливая результаты работы двух или более независимых транзакций так, как если бы эти транзакции всегда представляли собой единую транзакцию. Использование операций разделения транзакции, дополненных операциями объединения одной или больше вновь созданных транзакций, позволяет обмениваться ресурсами между определенными транзакциями, не делая эти ресурсы доступными другим транзакциям.
Основные достоинства метода динамической реструктуризации состоят в следующем.
Адаптивное восстановление. Возможность зафиксировать часть выполненной в транзакции работы, что исключает ее зависимость от последующих отказов.
Снижение уровня изолированности. Возможность освободить часть использовавшихся в транзакции ресурсов посредством фиксации уже выполненной части ее работы.
Модели рабочих потоков
Все обсуждавшиеся в этом разделе модели были разработаны с целью преодоления ограничений, накладываемых моделью плоских транзакций, не приемлемых для транзакций большой продолжительности. Однако было отмечено, что все эти модели по-прежнему не обладают мощностью, необходимой для удовлетворения потребностей деловых процессов определенных видов. Поэтому были предложены более сложные модели, представляющие собой комбинации моделей открытых и вложенных транзакций. Однако, поскольку эти модели слабо отвечают ACID-требованиям плоских транзакций, для них используется более подходящее название - модели рабочих потоков.
Рабочий поток представляет собой некоторый вид деятельности, предусматривающий координированное выполнение множества заданий, осуществляемых различными субъектами обработки, которые могут представлять собой людей или некоторые программные комплексы (например, СУБД, прикладные программы или службы электронной почты
Перед любыми системами с рабочими потоками стоят две общие проблемы: определение рабочего потока и выполнение рабочего потока. Обе проблемы усложняются тем фактом, что во многих организациях одновременно используется несколько независимых компьютерных систем, предназначенных для автоматизации различных сторон общего процесса. Приведенные ниже определения выделяют ключевые элементы, используемые при определении рабочего потока (Rusinkiewicz and Sheth, 1995).
Спецификация задания - структура выполнения каждого задания, определяемая посредством предоставления набора обозримых извне состояний процесса и набора переходов между этими состояниями.
Требования по координации заданий - обычно задаются посредством указания зависимостей между процессами выполнения заданий и зависимостей между потоками данных, дополненных условиями завершения рабочего потока.
Требования к корректности выполнения - ограничения, накладываемые на показатели выполнения рабочего потока, с целью его соответствия требованиям корректности в данном приложении. Сюда относятся требования по устойчивости к отказам, независимости и параллельности обработки, возможностям восстановления и т.д.
Процессам выполнения свойственна семантика открытой вложенности, допускающая видимость промежуточных результатов процесса вне его границ и разрешающая различным компонентам фиксировать результаты своей работы в индивидуальном порядке. Компонентами могут быть другие субъекты обработки, характеризующиеся как семантикой открытой вложенности, так и семантикой закрытых вложенных транзакций, результаты функционирования которых будут видны всей системе только после полного завершения работы этого компонента. Следует отметить, что компоненты с семантикой закрытых вложенных транзакций могут состоять только из компонентов с семантикой того же типа. Некоторые из этих компонентов могут расцениваться как жизненно важные, и в случае отказа от их выполнения потребуется прекратить выполнение и их родительских компонентов. Кроме того, могут быть определены компенсирующие и возможные (альтернативные) транзакции, речь о которых уже шла выше.
Журнал транзакций. Восстановление после сбоев.
Назначение атрибутов пользователей
Важнейшей составляющей процесса управления доступом к БД является возможность изменения атрибутов пользователей уже после создания и регистрации в БД их учетных записей. Это происходит из-за изменения статуса пользователей, их полномочий и характера выполняемой работы. В организациях, где наблюдается высокая текучесть кадров и постоянное изменение обязанностей нельзя полагаться только на память администратора БД - следует вести журнал изменения полномочий, в котором отражается вся динамика изменения привилегий доступа к данным. Еще более квалифицированным выходом из сложившейся ситуации является создание "службы безопасности", отслеживающей всю статистику изменения полномочий пользователей. В СУБД Oracle оператор изменения атрибутов пользователя выглядит так:
ALTER USER ИМЯ_ПОЛЬЗОВАТЕЛЯ IDENTIFIED BY ПАРОЛЬ
DEFAULT TABLESPACE ИМЯ_ОБЛАСТИ,...
Именно этот оператор позволяет назначить указанному пользователю новый пароль и изменить другие атрибуты. Не все реализации SQL поддерживают эту команду, а подавляющее большинство современных СУБД для выполнения этой задачи предлагают средства графического интерфейса.
Контроль сеансов доступа к данным
Как мы уже знаем, сеанс доступа к БД начинается с момента подключения пользователя к БД с помощью директивы CONNECT и заканчивается сразу после выполнения оператора DISCONNECT. Причем в большинстве случаев подключение и отключение к БД происходит в фоновом режиме, так как обычно эти операторы выполняются программой, с которой работает пользователь.
Сеансы доступа к данным могут автоматически отслеживаться администраторами БД. При наблюдении за деятельностью пользователей сеансы доступа к данным связываются с учетными записями пользователей. При этом сеанс доступа представляет собой отдельный процесс главного компьютера.
Уровни защиты БД
Обсуждая обеспечение механизмов сохранности и защиты данных нельзя не уточнить, что "защита данных" и "управление пользователями" - это
неразрывно связанные, но принципиально разные задачи администратора БД.
Защита данных - это сложный комплексный механизм блокировки информации от несанкционированного доступа. В него входят и средства СУБД, и сетевые возможности самой ОС.
В БД хранится целая иерархия пользователей, начиная от проектировщиков, людей, ответственных за ее работу, программистов и конечных пользователей. И хотя конечные пользователи являются с точки зрения полномочий наиболее "ущербными", именно для них и создается БД, в которой этот пользователь выполняет свои действия в рамках индивидуального набора прав по отношению к объектам БД.
Виды привилегий
Привилегии - это уровень полномочий, предоставляемый пользователю при корректном (санкционированном) подключении к БД. В классическом SQL привилегии предоставляются посредством директивы GRANT и отменяются командой REVOKE. Однако большинство современных СУБД (с развитым интерфейсом) позволяют наделять пользователей привилегиями не из командной строки SQL, а с помощью диалогового интерфейса.
То, что пользователь может подключиться к БД, совсем не означает того, что он может получить из нее какие-либо данные. Доступ к данным в рамках сеанса подключения пользователя разрешается путем установки привилегий.
Существуют два уровня привилегий:
Привилегии доступа к системе;
Привилегии доступа к объектам.
Привилегии доступа к системе
Привилегии доступа к системе - это привилегии, дающие возможность пользователю выполнять функции администрирования (создавать и удалять объекты БД и саму БД, наделять других пользователей правами доступа, выполнять резервное копирование данных и т.п.). Пользователь, наделенный этим уровнем привилегий, представляет наибольшую опасность для БД. Поэтому такие полномочия должны иметь только проектировщики БД н ее администраторы.
Несмотря на серьезные различия реализаций SQL список основных привилегий остается неизменным:
CREATE DATABASE- создание БД;
ALTER DATABASE- модификация БД;
CREATE TABLE- создание таблицы;
CREATE ANY TABLE - создание любой таблицы;
ALTER TABLE- модификация структуры таблицы;
DROP TABLE- удаление таблицы;
CREATE USER- создание учетной записи;
ALTER USER- изменение учетной записи;
DROP USER- удаление учетной записи;
SELECT ANY TABLE- выборка из любой таблицы;
BACKUP ANY TABLE - создание резервной копии.
Привилегии доступа к объектам
Привилегии доступа к объектам - это уровни полномочий пользователей по отношению к объектам БД. Стандарт SQL определяет следующий ряд привилегий:
USAGE- разрешение использования области;
SELECT- разрешение выборки из таблицы;
INSERT- добавление записей в таблицу;
INSERT (СТОЛБЕЦ) - добавление в указанный столбец;
UPDATE- модификация записей таблицы;
UPDATE (СТОЛБЕЦ) - изменение в указанном столбце;
REFERENCES- позволяет сослаться на столбец таблицы.
Владелец объекта автоматически наделяется всеми привилегиями по отношению к нему. Остальные пользователи наделяются правами доступа директивно.
Обычно правом выполнения команд GRANT и REVOKE наделён только администратор БД. Поэтому именно администратор БД, оперативно реагирует на письменные инструкции руководства, наделяет соответствующих пользователей необходимыми полномочиями и привилегиями.
Методы восстановления
Тип процедуры, которая будет использована для восстановления базы данных, зависит от размера повреждений, которые были нанесены этой базе в результате отказа. Рассмотрим два варианта:
Если базе данных нанесены обширные повреждения (например, разрушилась магнитная головка диска), то потребуется восстановить ее последнюю резервную копию, после чего повторить в ней все выполненные транзакции, сведения о которых присутствуют в журнале регистрации. Безусловно, предполагается, что файл журнала поврежден не был. При обсуждении пятого этапа физического проектирования базы данных рекомендовалось, чтобы во всех случаях, когда это возможно, файл журнала создавался на дисковых носителях, отличных от тех, на которых размещены основные файлы базы данных. Подобное решение снижает риск одновременной потери, как файлов базы данных, так и файла ее журнала.
Если база данных не получила физических повреждений, но лишь утратила согласованность размещенных в ней данных (например, из-за аварийного останова системы в процессе обработки транзакций), то достаточно будет отменить те изменения, которые вызвали переход базы данных в несогласованное состояние. Кроме того, возможно потребуется повторно прогнать некоторые транзакции, чтобы иметь уверенность в том, что внесенные в них изменения действительно зафиксированы во вторичной памяти. В данном случае нет необходимости обращаться к резервной копии базы данных, поскольку вернуть базу в согласованное состояние можно с помощью информации о содержимом полей до и после модификации, сохраняемой в файле журнала.
Дальше мы подробно рассмотрим два метода восстановления, которые могут быть применены в последнем из указанных выше случаев - т.е. когда база данных не была полностью разрушена, но лишь утратила согласованное состояние. Предлагаемые методы, известные как метод отложенного обновления и метод немедленного обновления, отличаются друг от друга способом внесения обновлений во вторичную память. Кроме того, мы бегло познакомимся с альтернативным методом, известным как метод теневых страниц.
Метод восстановления с использованием отложенного обновления
При использовании этого протокола обновления не заносятся в базу данных до тех пор, пока транзакция не выдаст команду фиксации выполненных изменений. Если выполнение транзакции будет прекращено до достижения этой точки, никаких изменений в базе данных выполнено не будет, поэтому не потребуется и их отмена. Однако в данном случае может потребоваться повторный прогон уже завершившихся транзакций, поскольку их результаты могли еще не достичь вторичной памяти. При применении данного метода файл журнала используется с целью восстановления следующим образом:
При запуске транзакции в журнал помещается запись Начало транзакции.
При выполнении любой операции записи помещаемая в файл журнала строка содержит все указанные выше данные (за исключением значения элементов данных до обновления). Реально запись изменений в буфера СУБД или саму базу данных не производится.
Когда транзакция достигает своей конечной точки, в журнал помещается запись Транзакция завершена. Все записи журнала по данной транзакции выводятся на диск, после чего выполняется фиксация внесенных транзакцией изменений. Для внесения действительных изменений в базу данных используется информация, помещенная в файл журнала.
В случае отмены выполнения транзакции записи журнала по данной транзакции аннулируются и не выводятся на диск.
Обратите внимание на то, что записи журнала по выполненной транзакции выводятся на диск до того, как ее результаты будут зафиксированы. Поэтому если отказ базы данных произойдет в процессе действительного выполнения обновлений в базу данных, помещенные в журнал сведения сохранятся и требуемые обновления можно будет выполнить позже.
В случае отказа файл журнала анализируется с целью выявления транзакций, которые находились в процессе выполнения в момент отказа. Начиная с последней строки, файл журнала просматривается в обратном направлении, вплоть до записи о последней выполненной контрольной точке:
Любые транзакции, для которых в файле журнала присутствуют записи Начало транзакции и Транзакция завершена должны быть выполнены повторно. Процедура повторного прогона транзакций выполняет все операции записи в базу данных, используя информацию о состоянии элементов данных после обновления, содержащуюся в записях журнала по данной транзакции, причем в том порядке� в каком они были записаны в файл журнала. Если эти операции записи уже были успешно завершены до возникновения отказа, это не окажет никакого влияния на состояние элементов данных, поскольку они не могут быть испорчены, если будут записаны еще раз. Однако этот метод гарантирует, что будут обновлены любые элементы данных, которые не были корректно обновлены до момента отказа.
Любая транзакция, для которой в файле журнала присутствуют записи Начало транзакции и Отмена транзакции, просто игнорируется, поскольку никаких реальных обновлений информации в базе данных по ней не выполнялось, а значит, не требуется и реального выполнения их отката.
Если в процессе восстановления возникнет другой системный сбой, записи файла журнала могут быть использованы для восстановления базы данных еще раз. В данном случае не имеет значения, сколько раз каждая из строк журнала была использована для повторного внесения изменений в базу данных.
Метод восстановления с использованием немедленного обновления
При использовании этого протокола все изменения вносятся в базу данных сразу же после их выполнения в транзакции, не дожидаясь ее завершения. Помимо необходимости повторного прогона изменений, выполненных транзакциями, закончившимися до появления сбоя, в данном случае может потребоваться выполнить откат изменений, внесенных транзакциями, которые не были завершены к этому моменту. При применении данного метода файл журнала используется с целью восстановления следующим образом.
При запуске транзакции в журнал помещается запись Начало транзакции.
При выполнении любой операции записи помещаемая в файл журнала строка содержит все указанные выше данные.
Как только упомянутая выше запись будет помещена в файл журнала, все выполненные обновления вносятся в буфера базы данных.
В собственно файлы базы данных изменения будут внесены при очередной разгрузке буферов базы данных во вторичную память.
Когда транзакция завершает свое выполнение, в файл журнала заносится запись Транзакция завершена.
Очень важно, чтобы в файл журнала все записи (или хотя бы определенная их часть) помещались до внесения соответствующих изменений в базу данных. Это требование известно как протокол предварительной записи журнала. Если изменения вначале будут внесены в базу данных и сбой в системе возникнет до помещения информации об этом в файл журнала, то менеджер восстановления не будет иметь возможности отменить (или повторить) данную операцию. При использовании протокола предварительной записи журнала менеджер восстановления всегда сможет безопасно предположить, что если для определенной транзакции в файле журнала отсутствует запись Транзакция завершена, значит, эта транзакция была активна в момент возникновения отказа и, следовательно, должна быть отменена.
Если выполнение транзакции было прекращено, то для отмены выполненных ею изменений может быть использован файл журнала, так как в нем сохранены сведения об исходных значениях всех измененных элементов данных. Поскольку транзакция может выполнить несколько изменений одного и того же элемента, отмена обновлений выполняется в обратном порядке. Независимо от того, были ли результаты выполнения транзакции внесены в саму базу данных, наличие в записях журнала исходных значений полей гарантирует, что база данных будет приведена в состояние, отвечающее началу отмененной транзакции.
На случай отказа системы процедурой восстановления предусмотрено использование файла журнала для повторного прогона или отката транзакций. Для любой транзакции Т, для которой в файле журнала присутствуют записи Начало транзакции и Транзакция завершена, следует выполнить ее повторный прогон, используя для внесения в базу данных значения после изменения всех обновленных полей - как было описано выше. Отметим, что если новые значения уже были реально внесены в файлы базы данных, повторная их перезапись, хотя и будет излишней, тем не менее не окажет на базу данных никакого отрицательного влияния. Те же изменения, которые еще не достигли базы данных к моменту отказа, будут в нее внесены в процессе восстановления. Для любой транзакции S, для которой в файле журнала присутствует запись Начало транзакции, но нет записи Транзакция завершена, необходимо выполнить откат внесенных ею изменений. На этот раз из записей файла журнала извлекается информация о значении измененных полей до их изменения, что позволяет привести базу данных в состояние, которое она имела до начала данной транзакции. Операции отмены выполняются в порядке, обратном порядку их записи в файл журнала.
Защита информации в базах данных
Защита баз данных – обеспечение защищённости базы данных против любых предумышленных или непредумышленных угроз с помощью различных компьютерных и некомпьютерных средств.
Понятие защиты применимо не только к сохраняемым данным. Бреши в системе защиты могут возникать и в других частях системы, что, в свою очередь, подвергает опасности и собственно базу данных. Следовательно, защита базы данных должна охватывать используемое оборудование, ПО, персонал и собственно данные. Для эффективной реализации защиты необходимы соответствующие средства контроля, которые определяются конкретными требованиями, вытекающими из особенностей эксплуатируемой системы. Необходимость защищать данные, которую раньше очень часто отвергали, в настоящее время всё яснее осознаётся различными организациями.
База данных представляет собой важнейший корпоративный ресурс, который должен быть надлежащим образом защищён с помощью соответствующих средств контроля. Мы обсудим проблемы защиты баз данных с точки зрения таких потенциальных опасностей, как:
Похищение и фальсификация данных;
Утрата конфиденциальность;
Нарушение неприкосновенности личных данных;
Утрата целостности;
Потеря доступности.
Указанные ситуации отмечают основные направления, в которых руководство должно принимать меры, снижающие степень риска, т.е. потенциальную возможность потери или повреждения данных. В некоторых ситуациях все отмеченные аспекты тесно связанны между собой, так что действия, направленные на нарушение работы одной системы, часто приводят к нарушению работоспособности другой.
Контрмеры – компьютерные средства контроля
В отношении опасностей, угрожающих компьютерным системам, могут бать приняты контрмеры самых различных типов, начиная от физического наблюдения и заканчивая административно – организационными процедурами. Несмотря на широкий диапазон компьютерных средств контроля, общий уровень защищённости СУБД определяется возможностями используемой операционной системы, поскольку работа этих двух компонентов тесно связана между собой. Обычно для платформы IBM PC применяются не все перечисленные ниже типы средств контроля:
Авторизация пользователей.
Представления.
Резервное копирование и восстановление.
Поддержка целостности.
Шифрование.
Вспомогательные процедуры.
Авторизация пользователей – предоставление прав или привилегий, позволяющих их владельцу иметь законный доступ к системе или к её объектам. Средства авторизации пользователей могут быть встроены непосредственно в программное обеспечение и управлять не только предоставленными пользователям правами доступа, но и набором операций, которые пользователи могут выполнять с каждым доступным ему объектом. Процесс авторизации включает в себя аутентификацию объектов, требующих получения доступа к объектам. Аутентификация – механизм определения того, является ли пользователь тем, за кого себя выдаёт. Как только пользователь получил право доступа к СУБД, ему могут автоматически предоставляться различные другие привилегии, связанные с его идентификатором. В частности, эти привилегии могут включать разрешение на доступ к определенным базам данных, таблицам, представлениям и индексам или же право запуска различных утилит СУБД.
Представления – это динамический результат одной или нескольких реляционных операций с базовыми отношениями с целью создания некоторого иного отношения. Представление является виртуальным отношением, которого реально в базе данных не существует, но которое создаётся по требованию отдельного пользователя в момент поступления этого требования. Механизм представления представляет собой мощный и гибкий инструмент организации защиты данных, позволяющий скрывать от определённых пользователей некоторые части БД. В результате пользователи не будут иметь никаких сведений о существовании любых атрибутов или строк данных, которые недоступны через представления, находящиеся в их распоряжении. Представление также может быть частичным.
Резервное копирование и восстановление – периодически выполняемая процедура получения копий базы данных и её файла журнала на носителе, сохраняемой отдельно от системы. Любая СУБД должна предоставлять средства резервного копирования, позволяющие восстанавливать базу в случае её разрушения. Кроме того, рекомендуется создавать резервные копии базы данных и её файла журнала с некоторой установленной периодичностью, а также организовать хранение созданных копий в местах, обеспеченных необходимой защитой. В случае отказа в работоспособности базы данных, резервная копия и зафиксированная в файле журнала определённая информация используется для восстановления базы данных до последнего согласованного состояния. Ведение журнала – процедура создания и обслуживания файла журнала, содержащего сведения обо всех изменениях, внесённых в БД с момента создания последней резервной копии, и предназначенного для обеспечения эффективного восстановления системы в случае её отказа.
Поддержка целостности. Средства поддержки целостности данных также вносят определённый вклад в общую защищённость базы данных, поскольку их назначением является предотвращение перехода данных в несогласованное состояние, а значит, и предотвращение угрозы получение ошибочных или некорректных результатов расчётов.
Шифрование – кодирование данных с использованием специального алгоритма, в результате чего данные становятся недоступными для чтения любой программой, не имеющих ключа дешифрования. Если в системе с базой данных содержится весьма важная информация, то имеет смысл закодировать её с целью предупреждения возможной угрозы несанкционированного доступа с внешней стороны. Шифрование также может использоваться для защиты данных при их передачи по линиям связи. Многие СУБД включают средства шифрования, предназначенные для использования в подобных целях. Для организации защищенной передачи данных по незащищённым сетям должны использоваться системы шифрования:
ключ шифрования, предназначен для шифрования исходных данных;
алгоритм шифрования, который описывает, как с помощью ключа шифрования преобразовать обычный текст в шифротекст;
ключ дешифрования, предназначен для дешифрования шифротекста;
алгоритм дешифрования, который описывает, как с помощью ключа дешифрования преобразовать шифротекст в обычный.
Существует две системы шифрования: симметричная и несимметричная. В симметричной системе используется один и тот же ключ как для шифрования так и для дешифрования. А в несимметричной системе используются разные ключи, причём один из ключей должен храниться в секрете.
Вспомогательные процедуры. Хотя выше уже были описаны различные процедуры, которые могут использоваться для защиты данных в СУБД, сами по себе они не гарантируют необходимого уровня защиты. По этой причине используются вспомогательные процедуры, которые не имеют ничего общего с процедурами встроенных в СУБД. Итак, перечислим их:
Авторизация и аутентификация. С точки зрения необходимого уровня защиты очень важно, чтобы все используемые пароль держались пользователями в секрете и регулярно обновлялись через некоторое время.
Копирование. Копирование происходит не только СУБД в целом, но и других прикладных программ. Причём процесс копирования происходит не под управлением базы, а под какой-либо независимой от неё программы.
Аудит. Здесь подразумевается проверка персонала на корректность работы. Например: поддержание точности вводимых данных, поддержание точности процедур обработки, предотвращение появления некорректных ошибок, несанкционированный доступ и т.д.
Установка или модернизация системного программного обеспечения.
Архитектура СУБД. Перспективы развития баз данных и СУБД
Архитектура Web-СУБД
В вопросе 15 рассматривается традиционная архитектура "клиент-сервер" современных СУБД, использующая двухуровневую схему построения приложений "клиент-сервер". В этом разделе рассматривается другая архитектура, более пригодная для работы в среде Web.
Традиционная двухуровневая архитектура "клиент-сервер"
Бизнес-приложения для интенсивной работы с данными состоят из четырех основных компонентов: базы данных, логики транзакций, логики приложения и интерфейса пользователя. В среде мейнфреймов все эти компоненты размещались в одном месте, как и надлежит быть в высокоцентрализованной бизнес-среде.
Для удовлетворения новых требований, связанных с растущей децентрализацией бизнес-среды, позже была разработана архитектура "клиент-сервер". Традиционная двухуровневая архитектура "клиент-сервер" предусматривает распределение основных решаемых задач между двумя уровнями. Клиентская часть, или клиент (уровень 1), прежде всего отвечает за представление данных пользователю, а серверная часть, или сервер (уровень 2), - за предоставление сервисов данных этому клиенту. Сервисы представления управляют пользовательским интерфейсом и основной бизнес-логикой приложения. Сервисы данных обеспечивают ограниченную поддержку бизнес-логики приложения, обычно в виде правил проверки корректности ввода данных (которую клиент неспособен выполнить из-за отсутствия соответствующей информации), а также реализуют доступ к запрашиваемым данным независимо от их расположения. Данные могут происходить от реляционных СУБД, объектно-ориентированных СУБД, традиционных СУБД или от устаревших специализированных систем доступа к данным. Клиентская часть обычно располагается на настольных компьютерах конечных пользователей и через сеть взаимодействует с центральным сервером базы данных.
Трехуровневая архитектура
Необходимость масштабируемости систем по мере развития предприятий стала непреодолимым барьером для традиционной двухуровневой архитектуры "клиент-сервер". В середине 1990-х годов стремительно усложнявшиеся приложения потенциально требовали разворачивания их программного обеспечения на сотнях и тысячах компьютеров конечных пользователей. В результате этого на клиентской стороне четко обозначились две указанные ниже проблемы, препятствующие достижению истинной масштабируемости приложений:
"Толстый" клиент, для эффективной работы которого требуются значительные вычислительные ресурсы, включая дисковое пространство, оперативную память и мощность центрального процессора.
Значительные накладные расходы на администрирование клиентской части приложений.
В 1995 году появился новый вариант модели традиционной двухуровневой архитектуры "клиент-сервер", который был призван решить проблемы корпоративной масштабируемости. В этой новой архитектуре предлагались три уровня программного обеспечения, каждый из которых может функционировать на разных платформах.
Уровень пользовательского интерфейса, который располагается на компьютере конечного пользователя (клиент).
Уровень бизнес - логики и обработки данных. Этот промежуточный уровень располагается на сервере и часто называется сервером приложения.
СУБД, в которой хранятся данные, необходимые для функционирования промежуточного уровня. Этот уровень может выполняться на отдельном севере базы данных.
Клиент отвечает только за пользовательский интерфейс и, возможно, выполняет некоторую очень простую �логическую обработку данных� например проверку корректности ввода данных. Ограниченный подобным функциональным набором клиент получил название "тонкого" клиента. Основная бизнес-логика приложения теперь находится на собственном выделенном уровне, который физически связан с клиентом и сервером базы данных посредством локальной (Local Area Network - LAN) или глобальной (Wide Area Network - WAN) вычислительной сети. При этом предполагается, что один сервер приложений может обслуживать множество клиентов.
Трехуровневая архитектура "клиент-сервер" имеет многие преимущества перед ж- и двухуровневой моделями. Ниже перечислены некоторые их них.
"Тонкий" клиент, для которого требуется менее дорогостоящее аппаратное обеспечение.
Централизация бизнес-логики для многих конечных пользователей на одном сервере приложения и, как следствие, централизация сопровождения приложения. Благодаря этому исключается необходимость развертывания программного обеспечения на множестве компьютеров, что представляет собой одну из самых сложных задач в двухуровневой модели "клиент-сервер".
Дополнительная модульность упрощает модификацию или замену программного обеспечения каждого уровня без оказания влияния на остальные уровни.
Отделение основной бизнес-логики приложения от функций базы данных упрощает задачу равномерного распределения нагрузки.
Дополнительное преимущество заключается в том, что трехуровневая архитектура довольно естественно отображается на среду Web, где Web-броузер выполняет роль "тонкого" клиента, а Web-сервер - сервера приложений. Трехуровневая архитектура может быть расширена до n-уровневой архитектуры с дополнительными уровнями, которые позволяют повысить гибкость и масштабируемость создаваемых приложений. Например, промежуточный уровень в трехуровневой архитектуре может быть расщеплен на два уровня, один из которых может выполнять роль обычного Web-сервера, а другой - типичного сервера приложений.
СУБД для хранилища данных
СУБД для хранилищ данных очень редко бывает источником проблем интеграции. Благодаря относительной зрелости таких программных продуктов, большинство реляционных баз данных интегрируется с другими типами программного обеспечение вполне предсказуемым образом. Однако потенциальным источником проблем может послужить большой размер базы данных хранилища. При работе с подобной базой данных становится особенно важным обеспечение параллельности, а также таких традиционно важных параметров, как высокая производительность, масштабируемость, готовность и управляемость, что обязательно следует принимать во внимание при выборе СУБД.
Сначала мы рассмотрим основные требования, предъявляемые к СУБД для хранилища данных, а затем кратко обсудим, как можно организовать в хранилищах данных параллельное выполнение вычислений.
Требования к СУБД для хранилища данных
Специализированные требования к реляционной СУБД (РСУБД), предназначенной для хранилища данных, были опубликованы в документе White Paper (Red Brick Systems, 1996). Вот эти требования.
Высокая производительность загрузки данных.
Возможность обработки данных во время загрузки.
Наличие средств управления качеством данных.
Высокая производительность запросов.
Широкая масштабируемость по размеру (до терабайт).
Масштабируемость по количеству пользователей.
Возможность организации сети хранилищ данных.
Наличие средств администрирования хранилища.
Поддержка интегрированного многомерного анализа.
Расширенный набор функциональных средств запросов
Высокая производительность загрузки данных
В хранилищах данных требуется периодически выполнять загрузку порций новых данных, причем в ограниченных временных рамках. Производительность процесса загрузки в подобных случаях должна измеряться в сотнях миллионов строк или гигабайтах данных в час. Со стороны бизнес - задач не существует никаких ограничений в отношении максимально допустимого уровня производительности.
Возможность обработки данных во время загрузки
При загрузке в хранилище новых или обновленных данных обычно требуется выполнение нескольких последовательных этапов, включающих преобразование данных, фильтрование, переформатирование, проверку целостности, физическое сохранение, индексирование и обновление метаданных. На практике каждый такой этап может выполняться по отдельности, однако в общем, процесс загрузки должен выглядеть как единая неразрывная процедура.
Наличие средств управления качеством данных
Для перехода к управлению на основе фактической информации требуются данные высочайшего качества. В хранилище данных должна гарантироваться локальная непротиворечивость данных, глобальная непротиворечивость данных, а также целостность данных на уровне ссылок, даже несмотря на использование "грязных" источников данных и громадные размеры базы данных. Хотя загрузка и подготовка данных - необходимые шаги, они все же не являются достаточными. Лишь способность дать ответы на запросы конечных пользователей является действительной мерой успешности создания хранилища данных. Анализ утверждает, что, чем больше ответов было успешно предоставлено пользователям, тем сложнее и изощреннее становятся очередные вводимые ими запросы.
Высокая производительность запросов
Управление на основе фактической информации и произвольный анализ не должны замедляться или стопориться из-за низкой производительности обработки запросов со стороны СУБД хранилища данных. Большие комплексные запросы для ключевых бизнес-операций должны завершаться за приемлемое время.
Широкая масштабируемость по размеру
Размеры хранилищ данных возрастают с огромной скоростью и достигают величин от сотен гигабайт до терабайт (1012 байт) и даже петабайт (1015 байт). Используемая РСУБД не должна иметь никаких архитектурных ограничений на размер базы данных и должна поддерживать модульное и параллельное управление. В случае сбоя РСУБД должна сохранять готовность к работе и предоставлять механизм восстановления до исходного состояния. РСУБД должна поддерживать работу с устройствами массовой памяти, например оптические диски или иерархические устройства хранения. Наконец, производительность выполнения запросов должна зависеть не от размера базы данных, а прежде всего от сложности самого запроса.
Масштабируемость по количеству пользователей
В настоящее время считается, что доступ к хранилищу данных будет ограничен только относительно небольшим кругом менеджеров. Однако маловероятно, что такая тенденция сохранится и при возрастании значения хранилищ данных. По некоторым оценкам, в недалеком будущем РСУБД для хранилищ данных должны будут поддерживать работу сотен и даже тысяч параллельно работающих пользователей, обеспечивая при этом приемлемую производительность выполнения их запросов.
Возможность организации сети хранилищ данных
Хранилище данных должно обладать способностью работать в большой сети, состоящей из многих хранилищ данных. Хранилище данных должно включать инструменты, которые координировали бы перемещение подмножеств данных между отдельными хранилищами. На своей рабочей станции пользователи должны иметь возможность просматривать и работать с содержимым нескольких хранилищ данных.
Наличие средств администрирования хранилища
Исключительно большой размер и цикличная природа хранилищ данных требует наличия простых и в то же время гибких инструментов администрирования. РСУБД должна предоставлять средства управления для ограничения ресурсов, подсчета накладных расходов для всех пользователей, а также систему установки приоритетов выполнения запросов для удовлетворения потребностей различных категорий пользователей и видов деятельности. РСУБД должна также иметь средства для отслеживания и настройки режимов рабочей нагрузки, необходимых для оптимизации производительности и пропускной способности системы. Наиболее очевидной и ощутимой ценностью реализации хранилища данных является беспрепятственный творческий доступ конечных пользователей к данным.
Поддержка многомерного интегрированного анализа
Ценность многомерных представлений - общепризнанный факт, а потому поддержка работы с ними непременно должна быть предусмотрена в РСУБД, используемой для организации хранилища данных, поскольку это является условием для обеспечения максимальной производительности реляционных OLAP-инструментов. РСУБД должна поддерживать быстрое и простое создание предварительно подготовленных итоговых значений для больших хранилищ данных, а также предоставлять инструменты для автоматизации процесса создания этих предварительно вычисленных обобщений. Динамическое вычисление обобщенных значений должно быть согласовано с требованиями обеспечения необходимого уровня производительности интерактивной работы пользователей. В том числе размещение базы данных на дисках с разной скоростью передачи данных, использование распараллеливания операций, использование хранилищ резервного копирования на дисковых массивах.
Расширенный набор функциональных средств запросов
Конечным пользователям необходимо иметь возможность выполнять аналитические вычисления, последовательный и сравнительный анализ, согласованный доступ к детальным и обобщенным данным. Использование языка SQL в клиент-серверной среде создания запросов по типу "выбери и щелкни" иногда может оказаться непрактичным или даже просто невозможным из-за высокой сложности пользовательских запросов. В РСУБД должен быть предусмотрен полный набор всех необходимых аналитических инструментов.
Параллельные СУБД
При работе с хранилищем данных обычно требуется обработать огромное количе-ств6\данных, а технология параллельной работы с базами данных предлагает эффективное решение для обеспечения необходимого роста производительности. Успешность работы параллельных СУБД зависит от эффективного управления многими ресурсами, например, таким, как процессоры, память, диски и сетевые подключения.
По мере возрастания популярности хранилищ данных фирмы-разработчики создают большие СУБД, предназначенные для систем поддержки принятия решений и использующие технологию организации параллельных вычислений. Основная цель состоит в решении поставленных пользователем задач с использованием нескольких узлов, параллельно работающих над одной и той же проблемой. Важнейшими характеристиками параллельных СУБД являются масштабируемость, оперативность и готовность.
Параллельные СУБД могут одновременно выполнять сразу несколько операций с базой данных, разбивая отдельные задачи на малые части так, чтобы они могли быть распределены между несколькими процессорами. Параллельные СУБД должны уметь выполнять параллельные запросы. Иначе говоря, они должны уметь разбивать большие сложные запросы на подзапросы, параллельно запускать отдельные подзапросы на выполнение, а затем собирать вместе полученные результаты. Такие СУБД должны уметь выполнять в параллельном режиме загрузку данных, сканирование таблицы, а также архивирование и резервное копирование данных.
Существуют две основные архитектуры аппаратного обеспечения для выполнения параллельных вычислений, которые могут использоваться в качестве платформы для сервера базы данных в хранилищах данных:
Симметричная мультипроцессорная обработка (Symmetric Multiprocessing - SMP) - это группа тесно связанных процессоров, которые совместно используют оперативную и дисковую память.
Массовая мультипроцессорная обработка (Massively Multi-Processing - ММР) - это группа слабо связанных процессоров, каждый из которых использует свою собственную оперативную и дисковую память.
Интерактивная аналитическая обработка данных (OLAP)
В этом разделе рассматривается природа многомерных данных, а также то, как эти данные могут быть наилучшим образом представлены в базе данных, предназначенной для оперативной аналитической обработки данных (Online Analytical Processing - OLAP). Кроме того, описываются требования, которым должны удовлетворять OLAP-инструменты, и описываются основные характеристики трех категорий OLAP-инструментов: MOLAP, ROLAP и MQE. В конце данного раздела дается краткий обзор расширений языка SQL, предназначенных для выполнения более сложных функций анализа данных.
Основной вопрос при обработке информации заключается в том, как обрабатывать все более и более крупные базы данных, содержащие данные с постоянно усложняющейся структурой, сохранив при этом приемлемое время реакции системы на запрос. Архитектура "клиент-сервер" позволяет организациям устанавливать специализированные серверы, оптимизированные для решения задач специфического управления данными. Для таких бизнес-приложений, как анализ рынка и финансовое прогнозирование, требуется использовать запросо-центрированные схемы баз данных, которые, по сути, имеют вид многомерных массивов. Эти приложения характеризуются необходимостью извлекать большое количество записей из очень больших наборов данных и мгновенно вычислять на их основе итоговые значения. Предоставление поддержки для таких приложений является основным назначением всех OLAP-инструментов.
Оперативная аналитическая обработка (OLAP) Динамический синтез, анализ и консолидация больших объемов многомерных данных.
Термин "OLAP" был предложен Коддом в 1993 году и определяет архитектуру, которая поддерживает сложные аналитические приложения. Большинство OLAP-приложений создается на основе специализированных многомерных СУБД (или ММ СУБД (multi-dimensional DBMS)) с ограниченным набором данных и настраиваемым пользовательским интерфейсом приложений. OLAP-архитектура предусматривает определенные уровни с четким разделением функций между приложением и СУБД. На основе этого разделения появилось новое поколение OLAP-инструментов, предоставляющих такие возможности, которые позволяют обычным СУБД конкурировать со специализированными технологиями СУБД. Начав программировать, мы не сильно задумываемся о том, где хранить результаты выполнения той или иной программы. А, что если нам необходимо хранить все результаты выполнения какой-то программы? И срок жизни программы не один год, а частота выполнения хотя бы 2-3 раза в день…
Литература, рекомендуемая при самоподготовке
Глушаков С.В., Ломотько Д.В. Базы данных. Учебный курс, - Харьков: Фолио, М.: АСТ, 2000.
Голицина О.Л., Максимов И.В., Попов И.И. �Базы данных�: Учебное пособие. – М.: 2016
Ефимова О., Морозов В., Шафрин Ю. Курс компьютерной технологии. Том 1, 2. – М.: АБФ, 1998.
Карпова Т.С. Базы данных: модели, разработка, реализация. СПб.: Питер, 2001.
Конолли Т. и др. Базы данных, - М., СПб, Киев: Вильямс, 2001.
Работа с базами данных. Профессиональная разработка. Документация и обзоры, компакт-диск, www.alexsoft.ru, 2002.
Симанович С. и др. Специальная информатика: Учеб. Пособие. – М.: АСТпресс, 1998.
Фаронов В.В., Шумаков П.В. Delphi 4. Руководство разработчика баз данных. М.: Нолидж, 1999.
Форум Инфра-М, 2003.
Шафрин Ю. Информационные технологии - М.:АБФ,1998.
Пособие по дисциплине �Базы данных�27
Пособие по дисциплине �Базы данных�66
Уровень внешних моделей
1-й уровень
2-й уровень
3-й уровень
Концептуальный уровень
База данных
СУБД
Внешняя модель
Концептуальная модель
Физическая модель
ОС
Пользователь
Рабочая область
Буфер
БД
a
b
1
2
3
a
a
a
b
b
b
1
2
3
1
2
3
ОСТАТОК
СТУДЕНТ
УЧЕБНЫЙ ПЛАН
(СТРОКИ)
содержит
указывает
сдает
РЕЗУЛЬТАТЫ
СЕССИИ-ОЦЕНКИ
ДИСЦИПЛИНЫ
ПРЕПОДАВАТЕЛЬ
имеет
ДОЛЖНОСТЬ
требует сдачи
М
М
М
М
М
М
М
М
1
TWork
На примере этой наглядной таблицы, содержащей дневной объем работы (кол-во деталей) токарей продемонстрируем работу агрегатных функций.
Преобразуем нашу таблицу к более рациональному виду. Обратите внимание, что в ее структуру были добавлены два новых столбца:
Цена - за деталь (руб.)
Брак - испорчено деталей (шт.)
Они позволят продемонстрировать возможность несложных расчетов.
В литературе вы можете встретить это действие под называнием "построение агрегатов, основанных на скалярном выражении".