{"Problem_Category_Codes": {"columns": [[0, "problem_category_code", "VARCHAR(20)", 0, null, 1], [1, "problem_category_description", "VARCHAR(80)", 0, null, 0]], "primary_keys": ["problem_category_code"], "foreign_keys": [], "unique_constraints": [[[0, 0, "problem_category_code"]]], "check_constraints": []}, "Problem_Log": {"columns": [[0, "problem_log_id", "INTEGER", 0, null, 1], [1, "assigned_to_staff_id", "INTEGER", 1, null, 0], [2, "problem_id", "INTEGER", 1, null, 0], [3, "problem_category_code", "VARCHAR(20)", 1, null, 0], [4, "problem_status_code", "VARCHAR(20)", 1, null, 0], [5, "log_entry_date", "DATETIME", 0, null, 0], [6, "log_entry_description", "VARCHAR(255)", 0, null, 0], [7, "log_entry_fix", "VARCHAR(255)", 0, null, 0], [8, "other_log_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["problem_log_id"], "foreign_keys": [[0, 0, "Problem_Status_Codes", "problem_status_code", "problem_status_code", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Problems", "problem_id", "problem_id", "NO ACTION", "NO ACTION", "NONE"], [2, 0, "Staff", "assigned_to_staff_id", "staff_id", "NO ACTION", "NO ACTION", "NONE"], [3, 0, "Problem_Category_Codes", "problem_category_code", "problem_category_code", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Problem_Status_Codes": {"columns": [[0, "problem_status_code", "VARCHAR(20)", 0, null, 1], [1, "problem_status_description", "VARCHAR(80)", 0, null, 0]], "primary_keys": ["problem_status_code"], "foreign_keys": [], "unique_constraints": [[[0, 0, "problem_status_code"]]], "check_constraints": []}, "Product": {"columns": [[0, "product_id", "INTEGER", 0, null, 1], [1, "product_name", "VARCHAR(80)", 0, null, 0], [2, "product_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["product_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Staff": {"columns": [[0, "staff_id", "INTEGER", 0, null, 1], [1, "staff_first_name", "VARCHAR(80)", 0, null, 0], [2, "staff_last_name", "VARCHAR(80)", 0, null, 0], [3, "other_staff_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["staff_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Problems": {"columns": [[0, "problem_id", "INTEGER", 0, null, 1], [1, "product_id", "INTEGER", 1, null, 0], [2, "closure_authorised_by_staff_id", "INTEGER", 1, null, 0], [3, "reported_by_staff_id", "INTEGER", 1, null, 0], [4, "date_problem_reported", "DATETIME", 1, null, 0], [5, "date_problem_closed", "DATETIME", 0, null, 0], [6, "problem_description", "VARCHAR(255)", 0, null, 0], [7, "other_problem_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["problem_id"], "foreign_keys": [[0, 0, "Staff", "reported_by_staff_id", "staff_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Product", "product_id", "product_id", "NO ACTION", "NO ACTION", "NONE"], [2, 0, "Staff", "closure_authorised_by_staff_id", "staff_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}}
CREATE TABLE Problem_Category_Codes (
    problem_category_code VARCHAR(20)   PRIMARY KEY,
    problem_category_description VARCHAR(80),
    UNIQUE (problem_category_code)
);

CREATE TABLE Problem_Log (
    problem_log_id INTEGER   PRIMARY KEY,
    assigned_to_staff_id INTEGER NOT NULL,
    problem_id INTEGER NOT NULL,
    problem_category_code VARCHAR(20) NOT NULL,
    problem_status_code VARCHAR(20) NOT NULL,
    log_entry_date DATETIME,
    log_entry_description VARCHAR(255),
    log_entry_fix VARCHAR(255),
    other_log_details VARCHAR(255),
    FOREIGN KEY (problem_status_code) REFERENCES Problem_Status_Codes(problem_status_code),
    FOREIGN KEY (problem_id) REFERENCES Problems(problem_id),
    FOREIGN KEY (assigned_to_staff_id) REFERENCES Staff(staff_id),
    FOREIGN KEY (problem_category_code) REFERENCES Problem_Category_Codes(problem_category_code)
);

CREATE TABLE Problem_Status_Codes (
    problem_status_code VARCHAR(20)   PRIMARY KEY,
    problem_status_description VARCHAR(80),
    UNIQUE (problem_status_code)
);

CREATE TABLE Product (
    product_id INTEGER   PRIMARY KEY,
    product_name VARCHAR(80),
    product_details VARCHAR(255)
);

CREATE TABLE Staff (
    staff_id INTEGER   PRIMARY KEY,
    staff_first_name VARCHAR(80),
    staff_last_name VARCHAR(80),
    other_staff_details VARCHAR(255)
);

CREATE TABLE Problems (
    problem_id INTEGER   PRIMARY KEY,
    product_id INTEGER NOT NULL,
    closure_authorised_by_staff_id INTEGER NOT NULL,
    reported_by_staff_id INTEGER NOT NULL,
    date_problem_reported DATETIME NOT NULL,
    date_problem_closed DATETIME,
    problem_description VARCHAR(255),
    other_problem_details VARCHAR(255),
    FOREIGN KEY (reported_by_staff_id) REFERENCES Staff(staff_id),
    FOREIGN KEY (product_id) REFERENCES Product(product_id),
    FOREIGN KEY (closure_authorised_by_staff_id) REFERENCES Staff(staff_id)
);