Difference between revisions of "HumanResources.Employee (table)"

From dbscript Online Help
Jump to: navigation, search
Line 12: Line 12:
  
  
 +
=== Columns ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 18: Line 19:
 
| '''Nullable
 
| '''Nullable
 
| '''Default
 
| '''Default
−
| '''Description / PK / Index
+
| '''Description
 
 
 
|- valign="top"
 
|- valign="top"
Line 25: Line 26:
 
| not null
 
| not null
 
|  
 
|  
−
| Primary key for Employee records.<br />PK_Employee_EmployeeID
+
| Primary key for Employee records.
 
|- valign="top"
 
|- valign="top"
 
| NationalIDNumber
 
| NationalIDNumber
Line 31: Line 32:
 
| not null
 
| not null
 
|  
 
|  
−
| Unique national identification number such as a social security number.<br />AK_Employee_NationalIDNumber
+
| Unique national identification number such as a social security number.
 
|- valign="top"
 
|- valign="top"
 
| ContactID
 
| ContactID
Line 37: Line 38:
 
| not null
 
| not null
 
|  
 
|  
−
| Identifies the employee in the Contact table. Foreign key to Contact.ContactID.<br />
+
| Identifies the employee in the Contact table. Foreign key to Contact.ContactID.
 
|- valign="top"
 
|- valign="top"
 
| LoginID
 
| LoginID
Line 43: Line 44:
 
| not null
 
| not null
 
|  
 
|  
−
| Network login.<br />AK_Employee_LoginID
+
| Network login.
 
|- valign="top"
 
|- valign="top"
 
| ManagerID
 
| ManagerID
Line 49: Line 50:
 
| null
 
| null
 
|  
 
|  
−
| Manager to whom the employee is assigned. Foreign Key to Employee.M<br />IX_Employee_ManagerID
+
| Manager to whom the employee is assigned. Foreign Key to Employee.M
 
|- valign="top"
 
|- valign="top"
 
| Title
 
| Title
Line 55: Line 56:
 
| not null
 
| not null
 
|  
 
|  
−
| Work title such as Buyer or Sales Representative.<br />
+
| Work title such as Buyer or Sales Representative.
 
|- valign="top"
 
|- valign="top"
 
| BirthDate
 
| BirthDate
Line 61: Line 62:
 
| not null
 
| not null
 
|  
 
|  
−
| Date of birth.<br />
+
| Date of birth.
 
|- valign="top"
 
|- valign="top"
 
| MaritalStatus
 
| MaritalStatus
Line 67: Line 68:
 
| not null
 
| not null
 
|  
 
|  
−
| M = Married, S = Single<br />
+
| M = Married, S = Single
 
|- valign="top"
 
|- valign="top"
 
| Gender
 
| Gender
Line 73: Line 74:
 
| not null
 
| not null
 
|  
 
|  
−
| M = Male, F = Female<br />
+
| M = Male, F = Female
 
|- valign="top"
 
|- valign="top"
 
| HireDate
 
| HireDate
Line 79: Line 80:
 
| not null
 
| not null
 
|  
 
|  
−
| Employee hired on this date.<br />
+
| Employee hired on this date.
 
|- valign="top"
 
|- valign="top"
 
| SalariedFlag
 
| SalariedFlag
Line 85: Line 86:
 
| not null
 
| not null
 
| ((1))
 
| ((1))
−
| Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.<br />
+
| Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.
 
|- valign="top"
 
|- valign="top"
 
| VacationHours
 
| VacationHours
Line 91: Line 92:
 
| not null
 
| not null
 
| ((0))
 
| ((0))
−
| Number of available vacation hours.<br />
+
| Number of available vacation hours.
 
|- valign="top"
 
|- valign="top"
 
| SickLeaveHours
 
| SickLeaveHours
Line 97: Line 98:
 
| not null
 
| not null
 
| ((0))
 
| ((0))
−
| Number of available sick leave hours.<br />
+
| Number of available sick leave hours.
 
|- valign="top"
 
|- valign="top"
 
| CurrentFlag
 
| CurrentFlag
Line 103: Line 104:
 
| not null
 
| not null
 
| ((1))
 
| ((1))
−
| 0 = Inactive, 1 = Active<br />
+
| 0 = Inactive, 1 = Active
 
|- valign="top"
 
|- valign="top"
 
| rowguid
 
| rowguid
Line 109: Line 110:
 
| not null
 
| not null
 
| (newid())
 
| (newid())
−
| ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.<br />AK_Employee_rowguid
+
| ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
 
|- valign="top"
 
|- valign="top"
 
| ModifiedDate
 
| ModifiedDate
Line 115: Line 116:
 
| not null
 
| not null
 
| (getdate())
 
| (getdate())
−
| Date and time the record was last updated.<br />
+
| Date and time the record was last updated.
 
|}
 
|}
  
 +
=== Primary Key ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Primary Key
 +
| '''Columns
 +
|-
 +
| PK_Employee_EmployeeID
 +
| EmployeeID
 +
|}
 +
 +
=== Indexes ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Index
 +
| '''Type
 +
| '''Columns
 +
 +
|-
 +
| AK_Employee_LoginID
 +
| Unique
 +
| LoginID
 +
|-
 +
| AK_Employee_NationalIDNumber
 +
| Unique
 +
| NationalIDNumber
 +
|-
 +
| AK_Employee_rowguid
 +
| Unique
 +
| rowguid
 +
|-
 +
| IX_Employee_ManagerID
 +
|
 +
| ManagerID
 +
|}
  
 +
=== Check Constraints ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Check Constraint
 +
| '''Expression
 +
| '''Description
 +
 +
|-
 +
| CK_Employee_BirthDate
 +
| ([BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate()))
 +
| Check constraint [BirthDate] >= '1930-01-01' AND [BirthDate] <= dateadd(year,(-18),GETDATE())
 +
|-
 +
| CK_Employee_Gender
 +
| (upper([Gender])='F' OR upper([Gender])='M')
 +
| Check constraint [Gender]='f' OR [Gender]='m' OR [Gender]='F' OR [Gender]='M'
 +
|-
 +
| CK_Employee_HireDate
 +
| ([HireDate]>='1996-07-01' AND [HireDate]<=dateadd(day,(1),getdate()))
 +
| Check constraint [HireDate] >= '1996-07-01' AND [HireDate] <= dateadd(day,(1),GETDATE())
 +
|-
 +
| CK_Employee_MaritalStatus
 +
| (upper([MaritalStatus])='S' OR upper([MaritalStatus])='M')
 +
| Check constraint [MaritalStatus]='s' OR [MaritalStatus]='m' OR [MaritalStatus]='S' OR [MaritalStatus]='M'
 +
|-
 +
| CK_Employee_SickLeaveHours
 +
| ([SickLeaveHours]>=(0) AND [SickLeaveHours]<=(120))
 +
| Check constraint [SickLeaveHours] >= (0) AND [SickLeaveHours] <= (120)
 +
|-
 +
| CK_Employee_VacationHours
 +
| ([VacationHours]>=(-40) AND [VacationHours]<=(240))
 +
| Check constraint [VacationHours] >= (-40) AND [VacationHours] <= (240)
 +
|}
  
 +
=== Foreign Keys ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 136: Line 204:
 
|}
 
|}
  
−
 
+
=== Detail Tables ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 173: Line 241:
 
|}
 
|}
  
−
 
+
=== Triggers ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 184: Line 252:
 
|}
 
|}
  
 +
Trigger dEmployee
 +
<pre>
 +
CREATE TRIGGER [HumanResources].[dEmployee] ON [HumanResources].[Employee]
 +
INSTEAD OF DELETE NOT FOR REPLICATION AS
 +
BEGIN
 +
    DECLARE @Count int;
 +
 +
    SET @Count = @@ROWCOUNT;
 +
    IF @Count = 0
 +
        RETURN;
 +
 +
    SET NOCOUNT ON;
 +
 +
    BEGIN
 +
        RAISERROR
 +
            (N'Employees cannot be deleted. They can only be marked as not current.', -- Message
 +
            10, -- Severity.
 +
            1); -- State.
  
 +
        -- Rollback any active or uncommittable transactions
 +
        IF @@TRANCOUNT > 0
 +
        BEGIN
 +
            ROLLBACK TRANSACTION;
 +
        END
 +
    END;
 +
END;
 +
</pre>
 +
 +
=== References ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 195: Line 291:
 
| Type
 
| Type
 
| [[dbo.Flag_(type)|dbo.Flag]]
 
| [[dbo.Flag_(type)|dbo.Flag]]
 +
 +
|-
 +
| Schema
 +
| Schema
 +
| [[HumanResources_(schema)|HumanResources]]
  
 
|}
 
|}
  
−
 
+
=== Dependencies ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 261: Line 362:
  
 
|}
 
|}
−
 
  
  

Revision as of 23:06, 2 February 2010

wikibot

Table HumanResources.Employee
Description Employee information such as salary, department, and title.


Columns

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

Primary Key

Primary Key Columns
PK_Employee_EmployeeID EmployeeID

Indexes

Index Type Columns
AK_Employee_LoginID Unique LoginID
AK_Employee_NationalIDNumber Unique NationalIDNumber
AK_Employee_rowguid Unique rowguid
IX_Employee_ManagerID ManagerID

Check Constraints

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

Foreign Keys

Relation Column Referenced Column
Person.Contact ContactID ContactID
HumanResources.Employee ManagerID EmployeeID

Detail Tables

Detail Table Column Referencing Column
HumanResources.Employee EmployeeID ManagerID
HumanResources.EmployeeAddress EmployeeID EmployeeID
HumanResources.EmployeeDepartmentHistory EmployeeID EmployeeID
HumanResources.EmployeePayHistory EmployeeID EmployeeID
HumanResources.JobCandidate EmployeeID EmployeeID
Purchasing.PurchaseOrderHeader EmployeeID EmployeeID
Sales.SalesPerson EmployeeID SalesPersonID

Triggers

Triggers Type
dEmployee INSTEAD OF DELETE

Trigger dEmployee 

CREATE TRIGGER [HumanResources].[dEmployee] ON [HumanResources].[Employee] 
INSTEAD OF DELETE NOT FOR REPLICATION AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN
        RAISERROR
            (N'Employees cannot be deleted. They can only be marked as not current.', -- Message
            10, -- Severity.
            1); -- State.

        -- Rollback any active or uncommittable transactions
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END
    END;
END;

References

Dependency Type Object Type Referenced Object
Data Type Type dbo.Flag
Schema Schema HumanResources

Dependencies

Reference Type Object Type Referencing Object
Select View HumanResources.vEmployee
Select View HumanResources.vEmployeeDepartment
Select View HumanResources.vEmployeeDepartmentHistory
Select View Sales.vSalesPerson
Select View Sales.vSalesPersonSalesByFiscalYears
Select Procedure dbo.uspGetEmployeeManagers
Select Procedure dbo.uspGetManagerEmployees
Update Procedure HumanResources.uspUpdateEmployeeHireInfo
Update Procedure HumanResources.uspUpdateEmployeeLogin
Update Procedure HumanResources.uspUpdateEmployeePersonalInfo
Select SQL table-valued-function dbo.ufnGetContactInformation


automatically generated

Table HumanResources.Employee
Description Employee information such as salary, department, and title.


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


Relation Column Referenced Column
Person.Contact ContactID ContactID
HumanResources.Employee ManagerID EmployeeID


Detail Table Column Referencing Column
HumanResources.Employee EmployeeID ManagerID
HumanResources.EmployeeAddress EmployeeID EmployeeID
HumanResources.EmployeeDepartmentHistory EmployeeID EmployeeID
HumanResources.EmployeePayHistory EmployeeID EmployeeID
HumanResources.JobCandidate EmployeeID EmployeeID
Purchasing.PurchaseOrderHeader EmployeeID EmployeeID
Sales.SalesPerson EmployeeID SalesPersonID


Triggers Type
dEmployee INSTEAD OF DELETE


Dependency Type Object Type Referenced Object
Data Type Type dbo.Flag
Schema Schema HumanResources


Reference Type Object Type Referencing Object
Select View HumanResources.vEmployee
Select View HumanResources.vEmployeeDepartment
Select View HumanResources.vEmployeeDepartmentHistory
Select View Sales.vSalesPerson
Select View Sales.vSalesPersonSalesByFiscalYears
Select Procedure dbo.uspGetEmployeeManagers
Select Procedure dbo.uspGetManagerEmployees
Update Procedure HumanResources.uspUpdateEmployeeHireInfo
Update Procedure HumanResources.uspUpdateEmployeeLogin
Update Procedure HumanResources.uspUpdateEmployeePersonalInfo
Select SQL table-valued-function dbo.ufnGetContactInformation
Retrieved from "http://dbshelp.devio.at/index.php?title=HumanResources.Employee_(table)&oldid=2131"