AdventureWorks 2008 Details
The following changes have been made in the design of AdventureWorks2008:
- The way people and businesses are represented has changed. Some tables have been added, renamed, and deleted. These changes enable better integration with the Microsoft ADO.NET Entity Framework.
- New data types and attributes are now demonstrated in the database including:
- date and time
- hierarchyid
- geography
- filestream
- Integrated full-text support has been incorporated into the database.
The schema introduces the idea of a business entity. A business entity is a person or business which has zero or more addresses and zero or more people related to it. For example, the people related to a store would typically be purchasing agents of a retail sporting or bicycle store who buy bicycles wholesale from AdventureWorks Cycles. A person related to an employee might be the employee’s emergency contact. All business entities have a common key (the
BusinessEntityID). This allows us to build a conceptual model using the Entity Framework which demonstrates table per type inheritance. It also simplifies the schema by having a single table (
Person.BusinessEntityAddress) relate addresses to business entities instead of using specialized tables (for example
CustomerAddress,
VendorAddress,
EmployeeAddress.
The concept of a customer has also been broadened. In AdventureWorks for SQL Server 2005, a customer is either a store (wholesale) or an individual (retail). In AdventureWorks2008, a customer can be any person or a store. This enables employees (for example) to be customers without storing redundant information about them.
The
hierarchyid data type is used to organize employees and their managers into a tree, and organize production documents into a tree (much like a file system).
The
date and
time data types are used instead of the datetime data type when appropriate (date for columns like
HireDate in the
HumanResources.Employee table, and time for columns like
StartTime in
HumanResources.Shift table).
The integrated full-text feature of SQL Server 2008 is used to demonstrate full-text querying over resumes stored in the
HumanResources.JobCandidate table.
Fictitious addresses stored in the
Person.Address table have been fictionally geocoded and the result stored in the
SpatialLocation column to demonstrate the new spatial features in SQL Server 2008.
For specific information about these changes an entity relational diagram showing the conceptual model of business entities, a relational schema diagram, a Transact-SQL script and the CSV data files necessary to build the database are included in the AdventureWorks2008Scripts.msi installer located in
The Sample Databases at CodePlex project.
AdventureWorks 2008 Installation
To install AdventureWorks2008 perform the following steps:
1. Download and invoke the appropriate installer from The Sample Databases at CodePlex. The appropriate installer will have “2008” in the name and the name will match the machine architecture of the machine on which you will be installing the samples. For example
AdventureWorks2008.msi will work on x86 machines, and
AdventureWorks2008_x64.msi will work on x64 machines.
2. Follow the directions on the installer screens.
3. A backup of the database and any schema diagrams will be installed by default into the
systemdrive:\Program Files\Microsoft SQL Server\100\Tools\Samples where systemdrive represents the disk drive letter of the disk where you installed windows.
4. If you have not already enabled the file stream feature, execute the following statement in SQL Server Management Studio (SSMS):
EXEC spfilestreamconfigure @enable_level = 3;5. Use to restore the database or use a Transact-SQL command similar to the following in SQLCMD or SSMS (where
systemdrive is replaced with the appropriate drive letter):
RESTORE DATABASE AdventureWorks2008FROM DISK = 'systemdrive:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks2008.bak';