EfStore.create.sql

USE [master]
GO

/****** Object:  Database [EfStore]    Script Date: 5/11/2017 1:09:16 AM ******/
CREATE DATABASE [EfStore]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'EfStore', FILENAME = N'D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\EfStore.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'EfStore_log', FILENAME = N'D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\EfStore_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [EfStore] SET COMPATIBILITY_LEVEL = 130
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [EfStore].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [EfStore] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [EfStore] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [EfStore] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [EfStore] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [EfStore] SET ARITHABORT OFF 
GO

ALTER DATABASE [EfStore] SET AUTO_CLOSE OFF 
GO

ALTER DATABASE [EfStore] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [EfStore] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [EfStore] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [EfStore] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [EfStore] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [EfStore] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [EfStore] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [EfStore] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [EfStore] SET  DISABLE_BROKER 
GO

ALTER DATABASE [EfStore] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [EfStore] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [EfStore] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [EfStore] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [EfStore] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [EfStore] SET READ_COMMITTED_SNAPSHOT OFF 
GO

ALTER DATABASE [EfStore] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [EfStore] SET RECOVERY FULL 
GO

ALTER DATABASE [EfStore] SET  MULTI_USER 
GO

ALTER DATABASE [EfStore] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [EfStore] SET DB_CHAINING OFF 
GO

ALTER DATABASE [EfStore] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO

ALTER DATABASE [EfStore] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO

ALTER DATABASE [EfStore] SET DELAYED_DURABILITY = DISABLED 
GO

ALTER DATABASE [EfStore] SET QUERY_STORE = OFF
GO

USE [EfStore]
GO

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
GO

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
GO

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
GO

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
GO

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
GO

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
GO

ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
GO

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
GO

ALTER DATABASE [EfStore] SET  READ_WRITE 
GO

USE [EfStore]
GO
/****** Object:  Table [dbo].[Category]    Script Date: 5/11/2017 1:08:08 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Category](
	[CategoryId] [int] IDENTITY(1,1) NOT NULL,
	[SystemId] [uniqueidentifier] NOT NULL,
	[Name] [varchar](20) NOT NULL,
	[Publish] [bit] NOT NULL,
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
	[CategoryId] 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].[CategorySubscription]    Script Date: 5/11/2017 1:08:08 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CategorySubscription](
	[SubscriptionId] [int] IDENTITY(1,1) NOT NULL,
	[Category] [uniqueidentifier] NOT NULL,
	[Product] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_CategorySubscription] PRIMARY KEY CLUSTERED 
(
	[SubscriptionId] 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].[CategoryTree]    Script Date: 5/11/2017 1:08:08 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CategoryTree](
	[TreeId] [int] IDENTITY(1,1) NOT NULL,
	[ParentCategory] [uniqueidentifier] NOT NULL,
	[ChildCategory] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_CategoryTree] PRIMARY KEY CLUSTERED 
(
	[TreeId] 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].[Product]    Script Date: 5/11/2017 1:08:08 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Product](
	[ProductId] [int] IDENTITY(1,1) NOT NULL,
	[SystemId] [uniqueidentifier] NOT NULL,
	[Name] [varchar](20) NOT NULL,
	[Description] [varchar](500) NOT NULL,
	[Publish] [bit] NOT NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
	[ProductId] 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].[ProductBundle]    Script Date: 5/11/2017 1:08:08 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProductBundle](
	[BundleId] [int] IDENTITY(1,1) NOT NULL,
	[RootProduct] [uniqueidentifier] NOT NULL,
	[RootSku] [varchar](10) NOT NULL,
	[AssociatedProduct] [uniqueidentifier] NOT NULL,
	[AssociatedSku] [varchar](10) NOT NULL,
	[AssociatedQuantity] [int] NOT NULL,
	[Price] [money] NULL,
 CONSTRAINT [PK_ProductBundle] PRIMARY KEY CLUSTERED 
(
	[BundleId] 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].[ProductSku]    Script Date: 5/11/2017 1:08:08 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProductSku](
	[SkuId] [int] IDENTITY(1,1) NOT NULL,
	[Product] [uniqueidentifier] NOT NULL,
	[SKU] [varchar](10) NOT NULL,
	[Price] [money] NOT NULL,
	[Bundled] [bit] NOT NULL,
 CONSTRAINT [PK_ProductSku] PRIMARY KEY CLUSTERED 
(
	[SkuId] 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].[User]    Script Date: 5/11/2017 1:08:08 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User](
	[UserId] [int] IDENTITY(1,1) NOT NULL,
	[Identity] [uniqueidentifier] NOT NULL,
	[Email] [varchar](20) NOT NULL,
	[Phone] [varchar](15) NOT NULL,
	[ContactPreference] [varchar](10) NULL,
	[Validated] [bit] NOT NULL,
	[InCollections] [bit] NOT NULL,
	[Inactive] [bit] NOT NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[UserId] 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].[UserBasket]    Script Date: 5/11/2017 1:08:08 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserBasket](
	[BasketId] [int] IDENTITY(1,1) NOT NULL,
	[User] [uniqueidentifier] NOT NULL,
	[Product] [uniqueidentifier] NOT NULL,
	[Sku] [varchar](10) NOT NULL,
	[Quantity] [int] NOT NULL,
 CONSTRAINT [PK_UserBasket] PRIMARY KEY CLUSTERED 
(
	[BasketId] 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:  Index [IX_Category]    Script Date: 5/11/2017 1:08:08 AM ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_Category] ON [dbo].[Category]
(
	[SystemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [IX_Product]    Script Date: 5/11/2017 1:08:08 AM ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_Product] ON [dbo].[Product]
(
	[SystemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [IX_User]    Script Date: 5/11/2017 1:08:08 AM ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_User] ON [dbo].[User]
(
	[Identity] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ProductBundle] ADD  CONSTRAINT [DF_ProductBundle_AssociatedQuantity]  DEFAULT ((1)) FOR [AssociatedQuantity]
GO
ALTER TABLE [dbo].[CategorySubscription]  WITH CHECK ADD  CONSTRAINT [FK_CategorySubscription_Category] FOREIGN KEY([Category])
REFERENCES [dbo].[Category] ([SystemId])
GO
ALTER TABLE [dbo].[CategorySubscription] CHECK CONSTRAINT [FK_CategorySubscription_Category]
GO
ALTER TABLE [dbo].[CategorySubscription]  WITH CHECK ADD  CONSTRAINT [FK_CategorySubscription_Product] FOREIGN KEY([Product])
REFERENCES [dbo].[Product] ([SystemId])
GO
ALTER TABLE [dbo].[CategorySubscription] CHECK CONSTRAINT [FK_CategorySubscription_Product]
GO
ALTER TABLE [dbo].[CategoryTree]  WITH CHECK ADD  CONSTRAINT [FK_CategoryTree_Category] FOREIGN KEY([ParentCategory])
REFERENCES [dbo].[Category] ([SystemId])
GO
ALTER TABLE [dbo].[CategoryTree] CHECK CONSTRAINT [FK_CategoryTree_Category]
GO
ALTER TABLE [dbo].[CategoryTree]  WITH CHECK ADD  CONSTRAINT [FK_CategoryTree_Category1] FOREIGN KEY([ChildCategory])
REFERENCES [dbo].[Category] ([SystemId])
GO
ALTER TABLE [dbo].[CategoryTree] CHECK CONSTRAINT [FK_CategoryTree_Category1]
GO
ALTER TABLE [dbo].[ProductBundle]  WITH CHECK ADD  CONSTRAINT [FK_ProductBundle_Product] FOREIGN KEY([RootProduct])
REFERENCES [dbo].[Product] ([SystemId])
GO
ALTER TABLE [dbo].[ProductBundle] CHECK CONSTRAINT [FK_ProductBundle_Product]
GO
ALTER TABLE [dbo].[ProductBundle]  WITH CHECK ADD  CONSTRAINT [FK_ProductBundle_Product1] FOREIGN KEY([AssociatedProduct])
REFERENCES [dbo].[Product] ([SystemId])
GO
ALTER TABLE [dbo].[ProductBundle] CHECK CONSTRAINT [FK_ProductBundle_Product1]
GO
ALTER TABLE [dbo].[ProductSku]  WITH CHECK ADD  CONSTRAINT [FK_ProductSku_Product] FOREIGN KEY([Product])
REFERENCES [dbo].[Product] ([SystemId])
GO
ALTER TABLE [dbo].[ProductSku] CHECK CONSTRAINT [FK_ProductSku_Product]
GO
ALTER TABLE [dbo].[UserBasket]  WITH CHECK ADD  CONSTRAINT [FK_UserBasket_Product] FOREIGN KEY([Product])
REFERENCES [dbo].[Product] ([SystemId])
GO
ALTER TABLE [dbo].[UserBasket] CHECK CONSTRAINT [FK_UserBasket_Product]
GO
ALTER TABLE [dbo].[UserBasket]  WITH CHECK ADD  CONSTRAINT [FK_UserBasket_User] FOREIGN KEY([User])
REFERENCES [dbo].[User] ([Identity])
GO
ALTER TABLE [dbo].[UserBasket] CHECK CONSTRAINT [FK_UserBasket_User]
GO