Dataedo
AdventureWorks
Data Dictionary
Table of Contents
1. AdventureWorks ...7 2. Business Entities ... 8 2.1. Tables ... 8 2.1.1. Table: Person.Address ...8 2.1.2. Table: Person.AddressType ...9 2.1.3. Table: Person.BusinessEntity ...10 2.1.4. Table: Person.BusinessEntityAddress ... 11 2.1.5. Table: Person.BusinessEntityContact ... 12 2.1.6. Table: Person.ContactType ...12 2.1.7. Table: Person.CountryRegion ... 13 2.1.8. Table: Person.Person ...14 2.1.9. Table: Person.StateProvince ... 15 3. Person ...17 3.1. Tables ... 17 3.1.1. Table: Person.ContactType ...17 3.1.2. Table: Person.CountryRegion ... 18 3.1.3. Table: Person.EmailAddress ... 18 3.1.4. Table: Person.Password ...19 3.1.5. Table: Person.Person ...19 3.1.6. Table: Person.PersonPhone ...21 3.1.7. Table: Person.PhoneNumberType ...22 3.2. Views ... 22 3.2.1. View: Person.vAdditionalContactInfo ...22 3.2.2. View: Person.vStateProvinceCountryRegion ... 23 4. Human Resources ...24 4.1. Tables ... 24 4.1.1. Table: HumanResources.Department ... 24 4.1.2. Table: HumanResources.Employee ... 25 4.1.3. Table: HumanResources.EmployeeDepartmentHistory ... 26 4.1.4. Table: HumanResources.EmployeePayHistory ... 27 4.1.5. Table: HumanResources.JobCandidate ... 28 4.1.6. Table: HumanResources.Shift ...28 4.2. Views ... 29 4.2.1. View: HumanResources.vEmployee ... 29 4.2.2. View: HumanResources.vEmployeeDepartment ...30 4.2.3. View: HumanResources.vEmployeeDepartmentHistory ...30 4.2.4. View: HumanResources.vJobCandidate ... 30 4.2.5. View: HumanResources.vJobCandidateEducation ...31 4.2.6. View: HumanResources.vJobCandidateEmployment ... 31 4.3. Procedures ... 32 4.3.1. Procedure: dbo.uspGetEmployeeManagers ... 32 4.3.2. Procedure: dbo.uspGetManagerEmployees ... 32 4.3.3. Procedure: dbo.uspSearchCandidateResumes ... 32 4.3.4. Procedure: HumanResources.uspUpdateEmployeeHireInfo ...324.3.6. Procedure: HumanResources.uspUpdateEmployeePersonalInfo ... 33 4.4. Functions ... 34 4.4.1. Function: dbo.ufnGetContactInformation ... 34 5. Products ... 35 5.1. Tables ... 35 5.1.1. Table: Production.Culture ... 35 5.1.2. Table: Production.Document ... 36 5.1.3. Table: Production.Illustration ...37 5.1.4. Table: Production.Product ... 37 5.1.5. Table: Production.ProductCategory ... 40 5.1.6. Table: Production.ProductDescription ... 41 5.1.7. Table: Production.ProductDocument ... 41 5.1.8. Table: Production.ProductModel ... 42 5.1.9. Table: Production.ProductModelIllustration ...43 5.1.10. Table: Production.ProductModelProductDescriptionCulture ... 43 5.1.11. Table: Production.ProductPhoto ...44 5.1.12. Table: Production.ProductProductPhoto ...45 5.1.13. Table: Production.ProductReview ...45 5.1.14. Table: Production.ProductSubcategory ... 46 5.1.15. Table: Production.UnitMeasure ...47 5.2. Views ... 48 5.2.1. View: Production.vProductAndDescription ... 48 5.2.2. View: Production.vProductModelCatalogDescription ...48 5.2.3. View: Production.vProductModelInstructions ... 49 5.3. Procedures ... 49 5.3.1. Procedure: dbo.uspGetBillOfMaterials ...49 5.3.2. Procedure: dbo.uspGetWhereUsedProductID ...49 5.4. Functions ... 50 5.4.1. Function: dbo.ufnGetDocumentStatusText ...50 5.4.2. Function: dbo.ufnGetProductDealerPrice ... 50 5.4.3. Function: dbo.ufnGetProductListPrice ... 50 5.4.4. Function: dbo.ufnGetProductStandardCost ... 50 5.4.5. Function: dbo.ufnGetStock ... 51 6. Manufacturing ... 52 6.1. Tables ... 52 6.1.1. Table: Production.BillOfMaterials ... 52 6.1.2. Table: Production.ProductCostHistory ...53 6.1.3. Table: Production.ProductListPriceHistory ...53 6.1.4. Table: Production.ScrapReason ...54 6.1.5. Table: Production.TransactionHistory ... 55 6.1.6. Table: Production.TransactionHistoryArchive ... 55 6.1.7. Table: Production.WorkOrder ...56 6.1.8. Table: Production.WorkOrderRouting ... 57 7. Purchasing ... 59
7.1.2. Table: Purchasing.PurchaseOrderDetail ... 60 7.1.3. Table: Purchasing.PurchaseOrderHeader ...61 7.1.4. Table: Purchasing.ShipMethod ... 62 7.1.5. Table: Purchasing.Vendor ... 63 7.2. Views ... 64 7.2.1. View: Purchasing.vVendorWithAddresses ... 64 7.2.2. View: Purchasing.vVendorWithContacts ... 65 7.3. Functions ... 65 7.3.1. Function: dbo.ufnGetPurchaseOrderStatusText ... 65 8. Inventory ... 66 8.1. Tables ... 66 8.1.1. Table: Production.Location ...66 8.1.2. Table: Production.ProductInventory ...67 9. Sales ... 68 9.1. Tables ... 68 9.1.1. Table: Sales.CountryRegionCurrency ... 68 9.1.2. Table: Sales.CreditCard ...69 9.1.3. Table: Sales.Currency ... 69 9.1.4. Table: Sales.CurrencyRate ...70 9.1.5. Table: Sales.Customer ...71 9.1.6. Table: Sales.PersonCreditCard ...72 9.1.7. Table: Sales.SalesOrderDetail ...73 9.1.8. Table: Sales.SalesOrderHeader ...74 9.1.9. Table: Sales.SalesOrderHeaderSalesReason ... 76 9.1.10. Table: Sales.SalesPerson ... 77 9.1.11. Table: Sales.SalesPersonQuotaHistory ... 78 9.1.12. Table: Sales.SalesReason ...78 9.1.13. Table: Sales.SalesTaxRate ...79 9.1.14. Table: Sales.SalesTerritory ... 80 9.1.15. Table: Sales.SalesTerritoryHistory ... 81 9.1.16. Table: Sales.ShoppingCartItem ... 82 9.1.17. Table: Sales.SpecialOffer ... 82 9.1.18. Table: Sales.SpecialOfferProduct ... 83 9.1.19. Table: Sales.Store ...84 9.2. Views ... 85 9.2.1. View: Sales.vIndividualCustomer ... 85 9.2.2. View: Sales.vPersonDemographics ... 86 9.2.3. View: Sales.vSalesPerson ... 86 9.2.4. View: Sales.vSalesPersonSalesByFiscalYears ... 87 9.2.5. View: Sales.vStoreWithAddresses ...87 9.2.6. View: Sales.vStoreWithContacts ... 87 9.2.7. View: Sales.vStoreWithDemographics ... 88 9.3. Functions ... 88 9.3.1. Function: dbo.ufnGetAccountingEndDate ... 88 9.3.2. Function: dbo.ufnGetAccountingStartDate ... 89
9.3.4. Function: dbo.ufnLeadingZeros ... 89 10. Admin ... 90 10.1. Tables ...90 10.1.1. Table: dbo.AWBuildVersion ... 90 10.1.2. Table: dbo.DatabaseLog ...90 10.1.3. Table: dbo.ErrorLog ... 90 10.2. Procedures ...91 10.2.1. Procedure: dbo.uspLogError ... 91 10.2.2. Procedure: dbo.uspPrintError ...91
Legend
Primary key Primary key disabled User-defined primary key Unique keyUnique key disabled User-defined unique key Active trigger
Disabled trigger Primary key relation
User-defined primary key relation Foreign key relation
User-defined foreign key relation Input
Output Input/Output
This is a sample Dataedo documentation - AdventureWorks - Microsoft SQL Server sample database.
2. Business Entities
Vendors, customers, and employees have common tables for addresses and contacts. Those tables are linked to one
table - BusinessEntity that holds ID for all vendors, customers, and employees tables.
2.1. Tables
2.1.1. Table: Person.Address
Street address information for customers, employees, and vendors.
Columns
Description Data type
Name
Primary key for Address records. int
AddressID
First street address line. nvarchar(60)
AddressLine1
Second street address line. nvarchar(60)
AddressLine2
Name of the city. nvarchar(30)
Description Data type
Name
Unique identification number for the state or province. Foreign key to StateProvince table.
int StateProvinceID
Postal code for the street address. nvarchar(15)
PostalCode
Latitude and longitude of this address. geography
SpatialLocation
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
uniqueidentifier rowguid
Date and time the record was last updated. datetime ModifiedDate
Links to
Name / Description Join TableFK_Address_StateProvince_StateProvinceID: Foreign key constraint referencing StateProvince.StateProvinceID. StateProvinceID = Person.StateProvince.StateProvinceID StateProvince
Linked from
Name / Description Join TableFK_BusinessEntityAddress_Address_AddressID: Foreign key constraint referencing Address.AddressID.
Person.BusinessEntityAddress.AddressI D = AddressID
BusinessEntityAddress
FK_SalesOrderHeader_Address_BillToAddressID: Foreign key constraint referencing Address.AddressID.
Sales.SalesOrderHeader.BillToAddressI D = AddressID
SalesOrderHeader
FK_SalesOrderHeader_Address_ShipToAddressID: Foreign key constraint referencing Address.AddressID.
Sales.SalesOrderHeader.ShipToAddres sID = AddressID SalesOrderHeader
Unique keys
Description Columns NamePrimary key (clustered) constraint AddressID
PK_Address_AddressID
Unique nonclustered index. Used to support replication samples. rowguid AK_Address_rowguid Nonclustered index. AddressLine1, AddressLine2, City, StateProvinceID, PostalCode IX_Address_AddressLine1_AddressL ine2_City_StateProvinceID_PostalCo de
Uses
Description Name Person.AddressForeign key constraint referencing StateProvince.StateProvinceID. Person.StateProvince
Used by
Description Name
Person.Address
Foreign key constraint referencing Address.AddressID. Person.BusinessEntityAddress
Foreign key constraint referencing Address.AddressID. Sales.SalesOrderHeader
Foreign key constraint referencing Address.AddressID. Sales.SalesOrderHeader
2.1.2. Table: Person.AddressType
Columns
Description Data type
Name
Primary key for AddressType records. int
AddressTypeID
Address type description. For example, Billing, Home, or Shipping. nvarchar(50)
Name
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
uniqueidentifier rowguid
Date and time the record was last updated. datetime ModifiedDate
Linked from
Name / Description Join TableFK_BusinessEntityAddress_AddressType_AddressTypeID: Foreign key constraint referencing AddressType.AddressTypeID.
Person.BusinessEntityAddress.Address TypeID = AddressTypeID BusinessEntityAddress
Unique keys
Description Columns NamePrimary key (clustered) constraint AddressTypeID
PK_AddressType_AddressTypeID
Unique nonclustered index. Name
AK_AddressType_Name
Unique nonclustered index. Used to support replication samples. rowguid AK_AddressType_rowguid
Used by
Description Name Person.AddressTypeForeign key constraint referencing AddressType.AddressTypeID. Person.BusinessEntityAddress
2.1.3. Table: Person.BusinessEntity
Source of the ID that connects vendors, customers, and employees with address and contact information.
Columns
Description Data type
Name
Primary key for all customers, vendors, and employees. int
BusinessEntityID
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
uniqueidentifier rowguid
Date and time the record was last updated. datetime ModifiedDate
Linked from
Name / Description Join TableFK_BusinessEntityAddress_BusinessEntity_BusinessEntityID: Foreign key constraint referencing BusinessEntity.BusinessEntityID.
Person.BusinessEntityAddress.Business EntityID = BusinessEntityID
BusinessEntityAddress
FK_BusinessEntityContact_BusinessEntity_BusinessEntityID: Foreign key constraint referencing BusinessEntity.BusinessEntityID.
Person.BusinessEntityContact.Business EntityID = BusinessEntityID
BusinessEntityContact
FK_Person_BusinessEntity_BusinessEntityID: Foreign key constraint referencing BusinessEntity.BusinessEntityID.
Person.Person.BusinessEntityID = BusinessEntityID
Person
FK_Store_BusinessEntity_BusinessEntityID: Foreign key constraint referencing BusinessEntity.BusinessEntityID
Sales.Store.BusinessEntityID = BusinessEntityID
Store
FK_Vendor_BusinessEntity_BusinessEntityID: Foreign key constraint referencing BusinessEntity.BusinessEntityID
Purchasing.Vendor.BusinessEntityID = BusinessEntityID
Unique keys
Description Columns
Name
Primary key (clustered) constraint BusinessEntityID
PK_BusinessEntity_BusinessEntityID
Unique nonclustered index. Used to support replication samples. rowguid AK_BusinessEntity_rowguid
Used by
Description Name Person.BusinessEntityForeign key constraint referencing BusinessEntity.BusinessEntityID. Person.BusinessEntityAddress
Foreign key constraint referencing BusinessEntity.BusinessEntityID. Person.BusinessEntityContact
Foreign key constraint referencing BusinessEntity.BusinessEntityID. Person.Person
Foreign key constraint referencing BusinessEntity.BusinessEntityID Purchasing.Vendor
Foreign key constraint referencing BusinessEntity.BusinessEntityID Sales.Store
2.1.4. Table: Person.BusinessEntityAddress
Cross-reference table mapping customers, vendors, and employees to their addresses.
Columns
Description Data type
Name
Primary key. Foreign key to BusinessEntity.BusinessEntityID. int
BusinessEntityID
Primary key. Foreign key to Address.AddressID. int
AddressID
Primary key. Foreign key to AddressType.AddressTypeID. int
AddressTypeID
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
uniqueidentifier rowguid
Date and time the record was last updated. datetime ModifiedDate
Links to
Name / Description Join TableFK_BusinessEntityAddress_Address_AddressID: Foreign key constraint referencing Address.AddressID.
AddressID =
Person.Address.AddressID Address
FK_BusinessEntityAddress_AddressType_AddressTypeID: Foreign key constraint referencing AddressType.AddressTypeID.
AddressTypeID =
Person.AddressType.AddressTypeID AddressType
FK_BusinessEntityAddress_BusinessEntity_BusinessEntityID: Foreign key constraint referencing BusinessEntity.BusinessEntityID.
BusinessEntityID = Person.BusinessEntity.BusinessEntityID BusinessEntity
Unique keys
Description Columns NamePrimary key (clustered) constraint BusinessEntityID,
AddressTypeID, AddressID PK_BusinessEntityAddress_Business
EntityID_AddressID_AddressTypeID
Unique nonclustered index. Used to support replication samples. rowguid
Uses
Description Name
Person.BusinessEntityAddress
Foreign key constraint referencing Address.AddressID. Person.Address
Foreign key constraint referencing AddressType.AddressTypeID. Person.AddressType
Foreign key constraint referencing BusinessEntity.BusinessEntityID. Person.BusinessEntity
2.1.5. Table: Person.BusinessEntityContact
Cross-reference table mapping stores, vendors, and employees to people
Columns
Description Data type
Name
Primary key. Foreign key to BusinessEntity.BusinessEntityID. int
BusinessEntityID
Primary key. Foreign key to Person.BusinessEntityID. int
PersonID
Primary key. Foreign key to ContactType.ContactTypeID. int
ContactTypeID
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
uniqueidentifier rowguid
Date and time the record was last updated. datetime ModifiedDate
Links to
Name / Description Join TableFK_BusinessEntityContact_BusinessEntity_BusinessEntityID: Foreign key constraint referencing BusinessEntity.BusinessEntityID.
BusinessEntityID =
Person.BusinessEntity.BusinessEntityID BusinessEntity
FK_BusinessEntityContact_ContactType_ContactTypeID: Foreign key constraint referencing ContactType.ContactTypeID.
ContactTypeID =
Person.ContactType.ContactTypeID ContactType
FK_BusinessEntityContact_Person_PersonID: Foreign key constraint referencing Person.BusinessEntityID. PersonID = Person.Person.BusinessEntityID Person
Unique keys
Description Columns NamePrimary key (clustered) constraint BusinessEntityID, PersonID,
ContactTypeID PK_BusinessEntityContact_BusinessE
ntityID_PersonID_ContactTypeID
Unique nonclustered index. Used to support replication samples. rowguid AK_BusinessEntityContact_rowguid
Uses
Description Name Person.BusinessEntityContactForeign key constraint referencing BusinessEntity.BusinessEntityID. Person.BusinessEntity
Foreign key constraint referencing ContactType.ContactTypeID. Person.ContactType
Foreign key constraint referencing Person.BusinessEntityID. Person.Person
2.1.6. Table: Person.ContactType
Columns
Description Data type
Name
Primary key for ContactType records. int
ContactTypeID
Contact type description. nvarchar(50)
Name
Date and time the record was last updated. datetime ModifiedDate
Linked from
Name / Description Join TableFK_BusinessEntityContact_ContactType_ContactTypeID: Foreign key constraint referencing ContactType.ContactTypeID.
Person.BusinessEntityContact.ContactT ypeID = ContactTypeID BusinessEntityContact
Unique keys
Description Columns NamePrimary key (clustered) constraint ContactTypeID
PK_ContactType_ContactTypeID
Unique nonclustered index. Name AK_ContactType_Name
Used by
Description Name Person.ContactTypeForeign key constraint referencing ContactType.ContactTypeID. Person.BusinessEntityContact
2.1.7. Table: Person.CountryRegion
Lookup table containing the ISO standard codes for countries and regions.
Columns
Description Data type
Name
ISO standard code for countries and regions. nvarchar(3)
CountryRegionCode
Country or region name. nvarchar(50)
Name
Date and time the record was last updated. datetime ModifiedDate
Linked from
Name / Description Join Table FK_CountryRegionCurrency_CountryRegion_CountryRegionCode: Foreign key constraint referencingCountryRegion.CountryRegionCode. Sales.CountryRegionCurrency.Country
RegionCode = CountryRegionCode CountryRegionCurrency
FK_SalesTerritory_CountryRegion_CountryRegionCode: Foreign key constraint referencing CountryRegion.CountryRegionCode. Sales.SalesTerritory.CountryRegionCod
e = CountryRegionCode SalesTerritory
FK_StateProvince_CountryRegion_CountryRegionCode: Foreign key constraint referencing CountryRegion.CountryRegionCode. Person.StateProvince.CountryRegionC ode = CountryRegionCode StateProvince
Unique keys
Description Columns NamePrimary key (clustered) constraint CountryRegionCode
PK_CountryRegion_CountryRegion Code
Unique nonclustered index. Name
Used by
Description Name
Person.CountryRegion
Foreign key constraint referencing CountryRegion.CountryRegionCode. Person.StateProvince
Foreign key constraint referencing CountryRegion.CountryRegionCode. Sales.CountryRegionCurrency
Foreign key constraint referencing CountryRegion.CountryRegionCode. Sales.SalesTerritory
2.1.8. Table: Person.Person
Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.
Columns
Description Data type
Name
Primary key for Person records. int
BusinessEntityID
Primary type of person: SC = Store Contact, IN = Individual (retail) customer, SP = Sales person, EM = Employee (non-sales), VC = Vendor contact, GC = General contact
nchar(2) PersonType
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.
bit NameStyle
A courtesy title. For example, Mr. or Ms. nvarchar(8)
Title
First name of the person. nvarchar(50)
FirstName
Middle name or middle initial of the person. nvarchar(50)
MiddleName
Last name of the person. nvarchar(50)
LastName
Surname suffix. For example, Sr. or Jr. nvarchar(10)
Suffix
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. int
EmailPromotion
Additional contact information about the person stored in xml format. xml
AdditionalContactInfo
Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis.
xml Demographics
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
uniqueidentifier rowguid
Date and time the record was last updated. datetime ModifiedDate
Links to
Name / Description Join TableFK_Person_BusinessEntity_BusinessEntityID: Foreign key constraint referencing BusinessEntity.BusinessEntityID. BusinessEntityID = Person.BusinessEntity.BusinessEntityID BusinessEntity
Linked from
Name / Description Join TableFK_BusinessEntityContact_Person_PersonID: Foreign key constraint referencing Person.BusinessEntityID.
Person.BusinessEntityContact.PersonID = BusinessEntityID
BusinessEntityContact
FK_Customer_Person_PersonID: Foreign key constraint referencing Person.BusinessEntityID.
Sales.Customer.PersonID = BusinessEntityID
Customer
FK_EmailAddress_Person_BusinessEntityID: Foreign key constraint referencing Person.BusinessEntityID.
Person.EmailAddress.BusinessEntityID = BusinessEntityID
Name / Description Join
Table
FK_Employee_Person_BusinessEntityID: Foreign key constraint referencing Person.BusinessEntityID.
HumanResources.Employee.BusinessE ntityID = BusinessEntityID
Employee
FK_Password_Person_BusinessEntityID: Foreign key constraint referencing Person.BusinessEntityID.
Person.Password.BusinessEntityID = BusinessEntityID
Password
FK_PersonCreditCard_Person_BusinessEntityID: Foreign key constraint referencing Person.BusinessEntityID.
Sales.PersonCreditCard.BusinessEntityI D = BusinessEntityID
PersonCreditCard
FK_PersonPhone_Person_BusinessEntityID: Foreign key constraint referencing Person.BusinessEntityID. Person.PersonPhone.BusinessEntityID = BusinessEntityID PersonPhone
Unique keys
Description Columns NamePrimary key (clustered) constraint BusinessEntityID
PK_Person_BusinessEntityID
Unique nonclustered index. Used to support replication samples. rowguid AK_Person_rowguid
Triggers
Description When NameAFTER 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.
After Insert, Update iuPerson
Uses
Description Name
Person.Person
Foreign key constraint referencing BusinessEntity.BusinessEntityID. Person.BusinessEntity
Used by
Description Name
Person.Person
Foreign key constraint referencing Person.BusinessEntityID. HumanResources.Employee
Foreign key constraint referencing Person.BusinessEntityID. Person.BusinessEntityContact
Foreign key constraint referencing Person.BusinessEntityID. Person.EmailAddress
Foreign key constraint referencing Person.BusinessEntityID. Person.Password
Foreign key constraint referencing Person.BusinessEntityID. Person.PersonPhone
Foreign key constraint referencing Person.BusinessEntityID. Sales.Customer
Foreign key constraint referencing Person.BusinessEntityID. Sales.PersonCreditCard
2.1.9. Table: Person.StateProvince
State and province lookup table.
Columns
Description Data type
Name
Primary key for StateProvince records. int
StateProvinceID
ISO standard state or province code. nchar(3)
StateProvinceCode
Description Data type
Name
0 = StateProvinceCode exists. 1 = StateProvinceCode unavailable, using CountryRegionCode.
bit IsOnlyStateProvinceFlag
State or province description. nvarchar(50)
Name
ID of the territory in which the state or province is located. Foreign key to SalesTerritory.SalesTerritoryID.
int TerritoryID
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
uniqueidentifier rowguid
Date and time the record was last updated. datetime ModifiedDate
Links to
Name / Description Join TableFK_StateProvince_CountryRegion_CountryRegionCode: Foreign key constraint referencing CountryRegion.CountryRegionCode. CountryRegionCode =
Person.CountryRegion.CountryRegion Code
CountryRegion
FK_StateProvince_SalesTerritory_TerritoryID: Foreign key constraint referencing SalesTerritory.TerritoryID. TerritoryID = Sales.SalesTerritory.TerritoryID SalesTerritory
Linked from
Name / Description Join TableFK_Address_StateProvince_StateProvinceID: Foreign key constraint referencing StateProvince.StateProvinceID.
Person.Address.StateProvinceID = StateProvinceID
Address
FK_SalesTaxRate_StateProvince_StateProvinceID: Foreign key constraint referencing StateProvince.StateProvinceID. Sales.SalesTaxRate.StateProvinceID = StateProvinceID SalesTaxRate
Unique keys
Description Columns NamePrimary key (clustered) constraint StateProvinceID
PK_StateProvince_StateProvinceID
Unique nonclustered index. Name
AK_StateProvince_Name
Unique nonclustered index. Used to support replication samples. rowguid
AK_StateProvince_rowguid
Unique nonclustered index. StateProvinceCode, CountryRegionCode AK_StateProvince_StateProvinceCo de_CountryRegionCode
Uses
Description Name Person.StateProvinceForeign key constraint referencing CountryRegion.CountryRegionCode. Person.CountryRegion
Foreign key constraint referencing SalesTerritory.TerritoryID. Sales.SalesTerritory
Used by
Description Name
Person.StateProvince
Foreign key constraint referencing StateProvince.StateProvinceID. Person.Address
Foreign key constraint referencing StateProvince.StateProvinceID. Sales.SalesTaxRate
3. Person
3.1. Tables
3.1.1. Table: Person.ContactType
Lookup table containing the types of business entity contacts.
Columns
Description Data type
Name
Primary key for ContactType records. int
ContactTypeID
Contact type description. nvarchar(50)
Name
Date and time the record was last updated. datetime ModifiedDate
Linked from
Name / Description Join TableFK_BusinessEntityContact_ContactType_ContactTypeID: Foreign key constraint referencing ContactType.ContactTypeID.
Person.BusinessEntityContact.ContactT ypeID = ContactTypeID BusinessEntityContact
Unique keys
Description Columns NamePrimary key (clustered) constraint ContactTypeID
PK_ContactType_ContactTypeID
Unique nonclustered index. Name AK_ContactType_Name
Used by
Description Name Person.ContactType3.1.2. Table: Person.CountryRegion
Lookup table containing the ISO standard codes for countries and regions.
Columns
Description Data type
Name
ISO standard code for countries and regions. nvarchar(3)
CountryRegionCode
Country or region name. nvarchar(50)
Name
Date and time the record was last updated. datetime ModifiedDate
Linked from
Name / Description Join Table FK_CountryRegionCurrency_CountryRegion_CountryRegionCode: Foreign key constraint referencingCountryRegion.CountryRegionCode. Sales.CountryRegionCurrency.Country
RegionCode = CountryRegionCode CountryRegionCurrency
FK_SalesTerritory_CountryRegion_CountryRegionCode: Foreign key constraint referencing CountryRegion.CountryRegionCode. Sales.SalesTerritory.CountryRegionCod
e = CountryRegionCode SalesTerritory
FK_StateProvince_CountryRegion_CountryRegionCode: Foreign key constraint referencing CountryRegion.CountryRegionCode. Person.StateProvince.CountryRegionC ode = CountryRegionCode StateProvince
Unique keys
Description Columns NamePrimary key (clustered) constraint CountryRegionCode
PK_CountryRegion_CountryRegion Code
Unique nonclustered index. Name AK_CountryRegion_Name
Used by
Description Name Person.CountryRegionForeign key constraint referencing CountryRegion.CountryRegionCode. Person.StateProvince
Foreign key constraint referencing CountryRegion.CountryRegionCode. Sales.CountryRegionCurrency
Foreign key constraint referencing CountryRegion.CountryRegionCode. Sales.SalesTerritory
3.1.3. Table: Person.EmailAddress
Where to send a person email.
Columns
Description Data type
Name
Primary key. Person associated with this email address. Foreign key to Person.BusinessEntityID
int BusinessEntityID
Primary key. ID of this email address. int
EmailAddressID
E-mail address for the person. nvarchar(50)
EmailAddress
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
uniqueidentifier rowguid
Date and time the record was last updated. datetime
Links to
Name / Description Join
Table
FK_EmailAddress_Person_BusinessEntityID: Foreign key constraint referencing Person.BusinessEntityID. BusinessEntityID = Person.Person.BusinessEntityID Person
Unique keys
Description Columns NamePrimary key (clustered) constraint BusinessEntityID, EmailAddressID PK_EmailAddress_BusinessEntityID_ EmailAddressID
Uses
Description Name Person.EmailAddressForeign key constraint referencing Person.BusinessEntityID. Person.Person
3.1.4. Table: Person.Password
One way hashed authentication information
Columns
Description Data type Name int BusinessEntityIDPassword for the e-mail account. varchar(128)
PasswordHash
Random value concatenated with the password string before the password is hashed.
varchar(10) PasswordSalt
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
uniqueidentifier rowguid
Date and time the record was last updated. datetime ModifiedDate
Links to
Name / Description Join TableFK_Password_Person_BusinessEntityID: Foreign key constraint referencing Person.BusinessEntityID. BusinessEntityID = Person.Person.BusinessEntityID Person
Unique keys
Description Columns NamePrimary key (clustered) constraint BusinessEntityID PK_Password_BusinessEntityID
Uses
Description Name Person.PasswordForeign key constraint referencing Person.BusinessEntityID. Person.Person
3.1.5. Table: Person.Person
Columns
Description Data type
Name
Primary key for Person records. int
BusinessEntityID
Primary type of person: SC = Store Contact, IN = Individual (retail) customer, SP = Sales person, EM = Employee (non-sales), VC = Vendor contact, GC = General contact
nchar(2) PersonType
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.
bit NameStyle
A courtesy title. For example, Mr. or Ms. nvarchar(8)
Title
First name of the person. nvarchar(50)
FirstName
Middle name or middle initial of the person. nvarchar(50)
MiddleName
Last name of the person. nvarchar(50)
LastName
Surname suffix. For example, Sr. or Jr. nvarchar(10)
Suffix
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. int
EmailPromotion
Additional contact information about the person stored in xml format. xml
AdditionalContactInfo
Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis.
xml Demographics
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
uniqueidentifier rowguid
Date and time the record was last updated. datetime ModifiedDate
Links to
Name / Description Join TableFK_Person_BusinessEntity_BusinessEntityID: Foreign key constraint referencing BusinessEntity.BusinessEntityID. BusinessEntityID = Person.BusinessEntity.BusinessEntityID BusinessEntity
Linked from
Name / Description Join TableFK_BusinessEntityContact_Person_PersonID: Foreign key constraint referencing Person.BusinessEntityID.
Person.BusinessEntityContact.PersonID = BusinessEntityID
BusinessEntityContact
FK_Customer_Person_PersonID: Foreign key constraint referencing Person.BusinessEntityID.
Sales.Customer.PersonID = BusinessEntityID
Customer
FK_EmailAddress_Person_BusinessEntityID: Foreign key constraint referencing Person.BusinessEntityID.
Person.EmailAddress.BusinessEntityID = BusinessEntityID
EmailAddress
FK_Employee_Person_BusinessEntityID: Foreign key constraint referencing Person.BusinessEntityID.
HumanResources.Employee.BusinessE ntityID = BusinessEntityID
Employee
FK_Password_Person_BusinessEntityID: Foreign key constraint referencing Person.BusinessEntityID.
Person.Password.BusinessEntityID = BusinessEntityID
Password
FK_PersonCreditCard_Person_BusinessEntityID: Foreign key constraint referencing Person.BusinessEntityID.
Sales.PersonCreditCard.BusinessEntityI D = BusinessEntityID
PersonCreditCard
FK_PersonPhone_Person_BusinessEntityID: Foreign key constraint referencing Person.BusinessEntityID.
Person.PersonPhone.BusinessEntityID = BusinessEntityID
Unique keys
Description Columns
Name
Primary key (clustered) constraint BusinessEntityID
PK_Person_BusinessEntityID
Unique nonclustered index. Used to support replication samples. rowguid AK_Person_rowguid
Triggers
Description When NameAFTER 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.
After Insert, Update iuPerson
Uses
Description Name
Person.Person
Foreign key constraint referencing BusinessEntity.BusinessEntityID. Person.BusinessEntity
Used by
Description Name
Person.Person
Foreign key constraint referencing Person.BusinessEntityID. HumanResources.Employee
Foreign key constraint referencing Person.BusinessEntityID. Person.BusinessEntityContact
Foreign key constraint referencing Person.BusinessEntityID. Person.EmailAddress
Foreign key constraint referencing Person.BusinessEntityID. Person.Password
Foreign key constraint referencing Person.BusinessEntityID. Person.PersonPhone
Foreign key constraint referencing Person.BusinessEntityID. Sales.Customer
Foreign key constraint referencing Person.BusinessEntityID. Sales.PersonCreditCard
3.1.6. Table: Person.PersonPhone
Telephone number and type of a person.
Columns
Description Data type
Name
Business entity identification number. Foreign key to Person.BusinessEntityID. int
BusinessEntityID
Telephone number identification number. nvarchar(25)
PhoneNumber
Kind of phone number. Foreign key to PhoneNumberType.PhoneNumberTypeID. int
PhoneNumberTypeID
Date and time the record was last updated. datetime ModifiedDate
Links to
Name / Description Join TableFK_PersonPhone_Person_BusinessEntityID: Foreign key constraint referencing Person.BusinessEntityID.
BusinessEntityID =
Person.Person.BusinessEntityID Person
FK_PersonPhone_PhoneNumberType_PhoneNumberTypeID: Foreign key constraint referencing PhoneNumberType.PhoneNumberTypeID. PhoneNumberTypeID =
Person.PhoneNumberType.PhoneNu PhoneNumberType
Unique keys
Description Columns
Name
Primary key (clustered) constraint BusinessEntityID, PhoneNumber, PhoneNumberTypeID PK_PersonPhone_BusinessEntityID_ PhoneNumber_PhoneNumberType ID
Uses
Description Name Person.PersonPhoneForeign key constraint referencing Person.BusinessEntityID. Person.Person
Foreign key constraint referencing PhoneNumberType.PhoneNumberTypeID. Person.PhoneNumberType
3.1.7. Table: Person.PhoneNumberType
Type of phone number of a person.
Columns
Description Data type
Name
Primary key for telephone number type records. int
PhoneNumberTypeID
Name of the telephone number type nvarchar(50)
Name
Date and time the record was last updated. datetime ModifiedDate
Linked from
Name / Description Join Table FK_PersonPhone_PhoneNumberType_PhoneNumberTypeID: Foreign key constraint referencing PhoneNumberType.PhoneNumberTypeID. Person.PersonPhone.PhoneNumberTy peID = PhoneNumberTypeID PersonPhoneUnique keys
Description Columns NamePrimary key (clustered) constraint PhoneNumberTypeID PK_PhoneNumberType_PhoneNum berTypeID
Used by
Description Name Person.PhoneNumberTypeForeign key constraint referencing PhoneNumberType.PhoneNumberTypeID. Person.PersonPhone
3.2. Views
3.2.1. View: Person.vAdditionalContactInfo
Displays the contact name and content from each element in the xml column AdditionalContactInfo for that person.
Columns
Description Data type Name int BusinessEntityID nvarchar(50) FirstName nvarchar(50) MiddleNameDescription Data type Name nvarchar(50) LastName nvarchar(50) TelephoneNumber nvarchar(MAX) TelephoneSpecialInstructions nvarchar(50) Street nvarchar(50) City nvarchar(50) StateProvince nvarchar(50) PostalCode nvarchar(50) CountryRegion nvarchar(MAX) HomeAddressSpecialInstructions nvarchar(128) EMailAddress nvarchar(MAX) EMailSpecialInstructions nvarchar(50) EMailTelephoneNumber uniqueidentifier rowguid datetime ModifiedDate
3.2.2. View: Person.vStateProvinceCountryRegion
Joins StateProvince table with CountryRegion table.
Columns
Description Data type Name int StateProvinceID nchar(3) StateProvinceCode bit IsOnlyStateProvinceFlag nvarchar(50) StateProvinceName int TerritoryID nvarchar(3) CountryRegionCode nvarchar(50) CountryRegionName4. Human Resources
4.1. Tables
4.1.1. Table: HumanResources.Department
Lookup table containing the departments within the Adventure Works Cycles company.
Columns
Description Data type
Name
Primary key for Department records. smallint
DepartmentID
Name of the department. nvarchar(50)
Name
Name of the group to which the department belongs. nvarchar(50)
GroupName
Date and time the record was last updated. datetime ModifiedDate
Linked from
Name / Description Join Table FK_EmployeeDepartmentHistory_Department_DepartmentID: Foreign key constraint referencing Department.DepartmentID.HumanResources.EmployeeDepartme ntHistory.DepartmentID = DepartmentID EmployeeDepartmentHistory
Unique keys
Description Columns NamePrimary key (clustered) constraint DepartmentID
PK_Department_DepartmentID
Unique nonclustered index. Name AK_Department_Name
Used by
Description Name HumanResources.DepartmentDescription Name
Foreign key constraint referencing Department.DepartmentID. HumanResources.EmployeeDepa
rtmentHistory
4.1.2. Table: HumanResources.Employee
Employee information such as salary, department, and title.
Columns
Description Data type
Name
Primary key for Employee records. Foreign key to BusinessEntity.BusinessEntityID.
int BusinessEntityID
Unique national identification number such as a social security number. nvarchar(15)
NationalIDNumber
Network login. nvarchar(256)
LoginID
Where the employee is located in corporate hierarchy. hierarchyid
OrganizationNode
The depth of the employee in the corporate hierarchy. smallint
OrganizationLevel
Work title such as Buyer or Sales Representative. nvarchar(50) JobTitle Date of birth. date BirthDate M = Married, S = Single nchar(1) MaritalStatus M = Male, F = Female nchar(1) Gender
Employee hired on this date. date
HireDate
Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.
bit SalariedFlag
Number of available vacation hours. smallint
VacationHours
Number of available sick leave hours. smallint
SickLeaveHours
0 = Inactive, 1 = Active bit
CurrentFlag
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
uniqueidentifier rowguid
Date and time the record was last updated. datetime ModifiedDate
Links to
Name / Description Join TableFK_Employee_Person_BusinessEntityID: Foreign key constraint referencing Person.BusinessEntityID. BusinessEntityID = Person.Person.BusinessEntityID Person
Linked from
Name / Description Join TableFK_Document_Employee_Owner: Foreign key constraint referencing Employee.BusinessEntityID.
Production.Document.Owner = BusinessEntityID
Document
FK_EmployeeDepartmentHistory_Employee_BusinessEntityID: Foreign key constraint referencing Employee.EmployeeID.
HumanResources.EmployeeDepartme ntHistory.BusinessEntityID = BusinessEntityID
EmployeeDepartmentHistory
FK_EmployeePayHistory_Employee_BusinessEntityID: Foreign key constraint referencing Employee.EmployeeID.
HumanResources.EmployeePayHistory .BusinessEntityID = BusinessEntityID EmployeePayHistory
FK_JobCandidate_Employee_BusinessEntityID: Foreign key constraint referencing Employee.EmployeeID.
HumanResources.JobCandidate.Busine ssEntityID = BusinessEntityID
Name / Description Join
Table
FK_PurchaseOrderHeader_Employee_EmployeeID: Foreign key constraint referencing Employee.EmployeeID.
Purchasing.PurchaseOrderHeader.Em ployeeID = BusinessEntityID PurchaseOrderHeader
FK_SalesPerson_Employee_BusinessEntityID: Foreign key constraint referencing Employee.EmployeeID. Sales.SalesPerson.BusinessEntityID = BusinessEntityID SalesPerson
Unique keys
Description Columns NamePrimary key (clustered) constraint BusinessEntityID
PK_Employee_BusinessEntityID
Unique nonclustered index. LoginID
AK_Employee_LoginID
Unique nonclustered index. NationalIDNumber
AK_Employee_NationalIDNumber
Unique nonclustered index. Used to support replication samples. rowguid AK_Employee_rowguid
Triggers
Description When NameINSTEAD OF DELETE trigger which keeps Employees from being deleted. Instead Of Delete dEmployee
Uses
Description Name HumanResources.EmployeeForeign key constraint referencing Person.BusinessEntityID. Person.Person
Used by
Description Name
HumanResources.Employee
Foreign key constraint referencing Employee.EmployeeID. HumanResources.EmployeeDepa
rtmentHistory
Foreign key constraint referencing Employee.EmployeeID. HumanResources.EmployeePayHi
story
Foreign key constraint referencing Employee.EmployeeID. HumanResources.JobCandidate
Foreign key constraint referencing Employee.BusinessEntityID. Production.Document
Foreign key constraint referencing Employee.EmployeeID. Purchasing.PurchaseOrderHeade
r
Foreign key constraint referencing Employee.EmployeeID. Sales.SalesPerson
4.1.3. Table: HumanResources.EmployeeDepartmentHistory
Employee department transfers.
Columns
Description Data type
Name
Employee identification number. Foreign key to Employee.BusinessEntityID. int
BusinessEntityID
Department in which the employee worked including currently. Foreign key to Department.DepartmentID.
smallint DepartmentID
Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID. tinyint
ShiftID
Date the employee started work in the department. date
Description Data type
Name
Date the employee left the department. NULL = Current department. date
EndDate
Date and time the record was last updated. datetime ModifiedDate
Links to
Name / Description Join Table FK_EmployeeDepartmentHistory_Department_DepartmentID: Foreign key constraint referencing Department.DepartmentID.DepartmentID =
HumanResources.Department.Depart mentID
Department
FK_EmployeeDepartmentHistory_Employee_BusinessEntityID: Foreign key constraint referencing Employee.EmployeeID.
BusinessEntityID =
HumanResources.Employee.BusinessE ntityID
Employee
FK_EmployeeDepartmentHistory_Shift_ShiftID: Foreign key constraint referencing Shift.ShiftID ShiftID = HumanResources.Shift.ShiftID Shift
Unique keys
Description Columns NamePrimary key (clustered) constraint DepartmentID, ShiftID, StartDate, BusinessEntityID PK_EmployeeDepartmentHistory_B usinessEntityID_StartDate_Departm entID
Uses
Description Name HumanResources.EmployeeDepartmentHistoryForeign key constraint referencing Department.DepartmentID. HumanResources.Department
Foreign key constraint referencing Employee.EmployeeID. HumanResources.Employee
Foreign key constraint referencing Shift.ShiftID HumanResources.Shift
4.1.4. Table: HumanResources.EmployeePayHistory
Employee pay history.
Columns
Description Data type
Name
Employee identification number. Foreign key to Employee.BusinessEntityID. int
BusinessEntityID
Date the change in pay is effective datetime
RateChangeDate
Salary hourly rate. money
Rate
1 = Salary received monthly, 2 = Salary received biweekly tinyint
PayFrequency
Date and time the record was last updated. datetime ModifiedDate
Links to
Name / Description Join TableFK_EmployeePayHistory_Employee_BusinessEntityID: Foreign key constraint referencing Employee.EmployeeID.
BusinessEntityID =
HumanResources.Employee.BusinessE ntityID
Unique keys
Description Columns
Name
Primary key (clustered) constraint BusinessEntityID, RateChangeDate PK_EmployeePayHistory_BusinessE ntityID_RateChangeDate
Uses
Description Name HumanResources.EmployeePayHistoryForeign key constraint referencing Employee.EmployeeID. HumanResources.Employee
4.1.5. Table: HumanResources.JobCandidate
Résumés submitted to Human Resources by job applicants.
Columns
Description Data type
Name
Primary key for JobCandidate records. int
JobCandidateID
Employee identification number if applicant was hired. Foreign key to Employee.BusinessEntityID. int BusinessEntityID Résumé in XML format. xml Resume
Date and time the record was last updated. datetime ModifiedDate
Links to
Name / Description Join TableFK_JobCandidate_Employee_BusinessEntityID: Foreign key constraint referencing Employee.EmployeeID. BusinessEntityID = HumanResources.Employee.BusinessE ntityID Employee
Unique keys
Description Columns NamePrimary key (clustered) constraint JobCandidateID PK_JobCandidate_JobCandidateID
Uses
Description Name HumanResources.JobCandidateForeign key constraint referencing Employee.EmployeeID. HumanResources.Employee
4.1.6. Table: HumanResources.Shift
Work shift lookup table.
Columns
Description Data type
Name
Primary key for Shift records. tinyint
ShiftID
Shift description. nvarchar(50)
Name
Shift start time. time(7)
StartTime
Shift end time. time(7)
Description Data type
Name
Date and time the record was last updated. datetime ModifiedDate
Linked from
Name / Description Join TableFK_EmployeeDepartmentHistory_Shift_ShiftID: Foreign key constraint referencing Shift.ShiftID HumanResources.EmployeeDepartme ntHistory.ShiftID = ShiftID EmployeeDepartmentHistory
Unique keys
Description Columns NamePrimary key (clustered) constraint ShiftID
PK_Shift_ShiftID
Unique nonclustered index. Name
AK_Shift_Name
Unique nonclustered index. StartTime, EndTime AK_Shift_StartTime_EndTime
Used by
Description Name HumanResources.ShiftForeign key constraint referencing Shift.ShiftID HumanResources.EmployeeDepa
rtmentHistory
4.2. Views
4.2.1. View: HumanResources.vEmployee
Employee names and addresses.
Columns
Description Data type Name int BusinessEntityID nvarchar(8) Title nvarchar(50) FirstName nvarchar(50) MiddleName nvarchar(50) LastName nvarchar(10) Suffix nvarchar(50) JobTitle nvarchar(25) PhoneNumber nvarchar(50) PhoneNumberType nvarchar(50) EmailAddress int EmailPromotion nvarchar(60) AddressLine1 nvarchar(60) AddressLine2 nvarchar(30) City nvarchar(50) StateProvinceName nvarchar(15) PostalCodeDescription Data type Name nvarchar(50) CountryRegionName xml AdditionalContactInfo
4.2.2. View: HumanResources.vEmployeeDepartment
Returns employee name, title, and current department.
Columns
Description Data type Name int BusinessEntityID nvarchar(8) Title nvarchar(50) FirstName nvarchar(50) MiddleName nvarchar(50) LastName nvarchar(10) Suffix nvarchar(50) JobTitle nvarchar(50) Department nvarchar(50) GroupName date StartDate4.2.3. View: HumanResources.vEmployeeDepartmentHistory
Returns employee name and current and previous departments.
Columns
Description Data type Name int BusinessEntityID nvarchar(8) Title nvarchar(50) FirstName nvarchar(50) MiddleName nvarchar(50) LastName nvarchar(10) Suffix nvarchar(50) Shift nvarchar(50) Department nvarchar(50) GroupName date StartDate date EndDate4.2.4. View: HumanResources.vJobCandidate
Job candidate names and resumes.
Columns
Description Data type Name int JobCandidateIDDescription Data type Name int BusinessEntityID nvarchar(30) Name.Prefix nvarchar(30) Name.First nvarchar(30) Name.Middle nvarchar(30) Name.Last nvarchar(30) Name.Suffix nvarchar(MAX) Skills nvarchar(30) Addr.Type nvarchar(100) Addr.Loc.CountryRegion nvarchar(100) Addr.Loc.State nvarchar(100) Addr.Loc.City nvarchar(20) Addr.PostalCode nvarchar(MAX) EMail nvarchar(MAX) WebSite datetime ModifiedDate
4.2.5. View: HumanResources.vJobCandidateEducation
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.
Columns
Description Data type Name int JobCandidateID nvarchar(MAX) Edu.Level datetime Edu.StartDate datetime Edu.EndDate nvarchar(50) Edu.Degree nvarchar(50) Edu.Major nvarchar(50) Edu.Minor nvarchar(5) Edu.GPA nvarchar(5) Edu.GPAScale nvarchar(100) Edu.School nvarchar(100) Edu.Loc.CountryRegion nvarchar(100) Edu.Loc.State nvarchar(100) Edu.Loc.City4.2.6. View: HumanResources.vJobCandidateEmployment
Displays the content from each employement history related element in the xml column Resume in the
Columns
Description Data type Name int JobCandidateID datetime Emp.StartDate datetime Emp.EndDate nvarchar(100) Emp.OrgName nvarchar(100) Emp.JobTitle nvarchar(MAX) Emp.Responsibility nvarchar(MAX) Emp.FunctionCategory nvarchar(MAX) Emp.IndustryCategory nvarchar(MAX) Emp.Loc.CountryRegion nvarchar(MAX) Emp.Loc.State nvarchar(MAX) Emp.Loc.City4.3. Procedures
4.3.1. Procedure: dbo.uspGetEmployeeManagers
Stored procedure using a recursive query to return the direct and indirect managers of the specified employee.
Input/Output
Description Data type
Name
Input parameter for the stored procedure uspGetEmployeeManagers. Enter a valid BusinessEntityID from the HumanResources.Employee table.
int BusinessEntityID
4.3.2. Procedure: dbo.uspGetManagerEmployees
Stored procedure using a recursive query to return the direct and indirect employees of the specified manager.
Input/Output
Description Data type
Name
Input parameter for the stored procedure uspGetManagerEmployees. Enter a valid BusinessEntityID of the manager from the HumanResources.Employee table. int BusinessEntityID
4.3.3. Procedure: dbo.uspSearchCandidateResumes
Input/Output
Description Data type Name nvarchar(1000) searchString bit useInflectional bit useThesaurus int language4.3.4. Procedure: HumanResources.uspUpdateEmployeeHireInfo
Input/Output
Description Data type
Name
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a valid BusinessEntityID from the Employee table.
int BusinessEntityID
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a title for the employee.
nvarchar(50) JobTitle
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a hire date for the employee.
datetime HireDate
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the date the rate changed for the employee.
datetime RateChangeDate
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the new rate for the employee.
money Rate
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the pay frequency for the employee.
tinyint PayFrequency
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the current flag for the employee.
bit CurrentFlag
4.3.5. Procedure: HumanResources.uspUpdateEmployeeLogin
Input/Output
Description Data type NameInput parameter for the stored procedure uspUpdateEmployeeLogin. Enter a valid EmployeeID from the Employee table.
int BusinessEntityID
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a valid ManagerID for the employee.
hierarchyid OrganizationNode
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a valid login for the employee.
nvarchar(256) LoginID
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a title for the employee.
nvarchar(50) JobTitle
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a hire date for the employee.
datetime HireDate
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter the current flag for the employee.
bit CurrentFlag
4.3.6. Procedure: HumanResources.uspUpdateEmployeePersonalInfo
Input/Output
Description Data type NameInput parameter for the stored procedure uspUpdateEmployeePersonalInfo. Enter a valid BusinessEntityID from the HumanResources.Employee table. int
BusinessEntityID
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a national ID for the employee.
nvarchar(15) NationalIDNumber
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a birth date for the employee.
datetime BirthDate
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a marital status for the employee.
nchar(1) MaritalStatus
Input parameter for the stored procedure uspUpdateEmployeeHireInfo. Enter a gender for the employee.
nchar(1) Gender
4.4. Functions
4.4.1. Function: dbo.ufnGetContactInformation
Table value function returning the first name, last name, job title and contact type for a given contact.
Input/Output
Description Data type Name table type ReturnsInput parameter for the table value function ufnGetContactInformation. Enter a valid PersonID from the Person.Contact table.
int PersonID
5. Products
5.1. Tables
5.1.1. Table: Production.Culture
Lookup table containing the languages in which some AdventureWorks data is stored.
Columns
Description Data type
Name
Primary key for Culture records. nchar(6)
CultureID
Culture description. nvarchar(50)
Name
Date and time the record was last updated. datetime ModifiedDate
Linked from
Name / Description Join Table FK_ProductModelProductDescriptionCulture_Culture_CultureID: Foreign key constraint referencing Culture.CultureID.Production.ProductModelProductDesc riptionCulture.CultureID = CultureID ProductModelProductDescriptionC ulture
Unique keys
Description Columns NamePrimary key (clustered) constraint CultureID
PK_Culture_CultureID
Unique nonclustered index. Name AK_Culture_Name
Used by
Description Name Production.CultureDescription Name
Foreign key constraint referencing Culture.CultureID. Production.ProductModelProduct
DescriptionCulture
5.1.2. Table: Production.Document
Product maintenance documents.
Columns
Description Data type
Name
Primary key for Document records. hierarchyid
DocumentNode
Depth in the document hierarchy. smallint
DocumentLevel
Title of the document. nvarchar(50)
Title
Employee who controls the document. Foreign key to Employee.BusinessEntityID
int Owner
0 = This is a folder, 1 = This is a document. bit
FolderFlag
File name of the document nvarchar(400)
FileName
File extension indicating the document type. For example, .doc or .txt. nvarchar(8)
FileExtension
Revision number of the document. nchar(5)
Revision
Engineering change approval number. int
ChangeNumber
1 = Pending approval, 2 = Approved, 3 = Obsolete tinyint Status Document abstract. nvarchar(MAX) DocumentSummary Complete document. varbinary(MAX) Document
ROWGUIDCOL number uniquely identifying the record. Required for FileStream. uniqueidentifier
rowguid
Date and time the record was last updated. datetime ModifiedDate
Links to
Name / Description Join TableFK_Document_Employee_Owner: Foreign key constraint referencing Employee.BusinessEntityID. Owner = HumanResources.Employee.BusinessE ntityID Employee
Linked from
Name / Description Join TableFK_ProductDocument_Document_DocumentNode: Foreign key constraint referencing Document.DocumentNode.
Production.ProductDocument.Docum entNode = DocumentNode ProductDocument
Unique keys
Description Columns NamePrimary key (clustered) constraint DocumentNode
PK_Document_DocumentNode
Unique nonclustered index. DocumentLevel,
DocumentNode AK_Document_DocumentLevel_Do
cumentNode
Unique nonclustered index. Used to support FileStream. rowguid
AK_Document_rowguid
rowguid UQ__Document__F73921F793071A
Uses
Description Name
Production.Document
Foreign key constraint referencing Employee.BusinessEntityID. HumanResources.Employee
Used by
Description Name
Production.Document
Foreign key constraint referencing Document.DocumentNode. Production.ProductDocument
5.1.3. Table: Production.Illustration
Bicycle assembly diagrams.
Columns
Description Data type
Name
Primary key for Illustration records. int
IllustrationID
Illustrations used in manufacturing instructions. Stored as XML. xml
Diagram
Date and time the record was last updated. datetime ModifiedDate
Linked from
Name / Description Join TableFK_ProductModelIllustration_Illustration_IllustrationID: Foreign key constraint referencing Illustration.IllustrationID.
Production.ProductModelIllustration.Ill ustrationID = IllustrationID ProductModelIllustration
Unique keys
Description Columns NamePrimary key (clustered) constraint IllustrationID PK_Illustration_IllustrationID
Used by
Description Name Production.IllustrationForeign key constraint referencing Illustration.IllustrationID. Production.ProductModelIllustrati
on
5.1.4. Table: Production.Product
Products sold or used in the manfacturing of sold products.
Columns
Description Data type
Name
Primary key for Product records. int
ProductID
Name of the product. nvarchar(50)
Name
Unique product identification number. nvarchar(25)
ProductNumber
0 = Product is purchased, 1 = Product is manufactured in-house. bit
MakeFlag
0 = Product is not a salable item. 1 = Product is salable. bit
Description Data type Name Product color. nvarchar(15) Color
Minimum inventory quantity. smallint
SafetyStockLevel
Inventory level that triggers a purchase order or work order. smallint
ReorderPoint
Standard cost of the product. money StandardCost Selling price. money ListPrice Product size. nvarchar(5) Size
Unit of measure for Size column. nchar(3)
SizeUnitMeasureCode
Unit of measure for Weight column. nchar(3)
WeightUnitMeasureCode
Product weight. decimal(8, 2)
Weight
Number of days required to manufacture the product. int
DaysToManufacture
R = Road, M = Mountain, T = Touring, S = Standard nchar(2)
ProductLine
H = High, M = Medium, L = Low nchar(2)
Class
W = Womens, M = Mens, U = Universal nchar(2)
Style
Product is a member of this product subcategory. Foreign key to ProductSubCategory.ProductSubCategoryID.
int ProductSubcategoryID
Product is a member of this product model. Foreign key to ProductModel.ProductModelID.
int ProductModelID
Date the product was available for sale. datetime
SellStartDate
Date the product was no longer available for sale. datetime
SellEndDate
Date the product was discontinued. datetime
DiscontinuedDate
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
uniqueidentifier rowguid
Date and time the record was last updated. datetime ModifiedDate
Links to
Name / Description Join TableFK_Product_ProductModel_ProductModelID: Foreign key constraint referencing ProductModel.ProductModelID.
ProductModelID =
Production.ProductModel.ProductMo delID
ProductModel
FK_Product_ProductSubcategory_ProductSubcategoryID: Foreign key constraint referencing ProductSubcategory.ProductSubcategoryID. ProductSubcategoryID =
Production.ProductSubcategory.Produ ctSubcategoryID
ProductSubcategory
FK_Product_UnitMeasure_SizeUnitMeasureCode: Foreign key constraint referencing UnitMeasure.UnitMeasureCode. SizeUnitMeasureCode =
Production.UnitMeasure.UnitMeasure Code
UnitMeasure
FK_Product_UnitMeasure_WeightUnitMeasureCode: Foreign key constraint referencing UnitMeasure.UnitMeasureCode. WeightUnitMeasureCode = Production.UnitMeasure.UnitMeasure Code UnitMeasure
Linked from
Name / Description Join TableFK_BillOfMaterials_Product_ComponentID: Foreign key constraint referencing Product.ProductAssemblyID.
Production.BillOfMaterials.Component ID = ProductID
BillOfMaterials
FK_BillOfMaterials_Product_ProductAssemblyID: Foreign key constraint referencing Product.ProductAssemblyID.
Production.BillOfMaterials.ProductAsse mblyID = ProductID
Name / Description Join
Table
FK_ProductCostHistory_Product_ProductID: Foreign key constraint referencing Product.ProductID.
Production.ProductCostHistory.Produc tID = ProductID
ProductCostHistory
FK_ProductDocument_Product_ProductID: Foreign key constraint referencing Product.ProductID.
Production.ProductDocument.Product ID = ProductID
ProductDocument
FK_ProductInventory_Product_ProductID: Foreign key constraint referencing Product.ProductID.
Production.ProductInventory.ProductI D = ProductID
ProductInventory
FK_ProductListPriceHistory_Product_ProductID: Foreign key constraint referencing Product.ProductID.
Production.ProductListPriceHistory.Pro ductID = ProductID
ProductListPriceHistory
FK_ProductProductPhoto_Product_ProductID: Foreign key constraint referencing Product.ProductID.
Production.ProductProductPhoto.Prod uctID = ProductID
ProductProductPhoto
FK_ProductReview_Product_ProductID: Foreign key constraint referencing Product.ProductID.
Production.ProductReview.ProductID = ProductID
ProductReview
FK_ProductVendor_Product_ProductID: Foreign key constraint referencing Product.ProductID.
Purchasing.ProductVendor.ProductID = ProductID
ProductVendor
FK_PurchaseOrderDetail_Product_ProductID: Foreign key constraint referencing Product.ProductID. Purchasing.PurchaseOrderDetail.Prod uctID = ProductID PurchaseOrderDetail User-defined relation Sales.SalesOrderDetail.ProductID = ProductID SalesOrderDetail
FK_ShoppingCartItem_Product_ProductID: Foreign key constraint referencing Product.ProductID.
Sales.ShoppingCartItem.ProductID = ProductID
ShoppingCartItem
FK_SpecialOfferProduct_Product_ProductID: Foreign key constraint referencing Product.ProductID.
Sales.SpecialOfferProduct.ProductID = ProductID
SpecialOfferProduct
FK_TransactionHistory_Product_ProductID: Foreign key constraint referencing Product.ProductID. Production.TransactionHistory.Product ID = ProductID TransactionHistory User-defined relation Production.TransactionHistoryArchive. ProductID = ProductID TransactionHistoryArchive
FK_WorkOrder_Product_ProductID: Foreign key constraint referencing Product.ProductID. Production.WorkOrder.ProductID = ProductID WorkOrder
Unique keys
Description Columns NamePrimary key (clustered) constraint ProductID
PK_Product_ProductID
Unique nonclustered index. Name
AK_Product_Name
Unique nonclustered index. ProductNumber
AK_Product_ProductNumber
Unique nonclustered index. Used to support replication samples. rowguid AK_Product_rowguid
Uses
Description Name Production.ProductForeign key constraint referencing ProductModel.ProductModelID. Production.ProductModel
Foreign key constraint referencing ProductSubcategory.ProductSubcategoryID. Production.ProductSubcategory
Foreign key constraint referencing UnitMeasure.UnitMeasureCode. Production.UnitMeasure
Foreign key constraint referencing UnitMeasure.UnitMeasureCode. Production.UnitMeasure
Used by
Description Name
Production.Product
Foreign key constraint referencing Product.ProductAssemblyID. Production.BillOfMaterials
Foreign key constraint referencing Product.ProductAssemblyID. Production.BillOfMaterials
Foreign key constraint referencing Product.ProductID. Production.ProductCostHistory
Foreign key constraint referencing Product.ProductID. Production.ProductDocument
Foreign key constraint referencing Product.ProductID. Production.ProductInventory
Foreign key constraint referencing Product.ProductID. Production.ProductListPriceHistor
y
Foreign key constraint referencing Product.ProductID. Production.ProductProductPhoto
Foreign key constraint referencing Product.ProductID. Production.ProductReview
Foreign key constraint referencing Product.ProductID. Production.TransactionHistory
Production.TransactionHistoryArc hive
Foreign key constraint referencing Product.ProductID. Production.WorkOrder
Foreign key constraint referencing Product.ProductID. Purchasing.ProductVendor
Foreign key constraint referencing Product.ProductID. Purchasing.PurchaseOrderDetail
Sales.SalesOrderDetail
Foreign key constraint referencing Product.ProductID. Sales.ShoppingCartItem
Foreign key constraint referencing Product.ProductID. Sales.SpecialOfferProduct
5.1.5. Table: Production.ProductCategory
High-level product categorization.
Columns
Description Data type
Name
Primary key for ProductCategory records. int
ProductCategoryID
Category description. nvarchar(50)
Name
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
uniqueidentifier rowguid
Date and time the record was last updated. datetime ModifiedDate
Linked from
Name / Description Join Table FK_ProductSubcategory_ProductCategory_ProductCategoryID: Foreign key constraint referencing ProductCategory.ProductCategoryID. Production.ProductSubcategory.Produ ctCategoryID = ProductCategoryID ProductSubcategoryUnique keys
Description Columns NamePrimary key (clustered) constraint ProductCategoryID
PK_ProductCategory_ProductCateg oryID
Unique nonclustered index. Name
Description Columns
Name
Unique nonclustered index. Used to support replication samples. rowguid AK_ProductCategory_rowguid
Used by
Description Name Production.ProductCategoryForeign key constraint referencing ProductCategory.ProductCategoryID. Production.ProductSubcategory
5.1.6. Table: Production.ProductDescription
Product descriptions in several languages.
Columns
Description Data type
Name
Primary key for ProductDescription records. int
ProductDescriptionID
Description of the product. nvarchar(400)
Description
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
uniqueidentifier rowguid
Date and time the record was last updated. datetime ModifiedDate
Linked from
Name / Description Join Table FK_ProductModelProductDescriptionCulture_ProductDescription_Prod uctDescriptionID: Foreign key constraint referencingProductDescription.ProductDescriptionID. Production.ProductModelProductDesc riptionCulture.ProductDescriptionID = ProductDescriptionID ProductModelProductDescriptionC ulture
Unique keys
Description Columns NamePrimary key (clustered) constraint ProductDescriptionID
PK_ProductDescription_ProductDes criptionID
Unique nonclustered index. Used to support replication samples. rowguid AK_ProductDescription_rowguid
Used by
Description Name Production.ProductDescriptionForeign key constraint referencing ProductDescription.ProductDescriptionID. Production.ProductModelProduct
DescriptionCulture
5.1.7. Table: Production.ProductDocument
Cross-reference table mapping products to related product documents.
Columns
Description Data type
Name
Product identification number. Foreign key to Product.ProductID. int
ProductID
Document identification number. Foreign key to Document.DocumentNode. hierarchyid
DocumentNode
Date and time the record was last updated. datetime
