schema.sql

CREATE TABLE Genre
(
  id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE Source
(
  id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE Author
(
  id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  first_name VARCHAR(100) NOT NULL
  last_name VARCHAR(100) NOT NULL
);

CREATE TABLE Label
(
  id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  title VARCHAR(100) NOT NULL
  color VARCHAR(100) NOT NULL
);

CREATE TABLE Item
(
  id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  genre_id INT
  author_id INT
  source_id INT
  label_id INT
  publish_date DATE NOT NULL
  archived BOOLEAN NOT NULL
  FOREIGN KEY (author_id) REFERENCES Author(id)
  FOREIGN KEY (label_id) REFERENCES Label(id)
  FOREIGN KEY (genre_id) REFERENCES Genre(id)
  FOREIGN KEY (source_id) REFERENCES Source(id)
);

CREATE TABLE Book
(
  publisher VARCHAR(200) NOT NULL
  cover_state VARCHAR(100) NOT NULL
) INHERITS (Item);

CREATE TABLE MusicAlbum
(
  on_spotify BOOLEAN NOT NULL
) INHERITS (Item);

CREATE TABLE Movie
(
  silent BOOLEAN
) INHERITS (Item);

CREATE TABLE Game
(
  multiplayer BOOLEAN NOT NULL, 
  last_played_at DATE NOT NULL,
) INHERITS (Item);