DDL.sql

CREATE TABLE users(
  user_id    VARCHAR(15) PRIMARY KEY,
  pw    VARCHAR(15) NOT NULL,
  nickname    VARCHAR(10) NOT NULL,
  isAdmin    BOOLEAN DEFAULT FALSE
);

  CREATE TABLE coupon(
  coupon_id    INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  percent    INT NOT NULL,
  owner_id    VARCHAR(15) NOT NULL,
  available    VARCHAR(10) CHECK (available IN ('USABLE', 'USED', 'EXPIRED')) NOT NULL,
  issue_date    DATE NOT NULL,
  expire_date    DATE NOT NULL,
  FOREIGN KEY (owner_id) REFERENCES users(user_id) ON DELETE CASCADE
);

CREATE TABLE item(
  item_id    INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  item_name    VARCHAR(20) NOT NULL,
  category    VARCHAR(15) CHECK (category IN ('ELECTRONICS', 'BOOKS', 'HOME', 'CLOTHING', 'SPORTINGGOODS', 'OTHERS')) NOT NULL,
  condition    VARCHAR(10) CHECK (condition IN ('NEW', 'LIKE_NEW', 'GOOD', 'ACCEPTABLE')) NOT NULL,
  details    VARCHAR(200),
  start_price    NUMERIC(10, 2),
  buy_now_price    NUMERIC(10, 2) NOT NULL,
  posted_date    TIMESTAMP NOT NULL,
  closing_date    TIMESTAMP NOT NULL,
  bidding_status    VARCHAR(10) CHECK (bidding_status IN ('OPENED', 'TENDERED', 'CLOSED')) NOT NULL,
  seller_id    VARCHAR(15) NOT NULL,
  seller_coupon_id    INT,
  FOREIGN KEY (seller_id) REFERENCES users(user_id) ON DELETE CASCADE,
  FOREIGN KEY (seller_coupon_id) REFERENCES coupon(coupon_id) ON DELETE SET NULL
  );

  CREATE TABLE bid(
    bid_id    INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    item_id    INT NOT NULL,
    bidder_id    VARCHAR(15) NOT NULL,
    bid_price    NUMERIC(10, 2) NOT NULL,
    bid_date    TIMESTAMP NOT NULL,
    prior_bidder_id    VARCHAR(15) NOT NULL,
    prior_price    NUMERIC(10, 2) DEFAULT 0,
    selected    BOOLEAN DEFAULT FALSE,
    selected_date    TIMESTAMP,
    FOREIGN KEY (item_id) REFERENCES item(item_id) ON DELETE CASCADE,
    FOREIGN KEY (bidder_id) REFERENCES users(user_id) ON DELETE CASCADE
  );

  CREATE TABLE account(
    account_id    INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id    VARCHAR(15) NOT NULL,
    amount    NUMERIC(10,2) NOT NULL,
    source    VARCHAR(20) NOT NULL,
    changed_time    TIMESTAMP NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
  );

  CREATE TABLE commission(
    commission_id    INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    bid_id    INT NOT NULL,
    tendered_date    TIMESTAMP NOT NULL,
    coupon_id    INT,
    commission_amount    INT,
    FOREIGN KEY (bid_id) REFERENCES bid(bid_id),
    FOREIGN KEY (coupon_id) REFERENCES coupon(coupon_id) ON DELETE SET NULL
  );