Создание автоматизированной информационной системы кадрового учета

• Введение
• 1 Техническое задание
• 1.1 Анализ предметной области
• 1.2 Постановка задачи
• 2 Технический проект информационной системы
• 2.1 Функциональная модель
• 2.1.1 Контекстная диаграмма и диаграммы детализации процессов
• 2.1.2 Диаграмма дерева узлов
• 2.2 Информационная модель
• 2.2.1 Идентификация сущностей и связей. ER-диаграмма логического уровня
• 2.2.2 ER-диаграмма физического уровня. Ограничения доменов. Ограничения ссылочной целостности. Переопределение триггеров. Индексирование отношений
• 2.2.3 Определение представлений, хранимых процедур серверной компоненты. ER-диаграмма в режиме отображения представлений
• 2.3 Верификация спроектированной логической модели
• 3 Реализация системы
• 3.1 T-SQL-определения регламентированных запросов
• 3.2 T-SQL-определения триггеров
• 3.3 T-SQL-определения хранимых процедур
• 3.4 T-SQL-определения курсоров
• 3.5 Распределение прав доступа
• 4 Результат тестирования информационной системы
• Заключение
• Список использованных литературных источников
• Введение
• Создание современных электронных вычислительных машин позволило автоматизировать обработку данных во многих сферах человеческой деятельности. Без современных систем обработки данных трудно представить сегодня передовые производственные технологии, управление экономикой на всех ее уровнях, научные исследования, образование, издательское дело, функционирование средств массовой информации, проведение крупных спортивных состязаний. Значительно расширило сферу применения систем обработки данных появление персональных компьютеров.
• Одним из наиболее распространенных классов систем обработки данных являются информационные системы. Хотя на уровне здравого смысла назначение таких систем понятно каждому, для серьезного обсуждения технологий современных информационных систем необходимо более четко определить, в чем заключаются их специфические особенности, чем они отличаются от других систем обработки данных, какие функции они могут выполнять, какими ресурсами они располагают.
• Любой разумный вид деятельности основывается на информации о свойствах состояния и поведения той части реального мира, с которой связана эта деятельность. Для получения такой информации во многих случаях необходимо регулярно через некоторые интервалы времени проводить натурные измерения (или наблюдения), позволяющие определять характеристики состояния сущностей реального мира и протекающих процессов, соответствующие моментам времени, когда эти измерения производятся.
• В других случаях удается воспользоваться «материализованной» информацией, содержащейся в различного рода бумажных документах, отчетах или публикациях, которые также выступают как часть реальности. Требуемую информацию можно извлечь из них путем своего рода «наблюдения».
• Однако некоторые натурные измерения или наблюдения могут оказаться неосуществимыми в отведенное для них время в связи с большой трудоемкостью, высокой стоимостью, недоступностью объекта измерения (наблюдения) и по другим причинам.
• Значительно сократить объем необходимых натурных измерений позволяет компьютерное моделирование реальности. Если компьютерная модель адекватно (относительно информационных потребностей пользователей) отражает состояние и динамику реальности, то многие необходимые сведения можно получать с помощью такой модели, избегая тем самым натурных измерений, с существенно меньшими затратами времени, а возможно, и при более низкой стоимости. Именно для поддержки таких моделей служит специальный класс систем обработки данных — автоматизированные информационные системы.
• Автоматизированной информационной системой называется комплекс, включающий вычислительное и коммуникационное оборудование, программное обеспечение, лингвистические средства и информационные ресурсы, а также системный персонал и обеспечивающий поддержку динамической информационной модели некоторой части реального мира для удовлетворения информационных потребностей пользователей.
• Поскольку модель предметной области, поддерживаемая информационной системой, материализуется в форме организованных необходимым образом информационных ресурсов, она называется информационной моделью.
• Информационные системы уже многие десятки и даже сотни лет существуют и используются на практике в форме различного рода картотек и/или коллекций бумажных документов. Однако в таких системах отсутствует какая-либо автоматизация обработки данных. Они позволяют лишь регистрировать и поддерживать в систематизированной форме на бумажных носителях результаты произведенных натурных измерений.
• Актуальность данной работы обусловлена теоретической и практической значимостью проблем, связанных с реализацией кадровой политики МВД в сфере подготовки кадров, как важнейшей гарантии укрепления законности и правопорядка, столь необходимых для формирования правового государства.
• Однако, несмотря на высокий научный уровень исследований управленческих отношений и вопросов кадровой политики, слабо изученными остаются проблемы кадрового обеспечения ОВД и их взаимодействие в формировании и реализации целостной системы кадровой политики.
• Все это в совокупности и обусловило определение цели, задач, объекта и предмета данной работы.
• Целью данного курсового проекта является создание автоматизированной информационной системы заданной предметной области? кадровый учет, определить состояние кадрового планирования в РОВД и специфику органов управления по формированию и реализации кадровой политики как целостной системы, выявить особенности управления персоналом в органах власти.
• 1 Техническое задание

1.1 Анализ предметной области

Объект автоматизации — РОВД г. Климовичи.

Документы, на основании которых создается система:

? Положение о прохождении службы в органах внутренних дел Республики Беларусь;

? Закон об ОВД РБ;

? Должностные инструкции;

? Трудовое законодательство;

? Методические материалы по ведению документации по учету и движению персонала.

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

Программа позволяет хранить всю информацию о сотрудниках РОВД.

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

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

Отдел кадров выполняет следующие функции.

? Учёт личного состава РОВД;

? Обработка и хранение данных о сотрудниках;

? Планирование структуры РОВД и кадровой политики;

? Произведение оперативного учёта движения кадров;

? Ведение кадрового делопроизводства;

? Осуществление контроля над своевременным исполнением распоряжений, приказов и поручений начальника РОВД;

? Оформление приема, перевода и увольнения сотрудников в соответствии с трудовым законодательством, положениями и приказами начальника РОВД;

? Формирование и ведение личных дел работников, внесение изменений, связанных с трудовой деятельностью;

? Заполнение, учёт и хранение трудовых книжек, произведение подсчёта трудового стажа;

? Произведение записи в трудовых книжках о поощрениях и награждениях сотрудников;

? Ведение учёта предоставления отпусков работникам, осуществление контроля над составлением и соблюдением графиков очередных отпусков;

? Ведение учёта нарушений трудовой дисциплины и контролирование своевременности принятия соответствующих мер;

? Выполнение других служебных поручений начальника РОВД.

1.2 Постановка задачи

Данная ИС разрабатывается с расчётом на одного пользователя — заместителя начальника РОВД по работе личным составом. При работе с системой кадровик должен решать следующие задачи:

? Получать доступ к данным таблицы, в которых должна содержаться вся необходимая информация;

? Просматривать данные таблицы, при необходимости редактировать их;

? Создавать на основе исходных данных личные дела сотрудников, отчёты и приказы.

Таким образом, основные процессы, которые должна реализовывать система:

1) Ведение личного дела сотрудника, включающего служебные данные и практически любые дополнительные сведения. Контроль над оформлением и хранением личных дел возлагается на специалиста по кадрам. До внедрения системы в отдел кадров РОВД документы личных дел формировались в специальные папки. В них включались документы, фиксирующие процесс поступления на работу, а затем все документы, возникающие в период работы, в хронологической последовательности. До использования автоматизированной системы формирование и ведение личного дела сотрудника занимало значительное время. Также для хранения этих дел должна была отводиться дополнительная площадь. Система может хранить данные о сотруднике сколь угодно долго, исключает ошибку при нумерации личных дел, исключает потерю данных о сотрудниках, даёт возможность быстрого поиска необходимой информации. Следовательно, благодаря автоматизированной системе, сокращается объем бумажного документооборота и время на работу с информацией о сотрудниках, данные о любом сотруднике можно получить путем запросов.

2) Предоставление сведений о штатной расстановке. Штатная расстановка применяется для оформления организационной структуры и штатного состава. Чтобы сформировать штатную расстановку, специалисту по кадрам приходится пересматривать и проверять большой объём документов. Это отнимает много времени. При помощи автоматизированной системы, кадровику будет быстро предоставлена необходимая информация: фамилия, имя и отчество сотрудников, номера и даты приказа о приёме на работу/должность, наименования и перечень структурных единиц, наименования должностей и т. д., собранная в одном месте. Это значительно облегчит и ускорит процесс формирования штатной расстановки.

3) Формирование списков личного состава РОВД. При составлении списков личного состава РОВД необходимо сортировать личные дела сотрудников по различным критериям (по половой принадлежности, возрасту, стажу, работы в ОВД, образовании, должности, звании и т. д.). Вручную это займёт достаточно долгое время, к тому же велика вероятность ошибки, например, пропустить личное дело какого-либо сотрудника милиции. Автоматизированная система осуществит обработку и формирование личного состава за очень короткое время.

4) Выведение на печать личного дела сотрудника. Если начальству РОВД необходимо личное дело какого-либо сотрудника, это легко осуществимо в один клик мыши. До использования автоматизированной системы нужно было обращаться в архив и искать дело по личному номеру (который до этого необходимо найти в специальном журнале), что долго и затруднительно. Такая функция системы, как выведение на печать личного дела сотрудника, облегчает работу специалиста по кадрам.

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

6) Поиск сотрудников по выбранному критерию. Без использования автоматизированной системы поиск производится вручную, отнимает очень много рабочего времени специалиста по кадрам. Автоматизированная система даёт возможность осуществить быстрый и удобный поиск сотрудников по определённому критерию, например, по должности или прохождению службы в Вооружённых силах РБ.

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

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

9) Поиск сотрудника милиции по личному номеру. Без автоматизированной системы поиск информации о нужном сотруднике милиции занимает значительное время, т.к. специалисту по кадрам приходиться просматривать много документов (личных дел сотрудников), чтобы найти нужный номер. Кроме того, из-за ошибок во время оформления и хранения этих личных дел, некоторые документы могут быть утеряны, а восстановление этих документов — достаточно трудоемкий процесс. Автоматизированная система хранит эту информацию сколь угодно долго, поиск осуществляется за считанные секунды, достаточно лишь вбить нужные цифры в строку поиска, исключены ошибки и нет необходимости в дополнительных папках (а, следовательно, выделение площади под эти папки) для хранения документов и журналов с учётом личных номеров сотрудников.

10) Экспорт в MS Excel. Благодаря автоматизированной системе экспорт в excel осуществляется в один клик мыши. После того как данные перенесены в еxcel, их можно анализировать самыми разными методами. Можно применить автофильтр, чтобы оставить только те данные, которые действительно нужно просматривать; можно составлять отчеты сводных таблиц, чтобы систематизировать и суммировать данные для выявления тенденций и закономерностей; можно строить диаграммы на основе этих данных.

11) Экспорт в PDF. Формат переносимых документов (PDF) представляет собой универсальный файловый формат, следовательно, рационально его использовать. Файлы с таким форматом имеют небольшой размер, что удобно, если рабочий компьютер специалиста по кадрам является устаревшим ПК и ощущает недостаток оперативной памяти. Любой пользователь бесплатной программы Adobe Reader может просматривать и печатать файлы, а также обмениваться ими с другими пользователями. Автоматизированная система сможет помочь при затруднениях, обычно возникающих в работе с электронными документами.

12) Экспорт в MS Word. Экспорт в один из самых популярных текстовых редакторов в мире, который может использоваться для самых разнообразных целей — Microsoft Word. Главные его достоинства — функциональность, быстродействие и простота в эксплуатации, при всей своей многогранности и обилии предлагаемых функций приложение не снижает производительность компьютера и занимает достаточно мало места на жестком диске. Также Microsoft Word одно из тех приложений, в которых может работать даже самый неопытный пользователь персональных компьютеров. С автоматизированной системой специалисту по кадрам не придётся перепечатывать всю информацию о сотрудниках вручную.

13) Оповещение по дням рождения. В реализуемой автоматизированной системе предполагается создать возможность оповещение по дням рождения сотрудников с выводом таблиц в MS Excel и выведением на печать. Чтобы узнать, когда у какого сотрудника День рождения, специалисту по кадрам приходится заводить специальный журнал, или же каждый раз просматривать огромное количество личных дел сотрудника. Автоматизированная система избавит кадровика от лишних дел, в нужное время оповестив его.

14) Оповещение о присвоении спецзваний. Специальные звания присваиваются по истечению определенного срока выслуги (например, для полковника нужно отслужить 5 лет подполковником). Отслеживать за каждым сотрудником время его службы в определённой должности сложно и трудоёмко. Специалисту по кадрам приходится либо заводить специальный журнал, где вести учёт выслуг, либо держать все данные в уме, что невозможно. При помощи автоматизированной системы можно производить контроль сроков выслуг и занимаемой сотрудником штатной должности, а также вести учёт квалификации сотрудников и образования.

15) Увольнение сотрудника. Предполагается с помощью автоматизированной системы легко и быстро удалить данные об уволенном сотруднике. Функция требует проставление данному сотруднику реквизитов уволенного. Также требуется ввод причины увольнения.

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

2 Технический проект информационной системы

2.1 Функциональная модель

2.1.1 Контекстная диаграмма и диаграммы детализации процессов

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

После того как контекст описан, проводится построение следующих диаграмм в иерархии. Каждая последующая диаграмма является более подробным описанием (декомпозицией) одной из работ на диаграмме вышестоящего уровня.

Контекстная диаграмма представляет собой схему организации работы отдела кадров РОВД г. Климовичи. Входными данными являются данные о сотрудниках, штатное расписние, нормативные документы, приказы и отчеты. Результатом функционирования являются списки сотрудников, их личные дела, штатная расстановка, информация о командировках, отпусках и увольненияхразличные отчеты: о проведенных библиотечных мероприятиях, о посещаемости, о платных услугах, о выданных книгах, об изменениях книжного фонда (рисунок 1).

Рисунок 1 — Контекстная диаграмма

Рисунок 2 — Декомпозиция процесса A0 «Автоматизировать отдел кадров РОВД г. Климовичи»

В дальнейшем диаграмма разбивается на уровни. Первый уровень — это диаграмма (рисунок 2), с помощью которой производится детализация основного процесса, находящегося на контекстной диаграмме. Контекстную диаграмму разбиваем на 2 блока в соответствии с поставленными целями.

Полученные блоки — «Автоматизировать контроль над штатной расстановкой» и «Автоматизировать формирование отчетов и выведение их на печать» — также подлежат дальнейшей детализации (рисунки 3, 4).

Рисунок 3 — Декомпозиция процесса A1 «Автоматизировать контроль над штатной расстановкой»

Рисунок 4 — Декомпозиция процесса A2 «Автоматизировать формирование отчётов и выведение их на печать»

Блок «Автоматизировать учёт личных дел сотрудников» разбиваем на 4 блока: «Формирование личного дела сотрудника», «Формирование списков личного состава», «Формирование списка дней рождения» и «Осуществление поиска сотрудника по личному номеру» (рисунок 5).

Рисунок 5 — Декомпозиция процесса A1.1 «Автоматизировать учёт личных дел сотрудников»

Блок «Автоматизировать формирование штатной расстановки» разбиваем на 4 блока: «Формирование штатной расстановки», «Увольнение сотрудника», «Контроль отпусков» и «Контроль командировок» (рисунок 6).

Рисунок 6 — Декомпозиция процесса A1.2 «Автоматизировать формирование штатной расстановки»

Блок «Автоматизировать учёт о присвоении спецзваний» разбиваем на 2 блока: «Формирование отчёта о выслугах сотрудников» и «Формирование списка выслуг сотрудников» (рисунок 7).

Рисунок 7 — Декомпозиция процесса A1.3 «Автоматизировать учет о присвоении спецзваний»

Блок «Автоматизировать подсчёт трудового стажа» разбиваем на 2 блока: «Подсчёт трудового стажа» и «Формирование отчёта о трудовом стаже» (рисунок 8).

Рисунок 8 — Декомпозиция процесса A1.4 «Автоматизировать подсчёт трудового стажа»

Блок «Автоматизировать формирование отчётов и выведение их на печать» разбиваем на 2 блока, которые подлежат детализации (рисунок 9).

Блок «Автоматизировать экспорт приказов и отчетов в табличные и текстовые редакторы» разбиваем на 3 блока: «Экспорт приказов и отчетов в MS Word», «Экспорт приказов и отчетов в PDF» и «Экспорт приказов и отчетов в MS Excel» (рисунок 10).

Рисунок 9 — Декомпозиция процесса A2 «Автоматизировать формирование отчетов и выведение их на печать»

Рисунок 10 — Декомпозиция процесса A2.1 «Автоматизировать экспорт приказов и отчётов в табличные и текстовые редакторы»

Блок «Автоматизировать выведение необходимых документов на печать» разбиваем на 4 блока: «Отправление приказов и отчётов на печать», «Отправление данных о сотруднике на печать», «Отправление нормативных документов на печать на печать» и «Отправление штатного расписания на печать» (рисунок 11).

Рисунок 11 — Декомпозиция процесса A2.2 «Автоматизировать выведение необходимых документов на печать»

2.1.2 Диаграмма дерева узлов

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

На диаграмме дерево узлов нижний уровень детализации представляется в виде списка, остальные процессы в виде прямоугольников.

Диаграмма дерева узлов проектируемой базы данных приведена в графическом материале «Диаграмма дерева узлов» (рисунок 12).

Рисунок 12 — Диаграмма дерева узлов

2.2 Информационная модель

2.2.1 Идентификация сущностей и связей. ER — диаграмма логического уровня

Erwin имеет два уровня представления модели: логический и физический. Логический уровень — это абстрактный взгляд на данные. Объекты модели, представляемые на нем, называются сущностями и атрибутами. Логическая модель данных является универсальной, т.к. не зависит от конкретной СУБД.

Для отображения информационной модели рассматриваемого процесса на логической модели используются следующие сущности:

? «Сотрудники» — для хранения информации о сотрудниках, которые работают в РОВД г. Климовичи;

? «День_рождения» — для хранения информации о дате рождения сотрудников, количестве полных лет;

? «Спецзвания» — для ведения учёта выслуги сотрудников и службе в Вооружённых силах;

? «Трудовой_стаж» — для хранения информации о дате приёма на работу и учёта трудового стажа;

? «Отделы» — для фиксации информации об отделах РОВД. Содержит название отдела, номер его штатного расписания, личный номер и ФИО начальника отдела, количество штатных единиц и тарифную ставку;

? «Нормативные_документы» — для хранения информации о нормативных документах, их типах и дат принятия;

? «Приказы» — для хранения информации о приказах, ФИО ответственного за приказ и дату приказа;

? «Командировки» — хранит информацию о местах командировок, сроках, дате и личных номерах камандированных сотрудников;

? «Увольнения» — для хранения информация об уволенных сотрудниках;

? «Табель_отпусков» — хранит информацию об отпусках сотрудников.

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

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

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

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

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

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

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

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

Связь между сущностями «Отделы» и «Нормативные_документы» многие-ко-многим, т.к. на один отдел может быть несколько нормативных документов, а один нормативный документ может быть на несколько отделов.

ER-диаграмма логического уровня приведена в графическом материале «ER-диаграмма логического уровня» (рисунок 13).

Рисунок 13 — ER-диаграмма логического уровня

2.2.2 ER — диаграмма физического уровня. Ограничения доменов. Ограничения ссылочной целостности. Переопределение триггеров. Индексирование отношений.

Физическая модель данных зависит от конкретной СУБД. В ней содержится информация обо всех объектах БД. Одной и той же логической модели может соответствовать несколько разных физических. В физической модели важно описать всю информацию о конкретных физических объектах — таблицах, колонках, индексах, процедурах.

Реализация ссылочной целостности:

? При изменении, удалении или обновлении информации в таблице «Сотрудники» не должно ничего происходить в таблице «Командировки», и наоборот;

? При удалении информации в таблице «Приказы_сотрудников» (или в таблице «Приказы») в таблице «Сотрудники» ничего не происходит, вставка и обновление возможно в том случае, если в таблице «Сотрудники» существует кортеж с соответственным первичным ключом. При удалении/обновлении информации из таблицы «Сотрудники» в таблице «Приказы_сотрудников» (или «Приказы») удаляется/обновляется соответствующий кортеж. При вставке кортежа в таблицу «Сотрудники», в таблице «Приказы_сотрудников» («Приказы») ничего не происходит.

? При вставке/удалении/обновлении кортежей в таблице «Увольнения» для поддержания ссылочной целостности не происходят никакие действия в таблице «Сотрудники».

? Вставка или обновление данных в таблице «Трудовой_стаж» возможно, только если в таблице «Сотрудники» существуют кортежи с соответствующими первичными ключами. При удалении ничего не происходит. При удалении или обновлении данных в таблице «Сотрудники» кортежи в таблице «Трудовой_стаж» удаляются/обновляются соответственно.

? При вставке/удалении/обновлении данных в таблице «Табель_отпусков» для поддержания ссылочной целостности не происходят никакие действия в таблице «Сотрудники». При удалении или обновлении данных в таблице «Сотрудники» кортежи в таблице «Табель_отпусков» удаляются/обновляются соответственно.

? Вставка или обновление данных в таблице «Спецзвания» возможно, только если в таблице «Сотрудники» существуют кортежи с соответствующими первичными ключами. При удалении ничего не происходит. При удалении или обновлении данных в таблице «Сотрудники» кортежи в таблице «Спецзвания» удаляются/обновляются соответственно.

? Вставка или обновление данных в таблице «День_рождения» возможно, только если в таблице «Сотрудники» существуют кортежи с соответствующими первичными ключами. При удалении ничего не происходит. При удалении или обновлении данных в таблице «Сотрудники» кортежи в таблице «День_рождения» удаляются/обновляются соответственно.

? При вставке/удалении/обновлении данных в таблице «Отделы» для поддержания ссылочной целостности не происходят никакие действия в таблице «Сотрудники». При удалении или обновлении данных в таблице «Сотрудники» кортежи в таблице «Отделы» удаляются/обновляются соответственно.

? При удалении информации в таблице «Нормативные_документы_отделов» (или в таблице «Нормативные_документы») в таблице «Отделы» ничего не происходит, вставка и обновление возможно в том случае, если в таблице «Отделы» существует кортеж с соответственным первичным ключом. При удалении информации из таблицы «Отделы» в таблице «Нормативные_документы_отделов» (или «Нормативные_документы») удаляется соответствующий кортеж. При вставке кортежа в таблицу «Отделы», в таблице «Нормативные_документы_отделов» («Нормативные_документы») ничего не происходит.

Для приложения были разработаны следующие триггеры:

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

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

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

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

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

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

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

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

proverka_speczvania проверяет, чтобы при добавлении записи втаблицу «Спецзвания» было заполнено поле «Служба_в_ВС»;

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

ER-диаграмма физического уровня приведена в графическом материале «ER-диаграмма физического уровня» (рисунок 14).

Для приложения были разработаны следующие индексы:

1) CREATE CLUSTERED INDEX Uvol

ON Увольнения (Личный_номер)

GO

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

2) CREATE NONCLUSTERED INDEX Trud

Рисунок 14 — ER-диаграмма физического уровня

база данные аccess

ON Трудовой_стаж (Стаж)

GO

Колонка «Стаж» используется в представлении Expierence и в хранимой процедуре WorkerExperience. Тип индекса — некластерный, т.к. в таблице «Трудовой_стаж» часто изменяются данные.

3) CREATE NONCLUSTERED INDEX Tab

ON Табель_отпусков (Количество_дней)

GO

Колонка «Количество_дней» используется в представлении Otpusk и в хранимой процедуре Vacation. Тип индекса — некластерный, т.к. в таблице «Табель_отпусков» часто изменяются данные.

4) CREATE NONCLUSTERED INDEX Cpec

ON Спецзвания (Выслуга)

GO

Колонка «Выслуга» используется в представлении Sluzba_v_VS и в хранимой процедуре SpecZvanie. Тип индекса — некластерный, т.к. в таблице «Спецзвания» часто изменяются данные.

5) CREATE NONCLUSTERED INDEX Sotr

ON Сотрудники (Личный_номер)

GO

Колонка «Личный_номер» используется в представлениях BD, Dismiss, Dolznost, Expierence, Komandirovki, Obrazovanie, Otdel, Otpusk, Sluzba_v_VS, Trip и в хранимых процедурах BDay, BusinessTrip, City, DeleteWorker, GetWorkerInfo, Lists, NewWorker, Position, Search, SearchWorker, SpecZvanie, UpdateWorker, Vacation, WorkerExperience. Тип индекса — некластерный, т.к. в таблице «Сотрудники» часто изменяются данные.

6) CREATE CLUSTERED INDEX PrS

ON Приказы_сотрудников (Номер_приказа)

GO

Колонка «Номер_приказа» используется в представлении Prikaz и в хранимой процедуре Orders. Тип индекса — кластерный, чтобы ускорить выборку данных.

7) CREATE NONCLUSTERED INDEX Otd

ON Отделы (Номер_штатного_расписания)

GO

Колонка «Номер_штатного_расписания» используется в представлениях Basic Wage, Raspisanie и в хранимых процедурах BasicWageRateUp, NewDepartment, Schedule. Тип индекса — некластерный, т.к. в таблице «Отделы» часто изменяются данные.

8) CREATE NONCLUSTERED INDEX NormD

ON Нормативные_документы (Номер_нормативного_документа)

GO

Колонка «Номер_нормативного_документа» используется в представлениях Docs, Ukaz и в хранимой процедуре Documents. Тип индекса — некластерный, т.к. в таблице «Нормативные_документы» часто изменяются данные.

9) CREATE NONCLUSTERED INDEX Kom

ON Командировки (Номер_командировки)

GO

Колонка «Номер_командировки» используется в представлениях Komandirovki, Trip и в хранимых процедурах BusinessTrip, City. Тип индекса — некластерный, т.к. в таблице «Командировки» часто изменяются данные.

10) CREATE NONCLUSTERED INDEX DR

ON День_рождения (Личный_номер)

GO

Колонка «Личный_номер» используется в представлении BD и в хранимых процедурах BDay, Search. Тип индекса — некластерный, т.к. в таблице «Командировки» часто изменяются данные.

2.2.3 Определение представлений, хранимых процедур серверной компоненты. ER-диаграмма в режиме отображения представлений

Для приложения были разработаны следующие представления:

Dolznost предоставляет информацию обо всех начальниках РОВД (из таблицы «Сотрудники»);

BD предоставляет информацию о сотрудниках, у кого будет День рождения в следующем месяце (из таблиц «День_рождения» и «Сотрудники»);

Dismiss предоставляет информацию об уволенных сотрудниках (из таблицы «Увольнения»);

Raspisanie предоставляет информацию о штатном расписании (из таблицы «Отделы»);

Prikaz предоставляет информацию о приказах и ответственных за их выполнение (из таблицы «Приказы»);

Otpusk предоставляет информацию обо всех отпусках (из таблиц «Табель_отпусков» и «Сотрудники»);

Ukaz предоставляет информацию о нормативных документах типа «Закон РБ» (из таблицы «Нормативные_документы»);

Trip предоставляет информацию о всех командировках, которая также включает в себя личный номер сотрудника, его ФИО, отдел, должность (из таблиц «Командировки» и «Сотрудники»);

Sluzba_v_VS предоставляет информацию о сотрудниках, которые служили в ВС (из таблиц «Сотрудники» и «Спецзвания»);

Otdel предоставляет информацию обо всех сотрудниках, которые работают в отделе ООПиП (из таблицы «Сотрудники);

Obrazovanie предоставляет информацию о сотрудниках с высшим образованием (из таблицы «Сотрудники»);

Komandirovki предоставляет информацию обо всех командировках прошлого года, также информацию о командированном сотруднике: ФИО, должность, отдел (из таблиц «Командировки» и «Сотрудники»);

Experience предоставляет информацию о трудовых стажах всех сотрудников (из таблиц «Трудовой_стаж» и «Сотрудники»);

Docs предоставляет информацию о нормативных документах (из таблицы «Нормативные_документы»);

Basic Wage предоставляет информацию об отделах, где тарифная ставка выше 220 000 (из таблицы «Отделы»).

Для приложения были разработаны следующие хранимые процедуры:

? NewDepartment для вставки новых данных в таблицу «Отделы», NewWorker? в таблицу «Сотрудники»;

? DeleteWorker для удаления данных из таблицы «Сотрудники»;

? UpdateWorker для обновления записей в таблице «Сотрудники», BasicWageRateUp — в таблице «Отделы»;

? Search (по возрасту) для поиска записей в таблице «Сотрудники» и «День_рождения», SearchWorker (на фамилии или началу фамилии) Search (по возрасту) в таблице"Сотрудники";

? BDay для предоставления информации о дне рождении по личному номеру сотрудника;

? BusinessTrip для просмотра командировок определенного сотрудника (по личному номеру);

? City для поиска всех командировок в определенном городе (город передается через параметр);

? Dismissal для просмотра уволенных сотрудников;

? Documents поиск всех нормативных документов отдела (название отдела передается через параметр);

? GetWorkerInfo для просмотра личного дела сотрудника по его личному номеру;

? Lists для просмотра штатной расстановки определенного отдела (название отдела передается через параметр);

? Orders поиск приказа по его номеру;

? Position для просмотра сотрудников, занимающих определенную должность (должность передается через параметр);

? Schedule для просмотра штатного расписания отдела по его номеру;

? SpecZvanie для просмотра сотрудников, которые служили в ВС;

? Vacation для просмотра отпусков определенного сотрудника (по личному номеру);

? WorkerExperience для подсчёта трудового стажа определенного сотрудника (по личному номеру).

2.3 Верификация спроектированной логической модели

После разработки информационной модели ее следует связать с функциональной моделью. Такая связь гарантирует завершенность анализа, гарантирует, что есть источники данных (сущности) для всех работ. Связывание моделей способствует согласованности, корректности и завершенности анализа. Стрелки в функциональной модели обозначают некоторую информацию, использующуюся в моделируемой системе. В информационной модели на логическом уровне информация изображается в виде сущностей. Сущности состоят из совокупностей экземпляров сущностей (кортежи отношений). К информационной модели предъявляется требование нормализации, что должно обеспечить компактность и непротиворечивость хранения данных. Информация, которая моделируется одной стрелкой в функциональной модели, может содержаться в нескольких сущностях и атрибутах информационной модели. На функциональной модели могут присутствовать различные стрелки, изображающие одни и те же данные. Информация о таких стрелках находится в одних и тех же сущностях. Следовательно, одной и той же стрелке в функциональной модели могут соответствовать несколько сущностей в информационной модели и, наоборот, одной сущности может соответствовать несколько стрелок.

Работы в функциональной модели могут создавать или изменять данные, которые соответствуют входящим и выходящим стрелкам. Они могут воздействовать как целиком на сущности (создавая и модифицируя экземпляры сущности), так и на отдельные атрибуты сущности.

Таблица 1 — Отчет о верификации модели

3 Реализация системы

3.1 T-SQL-определения регламентированных запросов

1. Вывод информации об отделах, где тарифная ставка выше 220 000

CREATE VIEW Basic Wage

AS

SELECT Название_отдела, Номер_штатного_расписания, Тарифная_ставка

FROM dbo. Отделы

WHERE (Тарифная_ставка > 220 000) // Выборка строк таблицы, где значения в колонке «Тарифная_ставка» больше 220 000

2. Предоставление информации о сотрудниках, у кого будет День рождения в следующем месяце

CREATE VIEW BD

AS

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

FROM dbo. Сотрудники INNER JOIN dbo. День_рождения ON dbo.Сотрудники.Личный_номер = dbo. День_рождения.Личный_номер

WHERE (MONTH (dbo.День_рождения.Дата_рождения) = MONTH (DATEADD (MONTH, 1, GETDATE ()))) // Выборка строк таблицы, где месяц, указанный в колонке «Дата_рождения», совпадает со следующим месяцем, в соответствии с текущей датой, установленной на данном сервере

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

CREATE VIEW Dismiss

AS

SELECT dbo.Сотрудники.Личный_номер, LOWER (dbo.Сотрудники.Название_отдела) AS [Название отдела],

// LOWER — функция преобразования текста в нижний регистр

dbo.Сотрудники.Фамилия, dbo.Сотрудники.Имя, dbo.Сотрудники.Отчество, dbo.Сотрудники.Должность, dbo.Сотрудники.Звание, dbo.Увольнения.Номер_документа, dbo.Увольнения.Дата_документа, dbo.Увольнения.Причина, GETDATE () //GETDATE — функция, которая возвращает текущую системную отметку времени базы данных в виде значения datetime

AS Сегодня

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

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

CREATE VIEW Docs

AS

SELECT dbo. Нормативные_документы.Номер_нормативного_документа, dbo. Нормативные_документы.Тип, DATEPART (YEAR, dbo. Нормативные_документы.Дата_документа) AS [Год принятия]

// DATEPART — функция, которая возвращает целое число, представляющее указанный компонент YEAR указанной даты «Дата_документа»

FROM dbo. Нормативные_документы INNER JOIN dbo. Нормативные_документы_отделов ON dbo. Нормативные_документы.Номер_нормативного_документа = dbo. Нормативные_документы_отделов.Номер_нормативного_документа

5. Предоставление информации обо всех начальниках РОВД

CREATE VIEW Dolznost

AS

SELECT Название_отдела, Личный_номер, Фамилия, Имя, Отчество, Должность, Звание

FROM dbo. Сотрудники

WHERE (Должность LIKE 'начальник') // Выборка строк таблицы, где значение колонки «Должность» совпадает с указанным значением «начальник»

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

CREATE VIEW Expierence

AS

SELECT dbo.Сотрудники.Личный_номер, dbo.Сотрудники.Фамилия, dbo.Сотрудники.Имя, dbo.Сотрудники.Должность, dbo.Сотрудники.Название_отдела, CAST (dbo.Трудовой_стаж.Стаж AS varchar (50)) AS Стаж // CAST — функция преобразования типа данных в varchar (50) значений колонки «Трудовой_стаж»

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

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

CREATE VIEW Komandirovki

AS

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

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

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

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

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

CREATE VIEW Obrazovanie

AS

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

Должность, Звание, UPPER (Образование) AS Образование

FROM dbo. Сотрудники

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

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

CREATE VIEW Otdel

AS

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

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

Фамилия, Имя, Отчество, Должность, Звание

FROM dbo. Сотрудники

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

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

CREATE VIEW Otpusk

AS

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

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

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

CREATE VIEW Prikaz

AS

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

dbo.Приказы.Дата_приказа

FROM dbo. Приказы INNER JOIN dbo. Приказы_сотрудников ON dbo.Приказы.Номер_приказа = dbo. Приказы_сотрудников.Номер_приказа

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

CREATE VIEW Raspisanie

AS

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

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

FROM dbo. Отделы

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

CREATE VIEW Sluzba_v_VS

AS

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

UPPER (dbo.Сотрудники.Должность) AS Expr1, dbo.Сотрудники.Звание,

// UPPER — функция преобразования текста в верхний регистр

dbo.Спецзвания.Выслуга

FROM dbo. Сотрудники INNER JOIN dbo. Спецзвания ON dbo.Сотрудники.Личный_номер = dbo.Спецзвания.Личный_номер AND dbo.Сотрудники.Название_отдела = dbo.Спецзвания.Название_отдела

WHERE (dbo.Спецзвания.Служба_в_ВС = 'да') // Выборка строк таблицы, где значения в колонке «Служба_в_ВС» равны «да»

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

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.Сотрудники.Личный_номер

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

CREATE VIEW Ukaz

AS

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

FROM dbo. Нормативные_документы

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

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

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

CREATE TABLE DeletedItem

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

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

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

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

ON [PRIMARY]

CREATE TRIGGER deletedBy

ON Нормативные_документы_отделов

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

AS

INSERT INTO DeletedItem

(Номер_нормативного_документа, Название_отдела, Имя_пользователя, Дата_удаления)

SELECT Номер_нормативного_документа, Название_отдела, SYSTEM_USER, GETDATE () // SYSTEM_USER — вставка в таблицу значение текущего имени входа, GETDATE? возвращает текущую дату, установленную на данном сервере

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

2. Срабатывает при добавлении в базу данных нового отдела и проверяет, чтобы номер штатного расписания нового отдела был в пределах 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=0

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

WHERE inserted. Номер_штатного_расписания>2000 OR inserted. Номер_штатного_расписания<1000) // Проверка при вводе строки в таблицу, чтобы значение в колонке «Номер_штатного_расписания» не был больше 2000 и меньше 1000

Set @@f=0

If @@f=0

BEGIN

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

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

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

END

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

CREATE TRIGGER proverka_komandirovka

ON Командировки

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

AS

IF EXISTS (SELECT * FROM Командировки, inserted

WHERE inserted. Дата_с>YEAR (DATEADD (YEAR, +1, GETDATE ())))

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

BEGIN

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

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

END

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

CREATE TRIGGER Upd_Tarif

ON Отделы

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

AS

IF UPDATE (Тарифная_ставка)

BEGIN

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

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

FROM Отделы

END

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

CREATE TRIGGER Ins_Otpusk

ON Табель_отпусков

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

AS

IF EXISTS (SELECT * FROM Табель_отпусков, inserted

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

BEGIN

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

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

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

END

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

CREATE TRIGGER Ins_Otdel

ON Отделы

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

AS

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

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

BEGIN

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

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

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

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

END

7. Триггер, который записывает в отдельную таблицу «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]

CREATE TRIGGER DelWorker

ON Сотрудники

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

AS

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

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

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

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

Адрес_дом, Адрес_квартира, Название_отдела, SYSTEM_USER, GETDATE ()

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

SELECT @@ROWCOUNT // ROWCOUNT — функция, которая возвращает число затронутых при выполнении транзакции строк

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

CREATE TRIGGER Ins_Komandirovka

ON Командировки

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

AS

IF EXISTS (SELECT * FROM Командировки, inserted

WHERE inserted. Место = '') // Проверка при вводе строки в таблицу, чтобы значение в колонке «Место» не было пустое

BEGIN

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

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

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

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

END

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

CREATE TRIGGER proverka_speczvania

ON Спецзвания

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

AS

IF EXISTS (SELECT * FROM Спецзвания, inserted

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

BEGIN

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

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

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

END

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

CREATE TRIGGER Worker

ON Сотрудники

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

AS

IF EXISTS (SELECT * FROM Сотрудники, inserted

WHERE inserted. Фамилия = '' OR inserted. Имя = '' OR inserted. Отчество = ''

OR inserted. Образование = '' OR inserted. Адрес_город = '' OR inserted. Адрес_улица = '')

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

BEGIN

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

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

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

END

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

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

CREATE PROC BasicWageRateUp

@dept varchar (100)

AS

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

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

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

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

CREATE PROC BDay

@id int

AS

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

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

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

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

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

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

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

CREATE PROC BusinessTrip

@id int

AS

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

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

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

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

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

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

CREATE PROC City

@city varchar (100)

AS

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

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

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

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

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

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

CREATE PROC DeleteWorker

@id int

AS

IF EXISTS (SELECT * FROM Сотрудники WHERE Личный_номер = @id)

DELETE Сотрудники // Проверка на наличие нужной строки в таблице

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

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

CREATE PROC Dismissal

@id int

AS

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

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

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

FROM Увольнения, Сотрудники

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

7. Поиск всех нормативных документов отдела

CREATE PROC Documents

@dept varchar (100)

AS

SELECT Нормативные_документы.Номер_нормативного_документа, Нормативные_документы.Тип,

Нормативные_документы.Дата_документа

FROM Нормативные_документы, Нормативные_документы_отделов

WHERE Нормативные_документы.Номер_нормативного_документа = Нормативные_документы_отделов.Номер_нормативного_документа

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

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

CREATE PROC GetWorkerInfo

@id int

AS

SELECT * FROM Сотрудники WHERE Личный_номер = @id

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

9. Просмотр штатной расстановки отдела

CREATE PROC Lists

@dept varchar (100)

AS

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

FROM Сотрудники

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

ORDER BY Фамилия // Сортирует данные, возвращаемые запросом, по фамилии

10. Добавление нового отдела в таблицу «Отделы»

CREATE PROC NewDepartment

@id int,

@dept varchar (100),

@name varchar (100),

@oName varchar (100),

@lastName varchar (100),

@kol int,

@tarif bigint,

@prim varchar (200),

@tel bigint,

@fax bigint

AS

INSERT INTO Отделы (Название_отдела, Номер_штатного_расписания, Фамилия_начальника, Имя_начальника,

Отчество_начальника, Количество_штатных_единиц, Тарифная_ставка, Примечание, Телефон, Факс) // Добавление новой строки в таблицу «Отделы»

VALUES (@id, @dept, @name, @oName, @lastName, @kol, @tarif, @prim, @tel, @fax) // Задает набор выражений значений строки

11. Добавление нового сотрудника

CREATE PROC NewWorker

@id int,

@name varchar (100),

@oName varchar (100),

@lastName varchar (100),

@education varchar (100),

@position varchar (100),

@rank varchar (100),

@town varchar (100),

@street varchar (100),

@d int,

@kv int,

@dept varchar (100)

AS

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

VALUES (@id, @name, @oName, @lastName, @education, @position, @rank, @town, @street, @d, @kv,@dept)) // Задает набор выражений значений строки

12. Поиск приказа по его номеру

CREATE PROC Orders

@id int

AS

SELECT Приказы. Номер_приказа, Приказы_сотрудников.Название_отдела, Приказы. Ответственный, Приказы. Дата_приказа

FROM Приказы, Приказы_сотрудников

WHERE Приказы. Номер_приказа = Приказы_сотрудников.Номер_приказа AND Приказы. Номер_приказа = @id

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

13. Поиск сотрудников, занимающий определенную должность

CREATE PROC Position

@position varchar (100)

AS

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

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

FROM Сотрудники

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

14. Поиск отдела по номеру его штатного расписания

CREATE PROC Schedule

@id int

AS

SELECT Отделы. Номер_штатного_расписания, Отделы. Название_отдела, Отделы. Фамилия_начальника, Отделы. Имя_начальника, Отделы. Отчество_начальника, Отделы. Количество_штатных_единиц, Отделы. Тарифная_ставка, Отделы. Примечание

FROM Отделы

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

15. Поиск сотрудников по возрасту

CREATE PROC Search

@i int

AS

SELECT Сотрудники. Личный_номер, Сотрудники. Фамилия, Сотрудники. Имя, Сотрудники. Отчество, Сотрудники. Должность,

День_рождения.Дата_рождения, День_рождения.Количество_полных_лет

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

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

AND ISNUMERIC (День_рождения.Количество_полных_лет)<>0

AND День_рождения.Количество_полных_лет >= @i; // Выборка строк таблицы, где значение колонки «Количество_полных_лет» больше или равно введенному значению @i; ISNUMERIC проверяет, чтобы значение «Количество_полных_лет» не было равно нулю

16. Поиск сотрудника по фамилии или началу фамилии

CREATE PROC SearchWorker

@name varchar (100)

AS

SELECT * FROM Сотрудники

WHERE Фамилия LIKE @name // Выборка строк таблицы, где значение колонки «Фамилия» соответствует введенному значению @name

17. Просмотра сотрудников, которые служили в ВС

CREATE PROC SpecZvanie

@vs varchar (20)

AS

SET @vs = 'да' // Установить значение переменной, равной значению «да»

SELECT Сотрудники. Личный_номер, Сотрудники. Фамилия, Сотрудники. Имя, Сотрудники. Отчество, Сотрудники. Должность

FROM Спецзвания, Сотрудники

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

18. Обновление данных о сотруднике

CREATE PROC UpdateWorker

@id int,

@position varchar (100),

@rank varchar (100)

AS

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

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

SET Должность = @position // Установить значение в колонке «Должность», равное введенному значению переменной @position

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

UPDATE Сотрудники

SET Звание = @rank // Установить значение в колонке «Звание», равное введенному значению переменной @ rank

WHERE Личный_номер = @id

19. Просмотр отпусков сотрудника

CREATE PROC Vacation

@id int

AS

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

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

Табель_отпусков.Дата_с, Табель_отпусков.Количество_дней

FROM Табель_отпусков, Сотрудники

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

20. Поиск сотрудника по трудовому стажу, выше указанного

CREATE PROC WorkerExperience

@experience int

AS

SELECT distinct Сотрудники. Личный_номер, Сотрудники. Фамилия, Сотрудники. Имя, Сотрудники. Должность, Сотрудники. Название_отдела, DATEDIFF (year, dbo. Трудовой_стаж.Дата_приёма_на_работу, GETDATE ()) AS 'Стаж' // DATEDIFF — функция, которая возвращает интервал времени day, прошедшего от указанной даты Дата_с до текущей даты, установленной на данном сервере, GETDATE — возвращает текущую дату, установленную на данном сервере

FROM Сотрудники, Трудовой_стаж

WHERE Сотрудники. Личный_номер=Трудовой_стаж.Личный_номер AND Трудовой_стаж.Стаж >= @experience // Выборка строк таблицы, где значение колонки «Стаж» больше или равно введенному значению @experience

3.4 T-SQL-определения курсоров

1. Курсор для просмотра сотрудников в выбранном отделе

CREATE PROCEDURE curs1

@otdel varchar (100)

AS

DECLARE curs1 CURSOR

GLOBAL SCROLL KEYSET

// глобальный прокручиваемый ключевой курсор, который будет существовать до закрытия текущего соединения

TYPE_WARNING

// Сервер будет информировать пользователя о неявном изменении типа курсора, если он несовместим с запросом SELECT

FOR

SELECT*FROM Сотрудники

WHERE Название_отдела LIKE @otdel

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

FOR READ ONLY // Только для чтения

open global curs1 // открытие глобального курсора

DECLARE

@@Counter int

SET @@Counter =@@CURSOR_ROWS

// присвоение переменной @@Counter значения, равного числу рядов курсора =@@CURSOR_ROWS

Select @@Counter 'Количество сотрудников в этом отделе'

CLOSE curs1 // закрытие курсора

DEALLOCATE curs1 // освобождение курсора

2. Курсор для просмотра количествa командировок в этом месяце

CREATE PROCEDURE curs2

AS

DECLARE curs2 CURSOR

GLOBAL SCROLL KEYSET

// глобальный прокручиваемый ключевой курсор, который будет существовать до закрытия текущего соединения

TYPE_WARNING

//Сервер будет информировать пользователя о неявном изменении типа курсора, если он несовместим с запросом SELECT

FOR

SELECT

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

Командировки.Личный_номер, Командировки. Название_отдела

FROM Командировки

FOR UPDATE // Курсор на обновление в таблице

open global curs2 // открытие глобального курсора

DECLARE

@@nomer int,

@@date datetime,

@@srok int,

@@mesto varchar (50),

@@l_nomer int,

@@otdel varchar (100),

@@var int,

@@Counter int

SET @@Counter = 1

SET @@var = 0

WHILE @@Counter<= @@CURSOR_ROWS

// Выполняется до тех пор, пока число строк в таблице меньше или равно числу рядов курсора

BEGIN

FETCH curs2 INTO @@nomer, @@date, @@srok, @@mesto, @@l_nomer,@@otdel

// FETCH — получает определенную строку из курсора и помещает данные из столбцов выборки в переменные @@nomer, @@date, @@srok, @@mesto, @@l_nomer,@@otdel

IF (DATEDIFF (M,@@date, GETDATE ())= 0) // Проверка, чтобы месяц текущей даты, установленной на данном сервере, был равен значению переменной @@date

BEGIN

SET @@var=@@var+1 // Установить значение переменной @@var большим на единицу

print @@date

END

SET @@Counter =@@Counter +1 // Установить значение переменной @@Counter большим на единицу

END

Select @@var as 'В этом месяце командировок:'

CLOSE curs2 //закрытие курсора

DEALLOCATE curs2 //освобождение курсора

3. Поиск сотрудника по фамилии

CREATE PROCEDURE curs3 // открытие глобального курсора

@fio varchar (100)

AS

DECLARE curs3 CURSOR

GLOBAL SCROLL KEYSET // глобальный прокручиваемый ключевой курсор, который будет существовать до закрытия текущего соединения

TYPE_WARNING // Сервер будет информировать пользователя о неявном изменении типа курсора, если он несовместим с запросом SELECT

FOR

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

FROM Сотрудники

FOR UPDATE // Курсор на обновление в таблице

open global curs3

DECLARE

@@id int,

@@name varchar (100),

@@oName varchar (100),

@@lastName varchar (100),

@@position varchar (100),

@@rank varchar (100),

@@dept varchar (100),

@@Counter int,

@@var int

SET @@Counter = 1

SET @@var = 0

WHILE @@Counter<= @@CURSOR_ROWS // Выполняется до тех пор, пока число строк в таблице меньше или равно числу рядов курсора

BEGIN

FETCH curs3 INTO @@id, @@name, @@oName,@@lastName,@@position,@@rank,@@dept

// FETCH — получает определенную строку из курсора и помещает данные из столбцов выборки в переменные @@id, @@name, @@oName,@@lastName,@@position,@@rank и @@dept

IF @fio = @@name // Проверка, чтобы вводимая фамилия @fio была равна значению @@name

BEGIN

Select @@id as 'Личный номер', @@name+' '+SUBSTRING (@@oName, 1, 1)+'.'+ SUBSTRING (@@lastName, 1, 1)+'.'

// SUBSTRING — функция, которая возвращает часть значения @@oName и @@lastName, чтобы «склеить» ФИО сотрудника

as 'ФИО',@@position as 'Должность', @@rank 'Звание', @@dept 'Отдел'

END

SET @@Counter =@@Counter +1 // Установить значение переменной @@Counter большим на единицу

END

CLOSE curs3 //закрытие курсора

DEALLOCATE curs3 //освобождение курсора

4. Курсор для просмотра отпусков по выбранному типу

CREATE PROCEDURE curs4

@otpusk varchar (100)

AS

DECLARE curs4 CURSOR

GLOBAL SCROLL KEYSET

// глобальный прокручиваемый ключевой курсор, который будет существовать до закрытия текущего соединения

TYPE_WARNING

// Сервер будет информировать пользователя о неявном изменении типа курсора, если он несовместим с запросом SELECT

FOR

SELECT * FROM Табель_отпусков

FOR UPDATE // Курсор на обновление в таблице

open global curs4 // открытие глобального курсора

DECLARE

@@nomer int,

@@type varchar (100),

@@date datetime,

@@day int,

@@id int,

@@dept varchar (100),

@@Counter int

SET @@Counter = 1

WHILE @@COUNTER<= @@CURSOR_ROWS

// Выполняется до тех пор, пока число строк в таблице меньше или равно числу рядов курсора

BEGIN

FETCH curs4 INTO @@nomer, @@type,@@date,@@day, @@id, @@dept

// FETCH — получает определенную строку из курсора и помещает данные из столбцов выборки в переменные @@nomer, @@type,@@date,@@day, @@id и @@dept

IF (CHARINDEX (@otpusk,@@type)<>0) // Проверка, чтобы значений вводимой переменной @otpusk не было в колонке со значениями @@type

BEGIN

Select @@nomer as 'Номер табеля', @@type as 'Тип отпуска', CAST (@@date AS nvarchar (12)) as 'Дата с', @@day as 'Срок',

// CAST — функция преобразования типа данных в nvarchar (12) значений колонки «Дата_с»

@@id 'Личный номер сотрудника', @@dept 'Отдел'

END

SET @@Counter =@@Counter +1 // Установить значение переменной @@Counter большим на единицу

END

CLOSE curs4 //закрытие курсора

DEALLOCATE curs4 //освобождение курсора

5. Курсор для просмотра сотрудников, у кого в текущем месяце День рождения

CREATE PROCEDURE curs5

as

DECLARE curs5 CURSOR

GLOBAL SCROLL KEYSET

// глобальный прокручиваемый ключевой курсор, который будет существовать до закрытия текущего соединения

TYPE_WARNING

// Сервер будет информировать пользователя о неявном изменении типа курсора, если он несовместим с запросом SELECT

FOR

SELECT * FROM День_рождения

FOR UPDATE // Курсор на обновление в таблице

open global curs5 // открытие глобального курсора

DECLARE

@@age int,

@@date datetime,

@@id int,

@@otdel varchar (100),

@@var int,

@@Counter int

SET @@Counter = 1

SET @@var = 0

WHILE @@Counter<= @@CURSOR_ROWS

// Выполняется до тех пор, пока число строк в таблице меньше или равно числу рядов курсора

BEGIN

FETCH curs5 INTO @@age, @@date, @@id,@@otdel

// FETCH — получает определенную строку из курсора и помещает данные из столбцов выборки в переменные @@age, @@date, @@id и @@otdel

IF (MONTH (@@date) = MONTH (DATEADD (MONTH, 0, GETDATE ())))

// Проверка, чтобы значение MONTH введенной переменной (@@date совпадало с текущим месяцем, в соответствии с текущей датой, установленной на данном сервере; GETDATE — функция, возвращающая текущую системную отметку времени базы данных

BEGIN

SET @@var=@@var+1

// Установить значение переменной @@var большим на единицу

PRINT @@date // Вывод на экран значения переменной @@date

Select @@age as 'Полных лет', CAST (@@date AS nvarchar (12)) as 'Дата рождения',

@@id 'Личный номер сотрудника', @@otdel 'Отдел'

END

SET @@Counter =@@Counter +1

// Установить значение переменной @@Counter большим на единицу

END

Select @@var as 'В этом месяце день рождение у:'

CLOSE curs5 //закрытие курсора

DEALLOCATE //освобождение курсора

3.5 Распределение прав доступа

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

? ограничение прав на чтение, изменение или уничтожение;

? обеспечение целостности информации, а также доступности информации для органов управления и уполномоченных пользователей;

? исключение утечки информации при обработке и передаче между объектами вычислительной техники.

Созданы группы пользователей, исполняющие различные роли:

1) Админ (login? admin/password? 0). Имеет доступ ко всей информации, может добавлять/изменять данные в таблицах, добавлять новых пользователей и удалять существующих.

CREATE LOGIN admin // Создание логина admin

WITH PASSWORD = '0' // Присвоение пароля

CREATE USER admin // Создание пользователя admin

FOR LOGIN admin

GRANT ALL PRIVILEGES // Назначение пользователю всех прав

TO admin

WITH GRANT OPTION // C возможностью назначения прав другим пользователям

GO

2) Сотрудник отдела кадров (kadr/1). Так же, как и админ, имеет доступ ко всем данным, есть возможность изменять их, но не может производить операции с пользователями.

CREATE LOGIN kadr // Создание логина kadr

WITH PASSWORD = '1' // Присвоение пароля

CREATE USER kadr // Создание пользователя kadr

FOR LOGIN kadr

DENY ALL PRIVILEGES ON OBJECT: Пользователи TO kadr // Запрет всех действий с таблицей

GRANT SELECT, UPDATE, DELETE, INSERT ON День_рождения TO kadr // Назначение всех прав доступа к таблице

GRANT SELECT, UPDATE, DELETE, INSERT ON Командировки TO kadr

GRANT SELECT, UPDATE, DELETE, INSERT ON Нормативные_доументы TO kadr

GRANT SELECT, UPDATE, DELETE, INSERT ON Нормативные_документы_отделов TO kadr

GRANT SELECT, UPDATE, DELETE, INSERT ON Отделы TO kadr

GRANT SELECT, UPDATE, DELETE, INSERT ON Приказы TO kadr

GRANT SELECT, UPDATE, DELETE, INSERT ON Приказы_сотрудников TO kadr

GRANT SELECT, UPDATE, DELETE, INSERT ON Сотрудники TO kadr

GRANT SELECT, UPDATE, DELETE, INSERT ON Спецзвания TO kadr

GRANT SELECT, UPDATE, DELETE, INSERT ON Табель_отпусков TO kadr

GRANT SELECT, UPDATE, DELETE, INSERT ON Трудовой_стаж TO kadr

GRANT SELECT, UPDATE, DELETE, INSERT ON Увольнения TO kadr

3) Сотрудник милиции (sort/2). Имеет доступ к информации, содержащейся в данной информационной системе, только в режиме чтения (отсутствуют кнопки удаления, добавления, сохранения).

CREATE LOGIN sort // Создание логина sort

WITH PASSWORD = '2' // Присвоение пароля

CREATE USER sort // Создание пользователя sort

FOR LOGIN sotr

DENY ALL PRIVILEGES ON OBJECT: DeletedItem TO sort // Запрет всех действий с таблицей

DENY ALL PRIVILEGES ON OBJECT: DeletedWorker TO sort

DENY ALL PRIVILEGES ON OBJECT: Пользователи TO sort // Запрет всех действий с таблицей

GRANT SELECT ON День_рождения TO sort // Назначение прав доступа только на выборку данных из таблицы в режиме чтения

GRANT SELECT ON Сотрудники TO sotr

GRANT SELECT ON Отделы TO sotr

GRANT SELECT ON Трудовой_стаж TO sort

GRANT SELECT ON Командировки TO sotr

GRANT SELECT ON Нормативные_документы TO sotr

GRANT SELECT ON Нормативные_документы_отделов TO sotr

GRANT SELECT ON Приказы TO sotr

GRANT SELECT ON Приказы_сотрудников TO sotr

GRANT SELECT ON Спецзвания TO sotr

GRANT SELECT ON Табель_отпусков TO sotr

GRANT SELECT ON Увольнения TO sotr

4 Результат тестирования информационной системы

Для нормальной работы системы необходимо 256 Мбайт оперативной памяти, Windows NT/2000/XP и выше, Access2000 и выше.

Результатом тестирования в данном случае может послужить создание отчета, содержащего информацию о штатной расстановке.

После тестирования получили результаты:

? Информационная система поддерживает многопользовательский режим.

? Тестирование запросов прошло успешно.

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

Отчеты составлены на основе запросов и используются для получения информации.

Заключение

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

Современные СУБД — многопользовательские системы управления базой данных, которые специализируется на управлении массивом информации одним или множеством одновременно работающих пользователей.

Во время выполнения курсовой работы было разработано многопользовательская информационная система «Отдел кадров РОВД г. Климовичи».

При создании ИС использовались СУБД Microsoft ACCESS 2000, средство моделирования баз данных All Fusion Data Modeler 4.1, средство моделирования бизнес-процессов All Fusion Process Modeler r7, сервер баз данных Microsoft SQL Server Express 2005, язык баз данных T-SQL.

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

Все пункты курсового задания были выполнены, разработанная система выполняет поставленную перед ним задачу.

Список использованных литературных источников

1. Бьюли, А. Изучаем SQL / А. Бьюли, Э. Оппель. — М.: Символ, 2007. — 312 с.

2. Жилинский, А. Самоучитель Microsoft SQL Server 2005 / А. Жилинский. — СПб: BHV, 2004. — 224 с.

3. Бхамидипати, К. SQL. Справочник программиста/ К. Бхамидипати. — М.: Эком, 2003. — 304 с.

4. Литвин, П. Разработка корпоративных приложений в Access 2002. Для профессионалов / П. Литвин, К. Гетц, М. Гунделой. — СПб.: Питер, 2002. — 1008 с.

5. Виейра, Р. Программирование баз данных Microsoft SQL Server 2005. Базовый курс / Р. Вийера. — М.: Вильямс, 2003. — 848 с.

6. Уилтон, П. SQL для начинающих / П. Уилтон, Дж. Колби. — М.: Вильямс, 2006. — 496 с.

7. Фленов, М. Transact SQL в подлиннике / М. Фленов. — СПб: BHV, 2006. — 576 с.

8. Моисеенко, С. SQL. Задачи и решения / С. Моисеенко. СПб.: Питер, 2006. — 256 с.

9. Microsoft SQL Server 7 для профессионалов. — СПб.: Питер, 2000. — 896 с.

10. Базы данных — революционная эволюция. / [Электронный ресурс] Режим доступа: http://www.advlab.ru/articles/article378.htm. — Дата доступа: 25.03.2013

11. Маклаков, С. В. Моделирование бизнес-процессов с BPwin 4.0 / С. В. Маклаков // — М.:"Диалог-МИФИ", 2002. — 209 с.