Biojoin/ddl.sql at main · Tsatsch/Biojoin · GitHub

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)
);