Московский государственный университет печати

Реляционные базы данных получили широкое распространение в персональных компьютерах. Наиболее известны такие СУБД, как dBASE, Paradox и особенно FoxPro. СУБД Oracle, Sybase, Informix, BTrieve, Ingress, InterBase были изначально предназначены для работы в сети с большими объемами данных.

В основе реляционной модели лежит математическое понятие теоретико-множественного отношения, которое представляет собой подмножество декартова произведения списка доменов.

Домен - множество значений (например, множество целых чисел). Декартовым произведением доменов D₁ , D₂ , .., D_k (обозначается как D₁ ×D₂ × ...×D_k ) называется множество всех кортежей (V₁ , V₂ , ..., V_k ) длины k, таких, что V_i D_i , i = 1, I. Например, если k = 2, D₁ = {0,1} и D₂ = {a,b,c}, то D₁ ×D₂ есть {(0, a), (0, b), (0, c), (1, a), (1, b),(1, c)}, а отношением может быть, например, {(0, a), (0, c), (1, b)}.

Элементы отношения называются кортежами и имеют арность k (степень отношения), причем i-м компонентом является V_i . Отношение удобно представлять таблицей - совокупностью всех кортежей: каждая строка есть кортеж и каждый столбец соответствует одному компоненту. Кортежи обычно нумеруются и их количество определяет размерность таблицы. Столбцы называются атрибутами, и им часто присваиваются имена. Упорядоченный список имен атрибутов отношения называется схемой отношения. Если отношение называется ИГРОКИ и его схема имеет атрибуты A₁ , A₂ , ..., A_k , то такую схему будем записывать как ИГРОКИ (A₁ , A₂ , ..., A_k ).

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

Данные из диаграммы объектов-связей представляются двумя видами отношений.

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

2. Связь между наборами объектов E₁ , E₂ , ..., E_k представляется отношением, схема которого состоит из атрибутов ключей каждого из этих наборов.

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

Для реляционных отношений характерны следующие особенности.

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

2. Порядок кортежей в таблице несуществен.

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

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

5. Если между двумя реляционными отношениями существует зависимость, то одно отношение является исходным, второе -подчиненным.

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

Пример 5.1. Представим БД «Учебный процесс»в виде реляционной модели (табл. 5.1).

Таблица 5.1.

а) Отношение ГРУППА

Индекс ИГ	Название группы НГ	Количество ответов КОЛ	Проходной балл ПБАЛЛ
1 2 3	А1 А2 А3	16 28 18	4,3 4,0 4,3

б) Отношение СТУДЕНТ

Номер зачетной книжки НЗ	ИГ	Фамилия, и,о СФИО	Год рождения ГР	Проходной балл ПБ
А-1746 А-1747 А-1748	А1 А1 А1	Серов А.П. Киров П.Г. Сухов П.Н.	1979 1980 1981	4,3 4,0 4,2

в) Отношение КАФЕДРА

Код кафедры ККАФ	Название кафедры НКАФ	Телефон ТЕЛ	Фамилия, и, о. зав ФЗАВ
К1 К2 К3	ИиУС АПП ТПП	154-12-80 171-12-85 212-10-81	Сорокин П.В. Борисов Б.В. Степанов И.В.

г) Отношение ПРЕПОДАВАТЕЛЬ

Табельный № ТБН	Фамилия, и, о ПФИО	Ученая степень СТ	Ученое звание ЗВ	ККАФ
381 401 402	Шаталов А.С. Сидоров А.Т. Тараканов П.Т.	д.т.н. к.т.н. к.ф.-м. н.	профессор доцент доцент	К1 К1 К1

д) Отношение ПРЕДМЕТ

Код предмета КП	Название предмета НП	Всего часов ВЧ	Лекции ЛЧ	Практ.-лаб. занятия ПЧ	Семинаров СЕМ
П1 П2 П3	Информатика Кибернетика Математика	350 300 600	130 120 200	К1 К1 К1	2 3 4

е) Отношение ИЗУЧЕНИЕ

Индекс гр. ИГ	Код предмета КП	Таб. № ТБН	Вид занятий ВЗ	Часы ЧАС
2 2 1 1	П2 П2 П3 П3	402 381 381 401	практич. лекции лекции практич.	180 120 200 400

ж) Отношение УСПЕВАЕМОСТЬ

Индекс ИГ	Номер зачетной книжки НЗ	Код предмета КП	Таб. № ТБН	Вид занятий ВЗ	Оценка ОЦ
1 1 1	А-1746 А-1747 А-1748	П3 П3 П3	381 381 381	экзамен экзамен экзамен	5 4 3

Далее отношения (например, табл. 5.1, а) будем записывать и в другой форме

ГРУППА(Индекс, Название, Количество_студентов, Проходной_балл),

где подчеркнут ключ.

Процедуры создания и использования реляционных БД основываются на теории реляционных БД, подробно рассмотренной в главе 4, результаты которой используем здесь в прикладных целях. При введении структуры данных используют соответствующие форматы данных. Для таблицы ПРЕПОДАВАТЕЛЬ они представлены в табл. 5.2.

Таблица 5.2.

Форматы отношения ПРЕПОДАВАТЕЛЬ

Имя поля	Ключ кн.	Уникальное поле	Обязательное поле	Тип данных ВЗ	Размер	Подписи поля
ТБН ПФИО СТ ЗВ ККАФ	* +	да	да да нет нет да	Числовой Текстовой Текстовой Текстовой Текстовой	Целое 30 12 12 6	Таб. № ПФИО Уч. ст. Уч. зв. Код каф.

Вся БД (табл. 5.1) представлена в 4НФ, поэтому отразим схему связей между ее отношениями (рис. 5.1), где символами *, +, x помечены первичные, внешние и составные ключи соответственно.

Основная задача при проектировании реляционных БД - формирование оптимальных отношений.

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

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

На этом «бумажное»построение БД заканчивается. Компьютерная реализация БД определяется языками описания (ЯОД) и манипулирования (ЯМД) данными.

Они могут базироваться на реляционной алгебре (процедурные языки) и реляционном исчислении кортежей и доменов (декларативные языки). На исчислении кортежей основан язык SQL (§ 5.3.1), на исчислении доменов - язык QBE (§ 5.3.2).

Для SQL имеется много вариантов и диалектов. Здесь изложим основные положения базового варианта: более подробное описание языка приведено в .

Иллюстрацию языка SQL проведем на примере базы данных, представленной в табл. 5.3 - 5.5.

Таблица 5.3.

ПРОДАВЦЫ

ПНОМ	ПИМЯ	город	КОММ
1001 1002 1007 1004 1003	Строков Кирюшин Аврорин Удалов Козлов	Москва Пермь Москва Курск Орел	.12 .11 .13 .15 .10

Названия полей:

ПНОМ - уникальный нмер, назначенный каждому продавцу (номер служащего);
ПИМЯ - имя продавца;
город - расположение продавца (город);
КОММ - комиссионные продавцов в десятичной форме

Таблица 5.4.

ЗАКАЗЧИКИ

ЗНОМ	ЗИМЯ	город	рейтинг	ПНОМ
3001 3002 3003 3004 3006 3008 3007	Иванов Петров Ковров Сидоров Крабов Конкин Красин	Москва С.-Петербург Сочи Кириши Русса Пермь Луга	100 300 200 300 100 300 100	1001 1003 1002 1003 1001 1007 1004

Названия полей:

ЗНОМ - уникальный номер, назначенный каждому заказчику;
ЗИМЯ - имя заказчика;
город - расположение заказчика (город);
рейтинг - код, указывающий уровень предпочтения данного заказчика перед другими: более высокий номер указывает на большее предпочтение (рейтинг);
ПНОМ - номер продавца, назначенному этому заказчику (из таблицы Продавцов)

Таблица 5.5.

ЗАКАЗЫ

ПРНОМ	СУМПР	ДАТПР	ЗНОМ	ПНОМ
3001 3003 3002 3005 3006 3009 3007 3008 3010 3011	18.69 767.19 1900.10 5160.46 1098.16 1713.23 75.75 4783.00 1309.95 9198.88	10/03/1990 10/03/1990 10/03/1990 10/03/1990 10/03/1990 10/04/1990 10/04/1990 10/05/1990 10/06/1990 10/06/1990	3008 3001 3007 3003 3008 3002 3004 3006 3004 3006	1007 1001 1004 1003 1007 1003 1002 1001 1002 1001

Названия полей:

ПРНОМ - уникальный номер, данный каждому приобретению;
СУМПР - значение суммы приобретений;
ДАТПР - дата приобретения;
ЗНОМ - номер заказчика, делающего приобретение (из таблицы Заказчиков);
ПНОМ - номер продавца, продающего приобретения (из таблицы Продавцов).

Для нее схема Access-связей показана на рис. 5.2.

В языке SQL возможно выделить три основные группы операций: создание (CREATE), обновление (INSERT, UPDATE, DELETE), запрос (SELECT). Они имеют следующие стандарты, в которых приняты обозначения: | - все, что предшествует символу, можно заменить тем, что следует за ним; {} - единое целое для применения символа; [] - необязательное выражение; ... - повторяется произвольное число раз ; .,... - повторяется произвольное число раз, но любое вхождение отделяется запятой.

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

({<имя столбца><тип данных> [размер]

[<тип столбца> ...]}.,...);

[<тип таблицы>].,..); (5.1)

Типы данных могут быть INTEGER, CHARACTER, DECIMAL, NUMERIC, SMALLINT, FLOAT, REAL, PRECISION, LONG, VARCHAR, DATE, TIME. Четыре последние типа не входят в стандарт SQL, но им могут поддерживаться.

Тип столбца (и тип таблиц) может быть UNIQUE, PRIMARY KEY, CHECK <предикат>, DEFAULT=<список полей>, REFERENCE <имя таблицы> [(<имя столбца>.,...)].

INSERT INTO <имя таблицы>[(<имя столбца>.,...)] (5.2)

{VALUES(<список полей>).,...}

DELETE <имя предиката> (5.3)

[WHERE <предикат>

|WHERE CURRENT OF <имя курсора>

(*только вложенный*)];

SELECT * |[{DISTINCT|ALL]<список полей> (5.4)

FROM <имя таблицы> [<алиас>]} ...

[WHERE <предикат>]

[GROUP BY {<имя столбца>|<целое>}.,...]}

[HAVING <предикат>] [ORDER BY {<имя столбца>}]|<целое>}...]

[{UNION}];

Выделяют две разновидности языка SQL: интерактивный и вложенный.

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

Вложенный SQL состоит из команд SQL, помещенных внутри программ, которые обычно написаны на некотором другом языке (типа КОБОЛА или Паскаля). Это делает эти программы более мощными и эффективными.

Будет рассматривать преимущественно - при отсутствии упоминаний - интерактивный язык.

В нем возможно выделить:

DDL (Язык Описания Данных) - это язык описания схемы, и в ANSI он состоит из команд, создающих объекты (таблицы, индексы, виды) в базе данных;

DML (Язык Манипулирования Данными) - это набор команд, определяющих, какие значения представлены в таблицах в любой момент времени;

DCD (Язык Управления Данными) состоит из средств, которые определяют разрешение пользователю выполнять определенные действия.

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

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

2) использование БД - запрос в различных формах (в том числе с обновлением).

Отдельно рассмотрим основы вложенного языка SQL.

Структура таблиц.

Таблицы («пустые») создаются командой CREATE TABLE (выражение (5.1)).

CREATE TABLE Продавцы

(пном integer,

пимя char (10), (5.5)

город char (10),

комм decimal);

Команда CREATE TABLE в основном определяет имя таблицы, набор имен столбцов, указанных в определенном порядке, типы данных и размеры столбцов. В SQL возможно задание следующих типов данных: integer, character, numeric, decimal, smallint, float, real, double, precision, long, varchar, date, time.

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

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

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

<тип данных><размер>;

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

Удаление производится по команде

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

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

Аналогично создаются и удаляются индексы.

Базовая таблица (5.5)

Таблица 5.5.

ЗАКАЗЫ

ПРНОМ	СУМПР	ДАТПР	ЗНОМ	ПНОМ
3001 3003 3002 3005 3006 3009 3007 3008 3010 3011	18.69 767.19 1900.10 5160.46 1098.16 1713.23 75.75 4783.00 1309.95 9198.88	10/03/1990 10/03/1990 10/03/1990 10/03/1990 10/03/1990 10/04/1990 10/04/1990 10/05/1990 10/06/1990 10/06/1990	3008 3001 3007 3003 3008 3002 3004 3006 3004 3006	1007 1001 1004 1003 1007 1003 1002 1001 1002 1001

Названия полей:

ПРНОМ - уникальный номер, данный каждому приобретению;
СУМПР - значение суммы приобретений;
ДАТПР - дата приобретения;
ЗНОМ - номер заказчика, делающего приобретение (из таблицы Заказчиков);
ПНОМ - номер продавца, продающего приобретения (из таблицы Продавцов).

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

Структура и содержание видов. Только что созданная таблица называется базовой. Можно создавать представление (вид, View) - таблицы, содержимое которых берется или выводится из других таблиц. Вид создается командой CREATE VIEW:

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

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

Имеются некоторые виды запросов, которые недопустимы в определениях представлений: одиночное представление должно основываться на одиночном запросе; ОБЪЕДИНЕНИЕ (UNION) и ОБЪЕДИНЕНИЕ ВСЕГО (UNION ALL), агрегатные функции, DISTINCT в определении, вычисляемые поля не разрешаются в работе с представлениями; упорядочение (ORDER BY) никогда не используется в определении представлений.

Удаление представлений осуществляется (его владельцем) командой

DROP VIEW <имя вида>.

Заполнение БД данными. Значения могут быть помещены и удалены из полей командами языка DML (Язык Манипулирования Данными - ЯМД) INSERT (ВСТАВИТЬ), DELETE (УДАЛИТЬ) - выражения (5.2) и (5.3). Так, например, чтобы ввести строку в таблицу Продавцов, можно использовать следующее условие:

Можно вставлять и пустое значение (NULL).

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

Отметим, что столбцы «рейтинг» и «пном» - отсутствуют: эти строки автоматически установлены в значение - по умолчанию.

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

Теперь таблица пуста и ее можно окончательно удалить командой DROP TABLE. Обычно нужно удалить только некоторые определенные строки из таблицы, для чего используется предикат. Например, чтобы удалить данные продавца Козлова из таблицы Продавцы, можно ввести

Команды INSERT, DELETE совместно с командой UPDATE используются в процедуре обновления при эксплуатации БД.

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

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

Перечислим некоторые ограничения.

1. Исключение пустых (NULL) указателей введением команды NOT NULL.

2. Уникальность данных и первичные ключи.

   Ограничение столбца UNIQUE в поле при создании таблицы отклонит любую попытку ввода в это поле для одной из строк значения, которое уже представлено в другой строке. Это ограничение может применяться только к полям, которые были объявлены как непустые (NOT NULL).

3. SQL поддерживает первичные ключи непосредственно с ограничением Первичный Ключ (PRIMARY KEY).Первичные ключи не могут иметь значений NULL. Это означает, что, подобно полям в ограничении UNIQUE, любое поле используемое в ограничении PRIMARY KEY, должно уже быть объявлено NOT NULL.

4. Ограничения на значения полей. Для этого используется ограничение CHECK: чтобы предотвратить ошибку неправильного введения значения комм, наложим ограничение столбца - CHECK (комм меньше, чем 1).

   Сказанное может быть представлено в виде

   CREATE TABLE Продавцы

   (пном integer NOT NULL PRIMARY KEY,

   пимя char(10) NOT NULL UNIQUE,

   город char(10),

   комм decimal CHECK (comm < 1));

   Могут быть заданы интервалы и множества значений ограничений.

   Создадим предварительно таблицу Заказы:

   CREATE TABLE Orders

   (прном integer NOT NULL UNIQUE,

   сумпр decimal,

   датпр date NOT NULL,

   зном integer NOT NULL,

   пном integer NOT NULL);

5. Установка значений по умолчанию.

   Значение DEFAULT (ПО УМОЛЧАНИЮ) указывается в команде CREATE TABLE тем же способом что и ограничение столбца.

   Если офис - в Орле и подавляющее большинство продавцов тоже живут в в Орле, то по умолчанию:

   CREATE TABLE Продавцы

   (пном integer NOT NULL UNIQUE,

   пимя char(10) NOT NULL UNIQUE,

   город char(10) DEFAULT = «Орел»,

   комм decimal CHECK (comm < 1);

6. Ограничения на внешний ключ (ссылочная целостность). SQL поддерживает ссылочную целостность в команде CREATE TABLE (или ALTER TABLE) ограничением FOREIGN KEY с синтаксисом

   FOREIGN KEY <список полей> REFERENCES

   <таблица с родительским ключом> [ <список полей>.

Создадим таблицу Заказчиков с полем пном, определенным в качестве внешнего ключа, ссылающегося на таблицу Продавцов:

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

Таким образом созданы все три таблицы и установлены связи между ними.

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

1) ограничить или запретить изменение (способом ANSI), обозначив, что изменения в родительском ключе ограничены (RESTRICTED);

2) можно сделать изменение в родительском ключе и тем самым автоматические изменения во внешнем ключе, т.е. каскадное изменение (CASCADES);

3) провести изменение в родительском ключе и установить автоматически внешний ключ в NULL (полагая, что NULLS разрешен во внешнем ключе) - пустое изменение внешнего ключа (NULL).

Например, обновление и уничтожение

Если теперь попробовать удалить Строков из таблицы Продавцов, команда будет недопустима, пока не будет изменено значение поля пном Заказчиков и Иванов и Крабов - для другого назначенного продавца.

В то же время можно изменить значение поля пном для Строкова, а 1009 Иванов и Крабов будут также автоматически изменены.

Возможны и NULL (пустые)-изменения. Например,

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

Система разрешений. Возможна система разрешения (привилегий) или запрета доступа к данным.

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

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

Рассмотрим основные принципы ее построения на базе языка SQL.

Различают следующие приоритеты (по убыванию): роль, пользователь, группа, общий доступ (public).

Имеются разрешающие и запрещающие действия.

Разрешение дается оператором вида

Последняя строка говорит о передаче права пользования.

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

Создание пользователя Илья (которому предоставляется возможность создания баз данных), выполняемое администратором БД, определяется командой

Имеется несколько типов привилегий, соответствующих нескольким типам операций. Привилегии даются и отменяются двумя командами SQL : GRANT (ДОПУСК) и REVOKE (ОТМЕНА).

Каждый пользователь в среде SQL имеет специальное идентификационное имя (идентификатор - ID) или номер.

ID разрешения - это имя пользователя, и SQL может использовать специальное ключевое слово USER, которое ссылается на идентификатор доступа, связанный с текущей командой. Команда интерпретируется и разрешается (или запрещается).

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

Привилегии относятся к командам SELECT, INSERT, UPDATE, DELETE, REFERENCES (определение внешнего ключа, который использует один или более столбцов этой таблицы как родительский ключ).

К нестандартным командам относятся INDEX (ИНДЕКС), дающий право создавать индекс в таблице, SYNONYM (СИНОНИМ), позволяющий создавать синоним для объекта, и ALTER (ИЗМЕНИТЬ) - для выполнения команды ALTER TABLE в таблице. Механизм SQL назначает пользователям эти привилегии с помощью команды GRANT.

Например, пользователь Илья имеет таблицу Заказчиков и хочет позволить пользователю Петр выполнить запрос к ней:

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

Возможны и групповые привилегии:

Для команд UPDATE и REFERNCES можно указывать и отдельные поля:

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

SQL поддерживает два аргумента для команды GRANT, которые имеют специальное значение: ALL PRIVILEGES (ВСЕ ПРИВИЛЕГИИ) или просто ALL - для команд и PUBLIC (ОБЩИЕ) - для пользователей.

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

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

Существует целый ряд вариантов работы с видами .

В системе любого размера всегда имеются некоторые типы суперпользователей - чаще всего Администратор Базы Данных, или DBA. У него имеются такие системные привилегии: CONNECT (Подключить), RESOURCE (Ресурс) и DBA (Администратор Базы Данных).

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

Некоторые системы имеют специального пользователя, иногда называемого SYSADM или SYS (Системный Администратор Базы Данных), который имеет наивысшие полномочия.

Команда GRANT, в измененной форме, является пригодной для использования с привилегиями объекта, как и с системными привилегиями:

GRANT RESOURCE TO Мирон;

Естественно, пользователь Мирон должен быть создан.

У пользователя может быть и пароль (например, Иван). Тогда команда имеет вид

GRANT CONNECT TO Федор IDENTIFIED BY Иван;

что приведет к созданию пользователя с именем Федор, даст ему право регистрироваться и назначит ему пароль Иван.

Если нужно запретить пользователю регистрироваться, следует использовать для REVOKE привилегию CONNECT, которая «удаляет» этого пользователя.

Запрещение (на создание таблиц в БД newa группе clerck) имеет вид

GRANT NOCREATE

ON DATABASE newa

TO GROUP clerk;

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

SQL позволяют создавать синонимы для таблиц (что не является стандартом ANSI).

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

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

CREATE SYNONYM Москва4 FOR Илья.Заказчики;

Теперь Петр может использовать таблицу с именем Москва4 в команде точно так же, как ее использует Илья.Заказчики.

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

DROP SYNONYM Москва4;

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

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

Системы, которые используют области базы данных, называемые Data Base Spaces (DBS), позволяют с помощью команд SQL обрабатывать эти области как объекты.

DBS создаются командами CREATE DBSPACE (СОЗДАТЬ DBS), ACQUIRE DBSPACE (ПОЛУЧИТЬ DBS) или CREATE TABLESPACE (СОЗДАТЬ ТАБЛИЧНУЮ ОБЛАСТЬ) в зависимости от используемой реализации. Одна DBS может вмещать любое число пользователей, и отдельный пользователь может иметь доступ к многим DBS. Привилегия создавать таблицы, хотя и может быть передана по всей базу данных, часто передается в конкретной DBS.

Создадим DBS с именем табл1:

CREATE DBSPACE табл1

(pctindex 10,

pctfree 25);

Параметр pctindex определяет, какой процент DBS должен быть оставлен, чтобы сохранять в нем индексы таблиц, рtfree - процент DBS, который оставлен, чтобы расширять таблицам размеры их строк (команда ALTER TABLE может добавлять столбцы или увеличивать размер столбцов, делая каждую строку длиннее).

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

Если DBS создалась, пользователям предоставляются права создавать в ней объекты. Можно, например, предоставить Илье право создать таблицу табл1 с помощью команды

GRANT RESOURCE ON табл1 TO Илья;

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

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

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

Синтаксис команд соответственно COMMIT WORK; или ROLLBACK WORK;

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

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

Имеется возможность установки AUTOCOMMIT, которую система выполнит автоматически при регистрации.

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

SQL имеет средства управления параллелизмом для точного указания места получения результата: ни одна команда не должна быть выдана, пока предыдущая не будет завершена (включая команды COMMIT или ROLLBACK).

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

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

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

Специальные блокировки (или X-блокировки) не позволяют никому вообще, кроме владельца этой блокировки, обращаться к данным. Специальные блокировки используются для команд, которые изменяют содержание или структуру таблицы. Они действуют до конца транзакции.

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

Обновление данных. К командам обновления относят INSERT, DELETE, рассмотренные при заполнении БД данными, и UPDATE, изменяющую некоторые или все значения в существующей строке. В команде названо имя используемой таблицы и предложение SET, указывающее на изменение, которое нужно сделать для определенного столбца. Например, чтобы изменить оценки всех заказчиков на 200, можно ввести

UPDATE, наподобие DELETE, может работать с предикатами. Например, можно выполнить изменение, одинаковое для всех заказчиков продавца Строков (имеющего пном=1001):

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

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

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

Запрос формируется командой SELECT (выражение (5.4)).

В самой простой форме команда SELECT просто извлекает информацию из таблицы. Например, вывод всей таблицы Продавцов (табл. 5.3)

Таблица 5.3.

ПРОДАВЦЫ

ПНОМ	ПИМЯ	город	КОММ
1001 1002 1007 1004 1003	Строков Кирюшин Аврорин Удалов Козлов	Москва Пермь Москва Курск Орел	.12 .11 .13 .15 .10

Названия полей:

ПНОМ - уникальный нмер, назначенный каждому продавцу (номер служащего);
ПИМЯ - имя продавца;
город - расположение продавца (город);
КОММ - комиссионные продавцов в десятичной форме

SELECT пном, пимя, город, комм

FROM Продавцы; (5.6)

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

Имеется большое число вариантов команды SELECT . Здесь, в силу ограниченного объема данной работы, рассмотрим основные.

В выражении (5.6) можно задать не все поля или переупорядочить поля.

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

В предложении WHERE используются не только сравнения вида < , >, ≠, =, ≤, ≥, но и области (множества), определяемые выражениямиДIN, BETWEEN, LIKE, IS (NOT) NULL.

К агрегатным функциям относят COUNT (количество строк в таблице), SUM (арифметическую сумму всех выбранных значений данного поля), AVG (всех выбранных значений данного поля), MAX или MIN (наибольшее или наименьшее из всех выбранных значений данного поля).

Вывод агрегатных функций (вычисляемых полей) имеет такой вид:

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

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

Получим

пном

1001 767.19

1002 1713.23

1003 75.75

1014 1309.95

1007 1098.16.

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

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

Аргументы в предложении HAVING следуют тем же самым правилам, что и в предложении SELECT, состоящей из команд, использующих GROUP BY. Они должны иметь одно значение на группу вывода. В строгой интерпретации ANSI SQL нельзя использовать агрегат агрегата.

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

то в поле комм появятся значения в процентах.

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

Вместо имен столбца можно использовать их порядковые номера для указания поля, используемого в упорядочении вывода. Эти номера могут ссылаться не на порядок столбцов в таблице, а на их порядок в выводе: поле, упомянутое в предложении SELECT первым, для ORDER BY - это поле 1, независимо от того, каким по порядку оно стоит в таблице. Например, команда упорядочения по убыванию значения комиссионных такова:

пном	комм
Строков Кирюшин Удалов	0.17 0.13 0.15

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

Создание объединения чаще всего осуществляется командой вида

Здесь после SELECT указываются через точку имя таблицы и файла:

зимя	пимя	город
Иванов Иванов Ковров Конкин Иванов Крабов	Строков Строков Кирюшин Кирюшин Аврорин Аврорин	Москва Москва Пермь Пермь Москва Москва

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

пимя	зимя
Строков	Красин
Аврорин	Красин
Козлов	Иванов
Козлов	Крабов
Козлов	Красин

Аналогично выглядит и команда соединения трех таблиц и более.

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

Для этого используются синонимы таблиц (алиасы).

Например, найти все пары заказчиков, имеющих одинаковый рейтинг:

Петров	Петров	200
Петров	Ковров	200
Ковров	Петров	200
Ковров	Ковров	200
Квакин	Квакин	300
Квакин	Конкин	300
Крабов	Иванов	100
Крабов	Крабов	100
Крабов	Красин	100
Конкин	Квакин	300
Конкин	Конкин	300
Красин	Иванов	100
Красин	Крабов	100
Красин	Красин	100

В вышеупомянутой команде SQL ведет себя так, как если бы он соединял две таблицы, называемые «первая» и «вторая». Обе они - фактически таблицы Заказчика, но псевдонимы разрешают им быть обработанными независимо. Псевдонимы «первый» и «второй» были установлены в предложении FROM запроса, сразу после имени копии таблицы. Обратите внимание, что псевдонимы могут использоваться в предложении SELECT, даже если они не определены в предложении FROM.

Следующая команда будет определять любые ошибки в заполнении:

Если заполнение таблицы проведено верно, то вывод будет пустым.

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

Пусть известно имя продавца (Аврорин ), но значение его поля пном неизвестно и надо извлечь все заказы из таблицы Заказов. Тогда

Чтобы оценить внешний (основной) запрос, SQL сначала должен оценить внутренний запрос (подзапрос) предложения WHERE. Ответ: пном = 1004. Однако SQL не просто выдает это значение, а помещает его в предикат основного запроса вместо самого подзапроса, так чтобы предикат прочитал WHERE пном = 1004.

Для работы с несколькими таблицами может использоваться и операция объединения (UNION). Например, для получения всех продавцов и заказчиков, размещенных в Москве, можно использовать команду:

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

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

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

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

Отметим некоторые ограничения SQL.

Логические конструкции типа if ... then («если ... то»), for ... do («для ... выполнить») и while ... repeat («пока ... повторять»), используемые для структур большинства компьютерных программ, здесь отсутствуют.

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

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

Для этого команды SQL помещаются в исходный текст главной программы, которой предшествует фраза EXEC SQL (EXECute SQL).

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

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

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

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

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

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

Главные переменные должны:

• быть объявленными в SQL DECLARE SESSION (РАЗДЕЛ ОБЪЯВЛЕНИЙ);

• иметь совместимый тип данных с их функциями в команде SQL;

• быть назначенными значению во время их использования в команде SQL, если команда SQL самостоятельно не может сделать назначение;

• предшествовать двоеточию (:), когда они упоминаются в команде SQL.

Пусть в программе имеются четыре переменные с именами id_num, salesperson, loc и comm. Они содержат значения, которые нужно вставить в таблицу Продавцов. Можно вложить следующую команду SQL в программу:

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

Команду можно включить в цикл:

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

Все переменные, на которые имеется ссылка в предложениях SQL, должны сначала быть объявлены в SQL DECLARE SECTION (РАЗДЕЛ ОБЪЯВЛЕНИЙ), использующем обычный синтаксис главного языка. Раздел объявлений должен начинаться и кончаться вложенными командами SQL - BEGIN DECLARE SECTION (Начало Раздела Объявлений) и ENDДDECLARE SECTION (Конец Раздела Объявлений), которым предшествует, как обычно, EXEC SQL (Выполнить).

Чтобы объявить переменные предыдущего примера, можно ввести:

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

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

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

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

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

1) через меню и экранные формы;

2) с помощью языка Query By Example - QBE (запрос по примеру).

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

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

Приведем два примера запросов, базирующихся на примере 4.1.

Пример 5.2. Получить фамилии студентов 4-го курса кафедры ИиУС, имеющих балл не менее 4 (рис. 5.3).

Пример 5.3. Получить фамилии студентов, руководителем которых является профессор кафедры ИиУС (рис. 5.4).

Могут быть использованы (в первом столбце запроса) агрегатные функции (SUM, AVG, MAX, MIN, CNT-счетчик). Возможно создавать виды (View).

QBE поддерживает справочник таблиц , процедуры обновления.

В последнем случае в первом столбце запроса размещают операторы U. (или UPDATE), D. (DELETE), I. (INSERT) для модификации, уничтожения и вставки данных соответственно.