Database reference - AdventureWorks2008

AdventureWorks2008 -  tables -  [HumanResources].[EmployeeDepartmentHistory]

Description

Employee department transfers. 

Table properties

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

Columns

columndatatypelengthbytesdefaultnullsPKFKUQcomputedcomment
BusinessEntityIDint104 no  composite PK  Employee.BusinessEntityID      no  Employee identification number. Foreign key to Employee.BusinessEntityID. 
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. 
StartDatedate103 no  composite PK    no  Date the employee started work in the department. 
EndDatedate103 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_BusinessEntityID_StartDate_DepartmentIDclustered, unique, primary key located on PRIMARYBusinessEntityID, 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_Employee_BusinessEntityIDBusinessEntityIDEmployee.BusinessEntityID   Foreign key constraint referencing Employee.EmployeeID. 
FK_EmployeeDepartmentHistory_Department_DepartmentIDDepartmentIDDepartment.DepartmentID   Foreign key constraint referencing Department.DepartmentID. 
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_BusinessEntityID_Star...vEmployeeDepartmentvEmployeeDepartmentHistory

Objects that depend on [HumanResources].[EmployeeDepartmentHistory]

nameobject typedatabaseserverlevel
vEmployeeDepartmentviewAdventureWorks2008SPRING\KATMAI1
vEmployeeDepartmentHistoryviewAdventureWorks2008SPRING\KATMAI1

Sample rows

BusinessEntityIDDepartmentIDShiftIDStartDateEndDateModifiedDate
1  16  1  2/15/1999 12:00:00 AM  NULL  2/14/1999 12:00:00 AM 
2  1  1  3/3/1998 12:00:00 AM  NULL  3/2/1998 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  1  1  2/6/1998 12:00:00 AM  NULL  2/5/1998 12:00:00 AM 
6  1  1  2/24/1998 12:00:00 AM  NULL  2/23/1998 12:00:00 AM 
7  6  1  3/12/1999 12:00:00 AM  NULL  3/11/1999 12:00:00 AM 
8  6  1  1/30/1999 12:00:00 AM  NULL  1/29/1999 12:00:00 AM 
9  6  1  2/17/1999 12:00:00 AM  NULL  2/16/1999 12:00:00 AM 

Code

CREATE TABLE [HumanResources].[EmployeeDepartmentHistory](
    [BusinessEntityID] [int] NOT NULL,
    [DepartmentID] [smallint] NOT NULL,
    [BusinessEntityID] [int] NOT NULL,
    [ShiftID] [tinyint] NOT NULL,
    [StartDate] [date] NOT NULL,
    [EndDate] [date] NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID] PRIMARY KEY CLUSTERED 
(
    [BusinessEntityID] 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_BusinessEntityID] FOREIGN KEY([BusinessEntityID])
REFERENCES [HumanResources].[Employee] ([BusinessEntityID])
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] CHECK CONSTRAINT [FK_EmployeeDepartmentHistory_Employee_BusinessEntityID]
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]



Documentation generated by SqlSpec