Курсовая №4281 Разработка базы данных для информационной системы учета и обработки заявок пользователей ПК
Аннотация
В данной курсовой работе разрабатывается база данных для информационной системы учета и обработки заявок пользователей ПК. В пояснительной записке изложена постановка задачи приводятся описание предметной области логическая и физическая модели базы данных. Приводятся SQL-скрипты по созданию триггеров хранимых процедур и SQL-скрипты по модификации разработанной базы данных и наделению пользователей работающих с базой данных различными привилегиями и правами доступа.
Оглавление
Аннотация 1
Введение 3
Детализация задания 4
Модификации 4
Триггеры 4
Хранимые процедуры 4
Роли 4
1 Описание предметной области 5
2 Разработка ER-моделей базы данных 6
2.1 Глобальная логическая ER-модель исходной базы данных 6
2.2 Глобальная физическая ER-модель исходной базы данных 7
2.3 Обоснование изменений исходной базы данных 9
2.4 Глобальная логическая ER-модель модифицированной базы данных 9
2.5 Глобальная физическая ER-модель модифицированной базы данных 9
3 Описание назначения таблиц и их полей 11
4 Модификация исходной базы данных 12
4.1 Скрипт модификации исходной базы данных 12
5 Скрипт по созданию триггеров 13
6 Скрипт по созданию процедур 15
7 Скрипт по созданию ролей 16
8 Экспериментальная часть 17
8.1 Проверка работы триггеров 17
8.2 Проверка работы хранимых процедур 19
8.3 Права пользователей 20
Заключение 22
Список используемых источников 23
_________________________________________________________________
Работа № 4281. Это
Advertisement
ОЗНАКОМИТЕЛЬНАЯ ВЕРСИЯ работы.
Цена оригинала 1000 рублей. Оформлен в программе Microsoft Word.
Оплата. Контакты
_________________________________________________________________
Введение
Для увеличения производительности постоянно разрабатываются новые информационные системы с новыми алгоритмами и новым функционалом. Разработка ИС — сложный и многоступенчатый процесс. Чтобы получить выгоду от использования ИС ее следует создавать в короткие сроки и с наименьшими затратами. Основой информационных систем являются базы данных.
База данных — это совместно используемый набор логически связанных данных и описание этих данных предназначенный для удовлетворения информационных потребностей учреждения.
Большинство ИС основываются на использовании различных систем управления базами данных СУБД таких как Microsoft SQL Server FireBird Oracle. Для удобства работы с базами данными создаются клиентские приложения.
Для нормальной работы проекта ИС должна быть грамотно спроектирована должны быть построены полные и непротиворечивые функциональные и информационные модели. Необходимо учитывать изменения предпочтений пользователей вследствие чего требуется уточнять и изменять разработанные модели.
В курсовой работе выполняется задача по сопровождению БД внесению модификаций в структуру БД написанию различных процедур и триггеров упрощающих работу с БД созданию пользователей и наделению их необходимыми правами. В качестве используемой СУБД выбрана MS SQL Server 2008. В качестве преимуществ MS SQL Server 2008 можно отметить простоту администрирования возможность подключения к Web
быстродействие и функциональные возможности механизма сервера СУБД
наличие средств удаленного доступа. Для написания скриптов используется язык SQL — это легкий для понимания язык и в то же время универсальное программное средство управления данными.
Детализация задания
Тема разработка базы данных для информационной системы учета и обработки заявок пользователей ПК.
Предусмотрено выполнение следующих заданий
Модификации
1 Объединить ФИО сотрудников в одно поле.
2 Объединить ФИО исполнителей в одно поле.
3 Объединить дату и время заявки в одно поле.
Триггеры
1 Невозможность установки флага «выполнено» без установки флага «принято».
2 Невозможность установки даты выполнения заявки раньше даты приема.
3 Невозможность выставить новому сотруднику ПК который уже занят другим пользователем.
Хранимые процедуры
1 Вывести список выполненных заявок за текущий месяц.
2 Выставить всем невыполненным заявкам за предыдущий месяц статус выполнено.
3 Повысить количество выполненных заявок по каждому исполнителю за предыдущий месяц.
Роли
1 Роль «исполнитель» обладает правами на чтение всех таблиц изменение и удаление данных в таблице «Заявки».
2 Роль «пользователь» обладает правами на чтение всех таблиц и добавление данных в таблице «Заявки».
3 Роль «администратор» обладает полным доступом ко всей базе данных.
1 Описание предметной области
В данном курсовом проекте в качестве предметной области рассматривается база данных для системы формирования и обработки заявок пользователей. База данных хранит информация о пользователях ФИО Отдел ПК об исполнителях список возможных неисправностей и непосредственно сами заявки в которые входит такая информация как пользователь подавший заявку время дата и время формирования заявки неисправность флаги состояний принятия и выполнения заявок исполнитель обработавший заявку.
С помощью встроенных процедур можно узнать какие заявки были выполнены за текущий месяц узнать количество выполненных заявок за предыдущий месяц проставить всем невыполненным заявкам за прошлый месяц статус выполнено.
Информационная система на основе данной базы данных должна работать следующим образом при возникновении неисправности в работе какого-либо устройства на своем рабочем месте пользователь создает заявку указывая наиболее подходящую к его случаю неисправность. После формирования заявки с клиентского приложения в нее добавляется информация о дате и времени формирования заявки а так же о пользователе подавшем заявку. Далее она отправляется по ЛВС на серверную часть приложения и записывается в базу данных. После чего серверная часть оповещает о новой заявке все клиентские части приложения в которые выполнен вход под учетной записью исполнителя. Свободный исполнитель принимает заявку и занимается исправлением неисправности после чего ставит статус «выполнено» и закрывает заявку. Изменения сохраняются в базе данных.
2 Разработка ER-моделей базы данных
Для разработки модели базы данных использовался программный продукт ERwin.
В результате проектирования были созданы логическая и физическая модели. В логической ER-модели все объекты системы именуются на языке понятном для любого пользователя работающего с данной моделью. Физическая ER-модель содержит описание реальной базы данных в терминах конкретной СУБД. В физической модели создаются домены типы данных используемые в СУБД.
2.1 Глобальная логическая ER-модель исходной базы данных
На начальном этапе проектирования была разработана логическая модель базы данных представленная на рисунке 1.
Рисунок 1. Глобальная логическая ER-модель исходной базы данных
2.2 Глобальная физическая ER-модель исходной базы данных
Анализируя предметную область и глобальную логическую ER—модель а также используя знания о типах данных получаем глобальную физическую ER-модель представленную на рисунке 2. Объекты модели обозначаются латинскими символами. В физической модели важно описать всю информацию о таблицах колонках индексах и т.д.
Рисунок 2. Глобальная физическая ER-модель исходной базы данных
2.3 Обоснование изменений исходной базы данных
При использовании исходного варианта БД были выявлены недочеты
проектирования которые необходимо исправить сохранив уже введенные
данные.
1 Для упрощения ввода информации и составления запросов объединить ФИО сотрудников в одно поле.
2 Для упрощения ввода информации и составления запросов объединить ФИО исполнителей в одно поле.
3 Для упрощения базы данных объединить дату и время формирования заявки в одно поле.
2.4 Глобальная логическая ER-модель модифицированной базы
данных
Выполнив модификации получим логическую ER-модель модифицированной базы данных представленную на рисунке 3.
Рисунок 3. Глобальная логическая ER-модель модифицированной базы данных
2.5 Глобальная физическая ER-модель модифицированной базы
данных
Физическую ER-модель модифицированной базы данных приведена на рисунке 4
Рисунок 4. Глобальная физическая ER-модель модифицированной базы данных
3 Описание назначения таблиц и их полей
При разработке использовались следующие типы данных
Тип данных Описание
INTEGER 4-байтные целочисленные данные
VARCHAR20 Символьные данные длины 20
DATETIME Дата созданиявыполнения заявки
BIT Флаг принятозавершено
Таблицы БД и их поля
1.Таблица DEPARTMENT содержит информацию об отделе.
• Department_id РК INTEGER NOT NULL – код отдела
• Department_name VARCHAR20 NOT NULL – название отдела
2. Таблица EMPLOYEES содержит информацию о сотрудниках.
• Employee_id РК INTEGER NOT NULL – код сотрудника
• Department FK INTEGER NOT NULL – код отдела
• Name VARCHAR20 NOT NULL – имя сотрудника
• Role FK INTEGER NOT NULL – код роли
• PC_number INTEGER NOT NULL – номер ПК
3. Таблица ROLE содержит информацию о ролях пользователей.
• Role_id РК INTEGER NOT NULL – код роли
• Role_name VARCHAR20 NOT NULL – название роли
4.Таблица EXECUTOR содержит информацию об исполнителях.
• Executor_id РК INTEGER NOT NULL – код исполнителя
• Name VARCHAR20 NOT NULL – имя исполнителя
• Role FK INTEGER NOT NULL – код роли
5. Таблица DISREPAIR содержит информацию о неисправностях.
• Disrepair_id РК INTEGER NOT NULL – код неисправности
• Disrepair_name VARCHAR20 NOT NULL – название неисправности
6.Таблица REQUEST содержит информацию о заявках.
• Request_id РК INTEGER NOT NULL – код заявки
• Employee FK INTEGER NOT NULL – код сотрудника
• Executor FK INTEGER NOT NULL – код исполнителя
• Disrepair FK INTEGER NOT NULL – код неисправности
• Create_datetime DATETIME NOT NULL – дата создания заявки
• Accepted BIT NOT NULL – флаг принято
• Done BIT NOT NULL – флаг завершено
• Execution_datetime DATETIME NOT NULL – дата и время завершения
4 Модификация исходной базы данных
4.1 Скрипт модификации исходной базы данных
Для упрощения ввода информации и составления запросов объединим ФИО сотрудников в одно поле.
Добавим новый столбец Name в таблицу EMPLOYEES и запишем в него ФИО из аналогичных столбцов этой таблицы. Удалим ненужные столбцы.
ALTER TABLE Employees ADD Name VARCHAR60
UPDATE Employees SET Name = SELECT Last_name + ‘ ‘ + First_name + ‘ ‘ + Third_name
ALTER TABLE Employees DROP COLUMN First_name Last_name Third_name
Для упрощения ввода информации и составления запросов объединим ФИО исполнителей в одно поле.
Добавим новый столбец Name в таблицу EXECUTOR и запишем в него ФИО из аналогичных столбцов этой таблицы. Удалим ненужные столбцы.
ALTER TABLE Executor ADD Name VARCHAR60
UPDATE Executor SET Name = SELECT Last_name + ‘ ‘ + First_name + ‘ ‘ + Third_name
ALTER TABLE Executor DROP COLUMN First_name Last_name Third_name
Для упрощения базы данных объединим дату и время формирования заявки в одно поле.
Добавим новый столбец Create_datetime в таблицу REQUEST и запишем в него ФИО из аналогичных столбцов этой таблицы. Удалим ненужные столбцы.
ALTER TABLE Request ADD Create_datetime DATETIME
UPDATE Request SET Create_datetime = SELECT DATEADDday DATEDIFFday’19000101’Date CASTTime AS DATETIME27
ALTER TABLE Request DROP COLUMN Date Time
5 Скрипт по созданию триггеров
Создать триггер который будет предотвращать возможность установки даты выполнения раньше даты приема
CREATE TRIGGER dbo.Check_date
ON Request AFTER UPDATE
AS IF UPDATE Execution_date
BEGIN
DECLARE @exec_date DATETIME
DECLARE @acc_date DATETIME
SELECT @exec_date = SELECT r.Execution_date FROM Request r INSERTED WHERE r.id = INSERTED.id
SELECT @acc_date = SELECT r.Create_datetime FROM Request r INSERTED WHERE r.id = INSERTED.id
Создать триггер который будет предотвращать возможность установки флага «выполнено» без установки флага «принято»
CREATE TRIGGER dbo.Check_done
ON Request AFTER INSERT
AS IF UPDATE Done
BEGIN
DECLARE @accepted BIT
DECLARE @done BIT
SELECT @accepted = SELECT r.Accepted FROM Request r INSERTED WHERE r.id = INSERTED.id
SELECT @done = SELECT r.Done FROM Request r INSERTED WHERE r.id = INSERTED.id
Создать триггер запрещающий выставлять новому сотруднику номер ПК который уже занят другим пользователем
CREATE TRIGGER dbo.Check_PCNum
ON Employees AFTER INSERT
AS IF UPDATE PC_number
BEGIN
DECLARE @pcNumNew INT
SELECT @pcNumNew = SELECT e.PC_number FROM Employees e INSERTED WHERE e.id = INSERTED.id
6 Скрипт по созданию процедур
Процедура выводящая список выполненных заявок за текущий месяц
CREATE PROCEDURE dbo.DoneList
AS
BEGIN
SELECT em.Name AS ‘Заявитель’ d.Disrepair_name AS ‘Неисправность’ r.Create_datetime AS ‘Дата и время заявки’
ex.Name AS ‘Исполнитель’ r.Execution_date AS ‘Дата и время выполнения’
FROM Request r Employees em Disrepair d Executor ex
WHERE r.Employer = em.id AND d.id = r.Disrepair AND ex.id = r.Executor AND done = 1
Процедура выводящая количество выполненных заявок по каждому пользователю за предыдущий месяц
CREATE PROCEDURE dbo.RequestCount
AS
BEGIN
SELECT e.Name AS ‘Исполнитель’
SELECT COUNTr.done FROM Request r WHERE r.Executor = e.id
AND MONTHr.Create_datetime = MONTHDATEADDMONTH -1 GETDATE AND YEARr.Create_datetime = YEARGETDATE AS ‘Количество выполненных заявок’
Процедура выставляющая всем невыполненным заявкам за предыдущий месяц статус выполнено
CREATE PROCEDURE dbo.CloseRequests
AS
BEGIN
UPDATE Request SET Done = 1
WHERE MONTHCreate_datetime = MONTHDATEADDMONTH -1 GETDATE
7 Скрипт по созданию ролей
Создание пользователей
CREATE LOGIN User_1 WITH PASSWORD = ‘ABCD’
CREATE LOGIN User_2 WITH PASSWORD = ‘EFGH’
CREATE LOGIN Admin WITH PASSWORD = ‘IJKL’
CREATE USER User_1 FOR LOGIN User_1
CREATE USER User_2 FOR LOGIN User_2
CREATE USER Admin FOR LOGIN Admin
Создание ролей
CREATE ROLE Employee_R
CREATE ROLE Executor_R
CREATE ROLE Admin_R
Наделение ролей соответствующими правами
GRANT SELECT ON Department TO Employee_R
GRANT SELECT ON Disrepair TO Employee_R
GRANT SELECT ON Employees TO Employee_R
GRANT SELECT ON Executor TO Employee_R
GRANT SELECT ON Request TO Employee_R
GRANT SELECT ON Roles TO Employee_R
GRANT INSERT ON Request TO Employee_R
GRANT SELECT ON Department TO Executor_R
GRANT SELECT ON Disrepair TO Executor_R
GRANT SELECT ON Employees TO Executor_R
GRANT SELECT ON Executor TO Executor_R
GRANT SELECT ON Request TO Executor_R
GRANT SELECT ON Roles TO Executor_R
GRANT UPDATE DELETE ON Request TO Executor_R
8 Экспериментальная часть
8.1 Проверка работы триггеров
Проверим триггер который будет запрещать устанавливать флаг «Выполнено» без установленного флага «Принято».
Создадим новую запись в таблице REQUEST и заполним необходимые поля.
Рисунок 5. Результат работы триггера dbo.check_done
Проверим триггер который будет запрещать устанавливать дату выполнения раньше даты создания. Создадим новую запись в таблице REQUEST и заполним необходимые поля.
Рисунок 6. Результат работы триггера dbo.Check_date
Проверим триггер который будет запрещать выставлять новому сотруднику номер ПК занятый другим сотрудником. Создадим новую запись в таблице EMPLOYEES и заполним необходимые поля.
Рисунок 7. Результат работы триггера dbo.Check_PCNum
8.2 Проверка работы хранимых процедур
Процедура выводящая список выполненных заявок за текущий месяц
Рисунок 8. Результат работы процедуры dbo.DoneList
Процедура выставляющая всем невыполненным заявкам за предыдущий месяц статус выполнено
Рисунок 9. Таблица REQUEST до выполнения процедуры
Рисунок 10. Таблица REQUEST после выполнения процедуры
8.3 Права пользователей
Рисунок 11. Выданные права для роли Admin_R
Рисунок 12. Выданные права для роли Employee_R
Рисунок 13. Выданные права для роли Executor_R
Заключение
Список используемых источников