Курсовая работа (т): Создание автоматизированной информационной системы кадрового учета

CREATE VIEW Komandirovki

ASdbo.Сотрудники.Название_отдела, dbo.Сотрудники.Личный_номер, LTRIM(dbo.Сотрудники.Фамилия) AS Фамилия, dbo.Сотрудники.Имя, dbo.Сотрудники.Отчество, // LTRIM - функция, которая возвращает значение колонки «Фамилия» после удаления начальных пробелов.Сотрудники.Должность, dbo.Командировки.Номер_командировки, dbo.Командировки.Дата_сdbo.Сотрудники INNER JOIN dbo.Командировки ON dbo.Сотрудники.Личный_номер = dbo.Командировки.Личный_номер

WHERE (YEAR(dbo.Командировки.Дата_с) = YEAR(DATEADD(YEAR, - 1, GETDATE()))) // Выборка строк таблицы, где год в колонке «Дата_с» совпадает с прошлым годом, в соответствии с текущей датой, установленной на данном сервере

.        Вывод в таблицу всех сотрудников, имеющий высшее образование

CREATE VIEW Obrazovanie

ASЛичный_номер, LTRIM(Фамилия) AS Фамилия, Имя, Отчество, // LTRIM - функция, которая возвращает значение колонки «Фамилия» после удаления начальных пробелов

Должность, Звание, UPPER(Образование) AS Образованиеdbo.Сотрудники(Образование LIKE '%высшее') // Выборка строк таблицы, где значение колонки «Образование» совпадает с указанным значением «высшее»

.        Вывод в таблицу сотрудников, работающих в отделе ООПиП

CREATE VIEW Otdel

ASНазвание_отдела, Личный_номер, LTRIM(Фамилия) AS

// LTRIM - функция, которая возвращает значение колонки «Фамилия» после удаления начальных пробелов

Фамилия, Имя, Отчество, Должность, Званиеdbo.Сотрудники(Название_отдела LIKE '%ООПиП') // Выборка строк таблицы, где значение колонки «Название_отдела» совпадает с указанным значением «ООПиП»

.        Предоставляет информацию обо всех отпусках

CREATE VIEW Otpusk

ASdbo.Сотрудники.Личный_номер, dbo.Сотрудники.Название_отдела, dbo.Сотрудники.Фамилия, dbo.Сотрудники.Имя, dbo.Сотрудники.Отчество, dbo.Сотрудники.Должность, dbo.Табель_отпусков.Номер_табеля, dbo.Табель_отпусков.Тип_отпуска, dbo.Табель_отпусков.Дата_с, DATEDIFF(day, dbo.Табель_отпусков.Дата_с, GETDATE()) AS [Количество дней] // DATEDIFF - функция, которая возвращает интервал времени day, прошедшего от указанной даты Дата_с до текущей даты, установленной на данном сервере, GETDATE - возвращает текущую дату, установленную на данном сервере

FROM dbo.Табель_отпусков INNER JOIN dbo.Сотрудники ON dbo.Табель_отпусков.Личный_номер = dbo.Сотрудники.Личный_номер

.        Предоставление информации о приказах и сотрудниках, ответственных за их выполнение

CREATE VIEW Prikaz

ASdbo.Приказы.Номер_приказа, dbo.Приказы_сотрудников.Название_отдела, RTRIM(dbo.Приказы.Ответственный) AS Ответственный, // RTRIM - функция, которая возвращает значение колонки «Фамилия» после удаления пробелов из конца строки.Приказы.Дата_приказаdbo.Приказы INNER JOIN dbo.Приказы_сотрудников ON dbo.Приказы.Номер_приказа = dbo.Приказы_сотрудников.Номер_приказа

.        Предоставление информации о штатном расписании

CREATE VIEW Raspisanie

ASНомер_штатного_расписания, Название_отдела, RTRIM(Фамилия_начальника) + SPACE(2) + LTRIM(Имя_начальника) + SPACE(2) + LTRIM(Отчество_начальника) AS Начальник, // RTRIM - функция, которая возвращает значение колонки «Фамилия» после удаления пробелов из конца строки, LTRIM - функция, которая возвращает значение колонки «Фамилия» после удаления начальных пробелов, SPACE - функция, которая возвращает строку пробелов

Количество_штатных_единиц, Тарифная_ставка, Примечаниеdbo.Отделы

.        Вывод в таблицу сотрудников, которые служили в ВС

CREATE VIEW Sluzba_v_VS

SELECT dbo.Сотрудники.Личный_номер, dbo.Сотрудники.Название_отдела, dbo.Сотрудники.Фамилия, dbo.Сотрудники.Имя, dbo.Сотрудники.Отчество,(dbo.Сотрудники.Должность) AS Expr1, dbo.Сотрудники.Звание,

// UPPER - функция преобразования текста в верхний регистр.Спецзвания.Выслугаdbo.Сотрудники INNER JOIN dbo.Спецзвания ON dbo.Сотрудники.Личный_номер = dbo.Спецзвания.Личный_номер AND dbo.Сотрудники.Название_отдела = dbo.Спецзвания.Название_отдела(dbo.Спецзвания.Служба_в_ВС = 'да') // Выборка строк таблицы, где значения в колонке «Служба_в_ВС» равны «да»

.        Предоставляет информацию обо всех командировках

CREATE VIEW Trip

AS

SELECT dbo.Сотрудники.Личный_номер, dbo.Сотрудники.Название_отдела, dbo.Сотрудники.Фамилия, dbo.Сотрудники.Имя, dbo.Сотрудники.Отчество, dbo.Сотрудники.Должность, dbo.Командировки.Номер_командировки, dbo.Командировки.Место, CONVERT(VARCHAR(11), dbo.Командировки.Дата_с, 106) // CONVERT - функция преобразования типа данных в varchar(11) значений колонки «Дата_с», 106 - формат даты дд мм гггг

AS Дата_с, dbo.Командировки.Срок

FROM dbo.Командировки INNER JOIN dbo.Сотрудники ON dbo.Командировки.Личный_номер = dbo.Сотрудники.Личный_номер

.        Предоставляет информацию о нормативных документах типа «Закон РБ»

CREATE VIEW Ukaz

ASНомер_нормативного_документа, REPLACE('Закон РБ', 'РБ', 'Республики Беларусь') AS Тип, Дата_документа // REPLACE - функция замены всех вхождений значения «РБ» в значении «Закон РБ» на «Республики Беларусь»dbo.Нормативные_документы

WHERE (Тип LIKE '%Закон РБ') // Выборка строк таблицы, где значение колонки «Тип» совпадает с указанным значением «Закон_РБ»

3.2 T-SQL-определения триггеров

. Триггер, который будет срабатывать при удалении нормативного документа из таблицы «Нормативные_документы_отделов». Все удаленные строки будут заноситься в таблицу «DeletedItem», а также имя пользователя, удалившего строку, и дату

CREATE TABLE DeletedItem

([Номер_нормативного_документа] [int] NOT NULL,

[Название_отдела] [varchar] (100) NULL,

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

[Дата_удаления][datetime] NULL )[PRIMARY] TRIGGER deletedBy

FOR DELETE // Триггер будет срабатывать на удаление из таблицы «Нормативные_документы_отделов»INTO DeletedItem

(Номер_нормативного_документа, Название_отдела, Имя_пользователя, Дата_удаления)Номер_нормативного_документа, Название_отдела,SYSTEM_USER, GETDATE() // SYSTEM_USER - вставка в таблицу значение текущего имени входа, GETDATE − возвращает текущую дату, установленную на данном сервереdeleted // Deleted - временная таблица, куда заносятся удаляемые данные

. Срабатывает при добавлении в базу данных нового отдела и проверяет, чтобы номер штатного расписания нового отдела был в пределах 1000<номер_штатного_расписания<2000. Если номер не соответствует условию, появляется сообщение с предупреждением

CREATE TRIGGER NomerOtdela

ON Отделы

FOR INSERT // Триггер будет срабатывать на вставку в таблице «Отделы»

AS

DECLARE @@f int // Объявление переменной

Set @@f=1000

IF NOT EXISTS (SELECT * FROM Отделы, inserted // Проверка на отсутствие строк в таблице

WHERE Отделы.Номер_штатного_расписания = inserted.Номер_штатного_расписания)

Set @@f=0EXISTS (SELECT * FROM Отделы, inserted inserted.Номер_штатного_расписания>2000 OR inserted.Номер_штатного_расписания<1000) // Проверка при вводе строки в таблицу, чтобы значение в колонке «Номер_штатного_расписания» не был больше 2000 и меньше 1000

Set @@f=0@@f=0UPPER ('Ошибка! Проверьте номер штатного расписания.')

// Предупреждающее сообщение с преобразованием в верхний регистр

ROLLBACK TRANSACTION // Откат транзакции

END

. Будет проверять, чтобы при вводе данных в таблицу «Командировки» год даты командировки «Дата_с» не был больше текущего года (например, 2015 год)

CREATE TRIGGER proverka_komandirovkaКомандировки INSERT // Триггер будет срабатывать на вставку в таблице «Командировки»

ASEXISTS (SELECT * FROM Командировки, insertedinserted.Дата_с>YEAR(DATEADD(YEAR, +1, GETDATE())))

// Проверка при вводе строки в таблицу, чтобы значение года в колонке «Дата_с» не было равно год+1 от текущей даты, установленной на данном сервере

BEGIN

PRINT 'Неверно введена дата командировки!' // Предупреждающее сообщение

ROLLBACK TRANSACTION // Откат транзакции

END

. Будет срабатывать при обновлении тарифной ставки в таблице «Отделы» и выдавать изменённую среднюю тарифную ставку по отделам

CREATE TRIGGER Upd_Tarif

ON Отделы

FOR UPDATE // Триггер будет срабатывать на обновление в таблице «Отделы»

ASUPDATE(Тарифная_ставка) 'Тарифная ставка повышена!' // Предупреждающее сообщение

SELECT AVG(Тарифная_ставка) AS 'Изменённая средняя тарифная ставка' // AVG - функция, которая возвращает среднее значение от всех значений в колонке «Тарифная_ставка»

FROM Отделы

END

. Триггер, который будет проверять, чтобы в таблицу «Табель_отпусков» не вносились увольнения, т.к. для этого существует отдельная таблица

CREATE TRIGGER Ins_OtpuskТабель_отпусков INSERT // Триггер будет срабатывать на вставку данных в таблицу «Табель_отпусков»

ASEXISTS (SELECT * FROM Табель_отпусков, inserted inserted.Тип_отпуска = 'увольнение') // Проверка при вводе строки в таблицу, чтобы значение в колонке «Типа_отпуска» не было «Увольнение»

BEGIN

PRINT 'Ошибка! Проверьте тип отпуска.' // Предупреждающее сообщение

SELECT CURRENT_TIMESTAMP // CURRENT_TIMESTAMP - функция возвращающая значение текущей даты, установленной на данном сервере

ROLLBACK TRANSACTION // Откат транзакции

END

. Триггер проверяет, чтобы при добавлении нового отдела в таблицу «Отделы» в поле «Количество_штатных_единиц» не было нуля. В противном случае появляется сообщение с предупреждением

CREATE TRIGGER Ins_Otdel

ON Отделы

FOR INSERT // Триггер будет срабатывать на вставку в таблицу «Отделы»

ASEXISTS (SELECT * FROM Отделы, inserted inserted.Количество_штатных_единиц = 0) // Проверка при вводе строки в таблицу, чтобы значение в колонке «Количество_штатных_единиц» не было равно 0

BEGIN

PRINT 'Ошибка! В отделе не может работать 0 человек.'

// Предупреждающее сообщение

SELECT GETUTCDATE() AS 'Дата:' // GETUTCDATE - функция, возвращающая значение текущей даты, установленной на данном сервере

ROLLBACK TRANSACTION // Откат транзакции

END

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

CREATE TABLE DeletedWorker (

[Личный_номер] [int] NOT NULL ,

[Фамилия] [varchar] (50) NULL ,

[Имя] [varchar] (50) NULL ,

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

[Образование][varchar] (100) NULL,

[Должность] [varchar] (100) NULL ,

[Звание] [varchar](100) NULL ,

[Адрес_город][varchar] (50) NULL,

[Адрес_улица][varchar] (50) NULL,

[Адрес_дом][int] NOT NULL,

[Адрес_квартира][varchar] (20) NULL,

[Название_отдела][varchar] (100) NOT NULL,

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

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

ON [PRIMARY]TRIGGER DelWorkerСотрудники DELETE // Триггер будет срабатывать на удаление данных из таблицы «Сотрудники»

ASINTO DeletedWorker // Вставить удаляемые данные в таблицу DeletedWorker

(Личный_номер , Фамилия , Имя, Отчество, Образование, Должность, Звание, Адрес_город, Адрес_улица, Адрес_дом,

Адрес_квартира, Название_отдела, Имя_пользователя,Дата_удаления)Личный_номер , Фамилия , Имя, Отчество, Образование, Должность, Звание, Адрес_город, Адрес_улица,

Адрес_дом, Адрес_квартира, Название_отдела, SYSTEM_USER, GETDATE()deleted // Deleted - временная таблица, куда заносятся удаляемые данные@@ROWCOUNT // ROWCOUNT - функция, которая возвращает число затронутых при выполнении транзакции строк

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

CREATE TRIGGER Ins_KomandirovkaКомандировки INSERT // Триггер будет срабатывать на вставку данных в таблицу «Командировки»

ASEXISTS (SELECT * FROM Командировки, inserted inserted.Место = '') // Проверка при вводе строки в таблицу, чтобы значение в колонке «Место» не было пустое

BEGIN

PRINT 'Ошибка! Необходимо указать место командировки.'

// Предупреждающее сообщение

SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name' // SPID - функция, которая возвращает идентификатор сеанса для текущего процесса, SYSTEM_USER - логин текущего пользователя, USER - имя текущего пользователяTRANSACTION // Откат транзакции

. Триггер, который проверяет, чтобы при добавлении записи втаблицу «Спецзвания» было заполнено поле «Служба_в_ВС»

CREATE TRIGGER proverka_speczvaniaСпецзвания INSERT // Триггер будет срабатывать на вставку данных в таблицу «Спецзвания»

ASEXISTS (SELECT * FROM Спецзвания, inserted inserted.Служба_в_ВС = '') // Проверка при вводе строки в таблицу, чтобы значение в колонке «Служба_в_ВС» не было пустое

BEGIN

PRINT 'Отметьте выслугу!' // Предупреждающее сообщение

SELECT SESSION_USER AS 'Session User' // SESSION_USER - функция, которая возвращает имя пользователя текущей сессии

ROLLBACK TRANSACTION // Откат транзакции

END

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

CREATE TRIGGER Worker

ON Сотрудники

FOR INSERT // Триггер будет срабатывать на вставку данных в таблицу «Сотрудники»

ASEXISTS (SELECT * FROM Сотрудники, insertedinserted.Фамилия = '' OR inserted.Имя = '' OR inserted.Отчество = ''inserted.Образование = '' OR inserted.Адрес_город = '' OR inserted.Адрес_улица = '')

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

BEGIN

PRINT 'Заполнены не все поля!' // Предупреждающее сообщение

SELECT @@SERVERNAME AS 'Server Name' // SERVERNAME - функция, возвращающая информацию об имени локального сервера

ROLLBACK TRANSACTION // Откат транзакции

END

.3 T-SQL-определения хранимых процедур

1.      Повышение тарифной ставки в отделе

CREATE PROC BasicWageRateUp

@dept varchar(100)

UPDATE Отделы // Обновление таблицы «Отделы»

SET Тарифная_ставка = Тарифная_ставка * 1.9

WHERE Название_отдела = @dept // Выборка строк таблицы, где значение колонки «Название_отдела» соответствует введенному значению @dept

.        Информация о дне рождении по личному номеру сотрудника

CREATE PROC BDay

@id int Сотрудники.Личный_номер, Сотрудники.Название_отдела, Сотрудники.Фамилия, Сотрудники.Имя,

Сотрудники.Отчество, Сотрудники.Должность, День_рождения.Дата_рождения,

DATEDIFF(year,dbo.День_рождения.Дата_рождения,GETDATE()) AS 'Полных лет'

// DATEDIFF - функция, которая возвращает интервал времени year, прошедшего от указанной даты Дата_рождения до текущей даты, установленной на данном сервере, GETDATE - возвращает текущую дату, установленную на данном сервере

FROM День_рождения, Сотрудники

WHERE День_рождения.Личный_номер = @id AND День_рождения.Личный_номер = Сотрудники.Личный_номер

.        Просмотр командировки определенного сотрудника по личному номеру

CREATE PROC BusinessTrip

@id int Сотрудники.Личный_номер, Сотрудники.Название_отдела, Сотрудники.Фамилия, Сотрудники.Имя,

Сотрудники.Отчество, Сотрудники.Должность, Командировки.Номер_командировки, Командировки.Место,

Командировки.Дата_с, Командировки.Срок

FROM Командировки, Сотрудники

WHERE Командировки.Личный_номер = @id AND Командировки.Личный_номер = Сотрудники.Личный_номер

.        Поиск всех командировок в определенном городе

CREATE PROC City

@city varchar(100)

SELECT Сотрудники.Название_отдела, Сотрудники.Личный_номер, Сотрудники.Фамилия, Сотрудники.Имя,

Сотрудники.Отчество, Сотрудники.Должность,Сотрудники.Звание, Командировки.Номер_командировки,

Командировки.Дата_с

FROM Сотрудники, Командировки

WHERE Сотрудники.Личный_номер = Командировки.Личный_номер AND Командировки.Место = @city // Выборка строк таблицы, где значение колонки «Место» соответствует введенному значению @city

5.      Удаление данных о сотруднике из таблицы «Сотрудники»

CREATE PROC DeleteWorker

@id intEXISTS (SELECT * FROM Сотрудники WHERE Личный_номер = @id) Сотрудники // Проверка на наличие нужной строки в таблице

WHERE Личный_номер = @id // Выборка строк таблицы, где значение колонки «Личный_номер» соответствует введенному значению @id

6.      Поиск уволенного сотрудника по личному номеру

CREATE PROC Dismissal

@id int Сотрудники.Личный_номер, Сотрудники.Название_отдела, Сотрудники.Фамилия, Сотрудники.Имя,

Сотрудники.Отчество, Сотрудники.Должность, Сотрудники.Звание, Увольнения.Номер_документа,

Увольнения.Дата_документа, Увольнения.Причина