Example Databases from Relational DBMS

Most database products ship with some test sample databases you can use for practice. We’ll summaries here the most significant and free example databases from well-known relational database products. Each will be introduced with one or more model diagrams, and a list of tables, with total number of records and the estimated occupied size. Check also the other list of free open-source test databases.

Sakila MySQL Example Database

Main sample database in MySQL or MariaDB is sakila. This implements the back-end database of a fictional medium-sized store selling movie DVDs. The dvdrental example database from PostgreSQL is actually sakila. Another port of sakila in PostgreSQL is Pagila.

While MySQL Workbench can create some nice-looking database diagrams, here is one with Model Xtractor. All our diagrams will use Crow’s Foot‘s notation here (default is a Model Xtractor simplified notation, with ERwin’s IDEF1X as third option).

We separated the tables into three different areas: Customer Data, Inventory and Billing. A fourth area lists the database views, in collapsed form.

Most data is concentrated in the payment and rental tables. They both record transactions. The inventory section holds also thousands of films, with their list of actors. You can group and filter customers per country or city.

To count or re-count the number of rows, and estimate the total stored size, click on the Tables node, select all table items from the Objects list, and call Update Row Count. When data changes, the row count and the estimate size are not automatically updated, so you’ll have to do it manually again, if needed. Be aware that large remote databases, with many tables and large numbers of records, may take a while for this update.

HR Oracle Example Database

HR schema is the main example database in the Oracle distribution. To use it, you’ll have to enable the HR user/schema first. Unlike the previous image, the next Model Xtractor diagram shows all HR tables with full column descriptions, including their specific physical data type. An asterisk means data is optional in that field.

HR implements the small database of a Human Resources department. The central Departments–Employees relationship reminds us of the old scott/tiger schema (scott schema/user name, with tiger password), which inspired our own Employees sample.

The Jobs–JobHistory branch allows you to drill-down into the log of an employee based on his/her work history in time, and the occupied positions. The Regions–Countries–Locations chain allows you to group or filter by the geographic location of his/her department.

EmpDetailsView is a database view, that uses denormalized data for the employees. Fields like the region, country and city names are brought from the related tables, walking through the relationships.

The database is very small in size, as the focus is rather on walking through the defined relationships. We have just over some 100 employees in the middle, with small number of records for all other tables.

AdventureWorks Microsoft SQL Server Samples

After Northwind, first most elaborate example database for SQL Server was FoodMart, followed by AdventureWorks. There are different files for SQL Server versions, or for OLTP vs DSS (Data Warehouse) databases. Next Model Xtractor diagrams are for an AdventureWorks2012 OLTP database installed on a Microsoft SQL Server 2017 engine.

The database is a catalog with tables in six different schemas: HumanResources, Purchasing, Sales, Person, Production, and dbo. Each table name is actually prefixed by its schema name. There are over 70 tables in total, plus 20 views. Such a large number of tables is very common in real-life databases (some large databases may have in fact hundreds of tables), and this makes it suitable for tests on enterprise databases from the corporate world.

First model diagram here presents all tables from the Production schema, with their relationships. When you have a large number of tables, it may be good to display them per schema, and collapsed. We used the All Teal theme as well, with green gradients.

The central table here is Product, which is basically divided into different parts in other tables like ProductModel, ProductReview, and ProductPhoto.

We have over 500 product entries. That’s not a large number, but related log tables like TransactionHistory or WorkOrder may allow querying more data.

The Sales schema stores order transactions. Just like for Production, here is a model with all table shapes expanded, to better understand the relationships.

For the Person schema we will present a different look, going back to the initial Cassandra Navy theme. The table shapes are expanded, and we can see both their internal structure, and the relationships.

WideWorldImporters Microsoft SQL Server Samples

After AdventureWorks, WideWorldImporters is the next most complex example database used for SQL Server 2016+. Microsoft shared its scripts and database files on GitHub, for SQL Server and Azure. While WideWorldImporters is used for OLTP,  WideWorldImportersDW is an OLAP database focused on analytics.

WideWorldImporters is a wholesale company, with transactions and real-time analytics. The 58 total tables are stored in the Application, Purchasing, Sales and Warehouse schemas, while Website schema contains three database views.

We’ll focus here below only on the Warehouse schema, with a simple model diagram with all collapsed table shapes, to highlight the relationships.

While most tables have an acceptable number of records, you can refer to the ColdRoomTemperature_Archive table and its 3.6 million rows as a very large container.

Northwind Microsoft Access Database Sample

For many years, the Northwind database used to promote Microsoft Access, and was one of the most recognizable example databases on the market. It has been gradually ported to SQL Server and SQL Server CE, and then to other non-Microsoft databases. Northwind is rather obsolete today, and it is recommended to use rather AdventureWorks, at least for SQL Server. However, you may still need it, and you can download it from different sources on the net. The diagram below is from an old MDB Microsoft Access database.

Northwind is the name of a fictional company, whose Employees process Customer Orders for its Products, delivered through specific Shippers. Products can be grouped by Categories, and are provided by Suppliers.

You may be surprised to not find the Order Details table in this view, but Model Xtractor can show conceptual many-to-many relationships, and Order Details is simply bypassed (and hidden) by the unusual direct link between Orders and Products. “Collapsed” (i.e. hidden) Xtractor relationships are represented by special relationship items. In our case, you can look inside both the Orders and Products shapes for the Order Details items. As bidirectional, each hidden relationship is always represented by a pair of such collapsed items. To “expand” and show this relationship as a link, drag any of these two items outside the shape, and drop it over an empty space on diagram’s surface. Both items will “expand” now into visible one-to-many links from Orders and Products, with Order Details in the middle.

The central tables are Orders and Order Details, with last table the largest in number of rows, but not in estimated size (Orders table contains more information).

Microsoft Access is great at reporting, as you can easily design Reports, from scratch or built-in templates. A number of predefined views are used for them, with read-only results.

Employee Firebird Example Database

One of the main example databases of Firebird reminds us of Employees and Oracle’s HR. There are however important differences in the database design, and it may be worth comparing the similar but slightly different models.

Employee is very small in size and number of table rows. You barely get enough data for some small tests.

DB2 Sample Database

IBM DB2 comes with this example database called either db2sampl or sample. When not already installed, it must be created. The database has 22 tables and 20 views.

You can recognize in the center the Department–Employee–Project relationships from a typical Employee database. However, employee data is here extended with binary photos and resumes. Tables with no relationships are displayed collapsed.

For now, we cannot estimate the occupied size per table in DB2. There is one table with 10K rows, but the rest are sparse.

SQL Anywhere Demo Database

The demo database coming with SAP/Sybase SQL Anywhere is also focused on order processing, with Employees selling stuff to Customers, and having these transactions recorded in our database.

You may wonder why this is such a recurring theme and if these database vendors may lack in imagination. However, many if not most customers, consumers of OLTP relational databases, may use this kind of databases the most. It’s also interesting to compare the differences between these models. They all store employee and customer data, transactions for the order processing, but each does it with subtle differences in the database design.

That’s also a small database, with barely over 1,000 order details in the largest table, SalesOrderItems.

ASPNETDB Membership Schema

This Microsoft SQL Server utility database has been used for many years in ASP.NET web applications. You never create it manually or with a script, but by calling a specific tool from the command line.

All table names are prefixed by aspnet_, to avoid eventual name conflicts when hosting within the same database with your business tables. There have been also variations to this model over the years: some implementations are slightly different, with additional tables and fields.

The aspnet_Users table stores application user details. Most of the other tables basically define application access rights for each user. As a web application, different users may log in and perform different operations. Some may be able to change data, or access certain views. Others may get access denied to some operations or data visualization.

The Membership Schema comes also with predefined views, as listed below. The number of rows is variable and irrelevant here. The database is usually created with just the current user, with the admin role and access to everything. This is basically you, the web developer. You may start to define now different access rights, and add new users to your system, with different assigned privileges.

Pubs Example Databases

Before Northwind, it was the pubs database in the Microsoft world, a small Book Publishing example database. Pubs used to promote the early versions of Microsoft Access. It has been later ported to SQL Server (here is a link from Microsoft, to download pubs and Northwind for SQL Server 2000) and other external RDBMS, but it is considered rather obsolete today. However, its data model is still sound and clear, and can be used for interesting tests, or SQL practice.

The stores table holds multiple store locations, each offering different discounts, with own sales billing records. The inventory area includes the catalog of book titles, written by different authors and published by different houses. Each publisher has also records with the employees and jobs they occupy.

The rowsched table, used for the paid royalties per title, holds most records. But by all means that’s a very small database in size, with just a few records per table, to be used for tests.

ClassicModels MySQL Example Database

Besides sakila, MySQL offers also some other small example databases. ClassicModels is one of them, and it reminds us the Northwind database for Microsoft Access, previously presented. Unlike Northwind, this ClassicModel comes with no enforced referential integrity (i.e. no foreign keys defined).

This diagram will show all tables expanded, with their detailed structure: columns with physical data types. We also separated the columns in categories, with Keys (primary keys) first.

Very small database as well, with just a bunch of rows per table, for tests.