Все окна, имеющиеся в программе, можно разделить на две группы: окна для работы с таблицами и окна для работы с запросами.
Пример окна для работы с таблицами представлен на рисунке 3.1.
| Рисунок 3.1 Окно для работы с таблицами |
Здесь пользователь может работать с содержимым одной из таблиц базы данных. При попытке внесения им некорректных данных на экран будет выведено соответствующее сообщение об ошибке (см. рисунок 3.2). Для фиксации всех произведенных изменений пользователю необходимо нажать кнопку «Cохранить» в правом верхнем углу окна, после чего программа проведет необходимые транзакции. Если при этом произойдет срабатывание триггера, то на экран будет выведено соответствующее сообщение (см. рисунок 3.3).
| Рисунок 3.2 Сообщение об ошибке внесения данных |
| Рисунок 3.3 Сообщение о срабатывании триггера |
Пример окна для работы с запросами представлен на рисунке 3.4.
| Рисунок 3.5 Окно для работы с запросами |
Здесь пользователь может задать параметры запроса, запустить его при помощи кнопки «Поиск» и просмотреть результат выполнения.
Для переключения между окнами необходимо использовать меню в верхней части окна. Для завершения работы с программой необходимо нажать кнопку «Закрыть» в правом верхнем углу окна (данная кнопка имеет вид иконки с изображением крестика).
ЗАКЛЮЧЕНИЕ
В результате проектирования информационной системы ГИБДД были получены база данных и прикладная программа, обеспечивающая интерфейс между пользователем и базой данных. В процессе выполнения работы были решены следующие задачи:
- Спроектирована схема данных
- Созданы необходимые таблицы согласно полученной схеме
- Реализованы указанные в задании запросы
- Созданы представления, имеющие смысл для данной предметной области
- Созданы пользователи и роли для управления доступом к различным объектам базы данных
- Созданы триггеры и ограничения целостности для поддержания целостности данных в базе данных
БИБЛИОГРАФИЧЕСКИЙ СПИСОК
1 Гарсиа-Молина, Г. Системы баз данных. Полный курс / Г. Гарсиа-Молина, Д. Ульман, Д. Уидом; пер. с англ. – М.: Издательский дом «Вильямс», 2003. – 1008 с.
2 Дейт, К. Дж. Введение в системы баз данных / К. Дж. Дейт; пер. с англ. – М.: Издательский дом «Вильямс», 2005. – 1328 с.
ПРИЛОЖЕНИЕ
SQL-скрипт для создания таблиц и внешних ключей
USE [master]
GO
/****** Object: Database [Gibdd] ******/
CREATE DATABASE [Gibdd]
CONTAINMENT = NONE
ON PRIMARY
(NAME = N'Gibdd', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Gibdd.mdf', SIZE = 5120KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB)
LOG ON
(NAME = N'Gibdd_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Gibdd_log.ldf', SIZE = 2048KB, MAXSIZE = 2048GB, FILEGROWTH = 10%)
GO
ALTER DATABASE [Gibdd] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Gibdd].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [Gibdd] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [Gibdd] SET ANSI_NULLS OFF
GO
ALTER DATABASE [Gibdd] SET ANSI_PADDING OFF
GO
ALTER DATABASE [Gibdd] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [Gibdd] SET ARITHABORT OFF
GO
ALTER DATABASE [Gibdd] SET AUTO_CLOSE ON
GO
ALTER DATABASE [Gibdd] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [Gibdd] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [Gibdd] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [Gibdd] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [Gibdd] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [Gibdd] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [Gibdd] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [Gibdd] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [Gibdd] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [Gibdd] SET DISABLE_BROKER
GO
ALTER DATABASE [Gibdd] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [Gibdd] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [Gibdd] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [Gibdd] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [Gibdd] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [Gibdd] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [Gibdd] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [Gibdd] SET RECOVERY SIMPLE
GO
ALTER DATABASE [Gibdd] SET MULTI_USER
GO
ALTER DATABASE [Gibdd] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [Gibdd] SET DB_CHAINING OFF
GO
ALTER DATABASE [Gibdd] SET FILESTREAM(NON_TRANSACTED_ACCESS = OFF)
GO
ALTER DATABASE [Gibdd] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
USE [Gibdd]
GO
/****** Object: User [test2] ******/
CREATE USER [test2] FOR LOGIN [test] WITH DEFAULT_SCHEMA=[guest]
GO
/****** Object: User [test] ******/
CREATE USER [test] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object: DatabaseRole [standart] ******/
CREATE ROLE [standart]
GO
/****** Object: DatabaseRole [Role] ******/
CREATE ROLE [Role]
GO
ALTER ROLE [standart] ADD MEMBER [test]
GO
/****** Object: Table [dbo].[Characteristics] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Characteristics](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
[Str] [nvarchar](max) NULL,
[Chisl] [int] NULL,
CONSTRAINT [PK_Characteristics_1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[DTP] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DTP](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[ReasonID] [smallint] NOT NULL,
[Data] [date] NOT NULL,
[Type] [smallint] NOT NULL,
[Arial] [nvarchar](max) NULL,
[StreetID] [smallint] NOT NULL,
CONSTRAINT [PK_DTP] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[DTP_Types] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DTP_Types](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_DTP_Types] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[Number_DTP] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Number_DTP](
[PtsID] [smallint] NOT NULL,
[DTP_ID] [smallint] NOT NULL,
[Leave] [bit] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Numbers] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Numbers](
[Avto_type] [smallint] NOT NULL,
[Number] [int] NOT NULL,
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Date] [date] NULL,
[SerialID] [smallint] NOT NULL,
[RegionID] [smallint] NOT NULL,
CONSTRAINT [PK_Numbers_1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Organisations] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Organisations](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
[StreetID] [smallint] NOT NULL,
[Manager] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_Organisations] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[PTS] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PTS](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[SignallingID] [smallint] NULL,
[Type] [smallint] NOT NULL,
[NumberID] [smallint] NOT NULL,
[Firm] [nvarchar](max) NOT NULL,
[Model] [nvarchar](max) NULL,
[Year] [date] NOT NULL,
[Color] [nvarchar](max) NOT NULL,
[Engine_number] [int] NULL,
[Chassis_number] [int] NOT NULL,
[Stand_number] [int] NOT NULL,
CONSTRAINT [PK_PTS] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[PTS_type] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PTS_type](
[id] [smallint] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_PTS_type] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[PTS_users] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PTS_users](
[PTS_ID] [smallint] NOT NULL,
[UserID] [smallint] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[PTS-chararacteristics] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PTS-chararacteristics](
[PTSID] [smallint] NOT NULL,
[characteristicID] [smallint] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Reason_DTP] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Reason_DTP](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_Reason_DTP] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[Reasosns] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Reasosns](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_Reasosns] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[Regions] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Regions](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Name] [int] NOT NULL,
CONSTRAINT [PK_Regions] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Serial] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Serial](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_Serial] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[Signalling] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Signalling](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_Signalling] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[Stealing] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Stealing](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[PTS_ID] [smallint] NOT NULL,
[ReasonID] [smallint] NOT NULL,
[Date] [date] NOT NULL,
[StreetID] [smallint] NOT NULL,
[Found] [bit] NOT NULL,
CONSTRAINT [PK_Stealing] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Streets] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Streets](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_Streets] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[To] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[To](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Date] [date] NOT NULL,
[PTS_ID] [smallint] NOT NULL,
[Passed] [bit] NOT NULL,
[Cost] [int] NOT NULL,
[period] [int] NOT NULL,
CONSTRAINT [PK_TO] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Users] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[First_name] [nvarchar](max) NOT NULL,
[Second_name] [nvarchar](max) NOT NULL,
[Father_name] [nvarchar](max) NOT NULL,
[Birthdate] [date] NOT NULL,
[Organisation] [smallint] NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[UsersTypes] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UsersTypes](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Name] [nchar](10) NULL,
CONSTRAINT [PK_UsersTypes] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: View [dbo].[Список_аварий_с_потсрадашими] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[Список_аварий_с_потсрадашими] AS
(SELECT DISTINCT DTP.Data, DTP_Types.Name as [Тип ДТП], Reason_DTP.Name as [Причина], Streets.Name as [Улица]
FROM DTP, Number_DTP, Streets, Reason_DTP, DTP_Types
WHERE DTP.ReasonID = Reason_DTP.ID
and DTP.StreetID = Streets.ID
and DTP.[Type] = DTP_Types.ID
and Number_DTP.DTP_ID = DTP.ID
and Number_DTP.Leave = 'true'
)
GO
/****** Object: View [dbo].[список_авто_принадлежащих_организации] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[список_авто_принадлежащих_организации] AS
(SELECT PTS.Firm, PTS.Color, Serial.Name as [Серия], Numbers.Number, Regions.Name as [Регион]
FROM PTS, Numbers, Users, Serial, Regions, PTS_users
WHERE Users.Organisation = '1'
and PTS_users.UserID = Users.ID
and PTS.ID = PTS_users.PTS_ID
and PTS.NumberID = Numbers.ID
and Serial.ID = Numbers.SerialID
and Regions.ID = Numbers.RegionID)
GO
/****** Object: View [dbo].[список_авто_скрвышихся_с_места_ДТП] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[список_авто_скрвышихся_с_места_ДТП] AS
(SELECT PTS.Firm, PTS.Color, Serial.Name as [Серия], Numbers.Number, Regions.Name as [Регион]
FROM PTS, Stealing, Numbers, Serial, Regions
WHERE Stealing.ReasonID = '1'
and Stealing.PTS_ID = PTS.ID
and PTS.NumberID = Numbers.ID
and Serial.ID = Numbers.SerialID
and Regions.ID = Numbers.RegionID)
GO
/****** Object: View [dbo].[список_граждан_не_прошедших_ТО] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[список_граждан_не_прошедших_ТО] AS
(SELECT Users.Second_name, Users.First_name, Users.Birthdate
FROM [TO], Users, PTS, 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')
GO
/****** Object: Index [IX_DTP_Types] ******/
CREATE NONCLUSTERED INDEX [IX_DTP_Types] ON [dbo].[DTP_Types]
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DTP] WITH CHECK ADD CONSTRAINT [FK_DTP_DTP_Types] FOREIGN KEY([Type])
REFERENCES [dbo].[DTP_Types] ([ID])
GO
ALTER TABLE [dbo].[DTP] CHECK CONSTRAINT [FK_DTP_DTP_Types]
GO
ALTER TABLE [dbo].[DTP] WITH CHECK ADD CONSTRAINT [FK_DTP_Reason_DTP] FOREIGN KEY([ReasonID])
REFERENCES [dbo].[Reason_DTP] ([ID])
GO
ALTER TABLE [dbo].[DTP] CHECK CONSTRAINT [FK_DTP_Reason_DTP]
GO
ALTER TABLE [dbo].[DTP] WITH CHECK ADD CONSTRAINT [FK_DTP_Streets] FOREIGN KEY([StreetID])
REFERENCES [dbo].[Streets] ([ID])
GO
ALTER TABLE [dbo].[DTP] CHECK CONSTRAINT [FK_DTP_Streets]
GO
ALTER TABLE [dbo].[Number_DTP] WITH CHECK ADD CONSTRAINT [FK_Number_DTP_DTP] FOREIGN KEY([DTP_ID])
REFERENCES [dbo].[DTP] ([ID])
GO
ALTER TABLE [dbo].[Number_DTP] CHECK CONSTRAINT [FK_Number_DTP_DTP]
GO
ALTER TABLE [dbo].[Number_DTP] WITH CHECK ADD CONSTRAINT [FK_Number_DTP_PTS] FOREIGN KEY([PtsID])
REFERENCES [dbo].[PTS] ([ID])
GO
ALTER TABLE [dbo].[Number_DTP] CHECK CONSTRAINT [FK_Number_DTP_PTS]
GO
ALTER TABLE [dbo].[Numbers] WITH CHECK ADD CONSTRAINT [FK_Numbers_PTS_type1] FOREIGN KEY([Avto_type])
REFERENCES [dbo].[PTS_type] ([id])
GO
ALTER TABLE [dbo].[Numbers] CHECK CONSTRAINT [FK_Numbers_PTS_type1]
GO
ALTER TABLE [dbo].[Numbers] WITH CHECK ADD CONSTRAINT [FK_Numbers_Regions] FOREIGN KEY([RegionID])
REFERENCES [dbo].[Regions] ([ID])
GO
ALTER TABLE [dbo].[Numbers] CHECK CONSTRAINT [FK_Numbers_Regions]
GO
ALTER TABLE [dbo].[Numbers] WITH CHECK ADD CONSTRAINT [FK_Numbers_Serial] FOREIGN KEY([SerialID])
REFERENCES [dbo].[Serial] ([ID])
GO
ALTER TABLE [dbo].[Numbers] CHECK CONSTRAINT [FK_Numbers_Serial]
GO
ALTER TABLE [dbo].[Organisations] WITH CHECK ADD CONSTRAINT [FK_Organisations_Streets] FOREIGN KEY([StreetID])
REFERENCES [dbo].[Streets] ([ID])
GO
ALTER TABLE [dbo].[Organisations] CHECK CONSTRAINT [FK_Organisations_Streets]
GO
ALTER TABLE [dbo].[PTS] WITH CHECK ADD CONSTRAINT [FK_PTS_Numbers] FOREIGN KEY([NumberID])
REFERENCES [dbo].[Numbers] ([ID])
GO
ALTER TABLE [dbo].[PTS] CHECK CONSTRAINT [FK_PTS_Numbers]
GO
ALTER TABLE [dbo].[PTS] WITH CHECK ADD CONSTRAINT [FK_PTS_PTS_type1] FOREIGN KEY([Type])
REFERENCES [dbo].[PTS_type] ([id])
GO
ALTER TABLE [dbo].[PTS] CHECK CONSTRAINT [FK_PTS_PTS_type1]
GO
ALTER TABLE [dbo].[PTS] WITH CHECK ADD CONSTRAINT [FK_PTS_Signalling] FOREIGN KEY([SignallingID])
REFERENCES [dbo].[Signalling] ([ID])
GO
ALTER TABLE [dbo].[PTS] CHECK CONSTRAINT [FK_PTS_Signalling]
GO
ALTER TABLE [dbo].[PTS_users] WITH CHECK ADD CONSTRAINT [FK_Number_users_Users1] FOREIGN KEY([UserID])
REFERENCES [dbo].[Users] ([ID])
GO
ALTER TABLE [dbo].[PTS_users] CHECK CONSTRAINT [FK_Number_users_Users1]
GO
ALTER TABLE [dbo].[PTS_users] WITH CHECK ADD CONSTRAINT [FK_PTS_users_PTS] FOREIGN KEY([PTS_ID])
REFERENCES [dbo].[PTS] ([ID])
GO
ALTER TABLE [dbo].[PTS_users] CHECK CONSTRAINT [FK_PTS_users_PTS]
GO
ALTER TABLE [dbo].[PTS-chararacteristics] WITH CHECK ADD CONSTRAINT [FK_PTS-chararacteristics_Characteristics] FOREIGN KEY([characteristicID])
REFERENCES [dbo].[Characteristics] ([ID])
GO
ALTER TABLE [dbo].[PTS-chararacteristics] CHECK CONSTRAINT [FK_PTS-chararacteristics_Characteristics]
GO
ALTER TABLE [dbo].[PTS-chararacteristics] WITH CHECK ADD CONSTRAINT [FK_PTS-chararacteristics_PTS] FOREIGN KEY([PTSID])
REFERENCES [dbo].[PTS] ([ID])
GO
ALTER TABLE [dbo].[PTS-chararacteristics] CHECK CONSTRAINT [FK_PTS-chararacteristics_PTS]
GO
ALTER TABLE [dbo].[Stealing] WITH CHECK ADD CONSTRAINT [FK_Stealing_PTS] FOREIGN KEY([PTS_ID])
REFERENCES [dbo].[PTS] ([ID])
GO
ALTER TABLE [dbo].[Stealing] CHECK CONSTRAINT [FK_Stealing_PTS]
GO
ALTER TABLE [dbo].[Stealing] WITH CHECK ADD CONSTRAINT [FK_Stealing_Reasosns] FOREIGN KEY([ReasonID])
REFERENCES [dbo].[Reasosns] ([ID])
GO
ALTER TABLE [dbo].[Stealing] CHECK CONSTRAINT [FK_Stealing_Reasosns]
GO
ALTER TABLE [dbo].[Stealing] WITH CHECK ADD CONSTRAINT [FK_Stealing_Streets] FOREIGN KEY([StreetID])
REFERENCES [dbo].[Streets] ([ID])
GO
ALTER TABLE [dbo].[Stealing] CHECK CONSTRAINT [FK_Stealing_Streets]
GO
ALTER TABLE [dbo].[To] WITH CHECK ADD CONSTRAINT [FK_TO_PTS1] FOREIGN KEY([PTS_ID])
REFERENCES [dbo].[PTS] ([ID])
GO
ALTER TABLE [dbo].[To] CHECK CONSTRAINT [FK_TO_PTS1]
GO
ALTER TABLE [dbo].[Users] WITH CHECK ADD CONSTRAINT [FK_Users_Organisations] FOREIGN KEY([Organisation])
REFERENCES [dbo].[Organisations] ([ID])
GO
ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users_Organisations]
GO
USE [master]
GO
ALTER DATABASE [Gibdd] SET READ_WRITE
GO
