{"Course_Authors_and_Tutors": {"columns": [[0, "author_id", "INTEGER", 0, null, 1], [1, "author_tutor_ATB", "VARCHAR(3)", 0, null, 0], [2, "login_name", "VARCHAR(40)", 0, null, 0], [3, "password", "VARCHAR(40)", 0, null, 0], [4, "personal_name", "VARCHAR(80)", 0, null, 0], [5, "middle_name", "VARCHAR(80)", 0, null, 0], [6, "family_name", "VARCHAR(80)", 0, null, 0], [7, "gender_mf", "VARCHAR(1)", 0, null, 0], [8, "address_line_1", "VARCHAR(80)", 0, null, 0]], "primary_keys": ["author_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Students": {"columns": [[0, "student_id", "INTEGER", 0, null, 1], [1, "date_of_registration", "DATETIME", 0, null, 0], [2, "date_of_latest_logon", "DATETIME", 0, null, 0], [3, "login_name", "VARCHAR(40)", 0, null, 0], [4, "password", "VARCHAR(10)", 0, null, 0], [5, "personal_name", "VARCHAR(40)", 0, null, 0], [6, "middle_name", "VARCHAR(40)", 0, null, 0], [7, "family_name", "VARCHAR(40)", 0, null, 0]], "primary_keys": ["student_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Subjects": {"columns": [[0, "subject_id", "INTEGER", 0, null, 1], [1, "subject_name", "VARCHAR(120)", 0, null, 0]], "primary_keys": ["subject_id"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Courses": {"columns": [[0, "course_id", "INTEGER", 0, null, 1], [1, "author_id", "INTEGER", 1, null, 0], [2, "subject_id", "INTEGER", 1, null, 0], [3, "course_name", "VARCHAR(120)", 0, null, 0], [4, "course_description", "VARCHAR(255)", 0, null, 0]], "primary_keys": ["course_id"], "foreign_keys": [[0, 0, "Subjects", "subject_id", "subject_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Course_Authors_and_Tutors", "author_id", "author_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Student_Course_Enrolment": {"columns": [[0, "registration_id", "INTEGER", 0, null, 1], [1, "student_id", "INTEGER", 1, null, 0], [2, "course_id", "INTEGER", 1, null, 0], [3, "date_of_enrolment", "DATETIME", 1, null, 0], [4, "date_of_completion", "DATETIME", 1, null, 0]], "primary_keys": ["registration_id"], "foreign_keys": [[0, 0, "Students", "student_id", "student_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Courses", "course_id", "course_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Student_Tests_Taken": {"columns": [[0, "registration_id", "INTEGER", 1, null, 0], [1, "date_test_taken", "DATETIME", 1, null, 0], [2, "test_result", "VARCHAR(255)", 0, null, 0]], "primary_keys": [], "foreign_keys": [[0, 0, "Student_Course_Enrolment", "registration_id", "registration_id", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}}
CREATE TABLE Course_Authors_and_Tutors (
    author_id INTEGER   PRIMARY KEY,
    author_tutor_ATB VARCHAR(3),
    login_name VARCHAR(40),
    password VARCHAR(40),
    personal_name VARCHAR(80),
    middle_name VARCHAR(80),
    family_name VARCHAR(80),
    gender_mf VARCHAR(1),
    address_line_1 VARCHAR(80)
);

CREATE TABLE Students (
    student_id INTEGER   PRIMARY KEY,
    date_of_registration DATETIME,
    date_of_latest_logon DATETIME,
    login_name VARCHAR(40),
    password VARCHAR(10),
    personal_name VARCHAR(40),
    middle_name VARCHAR(40),
    family_name VARCHAR(40)
);

CREATE TABLE Subjects (
    subject_id INTEGER   PRIMARY KEY,
    subject_name VARCHAR(120)
);

CREATE TABLE Courses (
    course_id INTEGER   PRIMARY KEY,
    author_id INTEGER NOT NULL,
    subject_id INTEGER NOT NULL,
    course_name VARCHAR(120),
    course_description VARCHAR(255),
    FOREIGN KEY (subject_id) REFERENCES Subjects(subject_id),
    FOREIGN KEY (author_id) REFERENCES Course_Authors_and_Tutors(author_id)
);

CREATE TABLE Student_Course_Enrolment (
    registration_id INTEGER   PRIMARY KEY,
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    date_of_enrolment DATETIME NOT NULL,
    date_of_completion DATETIME NOT NULL,
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

CREATE TABLE Student_Tests_Taken (
    registration_id INTEGER NOT NULL,
    date_test_taken DATETIME NOT NULL,
    test_result VARCHAR(255),
    FOREIGN KEY (registration_id) REFERENCES Student_Course_Enrolment(registration_id)
);