{"Addresses": {"columns": [[0, "address_id", "INTEGER", 0, null, 1], [1, "address_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["address_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Staff": {"columns": [[0, "staff_id", "INTEGER", 0, null, 1], [1, "staff_gender", "VARCHAR(1)", 0, null, 0], [2, "staff_name", "VARCHAR(80)", 0, null, 0]], "primary_keys": ["staff_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Suppliers": {"columns": [[0, "supplier_id", "INTEGER", 0, null, 1], [1, "supplier_name", "VARCHAR(80)", 0, null, 0], [2, "supplier_phone", "VARCHAR(80)", 0, null, 0]], "primary_keys": ["supplier_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Department_Store_Chain": {"columns": [[0, "dept_store_chain_id", "INTEGER", 0, null, 1], [1, "dept_store_chain_name", "VARCHAR(80)", 0, null, 0]], "primary_keys": ["dept_store_chain_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Customers": {"columns": [[0, "customer_id", "INTEGER", 0, null, 1], [1, "payment_method_code", "VARCHAR(10)", 1, null, 0], [2, "customer_code", "VARCHAR(20)", 0, null, 0], [3, "customer_name", "VARCHAR(80)", 0, null, 0], [4, "customer_address", "VARCHAR(255)", 0, null, 0], [5, "customer_phone", "VARCHAR(80)", 0, null, 0], [6, "customer_email", "VARCHAR(80)", 0, null, 0]], "primary_keys": ["customer_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Products": {"columns": [[0, "product_id", "INTEGER", 0, null, 1], [1, "product_type_code", "VARCHAR(10)", 1, null, 0], [2, "product_name", "VARCHAR(80)", 0, null, 0], [3, "product_price", "DECIMAL(19,4)", 0, null, 0]], "primary_keys": ["product_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Supplier_Addresses": {"columns": [[0, "supplier_id", "INTEGER", 1, null, 1], [1, "address_id", "INTEGER", 1, null, 2], [2, "date_from", "DATETIME", 1, null, 0], [3, "date_to", "DATETIME", 0, null, 0]], "primary_keys": ["supplier_id", "address_id"], "foreign_keys": [[0, 0, "Suppliers", "supplier_id", "supplier_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Addresses", "address_id", "address_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "supplier_id"], [1, 1, "address_id"]]], "check_constraints": []}, "Customer_Addresses": {"columns": [[0, "customer_id", "INTEGER", 1, null, 1], [1, "address_id", "INTEGER", 1, null, 2], [2, "date_from", "DATETIME", 1, null, 0], [3, "date_to", "DATETIME", 0, null, 0]], "primary_keys": ["customer_id", "address_id"], "foreign_keys": [[0, 0, "Customers", "customer_id", "customer_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Addresses", "address_id", "address_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "customer_id"], [1, 1, "address_id"]]], "check_constraints": []}, "Customer_Orders": {"columns": [[0, "order_id", "INTEGER", 0, null, 1], [1, "customer_id", "INTEGER", 1, null, 0], [2, "order_status_code", "VARCHAR(10)", 1, null, 0], [3, "order_date", "DATETIME", 1, null, 0]], "primary_keys": ["order_id"], "foreign_keys": [[0, 0, "Customers", "customer_id", "customer_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Department_Stores": {"columns": [[0, "dept_store_id", "INTEGER", 0, null, 1], [1, "dept_store_chain_id", "INTEGER", 0, null, 0], [2, "store_name", "VARCHAR(80)", 0, null, 0], [3, "store_address", "VARCHAR(255)", 0, null, 0], [4, "store_phone", "VARCHAR(80)", 0, null, 0], [5, "store_email", "VARCHAR(80)", 0, null, 0]], "primary_keys": ["dept_store_id"], "foreign_keys": [[0, 0, "Department_Store_Chain", "dept_store_chain_id", "dept_store_chain_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Departments": {"columns": [[0, "department_id", "INTEGER", 0, null, 1], [1, "dept_store_id", "INTEGER", 1, null, 0], [2, "department_name", "VARCHAR(80)", 0, null, 0]], "primary_keys": ["department_id"], "foreign_keys": [[0, 0, "Department_Stores", "dept_store_id", "dept_store_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Order_Items": {"columns": [[0, "order_item_id", "INTEGER", 0, null, 1], [1, "order_id", "INTEGER", 1, null, 0], [2, "product_id", "INTEGER", 1, null, 0]], "primary_keys": ["order_item_id"], "foreign_keys": [[0, 0, "Products", "product_id", "product_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Customer_Orders", "order_id", "order_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Product_Suppliers": {"columns": [[0, "product_id", "INTEGER", 1, null, 1], [1, "supplier_id", "INTEGER", 1, null, 2], [2, "date_supplied_from", "DATETIME", 1, null, 0], [3, "date_supplied_to", "DATETIME", 0, null, 0], [4, "total_amount_purchased", "VARCHAR(80)", 0, null, 0], [5, "total_value_purchased", "DECIMAL(19,4)", 0, null, 0]], "primary_keys": ["product_id", "supplier_id"], "foreign_keys": [[0, 0, "Products", "product_id", "product_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Suppliers", "supplier_id", "supplier_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "product_id"], [1, 1, "supplier_id"]]], "check_constraints": []}, "Staff_Department_Assignments": {"columns": [[0, "staff_id", "INTEGER", 1, null, 1], [1, "department_id", "INTEGER", 1, null, 2], [2, "date_assigned_from", "DATETIME", 1, null, 0], [3, "job_title_code", "VARCHAR(10)", 1, null, 0], [4, "date_assigned_to", "DATETIME", 0, null, 0]], "primary_keys": ["staff_id", "department_id"], "foreign_keys": [[0, 0, "Staff", "staff_id", "staff_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Departments", "department_id", "department_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "staff_id"], [1, 1, "department_id"]]], "check_constraints": []}}
CREATE TABLE Addresses (
    address_id INTEGER   PRIMARY KEY,
    address_details VARCHAR(255)
);

CREATE TABLE Staff (
    staff_id INTEGER   PRIMARY KEY,
    staff_gender VARCHAR(1),
    staff_name VARCHAR(80)
);

CREATE TABLE Suppliers (
    supplier_id INTEGER   PRIMARY KEY,
    supplier_name VARCHAR(80),
    supplier_phone VARCHAR(80)
);

CREATE TABLE Department_Store_Chain (
    dept_store_chain_id INTEGER   PRIMARY KEY,
    dept_store_chain_name VARCHAR(80)
);

CREATE TABLE Customers (
    customer_id INTEGER   PRIMARY KEY,
    payment_method_code VARCHAR(10) NOT NULL,
    customer_code VARCHAR(20),
    customer_name VARCHAR(80),
    customer_address VARCHAR(255),
    customer_phone VARCHAR(80),
    customer_email VARCHAR(80)
);

CREATE TABLE Products (
    product_id INTEGER   PRIMARY KEY,
    product_type_code VARCHAR(10) NOT NULL,
    product_name VARCHAR(80),
    product_price DECIMAL(19,4)
);

CREATE TABLE Supplier_Addresses (
    supplier_id INTEGER NOT NULL  PRIMARY KEY,
    address_id INTEGER NOT NULL  PRIMARY KEY,
    date_from DATETIME NOT NULL,
    date_to DATETIME,
    PRIMARY KEY (supplier_id, address_id),
    UNIQUE (supplier_id, address_id),
    FOREIGN KEY (supplier_id) REFERENCES Suppliers(supplier_id),
    FOREIGN KEY (address_id) REFERENCES Addresses(address_id)
);

CREATE TABLE Customer_Addresses (
    customer_id INTEGER NOT NULL  PRIMARY KEY,
    address_id INTEGER NOT NULL  PRIMARY KEY,
    date_from DATETIME NOT NULL,
    date_to DATETIME,
    PRIMARY KEY (customer_id, address_id),
    UNIQUE (customer_id, address_id),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
    FOREIGN KEY (address_id) REFERENCES Addresses(address_id)
);

CREATE TABLE Customer_Orders (
    order_id INTEGER   PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_status_code VARCHAR(10) NOT NULL,
    order_date DATETIME NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

CREATE TABLE Department_Stores (
    dept_store_id INTEGER   PRIMARY KEY,
    dept_store_chain_id INTEGER,
    store_name VARCHAR(80),
    store_address VARCHAR(255),
    store_phone VARCHAR(80),
    store_email VARCHAR(80),
    FOREIGN KEY (dept_store_chain_id) REFERENCES Department_Store_Chain(dept_store_chain_id)
);

CREATE TABLE Departments (
    department_id INTEGER   PRIMARY KEY,
    dept_store_id INTEGER NOT NULL,
    department_name VARCHAR(80),
    FOREIGN KEY (dept_store_id) REFERENCES Department_Stores(dept_store_id)
);

CREATE TABLE Order_Items (
    order_item_id INTEGER   PRIMARY KEY,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    FOREIGN KEY (product_id) REFERENCES Products(product_id),
    FOREIGN KEY (order_id) REFERENCES Customer_Orders(order_id)
);

CREATE TABLE Product_Suppliers (
    product_id INTEGER NOT NULL  PRIMARY KEY,
    supplier_id INTEGER NOT NULL  PRIMARY KEY,
    date_supplied_from DATETIME NOT NULL,
    date_supplied_to DATETIME,
    total_amount_purchased VARCHAR(80),
    total_value_purchased DECIMAL(19,4),
    PRIMARY KEY (product_id, supplier_id),
    UNIQUE (product_id, supplier_id),
    FOREIGN KEY (product_id) REFERENCES Products(product_id),
    FOREIGN KEY (supplier_id) REFERENCES Suppliers(supplier_id)
);

CREATE TABLE Staff_Department_Assignments (
    staff_id INTEGER NOT NULL  PRIMARY KEY,
    department_id INTEGER NOT NULL  PRIMARY KEY,
    date_assigned_from DATETIME NOT NULL,
    job_title_code VARCHAR(10) NOT NULL,
    date_assigned_to DATETIME,
    PRIMARY KEY (staff_id, department_id),
    UNIQUE (staff_id, department_id),
    FOREIGN KEY (staff_id) REFERENCES Staff(staff_id),
    FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);