SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `Microarray` ;
USE `Microarray` ;

-- -----------------------------------------------------
-- Table `Microarray`.`CHIP`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `Microarray`.`CHIP` (
  `Chip_ID` VARCHAR(45) NOT NULL ,
  `Type` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`Chip_ID`) ,
  UNIQUE INDEX `Chip_ID_UNIQUE` (`Chip_ID` ASC) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Microarray`.`PROBESET`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `Microarray`.`PROBESET` (
  `Identifier` VARCHAR(45) NOT NULL ,
  `CHIP_Chip_ID` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`Identifier`, `CHIP_Chip_ID`) ,
  INDEX `fk_PROBESET_CHIP1_idx` (`CHIP_Chip_ID` ASC) ,
  CONSTRAINT `fk_PROBESET_CHIP1`
    FOREIGN KEY (`CHIP_Chip_ID` )
    REFERENCES `Microarray`.`CHIP` (`Chip_ID` )
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Microarray`.`GENOME`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `Microarray`.`GENOME` (
  `Genome_Name` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`Genome_Name`) ,
  UNIQUE INDEX `Genome_Name_UNIQUE` (`Genome_Name` ASC) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Microarray`.`CHROMOSOME`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `Microarray`.`CHROMOSOME` (
  `Number` INT NOT NULL ,
  `GENOME_Genome_Name` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`Number`, `GENOME_Genome_Name`) ,
  INDEX `fk_CHROMOSOME_GENOME1_idx` (`GENOME_Genome_Name` ASC) ,
  CONSTRAINT `fk_CHROMOSOME_GENOME1`
    FOREIGN KEY (`GENOME_Genome_Name` )
    REFERENCES `Microarray`.`GENOME` (`Genome_Name` )
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Microarray`.`GENE`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `Microarray`.`GENE` (
  `Ensembl_ID` VARCHAR(45) NOT NULL ,
  `CHROMOSOME_GENOME_Genome_Name` VARCHAR(45) NOT NULL ,
  `CHROMOSOME_Number` INT NOT NULL ,
  `PROBESET_Identifier` VARCHAR(45) NOT NULL ,
  `PROBESET_CHIP_Chip_ID` VARCHAR(45) NOT NULL ,
  `Ensembl_Name` VARCHAR(45) NOT NULL ,
  `Description` VARCHAR(150) NOT NULL ,
  `Left_Marker` INT NOT NULL ,
  `Right_Marker` INT NOT NULL ,
  PRIMARY KEY (`Ensembl_ID`, `CHROMOSOME_GENOME_Genome_Name`, `CHROMOSOME_Number`, `PROBESET_Identifier`, `PROBESET_CHIP_Chip_ID`) ,
  INDEX `fk_GENE_PROBESET1_idx` (`PROBESET_Identifier` ASC, `PROBESET_CHIP_Chip_ID` ASC) ,
  INDEX `fk_GENE_CHROMOSOME1_idx` (`CHROMOSOME_Number` ASC, `CHROMOSOME_GENOME_Genome_Name` ASC) ,
  CONSTRAINT `fk_GENE_PROBESET1`
    FOREIGN KEY (`PROBESET_Identifier` , `PROBESET_CHIP_Chip_ID` )
    REFERENCES `Microarray`.`PROBESET` (`Identifier` , `CHIP_Chip_ID` )
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `fk_GENE_CHROMOSOME1`
    FOREIGN KEY (`CHROMOSOME_Number` , `CHROMOSOME_GENOME_Genome_Name` )
    REFERENCES `Microarray`.`CHROMOSOME` (`Number` , `GENOME_Genome_Name` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Microarray`.`PROBE`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `Microarray`.`PROBE` (
  `X_Coordinate` INT NOT NULL ,
  `Y_Coordinate` INT NOT NULL ,
  `PROBESET_Identifier` VARCHAR(45) NOT NULL ,
  `PROBESET_CHIP_Chip_ID` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`X_Coordinate`, `Y_Coordinate`, `PROBESET_Identifier`, `PROBESET_CHIP_Chip_ID`) ,
  INDEX `fk_PROBE_PROBESET1_idx` (`PROBESET_Identifier` ASC, `PROBESET_CHIP_Chip_ID` ASC) ,
  CONSTRAINT `fk_PROBE_PROBESET1`
    FOREIGN KEY (`PROBESET_Identifier` , `PROBESET_CHIP_Chip_ID` )
    REFERENCES `Microarray`.`PROBESET` (`Identifier` , `CHIP_Chip_ID` )
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Microarray`.`PROTEIN`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `Microarray`.`PROTEIN` (
  `Protein_ID` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`Protein_ID`) ,
  UNIQUE INDEX `Protein_ID_UNIQUE` (`Protein_ID` ASC) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Microarray`.`TRANSCRIPT`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `Microarray`.`TRANSCRIPT` (
  `Transcript_ID` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`Transcript_ID`) ,
  UNIQUE INDEX `Transcript_ID_UNIQUE` (`Transcript_ID` ASC) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Microarray`.`QTL`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `Microarray`.`QTL` (
  `QTL_Name` VARCHAR(45) NOT NULL ,
  `CHROMOSOME_GENOME_Genome_Name` VARCHAR(45) NOT NULL ,
  `CHROMOSOME_Number` INT NOT NULL ,
  `Left_flank_marker` VARCHAR(45) NOT NULL ,
  `right_flank_marker` VARCHAR(45) NOT NULL ,
  `peak_marker` VARCHAR(45) NULL ,
  `QTL_trait` VARCHAR(150) NOT NULL ,
  `RightFM` INT NULL ,
  `LeftFM` INT NULL ,
  PRIMARY KEY (`QTL_Name`, `CHROMOSOME_GENOME_Genome_Name`, `CHROMOSOME_Number`) ,
  INDEX `fk_QTL_CHROMOSOME1_idx` (`CHROMOSOME_Number` ASC, `CHROMOSOME_GENOME_Genome_Name` ASC) ,
  CONSTRAINT `fk_QTL_CHROMOSOME1`
    FOREIGN KEY (`CHROMOSOME_Number` , `CHROMOSOME_GENOME_Genome_Name` )
    REFERENCES `Microarray`.`CHROMOSOME` (`Number` , `GENOME_Genome_Name` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Microarray`.`ASSOCIATION`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `Microarray`.`ASSOCIATION` (
  `TRANSCRIPT_Transcript_ID` VARCHAR(45) NOT NULL ,
  `PROTEIN_Protein_ID` VARCHAR(45) NOT NULL ,
  `GENE_Ensembl_ID` VARCHAR(45) NOT NULL ,
  `NUMBER` INT NOT NULL DEFAULT 1 ,
  PRIMARY KEY (`TRANSCRIPT_Transcript_ID`, `PROTEIN_Protein_ID`, `GENE_Ensembl_ID`) ,
  INDEX `fk_ASSOCIATION_PROTEIN1_idx` (`PROTEIN_Protein_ID` ASC) ,
  INDEX `fk_ASSOCIATION_GENE1_idx` (`GENE_Ensembl_ID` ASC) ,
  CONSTRAINT `fk_ASSOCIATION_TRANSCRIPT`
    FOREIGN KEY (`TRANSCRIPT_Transcript_ID` )
    REFERENCES `Microarray`.`TRANSCRIPT` (`Transcript_ID` )
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ASSOCIATION_PROTEIN1`
    FOREIGN KEY (`PROTEIN_Protein_ID` )
    REFERENCES `Microarray`.`PROTEIN` (`Protein_ID` )
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ASSOCIATION_GENE1`
    FOREIGN KEY (`GENE_Ensembl_ID` )
    REFERENCES `Microarray`.`GENE` (`Ensembl_ID` )
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB;

USE `Microarray` ;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;