{"Investors": {"columns": [[0, "investor_id", "INTEGER", 0, null, 1], [1, "Investor_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["investor_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Lots": {"columns": [[0, "lot_id", "INTEGER", 0, null, 1], [1, "investor_id", "INTEGER", 1, null, 0], [2, "lot_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["lot_id"], "foreign_keys": [[0, 0, "Investors", "investor_id", "investor_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Ref_Transaction_Types": {"columns": [[0, "transaction_type_code", "VARCHAR(10)", 0, null, 1], [1, "transaction_type_description", "VARCHAR(80)", 1, null, 0]], "primary_keys": ["transaction_type_code"], "foreign_keys": [], "unique_constraints": [[[0, 0, "transaction_type_code"]]], "check_constraints": []}, "Transactions": {"columns": [[0, "transaction_id", "INTEGER", 0, null, 1], [1, "investor_id", "INTEGER", 1, null, 0], [2, "transaction_type_code", "VARCHAR(10)", 1, null, 0], [3, "date_of_transaction", "DATETIME", 0, null, 0], [4, "amount_of_transaction", "DECIMAL(19,4)", 0, null, 0], [5, "share_count", "VARCHAR(40)", 0, null, 0], [6, "other_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["transaction_id"], "foreign_keys": [[0, 0, "Ref_Transaction_Types", "transaction_type_code", "transaction_type_code", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Investors", "investor_id", "investor_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Sales": {"columns": [[0, "sales_transaction_id", "INTEGER", 0, null, 1], [1, "sales_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["sales_transaction_id"], "foreign_keys": [[0, 0, "Transactions", "sales_transaction_id", "transaction_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Purchases": {"columns": [[0, "purchase_transaction_id", "INTEGER", 1, null, 0], [1, "purchase_details", "VARCHAR(255)", 1, null, 0]], "primary_keys": [], "foreign_keys": [[0, 0, "Transactions", "purchase_transaction_id", "transaction_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Transactions_Lots": {"columns": [[0, "transaction_id", "INTEGER", 1, null, 0], [1, "lot_id", "INTEGER", 1, null, 0]], "primary_keys": [], "foreign_keys": [[0, 0, "Transactions", "transaction_id", "transaction_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Lots", "lot_id", "lot_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}}
CREATE TABLE Investors (
    investor_id INTEGER   PRIMARY KEY,
    Investor_details VARCHAR(255)
);

CREATE TABLE Lots (
    lot_id INTEGER   PRIMARY KEY,
    investor_id INTEGER NOT NULL,
    lot_details VARCHAR(255),
    FOREIGN KEY (investor_id) REFERENCES Investors(investor_id)
);

CREATE TABLE Ref_Transaction_Types (
    transaction_type_code VARCHAR(10)   PRIMARY KEY,
    transaction_type_description VARCHAR(80) NOT NULL,
    UNIQUE (transaction_type_code)
);

CREATE TABLE Transactions (
    transaction_id INTEGER   PRIMARY KEY,
    investor_id INTEGER NOT NULL,
    transaction_type_code VARCHAR(10) NOT NULL,
    date_of_transaction DATETIME,
    amount_of_transaction DECIMAL(19,4),
    share_count VARCHAR(40),
    other_details VARCHAR(255),
    FOREIGN KEY (transaction_type_code) REFERENCES Ref_Transaction_Types(transaction_type_code),
    FOREIGN KEY (investor_id) REFERENCES Investors(investor_id)
);

CREATE TABLE Sales (
    sales_transaction_id INTEGER   PRIMARY KEY,
    sales_details VARCHAR(255),
    FOREIGN KEY (sales_transaction_id) REFERENCES Transactions(transaction_id)
);

CREATE TABLE Purchases (
    purchase_transaction_id INTEGER NOT NULL,
    purchase_details VARCHAR(255) NOT NULL,
    FOREIGN KEY (purchase_transaction_id) REFERENCES Transactions(transaction_id)
);

CREATE TABLE Transactions_Lots (
    transaction_id INTEGER NOT NULL,
    lot_id INTEGER NOT NULL,
    FOREIGN KEY (transaction_id) REFERENCES Transactions(transaction_id),
    FOREIGN KEY (lot_id) REFERENCES Lots(lot_id)
);