USE [master]
GO

/****** Object:  Database [BDS420]    Script Date: 7/2/2013 12:27:32 AM ******/
/*DROP DATABASE [BDS420] */
GO

/****** Object:  Database [BDS420]    Script Date: 7/2/2013 12:27:32 AM ******/
CREATE DATABASE [BDS420]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'BDS420', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\BDS420.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'BDS420_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\BDS420_log.ldf' , SIZE = 3136KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [BDS420] SET COMPATIBILITY_LEVEL = 100
GO

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

ALTER DATABASE [BDS420] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [BDS420] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [BDS420] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [BDS420] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [BDS420] SET ARITHABORT OFF 
GO

ALTER DATABASE [BDS420] SET AUTO_CLOSE ON 
GO

ALTER DATABASE [BDS420] SET AUTO_CREATE_STATISTICS ON 
GO

ALTER DATABASE [BDS420] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [BDS420] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [BDS420] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [BDS420] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [BDS420] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [BDS420] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [BDS420] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [BDS420] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [BDS420] SET  DISABLE_BROKER 
GO

ALTER DATABASE [BDS420] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [BDS420] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [BDS420] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [BDS420] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [BDS420] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [BDS420] SET READ_COMMITTED_SNAPSHOT OFF 
GO

ALTER DATABASE [BDS420] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [BDS420] SET RECOVERY SIMPLE 
GO

ALTER DATABASE [BDS420] SET  MULTI_USER 
GO

ALTER DATABASE [BDS420] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [BDS420] SET DB_CHAINING OFF 
GO

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

ALTER DATABASE [BDS420] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO

ALTER DATABASE [BDS420] SET  READ_WRITE 
GO

USE [BDS420]
GO

/****** Object:  Table [dbo].[Cardinaliteit]    Script Date: 7/2/2013 12:36:06 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Cardinaliteit](
	[CardMin] [nchar](10) NOT NULL,
	[CardMax] [nchar](10) NOT NULL,
 CONSTRAINT [PK_Cardinaliteit] PRIMARY KEY CLUSTERED 
(
	[CardMin] ASC,
	[CardMax] 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
/***END*** Object:  Table [dbo].[Cardinaliteit]    Script Date: 7/2/2013 12:36:06 AM ******/

/****** Object:  Table [dbo].[Conformiteiten]    Script Date: 7/2/2013 12:36:27 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Conformiteiten](
	[Conformiteit] [char](2) NOT NULL,
	[Omschrijving] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_Conformiteiten] PRIMARY KEY CLUSTERED 
(
	[Conformiteit] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
/***END*** Object:  Table [dbo].[Conformiteiten]    Script Date: 7/2/2013 12:36:27 AM ******/

/****** Object:  Table [dbo].[Groepen]    Script Date: 7/2/2013 12:38:22 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Groepen](
	[GroepID] [char](4) NOT NULL,
	[Groep] [nvarchar](250) NOT NULL,
	[GroepType] [nchar](10) NULL,
	[Hierarchie] [char](4) NULL,
	[CardMin] [nchar](10) NULL,
	[CardMax] [nchar](10) NULL,
	[Conformiteit] [char](2) NULL,
	[Toelichting] [nvarchar](max) NULL,
	[Versie] [int] NOT NULL,
	[Sortering] [int] NULL,
	[Actief] [bit] NOT NULL,
 CONSTRAINT [PK_Groepen] PRIMARY KEY CLUSTERED 
(
	[GroepID] 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

ALTER TABLE [dbo].[Groepen]  WITH CHECK ADD  CONSTRAINT [FK_Groepen_Cardinaliteit] FOREIGN KEY([CardMin], [CardMax])
REFERENCES [dbo].[Cardinaliteit] ([CardMin], [CardMax])
GO

ALTER TABLE [dbo].[Groepen] CHECK CONSTRAINT [FK_Groepen_Cardinaliteit]
GO
/***END*** Object:  Table [dbo].[Groepen]    Script Date: 7/2/2013 12:38:22 AM ******/

/****** Object:  Table [dbo].[Rubrieken]    Script Date: 7/2/2013 12:39:13 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Rubrieken](
	[RubriekID] [char](4) NOT NULL,
	[Rubriek] [nvarchar](250) NOT NULL,
	[CardMin] [nchar](10) NULL,
	[CardMax] [nchar](10) NULL,
	[Conformiteit] [char](2) NULL,
	[Toelichting] [nvarchar](max) NULL,
	[Contactgebonden] [bit] NOT NULL,
	[Versie] [int] NOT NULL,
	[Sortering] [int] NULL,
	[Actief] [bit] NOT NULL,
 CONSTRAINT [PK_Rubrieken] PRIMARY KEY CLUSTERED 
(
	[RubriekID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Rubrieken]  WITH CHECK ADD  CONSTRAINT [FK_Rubrieken_Cardinaliteit] FOREIGN KEY([CardMin], [CardMax])
REFERENCES [dbo].[Cardinaliteit] ([CardMin], [CardMax])
GO

ALTER TABLE [dbo].[Rubrieken] CHECK CONSTRAINT [FK_Rubrieken_Cardinaliteit]
GO
/***END*** Object:  Table [dbo].[Rubrieken]    Script Date: 7/2/2013 12:39:13 AM ******/

/****** Object:  Table [dbo].[WaardenDomeinen]    Script Date: 7/2/2013 12:40:24 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[WaardenDomeinen](
	[WaardenDomeinID] [char](5) NOT NULL,
	[WaardenDomein] [nvarchar](250) NULL,
	[Type] [nvarchar](8) NULL,
	[Oorsprong] [nvarchar](50) NULL,
	[Eenheid] [nvarchar](16) NULL,
	[MinLengte] [numeric](18, 0) NULL,
	[MaxLengte] [numeric](18, 0) NULL,
	[MinWaarde] [nchar](10) NULL,
	[MaxWaarde] [nchar](10) NULL,
	[Vast] [nvarchar](50) NULL,
	[Standaard] [nvarchar](50) NULL,
	[Tijdstempelprecisie] [nchar](10) NULL,
	[Decimalen] [nchar](10) NULL,
	[Toelichting] [nvarchar](max) NULL,
	[Versie] [int] NOT NULL,
	[ValueSetOIDBDS] [nvarchar](128) NULL,
	[ValueSetOIDHL7] [nvarchar](128) NULL,
	[CodeSystemOIDHL7] [nvarchar](128) NULL,
	[IdentificationOIDHL7] [nvarchar](128) NULL,
	[Actief] [bit] NOT NULL,
 CONSTRAINT [PK_WaardenDomeinen] PRIMARY KEY CLUSTERED 
(
	[WaardenDomeinID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
/***END*** Object:  Table [dbo].[WaardenDomeinen]    Script Date: 7/2/2013 12:40:24 AM ******/

/****** Object:  Table [dbo].[Waarden]    Script Date: 7/2/2013 12:39:56 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Waarden](
	[WaardeID] [bigint] NOT NULL,
	[WaardenDomeinID] [char](5) NOT NULL,
	[Waarde] [nvarchar](250) NOT NULL,
	[WaardeCodeBDS] [nchar](10) NULL,
	[WaardeCodeHL7] [nchar](50) NULL,
	[Synoniem] [nvarchar](250) NULL,
	[Sortering] [int] NOT NULL,
	[CodeSystemOIDBDS] [nvarchar](128) NULL,
	[CodeSystemOIDHL7] [nvarchar](128) NULL,
	[Toelichting] [nvarchar](max) NULL,
	[Actief] [bit] NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Waarden]  WITH NOCHECK ADD  CONSTRAINT [FK_Waarden_WaardenDomeinen] FOREIGN KEY([WaardenDomeinID])
REFERENCES [dbo].[WaardenDomeinen] ([WaardenDomeinID])
GO

ALTER TABLE [dbo].[Waarden] CHECK CONSTRAINT [FK_Waarden_WaardenDomeinen]
GO
/***END*** Object:  Table [dbo].[Waarden]    Script Date: 7/2/2013 12:39:56 AM ******/

/****** Object:  Table [dbo].[Berichten]    Script Date: 7/2/2013 12:31:45 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Berichten](
	[BerichtID] [nvarchar](128) NOT NULL,
	[Bericht] [nvarchar](max) NOT NULL,
	[Richting] [nchar](10) NOT NULL,
	[HL7Model] [nvarchar](max) NOT NULL,
	[AntwoordBerichtID] [nvarchar](128) NULL,
	[Actief] [bit] NOT NULL,
 CONSTRAINT [PK_Berichten] PRIMARY KEY CLUSTERED 
(
	[BerichtID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/***END*** Object:  Table [dbo].[Berichten]    Script Date: 7/2/2013 12:31:45 AM ******/

/****** Object:  Table [dbo].[Elementen]    Script Date: 7/2/2013 12:37:26 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Elementen](
	[BDSnummer] [int] NOT NULL,
	[BDSelement] [nvarchar](250) NOT NULL,
	[WaardenDomeinID] [char](5) NOT NULL,
	[RubriekID] [char](4) NOT NULL,
	[GroepID] [char](4) NULL,
	[GroepIDPresentatie] [char](4) NULL,
	[Tonen] [bit] NOT NULL,
	[BDSelementPresentatie] [nvarchar](250) NULL,
	[CardMin] [nchar](10) NULL,
	[CardMax] [nchar](10) NULL,
	[Conformiteit] [char](2) NULL,
	[Condities] [nvarchar](250) NULL,
	[Toelichting] [nvarchar](max) NULL,
	[Synoniem] [nvarchar](250) NULL,
	[TechOpmerking] [nvarchar](max) NULL,
	[Versie] [int] NOT NULL,
	[Sortering] [int] NULL,
	[Actief] [bit] NOT NULL,
 CONSTRAINT [PK_Elementen] PRIMARY KEY CLUSTERED 
(
	[BDSnummer] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Elementen]  WITH CHECK ADD  CONSTRAINT [FK_Elementen_Cardinaliteit] FOREIGN KEY([CardMin], [CardMax])
REFERENCES [dbo].[Cardinaliteit] ([CardMin], [CardMax])
GO

ALTER TABLE [dbo].[Elementen] CHECK CONSTRAINT [FK_Elementen_Cardinaliteit]
GO

ALTER TABLE [dbo].[Elementen]  WITH CHECK ADD  CONSTRAINT [FK_Elementen_Conformiteiten] FOREIGN KEY([Conformiteit])
REFERENCES [dbo].[Conformiteiten] ([Conformiteit])
GO

ALTER TABLE [dbo].[Elementen] CHECK CONSTRAINT [FK_Elementen_Conformiteiten]
GO

ALTER TABLE [dbo].[Elementen]  WITH CHECK ADD  CONSTRAINT [FK_Elementen_Groepen] FOREIGN KEY([GroepID])
REFERENCES [dbo].[Groepen] ([GroepID])
GO

ALTER TABLE [dbo].[Elementen] CHECK CONSTRAINT [FK_Elementen_Groepen]
GO

ALTER TABLE [dbo].[Elementen]  WITH CHECK ADD  CONSTRAINT [FK_Elementen_GroepenPresentatie] FOREIGN KEY([GroepIDPresentatie])
REFERENCES [dbo].[Groepen] ([GroepID])
GO

ALTER TABLE [dbo].[Elementen] CHECK CONSTRAINT [FK_Elementen_GroepenPresentatie]
GO

ALTER TABLE [dbo].[Elementen]  WITH CHECK ADD  CONSTRAINT [FK_Elementen_Rubrieken] FOREIGN KEY([RubriekID])
REFERENCES [dbo].[Rubrieken] ([RubriekID])
GO

ALTER TABLE [dbo].[Elementen] CHECK CONSTRAINT [FK_Elementen_Rubrieken]
GO

ALTER TABLE [dbo].[Elementen]  WITH CHECK ADD  CONSTRAINT [FK_Elementen_WaardenDomeinen] FOREIGN KEY([WaardenDomeinID])
REFERENCES [dbo].[WaardenDomeinen] ([WaardenDomeinID])
GO

ALTER TABLE [dbo].[Elementen] CHECK CONSTRAINT [FK_Elementen_WaardenDomeinen]
GO
/***END*** Object:  Table [dbo].[Elementen]    Script Date: 7/2/2013 12:37:26 AM ******/

/****** Object:  Table [dbo].[BerichtElementen]    Script Date: 7/2/2013 12:28:41 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[BerichtElementen](
	[BerichtID] [nvarchar](128) NOT NULL,
	[RubriekID] [char](4) NULL,
	[GroepID] [char](4) NULL,
	[ElementID] [int] NULL,
	[HL7Pad] [nvarchar](max) NULL,
	[HL7Datatype] [nchar](10) NULL,
	[HL7CardMin] [nchar](10) NULL,
	[HL7CardMax] [nchar](10) NULL,
	[HL7Conformance] [char](2) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[BerichtElementen]  WITH CHECK ADD  CONSTRAINT [FK_BerichtElementen_Berichten] FOREIGN KEY([BerichtID])
REFERENCES [dbo].[Berichten] ([BerichtID])
GO

ALTER TABLE [dbo].[BerichtElementen] CHECK CONSTRAINT [FK_BerichtElementen_Berichten]
GO

ALTER TABLE [dbo].[BerichtElementen]  WITH CHECK ADD  CONSTRAINT [FK_BerichtElementen_Cardinaliteit] FOREIGN KEY([HL7CardMin], [HL7CardMax])
REFERENCES [dbo].[Cardinaliteit] ([CardMin], [CardMax])
GO

ALTER TABLE [dbo].[BerichtElementen] CHECK CONSTRAINT [FK_BerichtElementen_Cardinaliteit]
GO

ALTER TABLE [dbo].[BerichtElementen]  WITH CHECK ADD  CONSTRAINT [FK_BerichtElementen_Conformiteiten] FOREIGN KEY([HL7Conformance])
REFERENCES [dbo].[Conformiteiten] ([Conformiteit])
GO

ALTER TABLE [dbo].[BerichtElementen] CHECK CONSTRAINT [FK_BerichtElementen_Conformiteiten]
GO

ALTER TABLE [dbo].[BerichtElementen]  WITH CHECK ADD  CONSTRAINT [FK_BerichtElementen_Elementen] FOREIGN KEY([ElementID])
REFERENCES [dbo].[Elementen] ([BDSnummer])
GO

ALTER TABLE [dbo].[BerichtElementen] CHECK CONSTRAINT [FK_BerichtElementen_Elementen]
GO

ALTER TABLE [dbo].[BerichtElementen]  WITH CHECK ADD  CONSTRAINT [FK_BerichtElementen_Groepen] FOREIGN KEY([GroepID])
REFERENCES [dbo].[Groepen] ([GroepID])
GO

ALTER TABLE [dbo].[BerichtElementen] CHECK CONSTRAINT [FK_BerichtElementen_Groepen]
GO

ALTER TABLE [dbo].[BerichtElementen]  WITH CHECK ADD  CONSTRAINT [FK_BerichtElementen_Rubrieken] FOREIGN KEY([RubriekID])
REFERENCES [dbo].[Rubrieken] ([RubriekID])
GO

ALTER TABLE [dbo].[BerichtElementen] CHECK CONSTRAINT [FK_BerichtElementen_Rubrieken]
GO

ALTER TABLE [dbo].[BerichtElementen]  WITH CHECK ADD  CONSTRAINT [CK_BerichtElementen_ElementGroepRubriek] CHECK  (([ElementID] IS NOT NULL AND [GroepID] IS NULL AND [RubriekID] IS NULL OR [ElementID] IS NULL AND [GroepID] IS NOT NULL AND [RubriekID] IS NULL OR [ElementID] IS NULL AND [GroepID] IS NULL AND [RubriekID] IS NOT NULL))
GO

ALTER TABLE [dbo].[BerichtElementen] CHECK CONSTRAINT [CK_BerichtElementen_ElementGroepRubriek]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Een en slechts een van ElementID, GroepID, RubriekID MOET een waarde hebben' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BerichtElementen', @level2type=N'CONSTRAINT',@level2name=N'CK_BerichtElementen_ElementGroepRubriek'
GO
/***END*** Object:  Table [dbo].[BerichtElementen]    Script Date: 7/2/2013 12:28:41 AM ******/