Разработка базы данных служебных заданий для направления в командировку и отчетах об их выполнении
Содержание
1.
Описание предметной области
2.
Цель и задачи создания базы данных
3.
Концептуальная модель базы данных
4.
Модель данных в sql server 2008
5.
Структура таблиц и данные в таблицах
6.
Объекты базы данных
Список используемой литературы
1. Описание предметной области
Авиакомпанией является предприятие, которое
занимается авиаперевозкой пассажиров, грузов, багажа и почты пассажирскими,
регулярными и чартерными рейсами на внутренних и международных авиалиниях;
бронирование и продажа авиаперевозок других авиакомпаний.
S7 Airlines (бренд авиакомпании
"Сибирь") обладает крупнейшей в России сетью внутренних маршрутов,
выстроенной на базе трех крупных авиатранспортных узлов в Москве (Домодедово),
Новосибирске (Толмачёво) и Иркутске. S7 Airlines выполняет регулярные полеты в
страны СНГ, Европу, Ближний Восток, Южную Азию и страны Азиатско-Тихоокеанского
региона. S7 Airlines является участником глобального авиационного альянса
oneworld, мирового лидера по качеству обслуживания пассажиров. Авиакомпании oneworld
базируются на всех континентах и выполняют полеты в 141 страну мира. Входящие в
oneworld авиакомпании предлагают пассажирам гораздо больше услуг и преимуществ,
чем каждая из авиакомпаний в отдельности. Членство S7 Airlines в oneworld
подтверждает соответствие авиакомпании международным стандартам качества и
безопасности.
Филиалы и представительства авиакомпании S7
Airlines действуют в 37 городах России, 13 городах республик СНГ и 14 - в
дальнем зарубежье, в том числе во Франкфурте-на-Майне (Германия), Пекине
(Китай), Сеуле (Южная Корея), Анталии (Турция) и Бангкоке (Таиланд).Airlines
является лидером по внедрению современных технологий онлайн обслуживания
пассажиров на российском рынке авиаперевозок. Авиакомпания предлагает
пассажирам удобные услуги по выбору рейсов, бронированию билетов, их оплате и
регистрации на рейс через сайт S7 Airlines в Интернете, мобильный телефон и
Контактный центр. В январе 2011 года авиакомпания запустила услугу мобильного
посадочного талона. В 2009 году S7 Airlines предложила пассажирам первый в
России среди авиакомпаний мобильный сайт - s7.ru. В 2008 году S7 Airlines
полностью перешла на использование технологии электронного билета (e-ticket)
Так как компания занимается перевозкой грузов,
то она должна обеспечивать сохранность этих грузов. Одним из важных факторов
для этого, конечно, является безупречное техническое состояние авиатранспорта,
но так же важен тот факт сопровождения груза человеком, для этого сотруднику
нужно оформить командировку.
Так же, чтоб держаться на международном уровне,
руководство компании должно заботиться о том, чтобы сотрудники не только
накапливали знания, но и постоянно их пополняли, для этого, нужно оформлять
командировки в разные города, сотрудничать с различными предприятиями.
Причин для командировок может быть много, но
нужна специальная система, которая учитывала бы какой сотрудник, куда направлен
и с какими целями. Для этого нужно создать базу данных, которая будет
осуществлять и учёт сотрудников компании, и учёт предприятий, с которыми сотрудничает
авиакомпания, и учёт самих командировок.
Процесс оформления командировки показан на
Рис.1.
Рис. 1. Процесс оформления командировки
2. Цель и задачи создания базы данных
Целью создания базы данных для начальника отдела
управления персоналом, является учёт осуществления командировок в компании в
целом, о том какие сотрудники какие в какие командировки отправлялись, о
продолжительности командировок и о затратах на них.
Задачи, решаемые проектируемой базой данных:
. получение информации о сотрудниках,
работающих в компании,
. хранение информации о том, в каких командировках
побывал сотрудник,
. информация о том, в какие города была
направлена командировка,
. получение информации о предприятиях, с
которыми сотрудничает компания,
. контроль за вводом информации в
таблицы,
. получение информации о том, на какие
задания был отправлен сотрудник в командировку,
. получение информации о должностях и
подразделениях, в которых работают сотрудники,
3.
Концептуальная
модель базы данных
4. Модель данных в SQL Server 2008
На рис.2. показана модель данных.
Рис. 2 Модель данных в SQL
Server-2008
5. Структура таблиц и данные в
таблицах
Таблица "Сотрудник" (
Employee)
Cоздание таблицы Employee
(Сотрудник)
create table Employee (_ID int
identity primary key check (Employee_ID>0),_full_name varchar(max) not
null,_of_birth date not null,_of_acceptance_to_work date not null,_ID int
references dbo.Post(Post_ID)
);
Заполнение таблицы
Employee (Сотрудник)
insert into Employee values
('Харина С. И.', '1991-03-13', '2008-09-16',
'1'),
('Крыльцов Е. И.', '1988-05-04', '2001-03-23',
'13'),
('Савоськина А. А.', '1989-06-06', '2002-09-02',
'2'),
('Иванов А.К.', '0989-12-09', '2002-03-12',
'7'),
('Петров Р.А.', '1989-06-07', '2002-07-22',
'2'),
('Вадимова О.В.', '1989-12-11', '2003-09-25',
'8'),
('Кузнецова Е.А.', '1992-10-23', '2003-11-12',
'3'),
('Клепиков Д.А.', '1991-08-03', '2004-07-22',
'12'),
('Щеглов Р.А.', '1988-11-09', '2004-09-25',
'9'),
('Лакиза О.Л.', '1989-12-06', '2004-11-12',
'8'),
('Кулинкова А.Г.', '1989-12-09', '2005-07-22',
'8'),
('Шахматов Р.Г.', '1991-03-13', '2005-09-25',
'5'),
('Реунов П.М.', '1988-05-04', '2005-11-12',
'5'),
('Реунова М.П.', '1989-09-14', '2005-07-22',
'2'),
('Бибин П.Л.', '1989-12-18', '2006-09-25', '7'),
('Шоколадин Д.А.', '1991-12-13', '2006-11-12',
'5'),
('Герберт У.Т.', '1988-05-26', '2007-07-22',
'6'),
('Демьянова С.С.', '1989-09-09', '2007-09-25',
'11'),
('Олалошев В.В.', '1989-11-17', '2007-11-12',
'7'),
('Карпова Л.Б.', '1991-03-13', '2008-07-22',
'10'),
('Мельчукова Л.Д.', '1988-12-29', '2008-09-25',
'6'),
('Мельников П.М.', '1989-09-26', '2008-11-12',
'9');
Рис. 3 Проект таблицы "Сотрудник"
Рис. 4 Таблица "Сотрудник"
Таблица "Основание для
командировки" (Base_of_trips)
Cоздание таблицы Base_for_trips (Основание для
командировки)
create table Base_for_trips (_ID int
identity primary key check (Base_ID>0),_NAME varchar(max) not null
);
Заполнение таблицы Base_for_trips (Основание для
командировки)
insert into Base_for_trips values
('Повышение квалификации'), ('Заключение
сделки'), ('Налаживание партнёрских отношений'), ('Перевозка ценных бумаг'),
('Сопровождение ценных грузов');
Рис. 5. Проект таблицы "Основание для
командировки"
Рис. 6. Таблица "Основание для
командировки"
Таблица
"Командировка" (Business_trips)
Cоздание таблицы Business_trip (Командировка)
create table Business_trip (_trip
int identity primary key check (Business_trip>0),_ID int references
dbo.Employee (Employee_ID),_payer_ID int references dbo.Org_payer
(Org_payer_ID),_insp_ID int references dbo.Org_insp (Org_insp_ID),_ID int
references dbo.Report (Report_ID)
);
Заполнение таблицы Business_trip (Командировка)
insert into Business_trip values
('2','1','2','1'),
('18','1','2','2'), ('15','1','3','3'), ('4','2','1','4'), ('22','1','2','5'),
('11','1','3','6'), ('18','1','3','7'), ('4','3','1','8');
Рис. 7. Проект таблицы "Командировка"
Рис. 8. Командировка
Таблица "Город" (City)
Cоздание
таблицы
City (Город)
table City (_ID int identity primary
key check (City_ID>0),_NAME varchar(max) not null,_ID int references
dbo.Country (Country_ID)
);
Заполнение таблицы
City (Город)
into City values ('Варшава',
'3'), ('Москва',
'1'), ('Сочи',
'1'), ('Мюнхен',
'2'), ('Санкт-Петербург',
'1');
Рис. 9. Проект таблицы "Город"
Рис. 10. Таблица "Город"
Таблица "Страна" (Country)
Cоздание
таблицы
Country (Cтрана)
table Country (_ID int identity
primary key,_NAME varchar(max) not null
);
Заполнение таблицы
Country (Страна)
into Country values ('Россия'),
('Германия'),
('Польша');
Рис. 11. Проект таблицы "Страна"
Рис. 12. Таблица "Страна"
Таблица
"Подразделение" (Department)
Cоздание
таблицы
Department (Подразделение)
table Department (_ID int identity
primary key check (Department_ID>0),_NAME My_type not null
);
Заполнение таблицы
Department (Подразделение)
into Department
values ('Отдел безопасности'),('Подразделение по
работе с клиента'),
('Подразделение по техническому
обеспечению'),('Бухгалтерия'),('Отдел стратегического планирования'),
('Инвестиционный отдел'), ('Отдел по работе с персоналом'),('Отдел производства
проектов');
Рис. 13. Проект таблицы
"Подразделение"
Рис. 14. Таблица "Подразделение"
Таблица
"Организация-приёмщик" (Org_insp)
Cоздание таблицы Organization_insp
(Организация-приёмщик)
drop table Org_insptable Org_insp (_insp_ID
int identity primary key check (Org_insp_ID>0),_insp_NAME varchar(max) not
null,_insp_INN bigint check (Org_insp_INN>0) not null,_ID int references
dbo.City ( City_ID)
);
Привязка правила
MyRul
sp_bindrule MyRul,
'Org_insp.Org_insp_INN'
--Заполнение таблицы Organization_insp
(Организация-приёмщик)
insert into Org_insp values
('ООО
"Большая
медведица"',
135489781245 ,'1'),
('ОАО "Практик"', 2458978563215 ,'1'),
('ООО "Ученье-свет!"', 568945712895
,'1');
Рис. 15. Проект таблицы
"Организация-приёмщик"
Рис. 16. Таблица
"Организация-приёмщик"
Таблица
"Огранизация-плательщик" (Org-payer)
Cоздание таблицы Org_payer
(организация-плательщик)
create table Org_payer (_payer_ID
int identity primary key check (Org_payer_ID>0),_payer_NAME varchar(max) not
null,_ID int references dbo.City (City_ID)
);
-Заполнение таблицы Org_payer
(организация-плательщик)
insert into Org_payer values
('ООО
"S7 Airlines"', '1'), ('ООО
" Груз"',
'3'), ('ОАО
"Пилот"',
'4');
Рис. 17. Проект таблицы
"Огранизация-плательщик"
Рис. 18.Таблица
"Огранизация-плательщик"
Таблица "Должность" (Post)
Cоздание
таблицы
Post ( Должность)
table Post (_ID int identity primary
key check (Post_ID>0)_NAME varchar(max) not null,_ID int references
Department(Department_ID)
);
Заполнение таблицы
Post (Должность)
insert into Post values
('Главный
бухгалтер',
'4'),
('Бухгалтер', '4'),
('Системный аналитик', '6'),
('Консультант', '6'),
('Менеджер по разработки проектов', '8'),
('Начальник отдела безопасности', '1'),
('Охранник', '1'),
('Менеджер по работе с клиентами', '2'),
('Старший инженер', '3'),
('Инженер', '3'),
('Конструктор', '3'),
('Бизнес-аналитик', '5'),
('Менеджер по работе с персоналом', '7');
Рис. 19. Проект Таблицы "Должность"
Рис. 20. Таблица "Должность"
Таблица "Документ-отчёт
по командировкам" (Report)оздание
таблицы Report (Документ-отчёт по командировкам)
create table Report (_ID int
identity primary key check (Report_ID>0),_ID int references
dbo.Base_for_trips (Base_ID),_Date_SD date not null,_Date_SD date not null_CASH
smallmoney not null
);
Заполнение таблицы Report (Документ-отчёт по
командировкам)
insert into Report values
('1', '2011-10-19',
'2011-10-29','2000'),
('5', '2011-10-30', '2011-11-09',
'3000'),
('2', '2011-11-10',
'2011-11-20','5600'),
('5', '2011-12-01',
'2011-12-05','2300'),
('3', '2011-12-10',
'2011-12-12','1200'),
('2', '2012-01-10',
'2012-01-20','8900'),
('3', '2012-01-15',
'2011-01-17','13000'),
('4', '2011-12-31',
'2012-01-01','4000');
Рис. 21.Проект таблицы "Документ-отчёт по
командировкам"
Рис. 22. Таблица "Документ-отчёт по
командировкам"
6. Объекты
базы данных
Проверки
Для того, чтобы в базу данных вводилась
корректная информация, существуют так называемые "проверки" вводимой
информации. В базе данных было создано несколько проверок. Например:
Business_trip
int identity
primary key
check (Business_trip>0)
Этим кодом мы не только создаём столбец Business_trip
в таблице "Командировка", но ещё оговариваем, то, что это значение
всегда будет больше нуля: check
(Business_trip>0).
Такое условие прописано для каждой таблицы и для каждого столбца, который
является суррогатным ключом.
Так же подобная проверка была создана для
столбца, в котором хранится значение ИНН компании: Org_insp_INN
bigint check
(Org_insp_INN>0)
not null.
Эта проверка говорит о том, что значение ИНН не может быть отрицательным.
Правила
Правила - это объекты MS
SQL Server,
которые применяются для отдельных столбцов, для того, чтобы контролировать ввод
данных в этот столбец.
use My_DataBase
оздание правила
rule MyRul@value>=100000000000;
Привязка правил к таблицы происходит следующим
образом:
exec sp_bindrule MyRul,
'Org_insp.Org_insp_INN'
Запросы
Запрос 1: Кто из сотрудников родился в 1991
году?
Листинг программного кода:
use
My_DataBase
select
Employee_full_namedbo.EmployeeDate_of_birth like '1991%
Результат запроса:
Запрос 2: Вывести список сотрудников, фамилии
которых начинаются на К? Листинг программного
кода:
Employee_full_namedbo.EmployeeEmployee_full_name
like 'К%'
Результат запроса:
Запрос 3: Вывести документов по командировкам,
осуществлённым в октябре 2011года?
Листинг программного кода:
select
Report_ID
from dbo.ReportStart_Date_SD like
'2011-10%'
Результат запроса:
Запрос 4: Чтобы отправить сотрудка в
командировку на заключение сделки, каким кодом нужно воспользоваться?
Листинг программного кода:
select
Base_ID
from dbo.Base_for_tripsBase_NAME = 'заключение
сделки'
Результат запроса:
Запрос :5 Вывести ФИО и дату принятия на работу,
тех сотрудников, которые работают с 2005 года?
Листинг программного
кода:
Employee_full_name, Date_of_acceptance_to_workdbo.EmployeeDate_of_acceptance_to_work
like '2005-%'
Результат запроса:
Запрос :6 Чем занимается подразделение №5?
Листинг программного кода:
select Department_NAMEdbo.Department
WHERE Department_ID = '5'
Результат запроса:
Запрос 7 : Вывести название
организации-приёмщика и ИНН с кодом 2?
Листинг программного
кода:
Org_insp_NAME,
Org_insp_INNdbo.Org_inspOrg_insp_ID = '2'
Результат запроса:
Запрос :8 В какие компании сотрудники могут быть
?
Листинг программного кода:
select
Org_insp_NAME
from
dbo.Org_insp
Результат запроса:
Запрос :9 Какой код у г Москва ?
Листинг программного кода:
select
City_ID
from dbo.CityCity_NAME='Москва'
Результат запроса:
Запрос :10 Вывести название организации с ИНН
2458978563215?
Листинг программного кода:
select
Org_insp_NAME
from dbo.Org_inspOrg_insp_INN
='2458978563215'
Результат запроса:
Хранимые процедуры
Процедура 1: Формирует список клиентов,
родившихся в конкретную дату
Листинг программного кода:
My_DataBase
goprocedure Who @d
dateEmployee_full_name as 'Сотрудник'dbo.EmployeeDate_of_birth=@d
Вызов процедуры:
Вызвать всех сотрудников, родившихся 13.03.1991
Who '1991-03-13'
Результат выполнения процедуры:
Процедура 2: Подсчитывает сколько лет в компании
проработал сотрудник
Листинг программного
кода:
create procedure
ExperienceInTheCompany @r varchar(max)@curdate date@curdate =
GetDate()Employee_full_name as 'Сотрудник',
datediff(month,Date_of_acceptance_to_work , @curdate)/12 as 'Опыт
работы'dbo.EmployeeEmployee_full_name
= @r
Вызов процедуры:
ExperienceInTheCompany 'Харина
С.
И.'
Результат выполнения процедуры:
Процедура 3: Какую должность занимает в компании
Сотрудник
Листинг программного
кода:
procedure PositionHeld @y
varchar(max)Employee_full_name as 'Сотрудник',
Post_NAME as 'Должность'dbo.Post,dbo.Employeedbo.Employee.Post_ID=
dbo.Post.Post_IDEmployee_full_name = @y
Вызов процедуры:
exec PositionHeld 'Бибин
П.Л.'
Результат выполнения процедуры:
Процедура 4: Выводит список сотрудников, которые
были приняты в определённый год?
Листинг программного
кода:
procedure DateoAcceptanceForWork @y
DATEEmployee_full_name as 'Сотрудник',
Date_of_acceptance_to_work as 'Дата
принятия'dbo.EmployeeDate_of_acceptance_to_work
LIKE @y
Вызов процедуры:My_DataBaseDateoAcceptanceForWork
'2005%'
Результат выполнения процедуры:
Процедура 5: Даёт информацию какой стране
находится город?
Листинг программного кода:
use
My_DataBase
goprocedure whatCountryIsTheCity @y
varchar(max)Country_NAME as 'Страна',
City_NAME as 'Город'dbo.Country,
dbo.Citydbo.Country.Country_ID = dbo.City.Country_IDCity_NAME = @y
Вызов процедуры:
My_DataBasewhatCountryIsTheCity Мюнхен
Результат выполнения процедуры:
Процедура 6: Список номеров командировок,
совершённых в конкретный год и конкретный месяц?
Листинг программного кода:
use
My_DataBase
goprocedure ListOfAssigments @y
varchar(10)Business_trip 'Номер
командировки'dbo.Report,
dbo.Business_tripdbo.Report.Report_ID =
dbo.Business_trip.Report_IDStart_Date_SD like @y
Вызов процедуры:
My_DataBaseListOfAssigments
'2011-12-%'
Результат выполнения процедуры:
Процедура 7: Какой сотрудник был направлен в
командировку
Листинг программного кода:
use
My_DataBase
goprocedure WhatEmployee @y
intEmployee_full_name 'Сотрудник'dbo.Employee,
dbo.Business_tripdbo.Employee.Employee_ID=
dbo.Business_trip.Employee_IDBusiness_trip = @y
Вызов процедуры:
use My_DataBaseWhatEmployee 5
Результат выполнения процедуры:
Процедура 8: Подсчитывает сколько лет сейчас
сотруднику
Листинг программного
кода:
procedure HowManyYearsTheEmployee @d
int@curdate date@curdate = GetDate()Employee_full_name as 'Сотрудник',
datediff(month,Date_of_birth , @curdate)/12 as 'возрастт'dbo.EmployeeEmployee_ID
= @d
Вызов процедуры:
exec
HowManyYearsTheEmployee
6
Результат выполнения процедуры:
Процедура 9: Выдаёт основание по номеру
документа
Листинг программного кода:
use
My_DataBase
goprocedure BaseDocument @d
intReport_ID as 'Номер документа',
Base_NAME as 'Основание'dbo.Base_for_trips,
dbo.Reportdbo.Report.Base_ID=dbo.Base_for_trips.Base_IDReport_ID = @d
Вызов процедуры:
exec BaseDocument 6
Результат выполнения процедуры:
Процедура 10: Выдаёт информацию о том, в каком
городе юридически находится компания
Листинг программного кода:
use
My_DataBase
goprocedure InWhatCityIsTheCompany
@y varchar(max)Org_insp_NAME as 'Название
компании',
City_NAME as 'Город'dbo.City,
dbo.Org_inspdbo.City.City_ID= dbo.Org_insp.City_IDOrg_insp_NAME = @y
Вызов процедуры:
InWhatCityIsTheCompany 'ООО
"Большая
медведица"'
Результат выполнения процедуры:
Процедура 11: Выдаёт информацию о том, к какому
подразделению относится должность
Листинг программного кода:
use
My_DataBase
goprocedure
WhichDivisionIsThePosition @ref varchar(max)Post_NAME as 'Должность',Department_NAME
as 'Подразделение'dbo.Post,
dbo.Departmentdbo.Post.Department_ID=dbo.Department.Department_IDPost_NAME =
@ref
Вызов процедуры:
exec WhichDivisionIsThePosition 'Системный
аналитик'
Результат выполнения процедуры:
Процедура 12: Выдаёт список должностей в
подразделении
Листинг программного кода:
use
My_DataBase
goprocedure PositionsInTheUnit @ref
varchar(max)Post_NAME as 'Должность'dbo.Post,
dbo.Departmentdbo.Post.Department_ID=dbo.Department.Department_IDDepartment_NAME=
@ref
Вызов процедуры:
exec PositionsInTheUnit 'Подразделение по
техническому обеспечению'
Результат выполнения процедуры:
Листинг программного кода:
use My_DataBaseprocedure
HowManyPositionsInUni @ref varchar(max)COUNT(Post_ID) as 'Число
должностей'dbo.Post,
dbo.Departmentdbo.Post.Department_ID=dbo.Department.Department_IDDepartment_NAME
= @ref
Вызов процедуры:
exec HowManyPositionsInUni 'Подразделение по
техническому обеспечению'
Результат выполнения процедуры:
Процедура 14: Сколько в компании сотрудников
одной должности
Листинг программного кода:
use
My_DataBase
goPROCEDURE Employee_r @r
varchar(max)COUNT(Post_ID) as 'Число
должностей'dbo.PostPost_NAME
= @R
Вызов процедуры:
Employee_r 'Бизнес-аналитик'
Результат выполнения процедуры:
Процедура 15: Какой сотрудник был отправлен в
командировку
Листинг программного кода:
use
My_DataBase
goPROCEDURE Employee_open @r
intBusiness_trip as 'Командировка',
Employee_full_name as 'Сотрудник'dbo.Employee,
dbo.Business_tripdbo.Employee.Employee_ID=
dbo.Business_trip.Employee_IDBusiness_trip = @R
Вызов процедуры:
exec Employee_open 5
Результат выполнения процедуры:
Процедура 16: Какая организация оплачивала
командировку
Листинг программного кода:
use My_DataBasePROCEDURE Org_pa @r
intBusiness_trip as 'Командировка',
Org_payer_NAME as 'Компания'dbo.Org_payer
, dbo.Business_tripdbo.Org_payer.Org_payer_ID=Business_trip.Org_payer_IDBusiness_trip
= @R
Вызов процедуры:
exec Org_pa 2
Результат выполнения процедуры:
Процедура 17: В какую организацию был направлен
сотрудник по командировке
Листинг программного кода:
use
My_DataBase
goPROCEDURE Org_exep @r
intBusiness_trip as 'Командировка',
Employee_full_name as 'Сотрудник',
Org_insp_NAME as 'Организация'dbo.Org_insp
, dbo.Business_trip ,
dbo.Employeedbo.Org_insp.Org_insp_ID=Business_trip.Org_payer_IDdbo.Employee.Employee_ID
= dbo.Business_trip.Employee_IDBusiness_trip= @R
Вызов процедуры:
exec Org_exep 4
Результат выполнения процедуры:
Процедура 18: Список командировок,
осуществлённых в определённом году и месяце
Листинг программного кода:
use
My_DataBase
goPROCEDURE ListOfTrip @r
varchar(max)Business_trip as 'Командировка',
Start_Date_SD as 'Дата начала',End_Date_SD
as 'Дата
окончания'dbo.Report,
dbo.Business_tripdbo.Report.Report_ID =
dbo.Business_trip.Report_IDStart_Date_SD like @R
Вызов процедуры:
ListOfTrip '2011-12%'
Результат выполнения процедуры:
Процедура 19: Сколько денежных средств было
затрачено на командировки в определёном году?
Листинг программного кода:
My_DataBase
goPROCEDURE Many_Y @r
varchar(max)SUM(Spent_CASH)dbo.ReportStart_Date_SD like @R
Вызов процедуры:Many_Y '2011%'
Результат выполнения процедуры:
Процедура 20: Сколько денежных средств было
затрачено на отдельную командировку
Листинг программного кода:
use
My_DataBase
goPROCEDURE Many_trip @r
intBusiness_trip as 'Командировка',
Spent_CASH as 'Денежные
средства'dbo.Report,dbo.Business_tripdbo.Business_trip.Report_ID=dbo.Report.Report_IDBusiness_trip=
@R
Вызов процедуры:
exec Many_trip 5
Результат выполнения процедуры:
Процедура 21: Вывести ФИО и дату принятия на
работу, тех сотрудников, которые работатю с определённого года
Листинг программного кода:
use
My_DataBase
goPROCEDURE Empt @r
varchar(max)Employee_full_name as 'Сотрудник',
Date_of_acceptance_to_work as 'Дата
устройства
на
работу'dbo.EmployeeDate_of_acceptance_to_work
like @R
Вызов процедуры:
Empt '2005-%'
Результат выполнения процедуры:
Процедура 22: Выводит сотрудника и номер
командировки который был направлен на конкретное задание
Листинг программного кода:
My_DataBase
goPROCEDURE Empt_patrolq @r
varchar(max)Employee_full_name as 'Сотрудник',
Business_trip as 'Командировка'dbo.Employee,
dbo.Base_for_trips, dbo.Business_trip, dbo.Reportdbo.Employee.Employee_ID =
dbo.Business_trip.Employee_IDdbo.Business_trip.Report_ID=dbo.Report.Report_IDdbo.Report.Base_ID=dbo.Base_for_trips.Base_IDBase_NAME
= @r
Вызов процедуры:
exec Empt_patrolq 'Сопровождение ценных грузов'
Результат выполнения процедуры:
Процедура 23: Сколько список сотрудников
компании со стажем менее определённого количества лет
Листинг программного кода:
use
My_DataBase
goPROCEDURE Employee_i @r
int@curdate date@curdate = GetDate()Employee_full_name as 'Сотрудник',
datediff(month,Date_of_acceptance_to_work , @curdate)/12 as 'Сколько
лет
проработал'dbo.Employeedatediff(month,Date_of_acceptance_to_work
, @curdate)/12 < @r
Вызов процедуры:Employee_i
6
Результат выполнения процедуры:
Процедура 24: Выводит сотрудника который был
отправлен в определённую командировку, его должность и подразделение и задание
Листинг программного кода:
My_DataBase
goPROCEDURE Em_po @r
intBusiness_trip as 'Командировка',
Employee_full_name as 'Сотрудник',
Post_NAME AS 'Должность',
Department_NAME AS 'Подразделение'dbo.Employee,
dbo.Base_for_trips, dbo.Business_trip, dbo.Department,
dbo.Postdbo.Employee.Employee_ID =
dbo.Business_trip.Employee_IDdbo.Employee.Post_ID = dbo.Post.Post_IDdbo.Post.Department_ID
= dbo.Department.Department_IDBusiness_trip = @r
Вызов процедуры:
exec Em_po 6
Результат выполнения процедуры:
Процедура 25: Количество командировок
отправленных в определённую организацию
Листинг программного кода:
use
My_DataBase
goPROCEDURE Emty_porty @r
varchar(max)COUNT(dbo.Business_trip.Org_insp_ID)dbo.Business_trip,dbo.Org_inspdbo.Business_trip.Org_insp_ID
= dbo.Org_insp.Org_insp_IDOrg_insp_NAME= @rby dbo.Business_trip.Org_insp_ID
Вызов процедуры:
Emty_porty 'ОАО "Практик"'
Результат выполнения процедуры:
Процедура 26: Количество командировок оплаченных
определённой организацией
Листинг программного кода:
use
My_DataBase
goPROCEDURE Emty_akf @r
varchar(max)COUNT(dbo.Business_trip.Org_payer_ID)dbo.Business_trip,
dbo.Org_payerdbo.Business_trip.Org_payer_ID = dbo.Org_payer.Org_payer_IDOrg_payer_NAME
= @rby dbo.Business_trip.Org_payer_ID
Вызов процедуры:
Emty_akf 'ООО
"S7 Airlines"'
Результат выполнения процедуры:
Процедура 27: Выводит всех сотрудников, Фамилия
которых начинается на определённую букву их должность и подразделение
Листинг программного кода:
use
My_DataBase
goPROCEDURE Employee_p @r
varchar(max)Employee_full_name as 'Сотрудник',
Post_NAME as 'Должность',
Department_NAME as 'Подразделение'dbo.Employee,
dbo.Post, dbo.Departmentdbo.Employee.Post_ID =
dbo.Post.Post_IDdbo.Post.Department_ID=
dbo.Department.Department_IDEmployee_full_name like @r
Вызов процедуры:
Employee_p 'К%'
Результат выполнения процедуры:
Процедура 28: Выводит всех сотрудников, которые
работают в определённом отделе и их должности
Листинг программного
кода:
PROCEDURE Employee_post @r
varchar(max)Employee_full_name as 'Сотрудник',
Post_NAME as 'Должность'dbo.Employee,
dbo.Post, dbo.Departmentdbo.Employee.Post_ID =
dbo.Post.Post_IDdbo.Post.Department_ID=
dbo.Department.Department_IDDepartment_NAME = @r
Вызов процедуры:
exec Employee_post 'Инвестиционный отдел'
Результат выполнения процедуры:
Процедура 29: Сколько раз был отправлен в
командировки определённый сотрудник
Листинг программного кода:
use
My_DataBase
goPROCEDURE Employee_intt @r
varchar(MAX)COUNT(dbo.Business_trip.Employee_ID)dbo.Business_trip, dbo.Employeedbo.Employee.Employee_ID=dbo.Business_trip.Employee_IDEmployee_full_name
= @r
Вызов процедуры:
Employee_intt 'Иванов
А.К.'
Результат выполнения процедуры:
Процедура 30: сколько список сотрудников
компании со стажем более определённого количества лет
Листинг программного кода:
use
My_DataBase
goPROCEDURE Employee_i @r
int@curdate date@curdate = GetDate()Employee_full_name as 'Сотрудник',
datediff(month,Date_of_acceptance_to_work , @curdate)/12 as 'Сколько
лет
проработал'dbo.Employeedatediff(month,Date_of_acceptance_to_work
, @curdate)/12 > @r
Вызов процедуры:
Employee_i 6
Результат выполнения процедуры:
Представления
Представление 1: Выводит таблицу Employee и
заполняет в ней столбец Post_NAME из таблицы Post
Листинг программного
кода:
VIEW A_Employee asEmployee_ID,
Employee_full_name,Date_of_acceptance_to_work,Post_NAMEdbo.Employee,
dbo.Postdbo.Employee.Post_ID=dbo.Post.Post_ID
Результат выполнения:
Представление 2: Выводит таблицу Business_trip
, заполняя в ней все столбцы значениями.
Листинг программного кода:
use
My_DataBase
goVIEW AA_Business_trip
asBusiness_trip, Employee_full_name, Org_payer_NAME, Org_insp_NAMEdbo.Employee,
dbo.Org_insp, dbo.Org_payer, dbo.Business_tripdbo.Business_trip.Employee_ID =
dbo.Employee.Employee_IDdbo.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
goVIEW A_Report asReport_ID,
Base_NAME, Start_Date_SD, End_Date_SD, Spent_CASHdbo.Report, dbo.Base_for_tripsdbo.Report.Base_ID
= dbo.Base_for_trips.Base_ID
Результат выполнения:
Триггеры
Триггер 1: При добавлении в таблицу Employee
новой записи выводит сообщение: "В таблицу "Cотрудник" внесена
новая запись!"
Листинг программного кода:
My_DataBase
Gotrigger
Employee_insertdbo.Employee insert
AS'В таблицу
"Cотрудник" внесена новая запись!'
Результат выполнения:
Триггер 2: При заполнении таблицы Report,
если в столбец Spent_CASH
введена сумма более 30000, то программа выводит сообщение : "Расходы на
командировку более положенного!!!"
Листинг программного кода:
USE
My_DataBase
Gotrigger
ChekSpentAmountdbo.Reportinsert, update, deleteEXISTS (select * from dbo.Report
where Spent_CASH > 30000)
begin'Расходы на командировку более
положенного!!!'
Результат выполнения:
Триггер 3: При пополнении таблицы сотрудник,
проверяет на возраст нового сотрудника. И, если возраст менее 18 лет, то выдаёт
соответствующее сообщение: "Вы не можете принять этого сотрудника на
работу, ему менее 18 лет!!!"
Листинг программного кода:
USE
My_DataBase
Gotrigger Chek_Agedbo.Employeeinsert,
update, delete@curdate date@curdate = GetDate()EXISTS (select * from
dbo.Employee where datediff(month,Date_of_birth , @curdate)/12 < 18)
begin'Вы не можете принять этого сотрудника на
работу, ему менее 18 лет!!!'
Результат выполнения:
Список используемой литературы
база данные
программный листинг
1. Официальный сайт компании S7
Airlines [электронный источник] http://www.s7.ru/
. Пашков П. М. Методические
пособия
. Петкович
Д.
Microsoft SQL Server 2008. Руководство для начинающих:
Пер. с англ. - СПб.: БВХ-Петербург, 2009. - 752 с.
. Дунаев В.В. Базы данных
язык SQL - СПб.: БХВ
Петербург, 2006. - 288с.