 |
Обеспечение целостности БД
|
|
|
|
Курсовой проект на тему
Разработка базы данных для системы
«Учет состояния пожарных гидрантов и водоемов, закрепленных за учебной пожарной частью»
Дисциплина
«СУБД»
Выполнил студент группы ИСТ-09
Волошин И.С.
Проверил преподаватель кафедры ИСТ
Вокуева Т.А.
УХТА, 2012
Огавление
Введение. 3
1. Постановка задачи. 4
1.1 Описание предметной области. 4
1.2 Функциональные требования. 5
2.Физическая модель. 6
2.1. Фрагмент БД "Учет водоисточников". 6
2.2. Фрагмент БД "Учет неисправностей". 7
2.3. Фрагмент БД "Учет отправленных писем". 7
2.4. Используемая СУБД.. 8
3.Обеспечение целостности БД.. 9
4. Создание таблиц и ограничений. 12
5.Хранимые процедуры.. 19
6. Выходные формы.. 22
7. Обеспечение безопасности. 25
8. Оптимизация. 28
9. Тестирование. 29
Заключение. 34
Библиографический список. 37
Приложение 1 - логическая модель. 38
Приложение 2 - физическая модель. 39
Приложение 3 – письмо в ГосПожНадзор. 40
Приложение 4 – журнал проверок (Личная карточка водоисточника) 41
Приложение 5 – создание таблиц. 42
Приложение 6 – создание ограничений на таблицы.. 43
Приложение 7 –процедуры добавления. 45
Приложение 8 – процедуры обновления. 47
Приложение 9 – процедуры удаления. 49
Приложение 10 – триггеры.. 51
Приложение 11 – отчетные формы.. 52
Приложение 12 – безопасность. 58
Роли. 58
Праванароли. 58
Пользователи. 59
Добавлениепользователейкролям.. 59
Приложение 13 – оптимизация. 60
Приложение 14 – тестирование. 61
Введение
В подрайоне выезда учебной пожарной части находятся 99 источников наружного водоснабжения. На данный момент учет состояния этих водоемов и гидрантов ведется ручным способом. После проверки пожарного водоема или гидранта начальник караула вносит запись в журнал о результатах проверки. Если пожарный водоем или гидрант находится в неисправном состоянии, то об этом начальник караула сообщает начальнику части, который в свою очередь отправляет письмо с запросом на устранение неисправностей в обслуживающую организацию, через установленный срок пожарный водоем или гидрант перепроверяется.
|
|
|
Такой вид организации учета состояния пожарных водоемов и гидрантов приводит к следующим проблемам:
1. Сложность и длительность процесса поиска в журнале записей по пожарным водоемам и гидрантам, которые необходимо перепроверить в заданный день;
2. Сложность и длительность процесса составления письма с запросом на устранение неисправностей;
3. Длительность процесса поиска конкретного гидранта или водоема;
Введение системы автоматизирует данный процесс и тем самым упростит данный вид деятельности пожарной части.
Краткое содержание курсового проекта:
1. В первой главе описывается бизнес-процесс учета состояния пожарных водоемов и гидрантов, закрепленных заучебной пожарной частью. Доказывается актуальность данной работы, ставятся цели.
2. Во второй главе описывается переход от логической модели базы данных к физической. Описываются два основных способа поддержания целостность БД: декларативный и процедурный.
3. Третья глава посвящена выходным формам, а именно хранимым процедурам и функция, которые были реализованы для выведения отчетности.
4. В четвертой главе описываются основные методы организации обеспечения безопасности базы данных.
5. В пятой главе приводится описание оптимизации базы данных за счет индексов и анализа запросов.
6. В шестой главе приводится тестирование базы данных.
Постановка задачи
Описание предметной области
В соответствии с федеральным законом «О пожарной безопасности» необходимо поддерживать пожарные водоемы и гидранты в исправном состоянии. В каждом муниципалитете обслуживание пожарных водоемов и гидрантов ведут различные организации, но проверку на исправность ведет пожарная охрана.
|
|
|
В г.Сыктывкаре создан реестр пожарных водоемов и гидрантов, который утвержден администрацией г. Сыктывкара. В данном реестре указаны все пожарные водоемы и гидранты находящиеся на территории г.Сыктывкара, их характеристики и обслуживающие организации.
Вся территория г.Сыктывкара разделена на подрайоны, то есть на некоторые части города. Каждый подрайон закреплен за определенной пожарной частью.
В соответствии с этим каждая пожарная часть ведет надзорную деятельность за пожарными водоемами и гидрантами только в своем подрайоне.
В подрайоне учебной пожарной части находится 99 источников наружного водоснабжения, из них 81 пожарный гидрант и 18 пожарных водоемов. По плану каждый водоем или гидрант проверяется 1 раз в месяц.
Во время несения боевой службы бойцы караула выезжают на проверку пожарных водоемов и гидрантов, результатом проверки является запись в журнал (приложение 4) о том, исправен или нет пожарный водоем или гидрант. Если обнаружена неисправность, то об этом сообщается начальнику части, устанавливаются сроки устранения неисправности в зависимости от объема работ необходимых для устранения, и высылается письмо-запрос в обслуживающую организацию на устранение неисправности. В письме содержится информация о дате проверки, обнаруженной неисправности и указание сроков устранения. А в расписание проверки неисправного гидранта или водоема вносится изменение – дата следующей проверки изменятся на ту дату, которая указана в письме как срок устранения неисправности. При наступлении данного срока производится повторная проверка. Если неисправность устранена, то в журнале ставится соответствующая отметка. В противном случае сообщается об этом начальнику части, и он в свою очередь направляет письмо в Государственный Пожарный Надзор (приложение 3) с информацией о том, какая организация не выполняет свои обязанности.
В систему будет вноситься номер письма, дата его отправки и организация, которой направлено письмо.
|
|
|
Функциональные требования
Система должна выполнять следующие функции:
1) Предоставлять инструменты по управлению данными системы:
1. Добавление/редактирование/удаление данных о водоеме или гидранте;
2. Формирование сроков проведения плановых проверок;
2) Система должна осуществлять поиск информации:
1. Поиск водоема или гидранта по адресу привязки;
3) Система должна выполнять формирование необходимых форм отчетов:
1. Отчет о водоемах и гидрантах подлежащих проверке в текущий день;
2. Письмо-запрос в обслуживающую организацию;
3. Письмо в Государственный пожарный надзор
Ограничения на данные:
1) Дата отправки письма о неисправности должна быть больше, чем дата обнаружения данной неисправности.
2) Нельзя исправлять данные о неисправности, если было отправлено письмо.
3) Дата следующей проверки должна быть больше, чем даты проверки и время, отведенное на ремонт.
4) Объем водоема и диаметр сети гидранта не должны быть меньше нуля.
5) Названия улиц и названия организаций не должны повторяться.
Физическая модель
Перед построением физической модели базы данных было проведено концептуальное и логическое проектирование в пятом семестре, в ходе выполнения курсового проекта по дисциплине «Управление данными».
При анализе предметной области, были выделены основные сущности, участвующие в процессе учета обслуживания лифтов, и на их основе была смоделирована концептуальная модель.
На основе концептуальной модели была построена логическая модель данных, которая является начальным прототипом будущей базы данных. Кроме того, были сгенерированы отношения.
Поскольку логическая модель не учитывает всех особенностей целевой СУБД, то следующим этапом проектирования является разработка физической модели базы данных, которая тоже в свою очередь подразделяется на несколько этапов. Первый – создание таблиц в целевой СУБД и поддержание согласованности данных с помощью ограничений. Такой метод поддержания целостности называется декларативным.
|
|
|
2.1. Фрагмент БД "Учет водоисточников"
Учет водоисточников включает в себя описание всех пожарных гидрантов и водоемов, закрепленных за учебной пожарной частью.
К описанию водоисточников относится следующее:
· Название
· Адрес привязки (улица и номер дома)
· Характеристика водоисточника
· Обслуживающая организация
· Журнал проверок (фрагмент БД "Учет неисправностей")
2.2. Фрагмент БД "Учет неисправностей"
Учет неисправностей заключается в ведении журнала проверок по каждому водоисточнику. Во время проверок выявляется неисправность, если таковая имеется, либо определяется, что водоисточник исправен. После проверки вносится запись в журнал проверок.
Учет неисправностей содержит следующую информацию:
· Дата проверки
· Неисправность
· Работоспособность
2.3. Фрагмент БД "Учет отправленных писем"
Учет отправленных писем заключается в ведении информации о номерах и датах отправки писем в обслуживающие организации и в ГосПожНадзор.
Второй этап – анализ бизнес-правил, налагаемых на БД, и применение императивных средств для их поддержания (в случае, если декларативных средств СУБД для этого недостаточно). В качестве императивных средств могут выступать триггеры. Такой вид целостности называется процедурным.
Полная модель БД представлена в приложении 2.
Используемая СУБД
В качестве основного средства для разработки физической модели базы данных на тему «Учет состояния пожарных гидрантов и водоемов» была выбрана СУБД Microsoft SQL Server 2008.
Аргументами в пользу этого программного обеспечения стали:
· Наличие опыта работы с этой СУБД в рамках практических занятий по дисциплине «СУБД»;
· Microsoft SQL Server 2008 является последней версией этой линейки продукта;
· Microsoft SQL Server 2008 проста в использовании и поддерживает все необходимые для создания проекта средства, такие как: хранимые процедуры и функции, представления, транзакции, триггеры и курсоры;
· Microsoft SQL Server 2008 предоставляет точный и гибкий контроль для обеспечения безопасности данных: разделение доступа к данным между разными пользователями через роли и схемы.
Обеспечение целостности БД
Обеспечение целостности базы данных осуществляется за счет декларативной и процедурной целостности.
Декларативная целостность неразрывно связана с физической структурой таблиц БД и осуществляется при помощи некоторых условий (ограничений), налагаемых на столбцы или таблицы. Этот вид поддержания согласованности данных гарантирует, что хранимая информация будет удовлетворять определенным в таблицах правилам. Кроме того, декларативные ограничения целостности обеспечивают высокое быстродействие, что, несомненно, является плюсом.
|
|
|
К декларативным ограничениям, используемым в данном курсовом проекте, относятся:
· ограничение PRIMARYKEY
· ограничение NOT NULL
· ограничение UNIQUE
· ограничение CHECK
Ограничение PRIMARYKEYсоздает первичные ключи, при помощи которых обеспечивается связь таблиц.
Первичный ключ не может иметь атрибутов с NULL признаком. Поэтому при создании первичного ключа ограничение NOTNULL на атрибуты, входящие в первичный ключ, назначается автоматически.
Ограничение UNIQUE объявляет столбец или группу столбцов уникальным. Это ограничение необходимо для контроля того, что все названия улиц и организаций будут разными.
Данное ограничение было наложено на атрибуты 2-х таблиц:
· для таблицы Street (Улица)
CONSTRAINT NameStreetUnique UNIQUE(NameStreet)
· для таблицы Organization(Организация)
CONSTRAINT NameOrganizationUnique UNIQUE (NameOrganization)
При заполнении информации о водоеме или гидранте, необходима проверка, чтобы были введены корректные данные. А именно, необходимо контролировать атрибуты Caliber (таблица Гидрант) и Size (таблица Водоем). Введенные данные не должны быть меньше или равны нулю. Для контроля данных этих атрибутов используется ограничение CHECK.
· для таблицы Hydrant (Гидрант)
CONSTRAINT HydrantCaliber CHECK (Caliber>0)
· длятаблицыPond(Водоем)
CONSTRAINT PondSize CHECK (Size>0)
Процедурную целостность необходимо использовать, когда нет возможности осуществлять контроль вводимых данных по средствам декларативной целостности.
Остальные бизнес-правила были реализованы при помощи триггеров. Так как в триггере можно сравнивать значения атрибутов из разных таблиц, что нельзя реализовать при помощи ограничения CHECK.
Ниже приведены триггеры, используемые в данном курсовом проекте:
• TrDefect – триггер, контролирующий, чтобы не было возможности изменять данные о неисправности, если уже об этой неисправности было направлено письмо в обслуживающую организацию.
• TrLetter – триггер, контролирующий, чтобы внесенная дата отправки письма была не раньше, чем дата проверки.
• TrLetter2 – триггер, контролирующий, чтобы дата следующей проверки была больше, чем дата проверки и время, отведенное на ремонт.
CREATE TRIGGER TrDefect
ON Defect FOR UPDATE
AS
IF EXISTS
(SELECT 'TRUE'
FROM inserted i LEFT JOIN TestDefect T
ON i.IDDefect=T.IDDefect
LEFT JOIN Inspection Ins
ON T.IDInspection=Ins.IDInspection
LEFT JOIN Letter L
ON Ins.IDInspection=L.IDInspection
WHERE (L.IDLetter IS NOT NULL))
BEGIN
RAISERROR ('Нельзя изменять данные о неисправности!Отправлено письмо!',1,1)
ROLLBACK TRAN
END
CREATE TRIGGER TrLetter
ON Letter
FOR INSERT,UPDATE
AS
IF EXISTS
(SELECT 'TRUE'
FROM Letter L LEFT JOIN Inspection I
ON L.IDInspection=I.IDInspection
WHERE (L.DataSend<I.DateInspection))
BEGIN
RAISERROR ('Дата отправки должна быть не раньше даты проверки!',1,1)
ROLLBACK TRAN
END
CREATE TRIGGER TrLetter2
ON Letter
FOR INSERT,UPDATE
AS
IF EXISTS
(SELECT 'TRUE'
FROM Letter L LEFT JOIN Inspection I
ON L.IDInspection=I.IDInspection
LEFT JOIN TestDefect T
ON I.IDInspection=T.IDInspection
LEFT JOIN Defect D
ON T.IDDefect=D.IDDefect
WHERE (L.DateNextInspection<DATEADD(dd,D.RepairTime,I.DateInspection)))
BEGIN
RAISERROR ('Дата следующей проерки должна быть больше, чем даты проверки и время, отведенное на ремонт!',1,1)
ROLLBACK TRAN
END
|
|
|
