/*************************************************/
/***                    BIO                    ***/
/*************************************************/

CREATE TABLE BIO_Population
(
  ID          VARCHAR(50)  NOT NULL,
  name        VARCHAR(200) NOT NULL,
  organism    VARCHAR(50)  NOT NULL,
  description TEXT,
  comment     TEXT,

  PRIMARY KEY(ID),

  FOREIGN KEY(organism) REFERENCES VOC_organism(entry)
  ON DELETE RESTRICT ON UPDATE CASCADE
);


CREATE TABLE BIO_Source
(
  ID            VARCHAR(50)  NOT NULL,
  name          VARCHAR(200) NOT NULL,
  population_ID VARCHAR(50)  NOT NULL,
  description   TEXT,
  comment       TEXT,
 
  PRIMARY KEY(ID),

  FOREIGN KEY(population_ID) REFERENCES BIO_Population(ID)
  ON DELETE RESTRICT ON UPDATE CASCADE
);


CREATE TABLE BIO_BioSample
(
  ID                 VARCHAR(50)  NOT NULL,
  external_ID        VARCHAR(50),
  bar_code           VARCHAR(200),
  name               VARCHAR(200),
  source_ID          VARCHAR(50)  NOT NULL,
  experiment_ID      VARCHAR(50)  NOT NULL,
  sample_no          INT,
  class_label        VARCHAR(50),
  sample_type        VARCHAR(50),
  quantity           INT,
  storage_conditions TEXT,
  description        TEXT,
  comment            TEXT,

  PRIMARY KEY(ID),

  FOREIGN KEY(source_ID) REFERENCES BIO_Source(ID)
  ON DELETE RESTRICT ON UPDATE CASCADE,

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