Создание и использование БД

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

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

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

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

Рассмотрим возможности языков SQL и QBE. Прикладное их использование описано в гл. 15.

Язык SQL

Для SQL имеется много вариантов и диалектов. Здесь изложим основные положения базового варианта: более подробное описание языка приведено в [2, 5, 6, 26, 33].

Иллюстрацию языка SQL проведем на примере базы данных «Снабжение», представленной в табл. 5.3−5.5. Для нее схема Accessсвязей показана на рис. 5.2.

Таблица 5.3

" Продавцы"

Таблица 5.4

" Заказчики"

Таблица 5.5

" Заказы"

	18.69.	Ю.03.1990.
	767.19.	10.03.1990.	200!
	1900.10.	10.03.1990.
		10.03.1990.
	1098.16.	10.03.1990.
	1713.23.	10.04.1990.
	75.75.	10.04.1990.
	4783.00.	10.05.1990.
ЗОЮ.	1309.95.	10.06.1990.
ЗОН.	9891.88.	10.06.1990.

Название полей таблиц

Таблица «Продавцы» :

пном — уникальный номер продавца, первичный ключ;

пимя — имя продавца;

город — город расположения продавца;

комм — комиссионные продавца.

Таблица «Заказчики» :

зном — уникальный номер заказчика, первичный ключ;

зимя — имя заказчика;

рейтинг — число, показывающее уровень предпочтения данного заказчика перед другими;

пном — номер продавца, внешний ключ.

Таблица «Заказы» :

прном — уникальный номер заказа, первичный ключ;

сумпр — сумма (иена) заказа;

датпр — дата получения заказа;

зном — номер заказчика, делающего заказ, внешний ключ;

пном — номер продавца, продающего заказ, внешний ключ.

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

Статический язык программирования SQL

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

(5.1).

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

Тип столбца (и тип таблиц) может быть: UNIQUE, PRIMARY KEY, CHECK, DEFAULT =, REFERENCE [(.,…)|.

(5.2).

(5.3).

(5.4).

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

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

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

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

¦ ИНТЕРАКТИВНЫЙ ЯЗЫК SQL.

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

• • DDL (Язык Описания Данных) — язык описания схемы и в ANSI он состоит из команд, создающих объекты (таблицы, индексы, виды) в базе данных;
• • DML (Язык Манипулирования Данными) — набор команд, определяющих, какие значения представлены в таблицах в любой момент времени;
• • DCD (Язык Управления Данными) состоит из средств, которые определяют разрешение пользователю выполнять определенные действия.

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

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

Создание БД.

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

(5.5).

Команда CREATE TABLE в основном определяет имя таблицы, набор имен столбцов, указанных в определенном порядке, типы данных и размеры столбцов.

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

SQL позволяет создавать временные таблицы, «время жизни» которых — сеанс работы БД (время от открытия до закрытия базы данных).

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

CREATE GLOBAL TEMPORARY TABLE Продавцы.

(пном integer,.

пимя char (10),.

город char (10),.

комм decimal);

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

CREATE LOCAL TEMPORARY TABLE Продавцы.

(пном integer,.

пимя char (10),.

город char (10),.

комм decimal);

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

ALTER TABLE ADD.

;

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

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

DROP TABLE ;

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

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

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

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

CREATE VIEW Москва1.

AS SELECT *.

FROM Продавцы.

WHERE город = 'Москва';

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

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

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

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

DROP VIEW .

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

INSERT INTO Продавцы.

VALUES (', 'Строков', 'Москва', .12);

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

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

INSERT INTO Заказчики (город, имя, номер).

VALUES ('Москва', 'Иванов', 2001);

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

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

DELETE FROM Продавцы;

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

DELETE FROM Продавцы.

WHERE пном = 1003;

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

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

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

CREATE TABLE.

• (Сограничение столбца>,
• (,

Сограничение таблицы> (.

[, ]. .);

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

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

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

• 3. SQL поддерживает первичные ключи непосредственно с ограничением PRIMARY KEY (первичный ключ). Первичные ключи не могут иметь значений NULL. Это означает, что, подобно полям в ограничении UNIQUE, любое поле, используемое в ограничении PRIMARY KEY, должно уже быть объявлено NOT NULL.
• 4. Ограничения на значения полей. Для этого используется ограничение CHECK: чтобы предотвратить ошибку неправильного введения значения «комм», наложим ограничение столбца — CHECK («комм» меньше, чем I).

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

CREATE TABLE Продавцы.

(пном integer NOT NULL PRIMARY KEY,.

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

город char (10),.

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

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

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

CREATE TABLE Заказы.

(прном integer NOT NULL UNIQUE,.

сумпр decimal,.

датпр date NOT NULL,.

зном integer NOT NULL,.

пном integer NOT NULL);

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

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

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

CREATE TABLE Продавцы.

(пном integer NOT NULL UNIQUE,.

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

город char (10) DEFAULT = 'Орел',.

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

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

FOREIGN KEY REFERENCES.

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

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

CREATE TABLE Заказчики.

(зном integer NOT NULL PRIMARY KEY зимя char (10), город char (10),.

пном integer,.

FOREIGN KEY (пном) REFERENCES Продавцы (пном);

Ограничение могло быть внесено и отдельно:

CONSTRAINT полезном FOREIGN KEY (пном).

REFERENCES Продавцы (пном);

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

DROP CONSTRAINT поле_пном;

особенно удобно, если таблица заполнена.

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

CREATE TABLE Продавцы.

(пном integer NOT NULL PRIMARY KEY,.

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

город char (10),.

комм decimal);

CREATE TABLE Заказчики.

(зном integer NOT NULL PRIMARY KEY,.

зимя char (10) NOT NULL,.

город char{ 10),.

пном integer,.

рейтинг integer.

FOREIGN ???(???) REFERENCES Продавцы,.

UNIQUE (зном, пном);

CREATE TABLE Заказы.

(прном integer NOT NULL PRIMARY KEY,.

сумпр decimal,.

датпр date NOT NULL,.

зном integer NOT NULL.

пном integer NOT NULL.

FOREIGN KEY (зном, пном) REFERENCES.

Заказчики (зном, пном);

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

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

• 1) ограничить или запретить изменение (способом ANSI), обозначив, что изменения в родительском ключе ограничены (RESTRICTED);
• 2) можно сделать изменение в родительском ключе и тем самым автоматические изменения во внешнем ключе, т. е. каскадное изменение (CASCADES);
• 3) провести изменение в родительском ключе и установить автоматически внешний ключ в NULL (полагая, что NULLS разрешен во внешнем ключе) — пустое изменение внешнего ключа (NULL).

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

CREATE TABLE Заказчики.

(зном integer NOT NULL PRIMARY KEY,.

зимя char (10) NOT NULL, город char (10),.

рейтинг integer,.

пном integer REFERENCES Продавцы,.

UPDATE OF CASCADES,.

DELETE OF Продавцы RESTRICTED);

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

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

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

CREATE TABLE Заказы прном integer NOT NULL PRIMARY KEY,.

сумпр decimal,.

датпр date NOT NULL,.

3HOM integer NOT NULL REFERENCES Заказчики, пном integer REFERENCES Продавцы,.

UPDATE OF Заказчики CASCADES,.

DELETE OF Заказчики CASCADES,.

UPDATE OF Продавцы CASCADES,.

DELETE OF Продавцы NULLS);

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

С помощью языка SQL могут быть заданы база данных, хранимая процедура, генератор, счетчик, триггер.

База данных формируется так:

CREATE DATABASE «d:…a1 .gdb» .

USER «SYSDBA» .

PASSWORD «masterkey» .

Default character set WIN 1251.

Хранимая процедура:

CREATE PROCEDURE Rashod_Tovara (IN_Tovar varchar (20)).

RETURNS (OUT_Tovar varchar (20)).

AS.

BEGIN.

FOR SELECT Tovar.

FROM Rashod.

WHERE Tovar=: IN_Tovar.

INTO :OUT_Tovar;

DO.

SUSPEND;

END.

Генератор задается двумя процедурами:

CREATE PROCEDURE Get_N_Rash;

SET GENERATOR RASHOD_N_Rash TO 1;

CREATE PROCEDURE Get_N_Rash.

RETURNS (NR integer).

AS.

BEGIN.

NR=Gen_ID (RASHOD_N_Rash, 1);

END;

Триггер каскадного обновления:

CREATE TRIGGER BU_Tovary ACTIVE.

BEFORE UPDATE.

AS.

BEGIN.

IF (OLD.TovarONEW.Tovar) THEN.

UPDATE Rashod.

SET Tovar=NEW.Tovar.

WHERE Tovar=OLD.Tovar;

END.

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

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

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

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

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

GRANT.

ON.

ТО.

[WITH GRANT OPTION],.

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

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

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

CREATE USER Илья.

WITH PRIVILEGES create. db;

лишение этого пользователя привилегий осуществляется командой.

DROP USER Илья;

создание роли (с заданием пароля) проводится командой.

CREATE ROLE create_db WITH PASSWORD = '12';

а лишение роли — командой.

DROP ROLE create_db;

Имеется несколько типов привилегий, соответствующих нескольким типам операций. Привилегии даются и отменяются двумя командами SQL: GRANT (допуск) и REVOKE (отмена).

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

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

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

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

К нестандартным командам относятся INDEX (индекс), дающий право создавать индекс в таблице, и ALTER (изменить) — для выполнения команды ALTER TABLE в таблице. Механизм SQL назначает пользователям эти привилегии с помощью команды GRANT.

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

GRANT SELECT ON Заказчики ТО Петр;

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

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

GRANT SELECT, INSERT ON Заказы TO Илья, Петр;

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

GRANT UPDATE (комм) ON Продавцы ТО Илья;

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

GRANT REFERENCES (зимя, зном).

ON Заказчики ТО Степан;

SQL поддерживает два аргумента для команды GRANT, которые имеют специальное значение: ALL PRIVILEGES (все привилегии) или просто ALL — для команд и PUBLIC (общие) — для пользователей.

GRANT ALL ON Заказчики ТО PUBLIC;

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

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

Пусть Илья передает право Петру на привилегию SELECT в таблице «Заказчики» :

GRANT SELECT ON Заказчики ТО Петр

WITH GRANT OPTION;

Возможно разрешить выполнение процедуры integ check.

GRANT EXECUTE.

ON PROCEDURE integ_check.

TO PUBLIC;

Привилегия, например, на команду INSERT, может быть отменена:

REVOKE INSERT ON Заказы FROM Петр;

Здесь также можно использовать списки:

REVOKE INSERT, DELETE ON Заказчики FROM Петр, Степан;

Возможно задание (или отмена) привилегий с помощью рассмотренных ранее видов. Например,.

CREATE VIEW Москва 1.

AS.

SELECT зимя, пимя FROM Продавцы;

предоставляет Москва1 привилегию SELECT в виде (представлении), а не в самой таблице «Продавцы» :

GRANT SELECT ON Москва1 ТО Виктор;

Привилегии возможно ограничивать и строками:

CREATE VIEW Москва2.

AS SELECT *.

FROM Заказчики.

WHERE город = 'Сочи'.

WITH CHECK OPTION;

GRANT UPDATE ON Москва2 TO Петр;

Предложение WITH CHECK OPTION предохраняет Петр от замены значения поля «город» на любое значение, кроме Сочи.

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

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

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

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

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

GRANT RESOURCE ТО Мирон;

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

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

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

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

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

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

GRANT NOCREATE.

ON DATABASE newa.

TO GROUP clerk;