Банки и базы данных. Системы управления базами данных

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

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

Тема 10. Структура SQL

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

В стандарт SQL в качестве составных частей входят язык определения данных (Data Definition Language, DDL), язык манипулирования данными (Data Manipulation Language, DML) и язык управления данными (Data Control Language, DCL).

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

Существуют статический и динамический встроенный SQL.

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

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

Язык определения данных используется для создания и изменения структуры БД и ее составных частей - таблиц, индексов, представлений (виртуальных таблиц), а также триггеров и сохраненных процедур. Основными его операторами являются операторы CREATE <DATABASE, TABLE, VIEW, INDEX, TRIGGER, PROCEDURE> (создать БД, таблицу БД, вид, индекс, триггер, процедуру), ALTER <> (изменить БД и т. д.), DROP <> (удалить БД и т.д.).

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

CREATE TABLE <имя таблицы>

(<имя столбца> <тип данных> [NOT NULL, PRIMARY KEY]

[,<имя столбца> <тип данных> [NOT NULL], [UNIQUE]]…)

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

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

ALTER TABLE <имя таблицы>

({ADD, MODIFY, DROP} <имя столбца> [<тип данных>]

[NOT NULL]

[,{ADD, MODIFY, DROP} <имя столбца> [<тип данных>]

[NOT NULL]]…)

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

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

DROP TABLE <имя таблицы>

Оператор создания индекса используется для создания индекса для одного или нескольких столбцов данной таблицы для целей выполнения запросов и поиска данных:

CREATE [UNIQUE] INDEX <имя индекса>

ON <имя таблицы>

(<имя столбца> [ASC/DESC]

[,<имя столбца> [ASC/DESC]…)

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

Язык манипулирования данными используется, как это следует из его названия, для манипулирования данными в таблицах БД. Он состоит из 4 основных операторов: SELECT (выбрать), INSERT (вставить), UPDATE (обновить), DELETE (удалить).

Оператор выборки записей:

SELECT [ALL/DISTINCT]

<список данных>

FROM <список таблиц>

[WHERE <условие выборки>]

[GROUP BY <имя столбца> [, <имя столбца>…]

[HAVING <условие поиска>]

[ORDER BY <спецификация> [, <спецификация>]…]

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

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

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

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

Операнд HAVING используется для дополнительной селекции записей при определении списков групп. Правила его использования такие же, что и для операнда WHERE.

Для упорядочения записей применяется операнд ORDER BY.

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

Оператор изменения записей:

UPDATE <имя таблицы>

SET <имя столбца> = {<выражение>. NULL}

[, SET <имя столбца> = {<выражение>, NULL}…]

[WHERE <условие>]

Операнд SET определяет список столбцов таблицы, в которые вносятся изменения, определенные логическими или арифметическими выражениями в условии операнда WHERE. Новые значения в столбцах могут быть и пустыми (NULL).

Оператор добавления новых записей:

INSERT INTO <название таблицы>

[(<список столбцов>)]

VALUES (<список значений>)

или

INSERT INTO <название таблицы>

[(<список столбцов>)]

<предложение SELECT>

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

Оператор удаления записей:

DELETE FROM <название таблицы>

[WHERE <условие>]

Из таблицы удаляются записи, удовлетворяющие условию, определенному операндом WHERE. В случае отсутствие операнда WHERE из таблицы удаляются все записи.

Язык управления данными используется для управления правами доступа к данным и выполнением процедур в многопользовательской среде. Более точно его можно назвать “язык управления доступом”. Он состоит из двух основных операторов GRANT (дать права) и REVOKE (забрать права).

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

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

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

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

При задании проверочного ограничения выполняется проверка (CHECK) некоторого условия для значения данных в каждой строке таблицы - указывается предикат, который использует значения атрибутов для вычисления некоторого значения. Предикат может принимать значения TRUE, FALSE и UNKNOWN. Ограничение CHECK будет нарушено, если предикат принимает значение FALSE.

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

Для задания ограничений используется оператор CONSTRAINT. Конструкция этого оператора имеет следующий вид:

CONSTRAINT [идентификатор ограничения] <имя ограничения>

<выражение>

Существуют следующие идентификаторы ограничений:

PK - ограничение на первичный ключ;

FK - ограничение на внешний ключ;

U - ограничение на уникальность значения;

DF - ограничение на значение по умолчанию;

CK - ограничение на проверочное условие CHECK.

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

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

1. Числовые типы данных. Типы данных INT и SMALLINT задают целые числа. Эти типы удобно задавать, например, для идентификаторов, количества, возраста и т.д. Типы данных NUMERIC и DECIMAL определяют десятичные числа с фиксированным количеством знаков после запятой. Их можно использовать для хранения результатов арифметических операций. Для определения данных с переменным количеством знаков после запятой используются типы REAL и FLOAT. Диапазон допустимых значений данных, определяемых этими типами, существенно больше по сравнению с десятичными числами.

2. Символьные типы данных. Типы CHAR и VARCHAR определяют строки постоянной и переменной длины соответственно. Тип данных TEXT (длинный текст) используется для хранения больших документов.

3. Денежные типы данных MONEY и SMALLMONEY позволяют хранить денежные величины с заданием при необходимости признака валюты.

4. Данные типа время/дата DATETIME и SMALLDATETIME. Такой тип данных удобно задавать при необходимости обработки временных интервалов, например, для сравнения текущей даты с какой-либо фиксированной датой.

5. Логические типы данных. Тип данных BIT определяет так называемые булевы величины, которые могут принимать два значения - Истина и Ложь.

6. Неструктурированные типы данных BINARY, VARBINARY, IMAGE применяются для определения таких данных, как графические объекты, видеоизображения и других неструктурированных байтовых потоков.

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

Тема 11. Запросы на выборку

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

Основным оператором для отбора информации из БД является оператор SELECT.

Формат этого оператора имеет следующий вид:

SELECT [DISTINCT]

{<функция агрегирования>/<выражение для вычисления значения>

[AS <имя столбца>]}

FROM {{<имя таблицы> [AS] [<имя корреляции>].[<имя столбца>.,..]}

{подзапрос [AS] [<имя корреляции>].[<имя столбца>.,..]}

<соединенная таблица>}.,..

[WHERE <условие>]

[GROUP BY {{[<имя таблицы>/<имя корреляции>]}].[<имя

столбца>}.,..}]

[HAVING <условие>]

[UNION/INTERSECT/EXCEPT][ALL]

[CORRESPONDING [BY (<имя столбца>.,..)]]

<оператор SELECT>/ TABLE <имя таблицы>/<конструктор значений

таблицы>

[ORDER BY {{<столбец-результат> [ASC/DESC]}.,..}

Операнды SELECT, FROM, WHERE, GROUP BY, HAVING и ORDER BY должны записываться в той последовательности, в которой они перечислены.

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

Самый простой вариант запроса соответствует случаю декартова произведения таблиц Т1 и Т2:

SELECT *

FROM T1,T2

Реализация запроса в виде: SELECT T1.A, T2.B соответствует проекции декартова произведения двух таблиц на столбцы А и В исходных таблиц, однако в отличие от соответствующей операции реляционной алгебры дубликаты всех строк при этом сохраняются.

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

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

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

Запросы могут содержать следующие функции агрегирования:

1. COUNT - функция подсчета. Имеется два варианта использования этой функции. В первом случае функция подсчитывает количество строк в таблице или в группе, если используется в операнде GROUP BY. Смысл такого использования заключается в том, что результат подсчета не зависит от того, имеются ли в столбцах значения NULL и указан ли параметр DISTINCT. Во втором случае результат зависит от значений этих параметров.

2. SUM - суммирование значений в столбце.

3. MAX, MIN, AVERAGE - вычисление максимального, минимального и среднего значений.

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

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

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

RIGHT JOIN (правое внешнее соединение) является обратным по сравнению с левым внешним соединением - в соединенной таблице имеются все строки из второй таблицы и только те строки из первой таблицы, для которых во второй таблице имеются поля связи.

Кроме перечисленные типов соединения, которые поддерживаются большинством СУБД, в ряде систем присутствуют типы соединений FULL JOIN и UNION JOIN, которые фактически являются объединением и пересечением типов LEFT JOIN и RIGHT JOIN соответственно.

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

1. Интервальный предикат используется для задания диапазона значений:

WHERE [NOT] <выражение> BERWEEN <нижнее значение

выражения> AND <верхнее значение выражения>.

2. Предикат IN:

WHERE [NOT] <выражение> [NOT] IN (<список

значений>/<подзапрос>)

3. Предикат проверки на неопределенное значение:

WHERE <значение> IS [NOT] NULL

4. Предикат подобия:

WHERE <выражение 1> [NOT] LIKE <выражение 2>

Кроме того, при выполнении подзапросов в условии WHERE может быть использован операнд EXIST, который проверяет возврат подзапросом каких-либо данных:

WHERE [NOT] EXIST <подзапрос>.

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

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

Операнд HAVING часто используется вместе с операндом GROUP BY, причем условие, записанное в этом операнде, имеет то же самое значение для групп, что и условие, записанное в операнде WHERE для строк. Выражение оператора HAVING должно принимать единственное значение для всей группы.

Для упорядочения данных, получаемые в результате запроса, применяется операнд ORDER BY. В списке этого операнда указываются столбцы и строки сортируются в соответствии со значениями этих столбцов. В этом списке могут быть как названия столбцов, так и номера столбцов (целые числа). При этом столбцы всегда нумеруются целым числом в случае, если столбцы являются вычисляемыми, либо получаются в результате использования операнда UNION. Если в списке операнда UNION указывается список столбцов, то в этом случае происходит упорядочение по составному ключу.

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

1. В условии WHERE могут быть заданы в явном виде правила соединения таблиц.

2. Могут быть использованы вложенные запросы.

3. Можно использовать различные конструкции с использованием операнда JOIN.

Рассмотренные выше конструкции относились к поисковым запросам. В корректирующих запросах основными операторами являются операторы INSERT, UPDATE и DELETE.

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

INSERT INTO <название таблицы>

[<название столбца>.,..]<условие запроса>/<конструктор значений>

{DEFAULT VALUES}

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

Оператор UPDATE используется для корректировки содержания таблицы:

UPDATE <название таблицы> SET <название столбца> = <новое

значение> [,<название столбца > = < новое значение >…]

[WHERE <условие>]

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

Оператор DELETE используется для удаления строк из таблицы:

DELETE

FROM <название таблицы>

[WHERE <условие>]

Если условие WHERE отсутствует, то удаляются все строки таблицы.

Тема 12. Создание представлений

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

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

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

CREATE VIEW <имя представления>

[(<имя столбца> [, <имя столбца>]…)]

AS <оператор SELECT>

[WITH[CASCADED/LOCAL]CHECK OPTIONS]

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

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

Для уничтожения представления используется стандартный оператор DROP:

DROP VIEW <имя представления>

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

1. Скрытие от пользователя не предназначающейся для него информации.

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

3. Упрощение сложных запросов.

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

Последняя строка в операторе создания представления (WUTH CHECH OPTION) означает, что при создании представления должно проверяться некоторое заданное условие.

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

Горизонтальное представление используется, главным образом, для уменьшения объема реальных таблиц и имеет вид:

CREATE VIEW <имя представления>

AS

SELECT *

FROM <имя таблицы>

WHERE <имя столбца> = <значение>

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

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

CREATE VIEW <имя представления>

[(<имя столбца> [, <имя столбца>]…)]

AS

SELECT [(<имя столбца> [, <имя столбца>]…)]

FROM <имя таблицы>

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

CREATE VIEW <имя представления>

[(<имя столбца> [, <имя столбца>]…)]

AS

SELECT [([<функция>]<имя столбца> [, <имя столбца>]…)]

FROM <имя таблицы>

GROUP BY [(<имя столбца> [, <имя столбца>]…)]

Объединенные представления используются для представления в одной виртуальной таблице данных из нескольких таблиц БД:

CREATE VIEW <имя представления>

[(<имя столбца> [, <имя столбца>]…)]

AS

SELECT [(<имя столбца> [, <имя столбца>]…)]

FROM [(<имя таблицы> [, <имя таблицы>]…)]

[WHERE <условие>]

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

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

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

2. В корректирующем запросе не должны присутствовать данные, полученные с помощью операторов GROUP BY и HAVING.

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

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

5. В представлении не должно содержаться вложенных запросов.

6. В представлении нельзя использовать соединение таблицы “сама с собой”.

7. Ни на один столбец в выражении оператора SELECT нельзя ссылаться более одного раза.

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

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

DECLARE <имя курсора> CURSOR

FOR <подзапрос>

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

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

OPEN CURSOR <имя курсора>.

При закрытии курсора используется команда

CLOSE CURSOR <имя курсора>.

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

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

Тема 13. Распределенная обработка данных

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

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

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

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

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

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

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

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

Рассмотрим основные особенности функционирования БД в распределенных системах.

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

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

В дальнейшем обе эти системы будем называть распределенными системами.

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

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

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

В зависимости от количества узлов сети выделяют двухзвенную и трехзвенную архитектуру модели клиент-сервер.

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

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

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

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

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

5. Модель распределенной БД. В этой модели на мощном клиенте располагаются не только прикладные программы, но и часть самих данных.

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

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

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

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

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

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

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

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

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

Тема 14. Модели транзакций

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

В настоящее время существует ряд моделей транзакций, к основным из которых относятся плоские или классические транзакции, цепочечные (многозвенные) и вложенные транзакции. Однако ко всем моделям транзакций предъявляется общий набор требований, получивший название ACID (Atomicity - атомарность, Consistency - согласованность, Isolation - изолированность, Durability - долговечность).

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

Согласованность - при выполнении транзакции БД переходит из одного согласованного состояния в другое согласованное состояние.

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

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

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

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

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

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

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

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

- индивидуальный откат транзакции;

- потеря содержимого оперативной памяти (мягкий сбой);

- выход из строя носителя внешней пвмяти (жесткий сбой).

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

Для реализации возможности восстановления данных существует специальная структура, которая называется журналом транзакций. Основным принципом журнала транзакции является обеспечение протокола, называемого Write Ahead Log (WAL) - “пиши сначала в журнал”. Это означает, что любая запись об изменении объекта данных должна сначала попадать во внешнюю память журнала транзакций, и только потом во внешнюю память базы данных. Таким образом, если во внешней памяти базы данных содержится информация о некотором объекте, над которым уже выполнена операция модификации, то в журнале транзакций заведомо имеется соответствующая запись.

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

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

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

1. В ходе выполнения транзакции пользователь должен видеть только согласованные данные.

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

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

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

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

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

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

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

IS (Intented for Shared Lock) - по отношению к некоторому составному объекту данный тип блокировки означает намерение захватить часть этого объекта в совместном режиме. Если, например, необходимо прочитать часть кортежей из данного отношения, то это отношение вначале захватывается в режиме IS.

IX (Intented for eXclusive Lock) - по отношению к некоторому составному объекту данный тип блокировки означает намерение захватить часть этого объекта в монопольном режиме. При необходимости удаления части кортежей из данного отношения это отношение вначале захватывается в режиме IX.

SIX (Shared, Intented for eXclusive Lock) - по отношению к некоторому составному объекту данный тип блокировки означает совместный захват этого объекта с намерением впоследствии захватывать части этого объекта в монопольном режиме. Этот тип блокировки используется, если, например, выполняется просмотр записей отношения и при необходимости некоторые записи нужно удалить.

Для реализации протокола гранулированных захватов необходимо выполнение следующего условия: прежде чем устанавливается S-блокировка (или X-блокировка) на часть некоторого объекта (например, на кортеж отношения), должна быть установлена IS-блокировка (или IX-блокировка) на весь объект (например, на все отношение). Здесь вместо IS- и IX-блокировок могут быть более сильные блокировки.

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

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

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