{"Ref_Document_Types": {"columns": [[0, "document_type_code", "CHAR(15)", 1, null, 1], [1, "document_type_description", "VARCHAR(255)", 1, null, 0]], "primary_keys": ["document_type_code"], "foreign_keys": [], "unique_constraints": [[[0, 0, "document_type_code"]]], "check_constraints": []}, "Roles": {"columns": [[0, "role_code", "CHAR(15)", 1, null, 1], [1, "role_description", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["role_code"], "foreign_keys": [], "unique_constraints": [[[0, 0, "role_code"]]], "check_constraints": []}, "Addresses": {"columns": [[0, "address_id", "INTEGER", 1, null, 1], [1, "address_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["address_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Ref_Document_Status": {"columns": [[0, "document_status_code", "CHAR(15)", 1, null, 1], [1, "document_status_description", "VARCHAR(255)", 1, null, 0]], "primary_keys": ["document_status_code"], "foreign_keys": [], "unique_constraints": [[[0, 0, "document_status_code"]]], "check_constraints": []}, "Ref_Shipping_Agents": {"columns": [[0, "shipping_agent_code", "CHAR(15)", 1, null, 1], [1, "shipping_agent_name", "VARCHAR(255)", 1, null, 0], [2, "shipping_agent_description", "VARCHAR(255)", 1, null, 0]], "primary_keys": ["shipping_agent_code"], "foreign_keys": [], "unique_constraints": [[[0, 0, "shipping_agent_code"]]], "check_constraints": []}, "Documents": {"columns": [[0, "document_id", "INTEGER", 1, null, 1], [1, "document_status_code", "CHAR(15)", 1, null, 0], [2, "document_type_code", "CHAR(15)", 1, null, 0], [3, "shipping_agent_code", "CHAR(15)", 0, null, 0], [4, "receipt_date", "DATETIME", 0, null, 0], [5, "receipt_number", "VARCHAR(255)", 0, null, 0], [6, "other_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["document_id"], "foreign_keys": [[0, 0, "Ref_Shipping_Agents", "shipping_agent_code", "shipping_agent_code", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Ref_Document_Status", "document_status_code", "document_status_code", "NO ACTION", "NO ACTION", "NONE"], [2, 0, "Ref_Document_Types", "document_type_code", "document_type_code", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Employees": {"columns": [[0, "employee_id", "INTEGER", 1, null, 1], [1, "role_code", "CHAR(15)", 1, null, 0], [2, "employee_name", "VARCHAR(255)", 0, null, 0], [3, "other_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["employee_id"], "foreign_keys": [[0, 0, "Roles", "role_code", "role_code", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Document_Drafts": {"columns": [[0, "document_id", "INTEGER", 1, null, 1], [1, "draft_number", "INTEGER", 1, null, 2], [2, "draft_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["document_id", "draft_number"], "foreign_keys": [[0, 0, "Documents", "document_id", "document_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "document_id"], [1, 1, "draft_number"]]], "check_constraints": []}, "Draft_Copies": {"columns": [[0, "document_id", "INTEGER", 1, null, 1], [1, "draft_number", "INTEGER", 1, null, 2], [2, "copy_number", "INTEGER", 1, null, 3]], "primary_keys": ["document_id", "draft_number", "copy_number"], "foreign_keys": [[0, 0, "Document_Drafts", "document_id", "document_id", "NO ACTION", "NO ACTION", "NONE"], [0, 1, "Document_Drafts", "draft_number", "draft_number", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "document_id"], [1, 1, "draft_number"], [2, 2, "copy_number"]]], "check_constraints": []}, "Circulation_History": {"columns": [[0, "document_id", "INTEGER", 1, null, 1], [1, "draft_number", "INTEGER", 1, null, 2], [2, "copy_number", "INTEGER", 1, null, 3], [3, "employee_id", "INTEGER", 1, null, 4]], "primary_keys": ["document_id", "draft_number", "copy_number", "employee_id"], "foreign_keys": [[0, 0, "Employees", "employee_id", "employee_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Draft_Copies", "document_id", "document_id", "NO ACTION", "NO ACTION", "NONE"], [1, 1, "Draft_Copies", "draft_number", "draft_number", "NO ACTION", "NO ACTION", "NONE"], [1, 2, "Draft_Copies", "copy_number", "copy_number", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "document_id"], [1, 1, "draft_number"], [2, 2, "copy_number"], [3, 3, "employee_id"]]], "check_constraints": []}, "Documents_Mailed": {"columns": [[0, "document_id", "INTEGER", 1, null, 1], [1, "mailed_to_address_id", "INTEGER", 1, null, 2], [2, "mailing_date", "DATETIME", 0, null, 0]], "primary_keys": ["document_id", "mailed_to_address_id"], "foreign_keys": [[0, 0, "Addresses", "mailed_to_address_id", "address_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Documents", "document_id", "document_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "document_id"], [1, 1, "mailed_to_address_id"]]], "check_constraints": []}}
CREATE TABLE Ref_Document_Types (
    document_type_code CHAR(15) NOT NULL  PRIMARY KEY,
    document_type_description VARCHAR(255) NOT NULL,
    UNIQUE (document_type_code)
);

CREATE TABLE Roles (
    role_code CHAR(15) NOT NULL  PRIMARY KEY,
    role_description VARCHAR(255),
    UNIQUE (role_code)
);

CREATE TABLE Addresses (
    address_id INTEGER NOT NULL  PRIMARY KEY,
    address_details VARCHAR(255)
);

CREATE TABLE Ref_Document_Status (
    document_status_code CHAR(15) NOT NULL  PRIMARY KEY,
    document_status_description VARCHAR(255) NOT NULL,
    UNIQUE (document_status_code)
);

CREATE TABLE Ref_Shipping_Agents (
    shipping_agent_code CHAR(15) NOT NULL  PRIMARY KEY,
    shipping_agent_name VARCHAR(255) NOT NULL,
    shipping_agent_description VARCHAR(255) NOT NULL,
    UNIQUE (shipping_agent_code)
);

CREATE TABLE Documents (
    document_id INTEGER NOT NULL  PRIMARY KEY,
    document_status_code CHAR(15) NOT NULL,
    document_type_code CHAR(15) NOT NULL,
    shipping_agent_code CHAR(15),
    receipt_date DATETIME,
    receipt_number VARCHAR(255),
    other_details VARCHAR(255),
    FOREIGN KEY (shipping_agent_code) REFERENCES Ref_Shipping_Agents(shipping_agent_code),
    FOREIGN KEY (document_status_code) REFERENCES Ref_Document_Status(document_status_code),
    FOREIGN KEY (document_type_code) REFERENCES Ref_Document_Types(document_type_code)
);

CREATE TABLE Employees (
    employee_id INTEGER NOT NULL  PRIMARY KEY,
    role_code CHAR(15) NOT NULL,
    employee_name VARCHAR(255),
    other_details VARCHAR(255),
    FOREIGN KEY (role_code) REFERENCES Roles(role_code)
);

CREATE TABLE Document_Drafts (
    document_id INTEGER NOT NULL  PRIMARY KEY,
    draft_number INTEGER NOT NULL  PRIMARY KEY,
    draft_details VARCHAR(255),
    PRIMARY KEY (document_id, draft_number),
    UNIQUE (document_id, draft_number),
    FOREIGN KEY (document_id) REFERENCES Documents(document_id)
);

CREATE TABLE Draft_Copies (
    document_id INTEGER NOT NULL  PRIMARY KEY,
    draft_number INTEGER NOT NULL  PRIMARY KEY,
    copy_number INTEGER NOT NULL  PRIMARY KEY,
    PRIMARY KEY (document_id, draft_number, copy_number),
    UNIQUE (document_id, draft_number, copy_number),
    FOREIGN KEY (document_id) REFERENCES Document_Drafts(document_id),
    FOREIGN KEY (draft_number) REFERENCES Document_Drafts(draft_number)
);

CREATE TABLE Circulation_History (
    document_id INTEGER NOT NULL  PRIMARY KEY,
    draft_number INTEGER NOT NULL  PRIMARY KEY,
    copy_number INTEGER NOT NULL  PRIMARY KEY,
    employee_id INTEGER NOT NULL  PRIMARY KEY,
    PRIMARY KEY (document_id, draft_number, copy_number, employee_id),
    UNIQUE (document_id, draft_number, copy_number, employee_id),
    FOREIGN KEY (employee_id) REFERENCES Employees(employee_id),
    FOREIGN KEY (document_id) REFERENCES Draft_Copies(document_id),
    FOREIGN KEY (draft_number) REFERENCES Draft_Copies(draft_number),
    FOREIGN KEY (copy_number) REFERENCES Draft_Copies(copy_number)
);

CREATE TABLE Documents_Mailed (
    document_id INTEGER NOT NULL  PRIMARY KEY,
    mailed_to_address_id INTEGER NOT NULL  PRIMARY KEY,
    mailing_date DATETIME,
    PRIMARY KEY (document_id, mailed_to_address_id),
    UNIQUE (document_id, mailed_to_address_id),
    FOREIGN KEY (mailed_to_address_id) REFERENCES Addresses(address_id),
    FOREIGN KEY (document_id) REFERENCES Documents(document_id)
);