CREATE TABLE teacher
(
    teacher_id SERIAL PRIMARY KEY,
    login CHARACTER VARYING(50),
    password CHARACTER VARYING(50)
);
CREATE TABLE test
(
    test_id SERIAL PRIMARY KEY,
    name CHARACTER VARYING(200),
    teacher_id INTEGER REFERENCES teacher (teacher_id) ON DELETE CASCADE
);
CREATE TABLE test_execution
(
    execution_id SERIAL PRIMARY KEY,
    test_id INTEGER REFERENCES test (test_id) ON DELETE CASCADE,
    date TIMESTAMP,
    running BOOLEAN
);
CREATE TABLE question
(
    question_id SERIAL PRIMARY KEY,
    test_id INTEGER REFERENCES test (test_id) ON DELETE CASCADE,
    type INTEGER,
    text CHARACTER VARYING(200),
    image BYTEA,
    answer1 CHARACTER VARYING(500),
    answer2 CHARACTER VARYING(500),
    answer3 CHARACTER VARYING(500),
    answer4 CHARACTER VARYING(500),
    right_answer INTEGER
);
CREATE TABLE result
(
    teacher_id SERIAL PRIMARY KEY,
    student_name CHARACTER VARYING(200),
    execution_id INTEGER REFERENCES test_execution (execution_id) ON DELETE CASCADE,
    question_id INTEGER REFERENCES question (question_id) ON DELETE CASCADE,
    answer INTEGER
);