USE [MailDB]
GO

/****** Object:  Table [dbo].[SubscriberEvent]    Script Date: 01/03/2012 15:59:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SubscriberEvent]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SubscriberEvent](
	[EventID] [int] NOT NULL,
	[SubscriberID] [int] NOT NULL,
	[MailSent] [bit] NULL,
	[MailViewed] [bit] NULL,
	[Attending] [bit] NULL,
 CONSTRAINT [PK_SubscriberEvent] PRIMARY KEY CLUSTERED 
(
	[EventID] ASC,
	[SubscriberID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_SubscriberEvent_Event]') AND parent_object_id = OBJECT_ID(N'[dbo].[SubscriberEvent]'))
ALTER TABLE [dbo].[SubscriberEvent]  WITH CHECK ADD  CONSTRAINT [FK_SubscriberEvent_Event] FOREIGN KEY([EventID])
REFERENCES [dbo].[Events] ([EventID])
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_SubscriberEvent_Event]') AND parent_object_id = OBJECT_ID(N'[dbo].[SubscriberEvent]'))
ALTER TABLE [dbo].[SubscriberEvent] CHECK CONSTRAINT [FK_SubscriberEvent_Event]
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_SubscriberEvent_Subscriber]') AND parent_object_id = OBJECT_ID(N'[dbo].[SubscriberEvent]'))
ALTER TABLE [dbo].[SubscriberEvent]  WITH CHECK ADD  CONSTRAINT [FK_SubscriberEvent_Subscriber] FOREIGN KEY([SubscriberID])
REFERENCES [dbo].[Subscribers] ([SubscriberID])
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_SubscriberEvent_Subscriber]') AND parent_object_id = OBJECT_ID(N'[dbo].[SubscriberEvent]'))
ALTER TABLE [dbo].[SubscriberEvent] CHECK CONSTRAINT [FK_SubscriberEvent_Subscriber]
GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_SubscriberEvent_MailSent]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[SubscriberEvent] ADD  CONSTRAINT [DF_SubscriberEvent_MailSent]  DEFAULT ((0)) FOR [MailSent]
END

GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_SubscriberEvent_MailViewed]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[SubscriberEvent] ADD  CONSTRAINT [DF_SubscriberEvent_MailViewed]  DEFAULT ((0)) FOR [MailViewed]
END

GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_SubscriberEvent_Attending]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[SubscriberEvent] ADD  CONSTRAINT [DF_SubscriberEvent_Attending]  DEFAULT ((0)) FOR [Attending]
END

GO


USE [MailDB]
GO

/****** Object:  Table [dbo].[Subscribers]    Script Date: 01/03/2012 15:59:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Subscribers]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Subscribers](
	[SubscriberID] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [nvarchar](50) NOT NULL,
	[LastName] [nvarchar](100) NOT NULL,
	[Email] [nvarchar](100) NOT NULL,
	[CustomerID] [int] NULL,
 CONSTRAINT [PK_Subscribers] PRIMARY KEY CLUSTERED 
(
	[SubscriberID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Subscriber_Customer]') AND parent_object_id = OBJECT_ID(N'[dbo].[Subscribers]'))
ALTER TABLE [dbo].[Subscribers]  WITH CHECK ADD  CONSTRAINT [FK_Subscriber_Customer] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[Customers] ([CustomerID])
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Subscriber_Customer]') AND parent_object_id = OBJECT_ID(N'[dbo].[Subscribers]'))
ALTER TABLE [dbo].[Subscribers] CHECK CONSTRAINT [FK_Subscriber_Customer]
GO
USE [MailDB]
GO

/****** Object:  Table [dbo].[Events]    Script Date: 01/03/2012 15:59:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Events]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Events](
	[EventID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
	[LocationAddress] [nvarchar](100) NULL,
	[LocationPostalCode] [nvarchar](6) NULL,
	[LocationCountry] [nvarchar](50) NULL,
	[Date] [datetime] NOT NULL,
	[Duration] [int] NULL,
 CONSTRAINT [PK_Events] PRIMARY KEY CLUSTERED 
(
	[EventID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO