HumanResources

1 AdventureWorks.HumanResources © Matthias Wolf; wolf online training Hover columns to read comments. HumanResources Fk FK_EmployeeDepartmentHistory_Department_DepartmentID EmployeeDepartmentHistory ref Department ( DepartmentID ) Fk FK_EmployeeDepartmentHistory_Department_DepartmentID EmployeeDepartmentHistory ref Department ( DepartmentID ) Fk FK_EmployeeDepartmentHistory_Employee_BusinessEntityID EmployeeDepartmentHistory ref Employee ( BusinessEntityID ) Fk FK_EmployeeDepartmentHistory_Employee_BusinessEntityID EmployeeDepartmentHistory ref Employee ( BusinessEntityID ) Fk FK_EmployeeDepartmentHistory_Shift_ShiftID EmployeeDepartmentHistory ref Shift ( ShiftID ) Fk FK_EmployeeDepartmentHistory_Shift_ShiftID EmployeeDepartmentHistory ref Shift ( ShiftID ) Fk FK_EmployeePayHistory_Employee_BusinessEntityID EmployeePayHistory ref Employee ( BusinessEntityID ) Fk FK_EmployeePayHistory_Employee_BusinessEntityID EmployeePayHistory ref Employee ( BusinessEntityID ) Fk FK_JobCandidate_Employee_BusinessEntityID JobCandidate ref Employee ( BusinessEntityID ) Fk FK_JobCandidate_Employee_BusinessEntityID JobCandidate ref Employee ( BusinessEntityID ) DepartmentTable HumanResources.Department Lookup table containing the departments within the Adventure Works Cycles company. Pk PK_Department_DepartmentID ( DepartmentID ) Clustered index created by a primary key constraint.DepartmentIDDepartmentID * smallint Primary key for Department records. smallintReferred by EmployeeDepartmentHistory ( DepartmentID ) Unq AK_Department_Name ( Name ) Unique nonclustered index.NameName * name Name of the department. name GroupNameGroupName * name Name of the group to which the department belongs. name ModifiedDateModifiedDate * datetime default getdate() Date and time the record was last updated. datetime EmployeeTable HumanResources.Employee Employee information such as salary, department, and title. Pk PK_Employee_BusinessEntityID ( BusinessEntityID ) Clustered index created by a primary key constraint.BusinessEntityIDBusinessEntityID * int Primary key for Employee records. Foreign key to BusinessEntity.BusinessEntityID. intReferences Person ( BusinessEntityID ) Referred by EmployeeDepartmentHistory ( BusinessEntityID ) Referred by EmployeePayHistory ( BusinessEntityID ) Referred by JobCandidate ( BusinessEntityID ) Referred by Document ( Owner -> BusinessEntityID ) Referred by PurchaseOrderHeader ( EmployeeID -> BusinessEntityID ) Referred by SalesPerson ( BusinessEntityID ) Unq AK_Employee_NationalIDNumber ( NationalIDNumber ) Unique nonclustered index.NationalIDNumberNationalIDNumber * nvarchar(15) Unique national identification number such as a social security number. nvarchar(15) Unq AK_Employee_LoginID ( LoginID ) Unique nonclustered index.LoginIDLoginID * nvarchar(256) Network login. nvarchar(256) IX_Employee_OrganizationLevel_OrganizationNode ( OrganizationLevel, OrganizationNode ) Unique nonclustered index.IX_Employee_OrganizationNode ( OrganizationNode ) Unique nonclustered index.OrganizationNodeOrganizationNode hierarchyid Where the employee is located in corporate hierarchy. hierarchyid IX_Employee_OrganizationLevel_OrganizationNode ( OrganizationLevel, OrganizationNode ) Unique nonclustered index.OrganizationLevelOrganizationLevel smallint The depth of the employee in the corporate hierarchy. smallint JobTitleJobTitle * nvarchar(50) Work title such as Buyer or Sales Representative. nvarchar(50) BirthDateBirthDate * date Date of birth. date MaritalStatusMaritalStatus * nchar(1) M = Married, S = Single nchar(1) GenderGender * nchar(1) M = Male, F = Female nchar(1) HireDateHireDate * date Employee hired on this date. date SalariedFlagSalariedFlag * flag default 1 Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining. flag VacationHoursVacationHours * smallint default 0 Number of available vacation hours. smallint SickLeaveHoursSickLeaveHours * smallint default 0 Number of available sick leave hours. smallint CurrentFlagCurrentFlag * flag default 1 0 = Inactive, 1 = Active flag Unq AK_Employee_rowguid ( rowguid ) Unique nonclustered index. Used to support replication samples.rowguidrowguid * uniqueidentifier default newid() ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. uniqueidentifier ModifiedDateModifiedDate * datetime default getdate() Date and time the record was last updated. datetime EmployeeDepartmentHistoryTable HumanResources.EmployeeDepartmentHistory Employee department transfers. Pk PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID ( BusinessEntityID, StartDate, DepartmentID, ShiftID ) Clustered index created by a primary key constraint.BusinessEntityIDBusinessEntityID * int Employee identification number. Foreign key to Employee.BusinessEntityID. intReferences Employee ( BusinessEntityID ) Pk PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID ( BusinessEntityID, StartDate, DepartmentID, ShiftID ) Clustered index created by a primary key constraint.StartDateStartDate * date Date the employee started work in the department. date Pk PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID ( BusinessEntityID, StartDate, DepartmentID, ShiftID ) Clustered index created by a primary key constraint.IX_EmployeeDepartmentHistory_DepartmentID ( DepartmentID ) Nonclustered index.DepartmentIDDepartmentID * smallint Department in which the employee worked including currently. Foreign key to Department.DepartmentID. smallintReferences Department ( DepartmentID ) Pk PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID ( BusinessEntityID, StartDate, DepartmentID, ShiftID ) Clustered index created by a primary key constraint.IX_EmployeeDepartmentHistory_ShiftID ( ShiftID ) Nonclustered index.ShiftIDShiftID * tinyint Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID. tinyintReferences Shift ( ShiftID ) EndDateEndDate date Date the employee left the department. NULL = Current department. date ModifiedDateModifiedDate * datetime default getdate() Date and time the record was last updated. datetime EmployeePayHistoryTable HumanResources.EmployeePayHistory Employee pay history. Pk PK_EmployeePayHistory_BusinessEntityID_RateChangeDate ( BusinessEntityID, RateChangeDate ) Clustered index created by a primary key constraint.BusinessEntityIDBusinessEntityID * int Employee identification number. Foreign key to Employee.BusinessEntityID. intReferences Employee ( BusinessEntityID ) Pk PK_EmployeePayHistory_BusinessEntityID_RateChangeDate ( BusinessEntityID, RateChangeDate ) Clustered index created by a primary key constraint.RateChangeDateRateChangeDate * datetime Date the change in pay is effective datetime RateRate * money Salary hourly rate. money PayFrequencyPayFrequency * tinyint 1 = Salary received monthly, 2 = Salary received biweekly tinyint ModifiedDateModifiedDate * datetime default getdate() Date and time the record was last updated. datetime JobCandidateTable HumanResources.JobCandidate Résumés submitted to Human Resources by job applicants. Pk PK_JobCandidate_JobCandidateID ( JobCandidateID ) Clustered index created by a primary key constraint.JobCandidateIDJobCandidateID * int Primary key for JobCandidate records. int IX_JobCandidate_BusinessEntityID ( BusinessEntityID ) Nonclustered index.BusinessEntityIDBusinessEntityID int Employee identification number if applicant was hired. Foreign key to Employee.BusinessEntityID. intReferences Employee ( BusinessEntityID ) ResumeResume xml Résumé in XML format. xml ModifiedDateModifiedDate * datetime default getdate() Date and time the record was last updated. datetime ShiftTable HumanResources.Shift Work shift lookup table. Pk PK_Shift_ShiftID ( ShiftID ) Clustered index created by a primary key constraint.ShiftIDShiftID * tinyint Primary key for Shift records. tinyintReferred by EmployeeDepartmentHistory ( ShiftID ) Unq AK_Shift_Name ( Name ) Unique nonclustered index.NameName * name Shift description. name Unq AK_Shift_StartTime_EndTime ( StartTime, EndTime ) Unique nonclustered index.StartTimeStartTime * time(16) Shift start time. time(16) Unq AK_Shift_StartTime_EndTime ( StartTime, EndTime ) Unique nonclustered index.EndTimeEndTime * time(16) Shift end time. time(16) ModifiedDateModifiedDate * datetime default getdate() Date and time the record was last updated. datetime
Tables



Table Department

Lookup table containing the departments within the Adventure Works Cycles company.

IdxField NameData TypeDescription
* DepartmentID smallint AUTOINCREMENT Primary key for Department records.
* Name name Name of the department.
* GroupName name Name of the group to which the department belongs.
* ModifiedDate datetime DEFAULT getdate() Date and time the record was last updated.
Indexes
PK_Department_DepartmentID ON DepartmentID Clustered index created by a primary key constraint.
AK_Department_Name ON Name Unique nonclustered index.


Table Employee

Employee information such as salary, department, and title.

IdxField NameData TypeDescription
* BusinessEntityID int Primary key for Employee records. Foreign key to BusinessEntity.BusinessEntityID.
* NationalIDNumber nvarchar(15) Unique national identification number such as a social security number.
* LoginID nvarchar(256) Network login.
OrganizationNode hierarchyid Where the employee is located in corporate hierarchy.
OrganizationLevel smallint The depth of the employee in the corporate hierarchy.
* JobTitle nvarchar(50) Work title such as Buyer or Sales Representative.
* BirthDate date Date of birth.
* MaritalStatus nchar(1) M = Married, S = Single
* Gender nchar(1) M = Male, F = Female
* HireDate date Employee hired on this date.
* SalariedFlag flag DEFAULT 1 Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.
* VacationHours smallint DEFAULT 0 Number of available vacation hours.
* SickLeaveHours smallint DEFAULT 0 Number of available sick leave hours.
* CurrentFlag flag DEFAULT 1 0 = Inactive, 1 = Active
* rowguid uniqueidentifier DEFAULT newid() ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
* ModifiedDate datetime DEFAULT getdate() Date and time the record was last updated.
Indexes
PK_Employee_BusinessEntityID ON BusinessEntityID Clustered index created by a primary key constraint.
AK_Employee_LoginID ON LoginID Unique nonclustered index.
AK_Employee_NationalIDNumber ON NationalIDNumber Unique nonclustered index.
AK_Employee_rowguid ON rowguid Unique nonclustered index. Used to support replication samples.
IX_Employee_OrganizationLevel_OrganizationNode ON OrganizationLevel, OrganizationNode Unique nonclustered index.
IX_Employee_OrganizationNode ON OrganizationNode Unique nonclustered index.
Foreign Keys
FK_Employee_Person_BusinessEntityID ( BusinessEntityID ) ref Person (BusinessEntityID) Foreign key constraint referencing Person.BusinessEntityID.
Constraints
  CK_Employee_BirthDate [BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate())
  CK_Employee_MaritalStatus upper([MaritalStatus])='S' OR upper([MaritalStatus])='M'
  CK_Employee_Gender upper([Gender])='F' OR upper([Gender])='M'
  CK_Employee_HireDate [HireDate]>='1996-07-01' AND [HireDate]<=dateadd(day,(1),getdate())
  CK_Employee_VacationHours [VacationHours]>=(-40) AND [VacationHours]<=(240)
  CK_Employee_SickLeaveHours [SickLeaveHours]>=(0) AND [SickLeaveHours]<=(120)
Triggers
  dEmployee


Table EmployeeDepartmentHistory

Employee department transfers.

IdxField NameData TypeDescription
* BusinessEntityID int Employee identification number. Foreign key to Employee.BusinessEntityID.
* StartDate date Date the employee started work in the department.
* DepartmentID smallint Department in which the employee worked including currently. Foreign key to Department.DepartmentID.
* ShiftID tinyint Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID.
  EndDate date Date the employee left the department. NULL = Current department.
* ModifiedDate datetime DEFAULT getdate() Date and time the record was last updated.
Indexes
PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID ON BusinessEntityID, StartDate, DepartmentID, ShiftID Clustered index created by a primary key constraint.
IX_EmployeeDepartmentHistory_DepartmentID ON DepartmentID Nonclustered index.
IX_EmployeeDepartmentHistory_ShiftID ON ShiftID Nonclustered index.
Foreign Keys
FK_EmployeeDepartmentHistory_Department_DepartmentID ( DepartmentID ) ref Department (DepartmentID) Foreign key constraint referencing Department.DepartmentID.
FK_EmployeeDepartmentHistory_Employee_BusinessEntityID ( BusinessEntityID ) ref Employee (BusinessEntityID) Foreign key constraint referencing Employee.EmployeeID.
FK_EmployeeDepartmentHistory_Shift_ShiftID ( ShiftID ) ref Shift (ShiftID) Foreign key constraint referencing Shift.ShiftID
Constraints
  CK_EmployeeDepartmentHistory_EndDate [EndDate]>=[StartDate] OR [EndDate] IS NULL
  CK_EmployeeDepartmentHistory_EndDate [EndDate]>=[StartDate] OR [EndDate] IS NULL


Table EmployeePayHistory

Employee pay history.

IdxField NameData TypeDescription
* BusinessEntityID int Employee identification number. Foreign key to Employee.BusinessEntityID.
* RateChangeDate datetime Date the change in pay is effective
* Rate money Salary hourly rate.
* PayFrequency tinyint 1 = Salary received monthly, 2 = Salary received biweekly
* ModifiedDate datetime DEFAULT getdate() Date and time the record was last updated.
Indexes
PK_EmployeePayHistory_BusinessEntityID_RateChangeDate ON BusinessEntityID, RateChangeDate Clustered index created by a primary key constraint.
Foreign Keys
FK_EmployeePayHistory_Employee_BusinessEntityID ( BusinessEntityID ) ref Employee (BusinessEntityID) Foreign key constraint referencing Employee.EmployeeID.
Constraints
  CK_EmployeePayHistory_Rate [Rate]>=(6.50) AND [Rate]<=(200.00)
  CK_EmployeePayHistory_PayFrequency [PayFrequency]=(2) OR [PayFrequency]=(1)


Table JobCandidate

Résumés submitted to Human Resources by job applicants.

IdxField NameData TypeDescription
* JobCandidateID int AUTOINCREMENT Primary key for JobCandidate records.
BusinessEntityID int Employee identification number if applicant was hired. Foreign key to Employee.BusinessEntityID.
  Resume xml Résumé in XML format.
* ModifiedDate datetime DEFAULT getdate() Date and time the record was last updated.
Indexes
PK_JobCandidate_JobCandidateID ON JobCandidateID Clustered index created by a primary key constraint.
IX_JobCandidate_BusinessEntityID ON BusinessEntityID Nonclustered index.
Foreign Keys
FK_JobCandidate_Employee_BusinessEntityID ( BusinessEntityID ) ref Employee (BusinessEntityID) Foreign key constraint referencing Employee.EmployeeID.


Table Shift

Work shift lookup table.

IdxField NameData TypeDescription
* ShiftID tinyint AUTOINCREMENT Primary key for Shift records.
* Name name Shift description.
* StartTime time(16) Shift start time.
* EndTime time(16) Shift end time.
* ModifiedDate datetime DEFAULT getdate() Date and time the record was last updated.
Indexes
PK_Shift_ShiftID ON ShiftID Clustered index created by a primary key constraint.
AK_Shift_Name ON Name Unique nonclustered index.
AK_Shift_StartTime_EndTime ON StartTime, EndTime Unique nonclustered index.