test-1-app 1.0.0

CREATE DATABASE [Shoe7] GO

USE [Shoe7] GO

/****** Object: Table [dbo].[Category] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Category]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] nvarchar NOT NULL, CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) ON [PRIMARY] GO

/****** Object: Table [dbo].[OrderStatus] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[OrderStatus]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] nvarchar NOT NULL, CONSTRAINT [PK_OrderStatus] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) ON [PRIMARY] GO

/****** Object: Table [dbo].[PickUpPoint] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[PickUpPoint]( [Id] [int] IDENTITY(1,1) NOT NULL, [PostCode] nvarchar NOT NULL, [City] nvarchar NOT NULL, [Street] nvarchar NOT NULL, [Building] nvarchar NULL, CONSTRAINT [PK_PickUpPoint] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) ON [PRIMARY] GO

/****** Object: Table [dbo].[Producer] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Producer]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] nvarchar NOT NULL, CONSTRAINT [PK_Producer] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) ON [PRIMARY] GO

/****** Object: Table [dbo].[Provider] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Provider]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] nvarchar NOT NULL, CONSTRAINT [PK_Provider] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) ON [PRIMARY] GO

/****** Object: Table [dbo].[Role] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Role]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] nvarchar NOT NULL, CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) ON [PRIMARY] GO

/****** Object: Table [dbo].[Unit] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Unit]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] nvarchar NOT NULL, CONSTRAINT [PK_Unit] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) ON [PRIMARY] GO

/****** Object: Table [dbo].[User] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[User]( [Id] [int] IDENTITY(1,1) NOT NULL, [Surname] nvarchar NOT NULL, [Name] nvarchar NOT NULL, [Patronymic] nvarchar NULL, [Login] nvarchar NOT NULL, [Password] nvarchar NOT NULL, [RoleId] [int] NOT NULL, CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) ON [PRIMARY] GO

/****** Object: Table [dbo].[Product] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Product]( [Id] [int] IDENTITY(1,1) NOT NULL, [Article] nvarchar NOT NULL, [Name] nvarchar NOT NULL, [UnitId] [int] NOT NULL, [Price] [money] NOT NULL, [ProviderId] [int] NOT NULL, [ProducerId] [int] NOT NULL, [CategoryId] [int] NOT NULL, [Discount] [decimal](4, 2) NOT NULL, [AmoutInStock] [decimal](6, 2) NOT NULL, [Description] nvarchar NULL, [Photo] nvarchar NOT NULL, CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO

/****** Object: Table [dbo].[Order] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Order]( [Id] [int] IDENTITY(1,1) NOT NULL, [CreationDate] [date] NOT NULL, [DeliveryDate] [date] NOT NULL, [PickUpPointId] [int] NOT NULL, [UserId] [int] NOT NULL, [ReceiptCode] nvarchar NOT NULL, [StatusId] [int] NOT NULL, CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) ON [PRIMARY] GO

/****** Object: Table [dbo].[ProductOrder] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ProductOrder]( [Id] [int] IDENTITY(1,1) NOT NULL, [OrderId] [int] NOT NULL, [ProductId] [int] NOT NULL, [Amount] [int] NOT NULL, CONSTRAINT [PK_ProductOrder] PRIMARY KEY CLUSTERED ( [Id] ASC ) ) ON [PRIMARY] GO

-- Создание внешних ключей (Связи между таблицами)

ALTER TABLE [dbo].[User] WITH CHECK ADD CONSTRAINT [FK_User_Role] FOREIGN KEY([RoleId]) REFERENCES [dbo].[Role] ([Id]) GO ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_Role] GO

ALTER TABLE [dbo].[Product] WITH CHECK ADD CONSTRAINT [FK_Product_Category] FOREIGN KEY([CategoryId]) REFERENCES [dbo].[Category] ([Id]) GO ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Category] GO

ALTER TABLE [dbo].[Product] WITH CHECK ADD CONSTRAINT [FK_Product_Producer] FOREIGN KEY([ProducerId]) REFERENCES [dbo].[Producer] ([Id]) GO ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Producer] GO

ALTER TABLE [dbo].[Product] WITH CHECK ADD CONSTRAINT [FK_Product_Provider] FOREIGN KEY([ProviderId]) REFERENCES [dbo].[Provider] ([Id]) GO ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Provider] GO

ALTER TABLE [dbo].[Product] WITH CHECK ADD CONSTRAINT [FK_Product_Unit] FOREIGN KEY([UnitId]) REFERENCES [dbo].[Unit] ([Id]) GO ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Unit] GO

ALTER TABLE [dbo].[Order] WITH CHECK ADD CONSTRAINT [FK_Order_OrderStatus] FOREIGN KEY([StatusId]) REFERENCES [dbo].[OrderStatus] ([Id]) GO ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_OrderStatus] GO

ALTER TABLE [dbo].[Order] WITH CHECK ADD CONSTRAINT [FK_Order_PickUpPoint] FOREIGN KEY([PickUpPointId]) REFERENCES [dbo].[PickUpPoint] ([Id]) GO ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_PickUpPoint] GO

ALTER TABLE [dbo].[Order] WITH CHECK ADD CONSTRAINT [FK_Order_User] FOREIGN KEY([UserId]) REFERENCES [dbo].[User] ([Id]) GO ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_User] GO

ALTER TABLE [dbo].[ProductOrder] WITH CHECK ADD CONSTRAINT [FK_ProductOrder_Order] FOREIGN KEY([OrderId]) REFERENCES [dbo].[Order] ([Id]) GO ALTER TABLE [dbo].[ProductOrder] CHECK CONSTRAINT [FK_ProductOrder_Order] GO

ALTER TABLE [dbo].[ProductOrder] WITH CHECK ADD CONSTRAINT [FK_ProductOrder_Product] FOREIGN KEY([ProductId]) REFERENCES [dbo].[Product] ([Id]) GO ALTER TABLE [dbo].[ProductOrder] CHECK CONSTRAINT [FK_ProductOrder_Product] GO