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
|
-- $Id: phenotype.sql,v 1.6 2007-04-27 16:09:46 emmert Exp $
-- ==========================================
-- Chado phenotype module
--
-- 05-31-2011
-- added 'name' column to phenotype. non-unique human readable field.
--
-- =================================================================
-- Dependencies:
--
-- :import cvterm from cv
-- :import feature from sequence
-- =================================================================
-- ================================================
-- TABLE: phenotype
-- ================================================
CREATE TABLE phenotype (
phenotype_id SERIAL NOT NULL,
primary key (phenotype_id),
uniquename TEXT NOT NULL,
name TEXT default null,
observable_id INT,
FOREIGN KEY (observable_id) REFERENCES cvterm (cvterm_id) ON DELETE CASCADE,
attr_id INT,
FOREIGN KEY (attr_id) REFERENCES cvterm (cvterm_id) ON DELETE SET NULL,
value TEXT,
cvalue_id INT,
FOREIGN KEY (cvalue_id) REFERENCES cvterm (cvterm_id) ON DELETE SET NULL,
assay_id INT,
FOREIGN KEY (assay_id) REFERENCES cvterm (cvterm_id) ON DELETE SET NULL,
CONSTRAINT phenotype_c1 UNIQUE (uniquename)
);
CREATE INDEX phenotype_idx1 ON phenotype (cvalue_id);
CREATE INDEX phenotype_idx2 ON phenotype (observable_id);
CREATE INDEX phenotype_idx3 ON phenotype (attr_id);
COMMENT ON TABLE phenotype IS 'A phenotypic statement, or a single
atomic phenotypic observation, is a controlled sentence describing
observable effects of non-wild type function. E.g. Obs=eye, attribute=color, cvalue=red.';
COMMENT ON COLUMN phenotype.observable_id IS 'The entity: e.g. anatomy_part, biological_process.';
COMMENT ON COLUMN phenotype.attr_id IS 'Phenotypic attribute (quality, property, attribute, character) - drawn from PATO.';
COMMENT ON COLUMN phenotype.value IS 'Value of attribute - unconstrained free text. Used only if cvalue_id is not appropriate.';
COMMENT ON COLUMN phenotype.cvalue_id IS 'Phenotype attribute value (state).';
COMMENT ON COLUMN phenotype.assay_id IS 'Evidence type.';
-- ================================================
-- TABLE: phenotype_cvterm
-- ================================================
CREATE TABLE phenotype_cvterm (
phenotype_cvterm_id SERIAL NOT NULL,
primary key (phenotype_cvterm_id),
phenotype_id INT NOT NULL,
FOREIGN KEY (phenotype_id) REFERENCES phenotype (phenotype_id) ON DELETE CASCADE,
cvterm_id INT NOT NULL,
FOREIGN KEY (cvterm_id) REFERENCES cvterm (cvterm_id) ON DELETE CASCADE,
rank int not null default 0,
CONSTRAINT phenotype_cvterm_c1 UNIQUE (phenotype_id, cvterm_id, rank)
);
CREATE INDEX phenotype_cvterm_idx1 ON phenotype_cvterm (phenotype_id);
CREATE INDEX phenotype_cvterm_idx2 ON phenotype_cvterm (cvterm_id);
COMMENT ON TABLE phenotype_cvterm IS NULL;
-- ================================================
-- TABLE: feature_phenotype
-- ================================================
CREATE TABLE feature_phenotype (
feature_phenotype_id SERIAL NOT NULL,
primary key (feature_phenotype_id),
feature_id INT NOT NULL,
FOREIGN KEY (feature_id) REFERENCES feature (feature_id) ON DELETE CASCADE,
phenotype_id INT NOT NULL,
FOREIGN KEY (phenotype_id) REFERENCES phenotype (phenotype_id) ON DELETE CASCADE,
CONSTRAINT feature_phenotype_c1 UNIQUE (feature_id,phenotype_id)
);
CREATE INDEX feature_phenotype_idx1 ON feature_phenotype (feature_id);
CREATE INDEX feature_phenotype_idx2 ON feature_phenotype (phenotype_id);
COMMENT ON TABLE feature_phenotype IS NULL;
create table phenotypeprop (
phenotypeprop_id serial not null,
primary key (phenotypeprop_id),
phenotype_id int not null,
foreign key (phenotype_id) references phenotype (phenotype_id) on delete cascade INITIALLY DEFERRED,
type_id int not null,
foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
value text null,
rank int not null default 0,
constraint phenotypeprop_c1 unique (phenotype_id,type_id,rank)
);
create index phenotypeprop_idx1 on phenotypeprop (phenotype_id);
create index phenotypeprop_idx2 on phenotypeprop (type_id);
COMMENT ON TABLE phenotypeprop IS 'A phenotype can have any number of
slot-value property tags attached to it. This is an alternative to
hardcoding a list of columns in the relational schema, and is
completely extensible. There is a unique constraint, phenotypeprop_c1, for
the combination of phenotype_id, rank, and type_id. Multivalued property-value pairs must be differentiated by rank.';
|
