---

PRAGMA foreign_keys = ON;

CREATE TABLE books (
    id    INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    title TEXT    NOT NULL,
    year  INTEGER,
    isbn  TEXT
);

CREATE TABLE authors (
    id         INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    name       TEXT    NOT NULL,
    birth_year INTEGER,
    country    TEXT
);

CREATE TABLE book_authors (
    book_id   INTEGER NOT NULL,
    author_id INTEGER NOT NULL,

    PRIMARY KEY (book_id, author_id),

    FOREIGN KEY (book_id)   REFERENCES books(id)   ON DELETE CASCADE,
    FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE
);