{"Addresses": {"columns": [[0, "address_id", "INTEGER", 1, null, 1], [1, "line_1", "VARCHAR(80)", 0, null, 0], [2, "line_2", "VARCHAR(80)", 0, null, 0], [3, "city", "VARCHAR(50)", 0, null, 0], [4, "zip_postcode", "CHAR(20)", 0, null, 0], [5, "state_province_county", "VARCHAR(50)", 0, null, 0], [6, "country", "VARCHAR(50)", 0, null, 0]], "primary_keys": ["address_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "People": {"columns": [[0, "person_id", "INTEGER", 1, null, 1], [1, "first_name", "VARCHAR(255)", 0, null, 0], [2, "middle_name", "VARCHAR(255)", 0, null, 0], [3, "last_name", "VARCHAR(255)", 0, null, 0], [4, "cell_mobile_number", "VARCHAR(40)", 0, null, 0], [5, "email_address", "VARCHAR(40)", 0, null, 0], [6, "login_name", "VARCHAR(40)", 0, null, 0], [7, "password", "VARCHAR(40)", 0, null, 0]], "primary_keys": ["person_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Students": {"columns": [[0, "student_id", "INTEGER", 1, null, 1], [1, "student_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["student_id"], "foreign_keys": [[0, 0, "People", "student_id", "person_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Courses": {"columns": [[0, "course_id", "VARCHAR(100)", 1, null, 1], [1, "course_name", "VARCHAR(120)", 0, null, 0], [2, "course_description", "VARCHAR(255)", 0, null, 0], [3, "other_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["course_id"], "foreign_keys": [], "unique_constraints": [[[0, 0, "course_id"]]], "check_constraints": []}, "People_Addresses": {"columns": [[0, "person_address_id", "INTEGER", 1, null, 1], [1, "person_id", "INTEGER", 1, null, 0], [2, "address_id", "INTEGER", 1, null, 0], [3, "date_from", "DATETIME", 0, null, 0], [4, "date_to", "DATETIME", 0, null, 0]], "primary_keys": ["person_address_id"], "foreign_keys": [[0, 0, "Addresses", "address_id", "address_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "People", "person_id", "person_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Student_Course_Registrations": {"columns": [[0, "student_id", "INTEGER", 1, null, 1], [1, "course_id", "INTEGER", 1, null, 2], [2, "registration_date", "DATETIME", 1, null, 0]], "primary_keys": ["student_id", "course_id"], "foreign_keys": [[0, 0, "Courses", "course_id", "course_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Students", "student_id", "student_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "student_id"], [1, 1, "course_id"]]], "check_constraints": []}, "Student_Course_Attendance": {"columns": [[0, "student_id", "INTEGER", 1, null, 1], [1, "course_id", "INTEGER", 1, null, 2], [2, "date_of_attendance", "DATETIME", 1, null, 0]], "primary_keys": ["student_id", "course_id"], "foreign_keys": [[0, 0, "Student_Course_Registrations", "student_id", "student_id", "NO ACTION", "NO ACTION", "NONE"], [0, 1, "Student_Course_Registrations", "course_id", "course_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "student_id"], [1, 1, "course_id"]]], "check_constraints": []}, "Candidates": {"columns": [[0, "candidate_id", "INTEGER", 1, null, 1], [1, "candidate_details", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["candidate_id"], "foreign_keys": [[0, 0, "People", "candidate_id", "person_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Candidate_Assessments": {"columns": [[0, "candidate_id", "INTEGER", 1, null, 1], [1, "qualification", "CHAR(15)", 1, null, 2], [2, "assessment_date", "DATETIME", 1, null, 0], [3, "asessment_outcome_code", "CHAR(15)", 1, null, 0]], "primary_keys": ["candidate_id", "qualification"], "foreign_keys": [[0, 0, "Candidates", "candidate_id", "candidate_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "candidate_id"], [1, 1, "qualification"]]], "check_constraints": []}}
CREATE TABLE Addresses (
    address_id INTEGER NOT NULL  PRIMARY KEY,
    line_1 VARCHAR(80),
    line_2 VARCHAR(80),
    city VARCHAR(50),
    zip_postcode CHAR(20),
    state_province_county VARCHAR(50),
    country VARCHAR(50)
);

CREATE TABLE People (
    person_id INTEGER NOT NULL  PRIMARY KEY,
    first_name VARCHAR(255),
    middle_name VARCHAR(255),
    last_name VARCHAR(255),
    cell_mobile_number VARCHAR(40),
    email_address VARCHAR(40),
    login_name VARCHAR(40),
    password VARCHAR(40)
);

CREATE TABLE Students (
    student_id INTEGER NOT NULL  PRIMARY KEY,
    student_details VARCHAR(255),
    FOREIGN KEY (student_id) REFERENCES People(person_id)
);

CREATE TABLE Courses (
    course_id VARCHAR(100) NOT NULL  PRIMARY KEY,
    course_name VARCHAR(120),
    course_description VARCHAR(255),
    other_details VARCHAR(255),
    UNIQUE (course_id)
);

CREATE TABLE People_Addresses (
    person_address_id INTEGER NOT NULL  PRIMARY KEY,
    person_id INTEGER NOT NULL,
    address_id INTEGER NOT NULL,
    date_from DATETIME,
    date_to DATETIME,
    FOREIGN KEY (address_id) REFERENCES Addresses(address_id),
    FOREIGN KEY (person_id) REFERENCES People(person_id)
);

CREATE TABLE Student_Course_Registrations (
    student_id INTEGER NOT NULL  PRIMARY KEY,
    course_id INTEGER NOT NULL  PRIMARY KEY,
    registration_date DATETIME NOT NULL,
    PRIMARY KEY (student_id, course_id),
    UNIQUE (student_id, course_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id),
    FOREIGN KEY (student_id) REFERENCES Students(student_id)
);

CREATE TABLE Student_Course_Attendance (
    student_id INTEGER NOT NULL  PRIMARY KEY,
    course_id INTEGER NOT NULL  PRIMARY KEY,
    date_of_attendance DATETIME NOT NULL,
    PRIMARY KEY (student_id, course_id),
    UNIQUE (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES Student_Course_Registrations(student_id),
    FOREIGN KEY (course_id) REFERENCES Student_Course_Registrations(course_id)
);

CREATE TABLE Candidates (
    candidate_id INTEGER NOT NULL  PRIMARY KEY,
    candidate_details VARCHAR(255),
    FOREIGN KEY (candidate_id) REFERENCES People(person_id)
);

CREATE TABLE Candidate_Assessments (
    candidate_id INTEGER NOT NULL  PRIMARY KEY,
    qualification CHAR(15) NOT NULL  PRIMARY KEY,
    assessment_date DATETIME NOT NULL,
    asessment_outcome_code CHAR(15) NOT NULL,
    PRIMARY KEY (candidate_id, qualification),
    UNIQUE (candidate_id, qualification),
    FOREIGN KEY (candidate_id) REFERENCES Candidates(candidate_id)
);