schema.sql

-- code added by team member #3

CREATE TABLE IF NOT EXISTS game (
  id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  multiplayer text NOT NULL,
  last_played_at date NOT NULL,
  item_id integer REFERENCES item(id) ON DELETE CASCADE
);


CREATE TABLE IF NOT EXISTS author (
  id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  first_name text NOT NULL,
  last_name text NOT NULL
  author_id INT NULL
  FOREIGN KEY (author_id) REFERENCES item(id)
);


-- code below added by team member #2
CREATE TABLE Genre (
  id SERIAL NOT NULL,
  name VARCHAR(50) NOT NULL,
  PRIMARY KEY (id)
  genre_id INT NULL
  FOREIGN KEY (genre_id) REFERENCES item(id)
);


CREATE TABLE MusicAlbum (
  id SERIAL NOT NULL,
  on_spotify BOOLEAN NOT NULL,
  genre_id INT NULL,
  FOREIGN KEY (genre_id) REFERENCES item(id),
  PRIMARY KEY (id)
);
-- Add code above
-- Code below done by Team member #1
CREATE TABLE item (
  id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  publish_date DATE NOT NULL,
  archived BOOLEAN NOT NULL    
);


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


CREATE TABLE book (
  id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ,
  publisher VARCHAR(255) NOT NULL,
  cover_state VARCHAR(25) NOT NULL  
);


ALTER TABLE book ADD CONSTRAINT fk_item FOREIGN KEY (id) REFERENCES item(id) ON DELETE CASCADE;
ALTER TABLE label ADD CONSTRAINT fk_item FOREIGN KEY (id) REFERENCES book(id) ON DELETE CASCADE;
ALTER TABLE item ADD CONSTRAINT fk_item FOREIGN KEY (id) REFERENCES book(id) ON DELETE CASCADE;
ALTER TABLE item ADD CONSTRAINT fk_item FOREIGN KEY (id) REFERENCES game(id) ON DELETE CASCADE;
ALTER TABLE item ADD CONSTRAINT fk_item FOREIGN KEY (id) REFERENCES author(id) ON DELETE CASCADE;