ВВЕДЕНИЕ
В результате проектирования информационной системы ГИБДД должны быть получены база данных и прикладная программа, обеспечивающая интерфейс между пользователем и базой данных. В процессе выполнения работы необходимо решить следующие задачи:
- Спроектировать схему данных
- Согласно полученной схеме создать необходимые таблицы, предварительно определив тип данных каждого их поля
- Реализовать указанные в задании запросы
- Создать представления, имеющие смысл для данной предметной области
- Создать пользователей и роли для управления доступом к различным объектам базы данных
- Создать триггеры и ограничения целостности для поддержания целостности данных в базе данных
ПРОЕКТИРОВАНИЕ ИНФОРМАЦИОННОЙ СИСТЕМЫ ГИБДД
Проектирование схемы данных
Полученная схема данных представлена на рисунке 2.1:
| Рисунок 1.1 Схема данных |
Рассмотрим полученные таблицы:
| Таблица | Описание |
| PTS | Общие характеристики, которые присущи каждому виду транспорта |
| PTS_type | Справочник типов транспортных средств |
| Charasteristics | Перечень характеристик с их числовым, либо строковым значением |
| PTS-Characteristics | Смежная таблица, для того, чтобы обеспечить связь “многие ко многим” между PTS и Characteristics. |
| TO | Содержит всю информацию о прохождении техосмотра, включая период его прохождения |
| Numbers | Хранит перечень свободных и занятых номеров |
| Serial | Справочник серий номеров ТС |
| Regions | Справочник регионов номеров ТС |
| Streers | Справочник улиц |
| PTS_users | Смежная таблица, для того, чтобы обеспечить связь “многие ко многим” между PTS и Users |
| Users | содержит всю информацию о владельце, включая также то, является ли он юридическим лицом. |
| Organisations | Содержит информацию об организациях |
| Stealing | Содержит информацию о автомобилях, находившимся в розыске |
| Reasons | Справочник причин розыска |
| DTP_Type | Справочник типов ДТП |
| Reason_DTP | Справочник причин ДТП |
| DTP | Содержит всю информацию о дтп |
| Number_DTP | Смежная таблица, для того, чтобы обеспечить связь “многие ко многим” между PTS и DTP. |
| Таблица 1.1 Описание таблиц |
Рассмотрим каждую таблицу:
PTS:
· ID - Идентификатор
· SignalingID – Идентификатор сигнализации (может быть NULL)
· Type – Идентификатор типа ТС
· NumberID – Идентификатор номера ТС
· Firm – фирма изготовитель
· Model – модель ТС
· Year – год производства ТС
· Color – цвет
· Engine_number – номер двигателя (может принимать NULL для ТС без двигателя)
· Chassis_number – номер шасси
· Stand_number – номер кузова
| Рисунок 1.2 типы данных PTS |
PTS_Types:
· ID – Идентификатор
· Name – Тип ТС
| Рисунок 1.3 типы данных PTS_Types |
Charasteristics:
· ID – Идентификатор
· Name – название характеристики
· Str – строковое значение характеристики
· Chisl – числовое значение характеристики
| Рисунок 1.4 типы данных Charasteristics |
PTS-Characteristics:
· PTSID – Идентификатор ТС
· CharacteristicID – Идентификатор характеристики
| Рисунок 1.5 типы данных PTS - Charasteristics |
TO:
· ID – Идентификатор
· Date – дата прохождения ТО
· PTS_ID – Идентификатор ТС
· Passed – информация о своевременном прохождении ТО
· Cost - стоимость прохождения
· Period – период прохождения ТО
| Рисунок 1.6 типы данных TO |
Numbers:
· Avto_type – Идентификатор типа ТС
· Number – номер ТС
· ID – Идентификатор
· Date – Дата выдачи номера
· SeriallD – Идентификатор серии номера ТС
· RegionID – Идентификатор региона номера ТС
| Рисунок 1.7 типы данных Charasteristics |
Serial:
· ID – Идентификатор
· Name – серия номера ТС
| Рисунок 1.8 типы данных Serial |
Regions:
· ID – Идентификатор
· Name – регион номера ТС
| Рисунок 1.9 типы данных Regions |
Streers:
· ID – Идентификатор
· Name – название улицы
| Рисунок 1.10 типы данных Streets |
PTS_users:
· PTS_ID – Идентификатор ТС
· UserID – Идентификатор пользователя
| Рисунок 1.11 типы данных PTS_users |
Users
· ID – Идентификатор
· First_name – имя
· Second_name – фамилия
· Father_name – отчество
· Birthday – день рождения
· Organisation – идентификатор организации(может принимать NULL если пользователь частное лицо)
| Рисунок 1.11 типы данных Users |
Organisations:
· ID – Идентификатор
· Name – название организации
· StreetID – идентификатор улица расположения организации
· Manager – Директор организации
| Рисунок 1.12 типы данных Organisations |
Stealing:
· ID – Идентификатор
· PTS_ID – идентификатор ТС
· ReasonID – идентификатор причины розыска ТС
· Date – дата начала розыска
· StreetID – идентификатор улицы
· Found – найдено ли ТС
| Рисунок 1.13 типы данных Stealing |
Reasons
· ID – Идентификатор
· Name – причина розыска
| Рисунок 1.14 типы данных Reasons |
DTP_Type:
· ID – Идентификатор
· Name – Тип ДТП
| Рисунок 1.15 типы данных DTP_Type |
Reason_DTP:
· ID – Идентификатор
· Name – Причина ДТП
| Рисунок 1.16 типы данных Reason_DTP |
DTP:
· ID – Идентификатор
· ReasonID – Идентификатор причины ДТП
· Date – Дата ДТП
· Type – Идентификатор типа ДТП
· Arial – комментарий к месту ДТП
· StreetID – Идентификатор улицы
| Рисунок 1.17 типы данных DTP |
Number_DTP:
· PTSID – Идентификатор ТС
· DTP_ID – Идентификатор ДТП
· Leave – Информация о пострадавших
| Рисунок 1.18 типы данных Number_DTP |
Создание запросов
Подготовим SQL-скрипт для создания хранимых процедур, которые будут выполнять запросы, описанные в техническом задании:
1. Получить перечень и общее число организаций, которым выделены номера либо с указанной серией, либо за указанный период.
SELECT Organisations.Name, Organisations.Arial, Organisations.Addres, Organisations.Manager
FROM Serial, Numbers, PTS, Users, Organisations, PTS_users
WHERE Serial.ID = Numbers.SerialID
and Numbers.ID = PTS.NumberID
and PTS.ID = PTS_users.PTS_ID
and PTS_users.UserID = Users.ID
and Users.Organisation = Organisations.ID
and Serial.Name = 'CTO'
SELECT COUNT (Organisations.Name) as [Общее число организаций]
FROM Serial, Numbers, PTS, Users, Organisations, PTS_users
WHERE Serial.ID = Numbers.SerialID
and Numbers.ID = PTS.NumberID
and PTS.ID = PTS_users.PTS_ID
and PTS_users.UserID = Users.ID
and Users.Organisation = Organisations.ID
and Serial.Name = 'CTO'
SELECT Organisations.Name, Organisations.Arial, Organisations.Addres, Organisations.Manager
FROM Serial, Numbers, PTS, Users, Organisations, PTS_users
WHERE Serial.ID = Numbers.SerialID
and Numbers.ID = PTS.NumberID
and PTS.ID = PTS_users.PTS_ID
and PTS_users.UserID = Users.ID
and Users.Organisation = Organisations.ID
and Numbers.[Date] BETWEEN '07.10.2007' AND '14.08.2013'
Результат выполнения запроса:
Рисунок 1.19 Пример выполнения запроса
2. Получить сведения о владельце автотранспортного средства по государственному номеру автомашины.
SELECT Users.Second_name, Users.First_name, Users.Father_name, Users.Birthdate
FROM Numbers, PTS, USERS, PTS_users
WHERE Numbers.ID = PTS.NumberID
and PTS.ID = PTS_users.PTS_ID
and PTS_users.UserID = Users.ID
and Users.Organisation is NULL
and Numbers.Number = '125'
SELECT Users.Second_name, Users.First_name, Users.Father_name, Users.Birthdate, Organisations.Name, Organisations.Addres, Organisations.Arial, Organisations.Manager
FROM Numbers, PTS, USERS, PTS_users, Organisations
WHERE Numbers.ID = PTS.NumberID
and PTS.ID = PTS_users.PTS_ID
and PTS_users.UserID = Users.ID
and Users.Organisation = Organisations.ID
and Numbers.Number = '323'
Результат выполнения запроса:
Рисунок 1.20 Пример выполнения запроса
3. Получить "досье" на автомобиль по государственному номеру - номера двигателя, кузова и шасси, участвовал ли в ДТП, прошел ли техосмотр.
SELECT PTS.Firm, PTS.Engine_number, PTS.Chassis_number, PTS.Stand_number, DTP.Data as [Дата аварии], [To].Passed as [Техосмотр]
FROM Numbers, PTS, Number_DTP, DTP, [To]
WHERE Numbers.ID = PTS.NumberID
and Number_DTP.PtsID = PTS.ID
and DTP.ID = Number_DTP.DTP_ID
and Numbers.ID = Pts.NumberID
and Numbers.Number = '72'
and [To].PTS_ID = PTS.ID
Результат выполнения запроса:
Рисунок 1.21 Пример выполнения запроса
4. Получить перечень и общее число владельцев машин, не прошедших вовремя техосмотр.
SELECT Users.Second_name, Users.First_name, Users.Father_name, Users.Birthdate
FROM [TO], PTS, Users, PTS_users
WHERE Users.ID = PTS_users.UserID
and PTS_users.PTS_ID = PTS.ID
and [TO].PTS_ID = PTS.ID
and [TO].Passed = 'false'
SELECT COUNT (Users.Second_name) as [Общее число владельцев]
FROM [TO], PTS, Users, PTS_users
WHERE Users.ID = PTS_users.UserID
and PTS_users.PTS_ID = PTS.ID
and [TO].PTS_ID = PTS.ID
and [TO].Passed = 'false'
Результат выполнения запроса:
Рисунок 1.22 Пример выполнения запроса
5. Получить статистику по любому типу ДТП за указанный период.
SELECT COUNT(DTP.ID) as [Количество ДТП], COUNT(Number_DTP.PtsID)
as [Количество участников], COUNT(Number_DTP.Leave) as[Количество пострадавших]
FROM DTP, DTP_Types, Number_DTP
WHERE DTP_Types.Name = 'Наезд на велосипедиста'
and DTP.[Type] = DTP_Types.ID
and DTP.ID = Number_DTP.DTP_ID
and DTP.Data BETWEEN '06.07.2013' and '02.10.2015'
Результат выполнения запроса:
Рисунок 1.23 Пример выполнения запроса
6. Получить результаты анализа ДТП: самые опасные места в городе, самая частая причина ДТП
SELECT TOP 5 ДТП.Name, Количество
FROM (SELECT count(DTP.ReasonID) AS Количество, Reason_DTP.Name
FROM Reason_DTP, DTP
WHERE Reason_DTP.ID = DTP.ReasonID
GROUP BY Reason_DTP.Name) AS ДТП
ORDER BY Количество desc
SELECT TOP 5 ДТП.Name, Количество
FROM (SELECT count(DTP.StreetID) AS Количество, Streets.Name
FROM Streets, DTP
WHERE Streets.ID = DTP.StreetID
GROUP BY Streets.Name) AS ДТП
ORDER BY Количество desc
Результат выполнения запроса:
Рисунок 1.24 Пример выполнения запроса
7. Получить данные о количестве ДТП, совершаемых водителями в нетрезвом виде и доля таких происшествий в общем количестве ДТП.
SELECT B_DTP.[Количество проишествий], A_DTP.[Количество проишествий по причине], A_DTP.[Количество проишествий по причине]*100/B_DTP.[Количество проишествий] as [Доля проишествий(%)]
FROM (SELECT COUNT(DTP.ID) as [Количество проишествий по причине] FROM DTP, Reason_DTP WHERE DTP.ReasonID = Reason_DTP.ID and Reason_DTP.ID = '2') AS A_DTP,
(SELECT COUNT(DTP.ID) as [Количество проишествий] FROM DTP, Reason_DTP WHERE DTP.ReasonID = Reason_DTP.ID) AS B_DTP
Результат выполнения запроса:
Рисунок 1.25 Пример выполнения запроса
8. Получить список машин, отданных в розыск, будь то скрывшиеся с места ДТП или угнанные.
SELECT PTS_type.name as [Type],PTS.Firm, PTS.Color, Numbers.Number, Serial.Name as [Serial], Regions.Name as [Region], PTS.Engine_number, PTS.Engine_number, PTS.Stand_number, Stealing.[Date] as [Stealing date], Reasosns.Name as [Причина]
FROM Stealing, PTS, Numbers, Regions, PTS_type, Serial, Reasosns
WHERE Stealing.PTS_ID = PTS.ID
and PTS.NumberID = Numbers.ID
and Serial.ID = Numbers.SerialID
and Regions.ID = Numbers.RegionID
and PTS_type.id = PTS.[Type]
and Stealing.ReasonID = Reasosns.ID
and Stealing.Found = 'false'
Результат выполнения запроса:
Рисунок 1.26 Пример выполнения запроса
9. Получить данные об эффективности розыскной работы: количество найденных машин в процентном отношении.
SELECT steel_table.steel as [Найденные автомобили], nsteel_table.nsteel as [Общее количество], steel_table.steel*100/nsteel_table.nsteel as[Процент найденных]
FROM (SELECT COUNT(Stealing.ID) as steel FROM Stealing WHERE Stealing.Found = 'true') as steel_table,
(SELECT COUNT(Stealing.ID) as nsteel FROM Stealing) as nsteel_table
Результат выполнения запроса:
Рисунок 1.27 Пример выполнения запроса
10. Получить перечень и общее число угонов за указанный период.
SELECT PTS.Firm, PTS.Color, Numbers.Number, Serial.Name, Stealing.[Date], Stealing.Citi, Stealing.Street
FROM Stealing, PTS, Numbers, Serial, Reasosns
WHERE Serial.ID = Numbers.SerialID
and Numbers.ID = PTS.NumberID
and PTS.ID = Stealing.PTS_ID
and Reasosns.ID = '2'
and Stealing.[Date] BETWEEN '03.09.2013'AND'21.12.2015'
Результат выполнения запроса:
Рисунок 2.28 Пример выполнения запроса
11. Получить статистику по угонам: самые угоняемые марки машин, самые надежные сигнализации и т.п.
SELECT TOP 5 Угон.Firm, Количество
FROM (SELECT COUNT(PTS.ID) AS количество, PTS.Firm
FROM PTS, Stealing
WHERE Stealing.PTS_ID = PTS.ID
and Stealing.ReasonID = '2'
GROUP BY PTS.Firm) as Угон
ORDER BY Количество desc
SELECT TOP 5 Сигнализация.Name, Количество
FROM (SELECT COUNT(PTS.ID) AS количество, Signalling.Name
FROM PTS, Stealing, Signalling
WHERE Stealing.PTS_ID = PTS.ID
and Signalling.ID = PTS.SignallingID
and Stealing.ReasonID = '2'
GROUP BY Signalling.Name) as Сигнализация
ORDER BY Количество desc
Результат выполнения запроса:
Рисунок 1.29 Пример выполнения запроса
Создание триггеров
Подготовим SQL-скрипты для создания следующих триггеров:
1. Триггер для проверки корректного ввода даты выдачи номера тс:
CREATE TRIGGER Проверка_даты_выдачи_номера
ON Numbers
AFTER INSERT, UPDATE
AS
IF EXISTS (
SELECT *
FROM Numbers, Users
WHERE Users.Birthdate > Numbers.[Date])
BEGIN
ROLLBACK
PRINT 'Неверная дата выдачи номера'
END
Проверка триггера:
UPDATE Numbers
SET Date = '1970-06-03'
WHERE Date = '2006-06-03'
Результат:
2. Триггер для проверки корректного ввода даты аварии:
CREATE TRIGGER Проверка_даты_аварии
ON DTP
AFTER INSERT, UPDATE
AS
IF EXISTS (
SELECT *
FROM DTP, PTS
WHERE PTS.[Year] > DTP.Data)
BEGIN
ROLLBACK
PRINT 'Неверная дата аварии'
END
Проверка триггера:
UPDATE DTP
SET Data = '2015-12-12'
WHERE Data = '1999-08-09'
Результат:
3. Триггер для проверки даты пропажи (кража, либо владелец скрылся с места дтп):
CREATE TRIGGER Проверка_даты_пропажи
ON Stealing
AFTER INSERT, UPDATE
AS
IF EXISTS (
SELECT *
FROM Stealing, PTS
WHERE PTS.[Year] > Stealing.[Date])
BEGIN
ROLLBACK
PRINT 'Неверная дата пропажи транспортного средства'
END
Проверка триггера:
UPDATE Stealing
SET Date = '2015-08-09'
WHERE Date = '1999-08-09'
Результат:
