Esquema de bases de datos: Departamentos, Empleados y Sueldos, Guías, Proyectos, Investigaciones de Sistemas Integrados

¡Descarga Esquema de bases de datos: Departamentos, Empleados y Sueldos y más Guías, Proyectos, Investigaciones en PDF de Sistemas Integrados solo en Docsity!

(Last updated on mié., may. 22nd, 2019 at 8:42 a. m.)

Generated using SQL Data Dictionary demo version.

AdventureWorks

AdventureWorks 2012 Sample OLTP Database

I

Tables:

dbo.AWBuildVersion (1 row) ................................................................................... 1

Current version number of the AdventureWorks 2012 sample database.

dbo.DatabaseLog (1,597 rows) ................................................................................ 1

Audit table tracking all DDL changes made to the AdventureWorks database. Data is captured by the database trigger ddlDatabaseTriggerLog.

dbo.ErrorLog (0 rows) ............................................................................................. 1

Audit table tracking errors in the the AdventureWorks database that are caught by the CATCH block of a TRY...CATCH construct. Data is inserted by stored procedure dbo.uspLogError when it is executed from inside the CATCH block of a TRY...CATCH construct.

HumanResources.Department (16 rows) ................................................................. 2

Lookup table containing the departments within the Adventure Works Cycles company.

HumanResources.Employee (290 rows) .................................................................. 3

Employee information such as salary, department, and title.

HumanResources.EmployeeDepartmentHistory (296 rows) ..................................... 5

Employee department transfers.

HumanResources.EmployeePayHistory (316 rows) .................................................. 5

Employee pay history.

HumanResources.JobCandidate (13 rows) .............................................................. 6

Résumés submitted to Human Resources by job applicants.

HumanResources.Shift (3 rows) .............................................................................. 7

Work shift lookup table.

Person.Address (19,614 rows) ................................................................................. 7

Street address information for customers, employees, and vendors.

Person.AddressType (6 rows) ................................................................................. 9

Types of addresses stored in the Address table.

Person.BusinessEntity (20,777 rows) ...................................................................... 9

Source of the ID that connects vendors, customers, and employees with address and contact information.

Person.BusinessEntityAddress (19,614 rows) ........................................................ 10

Cross-reference table mapping customers, vendors, and employees to their addresses.

Person.BusinessEntityContact (909 rows) ............................................................. 11

Cross-reference table mapping stores, vendors, and employees to people

Person.ContactType (20 rows) .............................................................................. 12

Lookup table containing the types of business entity contacts.

Person.CountryRegion (238 rows) ......................................................................... 12

Lookup table containing the ISO standard codes for countries and regions.

Person.EmailAddress (19,972 rows) ...................................................................... 13

Where to send a person email.

III

Production.ProductModelIllustration (7 rows) ....................................................... 29

Cross-reference table mapping product models and illustrations.

Production.ProductModelProductDescriptionCulture (762 rows) ............................ 30

Cross-reference table mapping product descriptions and the language the description is written in.

Production.ProductPhoto (101 rows) ..................................................................... 30

Product images.

Production.ProductProductPhoto (504 rows) ......................................................... 31

Cross-reference table mapping products and product photos.

Production.ProductReview (4 rows) ...................................................................... 31

Customer reviews of products they have purchased.

Production.ProductSubcategory (37 rows) ............................................................ 32

Product subcategories. See ProductCategory table.

Production.ScrapReason (16 rows) ....................................................................... 33

Manufacturing failure reasons lookup table.

Production.TransactionHistory (113,443 rows) ...................................................... 33

Record of each purchase order, sales order, or work order transaction year to date.

Production.TransactionHistoryArchive (89,253 rows) ............................................ 34

Transactions for previous years.

Production.UnitMeasure (38 rows) ........................................................................ 35

Unit of measure lookup table.

Production.WorkOrder (72,591 rows) .................................................................... 35

Manufacturing work orders.

Production.WorkOrderRouting (67,131 rows) ........................................................ 36

Work order details.

Purchasing.ProductVendor (460 rows) .................................................................. 37

Cross-reference table mapping vendors with the products they supply.

Purchasing.PurchaseOrderDetail (8,845 rows) ...................................................... 38

Individual products associated with a specific purchase order. See PurchaseOrderHeader.

Purchasing.PurchaseOrderHeader (4,012 rows) .................................................... 39

General purchase order information. See PurchaseOrderDetail.

Purchasing.ShipMethod (5 rows) ........................................................................... 40

Shipping company lookup table.

Purchasing.Vendor (104 rows) .............................................................................. 41

Companies from whom Adventure Works Cycles purchases parts or other goods.

Sales.CountryRegionCurrency (109 rows) .............................................................. 42

Cross-reference table mapping ISO currency codes to a country or region.

Sales.CreditCard (19,118 rows) ............................................................................. 42

IV

Customer credit card information.

Sales.Currency (105 rows) .................................................................................... 43

Lookup table containing standard ISO currencies.

Sales.CurrencyRate (13,532 rows) ......................................................................... 44

Currency exchange rates.

Sales.Customer (19,820 rows) ............................................................................... 44

Current customer information. Also see the Person and Store tables.

Sales.PersonCreditCard (19,118 rows) ................................................................... 45

Cross-reference table mapping people to their credit card information in the CreditCard table.

Sales.SalesOrderDetail (121,317 rows) .................................................................. 46

Individual products associated with a specific sales order. See SalesOrderHeader.

Sales.SalesOrderHeader (31,465 rows) .................................................................. 47

General sales order information.

Sales.SalesOrderHeaderSalesReason (27,647 rows) .............................................. 49

Cross-reference table mapping sales orders to sales reason codes.

Sales.SalesPerson (17 rows) ................................................................................. 49

Sales representative current information.

Sales.SalesPersonQuotaHistory (163 rows) ........................................................... 50

Sales performance tracking.

Sales.SalesReason (10 rows) ................................................................................. 51

Lookup table of customer purchase reasons.

Sales.SalesTaxRate (29 rows) ............................................................................... 51

Tax rate lookup table.

Sales.SalesTerritory (10 rows) .............................................................................. 52

Sales territory lookup table.

Sales.SalesTerritoryHistory (17 rows) ................................................................... 53

Sales representative transfers to other sales territories.

Sales.ShoppingCartItem (3 rows) ......................................................................... 54

Contains online customer orders until the order is submitted or cancelled.

Sales.SpecialOffer (16 rows) ................................................................................. 54

Sale discounts lookup table.

Sales.SpecialOfferProduct (538 rows) ................................................................... 55

Cross-reference table mapping products to special offer discounts.

Sales.Store (701 rows) .......................................................................................... 56

Customers (resellers) of Adventure Works products.

Views:

VI

Uses PIVOT to return aggregated sales information for each sales representative.

Sales.vStoreWithAddresses ................................................................................. 66

Stores (including store addresses) that sell Adventure Works Cycles products to consumers.

Sales.vStoreWithContacts .................................................................................... 67

Stores (including store contacts) that sell Adventure Works Cycles products to consumers.

Sales.vStoreWithDemographics ........................................................................... 67

Stores (including demographics) that sell Adventure Works Cycles products to consumers.

Procedures:

dbo.uspGetBillOfMaterials .................................................................................... 68

Stored procedure using a recursive query to return a multi-level bill of material for the specified ProductID.

dbo.uspGetEmployeeManagers ............................................................................ 68

Stored procedure using a recursive query to return the direct and indirect managers of the specified employee.

dbo.uspGetManagerEmployees ............................................................................ 69

Stored procedure using a recursive query to return the direct and indirect employees of the specified manager.

dbo.uspGetWhereUsedProductID ......................................................................... 69

Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID.

dbo.uspLogError .................................................................................................. 70

Logs error information in the ErrorLog table about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without inserting error information.

dbo.uspPrintError ................................................................................................ 70

Prints error information about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without printing any error information.

dbo.uspSearchCandidateResumes ........................................................................ 71

HumanResources.uspUpdateEmployeeHireInfo ................................................... 71

Updates the Employee table and inserts a new row in the EmployeePayHistory table with the values specified in the input parameters.

HumanResources.uspUpdateEmployeeLogin ........................................................ 71

Updates the Employee table with the values specified in the input parameters for the given BusinessEntityID.

HumanResources.uspUpdateEmployeePersonalInfo ............................................ 72

Updates the Employee table with the values specified in the input parameters for the given EmployeeID.

Functions:

dbo.ufnGetAccountingEndDate ............................................................................ 73

Scalar function used in the uSalesOrderHeader trigger to set the starting account date.

VII

dbo.ufnGetAccountingStartDate ........................................................................... 73

Scalar function used in the uSalesOrderHeader trigger to set the ending account date.

dbo.ufnGetContactInformation ............................................................................ 73

Table value function returning the first name, last name, job title and contact type for a given contact.

dbo.ufnGetDocumentStatusText .......................................................................... 74

Scalar function returning the text representation of the Status column in the Document table.

dbo.ufnGetProductDealerPrice ............................................................................. 74

Scalar function returning the dealer price for a given product on a particular order date.

dbo.ufnGetProductListPrice ................................................................................. 74

Scalar function returning the list price for a given product on a particular order date.

dbo.ufnGetProductStandardCost .......................................................................... 75

Scalar function returning the standard cost for a given product on a particular order date.

dbo.ufnGetPurchaseOrderStatusText ................................................................... 75

Scalar function returning the text representation of the Status column in the PurchaseOrderHeader table.

dbo.ufnGetSalesOrderStatusText ......................................................................... 75

Scalar function returning the text representation of the Status column in the SalesOrderHeader table.

dbo.ufnGetStock .................................................................................................. 75

Scalar function returning the quantity of inventory in LocationID 6 (Miscellaneous Storage)for a specified ProductID.

dbo.ufnLeadingZeros ........................................................................................... 76

Scalar function used by the Sales.Customer table to help set the account number.

Sales.udfNetSale .................................................................................................. 76

Page 2 of 76

construct. Data is inserted by stored procedure dbo.uspLogError when it is executed from inside the CATCH block of a TRY...CATCH construct.

Column Data Type Identity Nullable Default PK ErrorLogID int X Primary key for ErrorLog records. ErrorTime datetime getdate() The date and time at which the error occurred. UserName sysname(nvarchar(128)) The user who executed the batch in which the error occurred. ErrorNumber int The error number of the error that occurred. ErrorSeverity int X The severity of the error that occurred. ErrorState int X The state number of the error that occurred. ErrorProcedure nvarchar(126) X The name of the stored procedure or trigger where the error occurred. ErrorLine int X The line number at which the error occurred. ErrorMessage nvarchar(4000) The message text of the error that occurred.

Indexes:

PK_ErrorLog_ErrorLogID (Primary Key) (Clustered)

Primary key (clustered) constraint

ErrorLogID

Used by:

Procedure dbo.uspLogError

H u m a n R e s o u r c e s . D e p a r t m e n t

Table HumanResources.Department (16 rows)

Lookup table containing the departments within the Adventure Works Cycles company.

Column Data Type Identity Nullable Default PK DepartmentID smallint X Primary key for Department records. UK Name Name(nvarchar(50)) Name of the department. GroupName Name(nvarchar(50)) Name of the group to which the department belongs. ModifiedDate datetime getdate() Date and time the record was last updated.

Indexes:

PK_Department_DepartmentID (Primary Key) (Clustered)

Primary key (clustered) constraint

DepartmentID

AK_Department_Name (Unique)

Unique nonclustered index.

Page 3 of 76

Name

Referenced by:

HumanResources.EmployeeDepartmentHistory (DepartmentID)

Used by:

View HumanResources.vEmployeeDepartment

View HumanResources.vEmployeeDepartmentHistory

H u m a n R e s o u r c e s . E m p l o y e e

Table HumanResources.Employee (290 rows)

Employee information such as salary, department, and title.

Column Data Type Identity Nullable Default PK, FK BusinessEntityID int Primary key for Employee records. Foreign key to BusinessEntity.BusinessEntityID. UK NationalIDNumber nvarchar(15) Unique national identification number such as a social security number. UK LoginID nvarchar(256) Network login. OrganizationNode hierarchyid X Where the employee is located in corporate hierarchy. OrganizationLevel smallint X The depth of the employee in the corporate hierarchy. JobTitle nvarchar(50) Work title such as Buyer or Sales Representative. BirthDate date Date of birth. MaritalStatus nchar(1) M = Married, S = Single Gender nchar(1) M = Male, F = Female HireDate date Employee hired on this date. SalariedFlag Flag(bit) 1 Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining. VacationHours smallint 0 Number of available vacation hours. SickLeaveHours smallint 0 Number of available sick leave hours. CurrentFlag Flag(bit) 1 0 = Inactive, 1 = Active UK rowguid uniqueidentifier newid() ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. ModifiedDate datetime getdate() Date and time the record was last updated.

Indexes:

PK_Employee_BusinessEntityID (Primary Key) (Clustered)

Primary key (clustered) constraint

BusinessEntityID

Page 5 of 76

View HumanResources.vEmployeeDepartment

View HumanResources.vEmployeeDepartmentHistory

View Sales.vSalesPerson

View Sales.vSalesPersonSalesByFiscalYears

H u m a n R e s o u r c e s . E m p l o y e e D e p a r t m e n t H i s t o r y

Table HumanResources.EmployeeDepartmentHistory (296 rows)

Employee department transfers.

Column Data Type Identity Nullable Default PK, FK BusinessEntityID int Employee identification number. Foreign key to Employee.BusinessEntityID. PK, FK DepartmentID smallint Department in which the employee worked including currently. Foreign key to Department.DepartmentID. PK, FK ShiftID tinyint Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID. PK StartDate date Date the employee started work in the department. EndDate date X Date the employee left the department. NULL = Current department. ModifiedDate datetime getdate() Date and time the record was last updated.

Indexes:

PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID (Primary Key) (Clustered)

Primary key (clustered) constraint

BusinessEntityID StartDate DepartmentID ShiftID

IX_EmployeeDepartmentHistory_DepartmentID

Nonclustered index.

DepartmentID

IX_EmployeeDepartmentHistory_ShiftID

Nonclustered index.

ShiftID

References:

HumanResources.Department (DepartmentID)

HumanResources.Employee (BusinessEntityID)

HumanResources.Shift (ShiftID)

Used by:

View HumanResources.vEmployeeDepartment

View HumanResources.vEmployeeDepartmentHistory

H u m a n R e s o u r c e s . E m p l o y e e P a y H i s t o r y

Table HumanResources.EmployeePayHistory (316 rows)

Page 6 of 76

Employee pay history.

Column Data Type Identity Nullable Default PK, FK BusinessEntityID int Employee identification number. Foreign key to Employee.BusinessEntityID. PK RateChangeDate datetime Date the change in pay is effective Rate money Salary hourly rate. PayFrequency tinyint 1 = Salary received monthly, 2 = Salary received biweekly ModifiedDate datetime getdate() Date and time the record was last updated.

Indexes:

PK_EmployeePayHistory_BusinessEntityID_RateChangeDate (Primary Key) (Clustered)

Primary key (clustered) constraint

BusinessEntityID RateChangeDate

References:

HumanResources.Employee (BusinessEntityID)

Used by:

Procedure HumanResources.uspUpdateEmployeeHireInfo

H u m a n R e s o u r c e s . J o b C a n d i d a t e

Table HumanResources.JobCandidate (13 rows)

Résumés submitted to Human Resources by job applicants.

Column Data Type Identity Nullable Default PK JobCandidateID int X Primary key for JobCandidate records. FK BusinessEntityID int X Employee identification number if applicant was hired. Foreign key to Employee.BusinessEntityID. Resume xml X Résumé in XML format. ModifiedDate datetime getdate() Date and time the record was last updated.

Indexes:

PK_JobCandidate_JobCandidateID (Primary Key) (Clustered)

Primary key (clustered) constraint

JobCandidateID

IX_JobCandidate_BusinessEntityID

Nonclustered index.

BusinessEntityID

References:

Page 8 of 76

UK AddressLine1 nvarchar(60) First street address line. UK AddressLine2 nvarchar(60) X Second street address line. UK City nvarchar(30) Name of the city. UK, FK StateProvinceID int Unique identification number for the state or province. Foreign key to StateProvince table. UK PostalCode nvarchar(15) Postal code for the street address. SpatialLocation geography X Latitude and longitude of this address. UK rowguid uniqueidentifier newid() ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. ModifiedDate datetime getdate() Date and time the record was last updated.

Indexes:

PK_Address_AddressID (Primary Key) (Clustered)

Primary key (clustered) constraint

AddressID

AK_Address_rowguid (Unique)

Unique nonclustered index. Used to support replication samples.

rowguid

IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode (Unique)

Nonclustered index.

AddressLine AddressLine City StateProvinceID PostalCode

IX_Address_StateProvinceID

Nonclustered index.

StateProvinceID

References:

Person.StateProvince (StateProvinceID)

Referenced by:

Person.BusinessEntityAddress (AddressID)

Sales.SalesOrderHeader (BillToAddressID - > AddressID)

Sales.SalesOrderHeader (ShipToAddressID - > AddressID)

Used by:

View HumanResources.vEmployee

View Purchasing.vVendorWithAddresses

Page 9 of 76

View Sales.vIndividualCustomer

View Sales.vSalesPerson

View Sales.vStoreWithAddresses

P e r s o n . A d d r e s s T y p e

Table Person.AddressType (6 rows)

Types of addresses stored in the Address table.

Column Data Type Identity Nullable Default PK AddressTypeID int X Primary key for AddressType records. UK Name Name(nvarchar(50)) Address type description. For example, Billing, Home, or Shipping. UK rowguid uniqueidentifier newid() ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. ModifiedDate datetime getdate() Date and time the record was last updated.

Indexes:

PK_AddressType_AddressTypeID (Primary Key) (Clustered)

Primary key (clustered) constraint

AddressTypeID

AK_AddressType_Name (Unique)

Unique nonclustered index.

Name

AK_AddressType_rowguid (Unique)

Unique nonclustered index. Used to support replication samples.

rowguid

Referenced by:

Person.BusinessEntityAddress (AddressTypeID)

Used by:

View Purchasing.vVendorWithAddresses

View Sales.vIndividualCustomer

View Sales.vStoreWithAddresses

P e r s o n . B u s i n e s s E n t i t y

Table Person.BusinessEntity (20,777 rows)

Source of the ID that connects vendors, customers, and employees with address and contact information.

Column Data Type Identity Nullable Default PK BusinessEntityID int X Primary key for all customers, vendors, and employees. UK rowguid uniqueidentifier newid() ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. ModifiedDate datetime getdate()

Page 11 of 76

AddressID

IX_BusinessEntityAddress_AddressTypeID

Nonclustered index.

AddressTypeID

References:

Person.Address (AddressID)

Person.AddressType (AddressTypeID)

Person.BusinessEntity (BusinessEntityID)

Used by:

View HumanResources.vEmployee

View Purchasing.vVendorWithAddresses

View Sales.vIndividualCustomer

View Sales.vSalesPerson

View Sales.vStoreWithAddresses

P e r s o n . B u s i n e s s E n t i t y C o n t a c t

Table Person.BusinessEntityContact (909 rows)

Cross-reference table mapping stores, vendors, and employees to people

Column Data Type Identity Nullable Default PK, FK BusinessEntityID int Primary key. Foreign key to BusinessEntity.BusinessEntityID. PK, FK PersonID int Primary key. Foreign key to Person.BusinessEntityID. PK, FK ContactTypeID int Primary key. Foreign key to ContactType.ContactTypeID. UK rowguid uniqueidentifier newid() ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. ModifiedDate datetime getdate() Date and time the record was last updated.

Indexes:

PK_BusinessEntityContact_BusinessEntityID_PersonID_ContactTypeID (Primary Key) (Clustered)

Primary key (clustered) constraint

BusinessEntityID PersonID ContactTypeID

AK_BusinessEntityContact_rowguid (Unique)

Unique nonclustered index. Used to support replication samples.

rowguid

IX_BusinessEntityContact_ContactTypeID

Nonclustered index.

Page 12 of 76

ContactTypeID

IX_BusinessEntityContact_PersonID

Nonclustered index.

PersonID

References:

Person.BusinessEntity (BusinessEntityID)

Person.ContactType (ContactTypeID)

Person.Person (PersonID - > BusinessEntityID)

Used by:

Function dbo.ufnGetContactInformation

View Purchasing.vVendorWithContacts

View Sales.vStoreWithContacts

P e r s o n . C o n t a c t T y p e

Table Person.ContactType (20 rows)

Lookup table containing the types of business entity contacts.

Column Data Type Identity Nullable Default PK ContactTypeID int X Primary key for ContactType records. UK Name Name(nvarchar(50)) Contact type description. ModifiedDate datetime getdate() Date and time the record was last updated.

Indexes:

PK_ContactType_ContactTypeID (Primary Key) (Clustered)

Primary key (clustered) constraint

ContactTypeID

AK_ContactType_Name (Unique)

Unique nonclustered index.

Name

Referenced by:

Person.BusinessEntityContact (ContactTypeID)

Used by:

Function dbo.ufnGetContactInformation

View Purchasing.vVendorWithContacts

View Sales.vStoreWithContacts

P e r s o n . C o u n t r y R e g i o n

Table Person.CountryRegion (238 rows)

Lookup table containing the ISO standard codes for countries and regions.