DrinkSoftSchema.sql

USE [master]
GO
/****** Object:  Database [DrinkSoftDB]    Script Date: 4/10/2018 9:55:38 PM ******/
CREATE DATABASE [DrinkSoftDB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Drinks', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\Drinks.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'Drinks_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\Drinks_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [DrinkSoftDB] SET COMPATIBILITY_LEVEL = 140
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [DrinkSoftDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [DrinkSoftDB] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [DrinkSoftDB] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [DrinkSoftDB] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [DrinkSoftDB] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [DrinkSoftDB] SET ARITHABORT OFF 
GO
ALTER DATABASE [DrinkSoftDB] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [DrinkSoftDB] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [DrinkSoftDB] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [DrinkSoftDB] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [DrinkSoftDB] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [DrinkSoftDB] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [DrinkSoftDB] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [DrinkSoftDB] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [DrinkSoftDB] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [DrinkSoftDB] SET  DISABLE_BROKER 
GO
ALTER DATABASE [DrinkSoftDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [DrinkSoftDB] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [DrinkSoftDB] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [DrinkSoftDB] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [DrinkSoftDB] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [DrinkSoftDB] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [DrinkSoftDB] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [DrinkSoftDB] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [DrinkSoftDB] SET  MULTI_USER 
GO
ALTER DATABASE [DrinkSoftDB] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [DrinkSoftDB] SET DB_CHAINING OFF 
GO
ALTER DATABASE [DrinkSoftDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [DrinkSoftDB] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [DrinkSoftDB] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [DrinkSoftDB] SET QUERY_STORE = OFF
GO
USE [DrinkSoftDB]
GO
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = ON;
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 MAXDOP = 0;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = 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
USE [DrinkSoftDB]
GO
/****** Object:  UserDefinedFunction [dbo].[fnIsVirgin]    Script Date: 4/10/2018 9:55:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[fnIsVirgin]
(
	-- Add the parameters for the function here
	@DrinkName nvarchar(50),
	@RecipeName nvarchar(50)
)
RETURNS bit
AS
BEGIN
	
	if exists
		(
			select DrinkName, RecipeName 
				from Drink d
				inner join recipe r on d.drinkid = r.drinkid
				inner join recipeingredient RI on RI.RecipeID = R.recipeID
				inner join Ingredient I on I.IngredientID = RI.IngredientID
				inner join IngredientComponent IC on I.IngredientID = IC.IngredientID
				inner join ParentIngredient [PI] on [PI].ParentIngredientID = IC.ParentIngredientID
			where 
			DrinkName = @DrinkName
			and 
			RecipeName = @RecipeName
			group by 
				DrinkName, 
				RecipeNAme
			having sum(cast([PI].IsAlcoholic as int)) = 0
		)
		return 1
	
	return 0

END
GO
/****** Object:  Table [dbo].[Drink]    Script Date: 4/10/2018 9:55:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Drink](
	[DrinkID] [int] IDENTITY(1,1) NOT NULL,
	[DrinkName] [nchar](50) NOT NULL,
 CONSTRAINT [PK_Drink] PRIMARY KEY CLUSTERED 
(
	[DrinkID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UC_DrinkName] UNIQUE NONCLUSTERED 
(
	[DrinkName] 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].[Ingredient]    Script Date: 4/10/2018 9:55:39 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Ingredient](
	[IngredientID] [int] IDENTITY(1,1) NOT NULL,
	[IngredientName] [nvarchar](50) NOT NULL,
	[StateID] [int] NULL,
	[IsCarbonated] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[IngredientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
	[IngredientName] 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].[IngredientComponent]    Script Date: 4/10/2018 9:55:39 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[IngredientComponent](
	[IngredientComponentID] [int] IDENTITY(1,1) NOT NULL,
	[IngredientID] [int] NULL,
	[ParentIngredientID] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[IngredientComponentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
	[IngredientID] ASC,
	[ParentIngredientID] 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].[ParentIngredient]    Script Date: 4/10/2018 9:55:39 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ParentIngredient](
	[ParentIngredientID] [int] IDENTITY(1,1) NOT NULL,
	[ParentIngredientName] [nvarchar](50) NULL,
	[IsAlcoholic] [bit] NULL,
	[IsFruity] [bit] NULL,
PRIMARY KEY CLUSTERED 
(
	[ParentIngredientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UC_ParentIngredientName_IsAlcoholic] UNIQUE NONCLUSTERED 
(
	[ParentIngredientName] ASC,
	[IsAlcoholic] 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].[PhysicalState]    Script Date: 4/10/2018 9:55:39 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PhysicalState](
	[StateID] [int] IDENTITY(1,1) NOT NULL,
	[State] [nvarchar](7) NULL,
 CONSTRAINT [PK_StateOfMatter] PRIMARY KEY CLUSTERED 
(
	[StateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
	[State] 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].[Recipe]    Script Date: 4/10/2018 9:55:39 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Recipe](
	[RecipeID] [int] IDENTITY(1,1) NOT NULL,
	[DrinkID] [int] NULL,
	[RecipeName] [nvarchar](50) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[RecipeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UC_RecipeName_DrinkID] UNIQUE NONCLUSTERED 
(
	[RecipeName] ASC,
	[DrinkID] 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].[RecipeIngredient]    Script Date: 4/10/2018 9:55:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RecipeIngredient](
	[DrinkIngredientID] [int] IDENTITY(1,1) NOT NULL,
	[RecipeID] [int] NOT NULL,
	[IngredientID] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[DrinkIngredientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UC_RecipeID_IngredientID] UNIQUE NONCLUSTERED 
(
	[RecipeID] ASC,
	[IngredientID] 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 ANSI_PADDING ON
GO
/****** Object:  Index [NonClusteredIndex-20180317-200432]    Script Date: 4/10/2018 9:55:40 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20180317-200432] ON [dbo].[Drink]
(
	[DrinkName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [NonClusteredIndex-20180317-200834]    Script Date: 4/10/2018 9:55:40 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20180317-200834] ON [dbo].[Ingredient]
(
	[IngredientName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [NonClusteredIndex-20180317-200845]    Script Date: 4/10/2018 9:55:40 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20180317-200845] ON [dbo].[Ingredient]
(
	[IsCarbonated] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [NonClusteredIndex-20180317-200816]    Script Date: 4/10/2018 9:55:40 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20180317-200816] ON [dbo].[IngredientComponent]
(
	[IngredientID] ASC,
	[ParentIngredientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [NonClusteredIndex-20180317-200921]    Script Date: 4/10/2018 9:55:40 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20180317-200921] ON [dbo].[ParentIngredient]
(
	[ParentIngredientName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [NonClusteredIndex-20180317-200930]    Script Date: 4/10/2018 9:55:40 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20180317-200930] ON [dbo].[ParentIngredient]
(
	[IsAlcoholic] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [NonClusteredIndex-20180317-200938]    Script Date: 4/10/2018 9:55:40 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20180317-200938] ON [dbo].[ParentIngredient]
(
	[IsFruity] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [NonClusteredIndex-20180317-200953]    Script Date: 4/10/2018 9:55:40 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20180317-200953] ON [dbo].[Recipe]
(
	[DrinkID] ASC,
	[RecipeName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [NonClusteredIndex-20180317-201009]    Script Date: 4/10/2018 9:55:40 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20180317-201009] ON [dbo].[RecipeIngredient]
(
	[RecipeID] ASC,
	[IngredientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Ingredient] ADD  DEFAULT ((0)) FOR [IsCarbonated]
GO
ALTER TABLE [dbo].[ParentIngredient] ADD  DEFAULT ((0)) FOR [IsAlcoholic]
GO
ALTER TABLE [dbo].[ParentIngredient] ADD  DEFAULT ((0)) FOR [IsFruity]
GO
ALTER TABLE [dbo].[Ingredient]  WITH CHECK ADD FOREIGN KEY([StateID])
REFERENCES [dbo].[PhysicalState] ([StateID])
GO
ALTER TABLE [dbo].[IngredientComponent]  WITH CHECK ADD  CONSTRAINT [FK_IngredientComponent_Ingredient] FOREIGN KEY([IngredientID])
REFERENCES [dbo].[Ingredient] ([IngredientID])
GO
ALTER TABLE [dbo].[IngredientComponent] CHECK CONSTRAINT [FK_IngredientComponent_Ingredient]
GO
ALTER TABLE [dbo].[IngredientComponent]  WITH CHECK ADD  CONSTRAINT [FK_IngredientComponent_ParentIngredient] FOREIGN KEY([ParentIngredientID])
REFERENCES [dbo].[ParentIngredient] ([ParentIngredientID])
GO
ALTER TABLE [dbo].[IngredientComponent] CHECK CONSTRAINT [FK_IngredientComponent_ParentIngredient]
GO
ALTER TABLE [dbo].[Recipe]  WITH CHECK ADD FOREIGN KEY([DrinkID])
REFERENCES [dbo].[Drink] ([DrinkID])
GO
ALTER TABLE [dbo].[RecipeIngredient]  WITH CHECK ADD FOREIGN KEY([IngredientID])
REFERENCES [dbo].[Ingredient] ([IngredientID])
GO
ALTER TABLE [dbo].[RecipeIngredient]  WITH CHECK ADD FOREIGN KEY([RecipeID])
REFERENCES [dbo].[Recipe] ([RecipeID])
GO
USE [master]
GO
ALTER DATABASE [DrinkSoftDB] SET  READ_WRITE 
GO