Newer
Older
felmer
committed
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
/* ---------------------------------------------------------------------- */
/* Script generated with: DeZign for Databases v5.2.3 */
/* Target DBMS: PostgreSQL 8 */
/* Project file: schema.dez */
/* Project name: */
/* Author: */
/* Script type: Database creation script */
/* Created on: 2009-12-07 11:03 */
/* Model version: Version 2009-12-07 */
/* ---------------------------------------------------------------------- */
/* ---------------------------------------------------------------------- */
/* Domains */
/* ---------------------------------------------------------------------- */
CREATE DOMAIN CHECKSUM AS CHARACTER VARYING(8);
CREATE DOMAIN CODE AS CHARACTER VARYING(40);
CREATE DOMAIN DESCRIPTION AS CHARACTER VARYING(2000);
CREATE DOMAIN INTEGER_NUMBER AS INTEGER;
CREATE DOMAIN REAL_NUMBER AS DOUBLE PRECISION;
CREATE DOMAIN LONG_SEQUENCE AS TEXT;
CREATE DOMAIN SHORT_DESCRIPTION AS CHARACTER VARYING(200);
CREATE DOMAIN TECH_ID AS BIGINT;
CREATE DOMAIN SHORT_SEQUENCE AS CHARACTER VARYING(1000);
CREATE DOMAIN ACCESSION_NUMBER AS CHARACTER VARYING(256);
CREATE DOMAIN SPECTRUM_REFERENCE AS CHARACTER VARYING(100);
/* ---------------------------------------------------------------------- */
/* Tables */
/* ---------------------------------------------------------------------- */
/* ---------------------------------------------------------------------- */
/* Add table "EXPERIMENTS" */
/* ---------------------------------------------------------------------- */
CREATE TABLE EXPERIMENTS (
ID BIGSERIAL NOT NULL,
PERM_ID CODE NOT NULL,
CONSTRAINT PK_EXPERIMENTS PRIMARY KEY (ID),
CONSTRAINT TUC_EXPERIMENTS_1 UNIQUE (PERM_ID)
);
/* ---------------------------------------------------------------------- */
/* Add table "DATA_SETS" */
/* ---------------------------------------------------------------------- */
CREATE TABLE DATA_SETS (
ID BIGSERIAL NOT NULL,
EXPE_ID TECH_ID NOT NULL,
SAMP_ID TECH_ID,
DB_ID TECH_ID NOT NULL,
PERM_ID CODE NOT NULL,
CONSTRAINT PK_DATA_SETS PRIMARY KEY (ID),
CONSTRAINT TUC_DATA_SETS_1 UNIQUE (PERM_ID)
);
CREATE INDEX IX_FK_DATA_SETS_EXPERIMENTS ON DATA_SETS (EXPE_ID);
CREATE INDEX IX_FK_DATA_SETS_SAMPLES ON DATA_SETS (SAMP_ID);
/* ---------------------------------------------------------------------- */
/* Add table "MODIFICATIONS" */
/* ---------------------------------------------------------------------- */
CREATE TABLE MODIFICATIONS (
ID BIGSERIAL NOT NULL,
MOPE_ID TECH_ID NOT NULL,
POS INTEGER_NUMBER NOT NULL,
MASS REAL_NUMBER NOT NULL,
CONSTRAINT PK_MODIFICATIONS PRIMARY KEY (ID)
);
/* ---------------------------------------------------------------------- */
/* Add table "PEPTIDES" */
/* ---------------------------------------------------------------------- */
CREATE TABLE PEPTIDES (
ID BIGSERIAL NOT NULL,
PROT_ID TECH_ID NOT NULL,
SEQUENCE SHORT_SEQUENCE NOT NULL,
CHARGE INTEGER_NUMBER NOT NULL,
CONSTRAINT PK_PEPTIDES PRIMARY KEY (ID)
);
CREATE INDEX IX_FK_PEPTIDES_PROTEINS ON PEPTIDES (PROT_ID);
/* ---------------------------------------------------------------------- */
/* Add table "PROTEINS" */
/* ---------------------------------------------------------------------- */
CREATE TABLE PROTEINS (
ID BIGSERIAL NOT NULL,
DASE_ID TECH_ID NOT NULL,
PROBABILITY REAL_NUMBER NOT NULL,
CONSTRAINT PK_PROTEINS PRIMARY KEY (ID)
);
CREATE INDEX IDX_PROTEINS_1 ON PROTEINS (DASE_ID);
/* ---------------------------------------------------------------------- */
/* Add table "SAMPLES" */
/* ---------------------------------------------------------------------- */
CREATE TABLE SAMPLES (
ID BIGSERIAL NOT NULL,
PERM_ID CODE NOT NULL,
EXPE_ID TECH_ID NOT NULL,
CONSTRAINT PK_SAMPLES PRIMARY KEY (ID),
CONSTRAINT TUC_SAMPLES_1 UNIQUE (PERM_ID)
);
/* ---------------------------------------------------------------------- */
/* Add table "SEQUENCES" */
/* ---------------------------------------------------------------------- */
CREATE TABLE SEQUENCES (
ID BIGSERIAL NOT NULL,
DB_ID TECH_ID NOT NULL,
PRRE_ID TECH_ID NOT NULL,
AMINO_ACID_SEQUENCE LONG_SEQUENCE NOT NULL,
CHECKSUM CHECKSUM NOT NULL,
CONSTRAINT PK_SEQUENCES PRIMARY KEY (ID)
);
/* ---------------------------------------------------------------------- */
/* Add table "IDENTIFIED_PROTEINS" */
/* ---------------------------------------------------------------------- */
CREATE TABLE IDENTIFIED_PROTEINS (
ID BIGSERIAL NOT NULL,
PROT_ID TECH_ID NOT NULL,
SEQU_ID TECH_ID NOT NULL,
CONSTRAINT PK_IDENTIFIED_PROTEINS PRIMARY KEY (ID)
);
/* ---------------------------------------------------------------------- */
/* Add table "ABUNDANCES" */
/* ---------------------------------------------------------------------- */
CREATE TABLE ABUNDANCES (
ID BIGSERIAL NOT NULL,
PROT_ID TECH_ID NOT NULL,
SAMP_ID TECH_ID NOT NULL,
VALUE REAL_NUMBER NOT NULL,
CONSTRAINT PK_ABUNDANCES PRIMARY KEY (ID)
);
/* ---------------------------------------------------------------------- */
/* Add table "PROBABILITY_FDR_MAPPINGS" */
/* ---------------------------------------------------------------------- */
CREATE TABLE PROBABILITY_FDR_MAPPINGS (
ID BIGSERIAL NOT NULL,
DASE_ID TECH_ID NOT NULL,
PROBABILITY REAL_NUMBER NOT NULL,
FALSE_DISCOVERY_RATE REAL_NUMBER NOT NULL,
CONSTRAINT PK_PROBABILITY_FDR_MAPPINGS PRIMARY KEY (ID)
);
/* ---------------------------------------------------------------------- */
/* Add table "PROTEIN_REFERENCES" */
/* ---------------------------------------------------------------------- */
CREATE TABLE PROTEIN_REFERENCES (
ID BIGSERIAL NOT NULL,
ACCESSION_NUMBER ACCESSION_NUMBER NOT NULL,
DESCRIPTION DESCRIPTION,
CONSTRAINT PK_PROTEIN_REFERENCES PRIMARY KEY (ID),
CONSTRAINT TUC_PROTEIN_REFERENCES_1 UNIQUE (ACCESSION_NUMBER)
);
CREATE INDEX IDX_PROTEIN_REFERENCES_1 ON PROTEIN_REFERENCES (ACCESSION_NUMBER);
/* ---------------------------------------------------------------------- */
/* Add table "DATABASES" */
/* ---------------------------------------------------------------------- */
CREATE TABLE DATABASES (
ID BIGSERIAL NOT NULL,
NAME_AND_VERSION SHORT_DESCRIPTION NOT NULL,
CONSTRAINT PK_DATABASES PRIMARY KEY (ID),
CONSTRAINT TUC_DATABASES_1 UNIQUE (NAME_AND_VERSION)
);
/* ---------------------------------------------------------------------- */
/* Add table "MODIFIED_PEPTIDES" */
/* ---------------------------------------------------------------------- */
CREATE TABLE MODIFIED_PEPTIDES (
ID BIGSERIAL NOT NULL,
PEPT_ID TECH_ID NOT NULL,
NTERM_MASS REAL_NUMBER NOT NULL,
CTERM_MASS REAL_NUMBER NOT NULL,
CONSTRAINT PK_MODIFIED_PEPTIDES PRIMARY KEY (ID)
);
/* ---------------------------------------------------------------------- */
/* Add table "SPECTRUM_REFERENCES" */
/* ---------------------------------------------------------------------- */
CREATE TABLE SPECTRUM_REFERENCES (
ID BIGSERIAL NOT NULL,
PEPT_ID TECH_ID NOT NULL,
REFERENCE SPECTRUM_REFERENCE NOT NULL,
CONSTRAINT PK_SPECTRUM_REFERENCES PRIMARY KEY (ID)
);
/* ---------------------------------------------------------------------- */
/* Add table "MODIFICATION_FRACTIONS" */
/* ---------------------------------------------------------------------- */
CREATE TABLE MODIFICATION_FRACTIONS (
ID BIGSERIAL NOT NULL,
MODI_ID TECH_ID NOT NULL,
SAMP_ID TECH_ID,
FRACTION REAL_NUMBER NOT NULL,
CONSTRAINT PK_MODIFICATION_FRACTIONS PRIMARY KEY (ID)
);
/* ---------------------------------------------------------------------- */
/* Add table "EVENTS" */
/* ---------------------------------------------------------------------- */
CREATE TABLE EVENTS (
LAST_SEEN_DELETION_EVENT_ID TECH_ID NOT NULL
);
/* ---------------------------------------------------------------------- */
/* Foreign key constraints */
/* ---------------------------------------------------------------------- */
ALTER TABLE DATA_SETS ADD CONSTRAINT DA_EX_FK
FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS (ID);
ALTER TABLE DATA_SETS ADD CONSTRAINT DA_SA_FK
FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES (ID);
ALTER TABLE DATA_SETS ADD CONSTRAINT DATABASES_DATA_SETS
FOREIGN KEY (DB_ID) REFERENCES DATABASES (ID);
ALTER TABLE MODIFICATIONS ADD CONSTRAINT MODIFIED_PEPTIDES_MODIFICATIONS
FOREIGN KEY (MOPE_ID) REFERENCES MODIFIED_PEPTIDES (ID) ON DELETE CASCADE;
ALTER TABLE PEPTIDES ADD CONSTRAINT PE_PR_FK
FOREIGN KEY (PROT_ID) REFERENCES PROTEINS (ID) ON DELETE CASCADE;
ALTER TABLE PROTEINS ADD CONSTRAINT DATA_SETS_PROTEINS
FOREIGN KEY (DASE_ID) REFERENCES DATA_SETS (ID) ON DELETE CASCADE;
ALTER TABLE SEQUENCES ADD CONSTRAINT DATABASES_SEQUENCES
FOREIGN KEY (DB_ID) REFERENCES DATABASES (ID);
ALTER TABLE SEQUENCES ADD CONSTRAINT PROTEIN_REFERENCES_SEQUENCES
FOREIGN KEY (PRRE_ID) REFERENCES PROTEIN_REFERENCES (ID);
ALTER TABLE IDENTIFIED_PROTEINS ADD CONSTRAINT PROTEINS_IDENTIFIED_PROTEINS
FOREIGN KEY (PROT_ID) REFERENCES PROTEINS (ID) ON DELETE CASCADE;
ALTER TABLE IDENTIFIED_PROTEINS ADD CONSTRAINT SEQUENCES_IDENTIFIED_PROTEINS
FOREIGN KEY (SEQU_ID) REFERENCES SEQUENCES (ID);
ALTER TABLE ABUNDANCES ADD CONSTRAINT SAMPLES_ABUNDANCES
FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES (ID);
ALTER TABLE ABUNDANCES ADD CONSTRAINT PROTEINS_ABUNDANCES
FOREIGN KEY (PROT_ID) REFERENCES PROTEINS (ID) ON DELETE CASCADE;
ALTER TABLE PROBABILITY_FDR_MAPPINGS ADD CONSTRAINT DATA_SETS_PROBABILITY_FDR_MAPPINGS
FOREIGN KEY (DASE_ID) REFERENCES DATA_SETS (ID) ON DELETE CASCADE;
ALTER TABLE MODIFIED_PEPTIDES ADD CONSTRAINT PEPTIDES_MODIFIED_PEPTIDES
FOREIGN KEY (PEPT_ID) REFERENCES PEPTIDES (ID) ON DELETE CASCADE;
ALTER TABLE SPECTRUM_REFERENCES ADD CONSTRAINT PEPTIDES_SPECTRUM_REFERENCES
FOREIGN KEY (PEPT_ID) REFERENCES PEPTIDES (ID) ON DELETE CASCADE;
ALTER TABLE MODIFICATION_FRACTIONS ADD CONSTRAINT MODIFICATIONS_MODIFICATION_FRACTIONS
FOREIGN KEY (MODI_ID) REFERENCES MODIFICATIONS (ID) ON DELETE CASCADE;
ALTER TABLE MODIFICATION_FRACTIONS ADD CONSTRAINT SAMPLES_MODIFICATION_FRACTIONS
FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES (ID);