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
- 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
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.
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).
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
The integrated full-text feature of SQL Server 2008 is used to demonstrate full-text querying over resumes stored in the HumanResources.JobCandidate
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