if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Employee_Department]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Employee] DROP CONSTRAINT FK_Employee_Department
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_DeleteDepartment]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_DeleteDepartment]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_DeleteEmployee]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_DeleteEmployee]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_GetDepartments]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_GetDepartments]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_GetEmployees]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_GetEmployees]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_InsertDepartment]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_InsertDepartment]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_InsertEmployee]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_InsertEmployee]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_UpdateDepartment]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_UpdateDepartment]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_UpdateEmployee]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_UpdateEmployee]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Department]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Department]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employee]
GO

CREATE TABLE [dbo].[Department] (
	[DepartmentID] [int] IDENTITY (1, 1) NOT NULL ,
	[DepartmentName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Employee] (
	[EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,
	[EmployeeName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[EmployeeDeptID] [int] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Department] WITH NOCHECK ADD 
	CONSTRAINT [PK_Department] PRIMARY KEY  CLUSTERED 
	(
		[DepartmentID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD 
	CONSTRAINT [PK_Employee] PRIMARY KEY  CLUSTERED 
	(
		[EmployeeID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Employee] ADD 
	CONSTRAINT [FK_Employee_Department] FOREIGN KEY 
	(
		[EmployeeDeptID]
	) REFERENCES [dbo].[Department] (
		[DepartmentID]
	) ON DELETE CASCADE 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.usp_DeleteDepartment
(
	@Original_DepartmentID int
)
AS
	SET NOCOUNT OFF;
DELETE FROM [Department] WHERE (([DepartmentID] = @Original_DepartmentID))
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.usp_DeleteEmployee
(
	@Original_EmployeeID int
)
AS
	SET NOCOUNT OFF;
DELETE FROM [Employee] WHERE (([EmployeeID] = @Original_EmployeeID))
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.usp_GetDepartments
AS
	SET NOCOUNT ON;
SELECT     DepartmentID, DepartmentName
FROM         Department
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.usp_GetEmployees
AS
	SET NOCOUNT ON;
SELECT     EmployeeID, EmployeeName, EmployeeDeptID
FROM         Employee
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.usp_InsertDepartment
(
	@DepartmentName nvarchar(255)
)
AS
	SET NOCOUNT OFF;
INSERT INTO [Department] ([DepartmentName]) VALUES (@DepartmentName);
	
SELECT DepartmentID, DepartmentName FROM Department WHERE (DepartmentID = SCOPE_IDENTITY())
RETURN SCOPE_IDENTITY()
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.usp_InsertEmployee
(
	@EmployeeName nvarchar(255),
	@EmployeeDeptID int
)
AS
	SET NOCOUNT OFF;
INSERT INTO [Employee] ([EmployeeName], [EmployeeDeptID]) VALUES (@EmployeeName, @EmployeeDeptID);
	
SELECT EmployeeID, EmployeeName, EmployeeDeptID FROM Employee WHERE (EmployeeID = SCOPE_IDENTITY())
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.usp_UpdateDepartment
(
	@DepartmentName nvarchar(255),
	@Original_DepartmentID int,
	@DepartmentID int
)
AS
	SET NOCOUNT OFF;
UPDATE [Department] SET [DepartmentName] = @DepartmentName WHERE (([DepartmentID] = @Original_DepartmentID));
	
SELECT DepartmentID, DepartmentName FROM Department WHERE (DepartmentID = @DepartmentID)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.usp_UpdateEmployee
(
	@EmployeeName nvarchar(255),
	@EmployeeDeptID int,
	@Original_EmployeeID int,
	@EmployeeID int
)
AS
	SET NOCOUNT OFF;
UPDATE [Employee] SET [EmployeeName] = @EmployeeName, [EmployeeDeptID] = @EmployeeDeptID WHERE (([EmployeeID] = @Original_EmployeeID));
	
SELECT EmployeeID, EmployeeName, EmployeeDeptID FROM Employee WHERE (EmployeeID = @EmployeeID)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO