USE COMPANY --name of your database
GO

--DROP FOREIGN KEY CONSTRAINTS
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_DEPARTMENT_EMPLOYEE]') AND parent_object_id = OBJECT_ID(N'[dbo].[DEPARTMENT]'))
ALTER TABLE [dbo].[DEPARTMENT] DROP CONSTRAINT [FK_DEPARTMENT_EMPLOYEE]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PROJECT_DEPARTMENT]') AND parent_object_id = OBJECT_ID(N'[dbo].[PROJECT]'))
ALTER TABLE [dbo].[PROJECT] DROP CONSTRAINT [FK_PROJECT_DEPARTMENT]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_EMPLOYEE_DEPARTMENT]') AND parent_object_id = OBJECT_ID(N'[dbo].[EMPLOYEE]'))
ALTER TABLE [dbo].[EMPLOYEE] DROP CONSTRAINT [FK_EMPLOYEE_DEPARTMENT]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_EMPLOYEE_EMPLOYEE]') AND parent_object_id = OBJECT_ID(N'[dbo].[EMPLOYEE]'))
ALTER TABLE [dbo].[EMPLOYEE] DROP CONSTRAINT [FK_EMPLOYEE_EMPLOYEE]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_WORKS_ON_EMPLOYEE]') AND parent_object_id = OBJECT_ID(N'[dbo].[WORKS_ON]'))
ALTER TABLE [dbo].[WORKS_ON] DROP CONSTRAINT [FK_WORKS_ON_EMPLOYEE]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_WORKS_ON_PROJECT]') AND parent_object_id = OBJECT_ID(N'[dbo].[WORKS_ON]'))
ALTER TABLE [dbo].[WORKS_ON] DROP CONSTRAINT [FK_WORKS_ON_PROJECT]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_DEPENDENT_EMPLOYEE]') AND parent_object_id = OBJECT_ID(N'[dbo].[DEPENDENT]'))
ALTER TABLE [dbo].[DEPENDENT] DROP CONSTRAINT [FK_DEPENDENT_EMPLOYEE]
GO


--DROP TABLES
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DEPARTMENT]') AND type in (N'U'))
DROP TABLE [dbo].[DEPARTMENT]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PROJECT]') AND type in (N'U'))
DROP TABLE [dbo].[PROJECT]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WORKS_ON]') AND type in (N'U'))
DROP TABLE [dbo].[WORKS_ON]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EMPLOYEE]') AND type in (N'U'))
DROP TABLE [dbo].[EMPLOYEE]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DEPENDENT]') AND type in (N'U'))
DROP TABLE [dbo].[DEPENDENT]
GO

--CREATE TABLES
CREATE TABLE [dbo].[PROJECT](
	[project_id] [int] NOT NULL,
	[name] [nvarchar](50) NOT NULL,
	[location] [nvarchar](50) NOT NULL,
	[controlling_department] [int] NOT NULL
	CONSTRAINT [PK_PROJECT] PRIMARY KEY 
	([project_id] ASC)
)
CREATE TABLE [dbo].[EMPLOYEE](
	[SSN] [int] NOT NULL,
	[Bdate] [smalldatetime] NOT NULL,
	[Fname] [nvarchar](20) NOT NULL,
	[Minit] [nvarchar](1) NOT NULL,
	[Lname] [nvarchar](30) NOT NULL,
	[Address] [nvarchar](100) NOT NULL,
	[Salary] [smallmoney] NOT NULL,
	[Sex] [bit] NOT NULL,
	[Department] [int] NOT NULL,
	[Supervisor] [int] NULL,
	CONSTRAINT [PK_EMPLOYEE] PRIMARY KEY 
	([SSN] ASC)
)
CREATE TABLE [dbo].[WORKS_ON](
	[SSN] [int] NOT NULL,
	[project_id] [int] NOT NULL
	CONSTRAINT [PK_WORKS_ON] PRIMARY KEY 
	([SSN] ASC, [project_id] ASC)
)
CREATE TABLE [dbo].[DEPENDENT](
	[Relationship] [nvarchar](30) NOT NULL,
	[Birth_date] [smalldatetime] NOT NULL,
	[Sex] [bit] NOT NULL,
	[Employee] [int] NOT NULL,
	[Dependent_name] [nvarchar](50) NOT NULL,
	CONSTRAINT [PK_DEPENDENT] PRIMARY KEY 
	([Employee] ASC,[Dependent_name] ASC)
)
CREATE TABLE [dbo].[DEPARTMENT](
	[department_id] [int] NOT NULL,
	[name] [nvarchar](50) NOT NULL,
	[Manager] [int] NULL,
	[Manager_start_date] [smalldatetime] NULL,
	CONSTRAINT [PK_DEPARTMENT] PRIMARY KEY 
	([department_id] ASC)
)

--CREATE FOREIGN KEY CONSTRAINTS
ALTER TABLE [dbo].[PROJECT]  WITH CHECK ADD  CONSTRAINT [FK_PROJECT_DEPARTMENT] FOREIGN KEY([controlling_department])
REFERENCES [dbo].[DEPARTMENT] ([department_id])
GO
ALTER TABLE [dbo].[EMPLOYEE]  WITH CHECK ADD  CONSTRAINT [FK_EMPLOYEE_DEPARTMENT] FOREIGN KEY([Department])
REFERENCES [dbo].[DEPARTMENT] ([department_id])
GO
ALTER TABLE [dbo].[EMPLOYEE]  WITH CHECK ADD  CONSTRAINT [FK_EMPLOYEE_EMPLOYEE] FOREIGN KEY([Supervisor])
REFERENCES [dbo].[EMPLOYEE] ([SSN])
GO
ALTER TABLE [dbo].[WORKS_ON]  WITH CHECK ADD  CONSTRAINT [FK_WORKS_ON_EMPLOYEE] FOREIGN KEY([SSN])
REFERENCES [dbo].[EMPLOYEE] ([SSN])
GO
ALTER TABLE [dbo].[WORKS_ON]  WITH CHECK ADD  CONSTRAINT [FK_WORKS_ON_PROJECT] FOREIGN KEY([project_id])
REFERENCES [dbo].[PROJECT] ([project_id])
GO
ALTER TABLE [dbo].[DEPENDENT]  WITH CHECK ADD  CONSTRAINT [FK_DEPENDENT_EMPLOYEE] FOREIGN KEY([Employee])
REFERENCES [dbo].[EMPLOYEE] ([SSN])
GO
ALTER TABLE [dbo].[DEPARTMENT]  WITH CHECK ADD  CONSTRAINT [FK_DEPARTMENT_EMPLOYEE] FOREIGN KEY([Manager])
REFERENCES [dbo].[EMPLOYEE] ([SSN])


--PRINT ALL CONSTRAINTS
SELECT 		OBJECT_NAME(OBJECT_ID) AS [Name of Constraint],
			OBJECT_NAME(parent_object_id) AS [Table Name],
			type_desc AS [Constraint Type]
FROM 		sys.objects
WHERE 		type_desc LIKE '%CONSTRAINT%'
			AND
			NOT OBJECT_NAME(parent_object_id)='sysdiagrams'
ORDER BY 	type_desc