{"Ref_Address_Types": {"columns": [[0, "address_type_code", "VARCHAR(15)", 0, null, 1], [1, "address_type_description", "VARCHAR(80)", 0, null, 0]], "primary_keys": ["address_type_code"], "foreign_keys": [], "unique_constraints": [[[0, 0, "address_type_code"]]], "check_constraints": []}, "Ref_Detention_Type": {"columns": [[0, "detention_type_code", "VARCHAR(10)", 0, null, 1], [1, "detention_type_description", "VARCHAR(80)", 0, null, 0]], "primary_keys": ["detention_type_code"], "foreign_keys": [], "unique_constraints": [[[0, 0, "detention_type_code"]]], "check_constraints": []}, "Ref_Incident_Type": {"columns": [[0, "incident_type_code", "VARCHAR(10)", 0, null, 1], [1, "incident_type_description", "VARCHAR(80)", 0, null, 0]], "primary_keys": ["incident_type_code"], "foreign_keys": [], "unique_constraints": [[[0, 0, "incident_type_code"]]], "check_constraints": []}, "Addresses": {"columns": [[0, "address_id", "INTEGER", 0, null, 1], [1, "line_1", "VARCHAR(120)", 0, null, 0], [2, "line_2", "VARCHAR(120)", 0, null, 0], [3, "line_3", "VARCHAR(120)", 0, null, 0], [4, "city", "VARCHAR(80)", 0, null, 0], [5, "zip_postcode", "VARCHAR(20)", 0, null, 0], [6, "state_province_county", "VARCHAR(50)", 0, null, 0], [7, "country", "VARCHAR(50)", 0, null, 0], [8, "other_address_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["address_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Students": {"columns": [[0, "student_id", "INTEGER", 0, null, 1], [1, "address_id", "INTEGER", 1, null, 0], [2, "first_name", "VARCHAR(80)", 0, null, 0], [3, "middle_name", "VARCHAR(40)", 0, null, 0], [4, "last_name", "VARCHAR(40)", 0, null, 0], [5, "cell_mobile_number", "VARCHAR(40)", 0, null, 0], [6, "email_address", "VARCHAR(40)", 0, null, 0], [7, "date_first_rental", "DATETIME", 0, null, 0], [8, "date_left_university", "DATETIME", 0, null, 0], [9, "other_student_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["student_id"], "foreign_keys": [[0, 0, "Addresses", "address_id", "address_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Teachers": {"columns": [[0, "teacher_id", "INTEGER", 0, null, 1], [1, "address_id", "INTEGER", 1, null, 0], [2, "first_name", "VARCHAR(80)", 0, null, 0], [3, "middle_name", "VARCHAR(80)", 0, null, 0], [4, "last_name", "VARCHAR(80)", 0, null, 0], [5, "gender", "VARCHAR(1)", 0, null, 0], [6, "cell_mobile_number", "VARCHAR(40)", 0, null, 0], [7, "email_address", "VARCHAR(40)", 0, null, 0], [8, "other_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["teacher_id"], "foreign_keys": [[0, 0, "Addresses", "address_id", "address_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Assessment_Notes": {"columns": [[0, "notes_id", "INTEGER", 1, null, 0], [1, "student_id", "INTEGER", 0, null, 0], [2, "teacher_id", "INTEGER", 1, null, 0], [3, "date_of_notes", "DATETIME", 0, null, 0], [4, "text_of_notes", "VARCHAR(255)", 0, null, 0], [5, "other_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": [], "foreign_keys": [[0, 0, "Teachers", "teacher_id", "teacher_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Students", "student_id", "student_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Behavior_Incident": {"columns": [[0, "incident_id", "INTEGER", 0, null, 1], [1, "incident_type_code", "VARCHAR(10)", 1, null, 0], [2, "student_id", "INTEGER", 1, null, 0], [3, "date_incident_start", "DATETIME", 0, null, 0], [4, "date_incident_end", "DATETIME", 0, null, 0], [5, "incident_summary", "VARCHAR(255)", 0, null, 0], [6, "recommendations", "VARCHAR(255)", 0, null, 0], [7, "other_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["incident_id"], "foreign_keys": [[0, 0, "Students", "student_id", "student_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Ref_Incident_Type", "incident_type_code", "incident_type_code", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Detention": {"columns": [[0, "detention_id", "INTEGER", 0, null, 1], [1, "detention_type_code", "VARCHAR(10)", 1, null, 0], [2, "teacher_id", "INTEGER", 0, null, 0], [3, "datetime_detention_start", "DATETIME", 0, null, 0], [4, "datetime_detention_end", "DATETIME", 0, null, 0], [5, "detention_summary", "VARCHAR(255)", 0, null, 0], [6, "other_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["detention_id"], "foreign_keys": [[0, 0, "Teachers", "teacher_id", "teacher_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Ref_Detention_Type", "detention_type_code", "detention_type_code", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Student_Addresses": {"columns": [[0, "student_id", "INTEGER", 1, null, 0], [1, "address_id", "INTEGER", 1, null, 0], [2, "date_address_from", "DATETIME", 1, null, 0], [3, "date_address_to", "DATETIME", 0, null, 0], [4, "monthly_rental", "DECIMAL(19,4)", 0, null, 0], [5, "other_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": [], "foreign_keys": [[0, 0, "Students", "student_id", "student_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Addresses", "address_id", "address_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Students_in_Detention": {"columns": [[0, "student_id", "INTEGER", 1, null, 0], [1, "detention_id", "INTEGER", 1, null, 0], [2, "incident_id", "INTEGER", 1, null, 0]], "primary_keys": [], "foreign_keys": [[0, 0, "Students", "student_id", "student_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Detention", "detention_id", "detention_id", "NO ACTION", "NO ACTION", "NONE"], [2, 0, "Behavior_Incident", "incident_id", "incident_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}}
CREATE TABLE Ref_Address_Types (
    address_type_code VARCHAR(15)   PRIMARY KEY,
    address_type_description VARCHAR(80),
    UNIQUE (address_type_code)
);

CREATE TABLE Ref_Detention_Type (
    detention_type_code VARCHAR(10)   PRIMARY KEY,
    detention_type_description VARCHAR(80),
    UNIQUE (detention_type_code)
);

CREATE TABLE Ref_Incident_Type (
    incident_type_code VARCHAR(10)   PRIMARY KEY,
    incident_type_description VARCHAR(80),
    UNIQUE (incident_type_code)
);

CREATE TABLE Addresses (
    address_id INTEGER   PRIMARY KEY,
    line_1 VARCHAR(120),
    line_2 VARCHAR(120),
    line_3 VARCHAR(120),
    city VARCHAR(80),
    zip_postcode VARCHAR(20),
    state_province_county VARCHAR(50),
    country VARCHAR(50),
    other_address_details VARCHAR(255)
);

CREATE TABLE Students (
    student_id INTEGER   PRIMARY KEY,
    address_id INTEGER NOT NULL,
    first_name VARCHAR(80),
    middle_name VARCHAR(40),
    last_name VARCHAR(40),
    cell_mobile_number VARCHAR(40),
    email_address VARCHAR(40),
    date_first_rental DATETIME,
    date_left_university DATETIME,
    other_student_details VARCHAR(255),
    FOREIGN KEY (address_id) REFERENCES Addresses(address_id)
);

CREATE TABLE Teachers (
    teacher_id INTEGER   PRIMARY KEY,
    address_id INTEGER NOT NULL,
    first_name VARCHAR(80),
    middle_name VARCHAR(80),
    last_name VARCHAR(80),
    gender VARCHAR(1),
    cell_mobile_number VARCHAR(40),
    email_address VARCHAR(40),
    other_details VARCHAR(255),
    FOREIGN KEY (address_id) REFERENCES Addresses(address_id)
);

CREATE TABLE Assessment_Notes (
    notes_id INTEGER NOT NULL,
    student_id INTEGER,
    teacher_id INTEGER NOT NULL,
    date_of_notes DATETIME,
    text_of_notes VARCHAR(255),
    other_details VARCHAR(255),
    FOREIGN KEY (teacher_id) REFERENCES Teachers(teacher_id),
    FOREIGN KEY (student_id) REFERENCES Students(student_id)
);

CREATE TABLE Behavior_Incident (
    incident_id INTEGER   PRIMARY KEY,
    incident_type_code VARCHAR(10) NOT NULL,
    student_id INTEGER NOT NULL,
    date_incident_start DATETIME,
    date_incident_end DATETIME,
    incident_summary VARCHAR(255),
    recommendations VARCHAR(255),
    other_details VARCHAR(255),
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (incident_type_code) REFERENCES Ref_Incident_Type(incident_type_code)
);

CREATE TABLE Detention (
    detention_id INTEGER   PRIMARY KEY,
    detention_type_code VARCHAR(10) NOT NULL,
    teacher_id INTEGER,
    datetime_detention_start DATETIME,
    datetime_detention_end DATETIME,
    detention_summary VARCHAR(255),
    other_details VARCHAR(255),
    FOREIGN KEY (teacher_id) REFERENCES Teachers(teacher_id),
    FOREIGN KEY (detention_type_code) REFERENCES Ref_Detention_Type(detention_type_code)
);

CREATE TABLE Student_Addresses (
    student_id INTEGER NOT NULL,
    address_id INTEGER NOT NULL,
    date_address_from DATETIME NOT NULL,
    date_address_to DATETIME,
    monthly_rental DECIMAL(19,4),
    other_details VARCHAR(255),
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (address_id) REFERENCES Addresses(address_id)
);

CREATE TABLE Students_in_Detention (
    student_id INTEGER NOT NULL,
    detention_id INTEGER NOT NULL,
    incident_id INTEGER NOT NULL,
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (detention_id) REFERENCES Detention(detention_id),
    FOREIGN KEY (incident_id) REFERENCES Behavior_Incident(incident_id)
);