jpwhite3/northwind-sqlite3

SQLite3 version of Microsoft's Northwind Database

northwind-sqlite3's Issues

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?

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!

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

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

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).

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.

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
);

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.

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