HumanResources.Employee Table

Description

Employee information such as salary, department, and title.

Properties

Creation Date	08/01/2010 08:40
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	290
Data Space Used	56.00 KB
Index Space Used	120.00 KB

Columns

Column Name	Description	Datatype	Length	Default
EmployeeID	Primary key for Employee records.	int	4
NationalIDNumber	Unique national identification number such as a social security number.	nvarchar	15
ContactID	Identifies the employee in the Contact table. Foreign key to Contact.ContactID.	int	4
LoginID	Network login.	nvarchar	256
ManagerID	Manager to whom the employee is assigned. Foreign Key to Employee.M	int	4
Title	Work title such as Buyer or Sales Representative.	nvarchar	50
BirthDate	Date of birth.	datetime	4
MaritalStatus	M = Married, S = Single	nchar	1
Gender	M = Male, F = Female	nchar	1
HireDate	Employee hired on this date.	datetime	4
SalariedFlag	Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.	dbo.Flag	1	((1))
VacationHours	Number of available vacation hours.	smallint	2	((0))
SickLeaveHours	Number of available sick leave hours.	smallint	2	((0))
CurrentFlag	0 = Inactive, 1 = Active	dbo.Flag	1	((1))
rowguid	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	uniqueidentifier	16	(newid())
ModifiedDate	Date and time the record was last updated.	datetime	4	(getdate())

Indexes

Index	Description	Primary	Unique
AK_Employee_LoginID	Unique nonclustered index.
AK_Employee_NationalIDNumber	Unique nonclustered index.
AK_Employee_rowguid	Unique nonclustered index. Used to support replication samples.
IX_Employee_ManagerID	Nonclustered index.
PK_Employee_EmployeeID	Primary key (clustered) constraint

Check Constraints

Name	Description	Expression
CK_Employee_BirthDate	Check constraint [BirthDate] >= '1930-01-01' AND [BirthDate] <= dateadd(year,(-18),GETDATE())	([BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate()))
CK_Employee_Gender	Check constraint [Gender]='f' OR [Gender]='m' OR [Gender]='F' OR [Gender]='M'	(upper([Gender])='F' OR upper([Gender])='M')
CK_Employee_HireDate	Check constraint [HireDate] >= '1996-07-01' AND [HireDate] <= dateadd(day,(1),GETDATE())	([HireDate]>='1996-07-01' AND [HireDate]<=dateadd(day,(1),getdate()))
CK_Employee_MaritalStatus	Check constraint [MaritalStatus]='s' OR [MaritalStatus]='m' OR [MaritalStatus]='S' OR [MaritalStatus]='M'	(upper([MaritalStatus])='S' OR upper([MaritalStatus])='M')
CK_Employee_SickLeaveHours	Check constraint [SickLeaveHours] >= (0) AND [SickLeaveHours] <= (120)	([SickLeaveHours]>=(0) AND [SickLeaveHours]<=(120))
CK_Employee_VacationHours	Check constraint [VacationHours] >= (-40) AND [VacationHours] <= (240)	([VacationHours]>=(-40) AND [VacationHours]<=(240))

Triggers

Trigger	Description
dEmployee	INSTEAD OF DELETE trigger which keeps Employees from being deleted.

Relationships

Relationship	Description
FK_Employee_Contact_ContactID	Foreign key constraint referencing Contact.ContactID.
FK_Employee_Employee_ManagerID	Foreign key constraint referencing Employee.ManagerID.
FK_EmployeeAddress_Employee_EmployeeID	Foreign key constraint referencing Employee.EmployeeID.
FK_EmployeeDepartmentHistory_Employee_EmployeeID	Foreign key constraint referencing Employee.EmployeeID.
FK_EmployeePayHistory_Employee_EmployeeID	Foreign key constraint referencing Employee.EmployeeID.
FK_JobCandidate_Employee_EmployeeID	Foreign key constraint referencing Employee.EmployeeID.
FK_PurchaseOrderHeader_Employee_EmployeeID	Foreign key constraint referencing Employee.EmployeeID.
FK_SalesPerson_Employee_SalesPersonID	Foreign key constraint referencing Employee.EmployeeID.

Objects that depend on HumanResources.Employee

Database Object	Object Type	Description	Dep Level
dEmployee	Trigger	INSTEAD OF DELETE trigger which keeps Employees from being deleted.	1
HumanResources.EmployeeAddress	Table	Cross-reference table mapping employees to their address(es).	1
HumanResources.EmployeeDepartmentHistory	Table	Employee department transfers.	1
HumanResources.EmployeePayHistory	Table	Employee pay history.	1
iduSalesOrderDetail	Trigger	AFTER INSERT, DELETE, UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in SalesOrderDetail and updates the SalesOrderHeader.SubTotal column.	4
Sales.Individual	Table	Demographic data about customers that purchase Adventure Works products online.	3
iPurchaseOrderDetail	Trigger	AFTER INSERT trigger that inserts a row in the TransactionHistory table and updates the PurchaseOrderHeader.SubTotal column.	3
iStore	Trigger	AFTER INSERT trigger inserting Store only if the Customer does not exist in the Individual table.	3
iuIndividual	Trigger	AFTER INSERT, UPDATE trigger inserting Individual only if the Customer does not exist in the Store table and setting the ModifiedDate column in the Individual table to the current date.	4
HumanResources.JobCandidate	Table	Résumés submitted to Human Resources by job applicants.	1
Purchasing.PurchaseOrderDetail	Table	Individual products associated with a specific purchase order. See PurchaseOrderHeader.	2
Purchasing.PurchaseOrderHeader	Table	General purchase order information. See PurchaseOrderDetail.	1
Sales.SalesOrderDetail	Table	Individual products associated with a specific sales order. See SalesOrderHeader.	3
Sales.SalesOrderHeader	Table	General sales order information.	2
Sales.SalesOrderHeaderSalesReason	Table	Cross-reference table mapping sales orders to sales reason codes.	3
Sales.SalesPerson	Table	Sales representative current information.	1
Sales.SalesPersonQuotaHistory	Table	Sales performance tracking.	2
Sales.SalesTerritoryHistory	Table	Sales representative transfers to other sales territories.	2
Sales.Store	Table	Customers (resellers) of Adventure Works products.	2
Sales.StoreContact	Table	Cross-reference table mapping stores and their employees.	3
dbo.ufnGetContactInformation	User Defined Function	Table value function returning the first name, last name, job title and contact type for a given contact.	1
uPurchaseOrderDetail	Trigger	AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in PurchaseOrderDetail and updates the PurchaseOrderHeader.SubTotal column.	3
uPurchaseOrderHeader	Trigger	AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the PurchaseOrderHeader table.	2
uSalesOrderHeader	Trigger	AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the SalesOrderHeader table.Updates the SalesYTD column in the SalesPerson and SalesTerritory tables.	3
dbo.uspGetEmployeeManagers	Stored Procedure	Stored procedure using a recursive query to return the direct and indirect managers of the specified employee.	1
dbo.uspGetManagerEmployees	Stored Procedure	Stored procedure using a recursive query to return the direct and indirect employees of the specified manager.	1
HumanResources.uspUpdateEmployeeHireInfo	Stored Procedure	Updates the Employee table and inserts a new row in the EmployeePayHistory table with the values specified in the input parameters.	1
HumanResources.uspUpdateEmployeeLogin	Stored Procedure	Updates the Employee table with the values specified in the input parameters for the given EmployeeID.	1
HumanResources.uspUpdateEmployeePersonalInfo	Stored Procedure	Updates the Employee table with the values specified in the input parameters for the given EmployeeID.	1
HumanResources.vEmployee	View	Employee names and addresses.	1
HumanResources.vEmployeeDepartment	View	Returns employee name, title, and current department.	1
HumanResources.vEmployeeDepartmentHistory	View	Returns employee name and current and previous departments.	1
Sales.vIndividualCustomer	View	Individual customers (names and addresses) that purchase Adventure Works Cycles products online.	4
Sales.vIndividualDemographics	View	Displays the content from each element in the xml column Demographics for each customer in the Sales.Individual table.	4
HumanResources.vJobCandidate	View	Job candidate names and resumes.	2
HumanResources.vJobCandidateEducation	View	Displays the content from each education related element in the xml column Resume in the HumanResources.JobCandidate table. The content has been localized into French, Simplified Chinese and Thai. Some data may not display correctly unless supplemental language support is installed.	2
HumanResources.vJobCandidateEmployment	View	Displays the content from each employement history related element in the xml column Resume in the HumanResources.JobCandidate table. The content has been localized into French, Simplified Chinese and Thai. Some data may not display correctly unless supplemental language support is installed.	2
Sales.vSalesPerson	View	Sales representiatives (names and addresses) and their sales-related information.	1
Sales.vSalesPersonSalesByFiscalYears	View	Uses PIVOT to return aggregated sales information for each sales representative.	1
Sales.vStoreWithDemographics	View	Stores (names and addresses) that sell Adventure Works Cycles products to consumers.	3

Objects that HumanResources.Employee depends on

Database Object	Object Type	Description	Dep Level
Person.Contact	Table	Names of each employee, customer contact, and vendor contact.	1
dbo.Flag	User Defined Data Type		1
dbo.Name	User Defined Data Type		2
dbo.NameStyle	User Defined Data Type		2
dbo.Phone	User Defined Data Type		2

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [HumanResources].[Employee](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[NationalIDNumber] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[ContactID] [int] NOT NULL,
	[LoginID] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[ManagerID] [int] NULL,
	[Title] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[BirthDate] [datetime] NOT NULL,
	[MaritalStatus] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Gender] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[HireDate] [datetime] NOT NULL,
	[SalariedFlag] [dbo].[Flag] NOT NULL,
	[VacationHours] [smallint] NOT NULL,
	[SickLeaveHours] [smallint] NOT NULL,
	[CurrentFlag] [dbo].[Flag] NOT NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED 
(
	[EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [HumanResources].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_Contact_ContactID] FOREIGN KEY([ContactID])
REFERENCES [Contact] ([ContactID])
ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [FK_Employee_Contact_ContactID]
ALTER TABLE [HumanResources].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_Employee_ManagerID] FOREIGN KEY([ManagerID])
REFERENCES [Employee] ([EmployeeID])
ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [FK_Employee_Employee_ManagerID]
ALTER TABLE [HumanResources].[Employee]  WITH CHECK ADD  CONSTRAINT [CK_Employee_BirthDate] CHECK  (([BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate())))
ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [CK_Employee_BirthDate]
ALTER TABLE [HumanResources].[Employee]  WITH CHECK ADD  CONSTRAINT [CK_Employee_Gender] CHECK  ((upper([Gender])='F' OR upper([Gender])='M'))
ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [CK_Employee_Gender]
ALTER TABLE [HumanResources].[Employee]  WITH CHECK ADD  CONSTRAINT [CK_Employee_HireDate] CHECK  (([HireDate]>='1996-07-01' AND [HireDate]<=dateadd(day,(1),getdate())))
ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [CK_Employee_HireDate]
ALTER TABLE [HumanResources].[Employee]  WITH CHECK ADD  CONSTRAINT [CK_Employee_MaritalStatus] CHECK  ((upper([MaritalStatus])='S' OR upper([MaritalStatus])='M'))
ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [CK_Employee_MaritalStatus]
ALTER TABLE [HumanResources].[Employee]  WITH CHECK ADD  CONSTRAINT [CK_Employee_SickLeaveHours] CHECK  (([SickLeaveHours]>=(0) AND [SickLeaveHours]<=(120)))
ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [CK_Employee_SickLeaveHours]
ALTER TABLE [HumanResources].[Employee]  WITH CHECK ADD  CONSTRAINT [CK_Employee_VacationHours] CHECK  (([VacationHours]>=(-40) AND [VacationHours]<=(240)))
ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [CK_Employee_VacationHours]
ALTER TABLE [HumanResources].[Employee] ADD  CONSTRAINT [DF_Employee_SalariedFlag]  DEFAULT ((1)) FOR [SalariedFlag]
ALTER TABLE [HumanResources].[Employee] ADD  CONSTRAINT [DF_Employee_VacationHours]  DEFAULT ((0)) FOR [VacationHours]
ALTER TABLE [HumanResources].[Employee] ADD  CONSTRAINT [DF_Employee_SickLeaveHours]  DEFAULT ((0)) FOR [SickLeaveHours]
ALTER TABLE [HumanResources].[Employee] ADD  CONSTRAINT [DF_Employee_CurrentFlag]  DEFAULT ((1)) FOR [CurrentFlag]
ALTER TABLE [HumanResources].[Employee] ADD  CONSTRAINT [DF_Employee_rowguid]  DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [HumanResources].[Employee] ADD  CONSTRAINT [DF_Employee_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]

Related Objects