Script to create HumanResources demo database

blog-create-human-resources.sql

USE [HumanResources]
GO
/****** Object:  Table [dbo].[Dependent]    Script Date: 11/14/2013 4:39:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Dependent](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeID] [int] NOT NULL,
	[FirstName] [nvarchar](50) NOT NULL,
	[LastName] [nvarchar](50) NOT NULL,
	[DateOfBirth] [datetime] NOT NULL,
 CONSTRAINT [PK_Dependent] 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]

GO
/****** Object:  Table [dbo].[Employee]    Script Date: 11/14/2013 4:39:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employee](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [nvarchar](50) NOT NULL,
	[LastName] [nvarchar](50) NOT NULL,
	[MiddleInitial] [nchar](1) NULL,
	[DateOfBirth] [datetime] NOT NULL,
	[DateOfHire] [datetime] NULL,
	[EmployeeStatusID] [int] NOT NULL,
	[JobTitleID] [int] NOT NULL,
	[PtoBalance] [smallint] NOT NULL,
 CONSTRAINT [PK_Person] 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]

GO
/****** Object:  Table [dbo].[EmployeeStatus]    Script Date: 11/14/2013 4:39:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmployeeStatus](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](25) NOT NULL,
 CONSTRAINT [PK_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]

GO
/****** Object:  Table [dbo].[JobTitle]    Script Date: 11/14/2013 4:39:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[JobTitle](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_JobTitle] 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]

GO
/****** Object:  Table [dbo].[PtoRequest]    Script Date: 11/14/2013 4:39:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PtoRequest](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeID] [int] NOT NULL,
	[SubmittedOn] [datetime] NOT NULL,
	[RequestDate] [datetime] NOT NULL,
	[Note] [nvarchar](max) NULL,
	[RequestStatusID] [int] NOT NULL,
 CONSTRAINT [PK_PtoRequest] 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] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[PtoRequestStatus]    Script Date: 11/14/2013 4:39:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PtoRequestStatus](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_PtoRequestStatus] 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]

GO
SET IDENTITY_INSERT [dbo].[Dependent] ON 

GO
INSERT [dbo].[Dependent] ([ID], [EmployeeID], [FirstName], [LastName], [DateOfBirth]) VALUES (1, 1, N'Marge', N'Simpson', CAST(0x000068D500000000 AS DateTime))
GO
INSERT [dbo].[Dependent] ([ID], [EmployeeID], [FirstName], [LastName], [DateOfBirth]) VALUES (2, 1, N'Lisa', N'Simpson', CAST(0x0000772200000000 AS DateTime))
GO
INSERT [dbo].[Dependent] ([ID], [EmployeeID], [FirstName], [LastName], [DateOfBirth]) VALUES (3, 1, N'Bart', N'Simpson', CAST(0x00007AEC00000000 AS DateTime))
GO
INSERT [dbo].[Dependent] ([ID], [EmployeeID], [FirstName], [LastName], [DateOfBirth]) VALUES (4, 1, N'Maggie', N'Simpson', CAST(0x00008C9D00000000 AS DateTime))
GO
INSERT [dbo].[Dependent] ([ID], [EmployeeID], [FirstName], [LastName], [DateOfBirth]) VALUES (6, 4, N'Skylar', N'White', CAST(0x000058BE00000000 AS DateTime))
GO
INSERT [dbo].[Dependent] ([ID], [EmployeeID], [FirstName], [LastName], [DateOfBirth]) VALUES (7, 4, N'Flynn', N'White', CAST(0x00008B1B00000000 AS DateTime))
GO
INSERT [dbo].[Dependent] ([ID], [EmployeeID], [FirstName], [LastName], [DateOfBirth]) VALUES (8, 4, N'Holly', N'White', CAST(0x00009BCA00000000 AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[Dependent] OFF
GO
SET IDENTITY_INSERT [dbo].[Employee] ON 

GO
INSERT [dbo].[Employee] ([ID], [FirstName], [LastName], [MiddleInitial], [DateOfBirth], [DateOfHire], [EmployeeStatusID], [JobTitleID], [PtoBalance]) VALUES (1, N'Homer', N'Simpson', N'J', CAST(0x0000616D00000000 AS DateTime), CAST(0x000079A000000000 AS DateTime), 1, 1, 0)
GO
INSERT [dbo].[Employee] ([ID], [FirstName], [LastName], [MiddleInitial], [DateOfBirth], [DateOfHire], [EmployeeStatusID], [JobTitleID], [PtoBalance]) VALUES (4, N'Walter', N'White', N'H', CAST(0x0000544500000000 AS DateTime), CAST(0x0000A4DC00000000 AS DateTime), 2, 2, 86)
GO
SET IDENTITY_INSERT [dbo].[Employee] OFF
GO
SET IDENTITY_INSERT [dbo].[EmployeeStatus] ON 

GO
INSERT [dbo].[EmployeeStatus] ([ID], [Name]) VALUES (1, N'Active')
GO
INSERT [dbo].[EmployeeStatus] ([ID], [Name]) VALUES (2, N'Terminated')
GO
INSERT [dbo].[EmployeeStatus] ([ID], [Name]) VALUES (3, N'Military Leave')
GO
SET IDENTITY_INSERT [dbo].[EmployeeStatus] OFF
GO
SET IDENTITY_INSERT [dbo].[JobTitle] ON 

GO
INSERT [dbo].[JobTitle] ([ID], [Name]) VALUES (1, N'Nuclear Safety Inspector')
GO
INSERT [dbo].[JobTitle] ([ID], [Name]) VALUES (2, N'Chemistry Teacher')
GO
SET IDENTITY_INSERT [dbo].[JobTitle] OFF
GO
SET IDENTITY_INSERT [dbo].[PtoRequest] ON 

GO
INSERT [dbo].[PtoRequest] ([ID], [EmployeeID], [SubmittedOn], [RequestDate], [Note], [RequestStatusID]) VALUES (1, 1, CAST(0x00007E5800000000 AS DateTime), CAST(0x00007EF200000000 AS DateTime), N'Mr. Plow business meeting', 3)
GO
INSERT [dbo].[PtoRequest] ([ID], [EmployeeID], [SubmittedOn], [RequestDate], [Note], [RequestStatusID]) VALUES (2, 1, CAST(0x00007E5900000000 AS DateTime), CAST(0x00007EF200000000 AS DateTime), N'Mr. Plow business meeting', 3)
GO
INSERT [dbo].[PtoRequest] ([ID], [EmployeeID], [SubmittedOn], [RequestDate], [Note], [RequestStatusID]) VALUES (3, 1, CAST(0x00007E5A00000000 AS DateTime), CAST(0x00007EF200000000 AS DateTime), N'Mr. Plow business meeting', 3)
GO
INSERT [dbo].[PtoRequest] ([ID], [EmployeeID], [SubmittedOn], [RequestDate], [Note], [RequestStatusID]) VALUES (4, 1, CAST(0x00007E5B00000000 AS DateTime), CAST(0x00007EF200000000 AS DateTime), N'Mr. Plow business meeting', 3)
GO
INSERT [dbo].[PtoRequest] ([ID], [EmployeeID], [SubmittedOn], [RequestDate], [Note], [RequestStatusID]) VALUES (5, 4, CAST(0x0000A23700000000 AS DateTime), CAST(0x0000A24100000000 AS DateTime), N'Trip to Belize', 1)
GO
SET IDENTITY_INSERT [dbo].[PtoRequest] OFF
GO
SET IDENTITY_INSERT [dbo].[PtoRequestStatus] ON 

GO
INSERT [dbo].[PtoRequestStatus] ([ID], [Name]) VALUES (1, N'Pending')
GO
INSERT [dbo].[PtoRequestStatus] ([ID], [Name]) VALUES (2, N'Approved')
GO
INSERT [dbo].[PtoRequestStatus] ([ID], [Name]) VALUES (3, N'Denied')
GO
INSERT [dbo].[PtoRequestStatus] ([ID], [Name]) VALUES (4, N'Cancelled')
GO
SET IDENTITY_INSERT [dbo].[PtoRequestStatus] OFF
GO
ALTER TABLE [dbo].[Dependent]  WITH CHECK ADD  CONSTRAINT [FK_Dependent_Employee] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employee] ([ID])
GO
ALTER TABLE [dbo].[Dependent] CHECK CONSTRAINT [FK_Dependent_Employee]
GO
ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_Employee] FOREIGN KEY([ID])
REFERENCES [dbo].[Employee] ([ID])
GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Employee]
GO
ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_EmployeeStatus] FOREIGN KEY([EmployeeStatusID])
REFERENCES [dbo].[EmployeeStatus] ([ID])
GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_EmployeeStatus]
GO
ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_JobTitle] FOREIGN KEY([JobTitleID])
REFERENCES [dbo].[JobTitle] ([ID])
GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_JobTitle]
GO
ALTER TABLE [dbo].[EmployeeStatus]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeStatus_EmployeeStatus] FOREIGN KEY([ID])
REFERENCES [dbo].[EmployeeStatus] ([ID])
GO
ALTER TABLE [dbo].[EmployeeStatus] CHECK CONSTRAINT [FK_EmployeeStatus_EmployeeStatus]
GO
ALTER TABLE [dbo].[PtoRequest]  WITH CHECK ADD  CONSTRAINT [FK_PtoRequest_Employee] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employee] ([ID])
GO
ALTER TABLE [dbo].[PtoRequest] CHECK CONSTRAINT [FK_PtoRequest_Employee]
GO
ALTER TABLE [dbo].[PtoRequest]  WITH CHECK ADD  CONSTRAINT [FK_PtoRequestStatus_PtoRequest] FOREIGN KEY([RequestStatusID])
REFERENCES [dbo].[PtoRequestStatus] ([ID])
GO
ALTER TABLE [dbo].[PtoRequest] CHECK CONSTRAINT [FK_PtoRequestStatus_PtoRequest]
GO
USE [master]
GO
ALTER DATABASE [HumanResources] SET  READ_WRITE 
GO