-- Entity: Category
CREATE TABLE category ( 
  cat_id        INTEGER NOT NULL, 
  name          VARCHAR(20) NOT NULL,
  PRIMARY KEY (cat_id));

-- Entity: Author
CREATE TABLE author (
  a_id        INTEGER NOT NULL,
  firstname   VARCHAR(20) NOT NULL,
  lastname    VARCHAR (20) NOT NULL,
  PRIMARY KEY (a_id));

-- Entity: Publisher
CREATE TABLE publisher (
  publ_id     INTEGER NOT NULL,
  name        VARCHAR(30) NOT NULL,
  address     VARCHAR(50),
  PRIMARY KEY (publ_id));

-- Entity: Book
CREATE TABLE book (
  isbn        VARCHAR(30) NOT NULL,
  title       VARCHAR(50) NOT NULL,
  publ_id     INTEGER NOT NULL,
  cat_id      INTEGER NOT NULL,
  price       FLOAT NOT NULL,
  stock       INTEGER NOT NULL,
  PRIMARY KEY (isbn),
  FOREIGN KEY (cat_id) REFERENCES category,
  FOREIGN KEY (publ_id) REFERENCES publisher);

-- Beziehung: writtenBy
CREATE TABLE book_author (
  isbn    VARCHAR(30) NOT NULL,
  a_id    INTEGER NOT NULL,
  PRIMARY KEY (isbn, a_id),
  FOREIGN KEY (isbn) REFERENCES book,
  FOREIGN KEY (a_id) REFERENCES author);

-- Entity: Customer
CREATE TABLE customer (
  cust_id    INTEGER NOT NULL,
  name       VARCHAR(30) NOT NULL,
  city       VARCHAR(30) NOT NULL,
  zipcode    INTEGER NOT NULL,
  street     VARCHAR(30) NOT NULL,
  PRIMARY KEY (cust_id));

-- Entity: BookOrder
CREATE TABLE book_order (
  order_id  INTEGER NOT NULL,
  cust_id   INTEGER NOT NULL,
  o_date    DATE NOT NULL,
  status    INTEGER NOT NULL,
  PRIMARY KEY (order_id),
  FOREIGN KEY (cust_id) REFERENCES customer);

-- Beziehung: contains
CREATE TABLE order_item (
  order_id   INTEGER NOT NULL,
  isbn       VARCHAR(30) NOT NULL,
  num        INTEGER NOT NULL,
  PRIMARY KEY (order_id, isbn),
  FOREIGN KEY (order_id) REFERENCES book_order,
  FOREIGN KEY (isbn) REFERENCES book);
  
CREATE SEQUENCE order_seq;