ОПИСАНИЕ ПРИКЛАДНОЙ ПРОГРАММЫ

⇐ Назад

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

Пример окна для работы с таблицами представлен на рисунке 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
⇐ Назад
12