Разработка базы данных служебных заданий для направления в командировку и отчетах об их выполнении

create table Base_for_trips (

Base_ID int identity primary key check (Base_ID>0),

Base_NAME varchar(max) not null

);

Заполнение таблицы Base_for_trips (Основание для командировки)

insert into Base_for_trips values

('Повышение квалификации'), ('Заключение сделки'), ('Налаживание партнёрских отношений'), ('Перевозка ценных бумаг'), ('Сопровождение ценных грузов');

Рис. 5. Проект таблицы "Основание для командировки"

Таблица "Командировка" (Business_trips)

Cоздание таблицы Business_trip (Командировка)

Таблица "Город" (City)

Cоздание таблицы City (Город)

create table City (

City_ID int identity primary key check (City_ID>0),

City_NAME varchar(max) not null,

Country_ID int references dbo.Country (Country_ID)

);

Заполнение таблицы City (Город)

insert into City values ('Варшава', '3'), ('Москва', '1'), ('Сочи', '1'), ('Мюнхен', '2'), ('Санкт-Петербург', '1');

Рис. 9. Проект таблицы "Город"

Рис. 10. Таблица "Город"

Рис. 12. Таблица "Страна"

Таблица "Подразделение" (Department)

Cоздание таблицы Department (Подразделение)

create table Department (

Department_ID int identity primary key check (Department_ID>0),

Department_NAME My_type not null

);

Заполнение таблицы Department (Подразделение)

insert into Department

values ('Отдел безопасности'),('Подразделение по работе с клиента'),

('Подразделение по техническому обеспечению'),('Бухгалтерия'),('Отдел стратегического планирования'), ('Инвестиционный отдел'), ('Отдел по работе с персоналом'),('Отдел производства проектов');

Рис. 13. Проект таблицы "Подразделение"

Рис. 14. Таблица "Подразделение"

Рис. 16. Таблица "Организация-приёмщик"

Таблица "Огранизация-плательщик" (Org-payer)

Cоздание таблицы Org_payer (организация-плательщик)

create table Org_payer (

Org_payer_ID int identity primary key check (Org_payer_ID>0),

Org_payer_NAME varchar(max) not null,

City_ID int references dbo.City (City_ID)

);

--Заполнение таблицы Org_payer (организация-плательщик)

insert into Org_payer values

('ООО "S7 Airlines"', '1'), ('ООО " Груз"', '3'), ('ОАО "Пилот"', '4');

Рис. 17. Проект таблицы "Огранизация-плательщик"

exec sp_bindrule MyRul, 'Org_insp.Org_insp_INN'

Запросы

Запрос 1: Кто из сотрудников родился в 1991 году?

Листинг программного кода:

use My_DataBase

select Employee_full_name

from dbo.Employee

WHERE Date_of_birth like '1991%

Результат запроса:

Запрос 2: Вывести список сотрудников, фамилии которых начинаются на К? Листинг программного кода:

select Employee_full_name

from dbo.Employee

WHERE Employee_full_name like 'К%'

Результат запроса:

Запрос 3: Вывести документов по командировкам, осуществлённым в октябре 2011года?

Листинг программного кода:

select Report_ID

from dbo.Report

WHERE Start_Date_SD like '2011-10%'

Результат запроса:

Запрос 4: Чтобы отправить сотрудка в командировку на заключение сделки, каким кодом нужно воспользоваться?

Листинг программного кода:

select Base_ID

from dbo.Base_for_trips

WHERE Base_NAME = 'заключение сделки'

Результат запроса:

Запрос :5 Вывести ФИО и дату принятия на работу, тех сотрудников, которые работают с 2005 года?

Листинг программного кода:

select Employee_full_name, Date_of_acceptance_to_work

from dbo.Employee

WHERE Date_of_acceptance_to_work like '2005-%'

Результат запроса:

Запрос :6 Чем занимается подразделение №5?

Листинг программного кода:

select Department_NAME

from dbo.Department

WHERE Department_ID = '5'

Результат запроса:

Запрос 7 : Вывести название организации-приёмщика и ИНН с кодом 2?

Листинг программного кода:

select Org_insp_NAME, Org_insp_INN

from dbo.Org_insp

WHERE Org_insp_ID = '2'

Результат запроса:

Запрос :8 В какие компании сотрудники могут быть ?

Листинг программного кода:

select Org_insp_NAME

from dbo.Org_insp

Результат запроса:

Запрос :9 Какой код у г Москва ?

Листинг программного кода:

select City_ID

from dbo.City

WHERE City_NAME='Москва'

Результат запроса:

Запрос :10 Вывести название организации с ИНН 2458978563215?

Листинг программного кода:

select Org_insp_NAME

from dbo.Org_insp

WHERE Org_insp_INN ='2458978563215'

Результат запроса:

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

Процедура 1: Формирует список клиентов, родившихся в конкретную дату

Листинг программного кода:

use My_DataBase

go

create procedure Who @d date

as

select Employee_full_name as 'Сотрудник'

from dbo.Employee

WHERE Date_of_birth=@d

Вызов процедуры:

Вызвать всех сотрудников, родившихся 13.03.1991

exec Who '1991-03-13'

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

Процедура 2: Подсчитывает сколько лет в компании проработал сотрудник

Листинг программного кода:

create procedure ExperienceInTheCompany @r varchar(max)

as

declare @curdate date

set @curdate = GetDate()

select Employee_full_name as 'Сотрудник', datediff(month,Date_of_acceptance_to_work , @curdate)/12 as 'Опыт работы'

from dbo.Employee

where Employee_full_name = @r

Вызов процедуры:

exec ExperienceInTheCompany 'Харина С. И.'

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

Процедура 3: Какую должность занимает в компании Сотрудник

Листинг программного кода:

create procedure PositionHeld @y varchar(max)

as

select Employee_full_name as 'Сотрудник', Post_NAME as 'Должность'

from dbo.Post,dbo.Employee

where dbo.Employee.Post_ID= dbo.Post.Post_ID

and Employee_full_name = @y

Вызов процедуры:

exec PositionHeld 'Бибин П.Л.'

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

Процедура 4: Выводит список сотрудников, которые были приняты в определённый год?

Листинг программного кода:

create procedure DateoAcceptanceForWork @y DATE

as

select Employee_full_name as 'Сотрудник', Date_of_acceptance_to_work as 'Дата принятия'

from dbo.Employee

where Date_of_acceptance_to_work LIKE @y

Вызов процедуры:

use My_DataBase

exec DateoAcceptanceForWork '2005%'

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

Процедура 5: Даёт информацию какой стране находится город?

Листинг программного кода:

use My_DataBase

go

create procedure whatCountryIsTheCity @y varchar(max)

as

select Country_NAME as 'Страна', City_NAME as 'Город'

from dbo.Country, dbo.City

where dbo.Country.Country_ID = dbo.City.Country_ID

and City_NAME = @y

Вызов процедуры:

use My_DataBase

exec whatCountryIsTheCity Мюнхен

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

Процедура 6: Список номеров командировок, совершённых в конкретный год и конкретный месяц?

Листинг программного кода:

use My_DataBase

go

create procedure ListOfAssigments @y varchar(10)

as

select Business_trip 'Номер командировки'

from dbo.Report, dbo.Business_trip

where dbo.Report.Report_ID = dbo.Business_trip.Report_ID

and Start_Date_SD like @y

Вызов процедуры:

use My_DataBase

exec ListOfAssigments '2011-12-%'

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

Процедура 7: Какой сотрудник был направлен в командировку

Листинг программного кода:

use My_DataBase

go

create procedure WhatEmployee @y int

as

select Employee_full_name 'Сотрудник'

from dbo.Employee, dbo.Business_trip

where dbo.Employee.Employee_ID= dbo.Business_trip.Employee_ID

and Business_trip = @y

Вызов процедуры:

use My_DataBase

exec WhatEmployee 5

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

Процедура 8: Подсчитывает сколько лет сейчас сотруднику

Листинг программного кода:

create procedure HowManyYearsTheEmployee @d int

as

declare @curdate date

set @curdate = GetDate()

select Employee_full_name as 'Сотрудник', datediff(month,Date_of_birth , @curdate)/12 as 'возрастт'

from dbo.Employee

where Employee_ID = @d

Вызов процедуры:

exec HowManyYearsTheEmployee 6

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

Процедура 9: Выдаёт основание по номеру документа

Листинг программного кода:

use My_DataBase

go

create procedure BaseDocument @d int

as

select Report_ID as 'Номер документа', Base_NAME as 'Основание'

from dbo.Base_for_trips, dbo.Report

where dbo.Report.Base_ID=dbo.Base_for_trips.Base_ID

and Report_ID = @d

Вызов процедуры:

exec BaseDocument 6

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

Процедура 10: Выдаёт информацию о том, в каком городе юридически находится компания

Листинг программного кода:

use My_DataBase

go

create procedure InWhatCityIsTheCompany @y varchar(max)

as

select Org_insp_NAME as 'Название компании', City_NAME as 'Город'

from dbo.City, dbo.Org_insp

WHERE dbo.City.City_ID= dbo.Org_insp.City_ID

and Org_insp_NAME = @y

Вызов процедуры:

exec InWhatCityIsTheCompany 'ООО "Большая медведица"'

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

Процедура 11: Выдаёт информацию о том, к какому подразделению относится должность

Листинг программного кода:

use My_DataBase

go

create procedure WhichDivisionIsThePosition @ref varchar(max)

as

select Post_NAME as 'Должность',Department_NAME as 'Подразделение'

from dbo.Post, dbo.Department

WHERE dbo.Post.Department_ID=dbo.Department.Department_ID

and Post_NAME = @ref

Вызов процедуры:

exec WhichDivisionIsThePosition 'Системный аналитик'

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

Процедура 12: Выдаёт список должностей в подразделении

Листинг программного кода:

use My_DataBase

go

create procedure PositionsInTheUnit @ref varchar(max)

as

select Post_NAME as 'Должность'

from dbo.Post, dbo.Department

WHERE dbo.Post.Department_ID=dbo.Department.Department_ID

and Department_NAME= @ref

Вызов процедуры:

exec PositionsInTheUnit 'Подразделение по техническому обеспечению'

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

Процедура 13: Сколько должностей в подразделении

Листинг программного кода:

use My_DataBase

go

create procedure HowManyPositionsInUni @ref varchar(max)

as

select COUNT(Post_ID) as 'Число должностей'

from dbo.Post, dbo.Department

WHERE dbo.Post.Department_ID=dbo.Department.Department_ID

and Department_NAME = @ref

Вызов процедуры:

exec HowManyPositionsInUni 'Подразделение по техническому обеспечению'

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

Процедура 14: Сколько в компании сотрудников одной должности

Листинг программного кода:

use My_DataBase

go

CREATE PROCEDURE Employee_r @r varchar(max)

as

select COUNT(Post_ID) as 'Число должностей'

from dbo.Post

WHERE Post_NAME = @R

Вызов процедуры:

exec Employee_r 'Бизнес-аналитик'

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

Процедура 15: Какой сотрудник был отправлен в командировку

Листинг программного кода:

use My_DataBase

go

CREATE PROCEDURE Employee_open @r int

as

select Business_trip as 'Командировка', Employee_full_name as 'Сотрудник'

from dbo.Employee, dbo.Business_trip

WHERE dbo.Employee.Employee_ID= dbo.Business_trip.Employee_ID

and Business_trip = @R

Вызов процедуры:

exec Employee_open 5

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

Процедура 16: Какая организация оплачивала командировку

Листинг программного кода:

use My_DataBase

go

CREATE PROCEDURE Org_pa @r int

as

select Business_trip as 'Командировка', Org_payer_NAME as 'Компания'

from dbo.Org_payer , dbo.Business_trip

WHERE dbo.Org_payer.Org_payer_ID=Business_trip.Org_payer_ID

and Business_trip = @R

Вызов процедуры:

exec Org_pa 2

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

Процедура 17: В какую организацию был направлен сотрудник по командировке

Листинг программного кода:

use My_DataBase

go

CREATE PROCEDURE Org_exep @r int

as

select Business_trip as 'Командировка', Employee_full_name as 'Сотрудник', Org_insp_NAME as 'Организация'

from dbo.Org_insp , dbo.Business_trip , dbo.Employee

WHERE dbo.Org_insp.Org_insp_ID=Business_trip.Org_payer_ID

and dbo.Employee.Employee_ID = dbo.Business_trip.Employee_ID

and Business_trip= @R

Вызов процедуры:

exec Org_exep 4

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

Процедура 18: Список командировок, осуществлённых в определённом году и месяце

Листинг программного кода:

use My_DataBase

go

CREATE PROCEDURE ListOfTrip @r varchar(max)

as

select Business_trip as 'Командировка', Start_Date_SD as 'Дата начала',End_Date_SD as 'Дата окончания'

from dbo.Report, dbo.Business_trip

WHERE dbo.Report.Report_ID = dbo.Business_trip.Report_ID

and Start_Date_SD like @R

Вызов процедуры:

exec ListOfTrip '2011-12%'

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

Процедура 19: Сколько денежных средств было затрачено на командировки в определёном году?

Листинг программного кода:

use My_DataBase

go

CREATE PROCEDURE Many_Y @r varchar(max)

as

select SUM(Spent_CASH)

from dbo.Report

where Start_Date_SD like @R

Вызов процедуры:

exec Many_Y '2011%'

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

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

Листинг программного кода:

use My_DataBase

go

CREATE PROCEDURE Many_trip @r int

as

select Business_trip as 'Командировка', Spent_CASH as 'Денежные средства'

from dbo.Report,dbo.Business_trip

where dbo.Business_trip.Report_ID=dbo.Report.Report_ID

and Business_trip= @R

Вызов процедуры:

exec Many_trip 5

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

Процедура 21: Вывести ФИО и дату принятия на работу, тех сотрудников, которые работатю с определённого года

Листинг программного кода:

use My_DataBase

go

CREATE PROCEDURE Empt @r varchar(max)

as

select Employee_full_name as 'Сотрудник', Date_of_acceptance_to_work as 'Дата устройства на работу'

from dbo.Employee

WHERE Date_of_acceptance_to_work like @R

Вызов процедуры:

exec Empt '2005-%'

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

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

Листинг программного кода:

use My_DataBase

go

CREATE PROCEDURE Empt_patrolq @r varchar(max)

as

select Employee_full_name as 'Сотрудник', Business_trip as 'Командировка'

from dbo.Employee, dbo.Base_for_trips, dbo.Business_trip, dbo.Report

WHERE dbo.Employee.Employee_ID = dbo.Business_trip.Employee_ID

and dbo.Business_trip.Report_ID=dbo.Report.Report_ID

and dbo.Report.Base_ID=dbo.Base_for_trips.Base_ID

and Base_NAME = @r

Вызов процедуры:

exec Empt_patrolq 'Сопровождение ценных грузов'

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

Процедура 23: Сколько список сотрудников компании со стажем менее определённого количества лет

Листинг программного кода:

use My_DataBase

go

CREATE PROCEDURE Employee_i @r int

as

declare @curdate date

set @curdate = GetDate()

select Employee_full_name as 'Сотрудник', datediff(month,Date_of_acceptance_to_work , @curdate)/12 as 'Сколько лет проработал'

from dbo.Employee

where datediff(month,Date_of_acceptance_to_work , @curdate)/12 < @r

Вызов процедуры:

exec Employee_i 6

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

Процедура 24: Выводит сотрудника который был отправлен в определённую командировку, его должность и подразделение и задание

Листинг программного кода:

use My_DataBase

go

CREATE PROCEDURE Em_po @r int

as

select Business_trip as 'Командировка', Employee_full_name as 'Сотрудник', Post_NAME AS 'Должность', Department_NAME AS 'Подразделение'

from dbo.Employee, dbo.Base_for_trips, dbo.Business_trip, dbo.Department, dbo.Post

WHERE dbo.Employee.Employee_ID = dbo.Business_trip.Employee_ID

and dbo.Employee.Post_ID = dbo.Post.Post_ID

and dbo.Post.Department_ID = dbo.Department.Department_ID

and Business_trip = @r

Вызов процедуры:

exec Em_po 6

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

Процедура 25: Количество командировок отправленных в определённую организацию

Листинг программного кода:

use My_DataBase

go

CREATE PROCEDURE Emty_porty @r varchar(max)

as

select COUNT(dbo.Business_trip.Org_insp_ID)

from dbo.Business_trip,dbo.Org_insp

where dbo.Business_trip.Org_insp_ID = dbo.Org_insp.Org_insp_ID

and Org_insp_NAME= @r

group by dbo.Business_trip.Org_insp_ID

Вызов процедуры:

exec Emty_porty 'ОАО "Практик"'

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

Процедура 26: Количество командировок оплаченных определённой организацией

Листинг программного кода:

use My_DataBase

go

CREATE PROCEDURE Emty_akf @r varchar(max)

as

select COUNT(dbo.Business_trip.Org_payer_ID)

from dbo.Business_trip, dbo.Org_payer

where dbo.Business_trip.Org_payer_ID = dbo.Org_payer.Org_payer_ID

and Org_payer_NAME = @r

group by dbo.Business_trip.Org_payer_ID

Вызов процедуры:

exec Emty_akf 'ООО "S7 Airlines"'

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

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

Листинг программного кода:

use My_DataBase

go

CREATE PROCEDURE Employee_p @r varchar(max)

as

select Employee_full_name as 'Сотрудник', Post_NAME as 'Должность', Department_NAME as 'Подразделение'

from dbo.Employee, dbo.Post, dbo.Department

where dbo.Employee.Post_ID = dbo.Post.Post_ID

and dbo.Post.Department_ID= dbo.Department.Department_ID

and Employee_full_name like @r

Вызов процедуры:

exec Employee_p 'К%'

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

Процедура 28: Выводит всех сотрудников, которые работают в определённом отделе и их должности

Листинг программного кода:

CREATE PROCEDURE Employee_post @r varchar(max)

as

select Employee_full_name as 'Сотрудник', Post_NAME as 'Должность'

from dbo.Employee, dbo.Post, dbo.Department

where dbo.Employee.Post_ID = dbo.Post.Post_ID

and dbo.Post.Department_ID= dbo.Department.Department_ID

and Department_NAME = @r

Вызов процедуры:

exec Employee_post 'Инвестиционный отдел'

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

Процедура 29: Сколько раз был отправлен в командировки определённый сотрудник

Листинг программного кода:

use My_DataBase

go

CREATE PROCEDURE Employee_intt @r varchar(MAX)

as

select COUNT(dbo.Business_trip.Employee_ID)

from dbo.Business_trip, dbo.Employee

where dbo.Employee.Employee_ID=dbo.Business_trip.Employee_ID

and Employee_full_name = @r

Вызов процедуры:

exec Employee_intt 'Иванов А.К.'

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

Процедура 30: сколько список сотрудников компании со стажем более определённого количества лет

Листинг программного кода:

use My_DataBase

go

CREATE PROCEDURE Employee_i @r int

as

declare @curdate date

set @curdate = GetDate()

select Employee_full_name as 'Сотрудник', datediff(month,Date_of_acceptance_to_work , @curdate)/12 as 'Сколько лет проработал'

from dbo.Employee

where datediff(month,Date_of_acceptance_to_work , @curdate)/12 > @r

Вызов процедуры:

exec Employee_i 6

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

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

Представление 1: Выводит таблицу Employee и заполняет в ней столбец Post_NAME из таблицы Post

Листинг программного кода:

go

create VIEW A_Employee as

select Employee_ID, Employee_full_name,Date_of_acceptance_to_work,Post_NAME

from dbo.Employee, dbo.Post

where dbo.Employee.Post_ID=dbo.Post.Post_ID

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

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

Листинг программного кода:

use My_DataBase

go

create VIEW AA_Business_trip as

select Business_trip, Employee_full_name, Org_payer_NAME, Org_insp_NAME

from dbo.Employee, dbo.Org_insp, dbo.Org_payer, dbo.Business_trip

where dbo.Business_trip.Employee_ID = dbo.Employee.Employee_ID

and dbo.Org_insp.Org_insp_ID = dbo.Business_trip.Org_insp_ID and dbo.Org_payer.Org_payer_ID = dbo.Business_trip.Org_payer_ID

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

Представление 3: Выводит таблицу Report, заполняя в ней столбец Base_NAME.

Листинг программного кода:

use My_DataBase

go

create VIEW A_Report as

select Report_ID, Base_NAME, Start_Date_SD, End_Date_SD, Spent_CASH

from dbo.Report, dbo.Base_for_trips

where dbo.Report.Base_ID = dbo.Base_for_trips.Base_ID

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

Триггеры

Триггер 1: При добавлении в таблицу Employee новой записи выводит сообщение: "В таблицу "Cотрудник" внесена новая запись!"

USE My_DataBase

Go

Create trigger Employee_insert

on dbo.Employee

for insert

AS

PRINT 'В таблицу "Cотрудник" внесена новая запись!'

GO

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

Триггер 2: При заполнении таблицы Report, если в столбец Spent_CASH введена сумма более 30000, то программа выводит сообщение : "Расходы на командировку более положенного!!!"

USE My_DataBase

Триггер 3: При пополнении таблицы сотрудник, проверяет на возраст нового сотрудника. И, если возраст менее 18 лет, то выдаёт соответствующее сообщение: "Вы не можете принять этого сотрудника на работу, ему менее 18 лет!!!"

Листинг программного кода:

USE My_DataBase