Кафедра «Автоматизированные системы управления»

Стр 1 из 8Следующая ⇒

МИНИСТЕРСТВО ОБРАЗОВАНИЯ РЕСПУБЛИКИ БЕЛАРУСЬ

МИНИСТЕРСТВО ОБРАЗОВАНИЯ РОССИЙСКОЙ ФЕДЕРАЦИИ

Учреждение образования Белорусско-Российский университет

Кафедра «Автоматизированные системы управления»

Методические указания

По курсовому проектированию

по дисциплине

«Базы и банки данных»

Могилев, 2007

Содержание

Постановка задачи. Анализ предметной области. 4

Разработка функциональной модели. 11

Создание информационной модели. 24

Связывание функциональной и информационной моделей. 36

Генерация схемы базы данных в MS ACSESS 2000. 44

Генерация схемы базы данных в MS SQL SERVER. 51

Создание источника данных OBDC. Связь с таблицами. 54

Создание связи с таблицами баз данных SQL Server 56

Создание хранимых процедур. 59

Триггеры.. 66

Представления. 75

Курсоры.. 81

Разработка клиентского приложения. 85

Опишите процесс добавления внешних сущностей на диаграмму.

Для чего нужны хранилища

3. Что такое туннелирование?

4. Что означает туннель в начале стрелки?

5. Для чего нужна диаграмма дерева узлов?

6. Как выполняется процесс декомпозиции модели?

7. Как добавить функциональный блок на диаграмму?

8. Какие типы стрелок бывают в методологии IDEF0?

9. В какой нотации выполняется последний уровень детализирования?

10. Что такое контекстная диаграмма?

Для чего необходимо связывание?

Какая связь между сущностями и стрелками?

Как создать отчет о связывании?

Опишите процесс связывания.

5. Какие ассоциации задаются для сущностей?

6. Что означает ассоциация IRUN?

7. Для чего служит кнопка Migrate на закладке Arrow Data?

8. Как сформировать отчет о связывании?

9. Какие поля можно отразить в отчете?

10. Как задать действия применимые к атрибутам?

Создание хранимых процедур

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

CREATE PROCEDURE] procedure_name [; number]

[ {^parameter data_type} [VARYING] [= default] [OUTPUT] ] [,...n]

[WITH { RECOMPILE

| ENCRYPTION

| RECOMPILE. ENCRYPTION } ]

[FOR REPLICATION]

AS sql_statement [...n]

Синтаксис имеет следующее назначение:

procedure_name — имя создаваемой процедуры. Используя префиксы sp_, # и ##, можно определить создаваемую процедуру как системную или временную. При этом, однако, необходимо позаботиться и о размещении процедуры в соответствующей системной базе данных, поскольку команда CREATE PROCEDURE создает хранимую процедуру в текущей базе данных. Поэтому перед созданием процедуры необходимо выполнить команду USE, чтобы сделать требуемую базу данных текущей;

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

(^parameter — определяет имя параметра, который будет использоваться создаваемой хранимой процедурой для передачи входных или выходных данных. Параметры, определяемые при создании хранимой процедуры, являются локальными переменными, поэтому несколько хранимых процедур могут иметь абсолютно идентичные параметры;

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

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

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

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

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

FOR REPLICATION — процедура, определенная с использованием данного ключевого слова, предназначена исключительно для осуществления процесса репликации. Вы не можете сочетать это ключевое слово с ключевым словом WITH RECOMPILE;

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

AS — ключевое слово, определяющее начало кода хранимой процедуры. После этого ключевого слова следуют команды Transact-SQL, которые и составляют непосредственно тело процедуры (sql statement). Здесь можно использовать любые команды, включая вызов других хранимых процедур, за исключением команд, начинающихся с ключевого слова CREATE.

Примеры

1. Список выдач книг за текущий день.

CREATE PROCEDURE CpicokVidach

SELECT /*Перечисляем поля, которые будут выведены в результате запроса */

Пользование_библиотекой2.Табельный_номер, COUNT(Пользование_библиотекой2.Дата_выдачи)

FROM /*указываем имя таблицы из которых выбираются записи*/

Пользование_библиотекой2

WHERE /*задаем условие отбора*/

Пользование_библиотекой2.Дата_выдачи=(SELECT GETDATE())

GROUP BY /*производится группировка по указанному полю*/

Табельный_номер

/*SELECT GETDATE() позволяет получить текущую дату (год, месяц, число)

COUNT (< поле> ) возвращает количество записей какого-либо поля*/

2. Количество экземпляров какой-либо книги.

CREATE PROCEDURE KolExzemplarov

/*Объявляем необходимые переменные*/

@ISBN varchar(20)

/* Следующая конструкция проверяет, существуют ли записи в таблице «Книги» с заданным ISBN*/

IF not EXISTS (SELECT * FROM Книга WHERE ISBN = @ISBN)

RETURN 0 /*Вызывает конец процедуры KolExzemplarov */

SELECT Экземпляр.ISBN

INTO TEMP1 /*Сохраняет выбранные поля во временной таблице Temp1*/

FROM Экземпляр

WHERE ISBN = @ISBN

SELECT COUNT(ISBN) /*Count подсчитывает количество неповторяющихся записей поля ISBN*/

FROM TEMP1

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

CREATE PROCEDURE CpicokKnigCtudenta

@Chit_nom int /*Объявляем необходимые переменные*/

AS SELECT Студенты.Имя, Студенты.Фамилия, Пользование_библиотекой2.Шифр, Книга.Автор, Книга.Название

FROM /*указываем имена таблиц, из которых выбираются записи*/

Книга, Экземпляр, Студенты, Пользование_библиотекой2

WHERE (Студенты.Читательский_номер = Пользование_библиотекой2.Читательский_номер) AND (Экземпляр.Шифр = Пользование_библиотекой2.Шифр) AND (Экземпляр.ISBN =Книга.ISBN)

/* AND позволяет задать в операторе WHERE несколько условий, которые должны выполняться одновременно*/

4. Удаление из таблицы «Студенты». Допустимо, если в таблице «Пользование библиотекой2» нет ссылающихся записей.

CREATE PROCEDURE DeleteStudent

@Chit_nom int /*Объявляем необходимые переменные*/

AS /*Проверяем, если ссылающиеся записи, если записей нет, разрешается удаление.*/

IF not EXISTS (SELECT * FROM Пользование_библиотекой2 WHERE Читательский_номер=@Chit_nom)

DELETE /*Оператор удаления*/

FROM Студенты /*Имя таблицы, откуда нужно удалить*/

WHERE /*Условие удаления – удаляем строку, для которой значение поля Читательский_номер совпадает с нужным*/

Читательский_номер=@Chit_nom

5. Вставка в таблицу «Заказы». Разрешена, если в таблицах «Книга» и «Преподаватели» есть записи, на которые будет ссылаться новая запись.

PROCEDURE NewZakaz

@Kolvo int, /*Объявляем необходимые переменные*/

@data_zakaza datetime,

@Chit_nomer int,

@ISBN varchar(20)

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

IF EXISTS (SELECT * FROM Заказы WHERE ISBN = @ISBN AND Читательский_номер=@Chit_nomer)

RETURN 0 /*Если есть, завершаем выполнение процедуры*/

IF EXISTS (SELECT * FROM Преподаватели WHERE Читательский_номер = @Chit_nomer)

/*Проверили, есть ли в «Преподаватели» соответствующая запись*/

IF EXISTS (SELECT * FROM Книга WHERE ISBN = @ISBN)

/*Проверили, есть ли в «Книга» соответствующая запись*/

INSERT INTO Заказы /*Указываем таблицу, куда вставляем запись*/

VALUES ( @Kolvo, @data_zakaza, @Chit_nomer, @ISBN) /*Указываем какие значения*/

6. Обновление таблицы «Студенты». Изменение фамилии студента.

CREATE PROCEDURE UpdateStudent

@Chit_nom int, /*Объявляем необходимые переменные*/

@Fam varchar(20)

IF EXISTS (SELECT * FROM Студенты /*Проверяем, существуют ли студенты, */

WHERE Читательский_номер = @Chit_nom) /*читательский номер которых равен искомому*/

UPDATE Студенты /*Если такие есть обновляем «Студенты»

SET Фамилия=@Fam /*полю фамилия присваиваем новое значение*/

WHERE Читательский_номер = @Chit_nom /*если читательский номер записи равен искомому*/

7. Вставка в таблицу «Пользование библиотекой2». Разрешается, если есть в таблицах «Студенты», «Сотрудники_библиотеки» и «Экземпляр» соответствующие записи.

ALTER PROCEDURE NewPolzovanieStydentov

@Chit_nomer int, /*Объявляем необходимые переменные*/

@data_vidachi datetime,

@data_priema datetime,

@tab_nomer int,

@Shifr varchar(20)

IF EXISTS (SELECT * FROM Пользование_библиотекой2 /*Проверяем, нет ли уже в таблице */

WHERE Шифр = @Shifr AND /*записи с таким же значением первичного ключа*/

Читательский_номер=@Chit_nomer)

RETURN 0 /*если есть, завершается процедура*/

IF EXISTS (SELECT * FROM Студенты /*проверяем наличие соответствующей записи в*/

WHERE Читательский_номер = @Chit_nomer) /*таблице «Студенты»*/

IF EXISTS (SELECT * FROM Экземпляр /*проверяем наличие соответствующей записи в*/

WHERE Шифр = @Shifr) /*таблице «Экземпляр»*/

IF EXISTS (SELECT * FROM Сотрудники_библиотеки /*проверяем наличие соответствующей */

WHERE табельный_номер = @tab_nomer) /* записи в таблице «Сотрудники_библиотеки»*/

INSERT /*если условия выполняются, добавляем*/

INTO Пользование_библиотекой2 /*в таблицу новые значения*/

VALUES ( @Chit_nomer, @data_vidachi, @data_priema, @tab_nomer, @Shifr)

8. Вставка в таблицу «Сотрудники_библиотеки». Проверяется, наличие соответствующей записи в поле номер-отдела в таблице «Отделы».

CREATE PROCEDURE NewSotrudnik

@Tab_nom int, /*Объявляем необходимые переменные*/

@Fam varchar(20),

@Name varchar(20),

@Sec_name varchar(20),

@data_rogd datetime,

@Dolgn varchar(20),

@Nom_otd int

IF EXISTS (SELECT * FROM Сотрудники_библиотеки /*Проверяем, нет ли уже в таблице */

WHERE Табельный_номер = @Tab_nom) /*записи с таким же значением первичного ключа*/

RETURN 0 /*если есть, завершается процедура*/

IF EXISTS (SELECT * FROM Отделы /*Проверяем, есть ли уже в таблице «Отделы» */

WHERE Номер_отдела = @Nom_otd) /*записи с таким же значением поля номер_отдела*/

INSERT /*если условие выполняется, добавляем*/

INTO Сотрудники_библиотеки /*в таблицу новые значения*/

VALUES ( @Tab_nom, @Fam, @Name, @Sec_name, @data_rogd, @Dolgn, @Nom_otd)

9. Сколько существует должностей в библиотеке.

CREATE PROCEDURE KolDolgn

AS SELECT COUNT (DISTINCT Сотрудники_библиотеки.должность)

FROM Сотрудники_библиотеки /*COUNT (DISTINCT < поле> ) подсчитывает количество разноименных значений какого-либо поля в таблице*/

10. Обновление таблицы «Отделы». Изменился начальник отдела.

ALTER PROCEDURE UpdateOtdel

@Nom_otdela int,

@Fam_New_Nach_otd varchar(20),

@Tab_Nom_New_Nach_otd int

IF not EXISTS (SELECT * FROM Сотрудники_библиотеки /*Условие проверяет, есть ли в */

WHERE табельный_номер = @Tab_Nom_New_Nach_otd /* библиотеке сотрудник с искомыми*/

AND фамилия=@Fam_New_Nach_otd) /*фамилией и табельным номером*/

RETURN 0 /*если нет, завершается процедура*/

IF EXISTS (SELECT * FROM Отделы /*Условие проверяет, есть ли в библиотеке*/

WHERE Номер_отдела = @Nom_otdela) /*искомый номер отдела*/

UPDATE Отделы

SET фамилия_начальника_отдела=@Fam_New_Nach_otd /*меняем значение поля «фамилия начальника отдела» на новое*/

UPDATE Отделы

SET табельный_номер_начальника=@Tab_Nom_New_Nach_otd /*меняем значение поля «табельный номер начальника отдела» на новое*/

WHERE Номер_отдела = @Nom_otdela /*меняем только для записи, у которой значение «номер_отдела» равно искомому*/

Вопросы:

1. Что такое хранимая процедура?

2. Чем хранимая процедура отличается от обычной процедуры?

3. С помощью какой команды можно создать хранимую процедуру?

4. С помощью какой команды можно изменять хранимую процедуру?

5. Какие стандартные виды хранимых процедур существуют?

6. Опишите структуру хранимой процедуры.

7. Для чего используется функция COUNT?

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

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

10. Для чего необходим оператор AS?

Триггеры

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

Существует три типа триггеров в зависимости от команд, на которые они реагируют:

1) Триггеры на вставку.

2) Триггеры на обновление.

3) Триггеры на удаление.

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

Для создания триггера используется следующая команда Transact SQL:

CREATE TRIGGER trigger_name

ON TABLE

[WITH ENCRYPTION]

{FOR {[DELETE] [, ] [INSERT] [, ] [UPDATE]}

[WITH APPEND]

[NOT FOR REPLICATION]

sql_statement [...n]

}

Trigger_name – задает имя триггера, с помощью которого он будет распознаваться хранимыми процедурами и командами Transact SQL. Имя триггера должно быть уникальным в пределах БД.

TABLE – имя таблицы БД, к которой будет привязан триггер.

WITH ENCRYPTION – при указании этой опции сервер выполняет шифрование кода триггера.

[DELETE] [, ] [INSERT] [, ] [UPDATE] – эта конструкция определяет, на какие автоматы будет реагировать триггер. При создании триггера должно быть указано хотя бы одно из этих ключевых слов, допускается создание триггера, реагирующего на две или три команды.

WITH APPEND – указание этого ключевого слова требуется для обеспечения совместимости с более ранними версиями SQL-сервер.

NOT FOR REPLICATION – запрещает запуск триггера при модификации таблиц с помощью репликации.

sql_statement – определяет набор команд, которые будут выполняться при запуске триггера.

Второй вариант данной команды:

CREATE TRIGGER trigger_name ON TABLE

[WITH ENCRYPTION]

{FOR { [[INSERT] [, ] [UPDATE]}

[WITH APPEND]

[NOT FOR REPLICATION]

{ IF UPDATE (column)

[ { AND/OR }

UPDATE(column) ]

[..n]}

sql_statement [...n]}}

FOR { [[INSERT] [, ] [UPDATE] – эта инструкция определяет, при выполнении какой команды будет запускаться триггер.

IF UPDATE (column) – использование этого параметра позволяет выполнить триггер при модификации конкретной колонки таблицы.

AND/OR UPDATE(column) – применение совпадает с предыдущим параметром, если необходимо выполнить запуск триггера при модификации нескольких колонок. Аргумент column задает имя колонки, при модификации которой будет производиться запуск триггера. Ключевое слово AND предписывает запускать триггер только в том случае, если были модифицированы обе колонки, указанные в этой и в предыдущей конструкции. При использовании ключевого слова OR триггер будет выполнен при выполнении изменений в любой из колонок. Допускается использование нескольких конструкций AND/OR UPDATE(column).

Для изменения триггера используется команда ALTER TRIGGER.

Примеры

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

CREATE TRIGGER udalenie /*Обьявляем имя триггера*/

ON Пользование_библиотекой2 /*Указываем имя таблицы, с которой будет связан триггер*/

FOR DELETE /*Указываем операцию, на кот. будет срабатывать триггер (здесь на удаление)*/

IF ( SELECT count(*) /*проверяет*/

from Пользование_библиотекой2 /*записи из таблицы «Пользование библиотекой»*/

where Пользование_библиотекой2.дата_выдачи is not null)> 0 /*условие проверяет наличие записи в поле «дата выдачи». Если count возвращает значение отличное от нуля (означает, что запись есть) то первое условие IF не выполнено*/

AND (CURRENT_USER < > 'dbo') /*вызывается функция определения имени текущего пользователя и проверяется, владелец ли он*/

BEGIN

PRINT 'у вас нет прав на удаление этой записи' /*выдача сообщения о неудаче операции*/

ROLLBACK TRANSACTION /*откат (отмена) транзакции*/

END

2. Проверяет правильность номера отдела. Существует лишь 5 отделов, которым присваиваются номера от 10 до 14 включительно.

CREATE TRIGGER dobavlenie

ON Отделы

FOR INSERT

DECLARE @@f int /*Объявляем переменную*/

Set @@f=10 /*Присваиваем ей значение*/

IF NOT EXISTS (SELECT * FROM Отделы, inserted

WHERE Отделы.Номер_отдела = inserted.Номер_отдела)

Set @@f=0

IF EXISTS (SELECT * FROM Отделы, inserted

WHERE inserted.Номер_отдела> 14 OR inserted.Номер_отдела< 10) /*Если номер отдела не > 14 или < 10, то такой отдел не существует*/

Set @@f=0 /*Меняем значение переменной*/

If @@f=0 /*если f=0, значит были ошибки*/

BEGIN

PRINT 'Неверно введены данные' /*выдача сообщения о неудаче операции*/

ROLLBACK TRANSACTION /*откат (отмена) транзакции*/

END

/*inserted – временная таблица, куда заносятся добавляемые данные*/

3. Триггер, который записывает при удалении записей из таблицы «Преподаватели» в отдельную таблицу информацию о дате удаления, пользователе,

Перед созданием такого триггера необходимо создать таблицу DeletedItems, куда будет производится запись:

CREATE TABLE DeletedItem (

[Читательский_номер] [int] NOT NULL, /*объявляем поля таблицы*/

[имя] [varchar] (25) NULL,

[отчество] [varchar] (75) NULL,

[фамилия] [varchar] (25) NULL,

[должность] [varchar] (35) NULL,

[Имя_пользователя] [varchar] (50) NULL,

[Дата_удаления] [datetime] NULL

) ON [PRIMARY]

Сам триггер выглядит следующим образом;

CREATE TRIGGER deletedby

ON Преподаватели /*Связываем триггер с таблицей Преподаватели*/

FOR DELETE

INSERT INTO DeletedItem

(Читательский_номер, имя, отчество, /*указываем, какие поля нужно вставить*/

фамилия, должность, Имя_пользователя, Дата_удаления)

SELECT Читательский_номер, имя, отчество, фамилия, должность,

SYSTEM_USER, /*функция определяет текущего пользователя*/

getdate() /*функция возвращает текущую дату*/

FROM deleted /*deleted – временная таблица, куда заносятся удаляемые данные*/

4. Триггер, который записывает при добавлении записей в таблицы «Сотрудники_Библиотеки» в отдельную таблицу информацию о дате удаления, пользователе.

Перед созданием такого триггера необходимо создать таблицу InsertedItemSotrydniki, куда будет производится запись:

CREATE TABLE InsertedItemSotrydniki

(

[табельный_номер] [int] NOT NULL, /*объявляем поля таблицы*/

[фамилия] [varchar] (25) NULL,

[имя] [varchar] (25) NULL,

[отчество] [varchar] (75) NULL,

[Дата_рождения] [datetime] NULL,

[должность] [varchar] (35) NULL,

[Имя_пользователя] [varchar] (50) NULL,

[Дата_добавления] [datetime] NULL

) ON [PRIMARY]

Сам триггер выглядит следующим образом;

CREATE TRIGGER infoInsertSotrud

ON Сотрудники_библиотеки /*Связываем триггер с таблицей Сотрудники_библиотеки */

FOR INSERT

INSERT INTO InsertedItemSotrydniki (Табельный_номер, /*указываем, какие поля нужно вставить*/

фамилия, имя, отчество, Дата_рождения, должность, Имя_пользователя, Дата_добавления)

SELECT Табельный_номер, фамилия, имя, отчество, Дата_рождения, должность,

SYSTEM_USER, /*функция определяет текущего пользователя*/

getdate() /*функция возвращает текущую дату*/

FROM inserted /*inserted – временная таблица, куда заносятся добавляемые данные*/

5. Триггер, который записывает при изменении записей в таблице «Студенты» в отдельную таблицу информацию о дате удаления, пользователе.

Перед созданием такого триггера необходимо создать таблицу UpdatedStudents, куда будет производится запись:

CREATE TABLE UpdatedStudents

(

[читательский_номер] [int] NOT NULL,

[фамилия] [varchar] (25) NULL,

[Имя_пользователя] [varchar] (50) NULL,

[Дата_добавления] [datetime] NULL

) ON [PRIMARY]

Триггер выглядит следующем образом:

CREATE TRIGGER infoUpdateStudent

ON Студенты /*Связываем с таблицей*/

FOR UPDATE

INSERT INTO UpdatedStudents (Читательский_номер, /*указываем, какие поля нужно вставить*/

фамилия, Имя_пользователя, Дата_добавления)

SELECT Читательский_номер, фамилия, /*указываем откуда*/

SYSTEM_USER, /*функция определяет текущего пользователя*/

getdate() /*функция возвращает текущую дату*/

FROM updated /*updated – временная таблица, куда заносятся данные*/

6. Триггер запрещает изменять в таблице «Пользование_библиотекой2» запись полей «дата_приема» и «дата выдачи» в воскресенье.

ALTER TRIGGER proverka ON Пользование_библиотекой2

FOR UPDATE

IF UPDATE(дата_приема) /*Проверяет, какое поле обновляется*/

AND datename(dw, GETDATE()) in ('Sunday') /*Проверяет, является ли день воскресеньем*/

BEGIN

PRINT 'В воскресение нельзя принимать книги'

ROLLBACK TRANSACTION

END

IF UPDATE(дата_выдачи) /*Проверяет, какое поле обновляется*/

AND datename(dw, GETDATE()) in ('Sunday') /*Проверяет, является ли день воскресеньем*/

BEGIN

PRINT 'В воскресение нельзя принимать книги'

ROLLBACK TRANSACTION

END

/* datename(dw, GETDATE()) – GETDATE()получает текущую дату, datename(dw, < дата> ) получает название дня недели*/

7. Триггер, который записывает при удалении записей из таблицы «Сотрудники_библиотеки» в отдельную таблицу информацию о дате удаления, пользователе,

Перед созданием такого триггера необходимо создать таблицу DeletedSotrudLibrary, куда будет производится запись:

CREATE TABLE DeletedSotrudLibrary (

[Табельный_номер] [int] NOT NULL, /*объявляем поля таблицы*/

[имя] [varchar] (25) NULL,

[фамилия] [varchar] (25) NULL,

[отчество] [varchar] (75) NULL,

[дата_рождения] [datetime] NULL,

[должность] [varchar] (35) NULL,

[номер_отдела] [int] NOT NULL,

[Имя_пользователя] [varchar] (50) NULL,

[Дата_удаления] [datetime] NULL

) ON [PRIMARY]

Триггер:

CREATE TRIGGER deletesotrud

ON Сотрудники_библиотеки /*Связываем с таблицей*/

FOR DELETE

INSERT INTO DeletedSotrudLibrary /*Указываем таблицу, куда будет производиться запись*/

(Табельный_номер, имя, фамилия, отчество, дата_рождения, /*Пречисляем поля таблицы*/

должность, номер_отдела, Имя_пользователя, Дата_удаления)

SELECT /*Причисляем вносимые значения*/

Табельный_номер, имя, фамилия, отчество, дата_рождения, должность, номер_отдела,

SYSTEM_USER, getdate()

FROM deleted /*deleted – временная таблица, куда заносятся удаляемые данные*/

8. Триггер, который записывает при добавлении записей в таблицы «Преподаватели» в отдельную таблицу информацию о дате удаления, пользователе.

Перед созданием такого триггера необходимо создать таблицу InsertedItemPrepdavateli, куда будет производится запись

CREATE TABLE InsertedItemPrepdavateli

(

[Читательский_номер] [int] NOT NULL, /*объявляем поля таблицы*/

[имя] [varchar] (25) NULL,

[отчество] [varchar] (75) NULL,

[фамилия] [varchar] (25) NULL,

[должность] [varchar] (35) NULL,

[Имя_пользователя] [varchar] (50) NULL,

[Дата_удаления] [datetime] NULL

) ON [PRIMARY]

Триггер:

CREATE TRIGGER infoInsertPred

ON Преподаватели

FOR INSERT

INSERT INTO InsertedItemPrepdavateli

(Читательский_номер, имя, отчество, фамилия, должность, Имя_пользователя, Дата_удаления)

SELECT Читательский_номер, имя, отчество, фамилия, должность,

SYSTEM_USER, getdate()

FROM inserted

9. Триггер, запрещающий ввод значения в поле «год_поступления», если оно превышает номер текущего года.

CREATE TRIGGER proverka_Studentov ON Студенты FOR INSERT

DECLARE @@t int /*Объявляем переменную*/

Set @@t=5 /*Присваиваем ей значение*/

IF NOT EXISTS (SELECT * FROM Студенты, inserted

WHERE Студенты.год_поступления = inserted.год_поступления)

Set @@t=0

IF EXISTS (SELECT * FROM Студенты, inserted

WHERE inserted.год_поступления> YEAR(GETDATE())) /*Проверяем, больше ли значения вводимого года, чем у текущего*/

Set @@t=0 /*Если да, то меняем значение переменной*/

If @@t=0 /*если f=0, значит были ошибки*/

BEGIN

PRINT 'Неверно введен год поступления' /*выдача сообщения о неудаче операции*/

ROLLBACK TRANSACTION /*откат (отмена) транзакции*/

END

10. Триггер, запрещающий обновление значения в поле «год_отчисления», если оно превышает номер текущего года.

CREATE TRIGGER proverka_otchislenia_Studentov

ON Студенты

FOR UPDATE

DECLARE @@t int /*Объявляем переменную*/

Set @@t=5 /*Присваиваем ей значение*/

IF NOT EXISTS (SELECT * FROM Студенты, inserted

WHERE Студенты.год_окончания = inserted.год_окончания)

Set @@t=0

IF EXISTS (SELECT * FROM Студенты, inserted

WHERE inserted.год_окончания> YEAR(GETDATE())) /*Проверяем, больше ли значения вводимого года, чем у текущего*/

Set @@t=0 /*Меняем значение переменной*/

If @@t=0 /*если f=0, значит были ошибки*/

BEGIN

PRINT 'Неверно введен год отчисления' /*выдача сообщения о неудаче операции*/

ROLLBACK TRANSACTION /*откат (отмена) транзакции*/

END

Вопросы:

1. Что такое триггер?

2. Чем хранимая процедура отличается от триггера?

3. С помощью какой команды можно создать триггер?

4. С помощью какой команды можно изменять триггер?

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

6. Опишите структуру триггера.

7. Для чего используется функция ROLLBACK TRANSACTION?

8. Как описываются переменные, используемые в триггером?

9. Как вызвать триггер?

10. Для чего предназначен триггер с оператором FOR INSERT?

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

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

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

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

Создание представлений с использованием Transact-SQL

CREATE VIEW view_name [(column [,...n])]

[WITH ENCRYPTION]

select_statement

[WITH CHECK OPTION]

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

column — имя колонки, которое будет использоваться в представлении. Максимальная длина имени составляет 128 символов. По умолчанию имена колонок в представлении соответствуют именам колонок в исходных таблицах. Явное указание имени колонки требуется при использовании вычисляемых колонок или при объединении нескольких таблиц, имеющих колонки с одинаковыми именами. Имена колонок перечисляются через запятую в соответствии с их порядком в представлении. Имена колонок можно указывать в команде SELECT, определяющей представление.

WITH ENCRYPTION - использование этого параметра предписывает серверу шифровать код SQL-запроса. Это гарантирует, что пользователи не смогут просмотреть код запроса и использовать его. Если при определении представления необходимо скрыть имена исходных таблиц и колонок, а также алгоритм объединения данных, то необходимо использовать эту опцию.

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

1) Нельзя создавать новую таблицу на основе результатов, полученных в ходе выполнения запроса, то есть запрещается использование параметра INTO.2) Нельзя проводить выборку данных из временных таблиц, то есть нельзя использовать имена таблиц, начинающихся на # или ##.

В запрос нельзя включать операции вычисления и группировки, то есть запрещается указание параметров ORDER BY, COMPUTE и COMPUTE BY.

Для удаления представления используется команда Transact SQL DROP VIEW{view […n]}. За один раз можно удалить несколько представлений.

Примеры

1.Вывод ограниченной информации о студентах с фамилией, оканчивающейся на «ов».

CREATE VIEW infoStudent /*Указываем имя представления*/

SELECT /*Указываем, какие поля будут выведены*/

Студенты.Имя, Студенты.Фамилия, Студенты.факультет, Студенты.специальность

FROM Студенты /*Из какой таблицы*/

WHERE Студенты.Фамилия LIKE ‘[]ов’

2. Информация о замене экземпляров по программированию на С++.

CREATE VIEW infoZamena

SELECT /*Указываем, какие поля, из каких таблиц будут выведены*/

Книга.Автор, Книга.Название, Экземпляр.Шифр, Замена_экземпляров.Номер_акта_замены

FROM /*Указываем таблицу, и связанные с ней при помощи оператора INNER JOIN таблицы, из которых выбираются связанные данные. После операнда ON указываем, по каким полям связаны две таблицы*/

(Книга INNER JOIN Экземпляр ON Книга.ISBN =Экземпляр.ISBN)

INNER JOIN Замена_экземпляров ON Экземпляр.Шифр = Замена_экземпляров.Шифр

WHERE Книга.Название LIKE '%C++' /*Выбираются только те книги, в названии которых присутствует “C++” */

3. Информация о списанных экземплярах по программированию на Delphi.

CREATE VIEW infoCpicanie

SELECT /*Указываем, какие поля, из каких таблиц будут выведены*/

Книга.Автор, Книга.Название, Экземпляр.Шифр, Списанные_книги.причина_списания, Списанные_книги.номер_протокола_списания, Списанные_книги.Табельный_номер_списавшего

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

Книга INNER JOIN (Экземпляр INNER JOIN Списанные_книги ON Экземпляр.Шифр = Списанные_книги.Шифр) ON Книга.ISBN = Экземпляр.ISBN

WHERE Книга.Название LIKE '%Delphi' /*Выбираются только те книги, в названии которых присутствует “ Delphi */

4. Информация о преподавателях и их заказах на литературу, количество книг должно быть от 10 до 20 или от 25 до 30.

CREATE VIEW infoZakazi /*Объявляем имя представления*/

SELECT /*Указываем, какие поля таблиц будут выведены*/

Преподаватели.Фамилия, Преподаватели.Имя, Преподаватели.Отчество, Книга.Автор, Книга.Название, Заказы.Количество

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

Книга INNER JOIN (Преподаватели INNER JOIN Заказы ON Преподаватели.Читательский_номер = Заказы.Читательский_номер)

ON Книга.ISBN = Заказы.ISBN

WHERE (Заказы.Количество BETWEEN 10 AND 20) OR (Заказы.Количество BETWEEN 25 AND 30) /* 10< количество заказов< 20 или 25< количество заказов< 30 */

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

CREATE VIEW infoOPolzovaniiStudentov

SELECT /*Указываем, какие поля таблиц будут выведены*/

Студенты.Имя, Студенты.Фамилия, Книга.Автор, Книга.Название, Сотрудники_библиотеки.табельный_номер

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

(((Книга INNER JOIN Экземпляр ON Книга.ISBN = Экземпляр.ISBN) INNER JOIN

Пользование_библиотекой2 ON Экземпляр.Шифр = Пользование_библиотекой2.Шифр) INNER JOIN

Студенты ON Пользование_библиотекой2.Читательский_номер = Студенты.Читательский_номер) INNER JOIN

Сотрудники_библиотеки ON Пользование_библиотекой2.Табельный_номер = Сотрудники_библиотеки.табельный_номер

WHERE Сотрудники_библиотеки.табельный_номер LIKE '10[]' /*Табельный номер сотрудников должен начинаться с «11»*/

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

CREATE VIEW infoOPolzovaniiPrepodovatelej

SELECT /*Указываем, какие поля таблиц будут выведены*/

Преподаватели.Имя, Преподаватели.Фамилия, Книга.Автор, Книга.Название, Сотрудники_библиотеки.табельный_номер

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

(((Книга INNER JOIN Экземпляр ON Книга.ISBN = Экземпляр.ISBN) INNER JOIN

Пользование_библиотекой3 ON Экземпляр.Шифр = Пользование_библиотекой3.Шифр) INNER JOIN

Преподаватели ON Пользование_библиотекой3.Читательский_номер = Преподаватели.Читательский_номер) INNER JOIN

Сотрудники_библиотеки ON Пользование_библиотекой3.Табельный_номер = Сотрудники_библиотеки.табельный_номер

WHERE Сотрудники_библиотеки.табельный_номер LIKE '10[]' /*Табельный номер сотрудников должен начинаться с «11»*/

7.Информация о отделах 11 и 12 и работающих в них сотрудниках.

CREATE VIEW Otdeli

SELECT /*Указываем какие поля будут выбраны*/

Отделы.номер_отдела, Отделы.название_отдела, Сотрудники_библиотеки.фамилия,

Сотрудники_библиотеки.имя, Сотрудники_библиотеки.отчество

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

Отделы INNER JOIN Сотрудники_библиотеки ON Отделы.Номер_отдела = Сотрудники_библиотеки.Номер_отдела

GROUP BY номер.отдела /*По какому полю будут сгруппированы поля*/

WHERE Отделы.номер_отдела BETWEEN 10 AND 13 /*Выбираются отделы, номер которых находится между 10 и 13*/

8.Информация о месте хранения периодических изданий название которых содержит в себе слово «вестник», но при этом д.б. исключены периодические издания, первое слово которых начинается на «Э», например «Экономический вестник».

CREATE VIEW INFOPeriodika

SELECT /*Указываем какие поля будут выбраны*/

Периодические_издания.Название, Периодические_издания.Дата_издания, Отделы.Название_отдела, Отделы.Номер_отдела

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

Отделы INNER JOIN Периодические_издания ON Отделы.Номер_отдела = Периодические_издания.Номер_отдела

WHERE Периодические_издания.Название LIKE '[^Э] %вестник%'

/*[^Э] запрещает начинаться первому слову на «Э», % вестник% – указывает на то, что оставшаяся часть названия должна содержать слово «вестник»*/

9. Полная информация об экземпляре, который был издан с 2000 до текущего года

CREATE VIEW FullInfoEkzemplar

SELECT /*Указываем какие поля будут выбраны*/

Экземпляр.шифр, Книга.Автор, Книга.Название, Книга.Год_издания, Книга.Издательство, Экземпляр.Предметная_область, Экземпляр.Номер_отдела, Экземпляр.Отметка_о_списании, Экземпляр.Отметка_о_замене

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

Книга INNER JOIN Экземпляр ON Книга.ISBN = Экземпляр.ISBN

WHERE Книга.Год_издания BETWEEN 2000 AND YEAR (GETDATE())

/*GETDATE() возвращает текущую дату, YEAR (< дата> ) – год < даты> */

10. Информация о наличии книги в библиотеке

CREATE VIEW InfoAboutBook

SELECT /*Указываем какие поля будут выбраны*/

Книга.ISBN, Экземпляр.Шифр, Экземпляр.Номер_отдела,

Пользование_библиотекой2.Дата_выдачи, Пользование_библиотекой2.Дата_приема

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

(Книга INNER JOIN Экземпляр ON Книга.ISBN = Экземпляр.ISBN) INNER JOIN

Пользование_библиотекой2 ON Экземпляр.Шифр = Пользование_библиотекой2.Шифр

WHERE Пользование_библиотекой2.Дата_приема BETWEEN Пользование_библиотекой2.Дата_выдачи AND GETDATE() /*Если книгу сдали, т.е. она в наличии в библиотеке, то дата приема будет между датой выдачи и текущей датой*/

Вопросы:

1. Что такое представление?

2. С помощью какой команды можно создать представление?

3. С помощью какой команды можно изменить представление?

4. Опишите структуру представления.

5. Для чего используется оператор INNER JOIN?

6. Можно ли использовать вычисляемые поля в представлениях?

7. Как просмотреть представление?

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

9. Какие ограничения существуют для команды SELECT?

10. Что означает параметр WITH ENCRYPTION?

Курсоры

12 3 4 5 6 7 8 Следующая ⇒