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

МИНИСТЕРСТВО ОБРАЗОВАНИЯ РЕСПУБЛИКИ БЕЛАРУСЬ БЕЛОРУСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ИНФОРМАТИКИ И РАДИОЭЛЕКТРОНИКИ Кафедра экономической информатики Инженерно-экономический факультет

Индивидуальная работа по дисциплине «Базы данных»

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

Выполнила:

студентка гр. 972 303

Шлык В.С.

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

Минск, 2011

• ВВЕДЕНИЕ
• 1. ПОСТАНОВКА ЗАДАЧИ
• 2. ПРОЕКТИРОВАНИЕ МОДЕЛИ БАЗЫ ДАННЫХ В ERwin
• 3. СОЗДАНИЕ БАЗЫ ДАННЫХ В СУБД MS ACCESS

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

• 5. ВЫПОЛНЕНИЕ ПРОЦЕДУР И ФУНКЦИЙ
• ЗАКЛЮЧЕНИЕ
• ЛИТЕРАТУРА
• ПриЛожение

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Цель этапа:

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

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

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

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

На основе анализа предметной области мною были разработаны с помощью 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

AS

IF (@ИмяТаблицы = 'Подразделения')

SELECT @ЧислоСтрок = COUNT (*)

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

ELSE IF (@ИмяТаблицы = 'Должность')

SELECT @ЧислоСтрок = COUNT (*)

FROM Должность

ELSE IF (@ИмяТаблицы = 'Сотрудник')

SELECT @ЧислоСтрок = COUNT (*)

FROM Сотрудник

ELSE IF (@ИмяТаблицы = 'ШтатноеРасписание')

SELECT @ЧислоСтрок = COUNT (*)

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

ELSE IF (@ИмяТаблицы = 'Контракт')

SELECT @ЧислоСтрок = COUNT (*)

FROM Контракт

ELSE IF (@ИмяТаблицы = 'Военнообязанные')

SELECT @ЧислоСтрок = COUNT (*)

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

GO

DECLARE @NAME_TABLE VARCHAR (20), @COUNT_STR INT

SET @NAME_TABLE = 'Контракт'

EXEC pr_ПодсчетСтрок @NAME_TABLE, @COUNT_STR OUTPUT

SELECT @NAME_TABLE [НАЗВАНИЕ_ТАБЛИЦЫ],@COUNT_STR [ЧИСЛО_СТРОК]

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

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

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

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

SELECT *

FROM Контракт

GO

CREATE PROCEDURE pr_ИзменениеДаты

@ФЛАГ VARCHAR (20),

@КОЛ_ДНЕЙ INT

AS

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

UPDATE Контракт

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

ELSE

UPDATE Контракт

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

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

UPDATE Контракт

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

ELSE

UPDATE Контракт

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

GO

DECLARE @FLAG VARCHAR (20), @KOL_DNEJ INT

SET @FLAG = '+'

SET @KOL_DNEJ = 4

EXEC pr_ИзменениеДаты @FLAG, @KOL_DNEJ

SELECT *

FROM Контракт

GO

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

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

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

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

CREATE PROCEDURE pr_MinAvrMaxSum_КоличествоЧасов

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

@МИНИМУМ INT OUTPUT,

@СРЕДНЕЕ INT OUTPUT,

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

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

AS

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

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

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

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

SELECT @МИНИМУМ = 0, @СРЕДНЕЕ = 0, @МАКСИМУМ = 0, @СУММАРНОЕ = 0

GO

DECLARE @MIN INT, @MAX INT, @AVG INT, @SUM INT, @IMYA_ST VARCHAR (20)

SET @IMYA_ST = 'КоличествоЧасов'

EXEC pr_MinAvrMaxSum_КоличествоЧасов @IMYA_ST, @MIN OUTPUT, @AVG OUTPUT, @MAX OUTPUT, @SUM OUTPUT

SELECT @MIN [МИН], @AVG [СРЕДН], @MAX [МАКС], @SUM [СУММ]

GO

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

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

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

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

CREATE PROCEDURE pr_Выбор

@Условие1 INT,

@Условие2 INT

AS

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

SELECT *

FROM Должность

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

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

SELECT *

FROM Должность

WHERE Оклад = @Условие1

ELSE IF (@Условие1 IS NULL AND @Условие2 IS NULL)

SELECT *

FROM Должность

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

SELECT *

FROM Должность

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

GO

DECLARE @УСЛ1 INT, @УСЛ2 INT

SET @УСЛ2 = 1

EXEC pr_Выбор @УСЛ1, NULL

SELECT @УСЛ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 Кадры

ON PRIMARY

(NAME = Кадры_Data,

FILENAME = 'D:Кадры_Data.mdf',

SIZE = 3MB,

MAXSIZE = 50MB,

FILEGROWTH = 2MB),

FILEGROUP Secondary

(NAME = Кадры2_Data,

FILENAME = 'D:Кадры_Data2.ndf',

SIZE = 2MB,

MAXSIZE = 70MB,

FILEGROWTH = 20%),

(NAME = Калры3_Data,

FILENAME = 'D:Кадры_Data3.ndf',

SIZE = 2MB,

FILEGROWTH = 5MB)

LOG ON

(NAME = Склад_Log,

FILENAME = 'D:Кадры_Log.ldf',

SIZE = 1MB,

MAXSIZE = 10MB,

FILEGROWTH = 15%),

(NAME = Склад2_Log,

FILENAME = 'D:Кадры_Log2.ldf',

SIZE = 512KB,

MAXSIZE = 5MB,

FILEGROWTH = 10%)

GO

USE Кадры

GO

CREATE TABLE Подразделения (

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

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

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

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

)

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

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

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

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

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

Премия INT NULL

)

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

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

ФИО VARCHAR (60) NULL,

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

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

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

Телефон INT NULL,

Пол VARCHAR (5) NULL

CHECK (Пол IN ('муж', 'жен')),

Возраст INT NULL CHECK (Возраст > 0),

CONSTRAINT FK_Сотрудник_Подразделения FOREIGN KEY (КодПодразделения)

REFERENCES Подразделения ON UPDATE CASCADE,

CONSTRAINT FK_Сотрудник_Должность FOREIGN KEY (КодДолжности)

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

)

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

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

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

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

Пропуски INT NULL,

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

CONSTRAINT FK_ШтатноеРасписание_Сотрудник FOREIGN KEY (КодСотрудника)

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

)

CREATE TABLE Контракт (

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

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

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

ДатаДо DATETIME NULL,

Заметки VARCHAR (30) NULL,

CONSTRAINT FK_Контракт_Сотрудник FOREIGN KEY (КодСотрудника)

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

)

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

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

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

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

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

ДолгЛет INT NULL CHECK (ДолгЛет > 0),

CONSTRAINT FK_Военнообязанные_Сотрудник FOREIGN KEY (КодСотрудника)

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

)

GO

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, 'Комякевич А.Л.')

GO

INSERT INTO Должность

VALUES (01, 'Начальник', 3 000 000, 20, 500 000)

INSERT INTO Должность

VALUES (02, 'Рабочий', 1 000 000, 12, 50 000)

INSERT INTO Должность

VALUES (03, 'Бригадир', 1 500 000, 14, 70 000)

INSERT INTO Должность

VALUES (04, 'Специалист', 2 000 000, 16, 90 000)

INSERT INTO Должность

VALUES (05, 'Стажер', 900 000, 1, 10 000)

GO

INSERT INTO Сотрудник

VALUES (1, 'Шлык В.С.', 'высшее', 4, 01, 5 802 489, 'жен', 20)

INSERT INTO Сотрудник

VALUES (2, 'Ксенжук Д.Ю.', 'высшее', 1, 03, 23 466, 'жен', 21)

INSERT INTO Сотрудник

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

INSERT INTO Сотрудник

VALUES (4, 'Алиев И.О.', 'среднее-специальное', 3, 04, 5 863 424, 'муж', 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')

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

VALUES (2, 2, '04.04.08', '14.04.29')

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

VALUES (3, 3, '04.04.00', '14.04.15')

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

VALUES (4, 4, '04.04.09', '14.04.29')

GO

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

VALUES (3, 1, 'не женат', 1)

GO