SQL-Northwind-Lesson-Overview.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "4dccbac4-2437-4266-88c8-07213210d7b4",
   "metadata": {},
   "source": [
    "# Northwind Database Overview\n",
    "\n",
    "Let's dive into the Northwind database—a classic, professionally curated dataset that simulates a small business's operations. We'll begin with **SQLite Browser**, a graphical user interface tool that allows you to interact with SQLite databases outside of Jupyter Notebooks.\n",
    "\n",
    "* * * \n",
    "\n",
    "<div class=\"alert alert-success\">  \n",
    "\n",
    "## Learning Objectives\n",
    "\n",
    "- Navigate and understand the structure of a relational database.\n",
    "- Become familiar with relational database concepts.\n",
    "</div>\n",
    "\n",
    "### Sections\n",
    "1. [Exploring the Database Schema](#ex1)\n",
    "2. [Diagram of Northwind Database Schema](#ex2)\n",
    "3. [Observations About the Schema](#ex3)\n",
    "4. [Primary Key (PK) vs. Foreign Key (FK)](#ex3)\n",
    "5. [Additional Resources](#resources)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9dc7b0ef-1667-4142-b31a-d85e9d3fca72",
   "metadata": {},
   "source": [
    "# About the SQLite version of the Northwind Database\n",
    "\n",
    "This workshop uses [the SQLite3 version of Microsoft's Northwind Database](https://github.com/jpwhite3/northwind-SQLite3/):\n",
    "\n",
    "> This is a version of the Microsoft Access 2000 Northwind sample database, re-engineered for SQLite3.\n",
    "> \n",
    "> 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.\n",
    ">\n",
    "> All the TABLES and VIEWS from the MSSQL-2000 version have been converted to Sqlite3 "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04544cd1-36c8-4dad-ba91-26e4fc7b1c2f",
   "metadata": {},
   "source": [
    "<a id='ex1'></a>\n",
    "## Exploring the Database Schema\n",
    "\n",
    "**Objective:** Familiarize yourself with the structure of the Northwind database.\n",
    "\n",
    "**Tasks:**\n",
    "  - Click on the **\"Database Structure\"** tab in SQLite Browser.\n",
    "  - Browse through the list of tables (e.g., `Customers`, `Orders`, `Products`, `Employees`, `Suppliers`).\n",
    "  - For each table, view the schema to understand the columns and data types.\n",
    "\n",
    "#### 🔔 Questions\n",
    "  - How many tables are in the database?\n",
    "  - What are the primary keys of the `Customers` and `Orders` tables?\n",
    "  - Identify foreign key relationships between tables.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77ec5fb5-465f-41a8-8886-26b67667ec79",
   "metadata": {},
   "source": [
    "<a id='ex2'></a>\n",
    "## Diagram of Northwind Database Schema"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4acece50-c3b6-4660-9361-7311c65ad3b0",
   "metadata": {},
   "source": [
    "```mermaid\n",
    "erDiagram\n",
    "    CustomerCustomerDemo }o--|| CustomerDemographics : have\n",
    "    CustomerCustomerDemo }o--|| Customers : through\n",
    "    Employees ||--|| Employees : \"reports to\"\n",
    "    Employees ||--o{ EmployeeTerritories : through\n",
    "    Orders }o--|| Shippers : \"ships via\"\n",
    "    \"Order Details\" }o--|| Orders : have\n",
    "    \"Order Details\" }o--|| Products : contain\n",
    "    Products }o--|| Categories : in\n",
    "    Products }o--|| Suppliers : \"supplied by\"\n",
    "    Territories ||--|| Regions : in\n",
    "    EmployeeTerritories }o--|| Territories : have\n",
    "    Orders }o--|| Customers : place\n",
    "    Orders }o--|| Employees : \"sold by\"\n",
    "\n",
    "\n",
    "    Categories {\n",
    "        int CategoryID PK\n",
    "        string CategoryName\n",
    "        string Description\n",
    "        blob Picture\n",
    "    }\n",
    "    CustomerCustomerDemo {\n",
    "        string CustomerID PK, FK\n",
    "        string CustomerTypeID PK, FK\n",
    "    }\n",
    "    CustomerDemographics {\n",
    "        string CustomerTypeID PK\n",
    "        string CustomerDesc\n",
    "    }\n",
    "    Customers {\n",
    "        string CustomerID PK\n",
    "        string CompanyName\n",
    "        string ContactName\n",
    "        string ContactTitle\n",
    "        string Address\n",
    "        string City\n",
    "        string Region\n",
    "        string PostalCode\n",
    "        string Country\n",
    "        string Phone\n",
    "        string Fax\n",
    "    }\n",
    "    Employees {\n",
    "        int EmployeeID PK\n",
    "        string LastName\n",
    "        string FirstName\n",
    "        string Title\n",
    "        string TitleOfCourtesy\n",
    "        date BirthDate\n",
    "        date HireDate\n",
    "        string Address\n",
    "        string City\n",
    "        string Region\n",
    "        string PostalCode\n",
    "        string Country\n",
    "        string HomePhone\n",
    "        string Extension\n",
    "        blob Photo\n",
    "        string Notes\n",
    "        int ReportsTo FK\n",
    "        string PhotoPath\n",
    "    }\n",
    "    EmployeeTerritories {\n",
    "        int EmployeeID PK, FK\n",
    "        int TerritoryID PK, FK\n",
    "    }\n",
    "    \"Order Details\" {\n",
    "        int OrderID PK, FK\n",
    "        int ProductID PK, FK\n",
    "        float UnitPrice\n",
    "        int Quantity\n",
    "        real Discount\n",
    "    }\n",
    "    Orders {\n",
    "        int OrderID PK\n",
    "        string CustomerID FK\n",
    "        int EmployeeID FK\n",
    "        datetime OrderDate\n",
    "        datetime RequiredDate\n",
    "        datetime ShippedDate\n",
    "        int ShipVia FK\n",
    "        numeric Freight\n",
    "        string ShipName\n",
    "        string ShipAddress\n",
    "        string ShipCity\n",
    "        string ShipRegion\n",
    "        string ShipPostalCode\n",
    "        string ShipCountry\n",
    "    }\n",
    "    Products {\n",
    "        int ProductID PK\n",
    "        string ProductName\n",
    "        int SupplierID FK\n",
    "        int CategoryID FK\n",
    "        int QuantityPerUnit\n",
    "        float UnitPrice\n",
    "        int UnitsInStock\n",
    "        int UnitsOnOrder\n",
    "        int ReorderLevel\n",
    "        string Discontinued\n",
    "    }\n",
    "    Regions {\n",
    "        int RegionID PK\n",
    "        string RegionDescription\n",
    "    }\n",
    "    Shippers {\n",
    "        int ShipperID PK\n",
    "        string CompanyName\n",
    "        string Phone\n",
    "    }\n",
    "    Suppliers {\n",
    "        int SupplierID PK\n",
    "        string CompanyName\n",
    "        string ContactName\n",
    "        string ContactTitle\n",
    "        string Address\n",
    "        string City\n",
    "        string Region\n",
    "        string PostalCode\n",
    "        string Country\n",
    "        string Phone\n",
    "        string Fax\n",
    "        string HomePage\n",
    "    }\n",
    "    Territories {\n",
    "        string TerritoryID PK\n",
    "        string TerritoryDescription\n",
    "        int RegionID FK\n",
    "    }\n",
    "\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97b0c3aa-9e9b-4b35-ae53-1a4f037db914",
   "metadata": {},
   "source": [
    "#### Sidebar: Mermaid Diagrams\n",
    "The diagram above was created with [Mermaid](https://github.com/mermaid-js/mermaid#mermaid) which is an open source diagram generator that uses a markdown-like syntax like this:\n",
    "\n",
    "```\n",
    "erDiagram\n",
    "    CustomerCustomerDemo }o--|| CustomerDemographics : have\n",
    "    CustomerCustomerDemo }o--|| Customers : through\n",
    "\n",
    "    Categories {\n",
    "        int CategoryID PK\n",
    "        string CategoryName\n",
    "        string Description\n",
    "        blob Picture\n",
    "    }\n",
    "    CustomerCustomerDemo {\n",
    "        string CustomerID PK, FK\n",
    "        string CustomerTypeID PK, FK\n",
    "    }\n",
    "```\n",
    "\n",
    "It is now also included in [JupyterLab 4.1 and Notebook 7.1](https://blog.jupyter.org/jupyterlab-4-1-and-notebook-7-1-are-here-20bfc3c102170)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68b93343-c235-411e-bffd-42db9cf049a9",
   "metadata": {},
   "source": [
    "---\n",
    "<a id='ex3'></a>\n",
    "### Observations About the Schema \n",
    "\n",
    "1. **Many-to-Many Relationships Through Linking Tables:**  \n",
    "   Some entities are not directly related in a simple one-to-many manner. Instead, they use intermediate tables (junction tables) to manage many-to-many relationships. For example:\n",
    "   - **EmployeeTerritories** links Employees and Territories. An employee can be responsible for multiple territories, and a territory can be assigned to multiple employees.\n",
    "   - **CustomerCustomerDemo** links Customers and CustomerDemographics, allowing one customer to match multiple demographic segments and vice versa.\n",
    "\n",
    "2. **Multiple Interconnected Entities Reflecting a Business Domain:**  \n",
    "   The schema models a fairly realistic business scenario similar to what might be found in a supply chain or order management system:\n",
    "   - **Customers** place **Orders**.\n",
    "   - **Orders** are fulfilled by **Employees** (“sold by”) and **Shippers** (“ships via”).\n",
    "   - **Orders** contain details referencing **Products**, which belong to certain **Categories** and are supplied by **Suppliers**.\n",
    "   This structure shows how different aspects of a business—customers, suppliers, products, orders, shipping—interconnect in a relational database.\n",
    "\n",
    "3. **Hierarchies and Self-Joins:**  \n",
    "   The **Employees** table includes a `ReportsTo` column, which is a foreign key referencing another EmployeeID. This creates a self-referential relationship that can represent an organizational hierarchy within the same table.\n",
    "\n",
    "4. **Use of Codes as IDs (Surrogate Keys):**  \n",
    "   Many tables use integer primary keys (like `CategoryID`, `EmployeeID`, `ProductID`) and sometimes strings (like `CustomerID`) instead of meaningful business descriptors. This is a common practice to keep relationships simple and efficient. We use surrogate keys (like numeric IDs) rather than meaningful business descriptors as primary keys because business data can change over time, isn’t guaranteed unique, and is often longer and more complex. Surrogate keys are stable, consistently unique, and efficient for indexing and performance. This approach simplifies maintenance, reduces the need for cascading updates, and keeps the database structure independent from changing business logic.\n",
    "\n",
    "5. **Data Integrity and Constraints:**  \n",
    "   The presence of foreign keys (FK) enforces referential integrity. For instance, you cannot have an Order referencing a non-existent Customer, or a Product referencing a Category that doesn’t exist. This ensures the data remains logically consistent. Data integrity and referential constraints ensure all references between tables are valid, preventing records that point to nonexistent entities. This consistency reduces the appearance of null or invalid values, making the data more coherent and reliable for analysis. As a result, analysts spend less time cleaning and fixing broken relationships, and can focus more on extracting meaningful insights."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3acb3120-2c39-4b16-84c8-71cd9c909662",
   "metadata": {},
   "source": [
    "---\n",
    "<a id='ex4'></a>\n",
    "### Primary Key (PK) vs. Foreign Key (FK):\n",
    "\n",
    "Primary keys and foreign keys are important building blocks of relational databases. Primary keys give each record a stable and unique identity, while foreign keys define how entities are connected, supporting the relational structure that allows complex queries and reliable data integrity.\n",
    "\n",
    "- **Primary Key (PK):**  \n",
    "  A primary key is a column (or set of columns) that uniquely identifies each record in a table. Every table should have a primary key to differentiate one row from another. For example:\n",
    "  - `ProductID` is the PK in the **Products** table.\n",
    "  - `CustomerID` is the PK in the **Customers** table.\n",
    "  No two products or customers can share the same primary key value, ensuring uniqueness.\n",
    "\n",
    "- **Foreign Key (FK):**  \n",
    "  A foreign key is a column (or set of columns) in one table that refers to a primary key in another table. It establishes a relationship between the two tables. For example:\n",
    "  - In the **Products** table, `CategoryID` is a FK referencing `Categories.CategoryID`.\n",
    "  - In the **Orders** table, `CustomerID` is a FK referencing `Customers.CustomerID`.\n",
    "\n",
    "**Difference Between PK and FK:**\n",
    "\n",
    "- The primary key identifies a record within its own table. It is unique within that table.\n",
    "- A foreign key references a primary key in another table. It does not need to be unique in its own table, but it must match an existing primary key value in the related table (unless it’s allowed to be NULL for optional relationships).\n",
    "\n",
    "**Purpose of PK and FK in a Database System:**\n",
    "\n",
    "- **Primary Keys:**  \n",
    "  PKs ensure each row in a table can be uniquely retrieved. This is fundamental for indexing, efficient look-ups, and maintaining data integrity. Without a PK, it would be difficult to update, delete, or relate specific rows without ambiguity.\n",
    "\n",
    "- **Foreign Keys:**  \n",
    "  FKs maintain referential integrity by ensuring that relationships between tables are consistent. If a table references an entity in another table, the FK ensures that the entity actually exists. For example, you cannot place an order for a non-existent product if the `ProductID` in the order details is a foreign key referencing the products table. This maintains logical coherence and prevents “orphan” records that reference non-existent parents."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "afe4dd34-97db-473d-bc07-cacffe9399e4",
   "metadata": {},
   "source": [
    "<a id='resources'></a>\n",
    "## Additional Resources\n",
    "\n",
    "- **SQLite Documentation:** [https://www.sqlite.org/docs.html](https://www.sqlite.org/docs.html)\n",
    "- **SQLite Command Line Reference:** [https://www.sqlite.org/cli.html](https://www.sqlite.org/cli.html)\n",
    "- **SQL Tutorial Cheatsheet:** [https://www.sqltutorial.org/sql-cheat-sheet/](https://www.sqltutorial.org/sql-cheat-sheet/)\n",
    "- **SQLite Browser Wiki:** [SQLite Browser GitHub Wiki](https://github.com/sqlitebrowser/sqlitebrowser/wiki)\n",
    "- **Northwind SQLite Repo:** [Github Repo](https://github.com/jpwhite3/northwind-SQLite3/)\n",
    "- **Northwind Database Schema:** [Schema Diagram](https://github.com/jpwhite3/northwind-SQLite3/blob/main/docs/Northwind_ERD.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e0d1a7af-0cd3-49bf-acd2-d4919a3ba37b",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}