Разработка базы данных для расчета зарплаты

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

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

Курсовая работа

по дисциплине

«Базы данных и информационные системы»

на тему:

«Разработка базы данных для расчета зарплаты»

Содержание

1. Введение

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

3. Проектирование информационной системы

3.1 Построение диаграммы потоков данных

3.1.1 Построение DFD-диаграммы 0-го уровня

3.1.2 Построение DFD-диаграммы 1-го уровня

3.1.3 Описание порядка построения DFD-диаграммы

3.2 Построение ER-диаграммы

3.2.1 Письменное обоснование физической организации данных

3.2.2 Описание порядка построения ER-диаграммы

3.2.3 Нормализация

4. Создание базы данных с помощью SQL сервера

4.1 Создание таблиц

4.2 Описание бизнес-правил и их физическая реализация

4.3 Заполнение таблиц данными

5. Работа с данными таблиц

5.1 Триггеры

5.2 Создание запросов

Выводы

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

1. Введение

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

SQL - Structured Query Language (Структурированный язык запросов). Язык SQL - наиболее распространённый язык управления базами данных типа клиент - сервер.

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

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

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

Стандарт SQL определяется ANSI (Американским Национальным Институтом Стандартов) и в данное время также принимается ISO (МЕЖДУНАРОДНОЙ ОРГАНИЗАЦИЕЙ ПО СТАНДАРТИЗАЦИИ). Однако, большинство коммерческих программ баз данных расширяют SQL без уведомления ANSI, добавляя разные другие особенности в этот язык, которые, как они считают, будут весьма полезны. Иногда они несколько нарушают стандарт языка, хотя хорошие идеи имеют тенденцию развиваться и вскоре становиться стандартами "рынка" сами по себе в силу полезности своих качеств. В этой книге, мы будем, в основном, следовать стандарту ANSI, но одновременно иногда будет показывать и некоторые наиболее общие отклонения от его стандарта.

Вы должны проконсультироваться с документацией вашего пакета программ который вы будете использовать, чтобы знать, где в нем этот стандарт видоизменен. ПРЕЖДЕ, ЧЕМ ВЫ СМОЖЕТЕ ИСПОЛЬЗОВАТЬ SQL, ВЫ должны понять, что такое реляционные базы данных. В этой главе, мы это объясним, и покажем насколько реляционные базы данных полезны. Мы не будем обсуждать SQL именно здесь, и если вы уже знаете эти понятия довольно хорошо, вы можете просто пропустить эту главу. В любом случае, вы должны рассмотреть три таблицы, которые предоставляются и объясняются в конце главы; они станут основой наших примеров в этой книге. Вторая копия этих таблиц находится Приложении E, и мы рекомендуем скопировать их для удобства ссылки к ним.

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

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

2. Постановка задачи:

Разработать программу расчета ежемесячной заработной платы.

Результаты расчета оформить в виде раздаточной ведомости.

3. Проектирование информационной системы

3.1 Построение диаграммы потоков данных

3.1.1 Построение DFD-диаграммы 0-го уровня:

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

1. Бухгалтерии нужно расчетать зарплату. Получить ведомость.

2. Для того, что бы произвести расчет, сначала отдел кадров составляет табель (отработанные дни и т.д.) по рабочим предприятия.

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

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

3.1.2 Построение DFD-диаграмм 1-го уровня:

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

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

- в бухгалтерии формируется раздаточная ведомость.

3.1.3 Описание порядка построения DFD-диаграммы:

1. Запустим среду построения DFD-диаграмм.

2. Щелкнем левой кнопкой мыши на значке Terminator и поместим его на DFD-диаграмму.

3. Щелкнем правой кнопкой мыши на Terminator и в контекстном меню выбираем Edit Terminator.

4. В появившемся окне меняем Terminator1 на Отдел кадров и нажимаем ОК.

5. Л.К.М щелкаем на Process и добавляем новый процесс в диаграмму.

6. Щелкнем правой кнопкой мыши на Process и в контекстном меню выбираем Edit Process.

7. В появившемся окне меняем Process1 на Формировать табель и нажимаем ОК.

8. Щелкнем левой кнопкой мыши на значке Terminator и поместим его на DFD-диаграмму.

9. Щелкнем правой кнопкой мыши на Terminator и в контекстном меню выбираем Edit Terminator.

10. В появившемся окне меняем Terminator2 на Бухгалтерия и нажимаем ОК.

11. Щелкнем левой кнопкой мыши на значке Terminator и поместим его на DFD- диаграмму.

12.Л.К.М щелкаем на Process и добавляем новый процесс в диаграмму

13.Щелкнем правой кнопкой мыши на Process и в контекстном меню выбираем Edit Process.

14.В появившемся окне меняем Process2 на Рассчитывать зарплату и нажимаем ОК.

15. Щелкаем л.к.м. на Data Flow и добавляем новый поток данных на диаграмму.

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

17. Щелкнем правой кнопкой мыши на Flow и в контекстном меню выбираем

Edit Flow. В появившемся окне меняем Flow1 на Данные о рабочем и нажимаем ОК.

17.Cм. п.15. Направляем поток данных от внешней сущности к процессу Щелкнем правой кнопкой мыши на Flow и в контекстном меню выбираем Edit Flow. В появившемся окне меняем Flow2 на Данные о зарплате и нажимаем ОК.

18. См. п.15. Направляем поток данных от процесса к внешней сущности. Щелкнем правой кнопкой мыши на Flow и в контекстном меню выбираем Edit Flow. В появившемся окне меняем Flow3 на Данные о зарплате и нажимаем ОК.

19. Добавляем хранилища данных.

19. Можно менять цвет потока данных на диаграмме.

20. Чтобы сделать уровень более высоким нужно щелкнуть правой кнопкой мыши на Process и в контекстном меню выбираем Edit Process.В появившемся окне снять метку с Lovest level и нажать ОК.

21. Нужно связать существующие потоки в новый процесс.

22. А дальше аналогично.

3.2 Построение ER-диаграммы

3.2.1 Письменное обоснование физической организации данных

Всю нашу информацию: данные о рабочих, табель, бухгалтерию, и должность будем хранить в 4-х таблицах:

Rabochi

Buhgalter

Doljnost

Vedomost

В таблице Rabochi мы будем хранить всю интересующую нас информацию о сотрудниках, а именно:

- Фамилия (Sername)

- Имя (Name)

- Номер паспорта (Pasport)

- идентификационный код (Id_cod)

В таблице Buhgalter мы будем хранить всю интересующую нас информацию о начислении зарплаты, а именно:

- Меясц (Mesyac)

- налоги (nalogi)

- зарплата которую рабочий получит на руки (na_ruki)

- оклад (oklad)

- льготы (lgoty)

- табельный номер рабочего (Tab_N)

- ставка (stava)

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

-Табельный номер рабочего (Tab_N)

- Номер паспорта рабочего (Pasport)

- Код должности (Kod_doljn)

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

- количество отработанных дней (kol_otrab_dney)

- табельный номер (Tab_N)

3.2.2 Описание порядка построения ER-диаграммы:

Запустим построение ER-диаграмм.

Создаем модель, с помощью команды меню File / New Model.

В "Target database selection" выберем формат базы данных ms SQL 7.

Добавляем три сущности. Для этого щелкаем на иконку «Entity» и потом щелкаем на область построения. Дважды щелкаем на сущности или щелкаем правой кнопкой мыши и выбираем пункт контекстного меню «Правка». Определяем логическое и физическое имя (Buhgalter, Doljnost, Rabochi, Vedomost) сущности и добавляем ее атрибуты. Можно добавлять атрибуты, индексы, ключи также свойства, настройки, определения и т.д.

Есть два способа добавить новые атрибуты к сущности. Нажимая на большую кнопку «Add», сохраняем все атрибуты, введя в появившемся окне все типы данных и свойства, которые будут использованы в атрибуте, или, нажимая на маленькую кнопку «Add», сохраняем новое имя атрибута с параметрами по умолчанию, таким способом мы можем сохранить новое имя атрибута быстрее. Добавляем такие атрибуты к сущностям:

Rabochi([Sername] Char(20) NOT NULL,

[Name] Char(20) NOT NULL,

[Pasport] Char(8) NOT NULL,

[Id_cod] Integer NOT NULL, UNIQUE ([Id_cod]),

Primary Key ([Pasport]))

Buhgalter ([kol_rab_dney] Integer NOT NULL,

[nalogi] Integer NOT NULL,

[na_ruki] money NOT NULL,

[oklad] Integer NOT NULL,

[stava] Integer NOT NULL,

[lgoty] Integer NULL,

[Tab_N] Char(10) NOT NULL,

Primary Key ([Tab_N]))

Vedomost ([kol_otrab_dney] Integer NULL,

[Tab_N] Char(10) NOT NULL,

Primary Key ([Tab_N]))

Doljnost

([Tab_N] Char(10) NOT NULL,

[Pasport] Char(8) NOT NULL,

[Kod_doljn] Char(10) NOT NULL,

[stavka] float NOT NULL,

Primary Key ([Tab_N]))



Вы можете выбрать, какие элементы вы будете показывать при помощи выпадающего списка на панели. Для маленьких диаграмм лучшим вариантом является показывать абсолютно все атрибуты. Для больших диаграмм можно выбрать сущности, первичные ключи или ключи. Выбрав «Attributes», мы видим все атрибуты.

Организуем связи. Щелкаем мышью на иконку идентифицирующей связи и добавляем связь: щелкаем на родительской сущности (Rabochi) и удерживая левую клавишу мыши тянем до дочерней сущности(Vedomost). Щелкаем мышью на иконку идентифицирующей связи и добавляем связь: щелкаем на родительской сущности (Vedomost) и удерживая левую клавишу мыши тянем до дочерней сущности(Doljnost). Щелкаем мышью на иконку идентифицирующей связи и добавляем связь: щелкаем на родительской сущности (Doljnost) и удерживая левую клавишу мыши тянем до дочерней сущности(Buhgalter).

У нас один тип связей между таблицами: идентифицирующие (mandatory- mandatory).

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

Чтобы править связь нужно дважды на ней щелкнуть или щелкнуть правой кнопкой мыши на ней. Можно изменить имя связи, элемент соединения, отношение и т.д. Можно использовать такие методы соединения: первичный ключ, уникальная связь и альтернативный ключ.

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

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

Щелкаем правой кнопкой мыши на рабочей области и меняем вид модели. Можно сделать тени, выровнять колонки и т.д.

Щелкаем на иконке и проверяем диаграмму. В появившемся окне нажимаем клавишу «Run».



Щелкая на иконке «Generate Script Icon», чтобы сгенерировать SQL код.

В появившемся диалоговом окне выбираем пункты, которые нужно сгенерировать, на вкладке «How To Generate» определяем правила и свойства. На вкладке «Entity List» можно выбрать главную модель или другую подмодель.

Нажимаем клавишу «Generate», чтобы сгенерировать SQL код, когда код сгенерирован, нажимаем «View button». Эта опция помогает с легкостью создать SQL код, не тратя при этом много времени.



3.2.3 Нормализация:

Сущность	Атрибут	Столбец	Мотивация	Тип данных	Примечание
Рабочие	Sername	Sername	Фамилия рабочего	Char(15)
Рабочие	Name	Name	Имя рабочего	Char(15)
Рабочие	Pasport	Pasport	Номер паспорта	Char(8)	Primary Key
Рабочие	Id_cod	Id_cod	Идентиф. код	Integer
Ведомость (табель)	Kol_otrab_dney	Kol_otrab_dney	Кол-во отработанных дней	Char(15)
Ведомость (табель)	Tab_N	Tab_N	Табельный номер	Char(10)	PFK
Должность	Tab_N	Tab_N	Табельный номер	Char(10)	PFK
Должность	Pasport	Pasport	Номер паспорта	Char(8)	PFK
Бухгалтерия	Mesyac	Mesyac	Месяц	Integer
Бухгалтерия	nalogi	nalogi	К-во налогов	Integer
Бухгалтерия	Na_ruki	Na_ruki	Зарплата на руки	Money
Бухгалтерия	oklad	oklad	Оклад	Integer
Бухгалтерия	lgoty	lgoty	Льготы	Integer
Бухгалтерия	Tab_N	Tab_N	Табельный номер	Char(10)	PFK
Бухгалтерия	stava	stava	Ставка	Float



Рассмотрим все наши Сущности(4). В таблицах находятся данные, которые касаются только данной сущности, то есть в них отсутствуют транзитивные и частичные ФЗ. Следовательно она приведена к 3НФ. Все ключи однозначно идентифицируют таблицы. При удалении\добавлении данных не возникает проблема «Достоверности данных». В нашем случае, всем отношениям соответствуют полная ФЗ, следовательно отношения нормализованы.

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

4. Создание базы данных с помощью SQL сервера

4.1 Создание таблиц:

-Запускаем Enterprise Manager;

-В появившемся окне «SQL server Enterprise Manager» создаём новую базу данных;

-В открывшемся окне вводим название нашей базы данных;

-Запускаем Query Analyzer:;

-В открывшемся окне «SQL Query Analyzer» пишем SQL код наших таблиц.

SQL код для создания таблиц:

Create table [Rabochi]

(

[Sername] Char(20) NOT NULL,

[Name] Char(20) NOT NULL,

[Pasport] Char(8) NOT NULL,

[Id_cod] Integer NOT NULL, UNIQUE ([Id_cod]),

Primary Key ([Pasport])

)

go

Create table [Buhgalter]

([nalogi] Integer NOT NULL,

[na_ruki] money,

[oklad] Integer NOT NULL,

[lgoty] Integer NULL,

[stava] Float NULL,

[Tab_Nomer] Integer NOT NULL,

[Pasport] Char(8) NOT NULL,

Primary Key ([Tab_Nomer],[Pasport]))

go

Create table [Vedomost]

(

[Tab_Nomer] Integer NOT NULL,

[Pasport] Char(8) NOT NULL,

[Kol_vo_otrab_dney] Integer NOT NULL,

Primary Key ([Tab_Nomer],[Pasport])

)

go

Create table [Doljnost]

(

[kod_doljn] Char(10) NULL,

[Tab_Nomer] Integer NOT NULL,

[Pasport] Char(8) NOT NULL,

Primary Key ([Tab_Nomer],[Pasport])

)

go

4.2 Бизнес правила для обеспечения целостности данных:

1. В таблице Rabochi:

-значение поля Sername должно быть: всегда заполненным, не превышать 20 символов;

-значение поля Name должно быть: всегда заполненным, не превышать 20 символов;

-значение поля Pasport должно быть: всегда заполненным, не превышать 8 символов, уникальным;

-значение поля Id_cod должно быть: всегда заполненным, не превышать 8 символов, уникальным;

2. В таблице Buhgalter:

-значение поля nalogi должно быть: всегда заполненным, целым числом, больше нуля;

-значение поля na_ruki имеет денежный тип, может быть не заполненным;

-значение поля oklad должно быть: всегда заполненным, целым числом, больше нуля;

-значение поля lgoty должно быть: всегда заполненным, целым числом;

-значение поля stava должно быть: всегда заполненным, может быть не целым числом;

-значение поля Tab_Nomer должно быть: всегда заполненным, целым числом, больше нуля, уникальным;

-значение поля Pasport должно быть: всегда заполненным, не превышать 8 символов, уникальным;

3. В таблице Vedomost:

-значение поля Tab_Nomer должно быть: всегда заполненным, целым числом, больше нуля, уникальным;

-значение поля Pasport должно быть: всегда заполненным, не превышать 8 символов, уникальным;

-значение поля Kol_vo_otrab_dney должно быть: всегда заполненным, целым числом, больше нуля;

4. В таблице Doljnost

-значение поля kod_doljn должно быть: всегда заполненным, не превышать 10 символов;

-значение поля Tab_Nomer должно быть: всегда заполненным, целым числом, больше нуля, уникальным;

-значение поля Pasport должно быть: всегда заполненным, не превышать 8 символов, уникальным;

Физическая реализация бизнес-правил:

Create table [Rabochi]

(

[Sername] Char(20) NOT NULL,

[Name] Char(20) NOT NULL,

[Pasport] Char(8) NOT NULL,

[Id_cod] Integer NOT NULL, UNIQUE ([Id_cod]),

Primary Key ([Pasport])

)

go

информационный система диаграмма база данное

Create table [Buhgalter]

(

[nalogi] Integer NOT NULL,

[na_ruki] money,

[oklad] Integer NOT NULL,

[lgoty] Integer NULL,

[stava] Float NULL,

[Tab_Nomer] Integer NOT NULL,

[Pasport] Char(8) NOT NULL,

Primary Key ([Tab_Nomer],[Pasport])

)

go

Create table [Vedomost]

(

[Tab_Nomer] Integer NOT NULL,

[Pasport] Char(8) NOT NULL,

[Kol_vo_otrab_dney] Integer NOT NULL,

Primary Key ([Tab_Nomer],[Pasport])

)

go

Create table [Doljnost]

(

[kod_doljn] Char(10) NULL,

[Tab_Nomer] Integer NOT NULL,

[Pasport] Char(8) NOT NULL,

Primary Key ([Tab_Nomer],[Pasport])

) go

4.3 Заполнение таблиц данными:

insert into rabochi values ('Варакин','Дмитрий','ТРПЛ1212','123211')Таким образом, заполняем остальные поля таблицы.

Результат:

insert into doljnost values ('1','АА111111','11','1')Таким образом, заполняем остальные поля таблицы:

Результат:

insert into vedomost values ('20','1')Таким образом, заполняем остальные поля таблицы:

Результат:

insert into Buhgalter values ('1','13','0','40','0','1','1')Таким образом, заполняем остальные поля таблицы:

Результат:

5.Работа с данными таблиц

5.1 Тригер:

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

CREATE TRIGGER Ras_Zarp ON Vedom

AFTER update

AS

declare @dni int

declare @tab int

declare @a float

declare @okl int

declare @nal int

declare @st float

declare @lg int

declare @b float

declare @zarp float

declare @z float

set @dni=(select kol_otrab_dney from inserted)

set @tab=(select Tab_N from deleted)

set @okl=(select oklad from Buhgalter where tab_n=@tab)

set @nal=(select nalogi from Buhgalter where tab_n=@tab)

set @st=(select stava from Buhgalter where tab_n=@tab)

set @lg=(select lgoty from Buhgalter where tab_n=@tab)

set @z=@dni*@st*@okl

set @a=(@z*@nal)/100

set @b=(@z*@lg)/100

set @zarp=@z-@a+@b

update Buhgalter

set na_ruki=@zarp where tab_n=@tab

{-----------------------------------------------------}

update vedom

set kol_otrab_dney=24

where tab_n=1

update vedom

set kol_otrab_dney=24

where tab_n=2

update vedom

set kol_otrab_dney=24

where tab_n=3

update vedom

set kol_otrab_dney=24

where tab_n=4

update vedom

set kol_otrab_dney=24

where tab_n=5

update vedom

set kol_otrab_dney=24

where tab_n=6

update vedom

set kol_otrab_dney=24

where tab_n=7

update vedom

set kol_otrab_dney=24

where tab_n=8

update vedom

set kol_otrab_dney=24

where tab_n=9

update vedom

set kol_otrab_dney=24

where tab_n=10

Результат:

5.2 Создание запросов:

1. Запрос № 1. Посчитать сумму зарплаты всех рабочих:

SELECT SUM(na_ruki) as 'Сумма выдачи' from Buhgalter

Результат:

Вывод:

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

Мы спроектировали базу данных для расчета зарплаты на предприятии, она позволяет реализовать следующее запросы:

- начислить зарплату за месяц, при обновлении данных о количестве отработанных дней(расчет производится автоматически после введения данных о количестве отработанных дней в таблице Vedomost);

- Выводит общую сумму которую предприятие должно выплатить рабочим.

Литература

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

2. Bruce, T. A., Designing Quality Databases with IDEF1X Information Models, Dorset House, 1992.

3. Грей П. Логика, алгебра и базы данных. - М.: Машиностроение, 1989. - 320 с.

4. Когаловский М.Р. Технология баз данных на персональных ЭВМ.- М.: Финансы и статистика,1992, - 312 с.

5. Дейт К. Введение в системы баз данных.- М.: Наука,1980. - 464 с.

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