Database reference - AdventureWorks

AdventureWorks -  tables -  [HumanResources].[EmployeeDepartmentHistory]

Description

Employee department transfers. 

Table properties

namevalue
name[HumanResources].[EmployeeDepartmentHistory]
createdMar 19 2009 9:08PM
modifiedMar 19 2009 9:08PM 
ansi nullson 
quoted identifieron 
row count296
Size of data16 kb
Size of indexes48 kb
Maximum size of a single row31 bytes

Columns

columndatatypelengthbytesdefaultnullsPKFKUQcomputedcomment
EmployeeIDint104 no  composite PK  Employee.EmployeeID      no  Employee identification number. Foreign key to Employee.EmployeeID. 
DepartmentIDsmallint52 no  composite PK  Department.DepartmentID      no  Department in which the employee worked including currently. Foreign key to Department.DepartmentID. 
ShiftIDtinyint31 no  composite PK  Shift.ShiftID      no  Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID. 
StartDatedatetime238 no  composite PK    no  Date the employee started work in the department. 
EndDatedatetime238 yes      no  Date the employee left the department. NULL = Current department. 
ModifiedDatedatetime238(getdate())no      no  Date and time the record was last updated. 

Indexes

namedescriptioncolumncomment
IX_EmployeeDepartmentHistory_DepartmentIDnonclustered located on PRIMARYDepartmentIDNonclustered index. 
IX_EmployeeDepartmentHistory_ShiftIDnonclustered located on PRIMARYShiftIDNonclustered index. 
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentIDclustered, unique, primary key located on PRIMARYEmployeeID, StartDate, DepartmentID, ShiftIDClustered index created by a primary key constraint. 

References

name
Department
Employee
Shift

Foreign key graph

DepartmentEmployeeShiftEmployeeDepartmentHistory

Foreign keys

namecolumnsforeign columnstypecomment
FK_EmployeeDepartmentHistory_Department_DepartmentIDDepartmentIDDepartment.DepartmentID   Foreign key constraint referencing Department.DepartmentID. 
FK_EmployeeDepartmentHistory_Employee_EmployeeIDEmployeeIDEmployee.EmployeeID   Foreign key constraint referencing Employee.EmployeeID. 
FK_EmployeeDepartmentHistory_Shift_ShiftIDShiftIDShift.ShiftID   Foreign key constraint referencing Shift.ShiftID 

Check constraints

namecolumncomment
CK_EmployeeDepartmentHistory_EndDateStartDate  EndDate    Check constraint [EndDate] >= [StartDate] OR [EndDate] IS NUL 

Defaults

default namecolumncomment
DF_EmployeeDepartmentHistory_ModifiedDateModifiedDateDefault constraint value of GETDATE() 

Dependency graph

DF_EmployeeDepartmentHistory_ModifiedDate...EmployeeDepartmentHistoryCK_EmployeeDepartmentHistory_EndDate...IX_EmployeeDepartmentHistory_DepartmentID...IX_EmployeeDepartmentHistory_ShiftID...PK_EmployeeDepartmentHistory_EmployeeID_StartDate_...vEmployeeDepartmentvEmployeeDepartmentHistory

Objects that depend on [HumanResources].[EmployeeDepartmentHistory]

nameobject typelevel
vEmployeeDepartmentview1
vEmployeeDepartmentHistoryview1

Sample rows

EmployeeIDDepartmentIDShiftIDStartDateEndDateModifiedDate
1  7  1  7/31/1996 12:00:00 AM  NULL  7/30/1996 12:00:00 AM 
2  4  1  2/26/1997 12:00:00 AM  NULL  2/25/1997 12:00:00 AM 
3  1  1  12/12/1997 12:00:00 AM  NULL  12/11/1997 12:00:00 AM 
4  1  1  1/5/1998 12:00:00 AM  6/30/2000 12:00:00 AM  6/28/2000 12:00:00 AM 
4  2  1  7/1/2000 12:00:00 AM  NULL  6/30/2000 12:00:00 AM 
5  2  1  1/11/1998 12:00:00 AM  NULL  1/10/1998 12:00:00 AM 
6  5  1  1/20/1998 12:00:00 AM  8/15/1999 12:00:00 AM  8/13/1999 12:00:00 AM 
6  4  1  8/16/1999 12:00:00 AM  NULL  8/15/1999 12:00:00 AM 
7  7  3  1/26/1998 12:00:00 AM  NULL  1/25/1998 12:00:00 AM 
8  7  1  2/6/1998 12:00:00 AM  NULL  2/5/1998 12:00:00 AM 

Code

CREATE TABLE [HumanResources].[EmployeeDepartmentHistory](
    [EmployeeID] [int] NOT NULL,
    [DepartmentID] [smallint] NOT NULL,
    [EmployeeID] [int] NOT NULL,
    [ShiftID] [tinyint] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID] PRIMARY KEY CLUSTERED 
(
    [EmployeeID] ASC,
    [StartDate] ASC,
    [DepartmentID] ASC,
    [ShiftID] 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].[EmployeeDepartmentHistory]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeDepartmentHistory_Department_DepartmentID] FOREIGN KEY([DepartmentID])
REFERENCES [HumanResources].[Department] ([DepartmentID])
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] CHECK CONSTRAINT [FK_EmployeeDepartmentHistory_Department_DepartmentID]
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeDepartmentHistory_Employee_EmployeeID] FOREIGN KEY([EmployeeID])
REFERENCES [HumanResources].[Employee] ([EmployeeID])
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] CHECK CONSTRAINT [FK_EmployeeDepartmentHistory_Employee_EmployeeID]
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeDepartmentHistory_Shift_ShiftID] FOREIGN KEY([ShiftID])
REFERENCES [HumanResources].[Shift] ([ShiftID])
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] CHECK CONSTRAINT [FK_EmployeeDepartmentHistory_Shift_ShiftID]
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory]  WITH CHECK ADD  CONSTRAINT [CK_EmployeeDepartmentHistory_EndDate] CHECK  (([EndDate]>=[StartDate] OR [EndDate] IS NULL))
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] CHECK CONSTRAINT [CK_EmployeeDepartmentHistory_EndDate]
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] ADD  CONSTRAINT [DF_EmployeeDepartmentHistory_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]

Powered by Yoyodyne © 1971