AdventureWorks Overview

Versions for
2005
and
2008
each have hardware platform-specific 32-bit (x86) and 64-bit (x64 and ia64) installers.

The AdventureWorks sample databases are for the ficticious AdventureWorks
-- 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."

The 2005 Version

SQL Server 2005 SP2a released May 7, 2007 assumes 32-bit (x86) in file name AdventureWorksDB.msi.
The .msi file for this version installs to
folder \Program Files\Microsoft SQL Server\100\Samples.

AdventureWorks Versions

To satisfy the need for a simple database, Microsoft now provides
LT (for LighT) - a highly simplified and smaller sample database
for developers and ITPros that are just starting to learn about relational database technology.
[Yet there fewer examples for this.]

Confirm the installation by invoking the SQL Management Studio's Object Explorer and
under "Databases", open AdventureWorks2008.

2008 vs. 2005

Versions of the database without "2008" in the name are 2005 versions.
The 2008 version improved on the 2005 version by creating new Person, PersonPhone, PersonCreditCard, and Password
tables in place of
Individual, VendorContact, StoreContact, and Contact.

The supertype/subtype contact tables in 2005 are no longer in 2008.
In 2005, when full address information is needed from
subtype tables (EmployeeAddress, CustomerAddress, and VendorAddress),
they are joined to supertype table Contact.
This drew
criticism from those using Microsoft's ADO.NET Entity Framework 1.0 (EF) to access
AdventureWorks 2005 in isolatation from the underlying logical database schemas
by referencing XML
Entity Data Models (EDM) or LINQ classes mapped from the physical schema.

Ironically, this refactoring cannot be easily used within EF 1.0
because EF has not yet been refactored to use the
new SQL Server 2008 datatypes in AdventureWorks2008 sample databases.
So there is no end-to-end for 2008?

The 2008 Person.Address table column SpatialLocation
is defined with new Geography datatype that meets
Open Geospatial Consortium (OGC) Simple Features Specification for SQL
in spatial (geographical) operations.
Unlike human-readable longitude/latitude numbers, these 47-character values
make sense only to mapping software.

Notice that both 2005 and 2008 versions avoid human-understandable, simpler
natural primary keys but use more efficient
surrogate integer keys (such as guids).
Keys consisting of strings are slower because they take up more room than interger keys
which cause database page splits and fragmentation.

2008 Conceptual Entity Model

Below is the 2008 Conceptual Entity Model (with objects moved closer together in a gif file).

Business Processes (Transactions) Using Data

Handling Future Growth

One discussion item mentions the need for a tool to grow the sample database, and
performance tests for it.

A database after several years of growth usually exhibits different response times than a small
starter database.

Larger database often require a different a different (usually more complex) structure to
more efficiently accomodate larger database sizes and higher transaction volumes. For example:

Tables holding historical data,
such as the Production.TransactionHistoryArchive table
(which does not have any linkage to other tables now) is moved to another database to reduce
fragmentation. When each archival table is provided its own hard drive spindel,
new data can be added to the database very efficiently since there is more efficient drive head movement.

Even though a small table which fits into a database page is more efficient without a
covering index, it may grow large enough to need one.

Partionining of filegroups to spread I/O among several physical hard drive spindles.

Secondary .ndf files

These may not be performed because the process of planning for growth can be time consuming.

AdventureWorks2008 does not use SOUNDEX index on name fields such as Person or City.

Estimating Future Growth

Currency exchange rate values change so quickly
that OLTP apps should obtain this information from a real-time web service
rather than retrieving them from a database table.
The settlement values are for consistency of a single value during off-line processing.

Because values in these look-up tables are rather static,
OLTP apps can leverage processing power on clients by
downloading these files to maintain domain intrgrity at each client.

The small size yet frequent use of these tables mean that they can be resident
in memory and thus not need indexing since the server can scan through them faster than looking up an index.

The slow growth of these tables mean that they can be created without the
fill space some tables use to more quickly accomodate for future growth.
Tables with no fill impose less memory overhead and perform faster.
When rows are occassionally added, the table can be rebuilt quickly due to their small size.

Not much growth (in rows) is expected within reference tables such as:

AddressTypeID in Person.AddressType

Person.StateProvince

Person.CountryRegion

Person.CountryRegionCurrency

PhoneNumberTypeID in Person.PhoneNumberType

ContactTypeID in Person.ContactType

CurrencyCode in Sales.Currency

Sales.CurrencyRate

Sales.SalesReason

Sales.SalesTaxRate

Purchasing.ShipMethod

Production.UnitMeasure

Production.ScrapReason

Production.Culture

HumanResources.Shift

Some tables have a direct correlation:

There should be always one ProductModelIllustration row for each combination of ProductModel and ProductIlluration
because that's why "Link" (associative) type tables exists -- to maintain many-to-many junction relationships.

Other such Link type tables include:

One Person.PersonCreditCard row per Person and CreditCard combination

One Sales.SpecialOfferProduct row per Product and Sales.SpecialOffer combination

One Production.ProductDocument row per Product and Document combination

One Production.ProductProductPhoto row per Product and ProductPhoto combination

One Production.TransactionHistoryArchive row for each Production.TransactionID

One Person.BusinessEntityAddress row for each Person.Address row

Some tables grow (or shrink) according to technical linkages:

There should be at least one Person.Password row per Person
as long as a password
is required by the application inserting Person rows.

This is also the case with PersonPhone and EmailAddress.
The AdventureWorks2008 DB by default has only one PersonPhone and EmailAddress per Person.
But nowdays most people have several phone numbers and email addresses.

When hiring occurs,
the number of Employee and Salesperson rows increase.

When wage increases (or decreases) occur,
the number of EmployeePayHistory rows per Employee increase more .

Whenever there is a reorganization,
the number of Department and EmployeeDepartmentHistory rows multiply .

When store openings are planned,
the number of Store rows increase.

Whenever there is a sales territory change,
the number of SalesTerritory rows may change.
But the number of SalesTerritoryHistory rows would multiply.

With each sales quota change,
the number of SalesPersonQuotaHistory rows will muliply.

When new products are added,
the number of Product and ProductModel rows increase.

Whenever there is a product re-categorization,
the number of ProductCategory and Production.ProductSubCategory rows per Product
would change.

As the organization expands internationally into more cultures (or withdraws from them)
the number of Production.ProductDescription
and Production.ProductModelProductDescriptionCulture rows per Product would change.

Management desire to achieve lower total product support costs
with more customer self-service would likely increase
the number of Production.Document and Production.Illustration rows per product .

Changes in floor layout
may impact the number of Production.Location rows.

Vendors are added or removed based on management decisions.

Some changes in master : detail row ratios over time reflect
choices among opposing management philosophies and strategies over time: