drop table number_plate;
drop table batch;
drop table bid;
drop table participant;
drop table auction;

CREATE TABLE auction (
  auction_id SERIAL,
  start_date date,
  end_date date,
  CONSTRAINT auction_id_pk PRIMARY KEY (auction_id)
);

  CREATE TABLE participant (
    participant_id SERIAL,
    name varchar(20),
    surname varchar(20),
    address varchar(70),
    DOB date,
    CONSTRAINT participant_id_pk PRIMARY KEY (participant_id)
  );

  CREATE TABLE bid (
    bid_id SERIAL,
    participant_id_three integer,
    auction_id_two integer,
    bid_amount integer,
    winner_found varchar(4),
    CONSTRAINT bid_id_pk PRIMARY KEY (bid_id),
  	CONSTRAINT participant_id_three_fk
  	FOREIGN KEY (participant_id_three)
  	REFERENCES participant (participant_id),
    CONSTRAINT auction_id_two_fk
    FOREIGN KEY (auction_id_two)
    REFERENCES auction (auction_id)
  );

CREATE TABLE batch (
    batch_id SERIAL,
    auction_id_three integer,
    start_bid integer,
    CONSTRAINT batch_id_pk PRIMARY KEY (batch_id),
  	CONSTRAINT auction_id_three_fk
  	FOREIGN KEY (auction_id_three)
  	REFERENCES auction (auction_id)
    );


  CREATE TABLE number_plate (
      number_plate_id SERIAL,
      batch_id_three integer,
      plate_number varchar(8),
      CONSTRAINT number_plate_id_pk PRIMARY KEY (number_plate_id),
    	CONSTRAINT batch_id_three_fk
    	FOREIGN KEY (batch_id_three)
    	REFERENCES batch (batch_id)
      );
