Northwind and the Sensory Homunculus of Phantom Retail

# project

Expert with relational database schemas for e-commerce ( online shopping ) and logistics ( shipping inventory and parcel delivery )
Meaning:
* the name, meaning and purpose of SQL tables
* the name, meaning and purpose of SQL columns

For purpose of this project documentation:
The phrase '[Source Database]' means the 'Northwind Database'
The phrase '[Source Table]' means a table in the 'Northwind Database'
The phrase '[Source Column]' means a column in the 'Northwind Datbase'
The phrade 'concept' means a logical placeholder for a named table or named column of a database table

Capture information context to help match with a customer natural language enquiry with common functionality that is:
* present in the [Source Database].
* missing from the [Source Database].

## Out of scope of project implementation
Mentioned for orientation
For example consider:
The customer question: "How can I track my package?"
This maps to the answer:
Pseudo concept 'tracking' ( example for illustrative purpose )
"Unfortunately we currently don't have ability to track your package. But your requirement to do this has been noted."
Here illustrative concept generalization of 'tracking' is a ghost feature for the [Source Database].

## adding ghost features
Your expert e-commerce and logistics expertise is needed to know about important *common* and *productive* functionally for customer interaction, tables, columns and features that exist on real production e-commerce and logistics systems, but are missing in the [Source Database].
These missing tables will be called 'ghost tables', with a name prefix of 'ghost'.
These missing columns will be called 'ghost columns', with a name prefix 'ghost'
For example: Source table 'nwOrders' defines an 'OrderDate' but not a 'ShippedDate' or 'DeliveredDate'.
Example: The illustrative externally determined 'ShippedDate' and 'DeliveredDate' would thus be included as 'ghostShippedDate' and 'ghostDeliveredDate' respectively. If these ghost columns are added to [Source Database] table 'nwOrders', their new enumeration names would be 'nwOrders_ghostShippedDate' and 'nwOrders_ghostDeliveredDate' respectively.
Ghost columns can be added to and removed via file Ghost_Database_Schema.md and concepts.py. However do not remove non-ghost columns from [Source Database] tables. Do not remove non-ghost tables from the [Source Database].
The file 'Ghost_Database_Schema.md' is to be populated with a larger overlay schema containing tables from [Source Database] but with ghost columns and tables added. This gives a SQL database audience understanding and documentation. This is maintained in tandem with concepts.py which must always agree ( be synchronized ).

## development environment
This is a python3 environment.

### Setup
No setup is required.
Don't create virtual environments. Just use the current python3 directly.
The file 'requirements.txt' shows available python libraries already loaded in the environment.
If you think additional dependencies are need then ask and justify why. Rationale: the pre-built development context will be reused.

## File Structure

Source_Database_Schema.md - Read-only. Shows relationships between columns in original [Source Database]
Ghost_Database_Schema.md - Expressions to recreate target [Source Database] plus additional ghost columns and ghost tables.
concepts.py - file to capture, manage, score and relate database tables and names

## Rules
Don't suggest adding or removing tables from the [Source Database]
Don't suggest removing non-ghost columns from the [Source Database] tables.
Don't create an actual database. We are simply exploring to understand and populate concepts.py from the e-commerce and logistic domain information analysis.

# Source Database Schema
Readonly. Do not modify information in this file.
Instead use for reference for when working on Ghost_Database_Schema.md
## Relationships between tables

```mermaid
%%{init: {
'theme': 'default',
'look': 'classic',
'layout': 'dagre'
} }%%
erDiagram
nwCustomers || --o{ nwOrders : nwOrders.CustomerID
nwOrders }| --|| Employees : nwOrders.EmployeeID
nwOrders }| --|| Shippers : nwOrders.ShipperID
nwOrders || --|{ OrderDetails : OrderDetails.OrderID
Products }| --|| Suppliers : Products.SupplierID
Products }| --|| Categories : Products.CategoryID
Products || --|{ OrderDetails: OrderDetails.ProductID

nwCustomers {
INTEGER CustomerID PK
VARCHAR(48) CustomerName
VARCHAR(32) ContactName
VARCHAR(64) Address
VARCHAR(32) City
VARCHAR(16) PostalCode
VARCHAR(32) Country
}
nwOrders {
INTEGER OrderID PK
INTEGER CustomerID
INTEGER EmployeeID
DATE OrderDate
INTEGER ShipperID
}
Shippers {
INTEGER ShipperID PK
VARCHAR(32) ShipperName
VARCHAR(14) Phone
}
Suppliers {
INTEGER SupplierID PK
VARCHAR(48) SupplierName
VARCHAR(32) ContactName
VARCHAR(64) Address
VARCHAR(32) City
VARCHAR(16) PostalCode
VARCHAR(32) Country
VARCHAR(15) Phone
}
Products {
INTEGER ProductID PK
VARCHAR(48) ProductName
INTEGER SupplierID
INTEGER CategoryID
VARCHAR(32) Unit
NUMERIC Price
}
OrderDetails {
INTEGER OrderDetailID PK
INTEGER OrderID
INTEGER ProductID
INTEGER Quantity
}
Categories {
INTEGER CategoryID PK
VARCHAR(32) CategoryName
VARCHAR(64) Description
}
Employees {
INTEGER EmployeeID PK
VARCHAR(32) LastName
VARCHAR(32) FirstName
DATE BirthDate
VARCHAR(16) Photo
VARCHAR(512) Notes
}

# SQL creating Northwind database tables and columns
```sql
CREATE TABLE nwCustomers (CustomerID INTEGER PRIMARY KEY, CustomerName VARCHAR(48), ContactName VARCHAR(32), Address VARCHAR(64), City VARCHAR(32), PostalCode VARCHAR(16), Country VARCHAR(32));
CREATE TABLE nwOrders (OrderID INTEGER PRIMARY KEY, CustomerID INTEGER, EmployeeID INTEGER, OrderDate DATE, ShipperID INTEGER);
CREATE TABLE Shippers (ShipperID INTEGER PRIMARY KEY, ShipperName VARCHAR(32), Phone VARCHAR(14));
CREATE TABLE Suppliers (SupplierID INTEGER PRIMARY KEY, SupplierName VARCHAR(48), ContactName VARCHAR(32), Address VARCHAR(64), City VARCHAR(32), PostalCode VARCHAR(16), Country VARCHAR(32), Phone VARCHAR(15));
CREATE TABLE Products (ProductID INTEGER PRIMARY KEY, ProductName VARCHAR(48), SupplierID INTEGER, CategoryID INTEGER, Unit VARCHAR(32), Price NUMERIC);
CREATE TABLE OrderDetails (OrderDetailID INTEGER PRIMARY KEY, OrderID INTEGER, ProductID INTEGER, Quantity INTEGER);
CREATE TABLE Categories (CategoryID INTEGER PRIMARY KEY, CategoryName VARCHAR(32), Description VARCHAR(64));
CREATE TABLE Employees (EmployeeID INTEGER PRIMARY KEY, LastName VARCHAR(32), FirstName VARCHAR(32), BirthDate DATE, Photo VARCHAR(16), Notes VARCHAR(512));

# This file holds enumeration placeholder definitions and descriptions (Config) for existing Northwind database tables and columns. To be extended with ghost columns and tables. The table and column names in Ghost_Database_Schema.md must always be synchronized with values in the Concept enumeration
# Naming relation convention:
# * Table name is equivalent to Enumeration name.
#   For example table 'nwCustomers' related to enumeration entry 'nwCustomers'.
# * Column name in table is equivalent to Enumeration name.
#   For example: column 'Address" in table 'nwCustomers' has corresponding enumeration entry 'nwCustomers_Address'
# Where the table name and column name are concatenated with underscore character

from enum import Enum,auto

# Holds concept names used as keys by dictionary 'Config'
# Note: Integer and GUID (UUID) Primary keys that join relational tables are not assigned a corresponding enumeration entry. Rationale: Customers would not enquire about database primary keys unless they have some informational value.
class Concept(Enum):
    nwCustomers=auto()
    nwCustomers_CustomerName=auto()
    nwCustomers_ContactName=auto()
    nwCustomers_Address=auto()
    nwCustomers_City=auto()
    nwCustomers_PostalCode=auto()
    nwCustomers_Country=auto()

    nwOrders=auto()
    nwOrders_OrderDate=auto()
    
    Shippers=auto()
    Shippers_ShipperName=auto()
    Shippers_Phone=auto()

    Suppliers=auto()
    Suppliers_SupplierName=auto()
    Suppliers_ContactName=auto()
    Suppliers_Address=auto()
    Suppliers_City=auto()
    Suppliers_PostalCode=auto()
    Suppliers_Country=auto()
    Suppliers_Phone=auto()

    Products=auto()
    Products_ProductName=auto()
    Products_Unit=auto()
    Products_Price=auto()
    
    OrderDetails=auto()
    OrderDetails_Quantity=auto()

    Categories=auto()
    Categories_CategoryName=auto()
    Categories_Description=auto()

    Employees=auto()
    Employees_LastName=auto()
    Employees_FirstName=auto()
    Employees_BirthDate=auto()
    Employees_Photo=auto()
    Employees_Notes=auto()


# Describes concepts used by base Nothwind Database plus ghost concepts
# Each [SourceDatabase] existing columns and tables have 'useful' set to one. Don't change these.
# Ghost Tables and Columns can be fully evaluated, compared and their 'useful' values
# elevated or decremented as new ghost tables and columns are added and compared.
# If a Ghost Table is removed then All of its corresponding Column entries would also be removed
# A Ghost table MUST have at least one Ghost Column entry
# Each ghost table or column MUST have a tangible 'useful' rating in the range from 1 to 20 inclusive, where:
# '1' means very useful and highest priority for direct customer interaction
# '20' means least useful and lowest priority for direct customer interaction
# Usefulness for customer interaction would be in customer scenarios:
# * buying a product from a website with their mobile phone or home computer
# * shipping product to the customer
# * tracking delivery of a dispatched product to a customer address
# * returning a defective product and asking for a refund
# * finding and viewing product details and features
# * requesting information about an order they made.
# * requesting a brochure about a product category
# These scenarios are illustrative and not exhaustive. Others can be suggested
Config={
    # Concept table Template by comments example
    Concept.nwCustomers:{
       # Mandatory. Type int
       # integer between 1 and 20
       'useful': 1,
       
       # Mandatory. Type String.
       # Capture rationale for 'useful score'
       'useful_justification':' In Northwind Database',

        # Use only if table is from Northwind database table
        'northwind_label': 'nwCustomers',

        # Type: list[tuple[str,str]]
        # List of table names use by other products for the same thing
        # Where type is ( [product_name], [column_name] 
        # Can accommodate multiple labels from a product if needed
        'ghost_label': [('ACME','Customer')],

        # Mandatory. Type list[str].
        # English language aliases in one or more words, with and without abbreviations, that are terms used commonly by general public to refer to this functionality.
        # Language aliases are free to overlap with terms and phrases used by other concepts
        # Aim to create between five and fifty variations.
        # Include negative and complaint phrasing customers use (e.g. 'Overcharged', 'Wrong item') where the complaint is about this concept.
        # The Quality of alias term is more important than maximizing the number of entries
        'generic_aliases':[
            'Customer', 'Customers', 'Client', 'Buyer', 'Purchaser'
            ],

        # Describe in one to four sentences precisely the functional intention of this table.
        'description':'Holds information about the customer making a purchase on an e-commerce site.'
    },

    # Concept column Template by comments example
    Concept.nwCustomers_Address:{
       # Mandatory. Type int
       # integer between 1 and 20
       'useful': 1,
       
       # Mandatory. Type String.
       # Capture rationale for 'useful score'
       'useful_justification':' In Northwind Database',

        # Use only if column is from Northwind database
        'northwind_label': 'Address',

        # Type: list[tuple[str,str]]
        # List of column names use by other products for the same thing
        # Where tuple type meaning is ( [product_name], [column_name] )
        # Can accommodate multiple labels from a product if needed
        'ghost_label': [('ACME','Street_name')],

        # Mandatory. Type list[str].
        # Generic aliases have one or more words, with and without abbreviations, that are terms or phrases used commonly by general public to refer to this functionality.
        # Language aliases are free to overlap with terms and phrases used by other concepts
        # Aim to create between five and fifty variations.
        # Include negative and complaint phrasing customers use (e.g. 'Overcharged', 'Wrong item') where the complaint is about this concept.
        # The Quality of alias term is more important than maximizing the number of entries
        'generic_aliases':[
            'Address', 'Street address', 'Address line 1'
            ],

        # Optional. Type list[str]. Columns only, not tables.
        # Around ten example values the column holds. For columns with a fixed set of values
        # (e.g. a status) list the canonical values; otherwise give realistic examples.
        'values':[
            ],

        # Mandatory. Type String.
        # Describe in one to four sentences precisely the functional intention this column.
        'description':'Home or delivery address information the customer making a purchase on an e-commerce site.'
    },

}