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';

CREATE SCHEMA IF NOT EXISTS `detectability` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `detectability`;

-- -----------------------------------------------------
-- Table `detectability`.`protein`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `detectability`.`protein` ;

CREATE  TABLE IF NOT EXISTS `detectability`.`protein` (
  `protein_id` INT NOT NULL AUTO_INCREMENT ,
  `sequence` VARCHAR(45) NOT NULL ,
  `coverage` DECIMAL(3,2) NOT NULL ,
  PRIMARY KEY (`protein_id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `detectability`.`peptide`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `detectability`.`peptide` ;

CREATE  TABLE IF NOT EXISTS `detectability`.`peptide` (
  `peptide_id` INT NOT NULL AUTO_INCREMENT ,
  `sequence` VARCHAR(500) NOT NULL ,
  `experimental` TINYINT(1) NOT NULL ,
  `detectability` DECIMAL(4,4) NULL ,
  `start` INT NULL ,
  `stop` INT NULL ,
  PRIMARY KEY (`peptide_id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `detectability`.`fractionation`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `detectability`.`fractionation` ;

CREATE  TABLE IF NOT EXISTS `detectability`.`fractionation` (
  `fractionation_id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(125) NOT NULL ,
  PRIMARY KEY (`fractionation_id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `detectability`.`depletion`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `detectability`.`depletion` ;

CREATE  TABLE IF NOT EXISTS `detectability`.`depletion` (
  `depletion_id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(125) NOT NULL ,
  PRIMARY KEY (`depletion_id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `detectability`.`instrument`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `detectability`.`instrument` ;

CREATE  TABLE IF NOT EXISTS `detectability`.`instrument` (
  `instrument_id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`instrument_id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `detectability`.`protocol`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `detectability`.`protocol` ;

CREATE  TABLE IF NOT EXISTS `detectability`.`protocol` (
  `protocol_id` INT NOT NULL AUTO_INCREMENT ,
  `l_fractionation_id` INT NOT NULL ,
  `l_depletion_id` INT NOT NULL ,
  `l_instrument_id` INT NOT NULL ,
  PRIMARY KEY (`protocol_id`) ,
  INDEX `l_fractionation_id` (`l_fractionation_id` ASC) ,
  INDEX `l_depletion_id` (`l_depletion_id` ASC) ,
  INDEX `l_instrument_id1` (`l_instrument_id` ASC) ,
  CONSTRAINT `l_fractionation_id`
    FOREIGN KEY (`l_fractionation_id` )
    REFERENCES `detectability`.`fractionation` (`fractionation_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `l_depletion_id`
    FOREIGN KEY (`l_depletion_id` )
    REFERENCES `detectability`.`depletion` (`depletion_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `l_instrument_id1`
    FOREIGN KEY (`l_instrument_id` )
    REFERENCES `detectability`.`instrument` (`instrument_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `detectability`.`experiment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `detectability`.`experiment` ;

CREATE  TABLE IF NOT EXISTS `detectability`.`experiment` (
  `experiment_id` INT NOT NULL AUTO_INCREMENT ,
  `l_protocol_id` INT NOT NULL ,
  `number` INT NOT NULL ,
  `scx_number` INT NULL ,
  PRIMARY KEY (`experiment_id`) ,
  INDEX `l_protocol_id` (`l_protocol_id` ASC) ,
  CONSTRAINT `l_protocol_id`
    FOREIGN KEY (`l_protocol_id` )
    REFERENCES `detectability`.`protocol` (`protocol_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `detectability`.`protein_has_peptide`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `detectability`.`protein_has_peptide` ;

CREATE  TABLE IF NOT EXISTS `detectability`.`protein_has_peptide` (
  `protein_has_peptide_id` INT NOT NULL AUTO_INCREMENT ,
  `l_protein_id` INT NOT NULL ,
  `l_peptide_id` INT NOT NULL ,
  PRIMARY KEY (`protein_has_peptide_id`) ,
  INDEX `l_protein_id` (`l_protein_id` ASC) ,
  INDEX `l_peptide_id` (`l_peptide_id` ASC) ,
  CONSTRAINT `l_protein_id`
    FOREIGN KEY (`l_protein_id` )
    REFERENCES `detectability`.`protein` (`protein_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `l_peptide_id`
    FOREIGN KEY (`l_peptide_id` )
    REFERENCES `detectability`.`peptide` (`peptide_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


-- -----------------------------------------------------
-- Table `detectability`.`sample`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `detectability`.`sample` ;

CREATE  TABLE IF NOT EXISTS `detectability`.`sample` (
  `sample_id` INT NOT NULL AUTO_INCREMENT ,
  PRIMARY KEY (`sample_id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `detectability`.`experiment_has_protein`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `detectability`.`experiment_has_protein` ;

CREATE  TABLE IF NOT EXISTS `detectability`.`experiment_has_protein` (
  `experiment_has_protein_id` INT NOT NULL AUTO_INCREMENT ,
  `l_experiment_id` INT NOT NULL ,
  `l_protein_id` INT NOT NULL ,
  PRIMARY KEY (`experiment_has_protein_id`) ,
  INDEX `l_experiment_id` (`l_experiment_id` ASC) ,
  INDEX `l_protein_id` (`l_protein_id` ASC) ,
  CONSTRAINT `l_experiment_id`
    FOREIGN KEY (`l_experiment_id` )
    REFERENCES `detectability`.`experiment` (`experiment_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `l_protein_id`
    FOREIGN KEY (`l_protein_id` )
    REFERENCES `detectability`.`protein` (`protein_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);



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