Отдел кадров предприятия

Отдел кадров предприятия

МИНИСТЕРСТВО ОБРАЗОВАНИЯ РЕСПУБЛИКИ БЕЛАРУСЬ

БЕЛОРУСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ИНФОРМАТИКИ И РАДИОЭЛЕКТРОНИКИ

Кафедра экономической информатики

Инженерно-экономический факультет

Индивидуальная работа

по дисциплине "Базы данных"

на тему: "Отдел кадров предприятия"

Выполнила:

студентка гр. 972303

Шлык В.С.

Проверил: Бутов А.А.

Минск, 2011

СОДЕРЖАНИЕ

ВВЕДЕНИЕ

. ПОСТАНОВКА ЗАДАЧИ

. ПРОЕКТИРОВАНИЕ МОДЕЛИ БАЗЫ ДАННЫХ В ERwin

. СОЗДАНИЕ БАЗЫ ДАННЫХ В СУБД MS ACCESS

4. Создание базы данных в SQL Server Management Studio

5. ВЫПОЛНЕНИЕ ПРОЦЕДУР И ФУНКЦИЙ

ЗАКЛЮЧЕНИЕ

ЛИТЕРАТУРА

ПриЛожение

ВВЕДЕНИЕ

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

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

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

изучить предметную область;

разработать логическую и физическую модель системы;

создать базу данных и хранимые процедуры с СУБД MS SQL Server.

1. ПОСТАНОВКА ЗАДАЧИ

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

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

. Создать SQL-сценарий, который позволяет сгенерировать базу данных в формате MS SQL Server 2005, а также наполнить созданную базу данных конкретными данными.

4. Дополнить сценарий текстами перечисленных ниже хранимых процедур:

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

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

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

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

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

5. Разработать SQL-код для проверки работы созданных хранимых процедур.

2. ПРОЕКТИРОВАНИЕ МОДЕЛИ БАЗЫ ДАННЫХ В ERWIN

Цель этапа:

)        проанализировать предметную область;

)        определить сущности предметной области;

)        определить логические связи.

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

На основе анализа предметной области мною были разработаны с помощью Erwin логическая и физическая (рисунок 2.1) модели данных базы данных для отдела кадров на предприятии.

Рисунок 2.1. Физическая модель базы данных.

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

Таблица 2.1. Сущности и их атрибуты

В результате данного этапа были определены сущности логической модели, а также свойства сущностей и ключевые атрибуты, логические связи между сущностями с помощью программы Erwin Data Modeler.

3.      СОЗДАНИЕ БАЗЫ ДАННЫХ В СУБД MS ACCESS

На основе проведенного анализа предметной области и проведенного в ErWin моделирование базы данных для управления АЗС была создана автоматически база данных в СУБД Access. Схема созданной базы данных приведена на рисунке 3.1.

Рисунок 3.1 Схема базы данных в СУБД MS ACCESS.

4.      Создание базы данных в SQL Server Management Studio

Следующим этапом в выполнении задания является создание SQL-скрипта, который сгенерирует аналогичную базу данных для MS SQL Server 2005. Код создания базы данных и ввода в таблицы некоторых значений, приведен в ПРИЛОЖЕНИИ 1.

В результате проведенных операций мы создали базу данных Кадры с следующими таблицами и полями:

Штатное Расписание

-Номер

КодСотрудника

КоличествоЧасов

Пропуски

Причина

Контракт

-КодКонтракта

КодСотрудника

ДатаДо

Заметки

Должность

-КодДолжности

Должность

Премия

Разряд

Оклад

Подразделение

-КодПодразделения

Подразделение

Численность

Руководитель

Военнообязанные

-Номер

КодСотрудника

СемейноеПоложение

ДолгЛет

Отсрочка

Сотрудник

-КодСотрудника

ФИО

КодПодразделения

КодДолжности

Образование

Пол

Возраст

Телефон

А также мы ввели в таблицу значения, представленные на рисунке 4.1.

Рисунок 4.1. Созданные таблицы и введенные в них значения.

5 ВЫПОЛНЕНИЕ ПРОЦЕДУР И ФУНКЦИЙ

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

Код данной процедуры представлен ниже:

CREATE PROCEDURE pr_ПодсчетСтрок

@ИмяТаблицы VARCHAR(20),

@ЧислоСтрок INT OUTPUT( @ИмяТаблицы = 'Подразделения' )@ЧислоСтрок = COUNT(*)ПодразделенияIF ( @ИмяТаблицы = 'Должность' )@ЧислоСтрок = COUNT(*)ДолжностьIF ( @ИмяТаблицы = 'Сотрудник' )@ЧислоСтрок = COUNT(*)СотрудникIF ( @ИмяТаблицы = 'ШтатноеРасписание' )@ЧислоСтрок = COUNT(*)ШтатноеРасписаниеIF ( @ИмяТаблицы = 'Контракт' )@ЧислоСтрок = COUNT(*)КонтрактIF ( @ИмяТаблицы = 'Военнообязанные')@ЧислоСтрок = COUNT(*)Военнообязанные@NAME_TABLE VARCHAR(20), @COUNT_STR INT@NAME_TABLE = 'Контракт'pr_ПодсчетСтрок @NAME_TABLE, @COUNT_STR OUTPUT @NAME_TABLE [НАЗВАНИЕ_ТАБЛИЦЫ],@COUNT_STR [ЧИСЛО_СТРОК]

Результаты работы процедуры представлены на рисунке 6.1

Рисунок 6.1. Результаты работы процедуры.

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

Код данной процедуры представлен ниже:

SELECT *КонтрактPROCEDURE pr_ИзменениеДаты

@ФЛАГ VARCHAR(20),

@КОЛ_ДНЕЙ INT( @ФЛАГ = '+' )

UPDATE Контракт

SET ДатаОт = ДатаОт + @КОЛ_ДНЕЙ

ELSE

UPDATE Контракт

SET ДатаОт = ДатаОт - @КОЛ_ДНЕЙ

IF( @ФЛАГ = '+' )

UPDATE Контракт

SET ДатаДо = ДатаДо + @КОЛ_ДНЕЙ

ELSE

UPDATE Контракт

SET ДатаДо = ДатаДо - @КОЛ_ДНЕЙ

GO@FLAG VARCHAR(20), @KOL_DNEJ INT@FLAG = '+'@KOL_DNEJ = 4 pr_ИзменениеДаты @FLAG, @KOL_DNEJ

SELECT *

FROM Контракт

GO

Результаты работы процедуры представлены на рисунке 6.2

Рисунок 6.2. Результаты работы процедуры.

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

Код данной процедуры представлен ниже:

@ИМЯ_СТОЛБЦА VARCHAR(20),

@МИНИМУМ INT OUTPUT,

@СРЕДНЕЕ INT OUTPUT,

@МАКСИМУМ INT OUTPUT,

@СУММАРНОЕ INT OUTPUT

IF ( @ИМЯ_СТОЛБЦА = 'КоличествоЧасов' )

SELECT @МИНИМУМ = MIN(КоличествоЧасов), @СРЕДНЕЕ = AVG(КоличествоЧасов), @МАКСИМУМ = MAX(КоличествоЧасов), @СУММАРНОЕ = SUM(КоличествоЧасов)

FROM ШтатноеРасписание

IF ( NOT @ИМЯ_СТОЛБЦА LIKE 'КоличествоЧасов' )

SELECT @МИНИМУМ = 0, @СРЕДНЕЕ = 0, @МАКСИМУМ = 0, @СУММАРНОЕ = 0@MIN INT, @MAX INT, @AVG INT, @SUM INT, @IMYA_ST VARCHAR(20)@IMYA_ST = 'КоличествоЧасов'pr_MinAvrMaxSum_КоличествоЧасов @IMYA_ST, @MIN OUTPUT, @AVG OUTPUT, @MAX OUTPUT, @SUM OUTPUT @MIN [МИН], @AVG [СРЕДН], @MAX [МАКС], @SUM [СУММ]

GO

Результаты работы процедуры представлены на рисунке 6.3

Рисунок 6.3. Результаты работы процедуры.

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

Код данной процедуры представлен ниже:

CREATE PROCEDURE pr_Выбор

@Условие1 INT,

@Условие2 INT( @Условие1 IS NULL AND @Условие2 IS NOT NULL ) *

FROM Должность

WHERE КодДолжности = @Условие2

ELSE IF ( @Условие2 IS NULL AND @Условие1 IS NOT NULL)*ДолжностьОклад = @Условие1IF ( @Условие1 IS NULL AND @Условие2 IS NULL )*ДолжностьIF ( @Условие1 IS NOT NULL AND @Условие2 IS NOT NULL ) *

FROM Должность

WHERE Оклад = @Условие1 AND КодДолжности= @Условие2

GO@УСЛ1 INT, @УСЛ2 INT@УСЛ2 = 1pr_Выбор @УСЛ1, NULL @УСЛ1 [1 условие], @УСЛ2 [2 условие]

GO

Результаты работы процедуры представлены на рисунке 6.4

Рисунок 6.4. Результаты работы процедуры.

ЗАКЛЮЧЕНИЕ

база данные erwin скрипт процедура

В ходе выполнения работы были закреплены знания о использовании команд языка Transact/SQL и ERwin. Нами были cозданы, заполнены данными, проделаны выборки из таблиц и представлений, обновление и удаление данных, изменение структуры таблицы, удаление таблиц и базы данных. Нами были решены перечень задач: по написанию кода, использующего применение языка Transact/SQL и временных таблиц, по созданию хранимых процедур, по написанию проверочного кода для тестирования правильности работы созданных хранимых процедур и функций. После этого стало более понятно, как команды манипулирования данными позволяют работать с хранящейся в базе данных информацией. Ознакомились с использованием триггеров в Transact-SQL ,освоили способы создания курсоров в Transact-SQL.

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

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

[1] Бутов, А.А. Базы данных: лабораторный практикум для студентов специальности "Информационные системы и технологии"/ А.А Бутов, И. Г. Орешко, Е. А. Шестаков. - М: БГУИР, 2009. - 108 с.

[2] Бурков, А.В. Проектирование информационных систем по технологии клиент-сервер в "Microsoft SQL Server 2008" и "Microsoft Visual Studio 2008"/ А.В. Бурков. - М: БГУИР, 2009. - 123 с.

ПРИЛОЖЕНИЕ

Создание таблиц и ввод значений в них

CREATE DATABASE КадрыPRIMARY

( NAME = Кадры_Data,= 'D:\Кадры_Data.mdf',= 3MB,= 50MB,= 2MB ),Secondary

( NAME = Кадры2_Data,= 'D:\Кадры_Data2.ndf',= 2MB,= 70MB,= 20% ),

( NAME = Калры3_Data,= 'D:\Кадры_Data3.ndf',= 2MB,= 5MB )ON

( NAME = Склад_Log,= 'D:\Кадры_Log.ldf',= 1MB,= 10MB,= 15% ),

( NAME = Склад2_Log,= 'D:\Кадры_Log2.ldf',= 512KB,= 5MB,= 10% )КадрыTABLE Подразделения (

КодПодразделения INT PRIMARY KEY,

Подразделение VARCHAR(20) DEFAULT 'Производство' NOT NULL,

Численность INT NOT NULL CHECK (Численность > 0),

Руководитель VARCHAR(50) NOT NULL

)TABLE Должность (

КодДолжности INT PRIMARY KEY,

Должность VARCHAR(40) NOT NULL,

Оклад INT NULL CHECK (Оклад > 0),

Разряд INT DEFAULT '1' NULL CHECK (Разряд > 0),

Премия INT NULL

)TABLE Сотрудник (

КодСотрудника INT PRIMARY KEY,

ФИО VARCHAR(60) NULL,

Образование VARCHAR(60) NULL,

КодПодразделения INT NOT NULL,

КодДолжности INT NOT NULL,

Телефон INT NULL,

Пол VARCHAR(5) NULL(Пол IN ('муж', 'жен')),

Возраст INT NULL CHECK (Возраст > 0),FK_Сотрудник_Подразделения FOREIGN KEY (КодПодразделения)Подразделения ON UPDATE CASCADE, FK_Сотрудник_Должность FOREIGN KEY (КодДолжности)

REFERENCES Должность ON UPDATE CASCADE

)TABLE ШтатноеРасписание (

Номер INT IDENTITY(1,1) NOT NULL,

КодСотрудника INT NOT NULL,

КоличествоЧасов INT NOT NULL CHECK (КоличествоЧасов > 0),

Пропуски INT NULL,

Причина VARCHAR(30) NULL ,

REFERENCES Сотрудник ON UPDATE CASCADE

)TABLE Контракт (

КодКонтракта INT PRIMARY KEY NOT NULL,

КодСотрудника INT NOT NULL,

ДатаОт DATETIME DEFAULT getdate() NOT NULL,

ДатаДо DATETIME NULL,

Заметки VARCHAR(30) NULL, FK_Контракт_Сотрудник FOREIGN KEY (КодСотрудника)

REFERENCES Сотрудник ON UPDATE CASCADE

)TABLE Военнообязанные (

Номер INT IDENTITY(1,1) NOT NULL,

КодСотрудника INT NOT NULL,

Отсрочка INT NOT NULL,

СемейноеПоложение VARCHAR(10) NOT NULL,

ДолгЛет INT NULL CHECK (ДолгЛет > 0), FK_Военнообязанные_Сотрудник FOREIGN KEY (КодСотрудника)

REFERENCES Сотрудник ON UPDATE CASCADE

)

INSERT INTO Подразделения

VALUES (1, 'Руководство', 15, 'Иванова А.А.')

INSERT INTO Подразделения

VALUES (2, 'Цех', 44, 'Петрова А.Ю.')

INSERT INTO Подразделения

VALUES (3, 'Контора', 36, 'Чернявская М.М.')

INSERT INTO Подразделения

VALUES (4, 'Отдел кадров', 6, 'Шлык В.С.')

INSERT INTO Подразделения

VALUES (5, 'Бухгалтерия', 3, 'Комякевич А.Л.')

GOINTO Должность(01, 'Начальник', 3000000, 20, 500000)INTO Должность(02, 'Рабочий', 1000000, 12, 50000)INTO Должность(03, 'Бригадир', 1500000, 14, 70000)INTO Должность(04, 'Специалист', 2000000, 16, 90000)INTO Должность(05, 'Стажер', 900000, 1, 10000)INTO Сотрудник(1, 'Шлык В.С.', 'высшее', 4, 01, 5802489, 'жен', 20)INTO Сотрудник(2, 'Ксенжук Д.Ю.', 'высшее', 1, 03, 23466, 'жен', 21) INTO Сотрудник

VALUES (3, 'Николаев А.Н.', 'среднее-специальное', 2, 02, 23698, 'муж', 22)

INSERT INTO Сотрудник

VALUES (4, 'Алиев И.О.', 'среднее-специальное', 3, 04, 5863424, 'муж', 22)

GO

INSERT INTO ШтатноеРасписание

VALUES (1, 236, 12, 'по причине болезни')

INSERT INTO ШтатноеРасписание

VALUES (2, 230, 1, 'по причине болезни')

INSERT INTO ШтатноеРасписание

VALUES (3, 240, 2, 'по причине болезни')

INSERT INTO ШтатноеРасписание

VALUES (4, 245, 10, 'по причине болезни')

GO

SET DATEFORMAT dmy

INSERT INTO Контракт (КодКонтракта, КодСотрудника, ДатаОт, ДатаДо)

VALUES (1, 1, '04.04.09', '14.04.19') INTO Контракт (КодКонтракта, КодСотрудника, ДатаОт, ДатаДо)

VALUES (2, 2, '04.04.08', '14.04.29') INTO Контракт (КодКонтракта, КодСотрудника, ДатаОт, ДатаДо)

VALUES (3, 3, '04.04.00', '14.04.15') INTO Контракт (КодКонтракта, КодСотрудника, ДатаОт, ДатаДо)

VALUES (4, 4, '04.04.09', '14.04.29')INTO Военнообязанные(3, 1, 'не женат', 1)