Skip to main content

AdventureWorks Database Schema: Introduction, Examples, Installation, SQL Practice, and More

· 7 min read
David Bui
Data Product Executive

This guide explores the AdventureWorks database schema, a comprehensive and detailed sample database created by Microsoft for SQL Server users.

AdventureWorks provides a practical learning environment for SQL, database management, and business analysis. Below, we'll break down the structure, key tables, relationships, and practical applications, making it an ideal resource for anyone interested in learning about SQL Server database systems.

1. Introduction to AdventureWorks Database​

What is AdventureWorks?​

AdventureWorks Database Schema

AdventureWorks is a free sample database provided by Microsoft, designed for SQL Server users. It mimics a fictional company, Adventure Works Cycles, and includes data on sales, production, and human resources. AdventureWorks is commonly used for training, demonstrations, and practicing SQL queries and database design principles.

  • Purpose: Helps users understand database design, normalization, and writing complex SQL queries.
  • Availability: Available for various SQL Server versions (e.g., 2012, 2014, 2016, 2019).
  • License: Distributed under an MIT license, which allows for free use in educational and testing contexts.

Why is AdventureWorks Useful?​

AdventureWorks serves as an excellent resource for:

  • Learning SQL: Its structure supports the practice of joins, aggregations, and subqueries.
  • Database Design: Provides a real-world example of normalization and data relationships.
  • Application Testing: Ideal for developing and testing SQL-based applications.
  • Business Analysis: Provides valuable insights into sales, inventory, and employee management.

2. Overview of the AdventureWorks Database Schema​

What is a Database Schema?​

A schema in a database is a collection of related tables and objects, organized by function. In AdventureWorks, schemas are used to categorize tables based on business areas, making the data easier to manage and query. The database includes multiple schemas, each representing different functional areas of the business.

AdventureWorks Schema Overview

Schemas in AdventureWorks​

AdventureWorks contains approximately 70 tables, organized into the following key schemas:

SchemaDescriptionExample Tables
dboMiscellaneous system tablesErrorLog, DatabaseLog
HumanResourcesEmployee and department managementEmployee, Department, JobCandidate
PersonPersonal and contact informationPerson, Address, BusinessEntity
ProductionProduct catalog and inventory managementProduct, ProductCategory, ProductModel
PurchasingProcurement and vendor managementVendor, PurchaseOrderHeader, PurchaseOrderDetail
SalesSales transactions and customer dataCustomer, SalesOrderHeader, SalesOrderDetail

Key Features​

  • Table Count: Around 70 tables, offering a diverse dataset for practical learning.
  • Relationships: The schema utilizes primary and foreign keys to link tables, creating realistic one-to-many and many-to-one relationships.
  • Design Principles: Uses normalization to ensure the database is logically structured and scalable.

3. Detailed Description of Key Tables and Relationships​

Key Tables​

The following tables are central to the AdventureWorks database. Each plays a role in simulating a real-world business environment, supporting various functions like sales, employee management, and product inventory.

Table NameDescriptionPrimary KeyForeign Keys
Person.PersonStores personal details (e.g., name, title)BusinessEntityIDNone (parent table)
HumanResources.EmployeeEmployee information (e.g., hire date)BusinessEntityIDFK to Person.Person (BusinessEntityID)
Sales.CustomerCustomer details (e.g., account number)CustomerIDFK to Person.Person (BusinessEntityID)
Sales.SalesOrderHeaderSales order headers (e.g., order date)SalesOrderIDFK to Sales.Customer (CustomerID)
Sales.SalesOrderDetailSales order line items (e.g., quantity)SalesOrderID, SalesOrderDetailIDFK to Sales.SalesOrderHeader (SalesOrderID)
Production.ProductProduct catalog (e.g., name, price)ProductIDNone (parent table)
Purchasing.VendorVendor details (e.g., vendor name)BusinessEntityIDFK to Person.Person (BusinessEntityID)

Relationships​

The relationships between tables help users understand how data flows within the database. Some important relationships include:

  • One-to-Many:
    • One Person can be associated with multiple Employees, Customers, or Vendors (Person.Person to HumanResources.Employee, Sales.Customer, Purchasing.Vendor).
    • One SalesOrderHeader can have multiple SalesOrderDetails (Sales.SalesOrderHeader to Sales.SalesOrderDetail).
  • Many-to-One:
    • Multiple Employees can belong to one Department (HumanResources.Employee to HumanResources.Department).
  • Inheritance Design:
    • The BusinessEntity table acts as a supertype, linking to Person, Employee, Customer, and Vendor tables, reducing redundancy.

4. Downloading and Installing AdventureWorks​

Step-by-Step Installation​

  1. Download the Database:
  2. Restore Using SSMS:
    • Open SQL Server Management Studio (SSMS), right-click "Databases" in Object Explorer, select "Restore Database," choose "Device," and browse to the downloaded .bak file.
  3. Verify Installation:
    • Confirm the AdventureWorks database appears under "Databases" in SSMS.

T-SQL Installation​

Alternatively, use T-SQL commands to restore the database:

RESTORE DATABASE AdventureWorks2019
FROM DISK = 'C:\Path\To\AdventureWorks2019.bak'
WITH MOVE 'AdventureWorks2019' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019.mdf',
MOVE 'AdventureWorks2019_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019_log.ldf'

Prerequisites​

  • SQL Server (2012 or later).
  • SQL Server Management Studio (SSMS) or Azure Data Studio.

5. Using AdventureWorks for SQL Practice and Learning​

Why Use AdventureWorks?​

AdventureWorks is perfect for learning SQL because:

  • Realistic Business Data: Simulates real-world business scenarios (sales, inventory, employee data).
  • Advanced SQL Techniques: Supports practicing joins, subqueries, aggregations, and more.
  • Widely Used Resource: Many resources, tutorials, and examples are available in the SQL community.

Example Queries​

Here are a few example SQL queries to help you get started:

  • Top 5 Products by Sales: 
    SELECT TOP 5 p.Name AS ProductName, SUM(sod.OrderQty * sod.UnitPrice) AS TotalSales
    FROM Production.Product p
    JOIN Sales.SalesOrderDetail sod ON p.ProductID = sod.ProductID
    JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
    GROUP BY p.Name
    ORDER BY TotalSales DESC;
  • Employee Department Details: 
    SELECT e.BusinessEntityID, p.FirstName, p.LastName, d.Name AS DepartmentName
    FROM HumanResources.Employee e
    JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID
    JOIN HumanResources.EmployeeDepartmentHistory edh ON e.BusinessEntityID = edh.BusinessEntityID
    JOIN HumanResources.Department d ON edh.DepartmentID = d.DepartmentID;

Learning Opportunities​

  • Joins and Relationships: Link tables across schemas.
  • Aggregations: Learn how to compute totals, averages, and counts.
  • Business Insights: Analyze sales trends, employee data, and product inventories.

6. Additional Resources​

Data Dictionary​

  • A complete data dictionary is available at AdventureWorks Data Dictionary. This dictionary outlines the purpose and structure of all tables in the database.

ER Diagram​

SQL Playground​

  • Practice SQL queries directly in your browser using RunSQL.com, a free SQL playground that includes the AdventureWorks database.

Official Documentation​

Community Resources​

7. FAQs​

What is AdventureWorks used for?​

AdventureWorks is used to practice SQL queries, learn about database design, and simulate business operations through real-world data.

Can I download AdventureWorks for free?​

Yes, AdventureWorks is available for free from Microsoft's GitHub repository.

What SQL Server versions are compatible with AdventureWorks?​

AdventureWorks is compatible with SQL Server 2012 and later versions.

How do I install AdventureWorks on SQL Server?​

You can install AdventureWorks by downloading the .bak file and restoring it through SQL Server Management Studio (SSMS).

How complex is the AdventureWorks schema?​

The schema is complex, with around 70 tables spread across various business functions like Sales, HR, and Production, making it ideal for practicing advanced SQL queries.

Where can I find additional resources to learn more about AdventureWorks?​

Additional resources, including data dictionaries, ER diagrams, and official documentation, are available in the sections above.

On this page