/*************************************************/
/***             SAMPLE PREPARATION            ***/
/*************************************************/

CREATE TABLE SMP_Sample
(
  ID                 VARCHAR(50)  NOT NULL,
  name               VARCHAR(200),
  biosample_ID       VARCHAR(50),
  experiment_ID      VARCHAR(50)  NOT NULL,
  ready_date         DATE,
  ready_time         TIME,
  expiry_date        DATE         NOT NULL,
  quantity           INT,
  storage_conditions TEXT,
  part_of            VARCHAR(50),
  comment            TEXT,

  PRIMARY KEY(ID),

  FOREIGN KEY(biosample_ID) REFERENCES BIO_BioSample(ID)
  ON DELETE RESTRICT ON UPDATE CASCADE,

  FOREIGN KEY(experiment_ID) REFERENCES ADM_Experiment(ID)
  ON DELETE RESTRICT ON UPDATE CASCADE,

  FOREIGN KEY(part_of) REFERENCES SMP_Sample(ID)
  ON DELETE RESTRICT ON UPDATE CASCADE
);