Создание автоматизированной информационной системы кадрового учета
Содержание
Введение
Техническое задание
.1 Анализ предметной области
.2 Постановка задачи
Технический проект
информационной системы
.1 Функциональная модель
.1.1 Контекстная диаграмма и
диаграммы детализации процессов
.1.2 Диаграмма дерева узлов
.2 Информационная модель
.2.1 Идентификация сущностей
и связей. ER-диаграмма логического уровня
.2.2 ER-диаграмма физического
уровня. Ограничения доменов. Ограничения ссылочной целостности. Переопределение
триггеров. Индексирование отношений
.2.3 Определение представлений,
хранимых процедур серверной компоненты. ER-диаграмма в режиме отображения
представлений
.3 Верификация
спроектированной логической модели
Реализация системы
.1 T-SQL-определения
регламентированных запросов
.2 T-SQL-определения
триггеров
.3 T-SQL-определения хранимых
процедур
.4 T-SQL-определения курсоров
.5 Распределение прав доступа
Результат тестирования
информационной системы
Заключение
Список использованных
литературных источников
Введение
Создание современных электронных вычислительных машин позволило
автоматизировать обработку данных во многих сферах человеческой деятельности.
Без современных систем обработки данных трудно представить сегодня передовые
производственные технологии, управление экономикой на всех ее уровнях, научные
исследования, образование, издательское дело, функционирование средств массовой
информации, проведение крупных спортивных состязаний. Значительно расширило
сферу применения систем обработки данных появление персональных компьютеров.
Одним из наиболее распространенных классов систем обработки данных
являются информационные системы. Хотя на уровне здравого смысла назначение
таких систем понятно каждому, для серьезного обсуждения технологий современных
информационных систем необходимо более четко определить, в чем заключаются их
специфические особенности, чем они отличаются от других систем обработки
данных, какие функции они могут выполнять, какими ресурсами они располагают.
Любой разумный вид деятельности основывается на информации о свойствах
состояния и поведения той части реального мира, с которой связана эта
деятельность. Для получения такой информации во многих случаях необходимо
регулярно через некоторые интервалы времени проводить натурные измерения (или
наблюдения), позволяющие определять характеристики состояния сущностей
реального мира и протекающих процессов, соответствующие моментам времени, когда
эти измерения производятся.
В других случаях удается воспользоваться «материализованной» информацией,
содержащейся в различного рода бумажных документах, отчетах или публикациях,
которые также выступают как часть реальности. Требуемую информацию можно
извлечь из них путем своего рода «наблюдения».
Однако некоторые натурные измерения или наблюдения могут оказаться
неосуществимыми в отведенное для них время в связи с большой трудоемкостью,
высокой стоимостью, недоступностью объекта измерения (наблюдения) и по другим
причинам.
Значительно сократить объем необходимых натурных измерений позволяет
компьютерное моделирование реальности. Если компьютерная модель адекватно (относительно
информационных потребностей пользователей) отражает состояние и динамику
реальности, то многие необходимые сведения можно получать с помощью такой
модели, избегая тем самым натурных измерений, с существенно меньшими затратами
времени, а возможно, и при более низкой стоимости. Именно для поддержки таких
моделей служит специальный класс систем обработки данных ─
автоматизированные информационные системы.
Автоматизированной информационной системой называется комплекс,
включающий вычислительное и коммуникационное оборудование, программное
обеспечение, лингвистические средства и информационные ресурсы, а также
системный персонал и обеспечивающий поддержку динамической информационной
модели некоторой части реального мира для удовлетворения информационных
потребностей пользователей.
Поскольку модель предметной области, поддерживаемая информационной
системой, материализуется в форме организованных необходимым образом
информационных ресурсов, она называется информационной моделью.
Информационные системы уже многие десятки и даже сотни лет существуют и
используются на практике в форме различного рода картотек и/или коллекций
бумажных документов. Однако в таких системах отсутствует какая-либо
автоматизация обработки данных. Они позволяют лишь регистрировать и поддерживать
в систематизированной форме на бумажных носителях результаты произведенных
натурных измерений.
Актуальность данной работы обусловлена теоретической и практической
значимостью проблем, связанных с реализацией кадровой политики МВД в сфере
подготовки кадров, как важнейшей гарантии укрепления законности и правопорядка,
столь необходимых для формирования правового государства.
Однако, несмотря на высокий научный уровень исследований управленческих
отношений и вопросов кадровой политики, слабо изученными остаются проблемы
кадрового обеспечения ОВД и их взаимодействие в формировании и реализации
целостной системы кадровой политики.
Все это в совокупности и обусловило определение цели, задач, объекта и
предмета данной работы.
Целью данного курсового проекта является создание автоматизированной
информационной системы заданной предметной области − кадровый учет,
определить состояние кадрового планирования в РОВД и специфику органов
управления по формированию и реализации кадровой политики как целостной системы,
выявить особенности управления персоналом в органах власти.
1
Техническое задание
.1 Анализ
предметной области
Объект автоматизации - РОВД г. Климовичи.
Документы, на основании которых создается система:
− Положение о прохождении службы в
органах внутренних дел Республики Беларусь;
− Закон об ОВД РБ;
− Должностные инструкции;
− Трудовое законодательство;
− Методические материалы по ведению
документации по учету и движению персонала.
Систему предполагается использовать для удобства учёта личного состава,
ускорения процесса поиска необходимой информации, упорядочивания хранения
данных.
Программа позволяет хранить всю информацию о сотрудниках РОВД.
Отдел кадров, деятельность которого планируется автоматизировать,
занимается учётом сотрудников РОВД г. Климовичи. Важнейшим звеном в данной
деятельности являются специалисты по работе с сотрудниками милиции. В
зависимости от того, насколько автоматизирована их работа, можно судить об
эффективности работы отдела кадров. Каждый день отдел кадров осуществляет
операции по работе с сотрудниками.
Сотрудник лично заполняет данные о себе, после этого специалист из отдела
кадров принимает эти данные и вносит их в базу данных. Непосредственно из базы
данных берутся необходимые данные для заполнения личного дела сотрудника,
формирования приказов и отчётов, внесения в послужной список.
Отдел кадров выполняет следующие функции.
− Учёт личного состава РОВД;
− Обработка и хранение данных о
сотрудниках;
− Планирование структуры РОВД и
кадровой политики;
− Произведение оперативного учёта
движения кадров;
− Ведение кадрового делопроизводства;
− Осуществление контроля над
своевременным исполнением распоряжений, приказов и поручений начальника РОВД;
− Оформление приема, перевода и
увольнения сотрудников в соответствии с трудовым законодательством, положениями
и приказами начальника РОВД;
− Формирование и ведение личных дел
работников, внесение изменений, связанных с трудовой деятельностью;
− Заполнение, учёт и хранение трудовых
книжек, произведение подсчёта трудового стажа;
− Произведение записи в трудовых
книжках о поощрениях и награждениях сотрудников;
− Ведение учёта предоставления отпусков
работникам, осуществление контроля над составлением и соблюдением графиков
очередных отпусков;
− Ведение учёта нарушений трудовой
дисциплины и контролирование своевременности принятия соответствующих мер;
− Выполнение других служебных поручений
начальника РОВД.
.2 Постановка задачи
Данная ИС разрабатывается с расчётом на одного пользователя - заместителя
начальника РОВД по работе личным составом. При работе с системой кадровик
должен решать следующие задачи:
− Получать доступ к данным таблицы, в
которых должна содержаться вся необходимая информация;
− Просматривать данные таблицы, при
необходимости редактировать их;
− Создавать на основе исходных данных
личные дела сотрудников, отчёты и приказы.
Таким образом, основные процессы, которые должна реализовывать система:
) Ведение личного дела сотрудника, включающего служебные данные и
практически любые дополнительные сведения. Контроль над оформлением и хранением
личных дел возлагается на специалиста по кадрам. До внедрения системы в отдел
кадров РОВД документы личных дел формировались в специальные папки. В них
включались документы, фиксирующие процесс поступления на работу, а затем все
документы, возникающие в период работы, в хронологической последовательности.
До использования автоматизированной системы формирование и ведение личного дела
сотрудника занимало значительное время. Также для хранения этих дел должна была
отводиться дополнительная площадь. Система может хранить данные о сотруднике
сколь угодно долго, исключает ошибку при нумерации личных дел, исключает потерю
данных о сотрудниках, даёт возможность быстрого поиска необходимой информации.
Следовательно, благодаря автоматизированной системе, сокращается объем
бумажного документооборота и время на работу с информацией о сотрудниках,
данные о любом сотруднике можно получить путем запросов.
) Предоставление сведений о штатной расстановке. Штатная
расстановка применяется для оформления организационной структуры и штатного
состава. Чтобы сформировать штатную расстановку, специалисту по кадрам
приходится пересматривать и проверять большой объём документов. Это отнимает
много времени. При помощи автоматизированной системы, кадровику будет быстро
предоставлена необходимая информация: фамилия, имя и отчество сотрудников,
номера и даты приказа о приёме на работу/должность, наименования и перечень
структурных единиц, наименования должностей и т.д., собранная в одном месте.
Это значительно облегчит и ускорит процесс формирования штатной расстановки.
) Формирование списков личного состава РОВД. При составлении
списков личного состава РОВД необходимо сортировать личные дела сотрудников по
различным критериям (по половой принадлежности, возрасту, стажу, работы в ОВД,
образовании, должности, звании и т.д.). Вручную это займёт достаточно долгое
время, к тому же велика вероятность ошибки, например, пропустить личное дело
какого-либо сотрудника милиции. Автоматизированная система осуществит обработку
и формирование личного состава за очень короткое время.
) Выведение на печать личного дела сотрудника. Если начальству
РОВД необходимо личное дело какого-либо сотрудника, это легко осуществимо в
один клик мыши. До использования автоматизированной системы нужно было
обращаться в архив и искать дело по личному номеру (который до этого необходимо
найти в специальном журнале), что долго и затруднительно. Такая функция
системы, как выведение на печать личного дела сотрудника, облегчает работу
специалиста по кадрам.
) Подсчёт трудового стажа сотрудника. Эта функция разработана для
облегчения подсчёта трудового стажа. В таблицу вводятся даты приема на работу и
увольнения с нее на основании трудовой книжки или иных подтверждающих стаж
документов. Ранее подсчёт производился вручную специалистом по кадрам, что
могло привести к ошибке в результате. Предполагается, что система облегчит эту
рутинную и трудоёмкую работу.
) Поиск сотрудников по выбранному критерию. Без использования
автоматизированной системы поиск производится вручную, отнимает очень много
рабочего времени специалиста по кадрам. Автоматизированная система даёт
возможность осуществить быстрый и удобный поиск сотрудников по определённому
критерию, например, по должности или прохождению службы в Вооружённых силах РБ.
) Формирование приказов, отчётов. Приказы и отчёты формировались
каждый раз заново, печатались вручную. Автоматизированная система должна
уменьшить время, затраченное на это. Предполагается создать шаблоны. Данные
полей генерируются из таблицы базы данных, подставляются в шаблоны, далее
формируются различные приказы и отчёты. Предполагается сократить время на
формирование отчётов для руководства и бухгалтерии.
) Выведение приказов, отчётов на печать. Данная функция позволяет
снизить время, затраченное на печать отчёта. До использования
автоматизированной системы все отчёты и приказы печатались вручную. После
автоматизации предполагается выведение на печать приказов и отчётов в один клик
мыши. На печать выводится данные из одной или нескольких баз данных.
) Поиск сотрудника милиции по личному номеру. Без
автоматизированной системы поиск информации о нужном сотруднике милиции
занимает значительное время, т.к. специалисту по кадрам приходиться
просматривать много документов (личных дел сотрудников), чтобы найти нужный
номер. Кроме того, из-за ошибок во время оформления и хранения этих личных дел,
некоторые документы могут быть утеряны, а восстановление этих документов -
достаточно трудоемкий процесс. Автоматизированная система хранит эту информацию
сколь угодно долго, поиск осуществляется за считанные секунды, достаточно лишь
вбить нужные цифры в строку поиска, исключены ошибки и нет необходимости в
дополнительных папках (а, следовательно, выделение площади под эти папки) для
хранения документов и журналов с учётом личных номеров сотрудников.
) Экспорт в MS Excel. Благодаря автоматизированной
системе экспорт в excel
осуществляется в один клик мыши. После того как данные перенесены в еxcel, их
можно анализировать самыми разными методами. Можно применить автофильтр, чтобы
оставить только те данные, которые действительно нужно просматривать; можно
составлять отчеты сводных таблиц, чтобы систематизировать и суммировать данные
для выявления тенденций и закономерностей; можно строить диаграммы на основе
этих данных.
) Экспорт в PDF.
Формат переносимых документов (PDF)
представляет собой универсальный файловый формат, следовательно, рационально
его использовать. Файлы с таким форматом имеют небольшой размер, что удобно,
если рабочий компьютер специалиста по кадрам является устаревшим ПК и ощущает
недостаток оперативной памяти. Любой пользователь бесплатной программы Adobe Reader может просматривать и печатать файлы, а также
обмениваться ими с другими пользователями. Автоматизированная система сможет
помочь при затруднениях, обычно возникающих в работе с электронными
документами.
) Экспорт в MS Word. Экспорт в один из самых популярных
текстовых редакторов в мире, который может использоваться для самых
разнообразных целей - Microsoft Word. Главные его достоинства - функциональность,
быстродействие и простота в эксплуатации, при всей своей многогранности и
обилии предлагаемых функций приложение не снижает производительность компьютера
и занимает достаточно мало места на жестком диске. Также Microsoft Word одно из
тех приложений, в которых может работать даже самый неопытный пользователь
персональных компьютеров. С автоматизированной системой специалисту по кадрам
не придётся перепечатывать всю информацию о сотрудниках вручную.
) Оповещение по дням рождения. В реализуемой автоматизированной
системе предполагается создать возможность оповещение по дням рождения
сотрудников с выводом таблиц в MS Excel и выведением на печать. Чтобы
узнать, когда у какого сотрудника День рождения, специалисту по кадрам
приходится заводить специальный журнал, или же каждый раз просматривать
огромное количество личных дел сотрудника. Автоматизированная система избавит
кадровика от лишних дел, в нужное время оповестив его.
) Оповещение о присвоении спецзваний. Специальные звания
присваиваются по истечению определенного срока выслуги (например, для
полковника нужно отслужить 5 лет подполковником). Отслеживать за каждым
сотрудником время его службы в определённой должности сложно и трудоёмко.
Специалисту по кадрам приходится либо заводить специальный журнал, где вести
учёт выслуг, либо держать все данные в уме, что невозможно. При помощи
автоматизированной системы можно производить контроль сроков выслуг и
занимаемой сотрудником штатной должности, а также вести учёт квалификации
сотрудников и образования.
) Увольнение сотрудника. Предполагается с помощью
автоматизированной системы легко и быстро удалить данные об уволенном
сотруднике. Функция требует проставление данному сотруднику реквизитов
уволенного. Также требуется ввод причины увольнения.
В готовом виде система должна быть максимально простой и удобной: все
операции должны выполняться с помощью элементарных действий пользователя. Здесь
необходима распечатка исходных таблиц и отчётов, источниками которых являются
ранее составленные запросы.
2 Технический
проект информационной системы
.1
Функциональная модель
.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.1
Идентификация сущностей и связей. ER - диаграмма логического уровня
Erwin
имеет два уровня представления модели: логический и физический. Логический
уровень - это абстрактный взгляд на данные. Объекты модели, представляемые на
нем, называются сущностями и атрибутами. Логическая модель данных является
универсальной, т.к. не зависит от конкретной СУБД.
Для отображения информационной модели рассматриваемого процесса на
логической модели используются следующие сущности:
− «Сотрудники» - для хранения
информации о сотрудниках, которые работают в РОВД г. Климовичи;
− «День_рождения» - для хранения
информации о дате рождения сотрудников, количестве полных лет;
− «Спецзвания» - для ведения учёта
выслуги сотрудников и службе в Вооружённых силах;
− «Трудовой_стаж» - для хранения
информации о дате приёма на работу и учёта трудового стажа;
− «Отделы» - для фиксации информации об
отделах РОВД. Содержит название отдела, номер его штатного расписания, личный
номер и ФИО начальника отдела, количество штатных единиц и тарифную ставку;
− «Нормативные_документы» - для
хранения информации о нормативных документах, их типах и дат принятия;
− «Приказы» - для хранения информации о
приказах, ФИО ответственного за приказ и дату приказа;
− «Командировки» - хранит информацию о
местах командировок, сроках, дате и личных номерах камандированных сотрудников;
− «Увольнения» - для хранения
информация об уволенных сотрудниках;
− «Табель_отпусков» - хранит информацию
об отпусках сотрудников.
Связь между сущностями «Сотрудники» и «Командировки» неидентифицирующая,
не разрешающая присутствие нулей, т.к. номер командировки уникален. Тип связи:
один ко многим, т.к. в один сотрудник может побывать в нескольких
командировках. и 1 ко многимттствие нулейтрудники библиотеки.
Связь между сущностями «Сотрудники» и «Увольнения» идентифицирующая, т.к.
для ввода данных в таблицу «Увольнения» необходима информация из таблицы
«Сотрудники». Тип связи: один к одному, т.к. увольнение осуществляется только
для одного сотрудника.
Связь между сущностями «Сотрудники» и «Трудовой_стаж» идентифицирующая,
т.к. трудовой стаж подсчитываются для сотрудника, информация о котором
содержится в таблице «Сотрудники». Тип связи: один к одному, т.к. у одного
сотрудника может быть только один трудовой стаж.
Связь между сущностями «Сотрудники» и «Табель_отпусков» идентифицирующая,
т.к. отпуск оформляется для сотрудника, информация о котором находится в
таблице «Сотрудники». Тип связи: один ко многим, т.к. один сотрудник может
побывать в отпуске несколько раз.
Связь между сущностями «Сотрудники» и «Спецзвания» идентифицирующая, т.к.
для формирования спецзвания необходима информация о сотруднике. Тип связи: один
к одному, т.к. у одного сотрудника может быть только один трудовой стаж.
Связь между сущностями «Сотрудники» и «День_рождения» идентифицирующая,
т.к. для подсчета количества полных лет и контроля Дней рождений необходима
информация о сотрудниках. Тип связи: один к одному, т.к. у одного сотрудника
может быть только один День рождения.
Связь между сущностями «Сотрудники» и «Отделы» идентифицирующая, т.к.
каждый отдел состоит из сотрудников, информация о которых находится в таблице
«Сотрудники». Тип связи: один к одному, т.к. один сотрудник может работать
только в одном отделе.
Связь между сущностями «Сотрудники» и «Приказы» многие-ко-многим, т.к.
один сотрудник может быть ответственный на несколько приказов, а один приказ
может быть на несколько сотрудников.
Связь между сущностями «Отделы» и «Нормативные_документы»
многие-ко-многим, т.к. на один отдел может быть несколько нормативных
документов, а один нормативный документ может быть на несколько отделов.
ER-диаграмма
логического уровня приведена в графическом материале «ER-диаграмма логического уровня» (рисунок 13).
Рисунок 13 - ER-диаграмма
логического уровня
.2.2 ER - диаграмма физического уровня.
Ограничения доменов. Ограничения ссылочной целостности. Переопределение
триггеров. Индексирование отношений.
Физическая модель данных зависит от конкретной СУБД. В ней содержится
информация обо всех объектах БД. Одной и той же логической модели может
соответствовать несколько разных физических. В физической модели важно описать
всю информацию о конкретных физических объектах - таблицах, колонках, индексах,
процедурах.
Реализация ссылочной целостности:
− При изменении, удалении или
обновлении информации в таблице «Сотрудники» не должно ничего происходить в
таблице «Командировки», и наоборот;
− При удалении информации в таблице
«Приказы_сотрудников» (или в таблице «Приказы») в таблице «Сотрудники» ничего
не происходит, вставка и обновление возможно в том случае, если в таблице
«Сотрудники» существует кортеж с соответственным первичным ключом. При
удалении/обновлении информации из таблицы «Сотрудники» в таблице
«Приказы_сотрудников» (или «Приказы») удаляется/обновляется соответствующий
кортеж. При вставке кортежа в таблицу «Сотрудники», в таблице
«Приказы_сотрудников» («Приказы») ничего не происходит.
− При вставке/удалении/обновлении
кортежей в таблице «Увольнения» для поддержания ссылочной целостности не
происходят никакие действия в таблице «Сотрудники».
− Вставка или обновление данных в
таблице «Трудовой_стаж» возможно, только если в таблице «Сотрудники» существуют
кортежи с соответствующими первичными ключами. При удалении ничего не
происходит. При удалении или обновлении данных в таблице «Сотрудники» кортежи в
таблице «Трудовой_стаж» удаляются/обновляются соответственно.
− При вставке/удалении/обновлении
данных в таблице «Табель_отпусков» для поддержания ссылочной целостности не
происходят никакие действия в таблице «Сотрудники». При удалении или обновлении
данных в таблице «Сотрудники» кортежи в таблице «Табель_отпусков»
удаляются/обновляются соответственно.
− Вставка или обновление данных в
таблице «Спецзвания» возможно, только если в таблице «Сотрудники» существуют
кортежи с соответствующими первичными ключами. При удалении ничего не
происходит. При удалении или обновлении данных в таблице «Сотрудники» кортежи в
таблице «Спецзвания» удаляются/обновляются соответственно.
− Вставка или обновление данных в
таблице «День_рождения» возможно, только если в таблице «Сотрудники» существуют
кортежи с соответствующими первичными ключами. При удалении ничего не
происходит. При удалении или обновлении данных в таблице «Сотрудники» кортежи в
таблице «День_рождения» удаляются/обновляются соответственно.
− При вставке/удалении/обновлении
данных в таблице «Отделы» для поддержания ссылочной целостности не происходят
никакие действия в таблице «Сотрудники». При удалении или обновлении данных в
таблице «Сотрудники» кортежи в таблице «Отделы» удаляются/обновляются
соответственно.
− При удалении информации в таблице
«Нормативные_документы_отделов» (или в таблице «Нормативные_документы») в
таблице «Отделы» ничего не происходит, вставка и обновление возможно в том
случае, если в таблице «Отделы» существует кортеж с соответственным первичным
ключом. При удалении информации из таблицы «Отделы» в таблице
«Нормативные_документы_отделов» (или «Нормативные_документы») удаляется
соответствующий кортеж. При вставке кортежа в таблицу «Отделы», в таблице
«Нормативные_документы_отделов» («Нормативные_документы») ничего не происходит.
Для приложения были разработаны следующие триггеры:срабатывает при
удалении нормативного документа из таблицы. Все удаленные строки заносятся в
таблицу «DeletedItem», а также имя пользователя, удалившего строку, и
дату;срабатывает при добавлении в базу данных нового отдела и проверяет, чтобы
номер штатного расписания нового отдела был в пределах 1000<номер_штатного_расписания<2000.
Если номер не соответствует условию, появляется сообщение с
предупреждением;_komandirovka проверяет, чтобы при вводе данных в таблицу
«Командировки» год даты командировки «Дата_с» не был больше текущего года (например,
2015 год);_Tarif срабатывает при обновлении тарифной ставки в таблице «Отделы»
и выдает изменённую среднюю тарифную ставку по отделам;_Otpusk проверяет, чтобы
в таблицу «Табель_отпусков» не вносились увольнения, т.к. для этого существует
отдельная таблица;_Otdel проверяет, чтобы при добавлении нового отдела в
таблицу «Отделы» в поле «Количество_штатных_единиц» не было нуля. В противном
случае появляется сообщение с предупреждением;записывает в отдельную таблицу
«DeletedWorker» информацию о записях, удаленных из таблицы «Сотрудники», а
также имя пользователя, который удалил записи, и дату удаления;_Komandirovka
проверяет, чтобы при добавлении записи в таблицу «Командировки» было указано
место командировки;_speczvania проверяет, чтобы при добавлении записи втаблицу
«Спецзвания» было заполнено поле «Служба_в_ВС»;запрещает добавление новой
записи в таблицу «Сотрудники», если не заполнено хотя бы одно из полей.
ER-диаграмма
физического уровня приведена в графическом материале «ER-диаграмма физического уровня» (рисунок 14).
Для
приложения были разработаны следующие индексы:
) CREATE
CLUSTERED INDEX UvolУвольнения (Личный_номер)
Колонка
«Личный_номер» используется в представлении Dismiss в качестве критерия выборки
и в хранимой процедуре Dismissal. Тип индекса был выбран кластерный, чтобы
ускорить выборку данных.
) CREATE
NONCLUSTERED INDEX Trud
Рисунок 14 - ER-диаграмма
физического уровня
база данные аccess
ON
Трудовой_стаж (Стаж)
Колонка
«Стаж» используется в представлении Expierence и в хранимой процедуре
WorkerExperience. Тип индекса - некластерный, т.к. в таблице «Трудовой_стаж»
часто изменяются данные.
) CREATE
NONCLUSTERED INDEX TabТабель_отпусков (Количество_дней)
Колонка
«Количество_дней» используется в представлении Otpusk и в хранимой процедуре
Vacation. Тип индекса - некластерный, т.к. в таблице «Табель_отпусков» часто
изменяются данные.
) CREATE
NONCLUSTERED INDEX CpecСпецзвания (Выслуга)
Колонка
«Выслуга» используется в представлении Sluzba_v_VS и в хранимой процедуре SpecZvanie.
Тип индекса - некластерный, т.к. в таблице «Спецзвания» часто изменяются
данные.
5) CREATE
NONCLUSTERED INDEX SotrСотрудники (Личный_номер)
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. Тип индекса - некластерный, т.к. в таблице «Сотрудники» часто
изменяются данные.
) CREATE
CLUSTERED INDEX PrSПриказы_сотрудников (Номер_приказа)
Колонка
«Номер_приказа» используется в представлении Prikaz и в хранимой процедуре
Orders. Тип индекса - кластерный, чтобы ускорить выборку данных.
7) CREATE
NONCLUSTERED INDEX Otd
Отделы (Номер_штатного_расписания)
Колонка
«Номер_штатного_расписания» используется в представлениях Basic Wage,
Raspisanie и в хранимых процедурах BasicWageRateUp, NewDepartment, Schedule.
Тип индекса - некластерный, т.к. в таблице «Отделы» часто изменяются данные.
8) CREATE NONCLUSTERED INDEX NormD
ON Нормативные_документы
(Номер_нормативного_документа)
GO
Колонка
«Номер_нормативного_документа» используется в представлениях Docs, Ukaz и в хранимой процедуре Documents. Тип индекса - некластерный, т.к. в
таблице «Нормативные_документы» часто изменяются данные.
9) CREATE
NONCLUSTERED INDEX KomКомандировки (Номер_командировки)
Колонка
«Номер_командировки» используется в представлениях Komandirovki, Trip и в хранимых процедурах BusinessTrip, City. Тип
индекса - некластерный, т.к. в таблице «Командировки» часто изменяются данные.
10) CREATE
NONCLUSTERED INDEX DR
ON День_рождения (Личный_номер)
GO
Колонка
«Личный_номер» используется в представлении BD и в хранимых процедурах BDay, Search. Тип индекса -
некластерный, т.к. в таблице «Командировки» часто изменяются данные.
.2.3
Определение представлений, хранимых процедур серверной компоненты. ER-диаграмма в режиме отображения
представлений
Для приложения были разработаны следующие представления:
Dolznost предоставляет информацию обо всех начальниках РОВД (из таблицы
«Сотрудники»);
BD
предоставляет информацию о сотрудниках, у кого будет День рождения в следующем
месяце (из таблиц «День_рождения» и «Сотрудники»);
Dismiss
предоставляет информацию об уволенных сотрудниках (из таблицы «Увольнения»);
Raspisanie предоставляет информацию о штатном расписании (из таблицы «Отделы»);
Prikaz
предоставляет информацию о приказах и ответственных за их выполнение (из
таблицы «Приказы»);
Otpusk предоставляет
информацию обо всех отпусках (из таблиц «Табель_отпусков» и «Сотрудники»);
Ukaz
предоставляет информацию о нормативных документах типа «Закон РБ» (из таблицы
«Нормативные_документы»);
Trip
предоставляет информацию о всех командировках, которая также включает в себя
личный номер сотрудника, его ФИО, отдел, должность (из таблиц «Командировки» и
«Сотрудники»);
Sluzba_v_VS предоставляет информацию о сотрудниках, которые служили в ВС
(из таблиц «Сотрудники» и «Спецзвания»);
Otdel
предоставляет информацию обо всех сотрудниках, которые работают в отделе ООПиП
(из таблицы «Сотрудники);
Obrazovanie предоставляет информацию о сотрудниках с высшим образованием (из таблицы
«Сотрудники»);
Komandirovki предоставляет информацию обо всех командировках прошлого года, также
информацию о командированном сотруднике: ФИО, должность, отдел (из таблиц
«Командировки» и «Сотрудники»);
Experience предоставляет информацию о трудовых стажах всех сотрудников (из таблиц
«Трудовой_стаж» и «Сотрудники»);
Docs
предоставляет информацию о нормативных документах (из таблицы
«Нормативные_документы»);
Basic Wage предоставляет
информацию об отделах, где тарифная ставка выше 220000 (из таблицы «Отделы»).
Для приложения были разработаны следующие хранимые процедуры:
− NewDepartment для вставки новых данных в таблицу
«Отделы», NewWorker − в таблицу «Сотрудники»;
− DeleteWorker для удаления данных из таблицы
«Сотрудники»;
− UpdateWorker для обновления записей в таблице «Сотрудники», BasicWageRateUp - в таблице «Отделы»;
− Search (по возрасту) для поиска записей в
таблице «Сотрудники» и «День_рождения», SearchWorker (на фамилии или началу
фамилии) Search (по возрасту) в таблице«Сотрудники»;
− BDay для предоставления информации о дне
рождении по личному номеру сотрудника;
− BusinessTrip для просмотра
командировок определенного сотрудника (по личному номеру);
− City для поиска всех командировок в
определенном городе (город передается через параметр);
− Dismissal для просмотра уволенных сотрудников;
− Documents поиск всех нормативных документов отдела
(название отдела передается через параметр);
− GetWorkerInfo для просмотра личного дела
сотрудника по его личному номеру;
− Lists для просмотра штатной расстановки
определенного отдела (название отдела передается через параметр);
− Orders поиск приказа по его номеру;
− Position для просмотра сотрудников,
занимающих определенную должность (должность передается через параметр);
− Schedule для просмотра штатного
расписания отдела по его номеру;
− SpecZvanie для просмотра сотрудников, которые
служили в ВС;
− Vacation для просмотра отпусков
определенного сотрудника (по личному номеру);
− WorkerExperience для подсчёта трудового стажа
определенного сотрудника (по личному номеру).
.3 Верификация спроектированной логической модели
После разработки информационной модели ее следует связать с
функциональной моделью. Такая связь гарантирует завершенность анализа,
гарантирует, что есть источники данных (сущности) для всех работ. Связывание
моделей способствует согласованности, корректности и завершенности анализа.
Стрелки в функциональной модели обозначают некоторую информацию, использующуюся
в моделируемой системе. В информационной модели на логическом уровне информация
изображается в виде сущностей. Сущности состоят из совокупностей экземпляров
сущностей (кортежи отношений). К информационной модели предъявляется требование
нормализации, что должно обеспечить компактность и непротиворечивость хранения
данных. Информация, которая моделируется одной стрелкой в функциональной
модели, может содержаться в нескольких сущностях и атрибутах информационной
модели. На функциональной модели могут присутствовать различные стрелки,
изображающие одни и те же данные. Информация о таких стрелках находится в одних
и тех же сущностях. Следовательно, одной и той же стрелке в функциональной
модели могут соответствовать несколько сущностей в информационной модели и,
наоборот, одной сущности может соответствовать несколько стрелок.
Работы в функциональной модели могут создавать или изменять данные,
которые соответствуют входящим и выходящим стрелкам. Они могут воздействовать
как целиком на сущности (создавая и модифицируя экземпляры сущности), так и на
отдельные атрибуты сущности.
Таблица 1 - Отчет о верификации модели
|
Arrow Name
|
Entity Name
|
Attribute Name
|
|
Данные о сотрудниках
|
Адрес
|
|
|
|
Город
|
|
|
Дом
|
|
|
Квартира
|
|
|
Личный_номер
|
|
|
Область
|
|
|
Страна
|
|
|
Улица
|
|
День_рождения
|
|
|
|
Дата_рождения
|
|
|
Количество_полных_лет
|
|
|
Личный_номер
|
|
Сотрудники
|
|
|
|
Должность
|
|
|
Звание
|
|
|
Имя
|
|
|
Личный_номер
|
|
|
Образование
|
|
|
Отчество
|
|
|
Фамилия
|
|
Нормативные документы
|
Нормативные_документы
|
|
|
|
Дата_документа
|
|
|
Номер_нормативного_документа
|
|
|
Тип
|
|
Приказы и отчёты
|
Приказы
|
|
|
|
Дата_приказа
|
|
|
Номер_приказа
|
|
|
Ответственный
|
|
Штатное расписание
|
Командировки
|
|
|
|
Дата_с
|
|
|
Личный_номер
|
|
|
Место
|
|
|
Номер_командировки
|
|
|
Срок
|
|
Отделы
|
|
|
|
Имя_начальника
|
|
|
Количество_штатных_единиц
|
|
|
Личный_номер
|
|
|
Название_отдела
|
|
|
Номер_штатного_расписания
|
|
|
Отчество_начальника
|
|
|
Примечание
|
|
|
Тарифная ставка
|
|
|
Телефон
|
|
|
Факс
|
|
|
Фамилия_начальника
|
|
Спецзвания
|
|
|
|
Выслуга
|
|
|
Личный_номер
|
|
|
Служба_в_ВС
|
|
Табель_отпусков
|
|
|
|
Дата_с
|
|
|
Количество_дней
|
|
|
Личный_номер
|
|
|
Номер_табеля
|
|
|
Тип_отпуска
|
|
Трудовой_стаж
|
|
|
|
Дата_приёма_на_работу
|
|
|
Личный_номер
|
|
|
Стаж
|
|
Увольнения
|
|
|
|
Дата_документа
|
|
|
Личный_номер
|
|
|
Номер_документа
|
|
|
Причина
|
3 Реализация системы
3.1 T-SQL-определения регламентированных запросов
1. Вывод информации об отделах, где тарифная ставка выше 220000
CREATE VIEW Basic Wage
ASНазвание_отдела,
Номер_штатного_расписания, Тарифная_ставкаdbo.Отделы(Тарифная_ставка >
220000) // Выборка строк таблицы, где значения в колонке «Тарифная_ставка»
больше 220000
. Предоставление информации о сотрудниках, у кого будет День
рождения в следующем месяце
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()))) // Выборка строк таблицы, где
месяц, указанный в колонке «Дата_рождения», совпадает со следующим месяцем, в
соответствии с текущей датой, установленной на данном сервере
. Предоставление информации об уволенных сотрудниках
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.Сотрудники.Личный_номер
. Предоставление информации о нормативных документах
CREATE VIEW Docs
AS
SELECT dbo.Нормативные_документы.Номер_нормативного_документа,
dbo.Нормативные_документы.Тип, DATEPART(YEAR, dbo.Нормативные_документы.Дата_документа)
AS [Год принятия]
// DATEPART - функция, которая возвращает целое
число, представляющее указанный компонент YEAR указанной даты «Дата_документа»
FROM dbo.Нормативные_документы
INNER JOIN dbo.Нормативные_документы_отделов ON dbo.Нормативные_документы.Номер_нормативного_документа = dbo.Нормативные_документы_отделов.Номер_нормативного_документа
. Предоставление информации обо всех начальниках РОВД
CREATE VIEW Dolznost
ASНазвание_отдела,
Личный_номер, Фамилия, Имя, Отчество, Должность, Званиеdbo.Сотрудники(Должность
LIKE 'начальник') // Выборка строк таблицы, где значение колонки «Должность»
совпадает с указанным значением «начальник»
. Предоставление информации о трудовых стажах сотрудников
CREATE VIEW Expierence
ASdbo.Сотрудники.Личный_номер,
dbo.Сотрудники.Фамилия, dbo.Сотрудники.Имя, dbo.Сотрудники.Должность,
dbo.Сотрудники.Название_отдела, CAST(dbo.Трудовой_стаж.Стаж AS varchar(50)) AS
Стаж // CAST - функция преобразования типа данных
в varchar(50) значений колонки «Трудовой_стаж»
FROM dbo.Сотрудники INNER JOIN dbo.Трудовой_стаж ON dbo.Сотрудники.Личный_номер = dbo.Трудовой_стаж.Личный_номер
. Предоставление информации обо всех командировках в прошлом году
CREATE VIEW Komandirovki
ASdbo.Сотрудники.Название_отдела,
dbo.Сотрудники.Личный_номер, LTRIM(dbo.Сотрудники.Фамилия) AS Фамилия,
dbo.Сотрудники.Имя, dbo.Сотрудники.Отчество, // LTRIM - функция, которая возвращает значение колонки
«Фамилия» после удаления начальных пробелов.Сотрудники.Должность, dbo.Командировки.Номер_командировки,
dbo.Командировки.Дата_сdbo.Сотрудники INNER JOIN dbo.Командировки ON
dbo.Сотрудники.Личный_номер = dbo.Командировки.Личный_номер
WHERE
(YEAR(dbo.Командировки.Дата_с) = YEAR(DATEADD(YEAR, - 1, GETDATE()))) // Выборка строк таблицы, где год в колонке
«Дата_с» совпадает с прошлым годом, в соответствии с текущей датой,
установленной на данном сервере
. Вывод в таблицу всех сотрудников, имеющий высшее образование
CREATE VIEW Obrazovanie
ASЛичный_номер,
LTRIM(Фамилия) AS Фамилия, Имя, Отчество, // LTRIM - функция, которая возвращает значение колонки
«Фамилия» после удаления начальных пробелов
Должность, Звание, UPPER(Образование) AS
Образованиеdbo.Сотрудники(Образование LIKE '%высшее') // Выборка строк таблицы,
где значение колонки «Образование» совпадает с указанным значением «высшее»
. Вывод в таблицу сотрудников, работающих в отделе ООПиП
CREATE VIEW Otdel
ASНазвание_отдела,
Личный_номер, LTRIM(Фамилия) AS
// LTRIM - функция, которая возвращает
значение колонки «Фамилия» после удаления начальных пробелов
Фамилия, Имя, Отчество, Должность, Званиеdbo.Сотрудники(Название_отдела
LIKE '%ООПиП') // Выборка строк таблицы, где значение колонки «Название_отдела»
совпадает с указанным значением «ООПиП»
. Предоставляет информацию обо всех отпусках
CREATE VIEW Otpusk
ASdbo.Сотрудники.Личный_номер,
dbo.Сотрудники.Название_отдела, dbo.Сотрудники.Фамилия, dbo.Сотрудники.Имя,
dbo.Сотрудники.Отчество, dbo.Сотрудники.Должность,
dbo.Табель_отпусков.Номер_табеля, dbo.Табель_отпусков.Тип_отпуска, dbo.Табель_отпусков.Дата_с,
DATEDIFF(day, dbo.Табель_отпусков.Дата_с, GETDATE()) AS [Количество дней] //
DATEDIFF - функция, которая возвращает интервал времени day, прошедшего от указанной даты Дата_с
до текущей даты, установленной на данном сервере, GETDATE - возвращает текущую дату, установленную на данном
сервере
FROM dbo.Табель_отпусков
INNER JOIN dbo.Сотрудники ON dbo.Табель_отпусков.Личный_номер =
dbo.Сотрудники.Личный_номер
. Предоставление информации о приказах и сотрудниках,
ответственных за их выполнение
CREATE VIEW Prikaz
ASdbo.Приказы.Номер_приказа,
dbo.Приказы_сотрудников.Название_отдела, RTRIM(dbo.Приказы.Ответственный) AS
Ответственный, // RTRIM - функция,
которая возвращает значение колонки «Фамилия» после удаления пробелов из конца
строки.Приказы.Дата_приказаdbo.Приказы INNER JOIN dbo.Приказы_сотрудников ON
dbo.Приказы.Номер_приказа = dbo.Приказы_сотрудников.Номер_приказа
. Предоставление информации о штатном расписании
CREATE VIEW Raspisanie
ASНомер_штатного_расписания,
Название_отдела, RTRIM(Фамилия_начальника) + SPACE(2) + LTRIM(Имя_начальника) +
SPACE(2) + LTRIM(Отчество_начальника) AS Начальник, // RTRIM - функция, которая возвращает значение колонки
«Фамилия» после удаления пробелов из конца строки, LTRIM - функция, которая возвращает значение колонки
«Фамилия» после удаления начальных пробелов, SPACE - функция, которая возвращает строку пробелов
Количество_штатных_единиц, Тарифная_ставка, Примечаниеdbo.Отделы
. Вывод в таблицу сотрудников, которые служили в ВС
CREATE VIEW Sluzba_v_VS
SELECT dbo.Сотрудники.Личный_номер, dbo.Сотрудники.Название_отдела,
dbo.Сотрудники.Фамилия, dbo.Сотрудники.Имя,
dbo.Сотрудники.Отчество,(dbo.Сотрудники.Должность) AS Expr1,
dbo.Сотрудники.Звание,
// UPPER - функция преобразования текста в
верхний регистр.Спецзвания.Выслугаdbo.Сотрудники INNER JOIN dbo.Спецзвания ON
dbo.Сотрудники.Личный_номер = dbo.Спецзвания.Личный_номер AND
dbo.Сотрудники.Название_отдела =
dbo.Спецзвания.Название_отдела(dbo.Спецзвания.Служба_в_ВС = 'да') // Выборка
строк таблицы, где значения в колонке «Служба_в_ВС» равны «да»
. Предоставляет информацию обо всех командировках
CREATE VIEW Trip
AS
SELECT dbo.Сотрудники.Личный_номер,
dbo.Сотрудники.Название_отдела, dbo.Сотрудники.Фамилия, dbo.Сотрудники.Имя, dbo.Сотрудники.Отчество, dbo.Сотрудники.Должность, dbo.Командировки.Номер_командировки, dbo.Командировки.Место, CONVERT(VARCHAR(11), dbo.Командировки.Дата_с,
106) // CONVERT - функция преобразования типа данных
в varchar(11) значений колонки «Дата_с», 106 -
формат даты дд мм гггг
AS
Дата_с, dbo.Командировки.Срок
FROM dbo.Командировки INNER JOIN dbo.Сотрудники ON dbo.Командировки.Личный_номер
= dbo.Сотрудники.Личный_номер
. Предоставляет информацию о нормативных документах типа «Закон
РБ»
CREATE VIEW Ukaz
ASНомер_нормативного_документа,
REPLACE('Закон РБ', 'РБ', 'Республики Беларусь') AS Тип, Дата_документа // REPLACE - функция замены всех вхождений
значения «РБ» в значении «Закон РБ» на «Республики
Беларусь»dbo.Нормативные_документы
WHERE
(Тип LIKE '%Закон РБ') // Выборка строк
таблицы, где значение колонки «Тип» совпадает с указанным значением «Закон_РБ»
3.2 T-SQL-определения триггеров
. Триггер, который будет срабатывать при удалении нормативного документа
из таблицы «Нормативные_документы_отделов». Все удаленные строки будут
заноситься в таблицу «DeletedItem», а также имя пользователя, удалившего
строку, и дату
CREATE TABLE DeletedItem
([Номер_нормативного_документа] [int] NOT NULL,
[Название_отдела] [varchar] (100) NULL,
[Имя_пользователя] [varchar](50) NULL,
[Дата_удаления][datetime] NULL )[PRIMARY] TRIGGER deletedBy
FOR DELETE // Триггер
будет срабатывать на удаление из таблицы «Нормативные_документы_отделов»INTO
DeletedItem
(Номер_нормативного_документа, Название_отдела, Имя_пользователя,
Дата_удаления)Номер_нормативного_документа, Название_отдела,SYSTEM_USER, GETDATE() // SYSTEM_USER -
вставка в таблицу значение текущего имени входа, GETDATE − возвращает текущую дату, установленную на данном
сервереdeleted // Deleted -
временная таблица, куда заносятся удаляемые данные
. Срабатывает при добавлении в базу данных нового отдела и проверяет,
чтобы номер штатного расписания нового отдела был в пределах
1000<номер_штатного_расписания<2000. Если номер не соответствует условию,
появляется сообщение с предупреждением
CREATE TRIGGER
NomerOtdela
ON
Отделы
FOR INSERT // Триггер
будет срабатывать на вставку в таблице «Отделы»
AS
DECLARE
@@f int // Объявление переменной
Set @@f=1000
IF NOT EXISTS (SELECT
* FROM Отделы, inserted // Проверка на отсутствие строк в
таблице
WHERE
Отделы.Номер_штатного_расписания = inserted.Номер_штатного_расписания)
Set @@f=0EXISTS (SELECT * FROM Отделы, inserted inserted.Номер_штатного_расписания>2000 OR inserted.Номер_штатного_расписания<1000)
// Проверка при вводе строки в таблицу, чтобы значение в колонке
«Номер_штатного_расписания» не был больше 2000 и меньше 1000
Set @@f=0@@f=0UPPER ('Ошибка! Проверьте номер штатного расписания.')
// Предупреждающее сообщение с преобразованием в верхний регистр
ROLLBACK TRANSACTION //
Откат транзакции
END
. Будет проверять, чтобы при вводе данных в таблицу «Командировки» год
даты командировки «Дата_с» не был больше текущего года (например, 2015 год)
CREATE TRIGGER proverka_komandirovkaКомандировки INSERT // Триггер будет срабатывать на вставку в таблице
«Командировки»
ASEXISTS (SELECT * FROM Командировки, insertedinserted.Дата_с>YEAR(DATEADD(YEAR, +1,
GETDATE())))
// Проверка при вводе строки в таблицу, чтобы значение года в колонке
«Дата_с» не было равно год+1 от текущей даты, установленной на данном сервере
BEGIN
PRINT
'Неверно введена дата командировки!' // Предупреждающее сообщение
ROLLBACK TRANSACTION //
Откат транзакции
END
. Будет срабатывать при обновлении тарифной ставки в таблице «Отделы» и
выдавать изменённую среднюю тарифную ставку по отделам
CREATE TRIGGER
Upd_Tarif
ON
Отделы
FOR UPDATE // Триггер
будет срабатывать на обновление в таблице «Отделы»
ASUPDATE(Тарифная_ставка) 'Тарифная ставка повышена!' // Предупреждающее сообщение
SELECT AVG(Тарифная_ставка)
AS 'Изменённая средняя тарифная ставка'
// AVG - функция, которая возвращает
среднее значение от всех значений в колонке «Тарифная_ставка»
FROM
Отделы
END
. Триггер, который будет проверять, чтобы в таблицу «Табель_отпусков» не
вносились увольнения, т.к. для этого существует отдельная таблица
CREATE TRIGGER Ins_OtpuskТабель_отпусков INSERT // Триггер будет срабатывать на вставку данных в
таблицу «Табель_отпусков»
ASEXISTS (SELECT * FROM Табель_отпусков, inserted inserted.Тип_отпуска = 'увольнение') //
Проверка при вводе строки в таблицу, чтобы значение в колонке «Типа_отпуска» не
было «Увольнение»
BEGIN
PRINT
'Ошибка! Проверьте тип отпуска.' // Предупреждающее сообщение
SELECT CURRENT_TIMESTAMP // CURRENT_TIMESTAMP - функция возвращающая значение текущей даты, установленной на данном
сервере
ROLLBACK TRANSACTION //
Откат транзакции
END
. Триггер проверяет, чтобы при добавлении нового отдела в таблицу
«Отделы» в поле «Количество_штатных_единиц» не было нуля. В противном случае
появляется сообщение с предупреждением
CREATE TRIGGER
Ins_Otdel
ON
Отделы
FOR INSERT // Триггер
будет срабатывать на вставку в таблицу «Отделы»
ASEXISTS (SELECT * FROM Отделы, inserted inserted.Количество_штатных_единиц = 0) //
Проверка при вводе строки в таблицу, чтобы значение в колонке
«Количество_штатных_единиц» не было равно 0
BEGIN
PRINT
'Ошибка! В отделе не может работать 0 человек.'
// Предупреждающее сообщение
SELECT GETUTCDATE() AS 'Дата:' // GETUTCDATE - функция, возвращающая значение
текущей даты, установленной на данном сервере
ROLLBACK TRANSACTION //
Откат транзакции
END
. Триггер, который записывает в отдельную таблицу «DeletedWorker»
информацию о записях, удаленных из таблицы «Сотрудники», а также имя
пользователя, который удалил записи, и дату удаления
CREATE TABLE DeletedWorker (
[Личный_номер] [int] NOT NULL ,
[Фамилия] [varchar] (50) NULL ,
[Имя] [varchar] (50) NULL ,
[Отчество] [varchar] (50) NULL ,
[Образование][varchar] (100) NULL,
[Должность] [varchar] (100) NULL ,
[Звание] [varchar](100) NULL ,
[Адрес_город][varchar] (50) NULL,
[Адрес_улица][varchar] (50) NULL,
[Адрес_дом][int] NOT NULL,
[Адрес_квартира][varchar] (20) NULL,
[Название_отдела][varchar] (100) NOT NULL,
[Имя_пользователя] [varchar] (50) NULL ,
[Дата_удаления] [datetime] NULL )
ON [PRIMARY]TRIGGER DelWorkerСотрудники DELETE // Триггер будет срабатывать на удаление данных из таблицы «Сотрудники»
ASINTO DeletedWorker // Вставить удаляемые данные
в таблицу DeletedWorker
(Личный_номер , Фамилия , Имя, Отчество, Образование, Должность, Звание,
Адрес_город, Адрес_улица, Адрес_дом,
Адрес_квартира, Название_отдела,
Имя_пользователя,Дата_удаления)Личный_номер , Фамилия , Имя, Отчество,
Образование, Должность, Звание, Адрес_город, Адрес_улица,
Адрес_дом, Адрес_квартира, Название_отдела, SYSTEM_USER, GETDATE()deleted
// Deleted - временная таблица, куда заносятся
удаляемые данные@@ROWCOUNT // ROWCOUNT - функция, которая возвращает число
затронутых при выполнении транзакции строк
. Триггер, который проверяет, чтобы при добавлении записи в таблицу
«Командировки» было указано место командировки
CREATE TRIGGER Ins_KomandirovkaКомандировки INSERT // Триггер будет срабатывать на вставку данных в таблицу «Командировки»
ASEXISTS (SELECT * FROM Командировки, inserted inserted.Место =
'') // Проверка при вводе строки в таблицу, чтобы значение в колонке «Место» не
было пустое
BEGIN
PRINT
'Ошибка! Необходимо указать место командировки.'
// Предупреждающее сообщение
SELECT
@@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name' // SPID - функция, которая возвращает идентификатор сеанса
для текущего процесса, SYSTEM_USER - логин текущего пользователя, USER - имя текущего
пользователяTRANSACTION // Откат транзакции
. Триггер, который проверяет, чтобы при добавлении записи втаблицу
«Спецзвания» было заполнено поле «Служба_в_ВС»
CREATE TRIGGER proverka_speczvaniaСпецзвания INSERT // Триггер будет срабатывать на вставку данных в
таблицу «Спецзвания»
ASEXISTS (SELECT * FROM Спецзвания, inserted inserted.Служба_в_ВС = '') // Проверка при
вводе строки в таблицу, чтобы значение в колонке «Служба_в_ВС» не было пустое
BEGIN
PRINT
'Отметьте выслугу!' // Предупреждающее сообщение
SELECT SESSION_USER AS 'Session
User' // SESSION_USER -
функция, которая возвращает имя пользователя текущей сессии
ROLLBACK TRANSACTION //
Откат транзакции
END
. Триггер, запрещающий добавление новой записи в таблицу «Сотрудники»,
если не заполнено хотя бы одно из полей
CREATE TRIGGER
Worker
ON
Сотрудники
FOR INSERT // Триггер
будет срабатывать на вставку данных в таблицу «Сотрудники»
ASEXISTS (SELECT * FROM Сотрудники, insertedinserted.Фамилия
= '' OR inserted.Имя = '' OR inserted.Отчество = ''inserted.Образование = '' OR inserted.Адрес_город = '' OR inserted.Адрес_улица = '')
// Проверка при вводе строки в таблицу, чтобы значения в колонках» не
было пустыми
BEGIN
PRINT
'Заполнены не все поля!' // Предупреждающее сообщение
SELECT
@@SERVERNAME AS 'Server Name' // SERVERNAME - функция, возвращающая информацию
об имени локального сервера
ROLLBACK TRANSACTION //
Откат транзакции
END
.3 T-SQL-определения хранимых процедур
1. Повышение тарифной ставки в отделе
CREATE PROC BasicWageRateUp
@dept varchar(100)
UPDATE
Отделы // Обновление таблицы «Отделы»
SET
Тарифная_ставка = Тарифная_ставка * 1.9
WHERE
Название_отдела = @dept // Выборка
строк таблицы, где значение колонки «Название_отдела» соответствует введенному
значению @dept
. Информация о дне рождении по личному номеру сотрудника
CREATE PROC BDay
@id int
Сотрудники.Личный_номер, Сотрудники.Название_отдела, Сотрудники.Фамилия,
Сотрудники.Имя,
Сотрудники.Отчество, Сотрудники.Должность, День_рождения.Дата_рождения,
DATEDIFF(year,dbo.День_рождения.Дата_рождения,GETDATE()) AS
'Полных лет'
// DATEDIFF - функция, которая возвращает интервал времени year, прошедшего от указанной даты
Дата_рождения до текущей даты, установленной на данном сервере, GETDATE - возвращает текущую дату,
установленную на данном сервере
FROM
День_рождения, Сотрудники
WHERE
День_рождения.Личный_номер = @id AND День_рождения.Личный_номер =
Сотрудники.Личный_номер
. Просмотр командировки определенного сотрудника по личному номеру
CREATE PROC BusinessTrip
@id int
Сотрудники.Личный_номер, Сотрудники.Название_отдела, Сотрудники.Фамилия,
Сотрудники.Имя,
Сотрудники.Отчество, Сотрудники.Должность,
Командировки.Номер_командировки, Командировки.Место,
Командировки.Дата_с, Командировки.Срок
FROM
Командировки, Сотрудники
WHERE
Командировки.Личный_номер = @id AND Командировки.Личный_номер =
Сотрудники.Личный_номер
. Поиск всех командировок в определенном городе
CREATE PROC City
@city varchar(100)
SELECT
Сотрудники.Название_отдела, Сотрудники.Личный_номер, Сотрудники.Фамилия,
Сотрудники.Имя,
Сотрудники.Отчество, Сотрудники.Должность,Сотрудники.Звание,
Командировки.Номер_командировки,
Командировки.Дата_с
FROM
Сотрудники, Командировки
WHERE
Сотрудники.Личный_номер = Командировки.Личный_номер AND Командировки.Место = @city // Выборка строк таблицы, где значение колонки
«Место» соответствует введенному значению @city
5. Удаление данных о сотруднике из таблицы «Сотрудники»
CREATE PROC DeleteWorker
@id intEXISTS (SELECT * FROM Сотрудники WHERE Личный_номер = @id) Сотрудники // Проверка на наличие
нужной строки в таблице
WHERE
Личный_номер = @id // Выборка
строк таблицы, где значение колонки «Личный_номер» соответствует введенному
значению @id
6. Поиск уволенного сотрудника по личному номеру
CREATE PROC Dismissal
@id int
Сотрудники.Личный_номер, Сотрудники.Название_отдела, Сотрудники.Фамилия,
Сотрудники.Имя,
Сотрудники.Отчество, Сотрудники.Должность, Сотрудники.Звание,
Увольнения.Номер_документа,
Увольнения.Дата_документа, Увольнения.Причина
FROM
Увольнения, Сотрудники
WHERE
Увольнения.Личный_номер = @id AND Увольнения.Личный_номер =
Сотрудники.Личный_номер // Выборка строк таблицы, где значение колонки
«Личный_номер» соответствует введенному значению @id
7. Поиск всех нормативных документов отдела
CREATE PROC Documents
@dept varchar(100)
SELECT
Нормативные_документы.Номер_нормативного_документа, Нормативные_документы.Тип,
Нормативные_документы.Дата_документа
FROM
Нормативные_документы, Нормативные_документы_отделов
WHERE
Нормативные_документы.Номер_нормативного_документа =
Нормативные_документы_отделов.Номер_нормативного_документа
AND
Название_отдела = @dept // Выборка
строк таблицы, где значение колонки «Название_отдела» соответствует введенному
значению @dept
8. Просмотр личного дела сотрудника по его личному номеру
CREATE PROC GetWorkerInfo
@id int
* FROM Сотрудники WHERE Личный_номер = @id
// Выборка строк таблицы, где значение колонки «Личный_номер»
соответствует введенному значению @id
9. Просмотр штатной расстановки отдела
CREATE PROC Lists
@dept varchar(100)
SELECT
Сотрудники.Название_отдела, Сотрудники.Личный_номер, Сотрудники.Фамилия,
Сотрудники.Имя, Сотрудники.Отчество, Сотрудники.Должность, Сотрудники.Звание
FROM
Сотрудники
WHERE
Сотрудники.Название_отдела = @dept
// Выборка строк таблицы, где значение колонки «Название_отдела» соответствует
введенному значению @deptBY Фамилия // Сортирует данные, возвращаемые запросом, по фамилии
. Добавление нового отдела в таблицу «Отделы»
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
INSERT INTO Отделы
(Название_отдела, Номер_штатного_расписания, Фамилия_начальника,
Имя_начальника,
Отчество_начальника, Количество_штатных_единиц, Тарифная_ставка,
Примечание, Телефон, Факс) // Добавление новой строки в таблицу «Отделы»
VALUES
(@id, @dept, @name,
@oName, @lastName, @kol, @tarif,
@prim, @tel, @fax)
// Задает набор выражений значений строки
. Добавление нового сотрудника
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) INTO
Сотрудники (Личный_номер, Фамилия, Имя, Отчество, Образование, Должность,
Звание, Адрес_город, Адрес_улица, Адрес_дом, Адрес_квартира, Название_отдела)
// Добавление новой строки в таблицу «Сотрудники»
VALUES
(@id, @name, @oName,
@lastName, @education, @position, @rank, @town,
@street, @d, @kv,@dept) ) // Задает набор выражений
значений строки
. Поиск приказа по его номеру
CREATE PROC Orders
@id int
Приказы.Номер_приказа, Приказы_сотрудников.Название_отдела,
Приказы.Ответственный, Приказы.Дата_приказа
FROM
Приказы, Приказы_сотрудников
WHERE
Приказы.Номер_приказа = Приказы_сотрудников.Номер_приказа AND Приказы.Номер_приказа = @id
// Выборка строк таблицы, где значение колонки «Номер_приказа»
соответствует введенному значению @id
13. Поиск сотрудников, занимающий определенную должность
CREATE PROC Position
@position varchar(100)
SELECT
Сотрудники.Название_отдела, Сотрудники.Личный_номер, Сотрудники.Фамилия,
Сотрудники.Имя,
Сотрудники.Отчество, Сотрудники.Должность,Сотрудники.Звание
FROM
Сотрудники
WHERE
Должность = @position // Выборка строк таблицы, где
значение колонки «Должность» соответствует введенному значению @position
14. Поиск отдела по номеру его штатного расписания
CREATE PROC Schedule
@id int
Отделы.Номер_штатного_расписания, Отделы.Название_отдела, Отделы.Фамилия_начальника,
Отделы.Имя_начальника, Отделы.Отчество_начальника,
Отделы.Количество_штатных_единиц, Отделы.Тарифная_ставка, Отделы.Примечание
FROM
Отделы
WHERE
Номер_штатного_расписания = @id //
Выборка строк таблицы, где значение колонки «Номер_штатного_расписания»
соответствует введенному значению @id
15. Поиск сотрудников по возрасту
CREATE PROC Search
@i int
SELECT
Сотрудники.Личный_номер, Сотрудники.Фамилия, Сотрудники.Имя,
Сотрудники.Отчество, Сотрудники.Должность,
День_рождения.Дата_рождения, День_рождения.Количество_полных_лет
FROM
Сотрудники, День_рождения
WHERE
Сотрудники.Личный_номер = День_рождения.Личный_номер
AND ISNUMERIC(День_рождения.Количество_полных_лет)<>0
AND
День_рождения.Количество_полных_лет >= @i; // Выборка строк таблицы, где значение колонки
«Количество_полных_лет» больше или равно введенному значению @i; ISNUMERIC проверяет, чтобы значение
«Количество_полных_лет» не было равно нулю
. Поиск сотрудника по фамилии или началу фамилии
CREATE PROC SearchWorker
@name varchar(100)
SELECT
* FROM Сотрудники
WHERE
Фамилия LIKE @name // Выборка строк таблицы, где значение колонки
«Фамилия» соответствует введенному значению @name
17. Просмотра сотрудников, которые служили в ВС
CREATE PROC SpecZvanie
@vs varchar (20)
AS@vs = 'да' // Установить значение переменной, равной значению
«да»Сотрудники.Личный_номер, Сотрудники.Фамилия, Сотрудники.Имя,
Сотрудники.Отчество, Сотрудники.ДолжностьСпецзвания,
СотрудникиСпецзвания.Личный_номер=Сотрудники.Личный_номер AND Служба_в_ВС = @vs
// Выборка строк таблицы, где значение колонки «Служба_в_ВС» соответствует
значению переменной @vs
. Обновление данных о сотруднике
CREATE PROC
UpdateWorker
@id int,
@position varchar (100),
@rank varchar (100)
IF EXISTS (SELECT * FROM
Сотрудники WHERE Личный_номер = @id ) // Проверка на наличие нужной
строки в таблице «Сотрудники»Сотрудники // Обновление таблицы
«Сотрудники»Должность = @position // Установить значение в колонке «Должность»,
равное введенному значению переменной @positionЛичный_номер = @id // Выборка строк
таблицы, где значение колонки «Личный_номер» соответствует введенному значению
@idСотрудникиЗвание = @rank //
Установить значение в колонке «Звание», равное введенному значению переменной @
rankЛичный_номер = @id
19. Просмотр отпусков сотрудника
CREATE PROC Vacation
@id int
ASСотрудники.Личный_номер, Сотрудники.Название_отдела,
Сотрудники.Фамилия, Сотрудники.Имя,
Сотрудники.Отчество, Сотрудники.Должность, Табель_отпусков.Номер_табеля,
Табель_отпусков.Тип_отпуска,
Табель_отпусков.Дата_с, Табель_отпусков.Количество_днейТабель_отпусков,
СотрудникиТабель_отпусков.Личный_номер = @id AND Табель_отпусков.Личный_номер =
Сотрудники.Личный_номер // Выборка строк таблицы, где значение колонки
«Личный_номер» соответствует введенному значению @id
20. Поиск сотрудника по трудовому стажу, выше указанного
CREATE PROC WorkerExperience
@experience int
SELECT distinct Сотрудники.Личный_номер, Сотрудники.Фамилия,
Сотрудники.Имя, Сотрудники.Должность, Сотрудники.Название_отдела,
DATEDIFF(year,dbo.Трудовой_стаж.Дата_приёма_на_работу,GETDATE()) AS 'Стаж' //
DATEDIFF - функция, которая возвращает интервал времени day, прошедшего от указанной даты Дата_с
до текущей даты, установленной на данном сервере, GETDATE - возвращает текущую дату, установленную на данном
сервереСотрудники, Трудовой_стажСотрудники.Личный_номер=Трудовой_стаж.Личный_номер
AND Трудовой_стаж.Стаж >= @experience // Выборка строк таблицы, где значение
колонки «Стаж» больше или равно введенному значению @experience
.4 T-SQL-определения курсоров
1. Курсор для просмотра сотрудников в выбранном отделе
CREATE PROCEDURE curs1
@otdel varchar(100)curs1 CURSORSCROLL KEYSET
// глобальный прокручиваемый ключевой курсор, который будет существовать
до закрытия текущего соединения
TYPE_WARNING
// Сервер будет информировать пользователя о неявном изменении типа
курсора, если он несовместим с запросом SELECT
FOR
SELECT*FROM Сотрудники
WHERE
Название_отдела LIKE @otdel
// Выборка строк таблицы, где значение колонки «Название_отдела»
соответствует введенному значению @otdel
FOR READ ONLY // Только для чтения
open global curs1 // открытие глобального курсора
DECLARE
@@Counter int@@Counter =@@CURSOR_ROWS
// присвоение переменной @@Counter
значения, равного числу рядов курсора =@@CURSOR_ROWS
Select
@@Counter 'Количество сотрудников в этом
отделе'
CLOSE curs1 // закрытие
курсора
DEALLOCATE curs1 //
освобождение курсора
. Курсор для просмотра количествa командировок в этом месяце
CREATE PROCEDURE curs2curs2 CURSOR 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@@Counter = 1@@var = 0@@Counter<=
@@CURSOR_ROWS
// Выполняется до тех пор, пока число строк в таблице меньше или равно
числу рядов курсора
BEGINcurs2 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
//освобождение курсора
. Поиск сотрудника по фамилии
CREATE PROCEDURE curs3 // открытие глобального курсора
@fio varchar (100)curs3 CURSOR SCROLL KEYSET // глобальный прокручиваемый ключевой курсор, который
будет существовать до закрытия текущего соединения
TYPE_WARNING // Сервер будет информировать
пользователя о неявном изменении типа курсора, если он несовместим с запросом
SELECT
FOR
SELECT
Сотрудники.Личный_Номер, Сотрудники.Фамилия, Сотрудники.Имя,
Сотрудники.Отчество, Сотрудники.Должность,
Сотрудники.Звание, Сотрудники.Название_отдела
FROM
Сотрудники
FOR UPDATE // Курсор
на обновление в таблице
open global curs3
@@id int,
@@name varchar(100),
@@oName varchar(100),
@@lastName varchar(100),
@@position varchar(100),
@@rank varchar(100),
@@dept varchar(100),
@@Counter int,
@@var int@@Counter = 1 @@var =
0
WHILE
@@Counter<= @@CURSOR_ROWS
// Выполняется до тех пор, пока число строк в таблице меньше или равно числу
рядов курсора
BEGINcurs3 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
//освобождение курсора
. Курсор для просмотра отпусков по выбранному типу
CREATE PROCEDURE curs4
@otpusk varchar(100)curs4 CURSORSCROLL 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@@Counter = 1 @@COUNTER<=
@@CURSOR_ROWS
// Выполняется до тех пор, пока число строк в таблице меньше или равно
числу рядов курсора
BEGINcurs4 INTO @@nomer, @@type,@@date,@@day, @@id, @@dept
// FETCH - получает определенную строку из
курсора и помещает данные из столбцов выборки в переменные @@nomer, @@type,@@date,@@day, @@id и @@dept
IF(CHARINDEX(@otpusk,@@type)<>0)
// Проверка, чтобы значений вводимой переменной @otpusk не было в колонке со значениями @@type
BEGIN@@nomer as 'Номер табеля', @@type as 'Тип отпуска',
CAST(@@date AS nvarchar(12)) as 'Дата с', @@day as 'Срок',
// CAST - функция преобразования типа данных
в nvarchar(12) значений колонки «Дата_с»
@@id 'Личный номер сотрудника', @@dept 'Отдел'
END
SET @@Counter =@@Counter +1 // Установить значение переменной @@Counter большим на единицу
END
DEALLOCATE curs4
//освобождение курсора
. Курсор для просмотра сотрудников, у кого в текущем месяце День
рождения
CREATE PROCEDURE curs5curs5 CURSOR 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@@Counter = 1@@var = 0 @@Counter<= @@CURSOR_ROWS
// Выполняется до тех пор, пока число строк в таблице меньше или равно
числу рядов курсора
BEGINcurs5 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 //освобождение курсора
.5
Распределение прав доступа
Информационная система предусматривает возможность гибкого разграничения
прав доступа пользователей к хранимой информации. Такой подход обеспечивает
защиту хранящейся и обрабатываемой информации, а именно:
− ограничение прав на чтение, изменение
или уничтожение;
− обеспечение целостности информации, а
также доступности информации для органов управления и уполномоченных
пользователей;
− исключение утечки информации при
обработке и передаче между объектами вычислительной техники.
Созданы группы пользователей, исполняющие различные роли:
) Админ (login −
admin/password − 0). Имеет доступ ко всей информации, может добавлять/изменять
данные в таблицах, добавлять новых пользователей и удалять существующих.
CREATE LOGIN admin // Создание логина adminPASSWORD = '0' // Присвоение пароляUSER admin // Создание пользователя adminLOGIN adminALL
PRIVILEGES // Назначение пользователю всех правadmin 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 // Назначение всех прав доступа к таблицеSELECT, UPDATE, DELETE, INSERT ON Командировки TO kadrSELECT, UPDATE, DELETE,
INSERT ON Нормативные_доументы TO kadrSELECT, UPDATE, DELETE,
INSERT ON Нормативные_документы_отделов TO kadrSELECT, UPDATE, DELETE, INSERT ON Отделы TO kadrSELECT, UPDATE, DELETE,
INSERT ON Приказы TO kadrSELECT, UPDATE,
DELETE, INSERT ON Приказы_сотрудников TO kadrSELECT, UPDATE, DELETE,
INSERT ON Сотрудники TO kadrSELECT, UPDATE,
DELETE, INSERT ON Спецзвания TO kadrSELECT, UPDATE,
DELETE, INSERT ON Табель_отпусков TO kadrSELECT, UPDATE, DELETE,
INSERT ON Трудовой_стаж TO kadrSELECT, UPDATE, DELETE,
INSERT ON Увольнения TO kadr
3) Сотрудник милиции (sort/2).
Имеет доступ к информации, содержащейся в данной информационной системе, только
в режиме чтения (отсутствуют кнопки удаления, добавления, сохранения).
CREATE LOGIN sort // Создание логина sortPASSWORD = '2' // Присвоение пароляUSER sort // Создание пользователя sortLOGIN sotrALL PRIVILEGES ON
OBJECT::DeletedItem TO sort // Запрет всех действий с таблицейALL PRIVILEGES ON
OBJECT::DeletedWorker TO sort ALL PRIVILEGES ON OBJECT::Пользователи TO sort // Запрет всех действий с таблицей
GRANT SELECT ON День_рождения TO sort // Назначение прав доступа только на выборку данных
из таблицы в режиме чтения
GRANT SELECT ON Сотрудники TO sotrSELECT ON Отделы TO sotrSELECT ON Трудовой_стаж TO sortSELECT ON Командировки TO sotrSELECT ON Нормативные_документы TO sotr SELECT ON Нормативные_документы_отделов TO sotr
GRANT SELECT ON Приказы TO sotrSELECT ON Приказы_сотрудников TO sotrSELECT ON Спецзвания TO sotrSELECT ON Табель_отпусков TO sotrSELECT 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.
Функциональная модель ИС описывает три клиентских приложения. При
разработке программного обеспечения использовались представления, хранимые
процедуры с параметрами, триггеры, курсоры.
Все пункты курсового задания были выполнены, разработанная система
выполняет поставленную перед ним задачу.
Список использованных литературных источников
. Бьюли,
А. Изучаем SQL / А. Бьюли, Э. Оппель. - М.: Символ, 2007. - 312 с.
. Жилинский,
А. Самоучитель Microsoft SQL Server 2005 / А. Жилинский. - СПб: BHV, 2004. -
224 с.
. Бхамидипати,
К. SQL. Справочник программиста/ К. Бхамидипати. - М.: Эком, 2003. - 304 с.
. Литвин,
П. Разработка корпоративных приложений в Access 2002. Для профессионалов / П.
Литвин, К. Гетц, М. Гунделой. - СПб.: Питер, 2002. - 1008 с.
. Виейра,
Р. Программирование баз данных Microsoft SQL Server 2005. Базовый курс / Р.
Вийера. - М.: Вильямс, 2003. - 848 с.
. Уилтон,
П. SQL для начинающих / П. Уилтон, Дж. Колби. - М.: Вильямс, 2006. - 496 с.
. Фленов,
М. Transact SQL в подлиннике / М. Фленов. - СПб: BHV, 2006. - 576 с.
. Моисеенко,
С. SQL. Задачи и решения / С. Моисеенко. СПб.: Питер, 2006. - 256 с.
. Microsoft
SQL Server 7 для профессионалов. - СПб.: Питер, 2000. - 896 с.
10. Базы данных -
революционная эволюция. / [Электронный ресурс] Режим доступа:
<http://www.advlab.ru/articles/article378.htm>. - Дата доступа:
25.03.2013
. Маклаков, С. В.
Моделирование бизнес-процессов с BPwin 4.0 / С. В. Маклаков // -
М.:«Диалог-МИФИ», 2002. - 209 с.