SAS/tools/SQLQuerySAS.sql

USE [master]
GO
/****** Object:  Database [SAS]    Script Date: 12.12.2024 8:33:59 ******/
CREATE DATABASE [SAS]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'SAS', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.IS4\MSSQL\DATA\SAS.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON 
( NAME = N'SAS_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.IS4\MSSQL\DATA\SAS_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
 WITH CATALOG_COLLATION = DATABASE_DEFAULT, LEDGER = OFF
GO
ALTER DATABASE [SAS] SET COMPATIBILITY_LEVEL = 160
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [SAS].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [SAS] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [SAS] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [SAS] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [SAS] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [SAS] SET ARITHABORT OFF 
GO
ALTER DATABASE [SAS] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [SAS] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [SAS] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [SAS] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [SAS] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [SAS] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [SAS] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [SAS] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [SAS] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [SAS] SET  DISABLE_BROKER 
GO
ALTER DATABASE [SAS] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [SAS] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [SAS] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [SAS] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [SAS] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [SAS] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [SAS] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [SAS] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [SAS] SET  MULTI_USER 
GO
ALTER DATABASE [SAS] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [SAS] SET DB_CHAINING OFF 
GO
ALTER DATABASE [SAS] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [SAS] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO
ALTER DATABASE [SAS] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [SAS] SET ACCELERATED_DATABASE_RECOVERY = OFF  
GO
EXEC sys.sp_db_vardecimal_storage_format N'SAS', N'ON'
GO
ALTER DATABASE [SAS] SET QUERY_STORE = ON
GO
ALTER DATABASE [SAS] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), DATA_FLUSH_INTERVAL_SECONDS = 900, INTERVAL_LENGTH_MINUTES = 60, MAX_STORAGE_SIZE_MB = 1000, QUERY_CAPTURE_MODE = AUTO, SIZE_BASED_CLEANUP_MODE = AUTO, MAX_PLANS_PER_QUERY = 200, WAIT_STATS_CAPTURE_MODE = ON)
GO
USE [SAS]
GO
/****** Object:  User [user2]    Script Date: 12.12.2024 8:33:59 ******/
CREATE USER [user2] FOR LOGIN [user2] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  User [user19]    Script Date: 12.12.2024 8:33:59 ******/
CREATE USER [user19] FOR LOGIN [user19] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  User [user18]    Script Date: 12.12.2024 8:33:59 ******/
CREATE USER [user18] FOR LOGIN [user18] WITH DEFAULT_SCHEMA=[db_datareader]
GO
/****** Object:  DatabaseRole [developer]    Script Date: 12.12.2024 8:33:59 ******/
CREATE ROLE [developer]
GO
ALTER ROLE [db_owner] ADD MEMBER [user2]
GO
ALTER ROLE [db_owner] ADD MEMBER [user19]
GO
ALTER ROLE [developer] ADD MEMBER [user18]
GO
ALTER ROLE [db_owner] ADD MEMBER [user18]
GO
/****** Object:  Table [dbo].[Brand]    Script Date: 12.12.2024 8:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Brand](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](35) NOT NULL,
 CONSTRAINT [PK_Brand] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Category]    Script Date: 12.12.2024 8:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Category](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](3) NOT NULL,
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Citizen]    Script Date: 12.12.2024 8:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Citizen](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Surname] [varchar](30) NOT NULL,
	[Name] [varchar](25) NOT NULL,
	[Patronymic] [varchar](30) NULL,
	[Job] [int] NULL,
	[Email] [varchar](50) NULL,
	[Phone] [varchar](16) NULL,
	[PassSeries] [varchar](4) NOT NULL,
	[PassNumber] [varchar](6) NOT NULL,
	[MailIndex] [varchar](6) NULL,
	[RegistrationAddress] [varchar](150) NULL,
	[Photo] [int] NULL,
	[CurrentAddress] [varchar](150) NOT NULL,
	[Comment] [varchar](300) NULL,
	[Discription] [varchar](300) NULL,
 CONSTRAINT [PK_Citizen] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Color]    Script Date: 12.12.2024 8:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Color](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[HEX] [nchar](7) NOT NULL,
	[NameRus] [varchar](35) NOT NULL,
	[DiscriptionRus] [varchar](35) NOT NULL,
	[NameEng] [varchar](35) NOT NULL,
	[DiscriptionEng] [varchar](35) NOT NULL,
	[IsMetallic] [bit] NOT NULL,
 CONSTRAINT [PK_Color] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Company]    Script Date: 12.12.2024 8:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Company](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DivisionGIBDD]    Script Date: 12.12.2024 8:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DivisionGIBDD](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_DivisionGIBDD] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DriveType]    Script Date: 12.12.2024 8:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DriveType](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](35) NOT NULL,
 CONSTRAINT [PK_DriveType] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Employee]    Script Date: 12.12.2024 8:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employee](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Surname] [varchar](30) NOT NULL,
	[Name] [varchar](25) NOT NULL,
	[Patronymic] [varchar](30) NULL,
	[DivisionGIBDD] [int] NOT NULL,
	[Role] [int] NOT NULL,
	[Login] [varchar](50) NOT NULL,
	[Password] [varchar](256) NOT NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[EngineType]    Script Date: 12.12.2024 8:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EngineType](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[NameRus] [varchar](35) NOT NULL,
	[NameEng] [varchar](35) NOT NULL,
 CONSTRAINT [PK_EngineType] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Extension]    Script Date: 12.12.2024 8:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Extension](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](6) NOT NULL,
 CONSTRAINT [PK_Extension] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[File]    Script Date: 12.12.2024 8:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[File](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Extension] [int] NULL,
	[BinaryData] [varbinary](max) NOT NULL,
	[Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_File] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Job]    Script Date: 12.12.2024 8:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Job](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[Company] [int] NOT NULL,
 CONSTRAINT [PK_Job] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[License]    Script Date: 12.12.2024 8:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[License](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Citizen] [int] NOT NULL,
	[DateStart] [date] NOT NULL,
	[DiateFinish] [date] NOT NULL,
	[Series] [varchar](4) NOT NULL,
	[Number] [varchar](6) NOT NULL,
 CONSTRAINT [PK_License] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[LicenseCategory]    Script Date: 12.12.2024 8:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LicenseCategory](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Category] [int] NOT NULL,
	[License] [int] NOT NULL,
 CONSTRAINT [PK_LicenseCategory] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[LoginAttempt]    Script Date: 12.12.2024 8:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LoginAttempt](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Employee] [int] NOT NULL,
	[CreatedAt] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_LoginAttempt] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[LoginBlock]    Script Date: 12.12.2024 8:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LoginBlock](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Employee] [int] NOT NULL,
	[BlockedUntil] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_LoginBlock] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Model]    Script Date: 12.12.2024 8:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Model](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Brand] [int] NOT NULL,
	[Name] [varchar](35) NOT NULL,
 CONSTRAINT [PK_Model] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Region]    Script Date: 12.12.2024 8:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Region](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[NameRus] [varchar](50) NOT NULL,
	[NameEng] [varchar](50) NULL,
	[Number] [varchar](3) NOT NULL,
	[OKATO] [varchar](3) NULL,
	[ISO3166_2] [varchar](7) NULL,
 CONSTRAINT [PK_Region] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[RegionCode]    Script Date: 12.12.2024 8:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RegionCode](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Region] [int] NOT NULL,
	[Code] [varchar](3) NOT NULL,
 CONSTRAINT [PK_RegionCode] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Role]    Script Date: 12.12.2024 8:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Role](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Transport]    Script Date: 12.12.2024 8:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Transport](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Region] [int] NOT NULL,
	[VIN] [varchar](17) NOT NULL,
	[Category] [int] NOT NULL,
	[Color] [int] NOT NULL,
	[EngineType] [int] NOT NULL,
	[Model] [int] NOT NULL,
	[RelaseYear] [date] NOT NULL,
	[WeightKg] [int] NOT NULL,
	[DriveType] [int] NOT NULL,
 CONSTRAINT [PK_Transport] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TransportOwnership]    Script Date: 12.12.2024 8:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TransportOwnership](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Citizen] [int] NOT NULL,
	[Transport] [int] NOT NULL,
	[CreatedAt] [date] NOT NULL,
 CONSTRAINT [PK_TransportOwnership] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TransportPhoto]    Script Date: 12.12.2024 8:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TransportPhoto](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Transport] [int] NOT NULL,
	[Photo] [int] NOT NULL,
 CONSTRAINT [PK_TransportPhoto] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Citizen]  WITH CHECK ADD  CONSTRAINT [FK_Citizen_File] FOREIGN KEY([Photo])
REFERENCES [dbo].[File] ([ID])
GO
ALTER TABLE [dbo].[Citizen] CHECK CONSTRAINT [FK_Citizen_File]
GO
ALTER TABLE [dbo].[Citizen]  WITH CHECK ADD  CONSTRAINT [FK_Citizen_Job] FOREIGN KEY([Job])
REFERENCES [dbo].[Job] ([ID])
GO
ALTER TABLE [dbo].[Citizen] CHECK CONSTRAINT [FK_Citizen_Job]
GO
ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_DivisionGIBDD] FOREIGN KEY([DivisionGIBDD])
REFERENCES [dbo].[DivisionGIBDD] ([ID])
GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_DivisionGIBDD]
GO
ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_Role] FOREIGN KEY([Role])
REFERENCES [dbo].[Role] ([ID])
GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Role]
GO
ALTER TABLE [dbo].[File]  WITH CHECK ADD  CONSTRAINT [FK_File_Extension] FOREIGN KEY([Extension])
REFERENCES [dbo].[Extension] ([ID])
GO
ALTER TABLE [dbo].[File] CHECK CONSTRAINT [FK_File_Extension]
GO
ALTER TABLE [dbo].[Job]  WITH CHECK ADD  CONSTRAINT [FK_Job_Company] FOREIGN KEY([Company])
REFERENCES [dbo].[Company] ([ID])
GO
ALTER TABLE [dbo].[Job] CHECK CONSTRAINT [FK_Job_Company]
GO
ALTER TABLE [dbo].[License]  WITH CHECK ADD  CONSTRAINT [FK_License_License] FOREIGN KEY([Citizen])
REFERENCES [dbo].[Citizen] ([ID])
GO
ALTER TABLE [dbo].[License] CHECK CONSTRAINT [FK_License_License]
GO
ALTER TABLE [dbo].[LicenseCategory]  WITH CHECK ADD  CONSTRAINT [FK_LicenseCategory_Category] FOREIGN KEY([Category])
REFERENCES [dbo].[Category] ([ID])
GO
ALTER TABLE [dbo].[LicenseCategory] CHECK CONSTRAINT [FK_LicenseCategory_Category]
GO
ALTER TABLE [dbo].[LicenseCategory]  WITH CHECK ADD  CONSTRAINT [FK_LicenseCategory_License] FOREIGN KEY([License])
REFERENCES [dbo].[License] ([ID])
GO
ALTER TABLE [dbo].[LicenseCategory] CHECK CONSTRAINT [FK_LicenseCategory_License]
GO
ALTER TABLE [dbo].[LoginAttempt]  WITH CHECK ADD  CONSTRAINT [FK_LoginAttempt_Employee] FOREIGN KEY([Employee])
REFERENCES [dbo].[Employee] ([ID])
GO
ALTER TABLE [dbo].[LoginAttempt] CHECK CONSTRAINT [FK_LoginAttempt_Employee]
GO
ALTER TABLE [dbo].[LoginBlock]  WITH CHECK ADD  CONSTRAINT [FK_LoginBlock_Employee] FOREIGN KEY([Employee])
REFERENCES [dbo].[Employee] ([ID])
GO
ALTER TABLE [dbo].[LoginBlock] CHECK CONSTRAINT [FK_LoginBlock_Employee]
GO
ALTER TABLE [dbo].[Model]  WITH CHECK ADD  CONSTRAINT [FK_Model_Brand] FOREIGN KEY([Brand])
REFERENCES [dbo].[Brand] ([ID])
GO
ALTER TABLE [dbo].[Model] CHECK CONSTRAINT [FK_Model_Brand]
GO
ALTER TABLE [dbo].[RegionCode]  WITH CHECK ADD  CONSTRAINT [FK_RegionCode_Region] FOREIGN KEY([Region])
REFERENCES [dbo].[Region] ([ID])
GO
ALTER TABLE [dbo].[RegionCode] CHECK CONSTRAINT [FK_RegionCode_Region]
GO
ALTER TABLE [dbo].[Transport]  WITH CHECK ADD  CONSTRAINT [FK_Transport_Category] FOREIGN KEY([Category])
REFERENCES [dbo].[Category] ([ID])
GO
ALTER TABLE [dbo].[Transport] CHECK CONSTRAINT [FK_Transport_Category]
GO
ALTER TABLE [dbo].[Transport]  WITH CHECK ADD  CONSTRAINT [FK_Transport_Color] FOREIGN KEY([Color])
REFERENCES [dbo].[Color] ([ID])
GO
ALTER TABLE [dbo].[Transport] CHECK CONSTRAINT [FK_Transport_Color]
GO
ALTER TABLE [dbo].[Transport]  WITH CHECK ADD  CONSTRAINT [FK_Transport_DriveType] FOREIGN KEY([DriveType])
REFERENCES [dbo].[DriveType] ([ID])
GO
ALTER TABLE [dbo].[Transport] CHECK CONSTRAINT [FK_Transport_DriveType]
GO
ALTER TABLE [dbo].[Transport]  WITH CHECK ADD  CONSTRAINT [FK_Transport_EngineType] FOREIGN KEY([EngineType])
REFERENCES [dbo].[EngineType] ([ID])
GO
ALTER TABLE [dbo].[Transport] CHECK CONSTRAINT [FK_Transport_EngineType]
GO
ALTER TABLE [dbo].[Transport]  WITH CHECK ADD  CONSTRAINT [FK_Transport_Model] FOREIGN KEY([Model])
REFERENCES [dbo].[Model] ([ID])
GO
ALTER TABLE [dbo].[Transport] CHECK CONSTRAINT [FK_Transport_Model]
GO
ALTER TABLE [dbo].[Transport]  WITH CHECK ADD  CONSTRAINT [FK_Transport_Region] FOREIGN KEY([Region])
REFERENCES [dbo].[Region] ([ID])
GO
ALTER TABLE [dbo].[Transport] CHECK CONSTRAINT [FK_Transport_Region]
GO
ALTER TABLE [dbo].[TransportOwnership]  WITH CHECK ADD  CONSTRAINT [FK_TransportOwnership_Citizen] FOREIGN KEY([Citizen])
REFERENCES [dbo].[Citizen] ([ID])
GO
ALTER TABLE [dbo].[TransportOwnership] CHECK CONSTRAINT [FK_TransportOwnership_Citizen]
GO
ALTER TABLE [dbo].[TransportOwnership]  WITH CHECK ADD  CONSTRAINT [FK_TransportOwnership_Transport] FOREIGN KEY([Transport])
REFERENCES [dbo].[Transport] ([ID])
GO
ALTER TABLE [dbo].[TransportOwnership] CHECK CONSTRAINT [FK_TransportOwnership_Transport]
GO
ALTER TABLE [dbo].[TransportPhoto]  WITH CHECK ADD  CONSTRAINT [FK_TransportPhoto_File] FOREIGN KEY([Photo])
REFERENCES [dbo].[File] ([ID])
GO
ALTER TABLE [dbo].[TransportPhoto] CHECK CONSTRAINT [FK_TransportPhoto_File]
GO
ALTER TABLE [dbo].[TransportPhoto]  WITH CHECK ADD  CONSTRAINT [FK_TransportPhoto_Transport] FOREIGN KEY([Transport])
REFERENCES [dbo].[Transport] ([ID])
GO
ALTER TABLE [dbo].[TransportPhoto] CHECK CONSTRAINT [FK_TransportPhoto_Transport]
GO
USE [master]
GO
ALTER DATABASE [SAS] SET  READ_WRITE 
GO