· 8 years ago · Jun 22, 2018, 10:28 PM
1SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
4
5CREATE SCHEMA IF NOT EXISTS `detectability` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
6USE `detectability`;
7
8-- -----------------------------------------------------
9-- Table `detectability`.`protein`
10-- -----------------------------------------------------
11DROP TABLE IF EXISTS `detectability`.`protein` ;
12
13CREATE TABLE IF NOT EXISTS `detectability`.`protein` (
14 `protein_id` INT NOT NULL AUTO_INCREMENT ,
15 `sequence` VARCHAR(45) NOT NULL ,
16 `coverage` DECIMAL(3,2) NOT NULL ,
17 PRIMARY KEY (`protein_id`) )
18ENGINE = InnoDB;
19
20
21-- -----------------------------------------------------
22-- Table `detectability`.`peptide`
23-- -----------------------------------------------------
24DROP TABLE IF EXISTS `detectability`.`peptide` ;
25
26CREATE TABLE IF NOT EXISTS `detectability`.`peptide` (
27 `peptide_id` INT NOT NULL AUTO_INCREMENT ,
28 `sequence` VARCHAR(500) NOT NULL ,
29 `experimental` TINYINT(1) NOT NULL ,
30 `detectability` DECIMAL(4,4) NULL ,
31 `start` INT NULL ,
32 `stop` INT NULL ,
33 PRIMARY KEY (`peptide_id`) )
34ENGINE = InnoDB;
35
36
37-- -----------------------------------------------------
38-- Table `detectability`.`fractionation`
39-- -----------------------------------------------------
40DROP TABLE IF EXISTS `detectability`.`fractionation` ;
41
42CREATE TABLE IF NOT EXISTS `detectability`.`fractionation` (
43 `fractionation_id` INT NOT NULL AUTO_INCREMENT ,
44 `name` VARCHAR(125) NOT NULL ,
45 PRIMARY KEY (`fractionation_id`) )
46ENGINE = InnoDB;
47
48
49-- -----------------------------------------------------
50-- Table `detectability`.`depletion`
51-- -----------------------------------------------------
52DROP TABLE IF EXISTS `detectability`.`depletion` ;
53
54CREATE TABLE IF NOT EXISTS `detectability`.`depletion` (
55 `depletion_id` INT NOT NULL AUTO_INCREMENT ,
56 `name` VARCHAR(125) NOT NULL ,
57 PRIMARY KEY (`depletion_id`) )
58ENGINE = InnoDB;
59
60
61-- -----------------------------------------------------
62-- Table `detectability`.`instrument`
63-- -----------------------------------------------------
64DROP TABLE IF EXISTS `detectability`.`instrument` ;
65
66CREATE TABLE IF NOT EXISTS `detectability`.`instrument` (
67 `instrument_id` INT NOT NULL AUTO_INCREMENT ,
68 `name` VARCHAR(45) NOT NULL ,
69 PRIMARY KEY (`instrument_id`) )
70ENGINE = InnoDB;
71
72
73-- -----------------------------------------------------
74-- Table `detectability`.`protocol`
75-- -----------------------------------------------------
76DROP TABLE IF EXISTS `detectability`.`protocol` ;
77
78CREATE TABLE IF NOT EXISTS `detectability`.`protocol` (
79 `protocol_id` INT NOT NULL AUTO_INCREMENT ,
80 `l_fractionation_id` INT NOT NULL ,
81 `l_depletion_id` INT NOT NULL ,
82 `l_instrument_id` INT NOT NULL ,
83 PRIMARY KEY (`protocol_id`) ,
84 INDEX `l_fractionation_id` (`l_fractionation_id` ASC) ,
85 INDEX `l_depletion_id` (`l_depletion_id` ASC) ,
86 INDEX `l_instrument_id1` (`l_instrument_id` ASC) ,
87 CONSTRAINT `l_fractionation_id`
88 FOREIGN KEY (`l_fractionation_id` )
89 REFERENCES `detectability`.`fractionation` (`fractionation_id` )
90 ON DELETE NO ACTION
91 ON UPDATE NO ACTION,
92 CONSTRAINT `l_depletion_id`
93 FOREIGN KEY (`l_depletion_id` )
94 REFERENCES `detectability`.`depletion` (`depletion_id` )
95 ON DELETE NO ACTION
96 ON UPDATE NO ACTION,
97 CONSTRAINT `l_instrument_id1`
98 FOREIGN KEY (`l_instrument_id` )
99 REFERENCES `detectability`.`instrument` (`instrument_id` )
100 ON DELETE NO ACTION
101 ON UPDATE NO ACTION)
102ENGINE = InnoDB;
103
104
105-- -----------------------------------------------------
106-- Table `detectability`.`experiment`
107-- -----------------------------------------------------
108DROP TABLE IF EXISTS `detectability`.`experiment` ;
109
110CREATE TABLE IF NOT EXISTS `detectability`.`experiment` (
111 `experiment_id` INT NOT NULL AUTO_INCREMENT ,
112 `l_protocol_id` INT NOT NULL ,
113 `number` INT NOT NULL ,
114 `scx_number` INT NULL ,
115 PRIMARY KEY (`experiment_id`) ,
116 INDEX `l_protocol_id` (`l_protocol_id` ASC) ,
117 CONSTRAINT `l_protocol_id`
118 FOREIGN KEY (`l_protocol_id` )
119 REFERENCES `detectability`.`protocol` (`protocol_id` )
120 ON DELETE NO ACTION
121 ON UPDATE NO ACTION)
122ENGINE = InnoDB;
123
124
125-- -----------------------------------------------------
126-- Table `detectability`.`protein_has_peptide`
127-- -----------------------------------------------------
128DROP TABLE IF EXISTS `detectability`.`protein_has_peptide` ;
129
130CREATE TABLE IF NOT EXISTS `detectability`.`protein_has_peptide` (
131 `protein_has_peptide_id` INT NOT NULL AUTO_INCREMENT ,
132 `l_protein_id` INT NOT NULL ,
133 `l_peptide_id` INT NOT NULL ,
134 PRIMARY KEY (`protein_has_peptide_id`) ,
135 INDEX `l_protein_id` (`l_protein_id` ASC) ,
136 INDEX `l_peptide_id` (`l_peptide_id` ASC) ,
137 CONSTRAINT `l_protein_id`
138 FOREIGN KEY (`l_protein_id` )
139 REFERENCES `detectability`.`protein` (`protein_id` )
140 ON DELETE NO ACTION
141 ON UPDATE NO ACTION,
142 CONSTRAINT `l_peptide_id`
143 FOREIGN KEY (`l_peptide_id` )
144 REFERENCES `detectability`.`peptide` (`peptide_id` )
145 ON DELETE NO ACTION
146 ON UPDATE NO ACTION);
147
148
149-- -----------------------------------------------------
150-- Table `detectability`.`sample`
151-- -----------------------------------------------------
152DROP TABLE IF EXISTS `detectability`.`sample` ;
153
154CREATE TABLE IF NOT EXISTS `detectability`.`sample` (
155 `sample_id` INT NOT NULL AUTO_INCREMENT ,
156 PRIMARY KEY (`sample_id`) )
157ENGINE = InnoDB;
158
159
160-- -----------------------------------------------------
161-- Table `detectability`.`experiment_has_protein`
162-- -----------------------------------------------------
163DROP TABLE IF EXISTS `detectability`.`experiment_has_protein` ;
164
165CREATE TABLE IF NOT EXISTS `detectability`.`experiment_has_protein` (
166 `experiment_has_protein_id` INT NOT NULL AUTO_INCREMENT ,
167 `l_experiment_id` INT NOT NULL ,
168 `l_protein_id` INT NOT NULL ,
169 PRIMARY KEY (`experiment_has_protein_id`) ,
170 INDEX `l_experiment_id` (`l_experiment_id` ASC) ,
171 INDEX `l_protein_id` (`l_protein_id` ASC) ,
172 CONSTRAINT `l_experiment_id`
173 FOREIGN KEY (`l_experiment_id` )
174 REFERENCES `detectability`.`experiment` (`experiment_id` )
175 ON DELETE NO ACTION
176 ON UPDATE NO ACTION,
177 CONSTRAINT `l_protein_id`
178 FOREIGN KEY (`l_protein_id` )
179 REFERENCES `detectability`.`protein` (`protein_id` )
180 ON DELETE NO ACTION
181 ON UPDATE NO ACTION);
182
183
184
185SET SQL_MODE=@OLD_SQL_MODE;
186SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
187SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;