Реализация базы данных в среде СУБД

В данном курсе предполагается использование СУБД MS SQL Server 2000 как целевой СУБД для реализации проектируемой базы данных

Для реализации схемы базы данных используются операторы языка описания данных соответствующей СУБД. В современных реляционных СУБД наиболее распространенным ЯОД являются подмножества языка SQL (Structured Query Language). Для описания ограничений целостности используются соответствующие средства ЯОД и языка разработки приложений (SAL – SQL Application Language), поддерживаемые конкретной СУБД.

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

Язык T_SQL является нечувствительным к регистру, т.е. записи CREATE и create означают одно и то же предложение языка. В дальнейшем для записи названий предложений языка SQL будет использоваться верхний регистр, а для записи остальных элементов – любой, для улучшения читабельности.

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

Процесс создания базы данных начинается с выполнения предложения:

CREATE DATABASE Имя_базы_данных

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

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

DROP DATABASE Имя_базы_данных

Аргумент указывает на существующую базу данных.

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

USE Имя_базы_данных

База данных всегда зарегистрирована на сервере. SQL Server имеет системную базу данных Master, в которой хранится информация обо всех зарегистрированных на сервере базах. Если требуется перенести базу данных на другой сервер, необходимо выполнить следующие операции:

- отсоединить файлы базы данных от сервера при помощи хранимой процедуры sp_detach_db,

- перекопировать файлы базы данных на другой сервер (как правило, требуется копирование двух файлов, с расширениями.mdf и.ldf, расположенных в каталоге \Program Files\Microsoft SQL Server\MSSQL\Data),

- присоединить файлы базы данных на другом сервере при помощи хранимой процедуры sp_attach_db.

Следующим шагом при создании базы данных является создание доменов, определенных на этапе построения структуры базы данных. В рамках SQL Server понятие домена заменено понятием пользовательского типа данных. Как известно, при определении домена требуется задание четырех составляющих: имени домена, типа данных домена, признака обязательности домена, ограничений на значения домена. Для добавления нового типа данных в текущую базу данных используются системная хранимая процедура SP_ADDTYPE, которая позволяет задать имя домена, тип данных и признак обязательности для домена. Для определения ограничений для значений домена необходимо создать специальный объект базы данных – правило, которое задает алгоритм проверки значения на удовлетворение тем или иным требованиям. Правило создается посредством выполнения предложения CREATE RULE языка T-SQL. Созданное правило необходимо связать с пользовательским типом данных, для связывания используется системная хранимая процедура SP_BINDRULE.

sp_addtype type_name, system_data_type, null_type

Здесь используются следующие аргументы:

type_name – имя создаваемого типа данных,

system_data_type – системный тип данных SQL Server, на основе которого создается пользовательский тип данных,

null_type – признак обязательности для типа данных (NULL или Not NULL).

Основными базовыми типами данных SQL Server являются:

· int – целочисленный тип данных

· float – вещественный тип данных

· decimal(p,s) – десятичный тип данных с фиксированной запятой, где p – максимальное возможное количество десятичных знаков, а s - максимальное возможное количество десятичных знаков после запятой

· varchar(n) – символьный тип данных, где n-максимальная длина строки

· datetime – тип данных для хранении даты и времени

· money – денежный тип данных

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

CREATE RULE rule_name AS condition_expression

Здесь rule_name – имя создаваемого правила,

condition_expression – логическое выражение, определяющие условие, накладываемое на значение. В качестве условия можно использовать любые логические команды, арифметические операторы, встроенные функции и условия (например, IN, LIKE, BEETWEN). В выражениях допустима одна локальная переменная, начинающаяся с символа @, с произвольным именем. Переменная может использоваться в любых логических операциях. При выполнении правила переменная будет содержать значение, которое пользователь пытается ввести в столбец с помощью команды INSERT или UPDATE.

Для связывания правила с пользовательским типом данных используется хранимая процедура:

sp_bindrule rule_name, type_name

Назначение параметров процедуры sp_bindrule соответствует назначению параметров, перечисленных выше. Если необходимо удалить пользовательский тип данных, требуется вызвать хранимые процедуры sp_unbindrule и sp_droptype.

Рассмотрим примеры:

Добавление пользовательского типа данных ssn на основе базового типа данных 'VARCHAR(11)', с обязательным признаком задания значения атрибутов данного типа данных:

EXEC sp_addtype ssn, 'VARCHAR(11)', 'NOT NULL'

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

EXEC sp_addtype birthday, datetime, 'NULL' Создание правила с именем rahge_rule для проверки на вхождение в диапазон значений между $1000 и $20000CREATE RULE range_ruleAS @range >= $1000 AND @range <= $20000или:CREATE RULE range_ruleAS @range BETWEEN $1000 AND $20000 Создание правила с именем list_rule, которое определяет список возможных значенийCREATE RULE list_ruleAS @list IN ('1389', '0736', '0877') Создание правила с именем pattern_rule, определяющее строковый шаблон для возможных значений. Строковые значения должны быть построены по следующему закону: ss - … d, где ss – любые два символа, - - сам символ, … - любая цепочка символов, d – любая цифра. CREATE RULE pattern_rule AS@value LIKE '_ _-%[0-9]' В рамках T-SQL в шаблонах возможно использование специальных символов:

% - любая последовательность из нуля или более символов,

_ - любой одиночный символ,

[…] – один из символов, указанных внутри скобок (в данном примере внутри скобок задается диапазон цифр.

Привязка правила rule_ssn к пользовательскому типу данных ssn:EXEC sp_bindrule 'rule_ssn', 'ssn'

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

CREATE TABLE имя_таблицы

(имя_колонки тип_данных [дополнительные_ограничения]

[,...]

[, дополнительные_ограничения]

)

Здесь имя_таблицы – имя создаваемой таблицы,

имя_колонки – имя колонки (атрибута) таблицы,

тип_данных – тип данных,

дополнительные_ограничения – дополнительные ограничения, накладываемые на атрибуты таблицы.

В предложении CREATE TABLE задаются описания атрибутов таблицы, их типы и дополнительные ограничения, накладываемые на атрибуты таблицы и на всю таблицу в целом. Для каждого отношения во внутренней модели данных создается таблица в базе данных. MS SQL Server позволяет использовать в качестве имени объекта базы данных зарезервированные имена или специальные символы; в этом случае имя объекта должно быть заключено в квадратные скобки. При создании таблиц, все атрибуты отношений становятся атрибутами соответствующих таблиц, с сохранением имен. Каждый атрибут может быть определен как на базовом типе данных MS SQL Server, так и на пользовательском типе данных, определенном ранее.

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

Рассмотрим примеры создания простых таблиц.

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

CREATE TABLE Tabl1

(

A int NOT NULL PRIMARY KEY,

B varchar (50)

)

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

CREATE TABLE Tabl2

(

A int NOT NULL,

B int NOT NULL,

PRIMARY KEY (A,B)

)

В случае если атрибут определен на стандартном типе данных MS SQL Server,то для него возможно определение признака обязательности (NOT NULL или NULL). В случае если атрибут определен на пользовательском типе данных, то признак обязательности наследуется от пользовательского типа данных.

CREATE TABLE Tabl2

(

A int NOT NULL PRIMARY KEY,

B int NOT NULL

)

Для связи таблиц в реляционной модели данных используется ограничение FOREIGN KEY. Внешний ключ дочерний таблицы связывается с первичным ключом родительской таблицы. При этом оба ключа должны быть определенны на одном домене или иметь одинаковый тип данных. Атрибут, который является внешним ключом в обязательной связи, должен иметь признак обязательности NOT NULL; в случае необязательной связи возможно использование признака обязательности NULL для данного атрибута. Кроме того, для атрибута внешнего ключа необходимо задание правила поддержания ссылочной целостности, которое выполняется при изменении значения первичного ключа родительской сущности в связи. По умолчанию, в MS SQL SERVER используется правило NO ACTION (эквивалентно правилу RESTRICT). В случае если требуется выполнение операции каскадного удаления или обновления, при описании внешнего ключа требуется включить инструкцию ON DELETE CASCADE или ON UPDATE CASCADE. Рассмотрим примеры задания внешних ключей таблиц.

CREATE TABLE Tabl2

(

A int NOT NULL,

B int NOT NULL REFERENCES Tabl1(A),

PRIMARY KEY (A,B)

)

В данном примере атрибуты А и В являются составным первичным ключом, а атрибут В является внешним ключом для связи с таблицей Tabl1.

CREATE TABLE Tabl3

(

A int NOT NULL PRIMARY KEY,

B int,

C int,

FOREIGN KEY (B,C) REFERENCES Tabl2(A,B)

ON UPDATE CASCADE ON DELETE CASCADE

)

Атрибуты В и С образуют составной внешний ключ, указывающий на составной первичный ключ таблицы Tabl2. Для данного первичного ключа заданно правило ссылочной целостности. По умолчанию в T-SQL заданно правило NO ACTION (запрет изменения и удаления строк таблицы, на которые указывает внешние ключи дочерних таблиц). Для задания правила каскадного удаления требуется указать ключевую фразу ON DELETE, UPDATE CASCADE, пример использования которой показан выше.

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

CREATE TABLE Tabl3

(

A int NOT NULL PRIMARY KEY,

B int UNIQUE,

C int,

FOREIGN KEY (B,C) REFERENCES Tabl2(A,B)

ON UPDATE CASCADE ON DELETE CASCADE

)

В каждой таблице возможно задание проверочных ограничений на атрибуты таблицы. Задание проверочных ограничений выполняется с использованием ограничения CHECK. Если атрибуты таблицы определены на доменах, то они наследуют все ограничения на значения домена, определенные на этапе построения доменов с использованием правил. Ограничение CHECK допускает задание ограничений на значения атрибутов, в случае если атрибуты определенны на стандартных типах данных MS SQL Server, или в случае использование пользовательских типов данных требуется задание дополнительных ограничений. Кроме того, возможно определение ограничения CHECK на уровне всей таблицы для совместной проверки значений нескольких атрибутов.

Рассмотрим пример:

CREATE TABLE Tabl4

(

A int PRIMARY KEY,

C varchar(20) CHECK(C like ‘a%b’),

D datetime CHECK(D > ‘03/04/2003’),

F int,

G int,

CHECK(F+G-A) BETWEEN (25 to 50)

)

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

проверка атрибута С – значением атрибута должна быть строка, начинающаяся буквой ‘а’ и заканчивающаяся буквой ‘b’,

проверка атрибута D – значением D должна быть дата позднее 4 апреля 2003 года.

На уровне таблицы задана проверка условия, что сумма значений атрибутов F и G за вычетом значения атрибута А должна находиться в диапазоне от 25 до 50.

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

IDENTITY [(начальное_значение [, шаг_изменения ])]

Здесь начальное_значение – задает первое значение соответствующей колонки таблицы,

шаг_изменения – определяет правила вычисления следующих значений:

новое_значение = текущее_значение + шаг_изменения

Если эти параметры не указаны, то начальное значение и приращение будут равны 1.

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

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

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

CREATE TABLE Tabl1

(

A int PRIMARY KEY IDENTITY,

B varchar (50)

)

Рассмотрим более сложные примеры создания таблиц:

CREATE TABLE jobs

(

job_id int IDENTITY(1,1) PRIMARY KEY,

job_desc varchar(50) NOT NULL,

min_lvl int NOT NULL CHECK (min_lvl >= 10),

max_lvl int NOT NULL CHECK (max_lvl <= 250)

)

CREATE TABLE employee

(

emp_id empid

CONSTRAINT PK_emp_id PRIMARY KEY

CONSTRAINT CK_emp_id CHECK (emp_id LIKE

'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or

emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'),

fname varchar(20) NOT NULL,

minit varchar(1) NULL,

lname varchar(30) NOT NULL,

job_id int NOT NULL REFERENCES jobs(job_id),

job_lvl tinyint,

pub_id char(4) NOT NULL REFERENCES publishers(pub_id),

hire_date datetime NOT NULL

)

CREATE TABLE publishers

(

pub_id char(4) NOT NULL

CONSTRAINT UPKCL_pubind PRIMARY KEY

CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756')

OR pub_id LIKE '99[0-9][0-9]'),

pub_name varchar(40) NULL,

city varchar(20) NULL,

state varchar(2) NULL,

country varchar(30) NULL

)

Для определения таких ограничений, накладываемых на данные, которые невозможно описать оператором CREATE TABLE, необходимо определить триггера. Триггеры создаются с помощью предложения CREATE TRIGGER. Триггер – это набор действий, оформленных в виде процедуры, которые вызываются автоматически при выполнении в базе данных действий над данными, связанных с этим триггером. Триггер всегда привязан к конкретной таблице. Существуют три разновидности триггеров:

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

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

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

Кроме того, в СУБД MS SQL Server возможно создание AFTER и INSTEAD OF триггеров, которые запускаются после или вместо операции над данными.

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

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

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

Рассмотрим примеры выполнения предложения CREATE TRIGGER.

CREATE TRIGGER reminderON titlesFOR INSERT, UPDATE, DELETE AS EXEC master..xp_sendmail 'MaryM', 'Don''t forget to print a report for the distributors.'

Данное предложение создает триггер с именем reminder на таблице titles для всех возможных операций над данными в таблице. В задачу триггера входит отправка E-Mail c информационным сообщением, что выполняется посредством системной хранимой процедуры xp_sendmail

CREATE TRIGGER employee_insupdON employeeFOR INSERT, UPDATEASDECLARE @min_lvl tinyint, @max_lvl tinyint, @emp_lvl tinyint, @job_id smallintSELECT @min_lvl = min_lvl, @max_lvl = max_lvl, @emp_lvl = i.job_lvl, @job_id = i.job_idFROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id JOIN jobs j ON j.job_id = i.job_idIF (@job_id = 1) and (@emp_lvl <> 10) BEGIN RAISERROR ('Job id 1 expects the default level of 10.', 16, 1) ROLLBACK TRANSACTIONENDELSEIF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)BEGIN RAISERROR ('The level for job_id:%d should be between %d and %d.', 16, 1, @job_id, @min_lvl, @max_lvl) ROLLBACK TRANSACTIONEND

Триггер создается в базе данных PUBS, которая устанавливается вместе с MS SQL Server. В данном примере создается триггер на таблицу EMPLOYEE, для операций INSERT и UPDATE. В триггере производится проверка введенных данных на соответствии определенным требованиям, для чего производится выборка данных из трех таблиц и результат выборки помещается во временные переменные. В случае если данные не соответствуют требованиям, выполняется предложение ROLLBACK, которое отменяет все раннее проведенные изменения.

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

DROP TABLE – удаление таблицы

ALTER TABLE – изменение таблицы

DROP TRIGGER – удаление триггера

ALTER TRIGGER – изменение триггера

DROP RULE – удаление правила

Кроме того, могут быть полезными системные хранимые процедуры:

sp_unbindrule – удаление связи между пользовательским типом данных и созданным правилом

sp_droptype – удаление пользовательского типа данных

Дополнительная информация:

1. Мамаев Е., Шкарина Л. Microsoft Sql Server 2000 для профессионалов. СПб: Питер 2001

Стр. 418-523, 627 -641

2. Ильиных Т.Е., Шустова Л.И. Проектирование реляционных баз данных в нотации IDF1X. М.:МИФИ, 2000 Стр.53 – 60

Дата добавления: 2015-05-09