Размещено на http://www.allbest.ru/

Содержание

Введение

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

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

1.2 Модель процессов предметной области

1.3 Количественный анализ модели процесса

1.4 Словарь элементарных функций

2. Концептуальное проектирование базы данных

2.1 Логический уровень концептуальной схемы

2.2 Физический уровень концептуальной схемы

3. Создание таблиц базы данных на языке SQL

3.1 Запросы SQL на манипулирование данными

3.2 Запросы SQL на выборку информации из базы данных

3.2.1 Простые запросы

3.2.2 Сложные запросы

4. Целостность и безопасность базы данных

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

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

4.3 Триггеры

4.4 Стратегии целостности базы данных

4.5 Стратегии безопасности базы данных

Заключение

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

Приложения



Введение

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

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

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

1. Построить модель процессов предметной области;

2. Осуществить логическое и физическое проектирование базы данных;

3. Описать запросы на выборку и манипуляцию данными на языке SQL. Представить написанные запросы с помощью формул реляционной алгебры и реляционного исчисления кортежей

4. Обеспечить ограничение целостности и безопасности для базы данных.



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

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

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

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

Процессы данной предметной области выполняются в соответствии с правового ограничения правилами, стандартами.

Процесс учета сотрудников предприятия осуществляется с момента сотрудник поступает на предприятии и включает в себя следующие этапы: управление данными сотрудников на предприятии, организация сотрудников на предприятии и проведение эффективности предприятия (рисунок 1). Рассмотрим подробнее каждый этап.

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

При этом узнаем персональные данные сотрудников предприятия,

результаты учёта и выбирают сотрудников которые необходимо для функционирования компании (предприятия).

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

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

1.2 Модель процессов предметной области

Семантическое описание предметной области произведено посредством построения стандарта IDEF0 в системе BРwin.

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

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

Следующим этапом проектирования является постановка ряда вопросов, на которые должна отвечать модель с поставленной точностью. Основным вопросом здесь является: «Как и в соответствии, с чем осуществляется расчет сотрудников предприятия?»

Функциональная модель представляет собой систему активностей (функций), которые взаимодействуют через потоки данных. В модели процессов предметной области было выделено три уровня диаграмм: контекстная диаграмма, её декомпозиция и декомпозиции активности «управление данными сотрудников на предприятии»,«организация сотрудников на предприятии » и «проведение эффективности предприятия».

На рисунке представлена контекстная диаграмма. Она отображает основной процесс предметной области - учет сотрудников предприятия.

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

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

Более детальное описание процессов предметной области приведено на рисунке 2 - декомпозиционной диаграмме «автоматизация учета сотрудников предприятия ».

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

* - «управление данными сотрудников»;

* - «организация сотрудников предприятия»;

* - «проведение отчетности на предприятии»;

Рисунок 2 - декомпозиционная диаграмма A0 «автоматизация учета сотрудников предприятия»

На следующих уровнях производятся дальнейшая декомпозиция, так как не получены ответы на все поставленные вопросы с заданной точностью.

Декомпозиционная диаграмма второго уровня (рисунок 3)состоит из следующих активностей:

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

* «увольнение сотрудников» - Документ «Увольнение сотрудника» Осуществляет руководитель и предназначен для ввода и корректировки приказов о прекращении действия трудового договора с работником, т. е. увольнение его с предприятия. Документ содержит следующие данные: основание увольнения (статья), дата увольнения, вид выходного пособия, количество дней неиспользованного отпуска и дополнительной компенсации, а также номер и дата решения профсоюза. При сохранении записи об увольнении сотрудника дата увольнения проставляется во все его текущих (открытых) назначениях.

Рисунок 3 - декомпозиционная диаграмма A1 «управление данными сотрудников»

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

Эта декомпозиционная диаграмма второго уровня (рисунок 4) состоит из следующих активностей:

* «оперативный контроль и учёт сотрудников на предприятии» - данный процесс является осуществлением контроли над деятельностями сотрудников и характеризуется обновлением профиля и компетентность персонала.

* «учёт по должностями сотрудников на предприятии» - на этом этапе перейдем к пребывании в должности сотрудников на предприятии.

* «Классификация сотрудников предприятия» - на этом этапе осуществляем распределение сотрудников, определяем составу предприятия на основание их категории.

Рисунок 4 - Декомпозиционная диаграмма A2 «организация сотрудников предприятия»

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

Эта декомпозиционная диаграмма второго уровня (рисунок 5) состоит из следующих активностей:

* «анализ и обработка входной информации о персонале предприяти » - данный процесс осуществляется руководителям и характеризуется анализом все входные информации о сотрудниках на предприятии.

* «выдача справок и выходных документов о сотрудниках, налоговых» - данный процесс осуществляется на ЭВМ. При этом с помощью ЭВМ руководитель предоставляет доступ к данными(учет социальных, финансовых сотрудников на предприятии) своим сотрудникам и имеет контроля над ними. без его разрешения сотрудники ничего не могут делать.

* «Компьютеризация предприятия» - на этом этапе предприятия функционирует в электроном виде. В результате пред проектного исследования установлено, что отдел сотрудников на предприятии нуждается в автоматизации своей деятельности с целью оперативной обработки данных о сотрудниках, что упрощает доступ к информации по иждивенцам, налоговым льготам, а также других данных о личном составе по бухгалтерии, на основе которых составляются таблицы с соответствующей информацией.

Рисунок 5 - Декомпозиционная диаграмма A3 «проведение эффективности предприятия»

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



1.3 Количественный анализ модели процесса

Для проведения количественного анализа модели процесса нужно убедиться о том, что количество блоков на диаграммах нижних уровней было меньше чем количества блоков на родительских диаграммах, т.е. с увеличением уровня декомпозиции коэффициент N/L должен убывать, где N - количество блоков на диаграмме и L - уровень декомпозиции диаграммы.

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

где Ai - количество стрелок, соединяющихся с i-м блоком;

N - количество блоков на диаграмме;

L - уровень декомпозиции диаграммы.

Итак, проверим выполнение этих условий.

Анализ детализации процесса A0 «автоматизация учета сотрудников предприятия»

N=3, L=1, A1 = 10, A2 = 9, A3 = 11;

N/L=3

Kв= 1.

Анализ детализация процесса A1 «управление данными сотрудников»

N=3, L=2, A1 = 10, A2 = 10, A3 = 12;

N/L=1,5

Kв= 0,8889.

Анализ детализация процесса A2 «организация сотрудников предприятия»

N=3, L=2, A1 = 8, A2 = 7, A3 = 8;

N/L=1,5.

Kв= 0

Анализ детализация процесса A3 «проведение эффективности предприятия»

N=3, L=2, A1 = 9, A2 = 9, A3 = 9

N/L=1,5

Kв= 0

Как видно из расчётов, N/L1>N/L2 и коэффициент сбалансированности убывает с увеличением уровня декомпозиции. Следовательно, убывание коэффициента N/L говорит о том, что по мере декомпозиции модели функции упрощаются. На нижнем уровне декомпозиции коэффициент сбалансированности равен 0, следовательно, функции упрощаются до тривиальных, и модель не содержит узкие места.

1.4 Словарь элементарных функций

Тривиальные функции диаграмма:

" прием сотрудников предприятия"

"учет перевод сотрудников на предприятии"

"увольнение сотрудников"

" оперативный контроль и учёт сотрудников на предприятии"

"учет по должностями сотрудников на предприятии"

"классификация сотрудников предприятия"

" анализ и обработка входной информации о персонале предприятии"

" выдача справок и выходных документов о сотрудниках, налоговых льготах и других сведений"

"компьютеризация предприятия"

Названия блоков диаграмм нижнего уровня модели:

" прием сотрудников предприятия"

"учет перевод сотрудников на предприятии"

"увольнение сотрудников"

" оперативный контроль и учёт сотрудников на предприятии"

"учет по должностями сотрудников на предприятии"

"классификация сотрудников предприятия"

" анализ и обработка входной информации о персонале предприятии"

" выдача справок и выходных документов о сотрудниках, налоговых льготах и других сведений"

"компьютеризация предприятия"

На итоге, количество совпадения имён равно с = 9 и количество уровня L=3

Коэффициент количество совпадения

Kс = c*L= 9*3 = 27.



2. Концептуальное проектирование базы данных

Концептуальное проектирование базы данных «Учет сотрудников предприятия» осуществляется по стандарту IDEFX с помощью CASE-средства Erwin 7.0.

Данный этап проектирования включает в себя:

1) Построение логического уровня концептуальной схемы;

2) Построение физического уровня концептуальной схемы.

Рассмотрим каждый из них подробнее.

2.1 Логический уровень концептуальной схемы

Логический уровень концептуальной схемы процесса «Учет сотрудников предприятия» представлен следующим набором сущностей (Рисунок 2.1):

1) Список личного состава;

2) Штатное расписание;

3) Подразделение;

4) Справочник иждивенцев;

5) Должность;

6) Налоговая карточка;

Сущность «Список личного состава» представляет собой набор атрибутов, который позволит хранить более подробную информацию об сотруднике. Ключевыми атрибутами данной сущности являются «Код сотрудника ». К неключевым относятся следующие атрибуты: «Код подразделения», «ФИО», «Дата рождения», «Паспортные данных» , «Адрес», «Номер телефон» , «Дата поступления». (Рисунок 6).



Рисунок 6 - Логический уровень концептуальной схемы

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

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

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

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

2.2 Физический уровень концептуальной схемы

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

Физический уровень концептуальной схемы процесса «Учет сотрудников предприятия» разрабатывался с учетом особенности СУБД Oracle.

В физической модели используются следующие типы атрибутов сущностей: INTEGER, VARCHAR, CHAR, DECIMAL, DATE.

Рассмотрим каждый из них подробнее.

Типу INTEGER принадлежат атрибуты:

«Номер телефон» , «Код сотрудника» сущности «Список личного состава»;

«количество иждивенца», «код иждивенца» «Код сотрудника» Сущность «Справочник иждивенцев»;

«количество сотрудников»,«Код подразделения» сущности «подразделений»;

«Код подразделения» , сущности «Штатное расписание»;

«Код карточка» сущности «Налоговая карточка»;

«Код подразделения», сущности «подразделений»;

«Код сотрудника», «Код подразделения», «Код должности», «Паспортные данных» сущности «Список личного состава»;

«Код должности», сущности «Должность»;

Тип INTEGER в СУБД Oracle позволяет хранить 4-байтные целочисленные данные. Диапазон от -231 (-2147483648) до 231-1 (2147483647). Этот тип выбран для перечисленных полей потому, что все они должны иметь значения целочисленного типа.

Типу VARCHAR принадлежат атрибуты:

«ФИО», «Адрес», сущность «Список личного состава»;

«Наименование подразделение», сущности «подразделений»;

«Наименование должность», «Квалификация», «Стаж» сущности «должность»;

«Примечание», «вид иждивенца» сущности «Справочник иждивенцев»;

Тип VARCHAR выбран для данных атрибутов потому, что в соответствующих полях будут храниться символьные данные переменной длинны.

Типу CHAR принадлежат атрибуты:

«месяц» сущности «Налоговая карточка »;

Типу DECIMAL принадлежат атрибуты:

«Оклад», «Надбавка» Штатное расписание»;

«Сумма выплат» ,«Сумма вычетов» ,«Подходный налог» сущности «Налоговая карточка»;

Тип DECIMAL предназначен для хранения в полях данных о стоимости.

Типу DATE принадлежат атрибуты:

«Дата рождения» «Дата поступления» сущности «Список личного состава»;

Данный тип позволяет устанавливать и хранить календарную дату. Диапазон этого типа от 1 января 100 года до 27 мая 32768 года.



Рисунок 7 - Физический уровень концептуальной схемы



3. Создание таблиц базы данных на языке SQL

По разработанной концептуальной схеме логического и физического уровней в базе данных «Учет сотрудников предприятия» были созданы 6 таблиц (Приложение А).

Соответствие между названиями сущностей, атрибутов и таблиц, полей базы данных соответственно, приведены в таблице 1.1

Таблица 1.1 - Соответствие между объектами концептуальной схемы и базы данных Соответствие между названиями сущностей, атрибутов и таблиц, полей базы данных соответственно, приведены в таблице 1.1

Таблица 1.1 - Соответствие между объектами концептуальной схемы и базы данных



Таблицы 1.1 Соответствие между названиями сущностей, атрибутов и таблиц, полей базы данных

Сущность	Атрибуты	Таблица	Название полей
Список Личного состава	Код сотрудника Код подразделения ФИО Дата рождения Паспортные данных Адрес Номер телефон Дата поступления Код иждивенца Код должности Код карточка	MUSTER_BOOK	COD_WORKER COD _ UNIT FIO BIRTHDAY PASSPORT DATA ADRESS TEL DATE_PASTU COD_SERVICE COD_CARD
Штатное расписание	Код подразделения Оклад Надбавка	STAFF_LIST	COD_ UNIT SALARY NADBAFKA
подразделения	Код подразделения Наименование подразделения Количество сотрудников	UNITS	COD _UNIT NAME _UNIT QUANTITY_WORKERS
Справочник иждивенцев	Примечание Количество иждивенцев Вид иждивенцев Код сотрудника Код иждивенца	DIRECTORY	PRIMITCHANIE QUANTITY_IJDIVINSA VID_ IJDIVINSA COD_WORKER COD_ IJDIVINSA
Сущность	Атрибуты	Таблица	Название полей
Должность	Код должности Название должности Квалификация Стаж	SERVICE	COD_ SERVICE NAME_SERVICE QUALIFICATION EXPERIENCE
Налоговая карточка	Подоходный налог Месяц Сумма выплат Код карточка Сумма вычетов	TAX_CARD	PADA_NALOG MONTHLY SUM_VOUIPLAT COD_CARD SUM_VOUITCHIT



3.1 Запросы SQL на манипулирование данными

После создания таблиц базы данных была произведена её актуализация заполнение. При заполнении таблиц базы данных использовалось особое подмножество языка SQL ? язык манипуляции данными (DML). Наиболее

часто пришлось использовать оператор INSERT, который позволяет написать запрос на заполнение полей таблиц соответствующими значениями.

3.2 Запросы SQL на выборку информации из базы данных

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

3.2.1 Простые запросы

1. Вывести информацию о сотрудниках, которые поступили в 2011 году:

select * from muster_book where (date_pastu > '31.12.2010' and date_pastu <= '31.12.2011');

Формула РА:

рCOD_WORKER,COD_UNIT,FIO,PASSPORT_DATA,ADRESS,TEL,DATE_PASTU,COD_SERVICE,BIRTHDAY,COD_CARD(у (DATE_PASTU> 31.12.2010)& (DATE_PASTU<=31.12.2011) (MUSTER_BOOK));

Формула РИК:

{p | p (MUSTER_BOOK) &P (DATE_PASTU > '31.12.2010') & (DATE_PASTU <= '31.12.2011')}

2. Вывести информацию о сотрудниках, у которых есть код больше 15 и код должность больше 230:

select * from muster_book where (cod_worker > '15' and cod_service > '230' );

Формула РА:

рCOD_WORKER,COD_UNIT,FIO,PASSPORT_DATA,ADRESS,TEL,DATE_PASTU,SERVICE,BIRTHDAY COD_CARD(у (COD_WORKER> 15)& (COD_SERVICE>230) (MUSTER_BOOK));

Формула РИК:

{f| f (MUSTER_BOOK) &f (COD_WORKER > 15) & (COD_SERVICE >230)}

3. Вывести информацию о сотрудниках, которые специалисты:

select * from service where (qualification ='Специалист');

Формула РА:

рCOD_SERVICE,NAME_SERVICE,QUALIFICATION,EXPERIENCE(у(NAME_SERVICE='СПЕЦИАЛИСТ') (SERVICE));

Формула РИК:

{h| h (SERVICE) &h (NAME_SERVICE=' СПЕЦИАЛИСТ') }

4. Вывести список сотрудников, у которых есть оклад больше 10000 и учасник ВОВ:

select * from staff_list,directory where (salary > 10000 and vid_ijdivinsa = `Учасник ВОВ' )

Формула РА:

рCOD_UNIT,SALARY,NADBAFKA,COD_DEPENDENT,PRIMITCHANIE,VID_IJDIVINSA,QUQNTITY_IJDIVINSA,COD_WORKER (у(VID_IJDIVINSA='УЧАСНИК ВОВ'& SALARY >10000) (STAFF_LIST & DIRECTORY));

Формула РИК:

{h| h (STAFF_LIST) &h (SALARY >'10000') &p¦ p(VID_IJDIVINSA) & p(VID_IJDIVINSA=`УЧАСНИК ВОВ')}

5. Вывести информацию о сотрудниках, родившихся между 1980-1993:

select * from muster_book where birthday between '31.12.1980' and '31.12.1993'

Формула РА:

рCOD_WORKER,COD_UNIT,FIO,PASSPORT_DATA,ADRESS,TEL,DATE_PASTU,SERVICE,BIRTHDAY COD_CARD(у (BIRTHDAY(=>' 31.12.1980'& =<'31.12.1993'))(MUSTER_BOOK));

Формула РИК: концептуальный база данный триггер

{i| i (MUSTER_BOOK) &i (BIRTHDAY( => 31.12.1980 & =< 31.12.1993))}

6. Вывести пересечение сущности `должност'ь и `личного состава':

select cod_service from service where cod_service in (select cod_service from muster_book);

Формула РА:

рCOD_SERVICE (у (COD_SERVICE )= (MUSTER_BOOK)?(SERVICE));

Формула РИК:

{j| j (SERVICE) &j (COD_SERVICE) ? k¦ k (MUSTER_BOOK) &k (COD_SERVICE) }

7. Вывести ФИО о сотрудниках, которые живут в адрес `Мира':

select fio from muster_book where (adress = 'мира')

Формула РА:

РFIO (у (ADRESS= `МИРА') (MUSTER_BOOK));

Формула РИК:

{l|l l (MUSTER_BOOK) &l (ADRESS=' МИРА') }

8. Вывести информацию о сотрудниках, которые имеют сумму вылат меньше 12000

select pada_nalog,sum_vouiplat from tax_card where sum_vouiplat < 12000;

Формула РА:

РPADA_NALOG,SUM_VOUIPLAT (у(SUM_VOUIPLAT < 12000) (TAX_CARD));

Формула РИК:

{m| m (TAX_CARD) &m (SUM_VOUIPLAT <' 12000') }

9. Вывести код подразделения и соответствующее ему имя

select l.cod_unit,l.name_unit,m.fio from list_units l,muster_book m

where list_units.cod_unit = muster_book.cod_unit

Формула РА:

Рcod_unit,name_unit(LIST_UNITS)

Формула РИК:

,

3.2.2 Сложные запросы

10.Вывести средние оклад от сотрудников предприятия

select avg (salary) as salary from staff_list;

Формула РА:

РSALARY(у(SALARY= AVG(SALARY)) (STAFF_LIST));

Формула РИК:

{m| m (STAFF_LIST) & (SALRAY= AVG(SALARY)) }

11. Вывести список сотрудников (ФИО, код должности) с окладом больше 10000 тысяч рублей

select fio,salary,cod_service from muster_book ,staff_list

where (salary > 10000 and muster_book.cod_unit = staff_list.cod_unit);

Формула РА:

Р FIO,SALARY,COD_SERVICE(у(SALARY) >`10000',COD_SERVICE,FIO) (MUSTER_BOOK >и< MUSTER_BOOK.COD_UNIT = STAFF_LIST.COD_UNIT STAFF_LIST));

Формула РИК:

{ g| g (MUSTER_BOOK) & g' | g' (STAFF_LIST) &g(COD_UNIT) = g'(COD_UNIT) &g'(SALARY > `10000') }

12. Для всех сотрудников из списка личного состава вывести оклад и надбавку.

select fio,salary,nadbafka from muster_book ,staff_list

where muster_book.cod_unit = staff_list.cod_unit;

Формула РА:

р FIO,SALARY,NADBAFKA(у (SALARY,NADBAFKA) (MUSTER_BOOK >и< MUSTER_BOOK.COD_UNIT = STAFF_LIST.COD_UNIT STAFF_LIST))

Формула РИК:

{o (FIO,SALARY,NADBAFKA)| o (MUSTER_BOOK) & s (STAFF_LIST) &o(COD_UNIT) = s(COD_UNIT) }.

13. Для сотрудников в названии(ФИО) которых начинают с букв `М' вывести их наименования должности.

select fio ,name_service from muster_book,service

where(fio like '%М%'and service.cod_service = muster_book.cod_service);

Формула РА:

р FIO,NAME_SERVICE(у (FIO ='М', NAME_SERVICE) (MUSTER_BOOK >и< MUSTER_BOOK.COD_SERVICE= SERVICE.COD_SERVICE SERVICE));

Формула РИК:

{q(FIO,NAME_SERVICE)| q (MUSTER_BOOK) & q' (SERVICE) &q(COD_SERVICE) = q'(COD_SERVICE)&q(FIO='М') }

14.Вывести дате поступления, дате рождения сотрудника, у которого есть надбавка

select birthday,date_pastu,nadbafka from muster_book,staff_list where

muster_book.cod_unit = staff_list.cod_unit and nadbafka is not null

Формула РА:

р(date_pastu,birthday,nadbafka)(д is not null(muster_book >< staff_list))

Формула РИК:

15.С помощью не связанный запрос вывести информаций о сотрудниках в подразделении имеющих код сотрудников меньше 20 и поступили после 1980

select * from list_units where cod_unit in (select cod_unit

from muster_book where cod_worker < '20' and date_pastu > '31.12.1980' )

Формула РА:

рCOD_UNIT,NAME_UNIT,QUNQTITY_WORKER(у(COD_WORKER <'20',DATE_PASTU > 31.12.1980) (LIST_UNIT (MUSTER_BOOK.COD_UNIT=LIST_UNIT.COD_UNIT )MUSTER_BOOK));

Формула РИК:

{r(COD_UNIT,NAME_UNIT,QUANTITY_WORKER)| r(LIST_UNIT) & r'(MUSTER_BOOK) & r'(COD_WORKER < '20'& DATE_PASTU >'1980') }

16.Вывести имя сотрудника , у которого минимальный оклад

select fio from muster_book,staff_list where muster_book.cod_unit = staff_list.cod_unit group by fio having max(salary)=(select max(salary)from staff_list);

Формула РА:

д max(salary)=(р max(salary) (staff_list)) (рfio(muster_book >< staff_list))

Формула РИК:

,

17. Вывести имя сотрудника и его вид иждивенца

select fio,vid_ijdivinsa from muster_book,directory where

muster_book.cod_worker = directory.cod_worker

Формула РА:

Рfio,vid_ijdivinsa(muster_book >< directory)

Формула РИК:

,

18.Вывести адрес ,квалификация и паспортные данных сотрудников , у которых нет стажа

select adress,passport_data,qualification from muster_book m,service s where

m.cod_service = s.cod_service and experience is null

Формула РА

р(adress,passport_data,qualification)(д experience is null(muster_book >< service))

Формула РИК:

,



4. Целостность и безопасность базы данных

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

Создать представление, отражающее ФИО сотрудников какая должность он занимается и какой него оклад:

CREATE VIEW FIO_SERVICE_V( V_FIO, V_NAME_SERVICE,

SALARY_V) AS

select fio,name_service,salary from muster_book,service,staff_list

where (muster_book.cod_service = service.cod_service) and

(muster_book.cod_unit = staff_list.cod_unit)

Создать представление, отражающее ФИО сотрудника,имя его подразделения и номера его Справочник иждивенцев:

CREATE VIEW FIO_list_units_V( V_FIO, V_NAME_unit, v_cod_dependent)

AS

select fio,name_unit,cod_dependent from muster_book ,list_units,directory

where (muster_book.cod_unit = list_units.cod_unit) and

(muster_book.cod_worker = directory.cod_worker)

Создать представление, выводящее вид иждивенца сотрудника ,её дата поступления и номера её Налоговая карточка:

CREATE VIEW VID_IJDIVINSA_V( V_VID_IJDIVINSA,

V_COD_CARD,v_date_pastu)

AS

select t.cod_card,d.vid_ijdivinsa,m.date_pastu from tax_card t,directory d,muster_book m

where (tax_card.cod_card = muster_book.cod_card) and (directory.cod_worker = muster_book.cod_worker);



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

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

Создать хранимую процедуру, которая выводит названия и код подразделения для сотрудника с ФИО, передаваемым в параметре:

create procedure unit

(fio varchar(128))

returns (cod_unit integer, name_unit varchar(128) )

as

begin

for select l.cod_unit, l.name_unit from list_units l,muster_book m

where m.fio=:fio and (l.cod_unit= m.cod_unit)

into :cod_unit,:name_unit

Do

BEGIN

SUSPEND;

end

end

Создать хранимую процедуру, которая выводит оклад и должности сотрудника, с паспортными данными, передаваемым в параметре

create procedure info_satroudnika

(passport_data integer))

returns (salary decimal,name_service varchar(128))

as

begin

for select st.salary,s.name_service from service s, staff_list st,muster_book m

where m.passport_data=:passport_data and (m.cod_service= s.cod_service and st.cod_unit = m.cod_unit)

into :name_service,:salary

Do

BEGIN

SUSPEND;

end

end

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

create procedure worker(cod_worker integer)

returns (date_pastu date,fio varchar(128),experience varchar(128), sum_vouiplat char(18))

as

begin

for select m.date_pastu,m.fio,s.experience,tc.sum_vouiplat from muster_book m,service s,tax_card tc

where m.cod_worker =:cod_worker and (m.cod_service = s.cod_service and tc.cod_card = m.cod_card

and s.experience is not null)

into :date_pastu,:fio,:experience,:sum_vouiplat

Do

BEGIN

SUSPEND;

end

end

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

create procedure cherch(adress varchar(128),birthday date,tel integer)

returns (vid_ijdivinsa varchar(128), primitchanie varchar(128),

qualification varchar(128),nadbafka decimal)

as

begin

for select d.vid_ijdivinsa,d.primitchanie,s.qualification,st.nadbafka

from directory d, service s,staff_list st,muster_book m

where (m.adress =:adress and m.birthday=:birthday and m.tel=:tel)

and (m.cod_worker = d.cod_worker and st.cod_unit = m.cod_unit and s.cod_service = m.cod_service )

into :vid_ijdivinsa,:primitchanie,:qualification,:nadbafka

Do

BEGIN

SUSPEND;

end

end.

4.3 Триггеры

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

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

CREATE TRIGGER LIST_UNITS1 FOR LIST_UNITS

ACTIVE AFTER UPDATE POSITION 1

AS

Begin

Update staff_list

set cod_unit= new.cod_unit

where cod_unit = old.cod_unit;

Update muster_book

set cod_unit= new.cod_unit

where cod_unit = old.cod_unit;

end

Создать триггер, который перед удалением кортежей из таблицы «подразделения » удаляет кортежи из таблицы «Штатное расписание» и «Список Личного состава» :

CREATE TRIGGER LIST_UNITS2 FOR LIST_UNITS

ACTIVE AFTER DELETE POSITION 1

AS

Begin

Delete from staff_list s

where s.cod_unit= old.cod_unit;

delete from muster_book m

where m.cod_unit= old.cod_unit;

end

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

CREATE TRIGGER delservice FOR service

ACTIVE AFTER DELETE POSITION 1

AS

Begin

delete from muster_book m

where m.cod_service= old.cod_service;

end

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

create trigger moveservice for service

active after update

as

begin

update muster_book

set cod_service = new.cod_service

where cod_service = old.cod_service;

end

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

create trigger move_ijdivinsa for directory

active after update

as

begin

update muster_book

set cod_worker = new.cod_worker

where cod_worker = old.cod_worker;

end

Создать триггер, который перед удалением кортежей из таблицы «справочник иждивенца» удаляет кортежи из таблицы «Список Личного состава»

create trigger del_directory for directory

active after delete position 1

as

begin

delete from muster_book m

where m.cod_worker = old.cod_worker;

end.

4.4 Стратегии целостности базы данных

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

1. Ограничение целостности домена

2. Ограничение целостности атрибута

3. Ограничение целостности отношения

4. Ограничение целостности базы данных в целом

При проектировании базы данных «Учет сотрудников предприятия» были использованы стратегии ограничения целостности домена и базы данных в целом.

Ограничение целостности домена определяется множеством значений, которое он образует. Другими словами, процесс создания домена - это реализация стратегии ограничение целостности домена. В процессе создания базы данных были описаны домены VID_IJDIVINSA, FIO_DOMAIN, AKLAD_DOMAIN, которые определяют значения полей VID_IJDIVINSA, FIO, SALARY таблиц DIRECTORY, MUSTER_BOOK, STAFF_LIST соответственно. Каждый домен определен набором значений, которые могут принимать поля VID_IJDIVINSA, FIO, SALARY. Запросы создания перечисленных доменов выглядят следующим образом (Листинг 3.1):

CREATE DOMAIN VID_IJDIVINSA AS

VARCHAR(128) CHARACTER SET NONE

DEFAULT NULL

CHECK (VALUE = 'инвалид' OR VALUE = `учасник вов')

COLLATE NONE

CREATE DOMAIN FIO_DOMAIN AS

VARCHAR(120) CHARACTER SET NONE

CHECK (value = 'A ...z' or value = 'А...Я')

COLLATE NONE

CREATE DOMAIN AKLAD_DOMAIN AS

DECIMAL(18,0)

DEFAULT null

CHECK (value <= '40000')

Листинг 3.1 - Определение доменов

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

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

1. RESTRICT ? Стратегия, согласно которой налагается запрет на все изменения первичного ключа, если существуют ссылающиеся на него внешние ключи.

2. CASCADE ? Воздействие операции с исходной строкой "каскадным" образом распространяется на все строки всех таблиц, где содержатся ссылки на данную строку.

3. SET NULL ? В этом случае, чтобы разрешить изменение или удаление исходной таблицы и не нарушить ссылочную целостность, соответствующие значения внешних ключей заменяются значением NULL.

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



Рисунок 8 - Стратегии обеспечения целостности базы данных

4.5 Стратегии безопасности базы данных

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

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

Было выделено 4 основных пользователя «Администратор», «директор» «Руководитель» и «Сотрудник» (Листинг 3.2):

CREATE ROLE administrator

CREATE ROLE director

CREATE ROLE roukavaditili

CREATE ROLE satroudnik

Листинг 3.2 ? Создание пользователей.

Установим права для каждого из пользователей, разрешив сотруднику только делать выборку и изменение информации из таблиц «SERVICE», « LIST_UNITS», «DIRECTORY»,директору будут доступны выборка, добавление, изменение информации всех таблиц, а администратору и руководителя ? все действия над таблицами (Листинг 3.3):

GRANT SELECT, UPDATE ON SERVICE TO SATROUDNIK;

GRANT SELECT, UPDATE ON LIST_UNITS TO SATROUDNIK ;

GRANT SELECT, UPDATE ON DIRECTORY TO SATROUDNIK ;

GRANT SELECT,INSERT,UPDATE ON DIRECTORY TO DIRECTOR;

GRANT SELECT,INSERT,UPDATE ON SERVICE TO DIRECTOR;

GRANT SELECT,INSERT,UPDATE ON MUSTER_BOOK TO DIRECTOR;

GRANT SELECT,INSERT,UPDATE ON STAFF_LISTTO DIRECTOR;

GRANT SELECT,INSERT,UPDATE ON LIST_UNITS TO DIRECTOR;

GRANT SELECT,INSERT,UPDATE ON TAX_CRAD TO DIRECTOR;

GRANT ALL ON DIRECTORY TO ADMIMISTRATOR,ROUKAVADITILI WITH GRANT;

GRANT ALL ON SERVICE TO ADMIMISTRATOR,ROUKAVADITILI WITH GRANT;

GRANT ALL ON LIST_UNITS TO ADMIMISTRATOR,ROUKAVADITILI WITH GRANT;

GRANT ALL ON TAX_CARD TO ADMIMISTRATOR,ROUKAVADITILI WITH GRANT;

GRANT ALL ON MUSTER_BOOKY TO ADMIMISTRATOR,ROUKAVADITILI WITH GRANT;

GRANT ALL ON STAFF_LIST TO ADMIMISTRATOR,ROUKAVADITILI WITH GRANT;

Листинг 3.3 - Установление полномочий.

Отменим право сотрудника модифицировать и заполнять таблицу «LIST_UNITS»:

REVOKE SELECT,UPDATE ON LIST_UNITS FROM SATROUDNIK;

Листинг 3.4 - Отмена полномочий сотрудник на модификацию и вставку.



Заключение

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

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

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



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

1. Крёнке. Д. Теория и практика построения баз данных: учеб. пособие./Крёнке Д. СПб.: Питер, 2003, 800 с.: ил.

2. Малыхина М. П. Базы данных: основы, проектирование, использование: учебное пособие. / М. П. Малыхина.// - Спб.: БХВ - Петербург, 2004, 512 с.

3. Роб П., Коронел К. Системы баз данных: проектирование,реализация и управление/Роб П., Коронел К.// - СПб.: БХВ-Петербург, 2004. - 1040 с.

4. Фуфаев Э.В., Фуфаев Д.Э. Базы данных (7-е изд.) / Фуфаев Э.В., Фуфаев Д.Э.// - Академия, 2012, 320 с.

5. Чекалов А. Базы данных: от проектирования до разработки приложений/ Чекалов А.// - БХВ-Петербург, 2003, 384 с.



Приложения

Приложение А

Структура базы данных

CREATE TABLE DIRECTORY (

COD_DEPENDENT INTEGER NOT NULL,

PRIMITCHANIE VARCHAR(128),

1. VID_IJDIVINSA VID_IJDIVINSA /* VID_IJDIVINSA = VARCHAR(128) DEFAULT NULL CHECK (VALUE = 'Инвалид' OR VALUE = 'Учасник вов') */,

QUANTITY_IJDIVINSA INTEGER,

COD_WORKER INTEGER NOT NULL

);

ALTER TABLE DIRECTORY ADD PRIMARY KEY (COD_DEPENDENT);

ALTER TABLE DIRECTORY ADD FOREIGN KEY (COD_WORKER) REFERENCES MUSTER_BOOK (COD_WORKER);

CREATE TABLE LIST_UNITS (

COD_UNIT INTEGER NOT NULL,

NAME_UNIT VARCHAR(128),

QUANTITY_WORKER INTEGER

);

ALTER TABLE LIST_UNITS ADD PRIMARY KEY (COD_UNIT);

CREATE TABLE MUSTER_BOOK (

COD_WORKER INTEGER NOT NULL,

COD_UNIT INTEGER NOT NULL,

FIO_DOMAIN /* FIO_DOMAIN = VARCHAR(120) CHECK (value = 'A ...z' or value = 'А...Я') */,

PASSPORT_DATA INTEGER,

ADRESS VARCHAR(128),

TEL INTEGER,

DATE_PASTU DATE,

COD_SERVICE INTEGER NOT NULL,

BIRTHDAY DATE,

COD_CARD NTEGER NOT NULL

);

ALTER TABLE MUSTER_BOOK ADD PRIMARY KEY (COD_WORKER);

ALTER TABLE MUSTER_BOOK ADD FOREIGN KEY (COD_UNIT) REFERENCES LIST_UNITS (COD_UNIT);

ALTER TABLE MUSTER_BOOK ADD FOREIGN KEY (COD_CARD) REFERENCES TAX_CARD (COD_CARD);

ALTER TABLE MUSTER_BOOK ADD FOREIGN KEY (COD_SERVICE) REFERENCES SERVICE (COD_SERVICE)

CREATE TABLE SERVICE (

COD_SERVICE INTEGER NOT NULL,

NAME_SERVICE VARCHAR(128),

QUALIFICATION VARCHAR(128),

EXPERIENCE VARCHAR(128)

);

ALTER TABLE SERVICE ADD PRIMARY KEY (COD_SERVICE);

CREATE TABLE STAFF_LIST (

COD_UNIT INTEGER NOT NULL,

SALARY AKLAD_DOMAIN /* AKLAD_DOMAIN = DECIMAL(18,0) DEFAULT null CHECK (value <= '40000') */,

NADBAFKA DECIMAL(9,0)

);

ALTER TABLE STAFF_LIST ADD PRIMARY KEY (COD_UNIT);

ALTER TABLE STAFF_LIST ADD FOREIGN KEY (COD_UNIT) REFERENCES LIST_UNITS (COD_UNIT);

CREATE TABLE TAX_CARD (

COD_CARD INTEGER NOT NULL,

MONTHLY CHAR(18),

SUM_VOUIPLAT CHAR(18),

PADA_NALOG CHAR(18),

SUM_VOUITCHIT CHAR(18)

);

ALTER TABLE TAX_CARD ADD PRIMARY KEY (COD_CARD);

Размещено на Allbest.ru