/
ddl.sql
69 lines (58 loc) · 1.16 KB
/
ddl.sql
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
CREATE TABLE SNP (
snp_id int NOT NULL,
snp_chr varchar,
snp_pos varchar,
gene_symb varchar,
anc_allele varchar,
min_allele varchar,
PRIMARY KEY (snp_id)
);
CREATE TABLE Gene (
tax_id varchar,
gene_id int UNIQUE,
gene_symb varchar NOT NULL,
gene_syn varchar,
gene_chr varchar,
gene_pos varchar,
gene_sum text,
gene_type varchar,
gene_mod_date date NOT NULL,
popularity float,
PRIMARY KEY (gene_id)
);
-- CREATE TABLE OMIM (
-- omim_id int,
-- omim_name varchar,
-- gene_symb varchar,
-- PRIMARY KEY (omim_id, gene_symb)
-- );
-- CREATE TABLE DISEASE (
-- disease_name varchar,
-- id varchar,
-- alt_id varchar,
-- PRIMARY KEY (id)
-- );
CREATE TABLE Disease (
disease_name varchar,
id varchar,
alt_id varchar,
gene_symb varchar,
PRIMARY KEY (id, gene_symb)
);
CREATE TABLE Disease_Drug (
drug_name varchar ,
drug_id varchar,
disease_name varchar,
disease_id varchar,
PRIMARY KEY (drug_id, disease_id)
);
CREATE TABLE Toxicity (
drug_id varchar,
tox decimal,
PRIMARY KEY (drug_id)
);
CREATE TABLE Prevalence (
disease_id varchar,
prevalence int,
PRIMARY KEY (disease_id)
);