SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T_Role]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[T_Role](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_T_Role] 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]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T_RevisionStatus]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[T_RevisionStatus](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_T_Status] 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]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T_ProjectProposalName]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[T_ProjectProposalName](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_T_ProjectProposalName] 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]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T_PartDescription]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[T_PartDescription](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[FormName] [nvarchar](max) NOT NULL,
	[Description] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_T_PartDescription] 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]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T_Version]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[T_Version](
	[Applic] [int] NOT NULL,
	[Version] [nchar](5) NOT NULL,
	[InUse] [int] NULL,
	[Datum] [datetime] NULL CONSTRAINT [DF_T_Version_Datum]  DEFAULT (getdate()),
 CONSTRAINT [PK_T_Version] PRIMARY KEY CLUSTERED 
(
	[Applic] ASC,
	[Version] 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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T_HigherPrivileges]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[T_HigherPrivileges](
	[EmployeeId] [nvarchar](max) NOT NULL,
	[Admin] [bit] NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T_Category]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[T_Category](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Description] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_T_Category] 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]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T_Auditor]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[T_Auditor](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeId] [nvarchar](max) NOT NULL,
	[Name] [nvarchar](max) NOT NULL,
	[SirName] [nvarchar](max) NOT NULL,
	[EmailAddress] [nvarchar](max) NOT NULL,
	[Title] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_T_Auditor] 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]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T_Creator]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[T_Creator](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeId] [nvarchar](max) NOT NULL,
	[Name] [nvarchar](max) NOT NULL,
	[SirName] [nvarchar](max) NOT NULL,
	[EmailAddress] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_T_Creator] 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]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T_ProjectProposalRevision]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[T_ProjectProposalRevision](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ProjectDescription] [nvarchar](max) NOT NULL,
	[SponsorId] [nvarchar](max) NULL,
	[SponsorName] [nvarchar](max) NULL,
	[PusherId] [nvarchar](max) NULL,
	[PusherName] [nvarchar](max) NULL,
	[CoordinatorId] [nvarchar](max) NULL,
	[CoordinatorName] [nvarchar](max) NULL,
	[Budget] [nvarchar](max) NULL,
	[Why] [nvarchar](max) NULL,
	[Include] [nvarchar](max) NULL,
	[Exclude] [nvarchar](max) NULL,
	[AsIs] [nvarchar](max) NULL,
	[ToBe] [nvarchar](max) NULL,
	[Objectifs] [nvarchar](max) NULL,
	[Cost] [nvarchar](max) NULL,
	[Benefits] [nvarchar](max) NULL,
	[Alignation] [nvarchar](max) NULL,
	[Preconditions] [nvarchar](max) NULL,
	[Constraints] [nvarchar](max) NULL,
	[Dependencies] [nvarchar](max) NULL,
	[Risk] [nvarchar](max) NULL,
	[Support] [nvarchar](max) NULL,
	[PreviousRevisionId] [int] NULL,
	[IsCurrentRevision] [bit] NOT NULL,
	[Creator_ID] [int] NULL,
	[Auditor_ID] [int] NULL,
	[SubCategory_ID] [int] NULL,
	[ProjectProposalName_ID] [int] NULL,
	[RevisionStatusLine_ID] [int] NULL,
 CONSTRAINT [PK_T_ProjectProposal] 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]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T_Milestone]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[T_Milestone](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](max) NOT NULL,
	[Description] [nvarchar](max) NOT NULL,
	[BeginDate] [datetime] NOT NULL,
	[EndDate] [datetime] NOT NULL,
	[WorkPackage_ID] [int] NULL,
 CONSTRAINT [PK_T_Milestone] 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]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T_TeamMember]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[T_TeamMember](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeId] [nvarchar](max) NOT NULL,
	[EmployeeName] [nvarchar](max) NOT NULL,
	[Department] [nvarchar](max) NOT NULL,
	[SubDepartment] [nvarchar](max) NOT NULL,
	[EmailAddress] [nvarchar](max) NULL,
	[Role_ID] [int] NULL,
	[ReportToTeamMember_ID] [int] NULL,
	[ProjectProposalRevision_ID] [int] NULL,
 CONSTRAINT [PK_T_TeamMember] 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]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T_RevisionStatusLine]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[T_RevisionStatusLine](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[MajorRevision] [int] NOT NULL,
	[MinorRevision] [int] NOT NULL,
	[MinorSubRevision] [int] NOT NULL,
	[Description] [nvarchar](max) NULL,
	[RevisedBy] [nvarchar](max) NULL,
	[DateModified] [datetime] NOT NULL,
	[RevisionStatus_ID] [int] NULL,
 CONSTRAINT [PK_T_RevisionStatusHistoric] 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]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T_SubCategory]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[T_SubCategory](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Description] [nvarchar](max) NOT NULL,
	[Category_ID] [int] NOT NULL,
 CONSTRAINT [PK_T_SubCategory] 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]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T_WorkPackage]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[T_WorkPackage](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](max) NOT NULL,
	[EstimatedTime] [nvarchar](max) NOT NULL,
	[ResponsibleId] [nvarchar](max) NOT NULL,
	[ResponsibleName] [nvarchar](max) NOT NULL,
	[DueDate] [datetime] NOT NULL,
	[ProjectProposalRevision_ID] [int] NULL,
 CONSTRAINT [PK_T_WorkPackage] 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]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_T_ProjectProposalRevision_T_Auditor]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_ProjectProposalRevision]'))
ALTER TABLE [dbo].[T_ProjectProposalRevision]  WITH CHECK ADD  CONSTRAINT [FK_T_ProjectProposalRevision_T_Auditor] FOREIGN KEY([Auditor_ID])
REFERENCES [dbo].[T_Auditor] ([ID])
GO
ALTER TABLE [dbo].[T_ProjectProposalRevision] CHECK CONSTRAINT [FK_T_ProjectProposalRevision_T_Auditor]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_T_ProjectProposalRevision_T_Creator]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_ProjectProposalRevision]'))
ALTER TABLE [dbo].[T_ProjectProposalRevision]  WITH CHECK ADD  CONSTRAINT [FK_T_ProjectProposalRevision_T_Creator] FOREIGN KEY([Creator_ID])
REFERENCES [dbo].[T_Creator] ([ID])
GO
ALTER TABLE [dbo].[T_ProjectProposalRevision] CHECK CONSTRAINT [FK_T_ProjectProposalRevision_T_Creator]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_T_ProjectProposalRevision_T_ProjectProposalName]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_ProjectProposalRevision]'))
ALTER TABLE [dbo].[T_ProjectProposalRevision]  WITH CHECK ADD  CONSTRAINT [FK_T_ProjectProposalRevision_T_ProjectProposalName] FOREIGN KEY([ProjectProposalName_ID])
REFERENCES [dbo].[T_ProjectProposalName] ([ID])
GO
ALTER TABLE [dbo].[T_ProjectProposalRevision] CHECK CONSTRAINT [FK_T_ProjectProposalRevision_T_ProjectProposalName]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_T_ProjectProposalRevision_T_RevisionStatusLine]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_ProjectProposalRevision]'))
ALTER TABLE [dbo].[T_ProjectProposalRevision]  WITH CHECK ADD  CONSTRAINT [FK_T_ProjectProposalRevision_T_RevisionStatusLine] FOREIGN KEY([RevisionStatusLine_ID])
REFERENCES [dbo].[T_RevisionStatusLine] ([ID])
GO
ALTER TABLE [dbo].[T_ProjectProposalRevision] CHECK CONSTRAINT [FK_T_ProjectProposalRevision_T_RevisionStatusLine]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_T_ProjectProposalRevision_T_SubCategory]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_ProjectProposalRevision]'))
ALTER TABLE [dbo].[T_ProjectProposalRevision]  WITH CHECK ADD  CONSTRAINT [FK_T_ProjectProposalRevision_T_SubCategory] FOREIGN KEY([SubCategory_ID])
REFERENCES [dbo].[T_SubCategory] ([ID])
GO
ALTER TABLE [dbo].[T_ProjectProposalRevision] CHECK CONSTRAINT [FK_T_ProjectProposalRevision_T_SubCategory]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_T_Milestone_T_WorkPackage]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_Milestone]'))
ALTER TABLE [dbo].[T_Milestone]  WITH CHECK ADD  CONSTRAINT [FK_T_Milestone_T_WorkPackage] FOREIGN KEY([WorkPackage_ID])
REFERENCES [dbo].[T_WorkPackage] ([ID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[T_Milestone] CHECK CONSTRAINT [FK_T_Milestone_T_WorkPackage]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_T_TeamMember_T_ProjectProposalRevision]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_TeamMember]'))
ALTER TABLE [dbo].[T_TeamMember]  WITH CHECK ADD  CONSTRAINT [FK_T_TeamMember_T_ProjectProposalRevision] FOREIGN KEY([ProjectProposalRevision_ID])
REFERENCES [dbo].[T_ProjectProposalRevision] ([ID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[T_TeamMember] CHECK CONSTRAINT [FK_T_TeamMember_T_ProjectProposalRevision]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_T_TeamMember_T_Role]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_TeamMember]'))
ALTER TABLE [dbo].[T_TeamMember]  WITH CHECK ADD  CONSTRAINT [FK_T_TeamMember_T_Role] FOREIGN KEY([Role_ID])
REFERENCES [dbo].[T_Role] ([ID])
GO
ALTER TABLE [dbo].[T_TeamMember] CHECK CONSTRAINT [FK_T_TeamMember_T_Role]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_T_RevisionStatusLine_T_RevisionStatus]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_RevisionStatusLine]'))
ALTER TABLE [dbo].[T_RevisionStatusLine]  WITH CHECK ADD  CONSTRAINT [FK_T_RevisionStatusLine_T_RevisionStatus] FOREIGN KEY([RevisionStatus_ID])
REFERENCES [dbo].[T_RevisionStatus] ([ID])
GO
ALTER TABLE [dbo].[T_RevisionStatusLine] CHECK CONSTRAINT [FK_T_RevisionStatusLine_T_RevisionStatus]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_T_SubCategory_T_Category]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_SubCategory]'))
ALTER TABLE [dbo].[T_SubCategory]  WITH CHECK ADD  CONSTRAINT [FK_T_SubCategory_T_Category] FOREIGN KEY([Category_ID])
REFERENCES [dbo].[T_Category] ([ID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[T_SubCategory] CHECK CONSTRAINT [FK_T_SubCategory_T_Category]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_T_WorkPackage_T_ProjectProposalRevision]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_WorkPackage]'))
ALTER TABLE [dbo].[T_WorkPackage]  WITH CHECK ADD  CONSTRAINT [FK_T_WorkPackage_T_ProjectProposalRevision] FOREIGN KEY([ProjectProposalRevision_ID])
REFERENCES [dbo].[T_ProjectProposalRevision] ([ID])
GO
ALTER TABLE [dbo].[T_WorkPackage] CHECK CONSTRAINT [FK_T_WorkPackage_T_ProjectProposalRevision]
