 |
Отчетные хранимые процедуры и функции
|
|
|
|
Для формирования отчетных результатов вспомогательные процедуры и функции были объединены в несколько общих функций, которые на основе всех реализованных запросов получают необходимые для отчета данные.
Процедуры и функции:
PrintPodr -функция вывода работы подразделения с определенной даты
PrintElevator-Личная карточка лифта со всей историей
Пример отчета личной карточки лифта со всей историей:
В качестве входных параметров подаем личный номер лифта, для которого будет выводиться отчет. Далее используя эти параметры получаем данные овсех его поломках, ремонтах, техосмотрах и застреваний.(см. приложение 8)
--Личная карточка лифта со всей историей
CREATEFUNCTIONPrintElevator
(@NumVARCHAR(10))
RETURNS@ResultTABLE(
NameStreetVARCHAR(40),
PersonalNumberINT,
FIOVARCHAR(80),
TypeOfAppVARCHAR(80),
TypeOfDefectVARCHAR(80),
BreakDescriptionVARCHAR(256),
BreakLevelBIT,
BreakTimeDATETIME,
SpecialityVARCHAR(40)
)
AS
BEGIN
INSERTINTO@Result
SELECT
NameStreet [Улица],
PersonalNumber [№ лифта],
FIO [ФИО],
TypeOfApp [Видзаявки],
TypeOfDefect [Виднеисправности],
BreakDescription [Описаниеполомки],
BreakLevel [степень],
BreakTime [времяполомки],
Speciality [Специальность]
FROMStreetSTINNERJOINElevatorsEL
ONST.IDStreet=EL.IDStreet
ANDPersonalNumber=@Num
INNERJOINRequestRE
ONEL.IDElevator=RE.IDElevator
INNERJOINTypeOfApplicationTA
ONRE.IDTypeOfApplication=TA.IDTypeOfApplication
INNERJOINOrdersORD
ONRE.IDRequest=ORD.IDRequest
INNERJOINElectricianELC
ONORD.IDElectrician=ELC.IDElectrician
INNERJOINDefectsDFS
ONORD.IDOrders=DFS.IDOrders
INNERJOINDefectDF
ONDFS.IDDefect=Df.IDDefect
RETURN;
END
SELECT*FROMPrintElevator(6)
Обеспечение безопасности
В ходе изучения предметной области в рамках курсового проекта по дисциплине «Управление данными» были определены основные пользователи для работы с базой данных:
· Диспетчер
· Администратор
Диспетчер вносит/редактирует данные в нарядах, заявках, а также создает все необходимые отчеты
|
|
|
Администратор создает и редактирует все справочники
| Диспетчеры | Администраторы | |
| Заполнение/редактирование справочников | - | + |
| Заполнение/редактирование заявок | + | - |
| Заполнение/редактирование нарядов | + | - |
| Создание отчетов | + | - |
| Просмотр данных | + | + |
Так как диспетчеров в организации несколько, а также возможно администраторов тоже будет 2 (посменно или же на пол ставки), было создано две роли:
· Диспетчеры
· Администраторы
--роль диспетчеры
CREATEROLEDispatchers
--рольАдминистраторы
CREATEROLEAdministrators
Ролям были выделены следующие права доступа(см. приложение 9):
| Dispatchers | Administrators | |
| Street | R | RWED |
| Electrician | R | RWED |
| TypeOfApplication | R | RWED |
| Defect | RWED | R |
| Elevator | R | RWED |
| Request | RWED | R |
| Orders | RWED | R |
| Defects | RWED | R |
| PrintElevator | R | - |
| PrintPodr | R | - |
| print1El | R | - |
| Printall | R | - |
Где R – права на чтение, W - права на запись, E – права на редактирование, D – права на удаление.
В свою очередь на каждую роль были созданы пользователи (см. приложение 9):
· Диспетчер1
· Диспетчер2
· Диспетчер3
· Администратор1
· Администратор2
-- Пользователь – Диспетчер1
USEElevator
CREATEUSERDispatcher1
WITHOUTLOGIN
-- Пользователь – Администратор1
USEElevator
CREATEUSERAdministrator1
WITHOUTLOGIN
Затем пользователи были добавлены в роли:
--добавление членов в роль Dispatchers базы данных
sp_addrolememberDispatchers,
Dispatcher1
sp_addrolememberDispatchers,
Dispatcher2
sp_addrolememberDispatchers,
Dispatcher3
--добавлениечленовврольAdministratorsбазыданных
sp_addrolememberAdministrators,
Administrator1
sp_addrolememberAdministrators,
Administrator2
Таким образом, безопасность базы данных была обеспечена за счет создания необходимого числа учетных записей для различных ролей пользователей БД, ограничения их доступа к таблицам и предоставления им права выполнения хранимых процедур, отвечающих за необходимые им действия.
Оптимизация
Несмотря на то, что пользователей у системы немного, это не понижает требования к ее быстродействию.
|
|
|
Первоначально анализ оптимизации проводился на уровне запросов, было выяснено, что в первую очередь надо связывать сущности, у которых меньше строк и больше критериев для соединения, и далее по возрастанию, тогда результирующий набор строк будет формироваться быстрее. Кроме того, по возможности стоит проанализировать все вводимые данные и на их основе сформировать ограничения CHECK. При реализации процедур и функций по возможности стоит избегать сильной вложенности одних процедур в другие, это замедляет поиск. Триггеры стоит использовать только в крайних случаях, потому что они сильно нагружают систему.
Для того, чтобы поиск по данным и запросы осуществлялись быстрее были введены такие объекты базы данных, как индексы.
Индекс – особый объект БД, создаваемый с целью увеличения производительности поиска данных. По умолчанию в СУБД Microsoft SQL Server создаются кластеризованные индексы. Помимо кластеризованных, в данном курсовом проекте были использованы некластеризованные индексы, которые накладывались на внешние ключи, и индексы с ограничением на уникальность.
Примеры индексов (см. приложение 10)
--TypeOfApplication
CREATE UNIQUE INDEX UniqueTypeOfApplicationIndex
ON TypeOfApplication(TypeOfApp ASC)
--Defect
CREATE UNIQUE INDEX UniqueDefectIndex
ON Defect(TypeOfDefect ASC)
--Elevators
CREATENONCLUSTEREDINDEXElevatortsIDStreet
ONElevators(IDStreetASC)
CREATEUNIQUEINDEXUniqueElevatorsIndex
ONElevators(Building,Door,PersonalNumberASC)
Тестирование
Для проверки корректности физической модели и правильности реализованных процедур и функций в хранилище данных были введены тестовые данные для каждой из имеющихся сущностей.
Процедура тестирования заключалась в том, что с помощью скрипта создавалась новая база данных. Далее создавались все декларативные ограничения, накладываемые на эти таблицы. После создавались хранимые процедуры вставки, обновления и удаления; хранимые процедуры и функции, необходимые для формирования отчетом; триггеры; индексы; матрица доступа. Далее запускались тестовые данные для таблиц, затем вызывались функции формирования отчетов. (см. приложение 11)
EXECPStreetINSERT
'Мороозова'
EXECPStreetINSERT
'Лесозаводская'
EXECPStreetINSERT
'Бабушкина'
EXECPElectricianINSERT
'Пупкин Петр Сергеевич','4','Аварийный электромеханик','03.11.1977'
|
|
|
EXECPElectricianINSERT
'Сидоров Сергей Михайлович','1','Основной электромеханик','12.01.1968'
EXECPElectricianINSERT
'Кузнецов Анатолий Александрович','3','Аварийный электромеханик','10.08.1972'
EXECPElectricianINSERT
'Худяев Иосив Абрамовичу','4','Основной электромеханик','24.09.1972'
EXECPDefectINSERT
'Застревание'
EXECPDefectINSERT
'Изношен трос'
EXECPDefectINSERT
'Поломка приводной цепи'
EXECPDefectINSERT
'Поломка буфера'
EXECPElevatorsINSERT
'333','175','3',3
EXECPElevatorsINSERT
'2','111','1',4
EXECPElevatorsINSERT
'6','45','1',5
EXECPElevatorsINSERT
'19','74','2',6
EXECPTypeOfApplicationINSERT
'Поломка'
EXECPTypeOfApplicationINSERT
'Застревание'
EXECPTypeOfApplicationINSERT
'Техосмотр'
EXECPRequestINSERT
'не открывается лифт','01.08.2011 11:10:00:00',0,1,1
EXECPRequestINSERT
'не работает свет','05.07.2011 09:00:00:00',0,4,1
EXECPRequestINSERT
'Технический осмотр','20.01.2012 16:45:00:00',0,5,3
EXECPRequestINSERT
'застрял человек','09.05.2012 14:40:00:00',1,6,2
EXECPOrdersINSERT
'01.08.2011 15:30:00:00',3,13
EXECPOrdersINSERT
'05.07.2011 012:00:00:00',4,14
EXECPOrdersINSERT
'20.01.2012 17:00:00:00',5,15
EXECPOrdersINSERT
'09.05.2012 14:50:00:00',6,16
EXECPDefectsINSERT
6,1
EXECPDefectsINSERT
5,2
EXECPDefectsINSERT
8,3
EXECPDefectsINSERT
7,4
EXECPDefectsINSERT
4,5
EXECPDefectsINSERT
3,6
Вывод отчетов
SELECT*FROMprintall
SELECT*FROMprint1El
SELECT*FROMPrintPodr('05.07.2011 012:00:00:00','Основной электромеханик')
SELECT*FROMPrintElevator(6)
Заключение
Данный курсовой проект является продолжением курсовых проектов по дисциплинам «Управление данными» и «Информационные технологии». В ходе выполнения этих курсовых проектов была выбрана и изучена предметная область «Обслуживание лифтов города Сыктывкар компанией ООО «ОТИС-Лайн»», выделены такие основные сущности и процессы, как Street, Electrician, TypeOfApplication, Defect, Elevators, Request, Orders и Defects, была построена логическая модель базы данных, а также определены атрибуты таблиц и выбраны типы данных.
В данном курсовомпроекте была реализована физическая модель базы данных из вышеперечисленных сущностей, были расставлены ограничения PRIMARY KEY, FOREIGN KEY, UNIQUE, проанализированы данные, которые будут храниться в БД, и на их основе были выделены ограничения CHECK. Помимо декларативной целостности была реализована процедурная целостность, соответствующая всем правилам данной предметной области.
|
|
|
На основе сформированных в курсовом проекте по «УД» запросов были реализованы хранимые процедуры и функции, которые обеспечивают управление базой данных, а также предоставление пользователям всех необходимых выходных форм.
Были реализованы все необходимые процедуры добавления и удаления данных, было решено, что функции редактирования нужны не для всех таблиц.Был реализован триггер для таблицы Электромеханик, связанный с запретом понижения разряда электромеханика.
Для обеспечения безопасности и надежности хранилища данных были выделены две основные роли Диспетчеры и Администраторы и в них были включены основные пользователи системы, такие как Диспетчер1, Диспетчер2, Диспетчер3, Администратор1 и Администратор2, и им были предоставлены только необходимые им права на работу с данными хранилища.
В рамках курсового проекта были протестированы все процедуры ввода, удаления и изменения данных, а так же процедуры создания отчетов. Тестирование БД позволило установить корректность физической модели базы данных, всех процедур и функций, реализованных для данного курсового проекта.
Таким образом, в результате выполнения курсового проекта было создано хранилище данных для информационной системы «Учет обслуживания, технического осмотра и ремонта лифтов в домах города Сыктывкар компанией ООО «ОТИС-Лайн»»необходимое для ее реализации.
Библиографический список
1. Виейра Роберт. Программирование баз данных Microsoft SQL Server 2005 для профессионалов.: Пер. с англ. – М.: ООО «И.Д.Вильямс», 2008. – 1072 с.: ил. – Парал.тит.англ.;
2. К. Дж. Дейт. Введение в системы баз данных.: Пер. с англ. М.: Изд. Вильямс, 2006. - 1328 с.: ил. – Парал. тит. англ.;
3. Коннолли Томас, Бегг Каролин. Базы данных: проектирование, реализация и сопровождение. Теория и практика, 3-е изд.: Пер. с англ. – М.: Издательский дом «Вильямс», 2003. – 1440 с.: ил. – Парал. тит. англ.;
4. Николаева Н.А. Язык структурированных запросов. Лабораторные работы: учебное пособие / Н.А. Николаева, Т.Ю. Калинина. – Ухта: УГТУ, 2006. – 124 с. ил.
Приложение 1
Логическая модель
Приложение 2
Физическая модель БД
Приложение3
Созданиетаблиц
CREATE DATABASE Elevator
GO
--Созданиетаблиц
CREATE TABLE Street --Улица
(
IDStreet INT IDENTITY NOT NULL,
NameStreetVARCHAR(40) NOT NULL
);
CREATE TABLE TypeOfApplication --Видзаявки
(
IDTypeOfApplication INT IDENTITY NOT NULL,
TypeOfAppVARCHAR(80) NOT NULL
);
CREATE TABLE Electrician --Елекромеханик
(
IDElectrician INT IDENTITY NOT NULL,
FIO VARCHAR(80) NOT NULL,
Class INT NOT NULL,
SpecialityVARCHAR(40) NOT NULL,
Birthday DATETIME NOT NULL
);
CREATE TABLE Defect --Неисправность
(
IDDefect INT IDENTITY NOT NULL,
|
|
|
TypeOfDefectVARCHAR(80) NOT NULL
);
CREATE TABLE Elevators --Лифт
(
IDElevator INT IDENTITY NOT NULL,
PersonalNumberVARCHAR(10) NOT NULL,
Building VARCHAR(10)NOT NULL,
Door INT NOT NULL, --подъезд
IDStreet INT NOT NULL,
);
CREATE TABLE Request --Заявка
(
IDRequest INT IDENTITY NOT NULL,
BreakDescriptionVARCHAR(256) NOT NULL,
BreakTime DATETIME NOT NULL,
BreakLevel BIT NOT NULL DEFAULT 0,
IDElevator INT NOT NULL,
IDTypeOfApplication INT NOT NULL,
);
CREATE TABLE Orders --наряд
(
IDOrders INT IDENTITY NOT NULL,
OrdersTime DATETIME,
IDElectrician INT NOT NULL,
IDRequest INT NOT NULL,
);
CREATE TABLE Defects --Неисправности
(
IDOrders INT,
IDDefect INT,
);
Приложение4
Удаление таблиц
--Удаление таблиц
DROPTABLEStreet;
DROP TABLE Electrician;
DROP TABLE TypeOfApplication;
DROP TABLE Defect;
DROP TABLE Elevator;
DROP TABLE Request;
DROP TABLE Orders;
DROPTABLEDefects;
Приложение 5
|
|
|
