Commit | Line | Data |
dc85da04 |
1 | -- The cvterm module design is based on the ontology |
2 | |
3 | -- ================================================ |
4 | -- TABLE: cv |
5 | -- ================================================ |
6 | |
7 | create table cv ( |
8 | cv_id serial not null, |
9 | primary key (cv_id), |
10 | cvname varchar not null, |
11 | cvdefinition text, |
12 | |
13 | unique(cvname) |
14 | ); |
15 | |
16 | -- ================================================ |
17 | -- TABLE: cvterm |
18 | -- ================================================ |
19 | |
20 | create table cvterm ( |
21 | cvterm_id serial not null, |
22 | primary key (cvterm_id), |
23 | cv_id int not null, |
24 | foreign key (cv_id) references cv (cv_id), |
25 | name varchar(255) not null, |
26 | termdefinition text, |
27 | -- the primary dbxref for this term. Other dbxrefs may be cvterm_dbxref |
28 | dbxref_id int, |
29 | foreign key (dbxref_id) references dbxref (dbxref_id), |
30 | |
31 | unique(termname, cv_id) |
32 | -- The unique key on termname, termtype_id ensures that all terms are |
33 | -- unique within a given cv |
34 | ); |
35 | create index cvterm_idx1 on cvterm (cv_id); |
36 | |
37 | |
38 | -- ================================================ |
39 | -- TABLE: cvrelationship |
40 | -- ================================================ |
41 | |
42 | create table cvrelationship ( |
43 | cvrelationship_id serial not null, |
44 | primary key (cvrelationship_id), |
45 | reltype_id int not null, |
46 | foreign key (reltype_id) references cvterm (cvterm_id), |
47 | subjterm_id int not null, |
48 | foreign key (subjterm_id) references cvterm (cvterm_id), |
49 | objterm_id int not null, |
50 | foreign key (objterm_id) references cvterm (cvterm_id), |
51 | |
52 | unique(reltype_id, subjterm_id, objterm_id) |
53 | ); |
54 | create index cvrelationship_idx1 on cvrelationship (reltype_id); |
55 | create index cvrelationship_idx2 on cvrelationship (subjterm_id); |
56 | create index cvrelationship_idx3 on cvrelationship (objterm_id); |
57 | |
58 | |
59 | -- ================================================ |
60 | -- TABLE: cvpath |
61 | -- ================================================ |
62 | |
63 | create table cvpath ( |
64 | cvpath_id serial not null, |
65 | primary key (cvpath_id), |
66 | reltype_id int, |
67 | foreign key (reltype_id) references cvterm (cvterm_id), |
68 | subjterm_id int not null, |
69 | foreign key (subjterm_id) references cvterm (cvterm_id), |
70 | objterm_id int not null, |
71 | foreign key (objterm_id) references cvterm (cvterm_id), |
72 | cv_id int not null, |
73 | foreign key (cv_id) references cv (cv_id), |
74 | pathdistance int, |
75 | |
76 | unique (subjterm_id, objterm_id) |
77 | ); |
78 | create index cvpath_idx1 on cvpath (reltype_id); |
79 | create index cvpath_idx2 on cvpath (subjterm_id); |
80 | create index cvpath_idx3 on cvpath (objterm_id); |
81 | create index cvpath_idx4 on cvpath (cv_id); |
82 | |
83 | |
84 | -- ================================================ |
85 | -- TABLE: cvtermsynonym |
86 | -- ================================================ |
87 | |
88 | create table cvtermsynonym ( |
89 | cvterm_id int not null, |
90 | foreign key (cvterm_id) references cvterm (cvterm_id), |
91 | termsynonym varchar(255) not null, |
92 | |
93 | unique(cvterm_id, termsynonym) |
94 | ); |
95 | create index cvterm_synonym_idx1 on cvterm_synonym (cvterm_id); |
96 | |
97 | |
98 | -- ================================================ |
99 | -- TABLE: cvterm_dbxref |
100 | -- ================================================ |
101 | |
102 | create table cvterm_dbxref ( |
103 | cvterm_dbxref_id serial not null, |
104 | primary key (cvterm_dbxref_id), |
105 | cvterm_id int not null, |
106 | foreign key (cvterm_id) references cvterm (cvterm_id), |
107 | dbxref_id int not null, |
108 | foreign key (dbxref_id) references dbxref (dbxref_id), |
109 | |
110 | unique(cvterm_id, dbxref_id) |
111 | ); |
112 | create index cvterm_dbxref_idx1 on cvterm_dbxref (cvterm_id); |
113 | create index cvterm_dbxref_idx2 on cvterm_dbxref (dbxref_id); |
114 | |