USE [master]
GO
/****** Object:  Database [digital_world_8]    Script Date: 20.12.2023 16:24:51 ******/
CREATE DATABASE [digital_world_8]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'digital_world_81', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\digital_world_81.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'digital_world_81_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\digital_world_81_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO
ALTER DATABASE [digital_world_8] SET COMPATIBILITY_LEVEL = 150
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [digital_world_8].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [digital_world_8] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [digital_world_8] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [digital_world_8] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [digital_world_8] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [digital_world_8] SET ARITHABORT OFF 
GO
ALTER DATABASE [digital_world_8] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [digital_world_8] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [digital_world_8] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [digital_world_8] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [digital_world_8] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [digital_world_8] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [digital_world_8] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [digital_world_8] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [digital_world_8] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [digital_world_8] SET  DISABLE_BROKER 
GO
ALTER DATABASE [digital_world_8] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [digital_world_8] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [digital_world_8] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [digital_world_8] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [digital_world_8] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [digital_world_8] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [digital_world_8] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [digital_world_8] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [digital_world_8] SET  MULTI_USER 
GO
ALTER DATABASE [digital_world_8] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [digital_world_8] SET DB_CHAINING OFF 
GO
ALTER DATABASE [digital_world_8] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [digital_world_8] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [digital_world_8] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [digital_world_8] SET ACCELERATED_DATABASE_RECOVERY = OFF  
GO
EXEC sys.sp_db_vardecimal_storage_format N'digital_world_8', N'ON'
GO
ALTER DATABASE [digital_world_8] SET QUERY_STORE = OFF
GO
USE [digital_world_8]
GO
/****** Object:  Table [dbo].[Components]    Script Date: 20.12.2023 16:24:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Components](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Название] [nvarchar](max) NULL,
	[Id единицы измерения] [int] NULL,
	[Тип_материала] [nvarchar](50) NULL,
	[Цена] [int] NULL,
	[количество] [int] NULL,
	[Id поставки] [int] NULL,
 CONSTRAINT [PK_Components] 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].[computer_assembly]    Script Date: 20.12.2023 16:24:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[computer_assembly](
	[Номер сборки] [int] IDENTITY(1,1) NOT NULL,
	[Видеокарта] [nvarchar](150) NULL,
	[Материнская плата] [nvarchar](150) NULL,
	[Процессор] [nvarchar](150) NULL,
	[Оперативная память] [nvarchar](150) NULL,
	[Оперативная_память] [nvarchar](150) NULL,
	[Жетский диск] [nvarchar](150) NULL,
	[Куллер] [nvarchar](150) NULL,
	[Система охлаждения] [nvarchar](150) NULL,
	[Блок питания] [nvarchar](150) NULL,
	[SSD] [nvarchar](150) NULL,
	[Итоговая цена] [int] NULL,
 CONSTRAINT [PK_computer_assembly] PRIMARY KEY CLUSTERED 
(
	[Номер сборки] 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].[Order]    Script Date: 20.12.2023 16:24:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Order](
	[Id_заказа] [int] IDENTITY(1,1) NOT NULL,
	[Номер заказа] [int] NULL,
	[Дата] [date] NULL,
	[Номер сборки] [int] NULL,
	[id статуса] [int] NULL,
 CONSTRAINT [PK_Order] 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].[order_by_clients]    Script Date: 20.12.2023 16:24:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[order_by_clients](
	[Номер заказа] [int] IDENTITY(1,1) NOT NULL,
	[Пожелания] [nvarchar](50) NULL,
	[Дата] [date] NULL,
 CONSTRAINT [PK_order_by_clients] PRIMARY KEY CLUSTERED 
(
	[Номер заказа] 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].[Order_status]    Script Date: 20.12.2023 16:24:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Order_status](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Название статуса] [nvarchar](50) NULL,
 CONSTRAINT [PK_Order_status] 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].[Postavka_material]    Script Date: 20.12.2023 16:24:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Postavka_material](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[id производителя] [int] NULL,
	[Дата] [date] NULL,
	[Название] [nvarchar](150) NULL,
	[Тип материала] [nvarchar](150) NULL,
	[Количество] [int] NULL,
 CONSTRAINT [PK_Postavka_material] 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].[provider]    Script Date: 20.12.2023 16:24:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[provider](
	[id] [int] NOT NULL,
	[название] [nvarchar](50) NULL,
	[Страна производитель] [nvarchar](50) NULL,
 CONSTRAINT [PK_provider] 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: 20.12.2023 16:24:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Role](
	[Id] [int] NOT NULL,
	[Название роли] [nvarchar](50) 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].[unit_of_measurement]    Script Date: 20.12.2023 16:24:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[unit_of_measurement](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[название] [nvarchar](50) NULL,
 CONSTRAINT [PK_unit_of_measurement] 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].[Users]    Script Date: 20.12.2023 16:24:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
	[Id] [int] NOT NULL,
	[Username] [nvarchar](50) NULL,
	[Password] [nvarchar](50) NULL,
	[Role] [int] 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, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Components] ON 

INSERT [dbo].[Components] ([id], [Название], [Id единицы измерения], [Тип_материала], [Цена], [количество], [Id поставки]) VALUES (1, N'Gigabyte GeForce RTX 3060 Ti GAMING OC PRO 8GB', 1, N'Видеокарта', 24000, 10, 1)
INSERT [dbo].[Components] ([id], [Название], [Id единицы измерения], [Тип_материала], [Цена], [количество], [Id поставки]) VALUES (2, N'MSI MAG B550 TOMAHAWK AM4', 1, N'Материнская плата', 3112, 14, 1)
INSERT [dbo].[Components] ([id], [Название], [Id единицы измерения], [Тип_материала], [Цена], [количество], [Id поставки]) VALUES (3, N'Intel Core i7 8700K OEM Coffee Lake 1151v2', 1, N'Процессор', 28990, 12, 1)
INSERT [dbo].[Components] ([id], [Название], [Id единицы измерения], [Тип_материала], [Цена], [количество], [Id поставки]) VALUES (4, N'16Gb 3200MHz Kingston HyperX Fury 2x8Gb KIT', 1, N'Оперативная память', 7947, 12, 1)
INSERT [dbo].[Components] ([id], [Название], [Id единицы измерения], [Тип_материала], [Цена], [количество], [Id поставки]) VALUES (5, N'DDR4 Corsair 8Gb 2133MHz', 1, N'Оперативная память', 4032, 11, 1)
INSERT [dbo].[Components] ([id], [Название], [Id единицы измерения], [Тип_материала], [Цена], [количество], [Id поставки]) VALUES (6, N'2Tb SATA-III Western Digital Red', 1, N'Жесткий диск', 6959, 31, 1)
INSERT [dbo].[Components] ([id], [Название], [Id единицы измерения], [Тип_материала], [Цена], [количество], [Id поставки]) VALUES (7, N'PCcooler GI-D56A HALO RGB', 1, N'Кулер', 2953, 14, 1)
INSERT [dbo].[Components] ([id], [Название], [Id единицы измерения], [Тип_материала], [Цена], [количество], [Id поставки]) VALUES (8, N'DeepCool Gammaxx L360 V2', 1, N'Водяная система охлаждения', 6347, 14, 1)
INSERT [dbo].[Components] ([id], [Название], [Id единицы измерения], [Тип_материала], [Цена], [количество], [Id поставки]) VALUES (9, N'1200W Corsair HX1200i', 1, N'Блок питания', 22697, 16, 1)
INSERT [dbo].[Components] ([id], [Название], [Id единицы измерения], [Тип_материала], [Цена], [количество], [Id поставки]) VALUES (10, N'1Tb Samsung 970 Evo Plus NVMe M.2', 1, N'Твердотельные диски SSD', 13590, 123, 1)
INSERT [dbo].[Components] ([id], [Название], [Id единицы измерения], [Тип_материала], [Цена], [количество], [Id поставки]) VALUES (11, N'test', 1, N'test', 1234, 1, 1)
SET IDENTITY_INSERT [dbo].[Components] OFF
GO
SET IDENTITY_INSERT [dbo].[computer_assembly] ON 

INSERT [dbo].[computer_assembly] ([Номер сборки], [Видеокарта], [Материнская плата], [Процессор], [Оперативная память], [Оперативная_память], [Жетский диск], [Куллер], [Система охлаждения], [Блок питания], [SSD], [Итоговая цена]) VALUES (11, N'Gigabyte GeForce RTX 3060 Ti GAMING OC PRO 8GB
', N'MSI MAG B550 TOMAHAWK AM4
', N'Intel Core i7 8700K OEM Coffee Lake 1151v2
', N'16Gb 3200MHz Kingston HyperX Fury 2x8Gb KIT
', N'DDR4 Corsair 8Gb 2133MHz
', N'2Tb SATA-III Western Digital Red
', N'PCcooler GI-D56A HALO RGB
', N'DeepCool Gammaxx L360 V2
', N'1200W Corsair HX1200i
', N'1Tb Samsung 970 Evo Plus NVMe M.2
', 150000)
INSERT [dbo].[computer_assembly] ([Номер сборки], [Видеокарта], [Материнская плата], [Процессор], [Оперативная память], [Оперативная_память], [Жетский диск], [Куллер], [Система охлаждения], [Блок питания], [SSD], [Итоговая цена]) VALUES (12, N'test', N'test', N'test', N'test', N'test', N'test', N'test', N'test', N'test', N'testt', 12345)
SET IDENTITY_INSERT [dbo].[computer_assembly] OFF
GO
SET IDENTITY_INSERT [dbo].[Order] ON 

INSERT [dbo].[Order] ([Id_заказа], [Номер заказа], [Дата], [Номер сборки], [id статуса]) VALUES (2, 1, CAST(N'2023-10-20' AS Date), 11, 2)
SET IDENTITY_INSERT [dbo].[Order] OFF
GO
SET IDENTITY_INSERT [dbo].[order_by_clients] ON 

INSERT [dbo].[order_by_clients] ([Номер заказа], [Пожелания], [Дата]) VALUES (1, N'test', CAST(N'2022-12-11' AS Date))
INSERT [dbo].[order_by_clients] ([Номер заказа], [Пожелания], [Дата]) VALUES (2, N'для монтажа', CAST(N'2021-12-10' AS Date))
SET IDENTITY_INSERT [dbo].[order_by_clients] OFF
GO
SET IDENTITY_INSERT [dbo].[Order_status] ON 

INSERT [dbo].[Order_status] ([id], [Название статуса]) VALUES (1, N'Готовится')
INSERT [dbo].[Order_status] ([id], [Название статуса]) VALUES (2, N'Готов')
SET IDENTITY_INSERT [dbo].[Order_status] OFF
GO
SET IDENTITY_INSERT [dbo].[Postavka_material] ON 

INSERT [dbo].[Postavka_material] ([id], [id производителя], [Дата], [Название], [Тип материала], [Количество]) VALUES (1, 1, CAST(N'2022-11-12' AS Date), N'Gigabyte GeForce RTX 3060 Ti GAMING OC PRO 8GB', N'Видеокарта', 10)
INSERT [dbo].[Postavka_material] ([id], [id производителя], [Дата], [Название], [Тип материала], [Количество]) VALUES (2, 1, NULL, N'test', N'test', 1)
SET IDENTITY_INSERT [dbo].[Postavka_material] OFF
GO
INSERT [dbo].[provider] ([id], [название], [Страна производитель]) VALUES (1, N'Рога и копыта', N'Япония')
INSERT [dbo].[provider] ([id], [название], [Страна производитель]) VALUES (2, N'У саныча', N'Россия')
INSERT [dbo].[provider] ([id], [название], [Страна производитель]) VALUES (3, N'Европаплюс', N'Америка')
GO
INSERT [dbo].[Role] ([Id], [Название роли]) VALUES (1, N'Администратор')
INSERT [dbo].[Role] ([Id], [Название роли]) VALUES (2, N'Мастер')
INSERT [dbo].[Role] ([Id], [Название роли]) VALUES (3, N'Менеджер')
GO
SET IDENTITY_INSERT [dbo].[unit_of_measurement] ON 

INSERT [dbo].[unit_of_measurement] ([id], [название]) VALUES (1, N'шт')
SET IDENTITY_INSERT [dbo].[unit_of_measurement] OFF
GO
INSERT [dbo].[Users] ([Id], [Username], [Password], [Role]) VALUES (1, N'Admin', N'Admin', 1)
INSERT [dbo].[Users] ([Id], [Username], [Password], [Role]) VALUES (2, N'Master', N'Master', 2)
INSERT [dbo].[Users] ([Id], [Username], [Password], [Role]) VALUES (3, N'Men', N'Men', 3)
INSERT [dbo].[Users] ([Id], [Username], [Password], [Role]) VALUES (1234, N'test', N'test', 3)
GO
ALTER TABLE [dbo].[Components]  WITH CHECK ADD  CONSTRAINT [FK_Components_Postavka_material] FOREIGN KEY([Id поставки])
REFERENCES [dbo].[Postavka_material] ([id])
GO
ALTER TABLE [dbo].[Components] CHECK CONSTRAINT [FK_Components_Postavka_material]
GO
ALTER TABLE [dbo].[Components]  WITH CHECK ADD  CONSTRAINT [FK_Components_unit_of_measurement] FOREIGN KEY([Id единицы измерения])
REFERENCES [dbo].[unit_of_measurement] ([id])
GO
ALTER TABLE [dbo].[Components] CHECK CONSTRAINT [FK_Components_unit_of_measurement]
GO
ALTER TABLE [dbo].[Order]  WITH CHECK ADD  CONSTRAINT [FK_Order_computer_assembly] FOREIGN KEY([Номер сборки])
REFERENCES [dbo].[computer_assembly] ([Номер сборки])
GO
ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_computer_assembly]
GO
ALTER TABLE [dbo].[Order]  WITH CHECK ADD  CONSTRAINT [FK_Order_order_by_clients] FOREIGN KEY([Номер заказа])
REFERENCES [dbo].[order_by_clients] ([Номер заказа])
GO
ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_order_by_clients]
GO
ALTER TABLE [dbo].[Order]  WITH CHECK ADD  CONSTRAINT [FK_Order_Order_status] FOREIGN KEY([id статуса])
REFERENCES [dbo].[Order_status] ([id])
GO
ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_Order_status]
GO
ALTER TABLE [dbo].[Postavka_material]  WITH CHECK ADD  CONSTRAINT [FK_Postavka_material_provider] FOREIGN KEY([id производителя])
REFERENCES [dbo].[provider] ([id])
GO
ALTER TABLE [dbo].[Postavka_material] CHECK CONSTRAINT [FK_Postavka_material_provider]
GO
ALTER TABLE [dbo].[Users]  WITH CHECK ADD  CONSTRAINT [FK_Users_Role] FOREIGN KEY([Role])
REFERENCES [dbo].[Role] ([Id])
GO
ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users_Role]
GO
USE [master]
GO
ALTER DATABASE [digital_world_8] SET  READ_WRITE 
GO
