ADVENTURE WORKS CYCLES BUSINESS

SCENARIOS DOCUMENTATION

INTRODUCTION

Adventure Works Cycles, the fictitious company on which the AdventureWorks sample
databases are based, is a large, multinational manufacturing company. The company
manufactures and sells metal and composite bicycles to North American, European and Asian
commercial markets. While its base operation is located in Bothell, Washington with 290
employees, several regional sales teams are located throughout their market base.

In 2000, Adventure Works Cycles bought a small manufacturing plant, Importadores Neptuno,
located in Mexico. Importadores Neptuno manufactures several critical subcomponents for the
Adventure Works Cycles product line. These subcomponents are shipped to the Bothell location
for final product assembly. In 2001, Importadores Neptuno, became the sole manufacturer and
distributor of the touring bicycle product group.

Coming off a successful fiscal year, Adventure Works Cycles is looking to broaden its market
share by targeting their sales to their best customers, extending their product availability through
an external Web site, and reducing their cost of sales through lower production costs.

Important
The example companies, organizations, products, domain names, e-mail addresses, logos,
people, places, and events depicted herein are fictitious. No association with any real company,
organization, product, domain name, e-mail address, logo, person, places, or events is intended
or should be inferred.

SALES AND MARKETING SCENARIO

Customer and sales-related information is a significant part of the AdventureWorks sample

database. This topic provides details about the customers that are represented in the sample
database, a schema of the major customer and sales tables and sample queries that demonstrate
table relationships.

CUSTOMERS TYPES

As a bicycle manufacturing company, Adventure Works Cycles has two types of customers:
  Individuals. These are consumers who buy products from the Adventure Works Cycles
online store.
 Stores. These are retail or wholesale stores that buy products for resale from Adventure
Works Cycles sales representatives.

The Customer table contains one record for each customer.

Customer Number of
Additional information
type customers
Sales and demographic data have been trended for data mining
scenarios.
Individual 18,484
Demographic data (income, hobbies, number of cars, and so on) is
stored as xml data in the Demographics column of the Individual
table.
Data has been trended for Analysis Services scenarios.

Stores are categorized by size: large, medium, and small.

Demographic data stored as xml data.

Store 701
Store contacts are employees of the store who interact with
Adventure Works Cycles sales representatives. For example, the
store owner or purchasing manager would be typical contacts for
Adventure Works Cycles salespeople.

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.

Contains this kind of

Schema.Table Comment
content
The
ProductAssemblyID
column represents the
A list of all the parent, or primary,
components used to product, and
Production.BillOfMaterials
manufacture bicycles and ComponentID
bicycle subassemblies. represents the child, or
individual, parts used to
build the parent
assembly.
Product descriptions are
The languages used in available in Arabic,
Production.Culture localized product English, French, Hebrew,
descriptions. 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
Production.Location
Paint Storage location in
the warehouse and in the
manufacturing work
center, Paint Shop, where
the bicycle frames are
painted.
The FinishedGoodsFlag
Information about each column indicates whether
product sold by Adventure a product is sold.
Works Cycles or used to Products that are not sold
Production.Product
manufacture Adventure are components of a
Works Cycles bicycles product that is sold. For
and bicycle components. 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.
Production.ProductCategory
For example, bike or
accessory.
The cost of products over
Production.ProductCostHistory
time.
Product descriptions are
A full description of provided in Arabic,
Production.ProductDescription products in various English, French, Hebrew,
languages. Simplified Chinese, and
Thai.
The inventory level of
products by their location.
Production.ProductInventory
See Production.Location
previously mentioned.
The list price of products
Production.ProductListPriceHistory
over time.
The CatalogDescription
column contains
additional product
The product models information by using the
associated with products. xml data type. The
Production.ProductModel
For example, Mountain- Instructions column
100 or LL Touring Frame. contains product
manufacturing
instructions by using the
xml data type.
Cross-reference between
product models, product
ProductModelProductDescriptionCulture descriptions, and the
languages the description
has been localized to.
Images of products sold The images are stored by
Production.ProductPhoto by Adventure Works using the varbinary(max)
Cycles. data type.
Customer reviews of
Production.ProductReview Adventure Works Cycles
products.
Subcategories of product
Production.ProductSubcategory categories. For example,
Mountain, Road, and
 Touring are subcategories
of the category Bike.

PURCHASING AND VENDOR SCENARIO

At Adventure Works Cycles, the purchasing department buys raw materials and parts used in the
manufacture of Adventure Works Cycles bicycles. Adventure Works Cycles also purchases
products for resale, such as bicycle apparel and bicycle add-ons like water bottles and pumps.
The information about these products and the vendors from whom they are obtained is stored in
the AdventureWorks sample database.

This topic provides details about the vendors represented in the sample database, a schema
diagram of the major vendor-related tables and sample queries that demonstrate common table
relationships.

VENDOR AND PURCHASIN G TABLES

The following table contains a brief description of the data stored in these tables.

Contains this kind

Schema.Table Comments
of content
Street address
information for all
customers.
The associative table VendorAddress
maps vendors to their addresses.
Customers may
have more than one
Person.Address The Address table also contains
address. For
address information for Adventure
example, a customer
Works Cycles employees and
may have a billing
customers.
address and a
different address for
shipping.
Names of vendor
The associative table VendorContact
employees with
maps contacts to vendors.
whom Adventure
Works Cycles
The column AdditionalContactInfo
purchasing agents
Person.Contact contains data such as additional
order products.
telephone numbers (cell telephone,
fax, and so on) specific to the contact.
A vendor may have
The column is an xml data type. For
more than one
more information, see About the
contact. For
 example, a sales Contact.AdditionalContactInfo xml
agent and a sales Column.
manager. The
Adventure Works
Cycles purchasing
agent may have the
sales agent as a
primary customer
contact and the sales
manager as a
secondary contact.
Maps vendors to the
products they
supply.

A product may be
Production.ProductVendor
supplied by more
than one vendor,
and a vendor may
supply more than
one product.
Details of the
purchase order, such
Purchasing.PurchaseOrderDetail as products ordered,
quantity, and unit
price.
Purchase order
summary The PurchaseOrderHeader and
Purchasing.PurchaseOrderHeader information, such as PurchaseOrderDetail tables together
total due, order date, create a master-detail relationship.
and order status.
A lookup table that
The ShipMethodID column is
is used to maintain
Purchasing.ShipMethod included in the
standard ways of
PurchaseOrderHeader table.
shipping products.
Details about
vendors, such as the
Purchasing.Vendor
vendor name and
account number.
Links customers to Addresses are categorized by type,
address information such as billing, home, shipping, and so
Purchasing.VendorAddress
in the Address on). The AddressTypeID column
table. maps to the AddressType table.
 Street address
information for all
customers.

Customers may
have more than one This is an associative table. See the
Purchasing.VendorContact
address. For Contact and Vendor tables.
example, a customer
may have a billing
address and a
different address for
shipping.

MANUFACTURING SCENARIO

This topic provides details about the Adventure Works Cycles manufacturing information that is
represented in the AdventureWorks sample database, a list of manufacturing-related tables, and
sample queries that demonstrate common table relationships.

MANUFACTURING OVERVIEW

In the AdventureWorks sample database, tables are provided that support the following typical
manufacturing areas:

 Manufacturing processes:
o Bill of materials: Lists the products that are used or contained in another product.
o Work orders: Manufacturing orders by work center.
o Locations: Defines the major manufacturing and inventory areas, such as frame
forming, paint, subassembly, and so on.
o Manufacturing and product assembly instructions by work center.
 Product inventory: The physical location of a product in the warehouse or manufacturing
area, and the quantity available in that area.
 Engineering documentation: Technical specifications and maintenance documentation for
bicycles or bicycle components.

MANUFACTURING TABLES

The following table contains a brief description of the data that is stored in the manufacturing
tables.
 Schema.Table Contains this kind of content Comment
There is an intrinsic recursive
relationship in the bill of
material structure that indicates
the relationship between a
parent product and the
components that make up that
product. For example, if the
parent product is a bicycle, the
first-level component might be
a wheel assembly. The wheel
assembly has its own
components, such as reflectors,
rims, spokes, tires, and tire
tubes.
A list of all the components
The ProductAssemblyID
Production.BillOfMaterials used to manufacture bicycles
column represents the parent,
and bicycle subassemblies.
or primary, product and
ComponentID represents the
child, or individual, parts used
to build the parent assembly.

The BOM_Level column

indicates the level of the
ComponentID relative to the
ProductAssemblyID. In the
previous example, the wheel
assembly would have a
BOM_Level of 1, the
components of the wheel
assembly would have a
BOM_Level of 2, and so on.
The DocumentSummary
column uses the varchar(max)
Engineering specifications and
Production.Document data type. The Document
other technical documentation.
column uses the
varbinary(max) data type.
The illustrations are rendered
in the manufacturing
Bicycle manufacturing instructions that are contained
Production.Illustration
illustrations. in the ProductModel table.
This column uses the xml data
type.
 A list of inventory and
manufacturing areas within
Adventure Works Cycles in
which the products and parts
are stored as inventory or built.
Production.Location 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.
The FinishedGoodsFlag
column indicates whether a
Information about each
product is sold. Products that
product sold by Adventure
are not sold are components of
Works Cycles or used to
Production.Product a product that is sold. For
manufacture Adventure Works
example, a bicycle would be
Cycles bicycles and bicycle
sold, but the sheet of metal
components.
used to create the bicycle
frame would not.
The inventory level of
products by their location. See
Production.ProductInventory
Production.Location
previously mentioned.
The CatalogDescription
column contains additional
The product models associated product information by using
with products. For example, the xml data type. The
Production.ProductModel
Mountain-100 or LL Touring Instructions column contains
Frame. product manufacturing
instructions by using the xml
data type
The WorkOrderRouting
A list of common reasons why
table tracks the quantity
bicycles or bicycles parts are
scrapped and the reason for
rejected during the
scrapping by product.
manufacturing process. For
Production.ScrapReason example, the scrap reason
Depending on the severity of
'Paint failed' is used in the
the problem, the product must
Paint work center to reject a
be fixed or replaced before the
bicycle frame for which the
product can move to the next
paint did not cure correctly.
work center.
Defines the products and
Production.WorkOrder
quantity that must be
 manufactured to meet current
and forecasted sales.
The details for each work
order. This includes the
sequence of work centers the
product travels through in the
manufacturing or assembly
process. For example, bicycle
handlebars are manufactured
Production.WorkOrderRouting
in the Frame Forming work
center. They are moved to the
Frame Welding work center
for additional work, and then
moved to the Subassembly
work center, where they are
added to the bicycle frame.