lerocha/chinook-database

Sample database for SQL Server, Oracle, MySQL, PostgreSQL, SQLite, DB2

Chinook Database

Chinook is a sample database available for SQL Server, Oracle, MySQL, etc. It can be created by running a single SQL script. Chinook database is an alternative to the Northwind database, being ideal for demos and testing ORM tools targeting single and multiple database servers.

Supported Database Servers

• DB2
• MySQL
• Oracle
• PostgreSQL
• SQL Server
• SQL Server Compact
• SQLite

Download

Download the SQL scripts from the latest release assets. One or more SQL script files are provided for each database vendor supported. You can run these SQL scripts with your preferred database tool.

Data Model

The Chinook data model represents a digital media store, including tables for artists, albums, media tracks, invoices and customers.

Sample Data

Media related data was created using real data from an iTunes Library. It is possible for you to use your own iTunes Library to generate the SQL scripts, see instructions below. Customer and employee information was manually created using fictitious names, addresses that can be located on Google maps, and other well formatted data (phone, fax, email, etc.). Sales information is auto generated using random data for a four year period.

Why the name Chinook?

The name of this sample database was based on the Northwind database. Chinooks are winds in the interior West of North America, where the Canadian Prairies and Great Plains meet various mountain ranges. Chinooks are most prevalent over southern Alberta in Canada. Chinook is a good name choice for a database that intents to be an alternative to Northwind.

Development

System Requirements:

• Visual Studio 2022 Community Edition (make sure to select Visual Studio extension development with the Text Template Transformation option during installation)
• .NET 8

Notes:

• The SQL scripts are auto-generated using Text Template Transformation.
• The ChinookDataSet.xsd file contains the schema definition, ChinookData.json contains the data, and the *.tt files are the text templates that are used to generate all SQL scripts.
• You can build the solution using any IDE (Visual Studio, Rider) or using dotnet build in any OS since I just migrated it to .NET 8, but to auto-generate the SQL scripts we still need Visual Studio. I will update here once I find a good way to auto-generate the scripts by using tools like dotnet-t4.

chinook-database's Issues

Visual Studio 2017 Problem

Hi. I opened the solution with Visual Studio 2017 and try to build the solution and I get error on this
using Microsoft.Data.Entity.Design.DatabaseGeneration Not found
I installed EF 6.0 and I have the same problem.
Thank you

Unacceptable song name for publicly available database

I'm unsure if it is any of the other versions, but the track with id 728 in the SQLite script should be removed from this database.

https://raw.githubusercontent.com/lerocha/chinook-database/master/ChinookDatabase/DataSources/Chinook_Sqlite.sql

Database diagrams

I'm trying to create a database diagram but only few of tables work fine then I get error Wrong Parameter without any further information.

latest source code link not working and detailed schema

This link is not working: http://www.codeplex.com/ChinookDatabase/SourceControl/ListDownloadableCommits.aspx

Also, I want to know if there is a detailed schema for the database, like data type for each field in each table and what it represents.

Thanks

Repeated playlists

Hi,

In the playlist table, there are repeating playlists like 'Music' (ID 1 and 8), 'Movies' (ID 2 and 7), Audiobooks (ID 4 and 6) and TV shows (ID 3 and 10). I feel like this shouldn't be the case (unless I'm missing something).

Don't use double quotations in PostreSQL

Hi, I notices that the downloadable PostreSQL script has all of the identifiers between double quotations (e.g. "Track"). Is there a reason for that? In PostgreSQL, if you put identifiers between quotes, it makes the case-sensitive which is very inconvenient for ad-hoc queries (or people who want to write their queries in a different style)

If you want, I can send you a PR

Chinook_PostgreSql.sql:237: ERROR: invalid byte sequence for encoding "UTF8": 0xf4 0x6e 0x69 0x6f

morenoh149/postgresDBSamples#1 (comment)

Oracle installation via Chinook_Oracle.sql in detail steps as its given for Sqlite

i am new to python and struggling to find installation steps for Oracle db and fetching data from it.
its well elaborative for sqlite and i have tried these from this link https://python.langchain.com/docs/use_cases/sql/agents

can you please provide similar documentation for oracle. or share some link please?

Tranform All Templates in Visual Studio Code

Forgive me for asking what may be a simple question. I am new to Visual Studio but I need this database for a course I'm taking.

In the instructions for creating the scripts in Visual Studio the following instruction is given...

On the Solution Explorer, click on your Solution and then click on the Transform All Templates button at the Solution Explorer buttons bar.

However, I am not seeing this in my Visual Studio Code which I recently installed. I'm also not seeing this option in any of my googling attempts to solve this issue.

How do I get these scripts built in VS Code if I can't run the solution?

redmine migration

Thanks for your permanent cooperation
if i can ask u question

How can I transfer fields from Oracle to MySQL?
In my work environment, I own two systems, the Oracle 12C system and the redmine system, and this requires users to enter information twice. I want to find a suitable solution to transfer information from the Oracle system to the Redmine system, and to take some fields and transfer them to the Redmine system through the similarity of the issue_id field

Script creates database but never exits

When I run wget -qO - https://raw.githubusercontent.com/cristiscu/employees-test-database/master/scripts/Employees%20-%20MySQL.sql | mysql a database is succesfully created.

But when I run your script wget -qO - https://raw.githubusercontent.com/lerocha/chinook-database/master/ChinookDatabase/DataSources/Chinook_MySql.sql | mysql the command never exits and I have to ctrl c manually. But the database looks like it has been succesfully created.

Here's my full process if you want to test:

sudo -s; 
snap install lxd;
lxd init; # use defaults
lxc launch ubuntu:22.04 vm;
lxc exec vm --  bash;
sudo apt install mariadb-server mariadb-client;
wget -qO - https://raw.githubusercontent.com/cristiscu/employees-test-database/master/scripts/Employees%20-%20MySQL.sql | mysql;
wget -qO - https://raw.githubusercontent.com/lerocha/chinook-database/master/ChinookDatabase/DataSources/Chinook_MySql.sql | mysql;

Visual Studio 2019 Community Edition build fail

At step "On the Solution Explorer, click on your Solution and then click on the Transform All Templates button at the Solution Explorer buttons bar.", getting multiple errors including:

• The type 'StoreItemCollection' is defined in an assembly that is not referenced. You must add a reference to assembly 'EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.

  • I checked the EF reference and it's 5.0.0, so I installed that - same error. I installed 6.0.0 and updated the package reference - same error.

• CS1061 'EdmProperty' does not contain a definition for 'ToStoreType' and no accessible extension method 'ToStoreType' accepting a first argument of type 'EdmProperty' could be found (are you missing a using directive or an assembly reference?)

Oracle errors in Express Edition 21

https://github.com/lerocha/chinook-database/blob/master/ChinookDatabase/DataSources/Chinook_Oracle.sql

SQL Error [900] [42000]: ORA-00900: invalid SQL statement

  Error : 900, Position : 0, Sql = conn chinook/p4ssw0rd


/*******************************************************************************
   Create Tables
********************************************************************************/
CREATE TABLE Album
(
    AlbumId NUMBER NOT NULL,
    Title VARCHAR2(160) NOT NULL,
    ArtistId NUMBER NOT NULL,
    CONSTRAINT PK_Album PRIMARY KEY  (AlbumId)
), OriginalSql = conn chinook/p4ssw0rd


/*******************************************************************************
   Create Tables
********************************************************************************/
CREATE TABLE Album
(
    AlbumId NUMBER NOT NULL,
    Title VARCHAR2(160) NOT NULL,
    ArtistId NUMBER NOT NULL,
    CONSTRAINT PK_Album PRIMARY KEY  (AlbumId)
), Error Msg = ORA-00900: invalid SQL statement

  ORA-00900: invalid SQL statement

Any chance to sponsor this repo?

Hi,
I'm the founder of AskYourDatabase.
We wanna sponsor this repo and it's related youtube video.
Let me know if you are interested.
Contact me through askyourdatabase@gmail
Best

Move build artifacts from repository to "Releases"

The documentation currently points the downloads to the DataSources repository folder.

While a human being browsing the web can get the files, this won't allow an automation script to do the same: the URL provided by GitHub is a blob: url used to avoid hotlinks.

I wish to use the SQLite version in some automated tests. Chinook would be perfect for this! But my scripts cannot download it because of the above problem.

The Git repository would also be significantly reduced (after some history rewriting), following the good practice of not committing build artifacts to the repository.

If interested, I can work on PRs for this.

Json file?

I tried to export the database using sqliteStudio. However, it was really bad the result.
Does someone have a well formated json file of this database?

using Chinook DB on mac

Hi!

I would like to use this DB on my mac, but I see that the scripts are all .bat so, as far as I understand, all Windows-based. How can I start using it on my mac (or *nix system in general)? Has anyone already written some appropriate scripts?

edit: I am using mysql 5.7

Thanks!
Francesco