Person.Person Table

Description

Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.

Properties

Creation Date	17/07/2014 16:11
File Group	PRIMARY
Text File Group	PRIMARY
System Object
Published for Replication
Rows	19972
Data Space Used	30,504.00 KB
Index Space Used	1,680.00 KB

Columns

Column Name	Description	Datatype	Length	Default
BusinessEntityID	Primary key for Person records.	Integer	4
PersonType	Primary type of person: SC = Store Contact, IN = Individual (retail) customer, SP = Sales person, EM = Employee (non-sales), VC = Vendor contact, GC = General contact	WChar	2
NameStyle	0 = The data in FirstName and LastName are stored in western style (first name, last name) order. 1 = Eastern style (last name, first name) order.	dbo.NameStyle	1	((0))
Title	A courtesy title. For example, Mr. or Ms.	VarWChar	8
FirstName	First name of the person.	dbo.Name	50
MiddleName	Middle name or middle initial of the person.	dbo.Name	50
LastName	Last name of the person.	dbo.Name	50
Suffix	Surname suffix. For example, Sr. or Jr.	VarWChar	10
EmailPromotion	0 = Contact does not wish to receive e-mail promotions, 1 = Contact does wish to receive e-mail promotions from AdventureWorks, 2 = Contact does wish to receive e-mail promotions from AdventureWorks and selected partners.	Integer	4	((0))
AdditionalContactInfo	Additional contact information about the person stored in xml format.	LongVarWChar	16
Demographics	Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis.	LongVarWChar	16
rowguid	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	GUID	16	(newid())
ModifiedDate	Date and time the record was last updated.	DBTimeStamp	4	(getdate())

Indexes

Index	Description	Primary	Unique
AK_Person_rowguid	Unique nonclustered index. Used to support replication samples.
IX_Person_LastName_FirstName_MiddleName
PK_Person_BusinessEntityID	Primary key (clustered) constraint

Check Constraints

Name	Description	Expression
CK_Person_EmailPromotion	Check constraint [EmailPromotion] >= (0) AND [EmailPromotion] <= (2)	([EmailPromotion]>=(0) AND [EmailPromotion]<=(2))
CK_Person_PersonType	Check constraint [PersonType] is one of SC, VC, IN, EM or SP.	([PersonType] IS NULL OR (upper([PersonType])='GC' OR upper([PersonType])='SP' OR upper([PersonType])='EM' OR upper([PersonType])='IN' OR upper([PersonType])='VC' OR upper([PersonType])='SC'))

Name

Description

Expression

CK_Person_EmailPromotion

Check constraint [EmailPromotion] >= (0) AND [EmailPromotion] <= (2)

([EmailPromotion]>=(0) AND [EmailPromotion]<=(2))

CK_Person_PersonType

Check constraint [PersonType] is one of SC, VC, IN, EM or SP.

([PersonType] IS NULL OR (upper([PersonType])='GC' OR upper([PersonType])='SP' OR upper([PersonType])='EM' OR upper([PersonType])='IN' OR upper([PersonType])='VC' OR upper([PersonType])='SC'))

Triggers

Trigger	Description
iuPerson	AFTER INSERT, UPDATE trigger inserting Individual only if the Customer does not exist in the Store table and setting the ModifiedDate column in the Person table to the current date.

Relationships

Relationship	Description
FK_BusinessEntityContact_Person_PersonID	Foreign key constraint referencing Person.BusinessEntityID.
FK_Customer_Person_PersonID	Foreign key constraint referencing Person.BusinessEntityID.
FK_EmailAddress_Person_BusinessEntityID	Foreign key constraint referencing Person.BusinessEntityID.
FK_Employee_Person_BusinessEntityID	Foreign key constraint referencing Person.BusinessEntityID.
FK_Password_Person_BusinessEntityID	Foreign key constraint referencing Person.BusinessEntityID.
FK_Person_BusinessEntity_BusinessEntityID	Foreign key constraint referencing BusinessEntity.BusinessEntityID.
FK_PersonCreditCard_Person_BusinessEntityID	Foreign key constraint referencing Person.BusinessEntityID.
FK_PersonPhone_Person_BusinessEntityID	Foreign key constraint referencing Person.BusinessEntityID.

Objects that depend on Person.Person

Database Object	Object Type	Description	Dep Level
Person.BusinessEntityContact	Table	Cross-reference table mapping stores, vendors, and employees to people	1
Sales.Customer	Table	Current customer information. Also see the Person and Store tables.	1
dEmployee	Trigger	INSTEAD OF DELETE trigger which keeps Employees from being deleted.	2
Production.Document	Table	Product maintenance documents.	2
Person.EmailAddress	Table	Where to send a person email.	1
HumanResources.Employee	Table	Employee information such as salary, department, and title.	1
HumanResources.EmployeeDepartmentHistory	Table	Employee department transfers.	2
HumanResources.EmployeePayHistory	Table	Employee pay history.	2
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.	2
iPurchaseOrderDetail	Trigger	AFTER INSERT trigger that inserts a row in the TransactionHistory table and updates the PurchaseOrderHeader.SubTotal column.	4
iuPerson	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 Person table to the current date.	1
HumanResources.JobCandidate	Table	Résumés submitted to Human Resources by job applicants.	2
Person.Password	Table	One way hashed authentication information	1
Sales.PersonCreditCard	Table	Cross-reference table mapping people to their credit card information in the CreditCard table.	1
Person.PersonPhone	Table	Telephone number and type of a person.	1
Production.ProductDocument	Table	Cross-reference table mapping products to related product documents.	3
Purchasing.PurchaseOrderDetail	Table	Individual products associated with a specific purchase order. See PurchaseOrderHeader.	3
Purchasing.PurchaseOrderHeader	Table	General purchase order information. See PurchaseOrderDetail.	2
Sales.SalesOrderDetail	Table	Individual products associated with a specific sales order. See SalesOrderHeader.	1
Sales.SalesPerson	Table	Sales representative current information.	2
Sales.SalesPersonQuotaHistory	Table	Sales performance tracking.	3
Sales.SalesTerritoryHistory	Table	Sales representative transfers to other sales territories.	3
Sales.Store	Table	Customers (resellers) of Adventure Works products.	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.	4
uPurchaseOrderHeader	Trigger	AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the PurchaseOrderHeader table.	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
dbo.uspSearchCandidateResumes	Stored Procedure		3
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.	2
HumanResources.uspUpdateEmployeeLogin	Stored Procedure	Updates the Employee table with the values specified in the input parameters for the given BusinessEntityID.	2
HumanResources.uspUpdateEmployeePersonalInfo	Stored Procedure	Updates the Employee table with the values specified in the input parameters for the given EmployeeID.	2
Person.vAdditionalContactInfo	View	Displays the contact name and content from each element in the xml column AdditionalContactInfo for that person.	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.	1
HumanResources.vJobCandidate	View	Job candidate names and resumes.	3
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.	3
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.	3
Sales.vPersonDemographics	View	Displays the content from each element in the xml column Demographics for each customer in the Person.Person table.	1
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.vStoreWithAddresses	View	Stores (including store addresses) that sell Adventure Works Cycles products to consumers.	4
Sales.vStoreWithContacts	View	Stores (including store contacts) that sell Adventure Works Cycles products to consumers.	1
Sales.vStoreWithDemographics	View	Stores (including demographics) that sell Adventure Works Cycles products to consumers.	4
Purchasing.vVendorWithContacts	View	Vendor (company) names and the names of vendor employees to contact.	1

Objects that Person.Person depends on

Database Object	Object Type	Description	Dep Level
Person.BusinessEntity	Table	Source of the ID that connects vendors, customers, and employees with address and contact information.	1
dbo.Name	User Defined Data Type		1
dbo.NameStyle	User Defined Data Type		1

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Person].[Person](
    [BusinessEntityID] [int] NOT NULL,
    [PersonType] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [NameStyle] [dbo].[NameStyle] NOT NULL,
    [Title] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [FirstName] [dbo].[Name] NOT NULL,
    [MiddleName] [dbo].[Name] NULL,
    [LastName] [dbo].[Name] NOT NULL,
    [Suffix] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [EmailPromotion] [int] NOT NULL,
    [AdditionalContactInfo] [xml](CONTENT [Person].[AdditionalContactInfoSchemaCollection]) NULL,
    [Demographics] [xml](CONTENT [Person].[IndividualSurveySchemaCollection]) NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Person_BusinessEntityID] PRIMARY KEY CLUSTERED 
(
    [BusinessEntityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER TABLE [Person].[Person] ADD  CONSTRAINT [DF_Person_NameStyle]  DEFAULT ((0)) FOR [NameStyle]
ALTER TABLE [Person].[Person] ADD  CONSTRAINT [DF_Person_EmailPromotion]  DEFAULT ((0)) FOR [EmailPromotion]
ALTER TABLE [Person].[Person] ADD  CONSTRAINT [DF_Person_rowguid]  DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [Person].[Person] ADD  CONSTRAINT [DF_Person_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
ALTER TABLE [Person].[Person]  WITH CHECK ADD  CONSTRAINT [FK_Person_BusinessEntity_BusinessEntityID] FOREIGN KEY([BusinessEntityID])
REFERENCES [Person].[BusinessEntity] ([BusinessEntityID])
ALTER TABLE [Person].[Person] CHECK CONSTRAINT [FK_Person_BusinessEntity_BusinessEntityID]
ALTER TABLE [Person].[Person]  WITH CHECK ADD  CONSTRAINT [CK_Person_EmailPromotion] CHECK  (([EmailPromotion]>=(0) AND [EmailPromotion]<=(2)))
ALTER TABLE [Person].[Person] CHECK CONSTRAINT [CK_Person_EmailPromotion]
ALTER TABLE [Person].[Person]  WITH CHECK ADD  CONSTRAINT [CK_Person_PersonType] CHECK  (([PersonType] IS NULL OR (upper([PersonType])='GC' OR upper([PersonType])='SP' OR upper([PersonType])='EM' OR upper([PersonType])='IN' OR upper([PersonType])='VC' OR upper([PersonType])='SC')))
ALTER TABLE [Person].[Person] CHECK CONSTRAINT [CK_Person_PersonType]

Related Objects

Person Schema
AdventureWorks Database