Технология разработки и защиты баз данных

1.

ГБПОУ «Нижегородский Губернский колледж»
Специальность 09.02.07 Информационные системы и программирование
Курсовая работа
по модулю 11.01 «Технология разработки и
защиты баз данных»
на тему
«Разработка базы данных для организации
автоматизированного учета рабочего времени и
расчета заработной платы работника»
Работу выполнил
студент группы 31П
Рзаев Евгений

2.

Введение
В качестве рассматриваемых вопросов выбраны следующие:
- Актуальность темы исследования
- Цели и задачи, требующие решения в ходе выполнения курсовой работы
- Описание предметной области, к которой применимы поставленные задачи
- Краткое пояснение выбора конкретной СУБД для выполнения работы
- Результаты выполненных заданий
- Подведение итога / заключение

3.

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

4.

Цели и задачи
Цель работы заключается в овладении навыками
работы с СУБД и формировании новых знаний в сфере
разработки баз данных
В качестве задач были поставлены следующие:
-
Работа над описанием предметной области и
формированием представления о ней;
-
Анализ предметной области в целом, разработка
базы
данных
и
решение
уникальных
задач,
раскрывающих суть разработанной базы данных
-
Сбор всей информации в единое целое

5.

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

6.

Основные сущности
Название
Назначение
Название
Назначение
Работники
Вся информация о
каждом из работников
Информация о графике
работы (расписание дня)
Учёт времени,
проведённого работником
за производством
Учёт зарплаты для
работников
Перечень продукции,
производимой
работниками
Наименования типа
продукции
Наименования форм
оплаты
Комбинированная форма
Информация,
необходимая для расчёта
зарплаты сотрудникам с
комбинированной формой
оплаты
Информация,
необходимая для расчёта
зарплаты сотрудникам со
сдельной формой оплаты
Информация,
необходимая для расчёта
зарплаты сотрудникам с
повременной формой
оплаты
График
Установка времени
Зарплата
Продукция
Детали о продукции
Форма оплаты
Главные сущности
Сдельная форма
Повременная форма
Побочные сущности

7.

ER-Диаграмма

8.

Выбор СУБД
Выбор проводился между следующими СУБД:
- PostgreSQL
- SQLite
- MS SQL Server
- LibreOffice Base
- Oracle Database

9.

Формирование базы данных
Создание и заполнение таблиц для базы данных проводится с помощью запросов
create table workers
(
id int identity primary key,
FIO varchar(50),
Title varchar(30),
payment int,
schedule int,
Phone_Number nvarchar(11),
foreign key (payment) references payment(id),
foreign key (schedule) references schedule(id)
)
insert into workers(FIO, Title, Payment, Schedule) values ('Галкин Аскольд Созонович',
'Директор', 1, 1);
insert into workers(FIO, Title, Payment, Schedule) values ('Агафонов Тимур Петрович',
'Зам. Директора', 15, 2);
insert into workers(FIO, Title, Payment, Schedule) values ('Самойлов Варлам Федосеевич',
'Работник склада', 2, 3);
insert into workers(FIO, Title, Payment, Schedule) values ('Зимин Святослав Якунович',
'Работник склада', 3, 4);
insert into workers(FIO, Title, Payment, Schedule) values ('Соловьёв Азарий
Константинович', 'Работник', 4, 5);
insert into workers(FIO, Title, Payment, Schedule) values ('Соколов Бронислав Ильяович',
'Работник', 5, 6);
insert into workers(FIO, Title, Payment, Schedule) values ('Дроздов Игнатий Игнатьевич',
'Бухгалтер', 6, 7);
insert into workers(FIO, Title, Payment, Schedule) values ('Буров Евгений Аркадьевич',
'Бухгалтер', 7, 8);
Код для создания и заполнения таблицы «Сотрудники»
Сформированная таблица «Сотрудники»

10.

Формирование простых запросов
Вывести всю информацию по заработной плате, где сумма
выплаты больше 50000 и выплата прошла после Нового Года
select* from payment
where amount>50000 and payment_date>'2023-01-01'
Код запроса и вывод

11.

Формирование подзапросов
Вывести ФИО работников и их время на производстве
select distinct FIO, (select time_amount from timeset where timeset.worker = workers.id)
as ВремяНаПроизводстве
from workers, timeset
Код запроса и вывод

12.

Формирование запросов на соединение
Вывести ФИО рабочих и их форму оплаты
select FIO, (select payment_type_name from payment_type where payment.payment_type
= payment_type.id) as ФормаОплаты
from workers left join payment
on Workers.payment = Payment.id
Код запроса и вывод

13.

Формирование запросов на
группировку/сортировку
Вывести ФИО всех простых работников организации и их заработную плату,
отсортировать по з/п
select FIO, title, amount
from workers, payment
where payment.id = workers.payment and
order by amount
Код запроса и вывод
title = 'Работник'

14.

Формирование представлений
Создание представления, которое выводит ФИО работника,
его должность и соответствующую заработную плату. Вызов
представления, с тем условием, что зарплата сотрудника
должна быть выше 50000
create view WorkersPayment as
select Workers.FIO as WorkerName,
Workers.Title as Title,
Payment.Amount as Payment
from Workers join Payment on Workers.Payment = Payment.id
select* from WorkersPayment
where Payment > 50000

15.

Формирование хранимых процедур
Создание процедуры, которая выводит ФИО сотрудника и
высчитывает положенное ему рабочее время на день (в
соответствии с графиком работы). Изменение процедуры
путём добавления колонки «Разница времени», в которой
рассчитана разница времени, проведённого сотрудником за
работой, и времени, которое заявлено ему графиком
create proc WorkersTimeset as
select Workers.FIO as WorkerName, datediff(hour, start_time, end_time) as DayWorkTime
from workers, schedule
where workers.schedule = schedule.id
exec WorkersTimeset
alter proc WorkersTimeset as
select Workers.FIO as WorkerName, datediff(hour, start_time, end_time) as DayWorkTime,
(time_amount - datediff(hour, start_time, end_time)) TimeDiff
from workers, schedule, timeset
where workers.schedule = schedule.id and timeset.worker = workers.id
exec WorkersTimeset
Начальная
процедура
Изменённая
процедура

16.

Формирование функций
Создание функции, которая выводит ФИО всех
сотрудников, которые занимаются производством
указанного продукта
create function WorkersProduct (@Product varchar(50))
returns table as return
(
select distinct FIO, (select producttype_name from production_details where
production.product_type = production_details.id) as Product
from workers, Production, production_details
where workers.id = production.worker and (select producttype_name from
production_details where production.product_type = production_details.id) = @Product
)
select* from dbo.WorkersProduct('Модем')

17.

Формирование триггеров
Создание триггера, с помощью которого при внесении
информации о времени на производстве у новых
работников, это время имело значение 0. Изменение
триггера, что при записи данных о работнике к его часам на
производстве прибавлялось ещё 2 часа.
insert into timeset(id, worker) values (16, 1)
select top 1* from timeset
order by id desc
Начальный триггер
alter trigger NewWorkerTime
on timeset after insert as
update timeset
set time_amount = time_amount+2
where id = (select id from inserted)
insert into timeset(id, worker, time_amount) values (18, 2, 1)
select top 1* from timeset
order by id desc
Изменённый триггер

18.

Формирование курсоров
Создание курсора, который выводит список с информацией
о производимой продукции (тип продукта и кол-во
производства сотрудником), также предусмотрен вывод
кода работника, занимающегося производством данного
продукта
declare @ProductAmount varchar(50), @ProductType varchar(50), @Worker varchar(50),
@text varchar(100)
print '--------Информация о производимой продукции--------'
declare CursorProduct cursor local for
select producttype_name, amount, worker
from production_details, production
where production.product_type = production_details.id
open Cursorproduct
fetch next from cursorproduct into @Producttype, @Productamount, @Worker
while @@FETCH_STATUS = 0
begin
select @text = '| Тип продукции - ' + @Producttype + ' - производится в количестве
' + @Productamount + ' сотрудником с кодом ' + @Worker + ' |'
print @text
fetch next from cursorproduct
into @Producttype, @Productamount, @Worker
end
close cursorproduct
deallocate cursorworker

19.

Заключение
- Выполнены поставленные задачи и цели
- Представлена сформированная база данных
- База данных удовлетворяет аспектам предметной области
- Представлены этапы выполнения задач
- Закреплён теоретический материал и освоены практические навыки

20.

Список использованной литературы
• https://learn.microsoft.com/ru-ru/sql/t-sql/functions/datediff-transact-sql?view=sql-serverver16
• https://sqlex.ru/blogs/?/Primer_triggera_v_SQL_Server.html&ysclid=lp0f0tax8v745623418
• https://learn.microsoft.com/ru-ru/sql/t-sql/statements/create-trigger-transactsql?view=sql-server-ver16
• https://sql-ex.ru/blogs/?/Primer_triggera_v_SQL_Server.html&ysclid=lp0f0tax8v745623418
• https://webcodius.ru/sql/sql-funkcii-daty-i-vremeni.html?ysclid=lp04h7n2p6917036661
• https://simplesqltutorials.com/cursors/