jpwhite3/northwind-sqlite3

SQLite3 version of Microsoft's Northwind Database

Northwind-SQLite3

This is a version of the Microsoft Access 2000 Northwind sample database, re-engineered for SQLite3.

The Northwind sample database was provided with Microsoft Access as a tutorial schema for managing small business customers, orders, inventory, purchasing, suppliers, shipping, and employees. Northwind is an excellent tutorial schema for a small-business ERP, with customers, orders, inventory, purchasing, suppliers, shipping, employees, and single-entry accounting.

All the TABLES and VIEWS from the MSSQL-2000 version have been converted to Sqlite3 and included here. Included is a single version prepopulated with data. Should you decide to, you can use the included python script to pump the database full of more data.

Download here

Structure

erDiagram
    CustomerCustomerDemo }o--|| CustomerDemographics : have
    CustomerCustomerDemo }o--|| Customers : through
    Employees ||--|| Employees : "reports to"
    Employees ||--o{ EmployeeTerritories : through
    Orders }o--|| Shippers : "ships via"
    "Order Details" }o--|| Orders : have
    "Order Details" }o--|| Products : contain
    Products }o--|| Categories : in
    Products }o--|| Suppliers : "supplied by"
    Territories ||--|| Regions : in
    EmployeeTerritories }o--|| Territories : have
    Orders }o--|| Customers : place
    Orders }o--|| Employees : "sold by"


    Categories {
        int CategoryID PK
        string CategoryName
        string Description
        blob Picture
    }
    CustomerCustomerDemo {
        string CustomerID PK, FK
        string CustomerTypeID PK, FK
    }
    CustomerDemographics {
        string CustomerTypeID PK
        string CustomerDesc
    }
    Customers {
        string CustomerID PK
        string CompanyName
        string ContactName
        string ContactTitle
        string Address
        string City
        string Region
        string PostalCode
        string Country
        string Phone
        string Fax
    }
    Employees {
        int EmployeeID PK
        string LastName
        string FirstName
        string Title
        string TitleOfCourtesy
        date BirthDate
        date HireDate
        string Address
        string City
        string Region
        string PostalCode
        string Country
        string HomePhone
        string Extension
        blob Photo
        string Notes
        int ReportsTo FK
        string PhotoPath
    }
    EmployeeTerritories {
        int EmployeeID PK, FK
        int TerritoryID PK, FK
    }
    "Order Details" {
        int OrderID PK, FK
        int ProductID PK, FK
        float UnitPrice
        int Quantity
        real Discount
    }
    Orders {
        int OrderID PK
        string CustomerID FK
        int EmployeeID FK
        datetime OrderDate
        datetime RequiredDate
        datetime ShippedDate
        int ShipVia FK
        numeric Freight
        string ShipName
        string ShipAddress
        string ShipCity
        string ShipRegion
        string ShipPostalCode
        string ShipCountry
    }
    Products {
        int ProductID PK
        string ProductName
        int SupplierID FK
        int CategoryID FK
        int QuantityPerUnit
        float UnitPrice
        int UnitsInStock
        int UnitsOnOrder
        int ReorderLevel
        string Discontinued
    }
    Regions {
        int RegionID PK
        string RegionDescription
    }
    Shippers {
        int ShipperID PK
        string CompanyName
        string Phone
    }
    Suppliers {
        int SupplierID PK
        string CompanyName
        string ContactName
        string ContactTitle
        string Address
        string City
        string Region
        string PostalCode
        string Country
        string Phone
        string Fax
        string HomePage
    }
    Territories {
        string TerritoryID PK
        string TerritoryDescription
        int RegionID FK
    }

Views

The following views have been converted from the original Northwind Access database. Please refer to the src/create.sql file to view the code behind each of these views.

View Name
[Alphabetical list of products]
[Current Product List]
[Customer and Suppliers by City]
[Invoices]
[Orders Qry]
[Order Subtotals]
[Order Subtotals]
[Product Sales for 1997]
[Products Above Average Price]
[Products by Category]
[Quarterly Orders]
[Sales Totals by Amount]
[Summary of Sales by Quarter]
[Summary of Sales by Year]
[Category Sales for 1997]
[Order Details Extended]
[Sales by Category]

Build Instructions

Prerequisites

• You are running in a unix-like environment (Linux, MacOS)
• Python 3.6 or higher (python3 --version)
• SQLite3 installed sqlite3 -help

Build

make build  # Creates database at ./dist/northwind.db

Populate with more data

make populate

Print report of row counts

make report

northwind-sqlite3's Issues

ERD Mismatches

Hi, I noticed the ERD labels the primary keys as CustomerId, ProductId, etc. But the actual database uses Id for all the primary keys.

It would be helpful if the ERD matched the database. So beginners and students don't get confused!

Incorrect CategoryID foreign key on Product

northwind-SQLite3/Northwind.Sqlite3.create.sql

Lines 5622 to 5623 in 46d5f8a

	FOREIGN KEY ([ProductID]) REFERENCES [Categories] ([CategoryID])
	ON DELETE NO ACTION ON UPDATE NO ACTION,

I'm pretty sure this should be FOREIGN KEY ([CategoryID]) REFERENCES [Categories] ([CategoryID])

Shouldn't EmployeeID references to ReportsTo?

EmployeeID referring to EmployeeID seems weird. I tried it works, but shouldn't the foreign key be set to ReportsTo column?

northwind-SQLite3/Northwind.Sqlite3.create.sql

Line 279 in 591cd32

FOREIGN KEY ([EmployeeID]) REFERENCES [Employees] ([EmployeeID])

I checked the Microsoft SQL samples for this table and they also have it referred to ReportsTo. Have a look at microsoft/sql-server-samples.

Correct foreignkeyconstraint should be:

FOREIGN KEY ([ReportsTo]) REFERENCES [Employees] ([EmployeeID]) 
		ON DELETE NO ACTION ON UPDATE NO ACTION

Providing a copy of the larger dataset in form of a release

How would you feel about providing the result of running populate.py on the database as a release?

This could simplify some use cases where the database is required, but preferred in the extended version. At the moment, one would have to grab both the smaller database and the population script from the repository and extend the database in place, which isn't ideal to say the least.

Additionally, the population script hardcodes a relative path to the database file, requiring users to rebuild your specified directory structure for the script to even work. In most cases, people have a different destination in mind, adding yet another extra step to the process

Regions and Territories have a large amount of trailing whitespace in them.

Most copies of the Northwind database seem to have this problem - excessive trailing whitespace. I'm not sure if it's intentional?

Error in ProductDetails_V view

Running this query:

sqlite> SELECT * FROM ProductDetails_V;

Generates this error
Parse error: no such column: c.id

This happens because the Categories table doesn't have an id column, but rather a CategoryID column:

CREATE TABLE [Categories]
( [CategoryID] INTEGER PRIMARY KEY AUTOINCREMENT,
[CategoryName] TEXT,
[Description] TEXT,
[Picture] BLOB
);

The view `ProductDetails_V` is incorrectly defined

The view ProductDetails_V is using a c.id and s.id that should be c.CategoryId and s.SupplierID instead.

CREATE VIEW [ProductDetails_V] as
select 
p.*, 
c.CategoryName, c.Description as [CategoryDescription],
s.CompanyName as [SupplierName], s.Region as [SupplierRegion]
from [Products] p
join [Categories] c on p.CategoryId = c.id  <-- ISSUE
join [Suppliers] s on s.id = p.SupplierId      <-- ISSUE

Countries with ISO codes

Is it beneficial and match this project's goals to add more quality to the data? Looking up countries by their name is hasslesome. For better working with the data I'd like to see some alpha3 codes based on ISO 3166-1.

Not an issu just a query

How did you create sqlite file from sql?
Also I don't wanted to make an issue but couldn't find you email anywhere.
@jpwhite3

Use tags please

When you expect others relying on this data you should use tags. This makes it easy for the users to still use the old table structure before your recent restructures.

Create script doesn't respect dependency order

The script cannot be run with a single command because the order of the Create statements doen's respect the FKs dependency.

Also, the drop statements should be ordered reversly to comply with the FXs dependency.

The update script has misspells

In the original scripts the table names are ini plural (e.g. Costumers), in the update scripts they are in the singular form (e.g. Costumer).