Проектирование схемы данных

ВВЕДЕНИЕ

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

- Спроектировать схему данных

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

- Реализовать указанные в задании запросы

- Создать представления, имеющие смысл для данной предметной области

- Создать пользователей и роли для управления доступом к различным объектам базы данных

- Создать триггеры и ограничения целостности для поддержания целостности данных в базе данных

ПРОЕКТИРОВАНИЕ ИНФОРМАЦИОННОЙ СИСТЕМЫ ГИБДД

Проектирование схемы данных

Полученная схема данных представлена на рисунке 2.1:

Рассмотрим полученные таблицы:

Рассмотрим каждую таблицу:

PTS:

· ID - Идентификатор

· SignalingID – Идентификатор сигнализации (может быть NULL)

· Type – Идентификатор типа ТС

· NumberID – Идентификатор номера ТС

· Firm – фирма изготовитель

· Model – модель ТС

· Year – год производства ТС

· Color – цвет

· Engine_number – номер двигателя (может принимать NULL для ТС без двигателя)

· Chassis_number – номер шасси

· Stand_number – номер кузова

PTS_Types:

· ID – Идентификатор

· Name – Тип ТС

Charasteristics:

· ID – Идентификатор

· Name – название характеристики

· Str – строковое значение характеристики

· Chisl – числовое значение характеристики

PTS-Characteristics:

· PTSID – Идентификатор ТС

· CharacteristicID – Идентификатор характеристики

TO:

· ID – Идентификатор

· Date – дата прохождения ТО

· PTS_ID – Идентификатор ТС

· Passed – информация о своевременном прохождении ТО

· Cost - стоимость прохождения

· Period – период прохождения ТО

Numbers:

· Avto_type – Идентификатор типа ТС

· Number – номер ТС

· ID – Идентификатор

· Date – Дата выдачи номера

· SeriallD – Идентификатор серии номера ТС

· RegionID – Идентификатор региона номера ТС

Serial:

· ID – Идентификатор

· Name – серия номера ТС

Regions:

· ID – Идентификатор

· Name – регион номера ТС

Streers:

· ID – Идентификатор

· Name – название улицы

PTS_users:

· PTS_ID – Идентификатор ТС

· UserID – Идентификатор пользователя

Users

· ID – Идентификатор

· First_name – имя

· Second_name – фамилия

· Father_name – отчество

· Birthday – день рождения

· Organisation – идентификатор организации(может принимать NULL если пользователь частное лицо)

Organisations:

· ID – Идентификатор

· Name – название организации

· StreetID – идентификатор улица расположения организации

· Manager – Директор организации

Stealing:

· ID – Идентификатор

· PTS_ID– идентификатор ТС

· ReasonID – идентификатор причины розыска ТС

· Date – дата начала розыска

· StreetID – идентификатор улицы

· Found – найдено ли ТС

Reasons

· ID – Идентификатор

· Name – причина розыска

DTP_Type:

· ID – Идентификатор

· Name – Тип ДТП

Reason_DTP:

· ID – Идентификатор

· Name – Причина ДТП

DTP:

· ID – Идентификатор

· ReasonID –Идентификатор причины ДТП

· Date– Дата ДТП

· Type – Идентификатор типа ДТП

· Arial –комментарий к месту ДТП

· StreetID –Идентификатор улицы

Number_DTP:

· PTSID –Идентификатор ТС

· DTP_ID –Идентификатор ДТП

· Leave –Информация о пострадавших

Создание запросов

Подготовим 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'

Результат:

12
Далее ⇒