AdventureWorks

a bicycle manufacturer - Adventure Works Cycles

Scenarios include Manufacturing, Sales, Purchasing, Product Management, Contact Management, and Human Resources

大略可分為9組DR關聯圖

Business Entities

People

Human Resources

Products

Manufacturing

Purchasing

Inventory

Sales

Admin

AdventureWorks – Data Dictionary

https://dataedo.com/samples/html/AdventureWorks/doc/AdventureWorks_2/home.html

Query Table Description from AdventureWorks Database

SELECT 

TABLE_NAME AS TableName, 

    --TABLE_TYPE AS tTableType,

    --q.epTableName,

    q.epExtendedProperty AS ExtendedProperty

FROM information_schema.tables AS t

    LEFT OUTER JOIN (SELECT OBJECT_NAME(ep.major_id) AS [epTableName],

        CAST(ep.Value AS nvarchar(500)) AS [epExtendedProperty]

        FROM sys.extended_properties ep

        WHERE ep.name = N'MS_Description' AND ep.minor_id = 0) As q

    ON t.table_name = q.epTableName 

WHERE TABLE_TYPE = N'BASE TABLE' 

ORDER BY TABLE_NAME

TableName	ExtendedProperty
Address	Street address information for customers, employees, and vendors.
AddressType	Types of addresses stored in the Address table.
AWBuildVersion	Current version number of the AdventureWorks 2016 sample database.
BillOfMaterials	Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.
BusinessEntity	Source of the ID that connects vendors, customers, and employees with address and contact information.
BusinessEntityAddress	Cross-reference table mapping customers, vendors, and employees to their addresses.
BusinessEntityContact	Cross-reference table mapping stores, vendors, and employees to people
ContactType	Lookup table containing the types of business entity contacts.
CountryRegion	Lookup table containing the ISO standard codes for countries and regions.
CountryRegionCurrency	Cross-reference table mapping ISO currency codes to a country or region.
CreditCard	Customer credit card information.
Culture	Lookup table containing the languages in which some AdventureWorks data is stored.
Currency	Lookup table containing standard ISO currencies.
CurrencyRate	Currency exchange rates.
Customer	Current customer information. Also see the Person and Store tables.
DatabaseLog	Audit table tracking all DDL changes made to the AdventureWorks database. Data is captured by the database trigger ddlDatabaseTriggerLog.
Department	Lookup table containing the departments within the Adventure Works Cycles company.
Document	Product maintenance documents.
EmailAddress	Where to send a person email.
Employee	Employee information such as salary, department, and title.
EmployeeDepartmentHistory	Employee department transfers.
EmployeePayHistory	Employee pay history.
ErrorLog	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.
Illustration	Bicycle assembly diagrams.
JobCandidate	Résumés submitted to Human Resources by job applicants.
Location	Product inventory and manufacturing locations.
Password	One way hashed authentication information
Person	Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.
PersonCreditCard	Cross-reference table mapping people to their credit card information in the CreditCard table.
PersonPhone	Telephone number and type of a person.
PhoneNumberType	Type of phone number of a person.
Product	Products sold or used in the manfacturing of sold products.
ProductCategory	High-level product categorization.
ProductCostHistory	Changes in the cost of a product over time.
ProductDescription	Product descriptions in several languages.
ProductDocument	Cross-reference table mapping products to related product documents.
ProductInventory	Product inventory information.
ProductListPriceHistory	Changes in the list price of a product over time.
ProductModel	Product model classification.
ProductModelIllustration	Cross-reference table mapping product models and illustrations.
ProductModelProductDescriptionCulture	Cross-reference table mapping product descriptions and the language the description is written in.
ProductPhoto	Product images.
ProductProductPhoto	Cross-reference table mapping products and product photos.
ProductReview	Customer reviews of products they have purchased.
ProductSubcategory	Product subcategories. See ProductCategory table.
ProductVendor	Cross-reference table mapping vendors with the products they supply.
PurchaseOrderDetail	Individual products associated with a specific purchase order. See PurchaseOrderHeader.
PurchaseOrderHeader	General purchase order information. See PurchaseOrderDetail.
SalesOrderDetail	Individual products associated with a specific sales order. See SalesOrderHeader.
SalesOrderHeader	General sales order information.
SalesOrderHeaderSalesReason	Cross-reference table mapping sales orders to sales reason codes.
SalesPerson	Sales representative current information.
SalesPersonQuotaHistory	Sales performance tracking.
SalesReason	Lookup table of customer purchase reasons.
SalesTaxRate	Tax rate lookup table.
SalesTerritory	Sales territory lookup table.
SalesTerritoryHistory	Sales representative transfers to other sales territories.
ScrapReason	Manufacturing failure reasons lookup table.
Shift	Work shift lookup table.
ShipMethod	Shipping company lookup table.
ShoppingCartItem	Contains online customer orders until the order is submitted or cancelled.
SpecialOffer	Sale discounts lookup table.
SpecialOfferProduct	Cross-reference table mapping products to special offer discounts.
StateProvince	State and province lookup table.
Store	Customers (resellers) of Adventure Works products.
TransactionHistory	Record of each purchase order, sales order, or work order transaction year to date.
TransactionHistoryArchive	Transactions for previous years.
UnitMeasure	Unit of measure lookup table.
Vendor	Companies from whom Adventure Works Cycles purchases parts or other goods.
WorkOrder	Manufacturing work orders.
WorkOrderRouting	Work order details.

SELECT objname, name, value  

FROM fn_listextendedproperty (NULL, 'schema', 'Production', 'table', 'WorkOrder', 'column', default);  

GO

objname	name	value
WorkOrderID	MS_Description	Primary key for WorkOrder records.
ProductID	MS_Description	Product identification number. Foreign key to Product.ProductID.
OrderQty	MS_Description	Product quantity to build.
StockedQty	MS_Description	Quantity built and put in inventory.
ScrappedQty	MS_Description	Quantity that failed inspection.
StartDate	MS_Description	Work order start date.
EndDate	MS_Description	Work order end date.
DueDate	MS_Description	Work order due date.
ScrapReasonID	MS_Description	Reason for inspection failure.
ModifiedDate	MS_Description	Date and time the record was last updated.

SELECT objname, name, value  

FROM fn_listextendedproperty (NULL, 'schema', 'Sales', 'table', 'SalesOrderHeader', 'column', default);  

GO

objname	name	value
SalesOrderID	MS_Description	Primary key.
RevisionNumber	MS_Description	Incremental number to track changes to the sales order over time.
OrderDate	MS_Description	Dates the sales order was created.
DueDate	MS_Description	Date the order is due to the customer.
ShipDate	MS_Description	Date the order was shipped to the customer.
Status	MS_Description	Order current status. 1 = In process; 2 = Approved; 3 = Backordered; 4 = Rejected; 5 = Shipped; 6 = Cancelled
OnlineOrderFlag	MS_Description	0 = Order placed by sales person. 1 = Order placed online by customer.
SalesOrderNumber	MS_Description	Unique sales order identification number.
PurchaseOrderNumber	MS_Description	Customer purchase order number reference.
AccountNumber	MS_Description	Financial accounting number reference.
CustomerID	MS_Description	Customer identification number. Foreign key to Customer.BusinessEntityID.
SalesPersonID	MS_Description	Sales person who created the sales order. Foreign key to SalesPerson.BusinessEntityID.
TerritoryID	MS_Description	Territory in which the sale was made. Foreign key to SalesTerritory.SalesTerritoryID.
BillToAddressID	MS_Description	Customer billing address. Foreign key to Address.AddressID.
ShipToAddressID	MS_Description	Customer shipping address. Foreign key to Address.AddressID.
ShipMethodID	MS_Description	Shipping method. Foreign key to ShipMethod.ShipMethodID.
CreditCardID	MS_Description	Credit card identification number. Foreign key to CreditCard.CreditCardID.
CreditCardApprovalCode	MS_Description	Approval code provided by the credit card company.
CurrencyRateID	MS_Description	Currency exchange rate used. Foreign key to CurrencyRate.CurrencyRateID.
SubTotal	MS_Description	Sales subtotal. Computed as SUM(SalesOrderDetail.LineTotal)for the appropriate SalesOrderID.
TaxAmt	MS_Description	Tax amount.
Freight	MS_Description	Shipping cost.
TotalDue	MS_Description	Total due from customer. Computed as Subtotal + TaxAmt + Freight.
Comment	MS_Description	Sales representative comments.
rowguid	MS_Description	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate	MS_Description	Date and time the record was last updated.