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

Дипломный проект

Дисциплина «СУБД»

Тема: «Разработка базы данных для системы «Учесть обслуживание, технический осмотр и ремонт лифтов в домах города Сыктывкар компанией ООО «ОТИС-Лайн»»»



Содержание

Введение

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

1.1 Описание предметной области

1.2 Функциональные требования

2. Физическая модель

2.1 Используемая СУБД

3. Обеспечение целостности БД

4. Создание таблиц и ограничений

5. Хранимые процедуры

6. Выходные формы

7. Обеспечение безопасности

8. Оптимизация

9. Тестирование

Заключение

Библиографический список

Приложениея



Введение

Каждый работник аварийной службы ООО «ОТИС-Лайн» в соответствии с личными обязанностями ведет свой журнал, отчет или график работы и хранит его у себя, при необходимости предоставляя доступ к ним начальству.

Журналы диспетчера о вызовах на устранение неисправностей, учет лифтов остановленных на ремонт и др. хранятся непосредственно у диспетчера в диспетчерской.

График технического осмотра лифтов основной механик хранит у себя на участке.

Перечень лифтов, для которых осуществляется аварийное обслуживание, хранится у аварийного электромеханика

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

Конкретная информация о каждом лифте хранится в конкретном журнале, поэтому чтобы учесть все, необходимо использовать сразу несколько различных журналов и записывать необходимые данные в необходимый журнал.

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

При заполнении журналов, происходит повторение информации. Так, например, отчет о ремонте или техосмотре практически одинаков. Журнал заявок от жильцов так же, от части, учитывается и в нарядах на ремонт и техосмотр, т.к. фактически одно происшествие заполняется во всех журналах. Единая база, позволит избегать излишнего повторения информации.

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

· Затруднение цельного мобильного учета всех лифтов, закрепленных за данным зональным участком, в составе которого действует служба

· Затруднение цельного оперативного получения информации о лифтах

· Неудобство и длительность составления отчетов в журналах

· Отсутствие наглядности работы всех подразделений ООО «ОТИС-Лайн»

· Хранение информации на бумажных носителях затрудняет ее поиск и возможность использования несколькими пользователями

· Хранение на бумажных носителях увеличивает вероятность потери информации или ее порчи

Для решения данных проблем ставится задача автоматизации процесса «Учета обслуживания, технического осмотра и ремонта лифтов», что является целью данного дипломного проекта. Система организует все данные и историю о каждом лифте, упростит ведение их отчетности, а также ускорит формирование заказов запасных частей снабженцем.

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

Для достижения поставленной цели будет проделана работа следующего содержания:

В первой главе описывается бизнес-процесс «Учесть обслуживание, технический осмотр и ремонт лифтов в домах города Сыктывкар компанией ООО «ОТИС-Лайн»»». Доказывается актуальность данной работы, ставятся цели.

Во второй главе описывается переход от логической модели базы данных к физической.

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

В четвертой главе описываются основные методы организации обеспечения безопасности базы данных.

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

В шестой главе приводится тестирование базы данных.



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

1.1 Описание предметной области

Лифтовая аварийная служба ООО «ОТИС-Лайн» в соответствии с функциональными обязанностями работников состоит из:

1) электромеханик-дежурный (аварийный механик), вызволяющий людей при аварийной поломки лифта.

2) электромеханик-ремонтник (основной механик), устраняющих неисправности на лифтах и ЛДСС

3) диспетчер, принимающий сигнал с ЛДСС и отправляющих электромехаников на место поломки/ аварийной поломки лифта.

4) Мастер - ответственный за организацию аварийного обслуживания лифтов и ЛДСС

5) Снабженец запчастями, который производит заказ запасных частей по запросам мастеров. Каждый мастер в свою очередь организуют заказ с запросов электромехаников, за которых он отвечает.

Оперативное устранение неисправностей осуществляется подразделением ООО «ОТИС-Лайн» только на лифтах и ЛДСС, техническое обслуживание которых ведется ООО «ОТИС-Лайн»

Лифтовая аварийная служба в соответствии с функциональными обязанностями работников должна состоять из электромехаников-дежурных (аварийный механик) по пункту ЛАС, электромехаников-ремонтников (основной механик), устраняющих неисправности на лифтах и ЛДСС, а также диспетчеров, принимающих сигнал с ЛДСС и отправляющих на место поломки электромехаников.

Бригадой электромехаников, осуществляющих техническое обслуживание лифтов и ЛДСС (далее - линейная бригада), производится аварийное обслуживание только лифтов и ЛДСС закрепленных за прорабским (мастерским) участком, в составе которого действует бригада. В этом случае ответственность за организацию аварийного обслуживания возлагается приказом начальника подразделения объединения на прораба (мастера) этого участка. Диспетчер осуществляет учет и регулирование деятельности организации или его подразделения. Все отчеты и журналы передаются от одного диспетчера к другому по сменам и заполняются после каждой заявки и ее исполнении. При необходимости, все ведущиеся журналы диспетчер может предоставлять мастеру, старшему диспетчеру или же другому ответственному за организацию аварийного обслуживания лифтов.

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

В течение смены диспетчер обязан:

· Принимать заявки на устранение неисправностей в работе лифтов и ЛДСС с занесением их в «Журнал регистрации вызовов на устранение неисправностей в работе лифтов и ЛДСС», с последующим заполнением данного журнала по данным заполненных электромеханиками Нарядов на устранение неисправностей.

· Направлять ремонтные бригады на устранение неисправностей в работе лифтов и ЛДСС с вручением электромеханику-ремонтнику «Наряда на устранение неисправностей в работе лифтов и ЛДСС»

· Вести учет лифтов и ЛДСС, находящихся на ремонтах с оформлением в «Журнале учета лифтов и ЛДСС, остановленных на ремонты»

Электромеханик-дежурный в своей работе непосредственно подчинен ответственному за организацию аварийного обслуживания. В период работы он должен находится на пункте ДПАС и выезжать на аварийную поломку лифта по указанию диспетчера с получением от вышеупомянутого Наряда на устранение неисправности. В Наряд аварийный электромеханик вписывает данные об аварии и (после возможном устранении неисправности или же просто после эвакуации пассажиров) отдает его диспетчеру.

Отчет о работе аварийный электромеханик ведет в «Перечне адресов и регистрационных номеров лифтов и ЛДСС, аварийное обслуживание которых осуществляется данной ЛАС (ДПАС)», заполняет после каждого выезда на аварийную поломку лифта и хранит у себя на пункте ДПАС. При необходимости аварийный электромеханик может предоставить свою отчетность мастеру или диспетчеру. Электромеханик-ремонтник ЛАС подчиняется ответственному за организацию аварийного обслуживания лифтов и ЛДСС, а в оперативном отношении электромеханику-дежурному по пункту ЛАС. В период работы электромеханик-ремонтник должен находиться на пункте ЛАС (ДПАС) или опорном пункте и выезжать (выходить) на устранение неисправностей по указанию диспетчера, получая Наряд на устранение неисправностей, а в случае, когда заявка передается по телефону, она оформляется электромехаником-ремонтником соответствующей записью в наряде. Наряд отдается диспетчеру для заполнения «Журнала регистрации вызовов на устранение неисправностей в работе лифтов и ЛДСС».

Основной работой электромеханика-ремонтника является технический осмотр лифтов закрепленных за ним. Сведения об осмотре и возможном ремонте лифтов основной электромеханик фиксирует в «Журналах технических осмотров», закрепленных каждый за своим лифтом и находящихся в машинном отделении лифта. Осмотр каждого лифта и внесение записей об осмотре производится раз в месяц. При необходимости основной электромеханик может предоставить свою отчетность мастеру или диспетчеру. В настоящее время информация о каждом лифте хранится в отдельных журналах, которые располагаются в машинных отделениях лифтов, информация о регистрации вызовов на устранение неисправностей в работе лифтов находится в журнале у диспетчера, а журнал технических осмотров и ремонтов лифтов находится у электромеханика-ремонтника. Такая организация хранения информации приводит к отсутствию возможности оперативного просмотра полной истории лифтов и ЛДСС, а также работы подразделений службы

1.2 Функциональные требования

Система должна выполнять следующие функции:

Предоставлять инструменты по управлению данными системы:

1) Добавление/правка данных о вызовах на устранение неисправностей в работе лифтов и ЛДСС;

2) Добавление/правка данных о технических осмотрах лифтов и ЛДСС;

3) Добавление/правка данных об аварийных обслуживаниях лифтов и ЛДСС;

4) Добавление/правка данных о личных данных электромехаников

Система должна выполнять формирование необходимых форм отчетов и статистик:

1) Личная карточка каждого лифта со всей историей.

2) Отчет о работе аварийных электромехаников.

3) Отчет о работе основных электромехаников

4) Полный отчет работы организации

Ограничения на данные:

1) В системе нельзя понижать разряд электромеханика на меньший

2) В системе не должно быть повторяющихся данных в справочниках

3) Необходимы ограничения на проверку даты рождения электромехаников, чтобы они были не моложе 20 лет

4) Необходимо ограничить варианты видов заявки на: техосмотр, застревание, поломка.



2. Физическая модель

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

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

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

Поскольку логическая модель не учитывает всех особенностей целевой СУБД, то следующим этапом проектирования является разработка физической модели базы данных, которая тоже в свою очередь подразделяется на несколько этапов. Первый - создание таблиц в целевой СУБД и поддержание согласованности данных с помощью ограничений. Такой метод поддержания целостности называется декларативным. Второй этап - анализ бизнес-правил, налагаемых на БД, и применение императивных средств для их поддержания (в случае, если декларативных средств СУБД для этого недостаточно). В качестве императивных средств могут выступать триггеры. Такой вид целостности называется процедурным.

2.1 Используемая СУБД

В качестве основного средства для разработки физической модели базы данных на тему «Учесть обслуживание, технический осмотр и ремонт лифтов в домах города Сыктывкар компанией ООО «ОТИС-Лайн»»» была выбрана СУБД Microsoft SQL Server 2008.

Аргументами в пользу этого программного обеспечения стали:

· Наличие опыта работы с этой СУБД в рамках практических занятий по дисциплине «СУБД»;

· Microsoft SQL Server 2008 проста в использовании и поддерживает все необходимые для создания проекта средства, такие как: хранимые процедуры и функции, представления, транзакции, триггеры и курсоры;

· Microsoft SQL Server 2008 предоставляет точный и гибкий контроль для обеспечения безопасности данных: разделение доступа к данным между разными пользователями через роли и схемы.



3. Обеспечение целостности БД

Обеспечение целостности базы данных осуществляется за счет декларативной и процедурной целостности.

Декларативная целостность неразрывно связана с физической структурой таблиц БД и осуществляется при помощи некоторых условий (ограничений), налагаемых на столбцы или таблицы. Этот вид поддержания согласованности данных гарантирует, что хранимая информация будет удовлетворять определенным в таблицах правилам. Кроме того, декларативные ограничения целостности обеспечивают высокое быстродействие, что, несомненно, является плюсом.

Ограничение PRIMARY KEY было наложено на все таблицы, т.к. необходим первичный ключ для связи таблиц, также происходит обеспечение отсутствия NULL-значений этих первичных ключей.

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

Данное ограничение было наложено на атрибуты таблиц: Street, Electrician, TypeOfApplication, Defect.

ALTER TABLE Street

WITH CHECK

ADD CONSTRAINT IDStreetPrimary PRIMARY KEY (IDStreet),

CONSTRAINT NameStreetUnique UNIQUE (NameStreet);

ALTER TABLE TypeOfApplication

WITH CHECK

ADD CONSTRAINT IDTypeOfApplicationPrimary PRIMARY KEY (IDTypeOfApplication),

CONSTRAINT TypeOfAppUnique UNIQUE (TypeOfApp);

В системе необходимы ограничения на проверку даты рождения электромехаников, чтобы они были не моложе 20 лет. Данное бизнес-правило было реализовано за счет ограничения CHECK, так как его можно реализовать при помощи него не используя например триггер либо транзакцию, т.к. check проще и выполняется в базе быстрее процедурных методов обеспечения целостности БД.

ALTER TABLE Electrician

WITH CHECK

ADD CONSTRAINT ElClassCheck CHECK (Class BETWEEN 1 AND 4),

CONSTRAINT ElectricianBirthdayCheck CHECK (DATEDIFF(year,Birthday,getDate())>20)

EXEC PElectricianINSERT

Пупкин Петр Сергеевич','4','Аварийный электромеханик','03.11.2011'

Если электромеханик не выполнил задание, то он не отдает наряд диспетчеру и в таком случае «время наряда» (время ремонта, техосмотра или вызволения людей) в наряде остается пустым. Остальные же атрибуты должны быть заполнены. Для данного бизнес правила использовалось ограничение NOT NULL, которое было наложено на все атрибуты кроме OrdersTime, т.к. он может быть пустым в случае не закрытия наряда.

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

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

В системе нельзя понижать разряд электромеханика на меньший. Это бизнес-правило было реализовано при помощи триггера, который запрещает данное понижение разряда. С одной стороны, применение триггеров очень удобно для пользователей базы данных, а с другой, их использование часто связано с дополнительными затратами ресурсов на операции ввода/вывода. Тем не менее, данное бизнес-правило было реализовано при помощи триггера, т.к. его невозможно реализовать при помощи более простого ограничения CHECK

Триггер - это откомпилированная SQL-процедура, исполнение которой обусловлено наступлением определенных событий внутри реляционной базы данных.

ALTER TRIGGER LowClass

ON Electrician FOR UPDATE

AS

IF EXISTS

(SELECT 'TRUE'

FROM insertedI

LEFT JOIN deleted D

ON D.IDElectrician = I.IDElectrician

WHERE I.Class < D.Class)

BEGIN

RAISERROR('Нельзя понижать разряд электромеханика!!!',16,1)

ROLLBACK TRAN

END



4. Создание таблиц и ограничений

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

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

Имена ограничений:

Договоримся, назначая ограничение PRIMARY KEY использовать шаблон: <имя таблицы><тип ограничения>, поскольку ограничение PRIMARY KEY может быть в таблице только одно. Для ограничений FOREIGN KEY будем использовать шаблон - <имя ссылающейся таблицы><имя ссылочной таблицы><тип ограничения>. Для ограничения CHECK<имя таблицы><имя столбца><тип ограничения>, а для ограничения UNIQUE <имя таблицы><имя одного из столбцов><тип ограничения>.

Таблицы базы данных именуются в соответствии с их содержимым и на английском языке, т.к. он является интернациональным. Например таблица хранящая в себе данные о лифтах (личный номер, дом, подъезд и .т.п.) называется Elevator. Именование процедур и функций осуществляется по следующему шаблону: добавление <имя таблицы>INCERT, удаление-<имя таблицы>Delete, обновление -<имя таблицы>Update, отчетные формы - print<имя объекта или действия>

Таблица Улица(Street)

Атрибут	Наименование атрибута	Тип атрибута	Ограничения
ID Улицы	IDStreet	INT	PRIMARY KEY
Название улицы	NameStreet	VARCHAR (40)	UNIQUE NOT NULL



В таблице «Улица»был выделен уникальный ключ «ID Улицы».

Таблица «Улица» является справочником, поэтому на название улиц было наложено ограничение уникальности, чтобы исключить дублирование записей в таблице.

CREATETABLE Street

IDStreet INTIDENTITY NOT NULL,

NameStreet VARCHAR(40)NOT NULL

ALTERTABLE Street

WITHCHECK

ADD CONSTRAINT IDStreetPrimary PRIMARYKEY (IDStreet),

CONSTRAINT NameStreetUnique UNIQUE (NameStreet)

Таблица Вид заявки(TypeOfApplication)

Атрибут	Наименование атрибута	Тип атрибута	Ограничения
ID вида заявки	IDTypeOfApplication	INT	PRIMARY KEY
Вид заявки	TypeOfApp	VARCHAR (80)	UNIQUE NOT NULL

Таблица «Вид заявки» хранит в себе вид заявки, поэтому данный атрибут не может быть пустым.

CREATE TABLE TypeOfApplication

(

IDTypeOfApplication INT IDENTITY NOT NULL,

TypeOfApp VARCHAR(80)NOT NULL

);

ALTERTABLE TypeOfApplication

WITH CHECK

ADD CONSTRAINT IDTypeOfApplicationPrimary PRIMARY KEY (IDTypeOfApplication),

CONSTRAINT TypeOfAppUnique UNIQUE(TypeOfApp)



Таблица Электромеханик(Electrician)

Атрибут	Наименование атрибута	Тип атрибута	Ограничения
ID Электромеханика	IDElectrician	INT	PRIMARY KEY
ФИО	FIO	VARCHAR (80)	UNIQUE NOT NULL
Разряд	Class	INT	NOT NULL
Специализация	Speciality	VARCHAR (40)	NOT NULL
Дата рождения	Birthday	DATETIME	NOT NULL

Таблица «Электромеханик» является справочником исодержитвсебеличныеданныеэлектромехаников. Чтобы избежать случайного повторного ввода данных об одном и том же работнике, атрибут ФИО установлен уникальным.

CREATE TABLE Electrician

(

IDElectrician INT IDENTITY NOT NULL,

FIO VARCHAR(80)NOT NULL,

Class INT NOT NULL,

Speciality VARCHAR(40)NOT NULL,

Birthday DATETIME NOT NULL

);

ALTER TABLE Electrician

WITH CHECK

ADD CONSTRAINT IDElectricianPrimary PRIMARY KEY (IDElectrician),

CONSTRAINT FIOUnique UNIQUE(FIO)

;

На атрибут «Разряд» было установлено ограничение от 1 до 4, чтобы избежать случайных грубых ошибок во введении разряда электромеханика.

Также было поставлено ограниечени на дату рождения: электромеханик не должен быть моложе 20 лет.

ALTERTABLE Electrician

WITH CHECK

ADD CONSTRAINT ElClassCheck CHECK (ClassBETWEEN 1 AND 4),

CONSTRAINT ElectricianBirthdayCheck CHECK (DATEDIFF(year,Birthday,getDate())>20)

В таблице «Неисправность» хранится краткое описание неисправности лифта и возможно причина неисправности. Т.к. каждый случай поломки уникален, на данный атрибут наложено ограничение UNIQUE.

CREATE TABLE Defect

(

IDDefect INT IDENTITY NOT NULL,

TypeOfDefect VARCHAR(80)NOT NULL

);

ALTER TABLE Defect

WITH CHECK

ADD CONSTRAINT IDDefectPrimary PRIMARY KEY (IDDefect),

CONSTRAINT TypeOfDesectUnique UNIQUE (TypeOfDefect)

Таблица Лифт(Elevator)

Атрибут	Наименование атрибута	Тип атрибута	Ограничения
ID Лифта	IDElevator	INT	PRIMARY KEY
Личный номер	PersonalNumber	VARCHAR (10)	NOT NULL
Дом	Building	INT	NOT NULL
Подъезд	Door	INT	NOT NULL
ID Улицы	IDStreet	INT	NOT NULL FOREIGN KEY

В таблице «Лифт» помимо первичного ключа (ID Лифта) был выделен потенциальный ключ: дом, подъезд и личный номер. Так как эти атрибуты являются уникальными, то по ним можно будет однозначно идентифицировать записи в таблице.

Так как данная таблица является ссылающейся, для внешних ключей была установлена опция ON UPDATE CASCADE, которая будет обеспечивать модификацию строк, как в родительской, так и в дочерней таблице. Кроме того, была установлена опция ON DELETE CASCADE, которая обеспечит удаление строк в дочерней таблице, при удалении строк из родительской. Далее эта опция применяется практически во всех таблицах.

CREATE TABLE Elevator

(

IDElevator INT IDENTITY NOT NULL,

PersonalNumber VARCHAR(10)NOT NULL,

Building VARCHAR(10)NOT NULL,

Door INT NOT NULL,--подъезд

IDStreet INT NOT NULL,

);

ALTER TABLE Elevator

WITH CHECK

ADD CONSTRAINT DElevator Primary PRIMARYKEY (IDElevator),

CONSTRAINT BuildingDoorPersonalNumberUniq UNIQUE (Building,Door,PersonalNumber),

CONSTRAINT ElevatorStreetForeign FOREIGNKEY(IDStreet)

REFERENCES Street ON UPDATE CASCADE ON DELETE CASCADE

Атрибут	Наименование атрибута	Тип атрибута	Ограничения
ID Заявки	IDRequest	INT	PRIMARY KEY
Описание поломки	BreakDescription	VARCHAR (256)	NOT NULL
Дата и время заявки	BreakTime	DATETIME	NOT NULL
Степень поломки	BreakLevel	BIT	NOT NULL
ID вида заявки	IDTypeOfApplication	INT	NOT NULL FOREIGN KEY
ID Лифта	IDElevator	INT	NOT NULL FOREIGN KEY



В таблица «Заявка» есть атрибут «Описание поломки» хранящий краткое описание поломки лифта со слов жильцов (звонящего диспетчеру). Также есть атрибуд «степень поломки», который показывает срочность и для кого эта заявка.

CREATE TABLE Request

(

IDRequest INT IDENTITY NOT NULL,

BreakDescription VARCHAR(256)NOT NULL,

BreakTime DATETIME NOT NULL,

BreakLevel BIT NOT NULL DEFAULT 0,

IDElevator INT NOT NULL,

IDTypeOfApplication INT NOT NULL,

);

ALTER TABLE Request

WITH CHECK

ADD CONSTRAINT IDRequestPrimary PRIMARY KEY (IDRequest),

CONSTRAINT RequestEkevatorForeign FOREIG NKEY(IDElevator)

REFERENCES Elevator ON UPDATE CASCADE ON DELETE CASCADE,

CONSTRAINT RequestTypeOfApplicationForeign FOREIGN KEY(IDTypeOfApplication)

REFERENCES TypeOfApplication ON UPDATE CASCADE ON DELETE CASCADE

Втаблице«Наряд» есть атрибут «Дата и время наряда», на котором отсутствуют ограничения. Если данный атрибут пустой, значит поломка еще не устранена и наряд не закрыт.

CREATETABLE Orders

(

IDOrders INT IDENTITY NOT NULL,

OrdersTime DATETIME,

IDElectrician INT NOT NULL,

IDRequest INT NOT NULL,

);

ALTER TABLE Orders

WITH CHECK

ADD CONSTRAINT IDOrdersPrimary PRIMARYKEY (IDOrders),

CONSTRAINT OrdersElectricianForeign FOREIGNKEY(IDElectrician)

REFERENCES Electrician ON UPDATE CASCADE ON DELETE CASCADE,

CONSTRAINT OrdersRequestForeign FOREIGN KEY(IDRequest)

REFERENCES Request ON UPDATE CASCADE ON DELETE CASCADE

;

Таблица «Неисправности» является слабой сущнюстью между «Неисправность» и «Наряд» в отношении многое ко многим и хранит в себе внешние ключи.

CREATE TABLE Defects

IDOrders INT,

IDDefect INT,

);

ALTERTABLE Defects

WITH CHECK

ADD CONSTRAINT DefectsOrdersForeign FOREIGN KEY(IDOrders)

REFERENCES Orders ON UPDATE CASCADE ON DELETE CASCADE,

CONSTRAINT DefectsDefectForeign FOREIGN KEY(IDDefect)

REFERENCES Defect ON UPDATE CASCADE ON DELETE CASCADE



5. Хранимые процедуры

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

Процедуры добавления были реализованы для всех таблиц БД, а процедуры удаления и обновления только для некоторых.

Были созданы процедуры добавления/удаления/обносления данных для выполнения следующих бизнес-правил:

1. Добавление/правка данных о вызовах на устранение неисправностей в работе лифтов и ЛДСС;

2. Добавление/правка данных о технических осмотрах лифтов и ЛДСС;

3. Добавление/правка данных об аварийных обслуживаниях лифтов и ЛДСС;

4. Добавление/правка данных о личных данных электромехаников

Процедуры добавления данных:

- PStreetINSERT

- PTypeOfApplicationINSERT

- PElectricianINSERT

- PDefectINSERT

- PElevatorsINSERT

- PRequestINSERT

- POrdersINSERT

- PDefectsINSERT

Процедуры обновления данных:

- UpdateStreet

- UpdateElectrician

- UpdateDefect

- UpdateElevators

- UpdateRequest

- UpdateOrders

- UpdateDefects

Процедуры удаления данных:

- DeleteStreet

- DeleteTypeOfApplication

- DeleteElectrician

- DeleteDefect

- DeleteElevators

- DeleteRequest

- DeleteOrders

- DeleteDefects

В качестве примера процедуры добавления данных ниже приведена хранимая процедура, которая будет вводить данные в таблицу Street(см. Приложение 7.)

CREATEPROC PStreetINSERT

@NameStreet VARCHAR(40)

AS

INSERT INTO Street

(NameStreet)

VALUES

(@NameStreet)

Пример процедуры удаления данных из таблицы TypeOfApplication(по умолчанию тип заявки может быть только «поломка», «застревание» или «техосмотр»)

CREATEPROCDeleteTypeOfApplication

(@IDTypeOfApplicationINT)

AS

DELETEFROMTypeOfApplication

WHEREIDTypeOfApplication=@IDTypeOfApplication

Пример процедуры обновления (замены) данных в таблицеStreet, на случай если Улицу переименуют (см. Приложение 7.)

CREATEPROCUpdateStreet

(@IDStreetINT,

@NameStreetVARCHAR(40))

AS

UPDATEStreet

SETNameStreet=@NameStreet

WHEREIDStreet=@IDStreet



6. Выходные формы

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

Для получения конечных отчетных форм были использованы:

· Представления;

· Отчетные хранимые процедуры и функции;

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

· Личная карточка каждого лифта со всей историей.

· Отчет о работе выбранного подразделения начиная с определенной даты

· Полный отчет всех подразделений

6.1 Представления

Механизм представления - мощное средство СУБД, позволяющее скрыть реальную структуру БД от некоторых пользователей за счет определения представлений.

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

Представления:

Printall-Отчет работы всех подразделений сортировка по улицам

print1El-вывод всех поломанных лифтов с высокой степенью

Пример представления:

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

CREATEVIEWprintall

AS

SELECTNameStreet [Улица],

PersonalNumber [№ лифта],

FIO [ФИО],

TypeOfApp [Вид заявки],

TypeOfDefect [Вид неисправности],

BreakDescription [Описание поломки],

BreakLevel [степень],

BreakTime [время поломки]

FROMStreetSTINNERJOINElevatorEL

ONST.IDStreet=EL.IDStreet

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

6.2 Отчетные хранимые процедуры и функции

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

Процедуры и функции:

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)



7. Обеспечение безопасности

В ходе изучения предметной области в рамках проекта по дисциплине «Управление данными» были определены основные пользователи для работы с базой данных:

· Диспетчер

· Администратор

Диспетчер вносит/редактирует данные в нарядах, заявках, а также создает все необходимые отчеты

	Диспетчеры	Администраторы
Заполнение/редактирование справочников	-	+
Заполнение/редактирование заявок	+	-
Заполнение/редактирование нарядов	+	-
Создание отчетов	+	-
Просмотр данных	+	+

Так как диспетчеров в организации несколько, а также возможно администраторов тоже будет 2 (посменно или же на пол ставки), было создано две роли:

· Диспетчеры

· Администраторы

--роль диспетчеры

CREATEROLEDispatchers

--рольАдминистраторы

CREATEROLEAdministrators

	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 - права на удаление.

· Диспетчер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

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



8. Оптимизация

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

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

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

Для того, чтобы поиск по данным и запросы осуществлялись быстрее были введены такие объекты базы данных, как индексы.

Индекс - особый объект БД, создаваемый с целью увеличения производительности поиска данных. По умолчанию в СУБД Microsoft SQL Server создаются кластеризованные индексы. Помимо кластеризованных, в данном проекте были использованы некластеризованные индексы, которые накладывались на внешние ключи, и индексы с ограничением на уникальность.

--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)



9. Тестирование

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

Процедура тестирования заключалась в том, что с помощью скрипта создавалась новая база данных. Далее создавались все декларативные ограничения, накладываемые на эти таблицы. После создавались хранимые процедуры вставки, обновления и удаления; хранимые процедуры и функции, необходимые для формирования отчетом; триггеры; индексы; матрица доступа. Далее запускались тестовые данные для таблиц, затем вызывались функции формирования отчетов. (см. приложение 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

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 для профессионалов.: Пер. с англ. - М.: ООО «И.Д.Вильямс», 2012. - 1072 с.: ил. - Парал.тит.англ.;

2. К. Дж. Дейт. Введение в системы баз данных.: Пер. с англ. М.: Изд. Вильямс, 2013. - 1328 с.: ил. - Парал. тит. англ.;

3. Коннолли Томас, Бегг Каролин. Базы данных: проектирование, реализация и сопровождение. Теория и практика, 3-е изд.: Пер. с англ. - М.: Издательский дом «Вильямс», 2010. - 1440 с.: ил. - Парал. тит. англ.;

4. Николаева Н.А. Язык структурированных запросов. Лабораторные работы: учебное пособие / Н.А. Николаева, Т.Ю. Калинина. - Ухта: УГТУ, 2010. - 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

Создание ограничений на таблицы

--Создание ограничений на таблицы

ALTERTABLEStreet

WITH CHECK

ADD CONSTRAINT IDStreetPrimary PRIMARY KEY (IDStreet),

CONSTRAINT NameStreetUnique UNIQUE (NameStreet)

;

ALTER TABLE TypeOfApplication

WITH CHECK

ADD CONSTRAINT IDTypeOfApplicationPrimary PRIMARY KEY (IDTypeOfApplication),

CONSTRAINT TypeOfAppUnique UNIQUE (TypeOfApp)

;

ALTER TABLE Electrician

WITH CHECK

ADD CONSTRAINT IDElectricianPrimary PRIMARY KEY (IDElectrician),

CONSTRAINT FIOUnique UNIQUE (FIO)

;

--check

ALTER TABLE Electrician

WITH CHECK

ADD CONSTRAINT ElClassCheck CHECK (Class BETWEEN 1 AND 4),

CONSTRAINT ElectricianBirthdayCheck CHECK (DATEDIFF(year,Birthday,getDate())>20)

;

ALTER TABLE Defect

WITH CHECK

ADD CONSTRAINT IDDefectPrimary PRIMARY KEY (IDDefect),

CONSTRAINT TypeOfDesectUnique UNIQUE (TypeOfDefect)

;

ALTER TABLE Elevators

WITH CHECK

ADD CONSTRAINT DElevatorPrimary PRIMARY KEY (IDElevator),

CONSTRAINT BuildingDoorPersonalNumberUniq UNIQUE (Building,Door,PersonalNumber),

CONSTRAINT ElevatorStreetForeign FOREIGN KEY(IDStreet)

REFERENCES Street ON UPDATE CASCADE ON DELETE CASCADE

;

ALTER TABLE Request

WITH CHECK

ADD CONSTRAINT IDRequestPrimary PRIMARY KEY (IDRequest),

CONSTRAINT RequestEkevatorForeign FOREIGN KEY(IDElevator)

REFERENCES Elevators ON UPDATE CASCADE ON DELETE CASCADE,

CONSTRAINT RequestTypeOfApplicationForeign FOREIGN KEY(IDTypeOfApplication)

REFERENCES TypeOfApplication ON UPDATE CASCADE ON DELETE CASCADE

;

ALTER TABLE Orders

WITH CHECK

ADD CONSTRAINT IDOrdersPrimary PRIMARY KEY (IDOrders),

CONSTRAINT OrdersElectricianForeign FOREIGN KEY(IDElectrician)

REFERENCES Electrician ON UPDATE CASCADE ON DELETE CASCADE,

CONSTRAINT OrdersRequestForeign FOREIGN KEY(IDRequest)

REFERENCES Request ON UPDATE CASCADE ON DELETE CASCADE

;

ALTER TABLE Defects

WITH CHECK

ADD CONSTRAINT DefectsOrdersForeign FOREIGN KEY(IDOrders)

REFERENCES Orders ON UPDATE CASCADE ON DELETE CASCADE,

CONSTRAINT DefectsDefectForeign FOREIGN KEY(IDDefect)

REFERENCES Defect ON UPDATE CASCADE ON DELETE CASCADE

;

Приложение 6

триггер

--триггер, запрещающий исправлять разряд электромеханика в отношении Electrician на менее низкую

CREATE TRIGGER LowClass

ON Electrician FOR UPDATE

AS

IF EXISTS

(SELECT 'TRUE'

FROM inserted I

LEFT JOIN deleted D

ON D.IDElectrician=I.IDElectrician

WHERE I.Class<D.Class)

BEGIN

RAISERROR ('Нельзя понижать разряд электромеханика!!!',16,1)

ROLLBACK TRAN

END

--проверка

UPDATE Electrician

SETClass = 1

WHEREFIO='Пупкин Василий Петрович'

Приложение 7

Хранимые процедуры

--------------------------------------------------------------------------------------------

--хранимые процедуры

************************************************Street*************************

--процедура, которая будет вводить данные в таблицу Street

GO

CREATE PROC PStreetINSERT

@NameStreetVARCHAR(40)

AS

INSERT INTO Street

(NameStreet)

VALUES

(@NameStreet)

EXEC PStreetINSERT

'Юбилейная'

EXEC PStreetINSERT

'Ленина'

EXECPStreetINSERT

'Коммунистическая'

--процедура, которая будет обновлять данные в таблице Street

GO

ALTER PROC UpdateStreet

(@IDStreet INT,

@NameStreetVARCHAR(40)

)

AS

UPDATE Street

SET NameStreet = @NameStreet

WHEREIDStreet=@IDStreet

EXECUpdateStreet

'Космотнавтов','Коммунистическая'

--процедура, которая будет удалять данные из таблицы Street

GO

ALTER PROC DeleteStreet

(@IDStreet INT)

AS

DELETE FROM Street

WHERE IDStreet=@IDStreet

--*****************************************************************************

************************************************TypeOfApplication**************

--процедура, которая будет вводить данные в таблицу TypeOfApplication

GO

CREATE PROC PTypeOfApplicationINSERT

@TypeOfAppVARCHAR(80)

AS

INSERT INTO TypeOfApplication

(TypeOfApp)

VALUES

(@TypeOfApp)

EXEC PTypeOfApplicationINSERT

'Поломка'

EXEC PTypeOfApplicationINSERT

'Застревание'

EXECPTypeOfApplicationINSERT

'Техосмотр'

--процедура, которая будет удалять данные из таблицы TypeOfApplication

GO

ALTER PROC DeleteTypeOfApplication

(@IDTypeOfApplication INT)

AS

DELETE FROM TypeOfApplication

WHERE IDTypeOfApplication=@IDTypeOfApplication

*****************************************************************************

************************************************Electrician*********************

-- процедура ввода данных в Electrician

GO

CREATE PROC PElectricianINSERT

@FIO VARCHAR(80),

@Class INT ,

@SpecialityVARCHAR(40),

@Birthday DATETIME

AS

INSERT INTO Electrician

(FIO,

Class,

Speciality,

Birthday)

VALUES

(@FIO,

@Class,

@Speciality,

@Birthday)

EXEC PElectricianINSERT

'Пупкин Василий Петрович','2','Аварийный электромеханик','03.11.1970'

EXEC PElectricianINSERT

'Алешин Генадий Иванович','3','Основной электромеханик','03.11.1985'

--процедура, которая будет обновлять данные в таблице Electrician

GO

ALTER PROC UpdateElectrician

(@IDElectrician INT,

@FIO VARCHAR(80),

@Class INT,

@SpecialityVARCHAR(40),

@Birthday DATETIME)

AS

UPDATE Electrician

SET FIO = @FIO,

Class = @Class,

Speciality=@Speciality,

Birthday=@Birthday

WHEREIDElectrician=@IDElectrician

--процедура, которая будет удалять данные из таблицы Electrician

GO

ALTER PROC DeleteElectrician

(@IDElectrisian INT)

AS

DELETE FROM Electrician

WHERE IDElectrician=@IDElectrisian

--процедура, которая будет вводить данные в таблицу Defect

GO

CREATE PROC PDefectINSERT

@TypeOfDefectVARCHAR(80)

AS

INSERT INTO Defect

(TypeOfDefect)

VALUES

(@TypeOfDefect)

EXEC PDefectINSERT

'Перегорелалампочка'

EXECPDefectINSERT

'Не работают двери'

--процедура, которая будет обновлять данные в таблице Defect

GO

ALTER PROC UpdateDefect

(@IDdefect INT,

@TypeOfDefectVARCHAR(80))

AS

UPDATE Defect

SET TypeOfDefect = @TypeOfDefect

WHEREIDDefect=@IDdefect

--процедура, которая будет удалять данные из таблицы Defect

GO

CREATE PROC DeleteDefect

(@IDDefect INT)

AS

DELETE FROM Defect

WHERE IDDefect = @IDDefect

--процедура, которая будет вводить данные в таблицу Elevator

GO

CREATE PROC PElevatorsINSERT

@PersonalNumberVARCHAR(10),

@Building VARCHAR(10),

@Door INT,

@IDStreet INT

AS

INSERT INTO Elevators

(PersonalNumber,

Building,

Door,

IDStreet)

VALUES

(@PersonalNumber,

@Building,

@Door,

@IDStreet)

EXEC PElevatorsINSERT

'123','121/2','2',1

EXEC PElevatorsINSERT

'21','65','1',2

--процедура, которая будет обновлять данные в таблице Elevstors

GO

ALTER PROC UpdateElevators

(@IDElevator INT,

@PersonalNumberVARCHAR(10),

@Building VARCHAR (10),

@Door INT,

@IDStreet INT)

AS

UPDATE Elevators

SET PersonalNumber = @PersonalNumber,

Building = @Building,

Door = @Door,

IDStreet = @IDStreet

WHEREIDElevator=@IDElevator

--процедура, которая будет удалять данные из таблицы Elevators

GO

ALTER PROC DeleteElevators

(@IDElevator INT)

AS

DELETE FROM Elevators

WHERE IDElevator=@IDElevator

*****************************************************************************

************************************************Request***********************

--процедура, которая будет вводить данные в таблицу Request

GO

CREATE PROC PRequestINSERT

@BreakDescriptionVARCHAR(256),

@BreakTime DATETIME,

@BreakLevel BIT,

@IDElevator INT,

@IDTypeOfApplication INT

AS

INSERT INTO Request

(BreakDescription,

BreakTime,

BreakLevel,

IDElevator,

IDTypeOfApplication)

VALUES

(@BreakDescription,

@BreakTime,

@BreakLevel,

@IDElevator,

@IDTypeOfApplication)

EXECPRequestINSERT

'не работает лифт','01.03.2012 11:10:00:00',0,1,1

EXECPRequestINSERT

'застрялилюди','04.11.2011 15:32:00:00',1,2,2

--процедура, которая будет обновлять данные в таблице Request

GO

ALTER PROC UpdateRequest

(@IDRequest INT,

@BreakDescriptionVARCHAR(256),

@BreakTime DATETIME,

@BreakLevel BIT,

@IDElevator INT,

@IDTypeOfApplication INT)

AS

UPDATE Request

SET BreakDescription = @BreakDescription,

BreakTime = @BreakTime,

BreakLevel = @BreakLevel,

IDElevator = @IDElevator,

IDTypeOfApplication = @IDTypeOfApplication

WHEREIDRequest = @IDRequest

--процедура, которая будет удалять данные из таблицы Request

GO

CREATE PROC DeleteRequest

(@IDRequest INT)

AS

DELETE FROM Request

WHERE IDRequest = @IDRequest

*****************************************************************************

Orders************************

--процедура, которая будет вводить данные в таблицу Orders

GO

CREATE PROC POrdersINSERT

(@OrdersTime DATETIME,

@IDElectrician INT,

@IDRequest INT)

AS

INSERT INTO Orders

(OrdersTime,

IDElectrician,

IDRequest)

VALUES

(@OrdersTime,

@IDElectrician,

@IDRequest)

EXEC POrdersINSERT

'01.03.2012 15:30:00:00',1,4

EXECPOrdersINSERT

'02.02.2012 09:15:00:00',2,5

--процедура, которая будет обновлять данные в таблице Orders

GO

ALTER PROC UpdateOrders

(@OrdersTime DATETIME,

@IDOrders INT,

@IDElectrician INT,

@IDRequest INT)

AS

UPDATE Orders

SET OrdersTime = @OrdersTime,

IDElectrician = @IDElectrician,

IDRequest = @IDRequest

WHERE IDOrders = @IDOrders

--процедура, которая будет удалять данные из таблицы Orders

GO

CREATE PROC DeleteOrders

(@IDOrders INT)

AS

DELETE FROM Orders

WHERE IDOrders = @IDOrders

************************************************Defects***********************

--процедура, которая будет вводить данные в таблицу Defects

GO

CREATE PROC PDefectsINSERT

(@IDOrders INT,

@IDDefect INT

)

AS

INSERT INTO Defects

(IDOrders,

IDDefect)

VALUES

(@IDOrders,

@IDDefect)

EXEC PDefectsINSERT

3,1

EXEC PDefectsINSERT

4,2

--процедура, котораябудетобновлятьданные в таблице Defects

GO

CREATE PROC UpdateDefects

(@IDDefect INT,

@IDOrders INT)

AS

UPDATE Defects

SET IDDefect = @IDDefect

WHEREIDOrders = @IDOrders

--процедура, которая будет удалять данные из таблицы Defects

GO

CREATE PROC DeleteDefects

(@IDOrders INT)

AS

DELETE FROM Defects

WHEREIDOrders = @IDOrders

****************************************************************************

Приложение 8

Отчетные формы

-------------------------Отчетные формы

--Отчет работы всех подразделений сортировка по улицам

CREATE VIEW printall

AS

SELECT NameStreet [Улица],

PersonalNumber [№ лифта],

FIO [ФИО],

TypeOfApp [Видзаявки],

TypeOfDefect [Виднеисправности],

BreakDescription [Описаниеполомки],

BreakLevel [степень],

BreakTime [времяполомки],

Speciality [Специальность]

FROM Street ST INNER JOIN Elevators EL

ON ST.IDStreet=EL.IDStreet

INNER JOIN Request RE

ON EL.IDElevator=RE.IDElevator

INNER JOIN TypeOfApplication TA

ON RE.IDTypeOfApplication=TA.IDTypeOfApplication

INNER JOIN Orders ORD

ON RE.IDRequest=ORD.IDRequest

INNER JOIN Electrician ELC

ON ORD.IDElectrician=ELC.IDElectrician

INNER JOIN Defects DFS

ON ORD.IDOrders=DFS.IDOrders

INNER JOIN Defect DF

ON DFS.IDDefect=Df.IDDefect

;

SELECT * FROMprintall

--вывод всех поломанных лифтов с высокой степенью

CREATE VIEW print1El

AS

SELECT NameStreet [Улица],

PersonalNumber [Номерлифта],

FIO [ФИО],

TypeOfApp [Видзаявки],

TypeOfDefect [Виднеисправности],

BreakDescription [Описаниеполомки],

BreakLevel [степеньполомки]

FROM Street ST INNER JOIN Elevators EL

ON ST.IDStreet=EL.IDStreet