Product Scenario
This topic provides details about the product information that is represented in the AdventureWorks sample database, a list of product-related tables, and sample queries that demonstrate common table relationships.
Product Overview
As a bicycle manufacturing company, Adventure Works Cycles has the following four product lines:
Bicycles that are manufactured at the Adventure Works Cycles company.
Bicycle components that are replacement parts, such as wheels, pedals, or brake assemblies.
Bicycle apparel that is purchased from vendors for resale to Adventure Works Cycles customers.
Bicycle accessories that are purchased from vendors for resale to Adventure Works Cycles customers.
Product Tables
The following table contains a brief description of the data stored in the product-related tables.
Schema.Table |
Contains this kind of content |
Comment |
|---|---|---|
A list of all the components used to manufacture bicycles and bicycle subassemblies. |
The ProductAssemblyID column represents the parent, or primary, product, and ComponentID represents the child, or individual, parts used to build the parent assembly. |
|
The languages used in localized product descriptions. |
Product descriptions are available in Arabic, English, French, Hebrew, Simplified Chinese, and Thai. |
|
A list of the locations within Adventure Works Cycles where products and parts are stored as inventory. For example, paint is stored in both the Paint Storage location in the warehouse and in the manufacturing work center, Paint Shop, where the bicycle frames are painted. |
|
|
Information about each product sold by Adventure Works Cycles or used to manufacture Adventure Works Cycles bicycles and bicycle components. |
The FinishedGoodsFlag column indicates whether a product is sold. Products that are not sold are components of a product that is sold. For example, a bicycle would be sold, but the sheet of metal used to create the bicycle frame would not. |
|
The most general classification of products. For example, bike or accessory. |
|
|
The cost of products over time. |
|
|
A full description of products in various languages. |
Product descriptions are provided in Arabic, English, French, Hebrew, Simplified Chinese, and Thai. |
|
The inventory level of products by their location. See Production.Location previously mentioned. |
|
|
The list price of products over time. |
|
|
The product models associated with products. For example, Mountain-100 or LL Touring Frame. |
The CatalogDescription column contains additional product information by using the xml data type. The Instructions column contains product manufacturing instructions by using the xml data type. |
|
Cross-reference between product models, product descriptions, and the languages the description has been localized to. |
|
|
Images of products sold by Adventure Works Cycles. |
The images are stored by using the varbinary(max) data type. |
|
Customer reviews of Adventure Works Cycles products. |
|
|
Subcategories of product categories. For example, Mountain, Road, and Touring are subcategories of the category Bike. |
|
Examples
You can use the following queries to view product data and to become familiar with the product-table relationships.
A. Viewing products by category, subcategory, and model
The following example lists all products by category, subcategory, and model. Products that are not categorized are not included. To include all products, change the join on ProductCategory to a full join.
USE AdventureWorks;
GO
SELECT PC.Name AS Category, PSC.Name AS Subcategory,
PM.Name AS Model, P.Name AS Product
FROM Production.Product AS P
FULL JOIN Production.ProductModel AS PM ON PM.ProductModelID = P.ProductModelID
FULL JOIN Production.ProductSubcategory AS PSC ON PSC.ProductSubcategoryID = P.ProductSubcategoryID
JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryID
ORDER BY PC.Name, PSC.Name ;
GO
B. Viewing product descriptions by product model
Product descriptions are created for each product model. Each description is available in multiple languages. The following example displays each product description in each language.
Note
Some languages may not display correctly unless the supplemental language support files for complex script and East Asian languages have been installed. To install these files, see the Windows documentation on Regional and Language Options.
USE AdventureWorks;
GO
SELECT PM.ProductModelID, PM.Name AS [Product Model], Description, PL.CultureID, CL.Name AS Language
FROM Production.ProductModel AS PM
JOIN Production.ProductModelProductDescriptionCulture AS PL
ON PM.ProductModelID = PL.ProductModelID
JOIN Production.Culture AS CL ON CL.CultureID = PL.CultureID
JOIN Production.ProductDescription AS PD
ON PD.ProductDescriptionID = PL.ProductDescriptionID
ORDER BY PM.ProductModelID ;
GO
C. Viewing single single-level bill of materials list for a parent product
The following example displays all the components that are used to create a specific parent product: ProductAssemblyID.
USE AdventureWorks;
GO
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
b.EndDate, 0 AS ComponentLevel
FROM Production.BillOfMaterials AS b
WHERE b.ProductAssemblyID = 800
AND b.EndDate IS NULL
UNION ALL
SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
bom.EndDate, ComponentLevel + 1
FROM Production.BillOfMaterials AS bom
INNER JOIN Parts AS p
ON bom.ProductAssemblyID = p.ComponentID
AND bom.EndDate IS NULL
)
SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,
ComponentLevel
FROM Parts AS p
INNER JOIN Production.Product AS pr
ON p.ComponentID = pr.ProductID
ORDER BY ComponentLevel, AssemblyID, ComponentID;
GO
See Also