sewing_factory/BDSQL.sql

USE [sewing_factory]
GO
CREATE TABLE [dbo].[Color](
	[id_colors] [int] IDENTITY(1,1) NOT NULL,
	[Title] [nvarchar](50) NULL,
 CONSTRAINT [PK_Color] PRIMARY KEY CLUSTERED 
(
	[id_colors] 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].[Furnitura]    Script Date: 04.05.2023 13:30:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Furnitura](
	[id_fur] [int] IDENTITY(1,1) NOT NULL,
	[namefur] [nvarchar](50) NULL,
	[photo] [nvarchar](50) NULL,
	[countfur] [int] NULL,
 CONSTRAINT [PK_Furnitura] PRIMARY KEY CLUSTERED 
(
	[id_fur] 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].[Izdelie]    Script Date: 04.05.2023 13:30:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Izdelie](
	[id_izd] [int] IDENTITY(1,1) NOT NULL,
	[name_izd] [nvarchar](50) NULL,
	[length] [int] NULL,
	[width] [int] NULL,
 CONSTRAINT [PK_Izdelie] PRIMARY KEY CLUSTERED 
(
	[id_izd] 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].[Orders]    Script Date: 04.05.2023 13:30:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders](
	[id_order] [int] IDENTITY(1,1) NOT NULL,
	[id_izd] [int] NULL,
	[id_zak] [int] NULL,
	[id_men] [int] NULL,
	[id_tkan] [int] NULL,
	[id_fur] [int] NULL,
	[CountFur] [int] NULL,
	[CountIzd] [int] NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
	[id_order] 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].[Roles]    Script Date: 04.05.2023 13:30:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Roles](
	[id_Roles] [int] IDENTITY(1,1) NOT NULL,
	[Title] [nvarchar](50) NULL,
 CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED 
(
	[id_Roles] 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].[Tkan]    Script Date: 04.05.2023 13:30:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tkan](
	[id_Tkan] [int] IDENTITY(1,1) NOT NULL,
	[Title] [nvarchar](50) NULL,
	[id_color] [int] NULL,
	[Length] [int] NULL,
	[Width] [int] NULL,
	[Comment] [nvarchar](50) NULL,
	[NamePhoto] [nvarchar](50) NULL,
 CONSTRAINT [PK_Tkan] PRIMARY KEY CLUSTERED 
(
	[id_Tkan] 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]    Script Date: 04.05.2023 13:30:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
	[id_User] [int] IDENTITY(1,1) NOT NULL,
	[login] [nvarchar](50) NULL,
	[pass] [nvarchar](50) NULL,
	[Role] [int] NULL,
	[Surname] [nvarchar](50) NULL,
	[Name] [nvarchar](50) NULL,
	[Otch] [nvarchar](50) NULL,
	[Phone] [nvarchar](50) NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[id_User] 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
SET IDENTITY_INSERT [dbo].[Color] ON 

INSERT [dbo].[Color] ([id_colors], [Title]) VALUES (1, N'оранжевый')
INSERT [dbo].[Color] ([id_colors], [Title]) VALUES (2, N'фиолетовый')
INSERT [dbo].[Color] ([id_colors], [Title]) VALUES (3, N'сине-бирюзовый')
INSERT [dbo].[Color] ([id_colors], [Title]) VALUES (4, N'синий')
INSERT [dbo].[Color] ([id_colors], [Title]) VALUES (5, N'бежевый')
INSERT [dbo].[Color] ([id_colors], [Title]) VALUES (6, N'светло-серый')
INSERT [dbo].[Color] ([id_colors], [Title]) VALUES (7, N'белый')
INSERT [dbo].[Color] ([id_colors], [Title]) VALUES (8, N'экрю')
INSERT [dbo].[Color] ([id_colors], [Title]) VALUES (9, N'коричневый')
INSERT [dbo].[Color] ([id_colors], [Title]) VALUES (10, N'золотой')
INSERT [dbo].[Color] ([id_colors], [Title]) VALUES (11, N'золотистый')
INSERT [dbo].[Color] ([id_colors], [Title]) VALUES (12, N'бирюзовый')
INSERT [dbo].[Color] ([id_colors], [Title]) VALUES (13, N'кремовый')
SET IDENTITY_INSERT [dbo].[Color] OFF
GO
SET IDENTITY_INSERT [dbo].[Furnitura] ON 

INSERT [dbo].[Furnitura] ([id_fur], [namefur], [photo], [countfur]) VALUES (1, N'Бусинки 1', N'Бусинки 1.jpg', 60)
INSERT [dbo].[Furnitura] ([id_fur], [namefur], [photo], [countfur]) VALUES (2, N'Бусинки 2', N'Бусинки 2.jpg', 45)
INSERT [dbo].[Furnitura] ([id_fur], [namefur], [photo], [countfur]) VALUES (3, N'Бусинки 3', N'Бусинки 3.jpg', 150)
INSERT [dbo].[Furnitura] ([id_fur], [namefur], [photo], [countfur]) VALUES (4, N'Бусинки 4', N'Бусинки 4.jpg', 210)
INSERT [dbo].[Furnitura] ([id_fur], [namefur], [photo], [countfur]) VALUES (5, N'Бусинки 5', N'Бусинки 5.jpg', 115)
INSERT [dbo].[Furnitura] ([id_fur], [namefur], [photo], [countfur]) VALUES (6, N'Пряжка 1  ', N'Пряжка 1.jpg', 432)
INSERT [dbo].[Furnitura] ([id_fur], [namefur], [photo], [countfur]) VALUES (7, N'Пряжка 2  ', N'Пряжка 2.jpg', 54)
INSERT [dbo].[Furnitura] ([id_fur], [namefur], [photo], [countfur]) VALUES (8, N'Пряжка 3  ', N'Пряжка 3.jpg', 542)
INSERT [dbo].[Furnitura] ([id_fur], [namefur], [photo], [countfur]) VALUES (9, N'Пряжка 4  ', N'Пряжка 4.jpg', 458)
INSERT [dbo].[Furnitura] ([id_fur], [namefur], [photo], [countfur]) VALUES (10, N'Пуговицы 1', N'Пуговицы 1.jpg', 54)
INSERT [dbo].[Furnitura] ([id_fur], [namefur], [photo], [countfur]) VALUES (11, N'Пуговицы 2', N'Пуговицы 2.jpg', 354)
INSERT [dbo].[Furnitura] ([id_fur], [namefur], [photo], [countfur]) VALUES (12, N'Пуговицы 3', N'Пуговицы 3.jpg', 78)
INSERT [dbo].[Furnitura] ([id_fur], [namefur], [photo], [countfur]) VALUES (13, N'Пуговицы 4', N'Пуговицы 4.jpg', 576)
INSERT [dbo].[Furnitura] ([id_fur], [namefur], [photo], [countfur]) VALUES (14, N'Пуговицы 5', N'Пуговицы 5.jpg', 43)
SET IDENTITY_INSERT [dbo].[Furnitura] OFF
GO
SET IDENTITY_INSERT [dbo].[Izdelie] ON 

INSERT [dbo].[Izdelie] ([id_izd], [name_izd], [length], [width]) VALUES (1, N'Наволочка', 60, 70)
INSERT [dbo].[Izdelie] ([id_izd], [name_izd], [length], [width]) VALUES (2, N'Полотенце', 70, 30)
INSERT [dbo].[Izdelie] ([id_izd], [name_izd], [length], [width]) VALUES (3, N'Салфетка', 30, 30)
INSERT [dbo].[Izdelie] ([id_izd], [name_izd], [length], [width]) VALUES (4, N'Простыня', 190, 150)
INSERT [dbo].[Izdelie] ([id_izd], [name_izd], [length], [width]) VALUES (5, N'Пододеяльник', 200, 150)
SET IDENTITY_INSERT [dbo].[Izdelie] OFF
GO
SET IDENTITY_INSERT [dbo].[Orders] ON 

INSERT [dbo].[Orders] ([id_order], [id_izd], [id_zak], [id_men], [id_tkan], [id_fur], [CountFur], [CountIzd]) VALUES (1, 1, 1, 2, 10, 12, 2, 1)
INSERT [dbo].[Orders] ([id_order], [id_izd], [id_zak], [id_men], [id_tkan], [id_fur], [CountFur], [CountIzd]) VALUES (2, 2, 1, 2, 11, 11, 3, 2)
INSERT [dbo].[Orders] ([id_order], [id_izd], [id_zak], [id_men], [id_tkan], [id_fur], [CountFur], [CountIzd]) VALUES (3, 5, 1, 5, 9, 10, 1, 1)
INSERT [dbo].[Orders] ([id_order], [id_izd], [id_zak], [id_men], [id_tkan], [id_fur], [CountFur], [CountIzd]) VALUES (4, 2, 4, 2, 4, 10, 1, 1)
INSERT [dbo].[Orders] ([id_order], [id_izd], [id_zak], [id_men], [id_tkan], [id_fur], [CountFur], [CountIzd]) VALUES (5, 3, 4, 2, 4, 7, 2, 1)
INSERT [dbo].[Orders] ([id_order], [id_izd], [id_zak], [id_men], [id_tkan], [id_fur], [CountFur], [CountIzd]) VALUES (6, 3, 1, 2, 16, 1, 0, 1)
INSERT [dbo].[Orders] ([id_order], [id_izd], [id_zak], [id_men], [id_tkan], [id_fur], [CountFur], [CountIzd]) VALUES (7, 3, 4, 2, 16, 1, 2, 2)
INSERT [dbo].[Orders] ([id_order], [id_izd], [id_zak], [id_men], [id_tkan], [id_fur], [CountFur], [CountIzd]) VALUES (8, 5, 4, 2, 15, 10, 2, 2)
INSERT [dbo].[Orders] ([id_order], [id_izd], [id_zak], [id_men], [id_tkan], [id_fur], [CountFur], [CountIzd]) VALUES (9, 1, 1, 2, 1, 1, 5, 5)
INSERT [dbo].[Orders] ([id_order], [id_izd], [id_zak], [id_men], [id_tkan], [id_fur], [CountFur], [CountIzd]) VALUES (10, 1, 1, 2, 1, 1, 5, 5)
INSERT [dbo].[Orders] ([id_order], [id_izd], [id_zak], [id_men], [id_tkan], [id_fur], [CountFur], [CountIzd]) VALUES (11, 1, 1, 2, 1, 1, 5, 5)
INSERT [dbo].[Orders] ([id_order], [id_izd], [id_zak], [id_men], [id_tkan], [id_fur], [CountFur], [CountIzd]) VALUES (12, 1, 1, 2, 1, 1, 5, 5)
INSERT [dbo].[Orders] ([id_order], [id_izd], [id_zak], [id_men], [id_tkan], [id_fur], [CountFur], [CountIzd]) VALUES (13, 2, 1, 2, 2, 2, 10, 10)
INSERT [dbo].[Orders] ([id_order], [id_izd], [id_zak], [id_men], [id_tkan], [id_fur], [CountFur], [CountIzd]) VALUES (14, 1, 1, 2, 1, 1, 8, 8)
SET IDENTITY_INSERT [dbo].[Orders] OFF
GO
SET IDENTITY_INSERT [dbo].[Roles] ON 

INSERT [dbo].[Roles] ([id_Roles], [Title]) VALUES (1, N'заказчик')
INSERT [dbo].[Roles] ([id_Roles], [Title]) VALUES (2, N'менеджер')
INSERT [dbo].[Roles] ([id_Roles], [Title]) VALUES (3, N'кладовщик')
SET IDENTITY_INSERT [dbo].[Roles] OFF
GO
SET IDENTITY_INSERT [dbo].[Tkan] ON 

INSERT [dbo].[Tkan] ([id_Tkan], [Title], [id_color], [Length], [Width], [Comment], [NamePhoto]) VALUES (1, N'Ткань Шарлотта', 1, 200, 100, N'', N'Шарлотта.jpg')
INSERT [dbo].[Tkan] ([id_Tkan], [Title], [id_color], [Length], [Width], [Comment], [NamePhoto]) VALUES (2, N'Тюль Семечки', 2, 200, 100, N'', N'Семечки.jpg')
INSERT [dbo].[Tkan] ([id_Tkan], [Title], [id_color], [Length], [Width], [Comment], [NamePhoto]) VALUES (3, N'Тюль Классика', 3, 280, 100, N'', N'Классика.jpg')
INSERT [dbo].[Tkan] ([id_Tkan], [Title], [id_color], [Length], [Width], [Comment], [NamePhoto]) VALUES (4, N'Тюль Ажур', 4, 145, 100, N'', N'Ажур.jpg')
INSERT [dbo].[Tkan] ([id_Tkan], [Title], [id_color], [Length], [Width], [Comment], [NamePhoto]) VALUES (5, N'Тюль  Ветки', 5, 145, 100, N'', N'Ветки.jpg')
INSERT [dbo].[Tkan] ([id_Tkan], [Title], [id_color], [Length], [Width], [Comment], [NamePhoto]) VALUES (6, N'Тюль Вензель', 6, 145, 100, N'', N'Вензель.jpg')
INSERT [dbo].[Tkan] ([id_Tkan], [Title], [id_color], [Length], [Width], [Comment], [NamePhoto]) VALUES (7, N'Тюль Пузырьки', 7, 280, 100, N'', N'Пузырьки.jpg')
INSERT [dbo].[Tkan] ([id_Tkan], [Title], [id_color], [Length], [Width], [Comment], [NamePhoto]) VALUES (8, N'Тюль Ветка', 8, 280, 100, N'', N'Ветка.jpg')
INSERT [dbo].[Tkan] ([id_Tkan], [Title], [id_color], [Length], [Width], [Comment], [NamePhoto]) VALUES (9, N'Ткань Эмили', 9, 280, 100, N'', N'Эмили.jpg')
INSERT [dbo].[Tkan] ([id_Tkan], [Title], [id_color], [Length], [Width], [Comment], [NamePhoto]) VALUES (10, N'Ткань Робина', 5, 280, 100, N'', N'Робина.jpg')
INSERT [dbo].[Tkan] ([id_Tkan], [Title], [id_color], [Length], [Width], [Comment], [NamePhoto]) VALUES (11, N'Ткань Амор', 10, 145, 150, N'', N'Амор.jpg')
INSERT [dbo].[Tkan] ([id_Tkan], [Title], [id_color], [Length], [Width], [Comment], [NamePhoto]) VALUES (12, N'Ткань Шалимар', 5, 290, 150, N'', N'Шалимар.jpg')
INSERT [dbo].[Tkan] ([id_Tkan], [Title], [id_color], [Length], [Width], [Comment], [NamePhoto]) VALUES (13, N'Тюль Балу', 7, 290, 150, N'', N'Балу.jpg')
INSERT [dbo].[Tkan] ([id_Tkan], [Title], [id_color], [Length], [Width], [Comment], [NamePhoto]) VALUES (14, N'Ткань Жаккард', 11, 290, 150, N'', N'Жаккард.jpg')
INSERT [dbo].[Tkan] ([id_Tkan], [Title], [id_color], [Length], [Width], [Comment], [NamePhoto]) VALUES (15, N'Ткань Цветы', 12, 290, 150, N'', N'Цветы.jpg')
INSERT [dbo].[Tkan] ([id_Tkan], [Title], [id_color], [Length], [Width], [Comment], [NamePhoto]) VALUES (16, N'Ткань Завитки', 13, 280, 150, N'', N'Завитки.jpg')
SET IDENTITY_INSERT [dbo].[Tkan] OFF
GO
SET IDENTITY_INSERT [dbo].[Users] ON 

INSERT [dbo].[Users] ([id_User], [login], [pass], [Role], [Surname], [Name], [Otch], [Phone]) VALUES (1, N'z', N'z', 1, N'Иванов', N'Иван', N'Ильич', N'+7(915) 158-98-45')
INSERT [dbo].[Users] ([id_User], [login], [pass], [Role], [Surname], [Name], [Otch], [Phone]) VALUES (2, N'm', N'm', 2, N'Петров', N'Андрей', N'Егорович', N'+7(905) 543-45-11')
INSERT [dbo].[Users] ([id_User], [login], [pass], [Role], [Surname], [Name], [Otch], [Phone]) VALUES (3, N'k', N'k', 3, N'Андреев', N'Семен', N'Степанович', N'+7(910) 324-65-23')
INSERT [dbo].[Users] ([id_User], [login], [pass], [Role], [Surname], [Name], [Otch], [Phone]) VALUES (4, N'stepanova', N'sgf432', 1, N'Степанова', N'Ирина', N'Петровна', N'+7(952) 543-67-43')
INSERT [dbo].[Users] ([id_User], [login], [pass], [Role], [Surname], [Name], [Otch], [Phone]) VALUES (5, N'ilyina', N'3245bghfx', 2, N'Ильина', N'Анна', N'Петровна', N'+7(905) 678-65-21')
INSERT [dbo].[Users] ([id_User], [login], [pass], [Role], [Surname], [Name], [Otch], [Phone]) VALUES (6, N'egorov', N'sdgf435', 3, N'Егоров', N'Петр', N'Андреевич', N'+7(910) 122-54-23')
INSERT [dbo].[Users] ([id_User], [login], [pass], [Role], [Surname], [Name], [Otch], [Phone]) VALUES (7, N'vasilyeva', N'gfbet345', 1, N'Васильева', N'Елена', N'Алексеевна', N'+7(915) 677-99-12')
INSERT [dbo].[Users] ([id_User], [login], [pass], [Role], [Surname], [Name], [Otch], [Phone]) VALUES (13, N'zb', N'zz&&%GG5', 1, N'hgjh', N'hgj', N'h', N'+7(000) 000-0000')
SET IDENTITY_INSERT [dbo].[Users] OFF
GO
ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Furnitura] FOREIGN KEY([id_fur])
REFERENCES [dbo].[Furnitura] ([id_fur])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Furnitura]
GO
ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Izdelie] FOREIGN KEY([id_izd])
REFERENCES [dbo].[Izdelie] ([id_izd])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Izdelie]
GO
ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Tkan] FOREIGN KEY([id_tkan])
REFERENCES [dbo].[Tkan] ([id_Tkan])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Tkan]
GO
ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Users1] FOREIGN KEY([id_men])
REFERENCES [dbo].[Users] ([id_User])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Users1]
GO
ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Users2] FOREIGN KEY([id_zak])
REFERENCES [dbo].[Users] ([id_User])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Users2]
GO
ALTER TABLE [dbo].[Tkan]  WITH CHECK ADD  CONSTRAINT [FK_Tkan_Color] FOREIGN KEY([id_color])
REFERENCES [dbo].[Color] ([id_colors])
GO
ALTER TABLE [dbo].[Tkan] CHECK CONSTRAINT [FK_Tkan_Color]
GO
ALTER TABLE [dbo].[Users]  WITH CHECK ADD  CONSTRAINT [FK_Users_Roles] FOREIGN KEY([Role])
REFERENCES [dbo].[Roles] ([id_Roles])
GO
ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users_Roles]
GO