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

Запретить изменения.

Сделав изменения в родительском ключе, произвести изменения во внешнем ключе автоматически (каскадное изменение).

Сделать изменение в родительском ключе и установить внешний ключ в NULL-значение автоматически.

В пределах этих возможностей выполняются все команды модификации.

Итак, изменения в родительском ключе можно разделить на ограниченные (RESTRICTED), каскадируемые (CASCADES) и пустые (NULL) изменения. Например, при изменении номера отделения, данные о новом значении поля Branch_no должны быть переданы в таблицу BUYER. В этом случае следует указать оператор UPDATE c каскадируемыми изменениями. То есть, при создании таблицы BUYER указать:

UPDATE OF BRANCH CASCADES;

Если необходимо запретить удаление значений в родительском поле при наличии соответствующих значений во внешнем ключе используется ограничение RESTRICTED. Например, при удалении данных об отделении из таблицы BRANCH, оператор не будет выполнен до тех пор, пока не будут удалены сведения о покупателях, обратившихся в это отделение или не изменится значение поля Branch_no в таблице BUYER (то есть пока покупателей не переведут в другое отделение). Для этого, при создании таблицы BUYER надо указать:

DELETE OF BRANCH RESTRICTED.

Упражнение: Через утилиту SQL Server Query Analyzer создайте таблицу BUYER в базе данных DreamHome_db с заданием ограничений.

1. Откройте SQL Server Query Analyzer и подключитесь к серверу с использованием средств аутентификации пользователей.

2. Опишите оператор создания таблицы BUYER, предполагая наличие следующих ограничений целостности:

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

· С таблицей BRANCH таблица BUYER связана обязательной связью, потому что когда потенциальный покупатель обращается в одно из отделений компании данные о нём, заносятся в базу данных. Для моделирования этой связи при создании таблицы BUYER должен быть определён внешний ключ Branch_no и значение его NOT NULL (таким образом, задаётся обязательность связи).

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

CREATE TABLE BUYER

(Buyer_no member_no NOT NULL PRIMARY KEY,

FName shortstring NOT NULL,

LName shortstring NOT NULL,

City shortstring NOT NULL,

Street shortstring NOT NULL,

House nchar(6) NOT NULL,

Flat smallint NULL,

Htel_no phonenumber NULL,

Wtel_no phonenumber NULL,

Prof_Rooms tinyint NOT NULL,

Max_Price money NOT NULL,

Branch_no member_no NOT NULL REFERENCES BRANCH,

UPDATE OF BRANCH CASCADES,

DELETE OF BRANCH RESTRICTED,

CHECK (Htel_no IS NOT NULL OR Wtel_no IS NOT NULL) Булевские операторы и предикаты описаны в лабораторной работе №5

).

рис. 3.9

Упражнение: Воспользуйтесь утилитой SQL Server Query Analyzer для создания именованного первичного ключа в таблице OWNER базы данных DreamHome_db.

Откройте SQL Server Query Analyzer и подключитесь к серверу с использованием средств аутентификации пользователей.

Напишите и выполните оператор, добавляющий ограничение PRIMARY KEY с именем PK_Owner Имя ограничения состоит из краткого названия типа ограничения, символа подчёркивания, имени поля или таблицы и порядкового номера ограничения данного типа, если к одному объекту задаётся несколько ограничений одного типа для поля Owner_no таблицы OWNER.

USE DreamHome_db

ALTER TABLE OWNER

ADD CONSTRAINT PK_Owner PRIMARY KEY NONCLUSTERED(Owner_no)

GO

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

рис. 3.10

Для создания ограничений для таблицы с помощью утилиты SQL Server Enterprise Manager, необходимо:

1. Выбрать таблицу в списке объектов базы данных;

2. Выполнить команду Design Table меню Action, после чего на экране отобразиться диалоговое окно дизайнера таблиц;

3. В дизайнере таблицы выберите кнопку Table and Index Properties, с помощью которой отобразиться диалоговое окно свойств данной таблицы;

4. Нажатие кнопки New приведет к созданию нового ограничения, после чего в поле Constraint Expression надо ввести SQL-команду проверки вводимого значения.

Например: SQL команда (ROOMS >=1 AND ROOMS <=5) или (ROOMS BETWEEN 1 AND 5) позволяет контролировать ввод значения (целое число не менее 1 и не более 5) в поле ROOMS таблицы PROPERTY.

Упражнение: Добавьте ограничение в таблицу BRANCH базы данных DreamHome_db, гарантирующее, что номер телефона соответствует принятому формату номеров. Для этого воспользуйтесь утилитой SQL Server Enterprise Manager.

Раскройте группу серверов, а затем - сервер.

Раскройте Databases и базу данных DreamHome_db.

Выберите Tables в списке объектов базы данных.

Щёлкните таблицу BRANCH правой кнопкой мыши и в контекстном меню выберите команду Design Table.

В дизайнере таблицы нажмите кнопку Table and Index Properties.

рис. 3.11

Нажмите кнопку New вкладки Tables и в поле Constraint Expression введите SQL-команду проверки вводимого значения: (Btel_no LIKE `8(021[2-6][0-9])[0-9][0-9]-[0-9][0-9]-[0-9][0-9]')

Нажмите кнопку Закрыть.

КОНТРОЛЬНЫЕ ВОПРОСЫ

1. Какой оператор служит для создания таблиц в базе данных?

2. С помощью какого оператора можно добавить поля к таблице или изменить их тип данных?

3. Какой оператор позволяет удалить таблицу в базе данных?

4. Охарактеризуйте понятия внешний ключ и первичный ключ.

5. Дайте определение индекса и способы создания индексов в SQL Server 7.0.

6. В чем различие между понятиями индекс и ключ?

7. Для чего применяются ограничения?

8. Какие типы ограничений Вам известны?

ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ

1. Для базы данных DreamHome_db создайте таблицы STAFF и PROPERTY c помощью утилиты SQL Server Enterprise Manager. При создании таблиц определите первичные ключи (поле Staff_no В качестве Номера сотрудника можно использовать № паспорта, соответственно тип данных: nchar(9) таблицы STAFF, поле Property_no таблицы PROPERTY). При создании таблиц для таблицы STAFF задайте необязательность значения внешнего ключа Branch_no , а для таблицы PROPERTY - обязательность или необязательность значений внешних ключей: Branch_no (Branch_no member_no NOT NULL), Staff_no (Staff_no nchar(9) NULL), Owner_no (Owner_no member_no NOT NULL).

2. С помощью утилиты SQL Server Query Analyzer для таблиц STAFF и PROPERTY задайте ограничение FOREIGN KEY. Предусмотрите каскадное изменение значения Staff_no в таблице PROPERTY при изменении значения этого поля в таблице STAFF. Запретите удаление значений поля Branch_no в таблице BRANCH при наличии ссылок на это значение в таблице PROPERTY.

3. Таблицу VIEWING создайте с помощью утилиты SQL Server Query Analyzer. При создании таблицы VIEWING определите два внешних ключа (поля Property_no, Buyer_no) и ограничение PRIMARY KEY (первичным ключом будет набор из этих двух полей).

4. Добавьте ограничения на ввод значений в поле Rooms таблицы PROPERTY и в поле Sex (M(м)ужской, Ж(ж)енский) таблицы STAFF с помощью утилит SQL Server Enterprise Manager и SQL Server Query Analyzer соответственно.

5. Задайте стандартное значение (значение по умолчанию) `T' для поля Ptel таблицы PROPERTY с помощью одной из утилит.

6. Для полей FName, Position таблицы STAFF, создайте индекс c помощью утилиты SQL Server Enterprise Manager.

7. Для поля Date_View таблицы VIEWING создайте индекс с помощью утилиты SQL Server Query Analyzer.

8. С помощью утилиты SQL Server Enterprise Manager просмотрите свойства созданных таблиц.

ЛАБОРАТОРНАЯ РАБОТА №6

Тема: Создание представлений, триггеров, хранимых процедур.

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

ОСНОВНЫЕ СВЕДЕНИЯ

Представление

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

Создание представления базы данных в системе SQL-сервер может осуществляться следующими способами:

Первый способ: С помощью утилиты SQL Server Query Analyzer.

Представление создается командой CREATE VIEW, после которой указывается его имя, а далее следует запрос, формирующий тело представления:

Синтаксис:

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

AS SELECT …

Горизонтальные представления

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

Например, создать представление, позволяющее руководителю отделения компании под номером 3 иметь доступ только к данным сотрудников своего отделения:

CREATE VIEW STAFF3

AS SELECT *

FROM STAFF

WHERE STAFF.Branch_no= 3;

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

Вертикальные представления

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

CREATE VIEW SALARY_OFF

AS SELECT Staff_no, FName, LName, DOB, City, Street, House, Flat, Stel_no, Position, Branch_no

FROM STAFF;

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

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

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

Упражнение: C помощью утилиты SQL Server Query Analyzer создайте представление, содержащее данные об агентах, отвечающих за продажу объектов. Представление должно включать номер отделения (Branch_no), номер работника (Staff_no) и сведения о количестве объектов, за которые он отвечает:

CREATE VIEW STAFF_PROP (Branch_no, Staff_no, Properties)

AS SELECT STAFF. Branch_no, STAFF. Staff_no, COUNT(*)

FROM STAFF INNER JOIN PROPERTY ON STAFF.Staff_no= PROPERTY.Staff_no

GROUP BY STAFF. Branch_no, STAFF. Staff_no;

рис. 3.36

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

если столбец в представлении создается с использованием обобщающей функции, то этот столбец может указываться только в предложениях SELECT и ORDER BY тех запросов, которые обеспечивают доступ к данному представлению. Этот столбец не может использоваться в предложении WHERE, а также не может быть аргументом в обобщающей функции;

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

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

в нем не используется ключевое слово DISTINCT;

каждый элемент в списке предложения SELECT представляет собой имя столбца, а не выражение или обобщающую функцию;

представление должно быть построено на базе одной таблицы;

запрос, определяющий представление не должен содержать предложений GROUP BY и HAVING.

Второй способ: С помощью утилиты SQL Server Enterprise Manager.

Для создания представления надо:

1. Выбрать группу Views в списке объектов базы данных, после чего воспользоваться командой New View меню Action.

После выполнения этих действий загрузиться дизайнер представлений. Диалоговое окно дизайнера представлений состоит из следующих частей:

· Diagram Pane (панель диаграмм) - используется для добавления новых таблиц в представление, описание связей между ними, определения полей, которые будут участвовать в представлении. Для открытия/закрытия данной панели используется кнопка ;

· Grid Pane (панель-список) - на этой панели отображается перечень полей, выбранных в Diagram Pane. Можно так же добавить новые поля, определить наличие различных критериев и т.д. Для открытия/закрытия данной панели используется кнопка ;

· SQL Pane (SQL-панель) - данная панель используется для ввода SQL-команд, с помощью которой создается представление. Для открытия/закрытия данной панели используется кнопка ;

· Result Pane (панель результатов) - работу произведенных настроек удобно проверить, используя данную панель, по нажатию кнопки Run , отображаются результаты настроенного представления. Для открытия/закрытия данной панели используется кнопка ;

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

3. Нажатие кнопки Close приведет к закрытию диалогового окна.

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

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

5. На панели диаграмм данного диалогового окна, слева от имени поля таблиц имеется флажок, при установки которого данное поле будет выведено на экран в результате выполнения представления. При выборе имени этого поля, оно автоматически появляется в списке Grid Pane, и в области оператора SELECT на панели SQL Pane. Проверить правильность создания представления можно используя кнопку Run, в результате чего должны отобразиться данные из созданного представления на панели Result Pane.

6. После сохранения созданного представления его имя появиться в списке объектов Views базы данных. Для просмотра информации из этого представления надо выполнить команду Open View/Return all rows меню Action, предварительно выбрав предварительно его в списке объектов Views

Удаление представления из базы данных осуществляется командой DROP VIEW имя_представления. При удалении представления пользователь должен являться его владельцем.

Упражнение: C помощью утилиты SQL Server Enterprise Manager просмотрите представление STAFF_PROP, содержащее данные о работниках, отвечающих за продажу объектов.

рис. 3.37

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

Хранимые процедуры - это подпрограммы, выполнение которых происходит непосредственно на сервере баз данных. Все хранимые процедуры в базе данных находятся в специально отведенном списке Stored Procedure. Для системных процедур в колонке Type возле имени процедуры находится ключевое слово System. Процедуры, создаваемые пользователем помечаются ключевым словом User.

Для создания новой процедуры с помощью утилиты SQL Server Enterprise Manager необходимо:

1. Выбрать команду New Stored Procedures меню Action. После чего на экране отобразиться диалоговое окно, в котором будет расположена область для ввода текста процедуры.

рис. 3.38

Вместо текста [PROCEDURE NAME] необходимо ввести имя создаваемой процедуры, после чего набрать текст ее команд.

2. Далее необходимо проверить работоспособность созданной процедуры.

Для этого надо запустить утилиту SQL Server Query Analyzer и осуществить подключение к требуемому серверу баз данных. Затем в выпадающем списке DB следует выбрать нужную базу данных, после чего ввести SQL команду:

EXEC имя_процедуры

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

@<имя переменной> <тип данных>

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

EXEC <имя процедуры><имя переменной = значение>

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

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

EXEC <имя процедуры><имя переменной1> <имя переменной2>…

Для создания процедур в MS SQL Server используется язык Transact SQL. Каждая хранимая процедура компилируется при первом выполнении. Описание процедуры совместно с планом ее работы хранится в системных таблицах БД.

Для создания хранимой процедуры используется оператор SQL CREATE PROCEDURE, имеющий следующий

Cинтаксис:

CREATE PROCEDURE имя_процедуры (@<имя перем1> <тип данных>, @<имя перем2> <тип данных>…)

[VARYING [=значение по умолчанию] [,.параметр N] [OUTPUT]

[WITH

{ RECOMPILE

|ENCRYPTION

|RECOMPILE,ENCRYPTION}]

AS тело_процедуры

Создает процедуру с указанным именем. Процедура может быть создана только в текущей базе данных, за исключением временных процедур, которые создаются в tempdb. Для создания временных процедур следует начинать ее имя с '#' или '##'. Длина имени хранимой процедуры вместе с ## не может превышать 20 символов.

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

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

Удаление процедуры осуществляется с помощью команды DROP PROCEDURE

DROP PROCEDURE [owner.]procedure_name [,[owner.]procedure_name...]

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

1. Оператор условия

IF <выражение>

BEGIN

<операторы>

END

[ELSE]

[IF <выражение>]

BEGIN

<операторы>

END

Если используется один оператор, то BEGIN … END не используется.

2. Циклическое выполнение операций

WHILE <логическое выражение>

BEGIN

<операторы>

END

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

3. Выбор одного из нескольких значений

CASE <переменная>

WHEN <условие1> THEN <оператор1>

WHEN <условие2> THEN <оператор2>

WHEN <условие3> THEN <оператор3>

…

ELSE <оператор>

END

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

Пример:

DECLARE @X INT;

SELECT @X=@X+1;

PRINT `Результат',@X;

Упражнение: C помощью утилиты SQL Server Enterprise Manager создайте процедуру для увеличения заработной платы сотрудников на 10 %.

1. Выберите команду New Stored Procedures из меню Action.

2. В поле Text системой будет предложена команда:

CREATE PROCEDURE [PROCEDURE NAME] AS. Вместо [PROCEDURE NAME] введите имя процедуры и наберите ее текст, который будет иметь следующий вид:

CREATE PROCEDURE NEW (@procent decimal)

AS

UPDATE STAFF

SET Salary =Salary * (100+ @procent)/100

3. Для проверки работоспособности процедуры запустите утилиту SQL Server Query Analyzer, выберите базу данных в выпадающем списке DB и введите команду:

EXEC NEW @procent = 10

4. Для проверки работоспособности процедуры выполните команду:

SELECT * FROM STAFF

До выполнения процедуры:

рис. 3.39

Результат выполнения процедуры:

рис. 3.40

Упражнение: Создайте процедуру для вывода окладов сотрудников заданного как параметр отделения.

CREATE PROCEDURE SALARY_OUT(@Branch_no member_no)

AS

SELECT Staff_no, FName, LName, Salary

FROM STAFF

WHERE Branch_no=@Branch_no

рис. 3.41

Результат выполнения процедуры:

рис. 3.42

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

CREATE PROC NEW_SALARY

(@Staff_no int,

@Procent decimal)

AS

IF EXISTS (SELECT property_no

FROM PROPERTY

WHERE Staff_No= @Staff_No )

UPDATE STAFF SET Salary=Salary*(100+ @Procent)/100

WHERE Staff_No= @STAFF_NO

Для запуска процедуры:

EXEC NEW_SALARY @Staff_No = BMO5502601, @PROCENT=10

Триггеры

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

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

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

Триггеры не возвращают наборы результатов. Это связано с тем, что операторы INSERT, UPDATE и DELETE не должны возвращать наборы результатов. Как и хранимые процедуры, триггеры содержат операторы Transact-SQL.

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

триггеры изменения (запускаются при попытке изменения данных с помощью команды UPDATE);

триггеры вставки (запускаются при попытке вставки данных с помощью команды INSERT);

триггеры удаления (запускаются при попытке удаления данных с помощью команды DELETE)..

При работе с триггерами доступны две специальные таблицы: таблицf вставок (INSERTED) и таблица удалений (DELETED) со структурой идентичной структуре таблицы, с которой связан триггер. Таблицы INSERTED и DELETED заполняются строками модифицируемой таблицы. При выполнении операции DELETE строки, удаленные из модифицируемой таблицы помещаются в таблицу DELETED. При выполнении операции INSERTED, строки, добавленные в модифицируемую таблицу, помещаются в таблицу INSERTED. При выполнении операции UPDATE для каждой измененной строки ее исходное значение помещается в таблицу DELETED, а новое значение - в таблицу INSERTED.Данные таблиц INSERTED и DELETED можно использовать в триггере.

Для создания триггеров используется оператор CREATE TRIGGER. В коде оператора указывается таблица, в которой следует создать триггер, а также операторы, включаемые в триггер. Для создания триггера следует открыть окно Trigger Properties таблицы, для которой создается триггер (Action/All Tasks/Manage Triggers) и ввести текст триггера.

Упражнение: Создайте триггер для поддержания целостности данных - проверки наличия связанной записи в главной таблице (BRANCH) при вводе данных в подчиненную таблицу (PROPERTY).

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

Для создания триггера с именем INSCHECK c помощью утилиты SQL Server Enterprise Manager выберите таблицу PROPERTY в списке объектов базы данных, после чего выполните команду All Tasks/Manage Triggers… меню Action. После этого будет открыто диалоговое окно свойств триггера (Trigger Properties), в которое введите следующий текст:

CREATE TRIGGER INSCHECK ON PROPERTY

FOR INSERT

AS

DECLARE @X Member_no

SELECT @X= Branch_no FROM INSERTED

IF NOT EXISTS(SELECT * FROM

BRANCH WHERE Branch_no=@X)

BEGIN

ROLLBACK TRAN

RAISERROR(`ОШИБКА ЦЕЛОСТНОСТИ! ОТДЕЛЕНИЕ ОТСУТСТВУЕТ В ТАБЛИЦЕ BRANCH',16,10)

END

Триггер активизируется при вставке (ключевое слово INSERT) новой записи. После определения переменной @X (DECLARE @X) ей присваивается значение поля Branch_no добавляемой записи. В процессе использования триггера создаются соответствующие временные таблицы UPDATED и DELETED, хранящие в себе соответственно добавляемые, изменяемые и удаляемые значения начальной таблицы PROPERTY. С помощью оператора SELECT переменной @X присваивается значение поля Branch_no из таблицы PROPERTY, то есть значение поля Branch_no вновь добавляемой записи.

Следующий шаг работы триггера - проверка наличия в поле Branch_no таблицы BRANCH значения переменной @X, то есть проверка допустимости вводимого значения. Если значение не найдено, то выполняется блок операторов, заключенных в области BEGIN …END. С помощью команды ROLLBACK TRAN, используемой при работе с транзакциями, отменяется последняя операция. Оператор RAISERROR осуществляет выдачу системного сообщения об ошибке. Значения 16 и 10 определяют уровень критичности операции.

Упражнение: Создайте триггер для удаления всех подчиненных записей в таблице PROPERTY при удалении записи из главной таблицы BRANCH. Если из таблицы BRANCH удаляется какое либо отделение (например, при его закрытии), то должны удаляться все записи таблицы PROPERTY, у которых значение поля Branch_no соответствует значению поля Branch_no удаляемой из таблицы BRANCH записи.

В таблице BRANCH создадим триггер DELCHECK следующего содержания:

CREATE TRIGGER DELCHECK ON BRANCH

FOR DELETE

AS

DECLARE @X Member_no

SELECT @X=Branch_no FROM DELETED

IF EXISTS (SELECT *

FROM PROPERTY

WHERE Branch_no = @X)

DELETE FROM PROPERTY

WHERE Branch_no=@X

В первой строке кода создается новый триггер с именем DELCHECK для таблицы BRANCH, активизирующийся при удалении записи. Следующим шагом является определение переменной @X, которая будет содержать значение поля Branch_no удаляемой записи. Затем с помощью оператора SELECT данной переменной присваивается значение поля Branch_no удаляемой записи, в которой буферизируется удаляемая запись. С помощью оператора EXISTS определяется наличие данных в таблице PROPERTY, для которых в поле Branch_no находится значение @X. Если такие записи найдены, то система выполняет их удаление.

Упражнение: Создайте триггер для увеличения зарплаты сотрудника на 1% при каждой продаже:

CREATE TRIGGER UPDATE_SALARY

ON VIEWING

FOR INSERT

AS

DECLARE @X Member_no

DECLARE @P Nchar(9)

DECLARE @Y Nchar(18)

SELECT @X=Property_no From INSERTED

SELECT @Y=Comments From INSERTED

SELECT @P= Staff_no From PROPERTY

WHERE Property_no = @X

IF (@Y= 'согласен')

BEGIN

UPDATE STAFF

SET STAFF.Salary = STAFF.Salary*1.1

WHERE Staff_no=@P

END

КОНТРОЛЬНЫЕ ВОПРОСЫ

1. Для чего используются представления?

2. Происходит ли изменение данных в представлении в случае внесения изменений базовые таблицы, на основе которых они созданы?

3. В чем заключается отличие горизонтальных и вертикальных представлений?

4. Можно ли создать представление на основании нескольких таблиц?

5. Перечислите ограничения на обновление данных в представлениях.

6. Для чего предназначены триггеры?

7. В чем заключается механизм работы триггеров?

8. Какие виды триггеров существуют?

9. Какие операторы управления могут использоваться в хранимых процедурах?

10. Как запустить хранимую процедуру на выполнение?

ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ

1. С помощью утилиты SQL Server Query Analyzer создайте представление, с помощью которого выводиться следующая информация об объектах собственности в Витебске из таблицы PROPERTY: улица, тип дома, этаж, количество комнат, площадь. Сохраните запрос на создание представления. После создания откройте SQL Server Enterprise Manager и проверьте созданное представление. (Если представления в группе View не будет, то выберите эту группу и выполните команду Refresh меню Action. После чего имя представления появится в списке View).

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

3. С помощью утилиты SQL Server Enterprise Manager создайте представление, содержащее следующую информацию о сотрудниках компании: номер, имя и фамилию, адрес и номер телефона.

4. Создайте триггер для удаления из таблицы PROPERTY объектов собственности, принадлежащих владельцу, данные о котором удаляются из таблицы OWNER. Проверьте работу этого триггера Предварительно снимите флажок Enable constraint for INSERT and UPDATE в окне Properties связи между таблицами PROPERTY и OWNER объекта Diagrams.

5. Создайте триггер для проверки наличия номера отделения Branch_no в таблице BRANCH при вводе этого номера в таблицу BUYER (при вставке новой строки) и запрета вставки записи при нарушении ссылочной целостности. Проверьте его работоспособность.

6. Создайте триггер для снижения стоимости квартиры, если в поле comments таблицы VIEWING вводится значение ”требует ремонта”.

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

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

ЛАБОРАТОРНАЯ РАБОТА №7

Тема: Администрирование баз данных. Резервное копирование.

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

Безопасность баз данных и привилегии

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

· в БД действующими лицами являются пользователи. Манипуляции с данными происходят от имени конкретного пользователя;

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

Администратор БД создает пользователей и дает им привилегии, пользователи, которые создают таблицы, сами имеют права на управление этими таблицами.

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

Существуют следующие привилегии:

· SELECT - пользователь может выполнять запросы к таблице;

· INSERT - пользователь может выполнять вставку записей;

· UPDATE - пользователь может выполнять корректировку данных;

· DELETE - пользователь может выполнять удаления в таблице;

· REFERENCES - пользователь может определять внешние ключи;

· INDEX - пользователь может создавать индекс в таблице;

· SYNONYM - пользователь может создавать синонимы для объекта;

· ALTER - пользователь может выполнять команду ALTER TABLE.

В SQL привилегии даются и отменяются двумя операторами - GRANT (допуск) и REVOKE (отмена).

Синтаксис:

GRANT привилегия1, привилегия2…

ON таблица

TO пользователь1, пользователь2…;

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

Для того чтобы пользователи могли передавать свои привилегии другим пользователям, используется оператор WITH GRANT OPTION.

Синтаксис:

GRANT привилегия1, привилегия2…

ON таблица

TO пользователь1, пользователь2…

WITH GRANT OPTION;

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

Для удаления привилегии используется оператор REVOKE

Синтаксис:

REVOKE привилегия

ON таблица

FROM пользователь;

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

Управление привилегиями и правами доступа

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

Для создания учетных записей пользователей в SQL Server Enterprise Manager необходимо:

Выбрать в группе объектов SQL-сервера Logins в списке Security, после чего выполнить команду New Logins меню Action. На экран будет выведено диалоговое окно настройки параметров создаваемой учетной записи пользователя.

рис. 3.43

На закладке General этого окна в поле Name вводиться имя учетной записи, а поле Password - пароль пользователя. Здесь также можно установить базу данных, подключение к которой осуществляется по умолчанию при входе пользователя в систему (список Database), а также используемый по умолчанию для данного пользователя язык (Language).

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

· System Administrators - любые функции администрирования;

· Security Administrators - управление доступом, возможность создания баз данных;

· Server Administrators - настройка конфигураций и выполнение функций закрытия SQL-сервера;

· Setup Administrators - управление связями между серверами;

· Process Administrators - управление процессами, выполняющимися на сервере;

· Disk Administrators - управление файлами сервера;

· Database Creator - управление процессами создания и удаления баз данных.

С помощью закладки Database Access диалогового окна осуществляется настройка доступа к объектам данных. В верхнем списке осуществляется выбор требуемой базы данных (базы, к которой будет разрешен доступ). А в нижнем списке отображается перечень ролей доступа к объектам базы данных. Возможны следующие роли:

· public - нет специальных ролей;

· db_owner - полный доступ;

· db_accessadmin - возможность добавления и удаления пользователей базы данных;

· db_securityadmin - управление всеми процессами доступа пользователей к объектам базы данных;

· db_ddladmin - выполнение основных команд, кроме GRANT, REVOKE;

· db_backupoperator - функции запуска процедуры резервного копирования;

· db_datareader - возможность чтения всех данных из любых таблиц базы данных;

· db_datawriter - возможность изменения всех данных из любых таблиц базы данных.

Дальнейшее изменение настроек учетной записи пользователя можно осуществлять с помощью выбора необходимого пользователя в списке Logins и выполнения команды Properties меню Action. Учетные записи доступа к SQL-серверу отображаются в группе объектов Security / Logins, а права доступа к базам данных в группе Users списка базы данных.

Для добавления нового пользователя в список Users необходимо:

Выбрать группу Users нужной базы данных.

Выполнить команду New Database User меню Action.

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

Системный каталог

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

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

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

Системный каталог состоит из следующих таблиц:

· SYSTEMCATALOG - храниться информация о базовых таблицах и представлении;

· SYSTEMCOLUMNS - данные о полях таблицы;

· SYSTEMTABLES - данные о системных таблицах системного каталога;

· SYSTEMINDEXES - информация об индексах в таблице;

· SYSTEMUSERAUTH - данные о пользователях БД;

· SYSTEMTABAUTH - данные о привилегиях пользователей;

· SYSTEMSYNONS - синонимы для таблиц.

Администратор БД может предоставить пользователю право просматривать системный каталог оператором:

GRANT SELECT ON SYSTEMCATALOG TO пользователь;

Резервное копирование

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

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

Для создания хранилища данных надо:

Выбрать в списке объектов SQL-сервера Backup, расположенный в группе Management.

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

В поле Name необходимо указать имя создаваемого устройства, причем автоматически при вводе этого имени в поле File name будет отображаться имя файла, создаваемого для данного устройства с расширением *.bak. После сохранения параметров устройства его имя отобразится в перечне объектов.

рис. 3.44

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

Для этого надо:

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

рис. 3.45

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

В области Backup диалогового окна настройки процесса определяются параметры записи данных:

Database complete - запись новой информации поверх существующей;

Database differential - обновление информации;

Transaction log - копирование журнала транзакций базы данных;

File and filegroup - определение группы файлов, требующих резервирования.

В области Backup to осуществляется выбор хранилища данных, ранее определенного в системе. Для добавления данного устройства надо воспользоваться кнопкой Add, после чего на экране отобразиться диалоговое окно выбора хранилища данных. Здесь можно явно указать файл для резервного копирования, используя область File Name, или осуществить выбор уже существующего устройства в списке Backup Device.

рис. 3.46

С помощью опций Append to media и Overwrite existing media диалогового окна настройки процесса резервного копирования определяется, будет ли добавляться новый процесс резервирования в данное хранилище или его предварительно необходимо очистить, и после этого произвести запись процесса.

рис. 3.47

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

Для проверки записанной в хранилище информации надо выбрать необходимое устройство в списке объектов SQL-сервера Backup, после чего воспользоваться командой Properties меню Action. На экране будет отображено диалоговое окно свойств хранилища данных, в котором необходимо выбрать кнопку View Contents. Это приведет к открытию диалогового окна просмотра содержимого этого хранилища, в котором описаны следующие параметры: название процесса резервирование, имя SQL-сервера, имя базы данных, тип резервирования, дата резервирования, отметка об истечении срока, размер копии.

рис. 3.48

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

Выбрать в списке объектов SQL-сервера базу данных, которую надо восстановить или установить курсор на группе Database.

Выполнить команду All Tasks / Restore Database меню Action, что приведет к открытию диалогового окна восстановления базы данных.

рис. 3.49

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

Если выбрать в группе Restore режим Database, а также выбрать в поле First backup to restore дату ее создания, то нажатие кнопки OK приведет к запуску процесса восстановления базы данных.

Если выбрать в группе Restore данного диалогового окна режим From device, что приведет к открытию окна настройки процесса восстановления базы данных с устройства резервного копирования. С помощью кнопки Select Device данного окна на экране отображается настройка использования хранилища. Если хранилище не задано, то его надо добавить в список, используя кнопку Add этого диалогового окна.

рис. 3.50

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

рис. 3.51

Затем выбрать параметры записи данных:

Database complete;

Database differential;

Transaction log;

File and filegroup.

Нажатие кнопки OK приведет к запуску процесса восстановления базы данных из выбранной резервной копии.

КОНТРОЛЬНЫЕ ВОПРОСЫ

1. Какие реализуются в основные принципы защиты данных в языке SQL?

2. Какие привилегии могут быть созданы в SQL?

3. С помощью каких команд в SQL даются и отменяются привилегии?

4. Что представляет собой двухуровневая настройка ограничения доступа к данным?

5. Как изменить настройки созданной учетной записи?

6. Для чего нужны системные таблицы?

7. Что такое системный каталог?

8. Что необходимо сделать перед началом процесса резервного копирования баз данных?

ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ

1. Создайте учетную запись New_User. Для создаваемой учетной записи определите серверную роль Security Administrators.

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

3. Создайте новое хранилище данных для дальнейшего резервного копирования.

4. Настройте параметры вновь созданного хранилища данных.

5. Проверьте записанную информацию в хранилище данных.

6. Удалите некоторые записи из любой таблицы, а затем восстановите базу данных из созданной резервной копии.

ВЫВОДЫ

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

Для решения поставленных задач было сделано следующее:

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

a) Отсутствие методической поддержки спецкурса «Сетевые БД», предусмотренного учебным планом подготовки студентов по специальности Н.08.01.00 Прикладная математика (методических разработок, пособий, дидактических пособий). Пособия, посвящённые проблемам разработки многопользовательских информационных систем имеют существенные недостатки с точки зрения возможности применения их для подготовки студентов в области многопользовательских БД. В них отсутствует единая методическая система формирования необходимых знаний и умений, нарушены дидактические принципы систематичности и последовательности при изложении учебного материала.

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