{"Document_Types": {"columns": [[0, "document_type_code", "VARCHAR(10)", 0, null, 1], [1, "document_description", "VARCHAR(255)", 1, null, 0]], "primary_keys": ["document_type_code"], "foreign_keys": [], "unique_constraints": [[[0, 0, "document_type_code"]]], "check_constraints": []}, "Documents": {"columns": [[0, "document_id", "INTEGER", 0, null, 1], [1, "document_type_code", "VARCHAR(10)", 0, null, 0], [2, "grant_id", "INTEGER", 1, null, 0], [3, "sent_date", "DATETIME", 1, null, 0], [4, "response_received_date", "DATETIME", 1, null, 0], [5, "other_details", "VARCHAR(255)", 1, null, 0]], "primary_keys": ["document_id"], "foreign_keys": [[0, 0, "Grants", "grant_id", "grant_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Document_Types", "document_type_code", "document_type_code", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Grants": {"columns": [[0, "grant_id", "INTEGER", 0, null, 1], [1, "organisation_id", "INTEGER", 1, null, 0], [2, "grant_amount", "DECIMAL(19,4)", 1, "0", 0], [3, "grant_start_date", "DATETIME", 1, null, 0], [4, "grant_end_date", "DATETIME", 1, null, 0], [5, "other_details", "VARCHAR(255)", 1, null, 0]], "primary_keys": ["grant_id"], "foreign_keys": [[0, 0, "Organisations", "organisation_id", "organisation_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Organisation_Types": {"columns": [[0, "organisation_type", "VARCHAR(10)", 0, null, 1], [1, "organisation_type_description", "VARCHAR(255)", 1, null, 0]], "primary_keys": ["organisation_type"], "foreign_keys": [], "unique_constraints": [[[0, 0, "organisation_type"]]], "check_constraints": []}, "Organisations": {"columns": [[0, "organisation_id", "INTEGER", 0, null, 1], [1, "organisation_type", "VARCHAR(10)", 1, null, 0], [2, "organisation_details", "VARCHAR(255)", 1, null, 0]], "primary_keys": ["organisation_id"], "foreign_keys": [[0, 0, "Organisation_Types", "organisation_type", "organisation_type", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Project_Outcomes": {"columns": [[0, "project_id", "INTEGER", 1, null, 0], [1, "outcome_code", "VARCHAR(10)", 1, null, 0], [2, "outcome_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": [], "foreign_keys": [[0, 0, "Research_Outcomes", "outcome_code", "outcome_code", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Projects", "project_id", "project_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Project_Staff": {"columns": [[0, "staff_id", "DOUBLE", 0, null, 1], [1, "project_id", "INTEGER", 1, null, 0], [2, "role_code", "VARCHAR(10)", 1, null, 0], [3, "date_from", "DATETIME", 0, null, 0], [4, "date_to", "DATETIME", 0, null, 0], [5, "other_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["staff_id"], "foreign_keys": [[0, 0, "Staff_Roles", "role_code", "role_code", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Projects", "project_id", "project_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "staff_id"]]], "check_constraints": []}, "Projects": {"columns": [[0, "project_id", "INTEGER", 0, null, 1], [1, "organisation_id", "INTEGER", 1, null, 0], [2, "project_details", "VARCHAR(255)", 1, null, 0]], "primary_keys": ["project_id"], "foreign_keys": [[0, 0, "Organisations", "organisation_id", "organisation_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Research_Outcomes": {"columns": [[0, "outcome_code", "VARCHAR(10)", 0, null, 1], [1, "outcome_description", "VARCHAR(255)", 1, null, 0]], "primary_keys": ["outcome_code"], "foreign_keys": [], "unique_constraints": [[[0, 0, "outcome_code"]]], "check_constraints": []}, "Research_Staff": {"columns": [[0, "staff_id", "INTEGER", 0, null, 1], [1, "employer_organisation_id", "INTEGER", 1, null, 0], [2, "staff_details", "VARCHAR(255)", 1, null, 0]], "primary_keys": ["staff_id"], "foreign_keys": [[0, 0, "Organisations", "employer_organisation_id", "organisation_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Staff_Roles": {"columns": [[0, "role_code", "VARCHAR(10)", 0, null, 1], [1, "role_description", "VARCHAR(255)", 1, null, 0]], "primary_keys": ["role_code"], "foreign_keys": [], "unique_constraints": [[[0, 0, "role_code"]]], "check_constraints": []}, "Tasks": {"columns": [[0, "task_id", "INTEGER", 0, null, 1], [1, "project_id", "INTEGER", 1, null, 0], [2, "task_details", "VARCHAR(255)", 1, null, 0], [3, "eg Agree Objectives", "VARCHAR(1)", 0, null, 0]], "primary_keys": ["task_id"], "foreign_keys": [[0, 0, "Projects", "project_id", "project_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}}
CREATE TABLE Document_Types (
    document_type_code VARCHAR(10)   PRIMARY KEY,
    document_description VARCHAR(255) NOT NULL,
    UNIQUE (document_type_code)
);

CREATE TABLE Documents (
    document_id INTEGER   PRIMARY KEY,
    document_type_code VARCHAR(10),
    grant_id INTEGER NOT NULL,
    sent_date DATETIME NOT NULL,
    response_received_date DATETIME NOT NULL,
    other_details VARCHAR(255) NOT NULL,
    FOREIGN KEY (grant_id) REFERENCES Grants(grant_id),
    FOREIGN KEY (document_type_code) REFERENCES Document_Types(document_type_code)
);

CREATE TABLE Grants (
    grant_id INTEGER   PRIMARY KEY,
    organisation_id INTEGER NOT NULL,
    grant_amount DECIMAL(19,4) NOT NULL DEFAULT 0,
    grant_start_date DATETIME NOT NULL,
    grant_end_date DATETIME NOT NULL,
    other_details VARCHAR(255) NOT NULL,
    FOREIGN KEY (organisation_id) REFERENCES Organisations(organisation_id)
);

CREATE TABLE Organisation_Types (
    organisation_type VARCHAR(10)   PRIMARY KEY,
    organisation_type_description VARCHAR(255) NOT NULL,
    UNIQUE (organisation_type)
);

CREATE TABLE Organisations (
    organisation_id INTEGER   PRIMARY KEY,
    organisation_type VARCHAR(10) NOT NULL,
    organisation_details VARCHAR(255) NOT NULL,
    FOREIGN KEY (organisation_type) REFERENCES Organisation_Types(organisation_type)
);

CREATE TABLE Project_Outcomes (
    project_id INTEGER NOT NULL,
    outcome_code VARCHAR(10) NOT NULL,
    outcome_details VARCHAR(255),
    FOREIGN KEY (outcome_code) REFERENCES Research_Outcomes(outcome_code),
    FOREIGN KEY (project_id) REFERENCES Projects(project_id)
);

CREATE TABLE Project_Staff (
    staff_id DOUBLE   PRIMARY KEY,
    project_id INTEGER NOT NULL,
    role_code VARCHAR(10) NOT NULL,
    date_from DATETIME,
    date_to DATETIME,
    other_details VARCHAR(255),
    UNIQUE (staff_id),
    FOREIGN KEY (role_code) REFERENCES Staff_Roles(role_code),
    FOREIGN KEY (project_id) REFERENCES Projects(project_id)
);

CREATE TABLE Projects (
    project_id INTEGER   PRIMARY KEY,
    organisation_id INTEGER NOT NULL,
    project_details VARCHAR(255) NOT NULL,
    FOREIGN KEY (organisation_id) REFERENCES Organisations(organisation_id)
);

CREATE TABLE Research_Outcomes (
    outcome_code VARCHAR(10)   PRIMARY KEY,
    outcome_description VARCHAR(255) NOT NULL,
    UNIQUE (outcome_code)
);

CREATE TABLE Research_Staff (
    staff_id INTEGER   PRIMARY KEY,
    employer_organisation_id INTEGER NOT NULL,
    staff_details VARCHAR(255) NOT NULL,
    FOREIGN KEY (employer_organisation_id) REFERENCES Organisations(organisation_id)
);

CREATE TABLE Staff_Roles (
    role_code VARCHAR(10)   PRIMARY KEY,
    role_description VARCHAR(255) NOT NULL,
    UNIQUE (role_code)
);

CREATE TABLE Tasks (
    task_id INTEGER   PRIMARY KEY,
    project_id INTEGER NOT NULL,
    task_details VARCHAR(255) NOT NULL,
    eg Agree Objectives VARCHAR(1),
    FOREIGN KEY (project_id) REFERENCES Projects(project_id)
);