CREATE DATABASE CPC;

USE CPC;

CREATE TABLE phenotypes (
  phenotype_uid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  unit_id INTEGER UNSIGNED NOT NULL,
  phenotype_name VARCHAR(255) NULL,
  phenotype_short_name VARCHAR(10) NULL,
  description TEXT NULL,
  datatype INTEGER NULL,
  created_on DATETIME NULL,
  updated_on DATETIME NULL,
  PRIMARY KEY(phenotype_uid)
)
TYPE=InnoDB;

CREATE TABLE phenotype_data (
  phenotype_data_uid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  dataset_uid INT NOT NULL,
  phenotype_uid INTEGER UNSIGNED NOT NULL,
  germinate_base_uid INTEGER(8) NOT NULL,
  value VARCHAR(255) NULL,
  recording_date DATETIME NULL,
  created_on DATETIME NULL,
  updated_on DATETIME NULL,
  PRIMARY KEY(phenotype_data_uid)
)
TYPE=InnoDB;

CREATE TABLE units (
  unit_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  unit_name VARCHAR(255) NOT NULL,
  unit_abbreviation VARCHAR(255) NOT NULL,
  unit_description VARCHAR(255) NOT NULL,
  created_on DATETIME NULL,
  updated_on DATETIME NULL,
  PRIMARY KEY(unit_id)
);

CREATE DATABASE Library;

USE Library;

CREATE TABLE artist (
  artistid INTEGER PRIMARY KEY,
  name TEXT NOT NULL 
);

CREATE TABLE cd (
  cdid INTEGER PRIMARY KEY,
  artist INTEGER NOT NULL REFERENCES artist(artistid),
  title TEXT NOT NULL
);

CREATE TABLE track (
  trackid INTEGER PRIMARY KEY,
  cd INTEGER NOT NULL REFERENCES cd(cdid),
  title TEXT NOT NULL
);