Notes

CREATE TABLE Books (
bid INT AUTO_INCREMENT,
title VARCHAR(100),
publication_year INT,
reading INT, -- 0: reading, 1: read, -1: will read
CONSTRAINT BOOKS_PK PRIMARY KEY (bid)
);

CREATE TABLE Authors (
aid INT AUTO_INCREMENT,
fname VARCHAR(20),
lname VARCHAR(20),
CONSTRAINT AUTHORS_PK PRIMARY KEY (aid)
);

CREATE TABLE Genres (
gid INT AUTO_INCREMENT,
name VARCHAR(30),
CONSTRAINT GENRES_PK PRIMARY KEY (gid)
);

CREATE TABLE Writes (
aid INT,
bid INT,
CONSTRAINT WRITES_PK PRIMARY KEY (aid,bid),
FOREIGN KEY (aid) REFERENCES Authors (aid)
ON DELETE CASCADE,
FOREIGN KEY (bid) REFERENCES Books (bid)
ON DELETE CASCADE
);

CREATE TABLE Is_of_genre (
bid INT,
gid INT,
CONSTRAINT IS_OF_GENRE_PK PRIMARY KEY (bid,gid),
FOREIGN KEY (bid) REFERENCES Books (bid)
ON DELETE CASCADE,
FOREIGN KEY (gid) REFERENCES Genres (gid)
ON DELETE CASCADE
);

INSERT INTO Authors (aid, fname, lname)
VALUES (1,'George','Orwell');

INSERT INTO Books (bid, title, publication_year, reading)
VALUES (1,'1984',1949,1);

INSERT INTO Genres (gid, name)
VALUES (1, 'Dystopian');
INSERT INTO Genres (gid, name)
VALUES (2, 'Political Fiction');
INSERT INTO Genres (gid, name)
VALUES (3, 'Sci-fi');

INSERT INTO Writes (aid, bid)
VALUES (1,1);

INSERT INTO IS_OF_GENRE (bid, gid)
VALUES (1,1);
INSERT INTO IS_OF_GENRE (bid, gid)
VALUES (1,2);
INSERT INTO IS_OF_GENRE (bid, gid)
VALUES (1,3);