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

Тип работы:
Курсовая
Предмет:
Программирование


Узнать стоимость

Детальная информация о работе

Выдержка из работы

  • Содержание
  • 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 -- в дальнем зарубежье, в том числе во Франкфурте-на-Майне (Германия), Пекине (Китай), Сеуле (Южная Корея), Анталии (Турция) и Бангкоке (Таиланд).

S7 Airlines является лидером по внедрению современных технологий онлайн обслуживания пассажиров на российском рынке авиаперевозок. Авиакомпания предлагает пассажирам удобные услуги по выбору рейсов, бронированию билетов, их оплате и регистрации на рейс через сайт S7 Airlines в Интернете, мобильный телефон и Контактный центр. В январе 2011 года авиакомпания запустила услугу мобильного посадочного талона. В 2009 году S7 Airlines предложила пассажирам первый в России среди авиакомпаний мобильный сайт — s7. ru. В 2008 году S7 Airlines полностью перешла на использование технологии электронного билета (e-ticket)

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

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

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

Процесс оформления командировки показан на Рис. 1.

Рис. 1. Процесс оформления командировки

2. Цель и задачи создания базы данных

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

Задачи, решаемые проектируемой базой данных:

1. получение информации о сотрудниках, работающих в компании,

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

3. информация о том, в какие города была направлена командировка,

4. получение информации о предприятиях, с которыми сотрудничает компания,

5. контроль за вводом информации в таблицы,

6. получение информации о том, на какие задания был отправлен сотрудник в командировку,

7. получение информации о должностях и подразделениях, в которых работают сотрудники,

3. Концептуальная модель базы данных

4. Модель данных в SQL Server 2008

На рис. 2. показана модель данных.

Рис. 2 Модель данных в SQL Server-2008

5. Структура таблиц и данные в таблицах

Таблица «Сотрудник» (Employee)

Cоздание таблицы Employee (Сотрудник)

create table Employee (

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

Employee_full_name varchar (max) not null,

Date_of_birth date not null,

Date_of_acceptance_to_work date not null,

Post_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 (

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. Проект таблицы «Основание для командировки»

Рис. 6. Таблица «Основание для командировки»

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

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

create table Business_trip (

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

Employee_ID int references dbo. Employee (Employee_ID),

Org_payer_ID int references dbo. Org_payer (Org_payer_ID),

Org_insp_ID int references dbo. Org_insp (Org_insp_ID),

Report_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 (Город)

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. Таблица «Город»

Таблица «Страна» (Country)

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

create table Country (

Country_ID int identity primary key,

Country_NAME varchar (max) not null

);

Заполнение таблицы Country (Страна)

insert into Country values ('Россия'), ('Германия'), ('Польша');

Рис. 11. Проект таблицы «Страна»

Рис. 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. Таблица «Подразделение»

Таблица «Организация-приёмщик» (Org_insp)

Cоздание таблицы Organization_insp (Организация-приёмщик)

drop table Org_insp

create table Org_insp (

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

Org_insp_NAME varchar (max) not null,

Org_insp_INN bigint check (Org_insp_INN> 0) not null,

City_ID int references dbo. City (City_ID)

);

Привязка правила MyRul

exec sp_bindrule MyRul, 'Org_insp. Org_insp_INN'

--Заполнение таблицы Organization_insp (Организация-приёмщик)

insert into Org_insp values

('ООО «Большая медведица"', 135 489 781 245 ,'1'),

('ОАО «Практик"', 2 458 978 563 215 ,'1'),

('ООО «Ученье-свет!"', 568 945 712 895 ,'1');

Рис. 15. Проект таблицы «Организация-приёмщик»

Рис. 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. Проект таблицы «Огранизация-плательщик»

Рис. 18. Таблица «Огранизация-плательщик»

Таблица «Должность» (Post)

Cоздание таблицы Post (Должность)

create table Post (

Post_ID int identity primary key check (Post_ID> 0)

Post_NAME varchar (max) not null,

Department_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)

Cоздание таблицы Report (Документ-отчёт по командировкам)

create table Report (

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

Base_ID int references dbo. Base_for_trips (Base_ID),

Start_Date_SD date not null,

End_Date_SD date not null

Spent_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','13 000'),

('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

go

Cоздание правила

create rule MyRul

as @value> =100 000 000 000;

Привязка правил к таблицы происходит следующим образом:

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 Вывести название организации с ИНН 2 458 978 563 215?

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

select Org_insp_NAME

from dbo. Org_insp

WHERE Org_insp_INN ='2 458 978 563 215'

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

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

Процедура 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 введена сумма более 30 000, то программа выводит сообщение: «Расходы на командировку более положенного!!!»

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

USE My_DataBase

Go

Create trigger ChekSpentAmount

on dbo. Report

for insert, update, delete

AS

IF EXISTS (select * from dbo. Report where Spent_CASH > 30 000)

begin

PRINT 'Расходы на командировку более положенного!!!'

end

GO

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

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

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

USE My_DataBase

Go

Create trigger Chek_Age

on dbo. Employee

for insert, update, delete

AS

declare @curdate date

set @curdate = GetDate ()

IF EXISTS (select * from dbo. Employee where datediff (month, Date_of_birth, @curdate)/12 < 18)

begin

rollback

PRINT 'Вы не можете принять этого сотрудника на работу, ему менее 18 лет!!!'

end

GO

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

Список используемой литературы

база данные программный листинг

1. Официальный сайт компании S7 Airlines [электронный источник] http: //www. s7. ru/

2. Пашков П. М. Методические пособия

3. Петкович Д. Microsoft SQL Server 2008. Руководство для начинающих: Пер. с англ. — СПб.: БВХ-Петербург, 2009. — 752 с.

4. Дунаев В. В. Базы данных язык SQL — СПб.: БХВ Петербург, 2006. — 288с.

ПоказатьСвернуть
Заполнить форму текущей работой