{"Ref_Document_Types": {"columns": [[0, "Document_Type_Code", "CHAR(15)", 1, null, 1], [1, "Document_Type_Name", "VARCHAR(255)", 1, null, 0], [2, "Document_Type_Description", "VARCHAR(255)", 1, null, 0]], "primary_keys": ["Document_Type_Code"], "foreign_keys": [], "unique_constraints": [[[0, 0, "Document_Type_Code"]]], "check_constraints": []}, "Ref_Calendar": {"columns": [[0, "Calendar_Date", "DATETIME", 1, null, 1], [1, "Day_Number", "INTEGER", 0, null, 0]], "primary_keys": ["Calendar_Date"], "foreign_keys": [], "unique_constraints": [[[0, 0, "Calendar_Date"]]], "check_constraints": []}, "Ref_Locations": {"columns": [[0, "Location_Code", "CHAR(15)", 1, null, 1], [1, "Location_Name", "VARCHAR(255)", 1, null, 0], [2, "Location_Description", "VARCHAR(255)", 1, null, 0]], "primary_keys": ["Location_Code"], "foreign_keys": [], "unique_constraints": [[[0, 0, "Location_Code"]]], "check_constraints": []}, "Roles": {"columns": [[0, "Role_Code", "CHAR(15)", 1, null, 1], [1, "Role_Name", "VARCHAR(255)", 0, null, 0], [2, "Role_Description", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["Role_Code"], "foreign_keys": [], "unique_constraints": [[[0, 0, "Role_Code"]]], "check_constraints": []}, "All_Documents": {"columns": [[0, "Document_ID", "INTEGER", 1, null, 1], [1, "Date_Stored", "DATETIME", 0, null, 0], [2, "Document_Type_Code", "CHAR(15)", 1, null, 0], [3, "Document_Name", "CHAR(255)", 0, null, 0], [4, "Document_Description", "CHAR(255)", 0, null, 0], [5, "Other_Details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["Document_ID"], "foreign_keys": [[0, 0, "Ref_Calendar", "Date_Stored", "Calendar_Date", "NO ACTION", "NO ACTION", "NONE"], [1, 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, "Gender_MFU", "CHAR(1)", 1, null, 0], [4, "Date_of_Birth", "DATETIME", 1, null, 0], [5, "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_Locations": {"columns": [[0, "Document_ID", "INTEGER", 1, null, 1], [1, "Location_Code", "CHAR(15)", 1, null, 2], [2, "Date_in_Location_From", "DATETIME", 1, null, 3], [3, "Date_in_Locaton_To", "DATETIME", 0, null, 0]], "primary_keys": ["Document_ID", "Location_Code", "Date_in_Location_From"], "foreign_keys": [[0, 0, "All_Documents", "Document_ID", "Document_ID", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Ref_Calendar", "Date_in_Locaton_To", "Calendar_Date", "NO ACTION", "NO ACTION", "NONE"], [2, 0, "Ref_Calendar", "Date_in_Location_From", "Calendar_Date", "NO ACTION", "NO ACTION", "NONE"], [3, 0, "Ref_Locations", "Location_Code", "Location_Code", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "Document_ID"], [1, 1, "Location_Code"], [2, 2, "Date_in_Location_From"]]], "check_constraints": []}, "Documents_to_be_Destroyed": {"columns": [[0, "Document_ID", "INTEGER", 1, null, 1], [1, "Destruction_Authorised_by_Employee_ID", "INTEGER", 0, null, 0], [2, "Destroyed_by_Employee_ID", "INTEGER", 0, null, 0], [3, "Planned_Destruction_Date", "DATETIME", 0, null, 0], [4, "Actual_Destruction_Date", "DATETIME", 0, null, 0], [5, "Other_Details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["Document_ID"], "foreign_keys": [[0, 0, "All_Documents", "Document_ID", "Document_ID", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Ref_Calendar", "Actual_Destruction_Date", "Calendar_Date", "NO ACTION", "NO ACTION", "NONE"], [2, 0, "Ref_Calendar", "Planned_Destruction_Date", "Calendar_Date", "NO ACTION", "NO ACTION", "NONE"], [3, 0, "Employees", "Destruction_Authorised_by_Employee_ID", "Employee_ID", "NO ACTION", "NO ACTION", "NONE"], [4, 0, "Employees", "Destroyed_by_Employee_ID", "Employee_ID", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}}
CREATE TABLE Ref_Document_Types (
    Document_Type_Code CHAR(15) NOT NULL  PRIMARY KEY,
    Document_Type_Name VARCHAR(255) NOT NULL,
    Document_Type_Description VARCHAR(255) NOT NULL,
    UNIQUE (Document_Type_Code)
);

CREATE TABLE Ref_Calendar (
    Calendar_Date DATETIME NOT NULL  PRIMARY KEY,
    Day_Number INTEGER,
    UNIQUE (Calendar_Date)
);

CREATE TABLE Ref_Locations (
    Location_Code CHAR(15) NOT NULL  PRIMARY KEY,
    Location_Name VARCHAR(255) NOT NULL,
    Location_Description VARCHAR(255) NOT NULL,
    UNIQUE (Location_Code)
);

CREATE TABLE Roles (
    Role_Code CHAR(15) NOT NULL  PRIMARY KEY,
    Role_Name VARCHAR(255),
    Role_Description VARCHAR(255),
    UNIQUE (Role_Code)
);

CREATE TABLE All_Documents (
    Document_ID INTEGER NOT NULL  PRIMARY KEY,
    Date_Stored DATETIME,
    Document_Type_Code CHAR(15) NOT NULL,
    Document_Name CHAR(255),
    Document_Description CHAR(255),
    Other_Details VARCHAR(255),
    FOREIGN KEY (Date_Stored) REFERENCES Ref_Calendar(Calendar_Date),
    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),
    Gender_MFU CHAR(1) NOT NULL,
    Date_of_Birth DATETIME NOT NULL,
    Other_Details VARCHAR(255),
    FOREIGN KEY (Role_Code) REFERENCES Roles(Role_Code)
);

CREATE TABLE Document_Locations (
    Document_ID INTEGER NOT NULL  PRIMARY KEY,
    Location_Code CHAR(15) NOT NULL  PRIMARY KEY,
    Date_in_Location_From DATETIME NOT NULL  PRIMARY KEY,
    Date_in_Locaton_To DATETIME,
    PRIMARY KEY (Document_ID, Location_Code, Date_in_Location_From),
    UNIQUE (Document_ID, Location_Code, Date_in_Location_From),
    FOREIGN KEY (Document_ID) REFERENCES All_Documents(Document_ID),
    FOREIGN KEY (Date_in_Locaton_To) REFERENCES Ref_Calendar(Calendar_Date),
    FOREIGN KEY (Date_in_Location_From) REFERENCES Ref_Calendar(Calendar_Date),
    FOREIGN KEY (Location_Code) REFERENCES Ref_Locations(Location_Code)
);

CREATE TABLE Documents_to_be_Destroyed (
    Document_ID INTEGER NOT NULL  PRIMARY KEY,
    Destruction_Authorised_by_Employee_ID INTEGER,
    Destroyed_by_Employee_ID INTEGER,
    Planned_Destruction_Date DATETIME,
    Actual_Destruction_Date DATETIME,
    Other_Details VARCHAR(255),
    FOREIGN KEY (Document_ID) REFERENCES All_Documents(Document_ID),
    FOREIGN KEY (Actual_Destruction_Date) REFERENCES Ref_Calendar(Calendar_Date),
    FOREIGN KEY (Planned_Destruction_Date) REFERENCES Ref_Calendar(Calendar_Date),
    FOREIGN KEY (Destruction_Authorised_by_Employee_ID) REFERENCES Employees(Employee_ID),
    FOREIGN KEY (Destroyed_by_Employee_ID) REFERENCES Employees(Employee_ID)
);