--USE [master]
--GO
/****** Object:  Database [kuchenbasar]    Script Date: 02.12.2014 15:25:12 ******/
--CREATE DATABASE [kuchenbasar]
-- CONTAINMENT = NONE
-- ON  PRIMARY 
--( NAME = N'kuchenbasar', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\kuchenbasar.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
-- LOG ON 
--( NAME = N'kuchenbasar_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\kuchenbasar_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
--GO
ALTER DATABASE [kuchenbasar] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [kuchenbasar].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [kuchenbasar] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [kuchenbasar] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [kuchenbasar] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [kuchenbasar] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [kuchenbasar] SET ARITHABORT OFF 
GO
ALTER DATABASE [kuchenbasar] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [kuchenbasar] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [kuchenbasar] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [kuchenbasar] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [kuchenbasar] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [kuchenbasar] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [kuchenbasar] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [kuchenbasar] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [kuchenbasar] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [kuchenbasar] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [kuchenbasar] SET  DISABLE_BROKER 
GO
ALTER DATABASE [kuchenbasar] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [kuchenbasar] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [kuchenbasar] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [kuchenbasar] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [kuchenbasar] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [kuchenbasar] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [kuchenbasar] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [kuchenbasar] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [kuchenbasar] SET  MULTI_USER 
GO
ALTER DATABASE [kuchenbasar] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [kuchenbasar] SET DB_CHAINING OFF 
GO
ALTER DATABASE [kuchenbasar] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [kuchenbasar] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO
USE [kuchenbasar]
GO
/****** Object:  Table [dbo].[kuchen]    Script Date: 02.12.2014 15:25:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF EXISTS (SELECT * FROM sys.views WHERE name='kuchenview')
   DROP VIEW kuchenview;
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name='kuchen')
   DROP TABLE kuchen;
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name='sorten')
   DROP TABLE sorten;
GO
CREATE TABLE [dbo].[kuchen](
	[id] [int] IDENTITY(0,1) NOT NULL,
	[anbieter] [varchar](50) NOT NULL,
	[sorte] [int] NOT NULL,
	[beschreibung] [varchar](50) NOT NULL,
	[abgabedatum] [datetime] NOT NULL,
	[anzahl] [int] NOT NULL,
	[diabetikergeeignet] [bit] NOT NULL,
	[letzterzugriff] [datetime] NOT NULL,
 CONSTRAINT [kuchen_PK] PRIMARY KEY CLUSTERED 
(
	[id] 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 OFF
GO
/****** Object:  Table [dbo].[sorten]    Script Date: 02.12.2014 15:25:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[sorten](
	[id] [int] NOT NULL,
	[backwarensorte] [varchar](50) NOT NULL,
 CONSTRAINT [sorten_PK] PRIMARY KEY CLUSTERED 
(
	[id] 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 OFF
GO
/****** Object:  View [dbo].[kuchenview]    Script Date: 02.12.2014 15:25:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[kuchenview]
AS
SELECT        dbo.kuchen.id, dbo.kuchen.anbieter, dbo.kuchen.beschreibung, dbo.sorten.backwarensorte, dbo.kuchen.abgabedatum, dbo.kuchen.anzahl, 
                         dbo.kuchen.diabetikergeeignet, dbo.kuchen.letzterzugriff
FROM            dbo.kuchen INNER JOIN
                         dbo.sorten ON dbo.kuchen.sorte = dbo.sorten.id
GO
ALTER TABLE [dbo].[kuchen] ADD  CONSTRAINT [DF_kuchen_abgabedatum]  DEFAULT (getdate()) FOR [abgabedatum]
GO
ALTER TABLE [dbo].[kuchen] ADD  CONSTRAINT [DF_kuchen_letzterzugriff]  DEFAULT (getdate()) FOR [letzterzugriff]
GO
SET IDENTITY_INSERT [dbo].[kuchen] ON 
INSERT [dbo].[kuchen] ([id], [anbieter], [sorte], [beschreibung], [anzahl], [diabetikergeeignet]) 
VALUES (0, N'noch nicht bekannt', 0, N'noch nicht bekannt', 0, 1 )
INSERT [dbo].[kuchen] ([id], [anbieter], [sorte], [beschreibung], [anzahl], [diabetikergeeignet]) 
VALUES (1, N'Frau Schmidt', 2, N'Kirschkuchen', 3, 1)
INSERT [dbo].[kuchen] ([id], [anbieter], [sorte], [beschreibung], [anzahl], [diabetikergeeignet]) 
VALUES (3, N'Cafe Wien', 3, N'Sachertorte', 12, 0)
INSERT [dbo].[kuchen] ([id], [anbieter], [sorte], [beschreibung], [anzahl], [diabetikergeeignet]) 
VALUES (2, N'Grubenbäcker', 2, N'Dominosteine', 6, 1)
INSERT [dbo].[kuchen] ([id], [anbieter], [sorte], [beschreibung], [anzahl], [diabetikergeeignet]) 
VALUES (8, N'Lidl', 1, N'Käsestangen', 1, 1)
SET IDENTITY_INSERT [dbo].[kuchen] OFF
INSERT [dbo].[sorten] ([id], [backwarensorte]) VALUES (0, N'noch nicht bekannt')
INSERT [dbo].[sorten] ([id], [backwarensorte]) VALUES (1, N'Brot und Brötchen')
INSERT [dbo].[sorten] ([id], [backwarensorte]) VALUES (2, N'Kuchen')
INSERT [dbo].[sorten] ([id], [backwarensorte]) VALUES (3, N'Torten')
GO
ALTER TABLE [dbo].[kuchen]  WITH CHECK ADD  CONSTRAINT [kuchen_sorte_FK] FOREIGN KEY([sorte])
REFERENCES [dbo].[sorten] ([id])
GO
ALTER TABLE [dbo].[kuchen] CHECK CONSTRAINT [kuchen_sorte_FK]
GO
USE [master]
GO
ALTER DATABASE [kuchenbasar] SET  READ_WRITE 
GO
