--- to make this database, type
---
--- "createdb MYDBNAME"
--- psql -a MYDBNAME < toy-example.sql

CREATE TABLE gene (
        gene_acc                CHAR(12) NOT NULL PRIMARY KEY,
        symbol                  VARCHAR(255) NOT NULL,
        cytology                VARCHAR(16),

        UNIQUE (symbol)
);
CREATE TABLE gene_function (
        gene_acc                CHAR(12),
        FOREIGN KEY (gene_acc) REFERENCES gene(gene_acc),
        mol_function            VARCHAR(128)
);
CREATE TABLE protein (
        protein_acc             CHAR(12) NOT NULL PRIMARY KEY,
        molecular_weight        FLOAT,
        protein_seq             TEXT
);
CREATE TABLE transcript (
        transcript_acc          CHAR(12) NOT NULL PRIMARY KEY,
        gene_acc                CHAR(12),
        FOREIGN KEY (gene_acc) REFERENCES gene(gene_acc),
        protein_acc             CHAR(12),
        FOREIGN KEY (protein_acc) REFERENCES protein(protein_acc),
        mrna_seq                TEXT
);
CREATE TABLE exon (
        exon_acc                CHAR(12) NOT NULL PRIMARY KEY,
        seq_start               INTEGER,
        seq_end                 INTEGER,
        seq_strand              SMALLINT,
        chromosome_arm          CHAR(2)
);
CREATE TABLE exon_to_transcript (
        exon_acc                CHAR(12),
        FOREIGN KEY (exon_acc) REFERENCES exon(exon_acc),
        transcript_acc                CHAR(12),
        FOREIGN KEY (transcript_acc) REFERENCES transcript(transcript_acc),
        exon_rank               INTEGER,

        UNIQUE (exon_acc, transcript_acc)
);

INSERT INTO gene VALUES ('gn00002     ','foo','35A7');
INSERT INTO gene VALUES ('gn00003     ','bar','60F1');
INSERT INTO gene VALUES ('gn00004     ','whee','58C2');
INSERT INTO gene_function VALUES ('gn00002     ','transmembrane receptor');
INSERT INTO gene_function VALUES ('gn00003     ','glutamine-tRNA ligase');
INSERT INTO gene_function VALUES ('gn00003     ','ATP binding');
INSERT INTO gene_function VALUES ('gn00004     ','GPCR');
INSERT INTO gene_function VALUES ('gn00004     ','taste receptor');
INSERT INTO protein VALUES ('gn00002P1   ',62,'MQVVQVQVQVQSCSCS');
INSERT INTO protein VALUES ('gn00003P1   ',55.8,'MSVXXXXXQLQLQ');
INSERT INTO protein VALUES ('gn00003P2   ',57.2,'MSVXXXXXSSSSSSSVVV');
INSERT INTO protein VALUES ('gn00004P1   ',82.3,'MLSTRQQCSCSXXXXLV');
INSERT INTO transcript VALUES ('gn00002A    ','gn00002     ','gn00002P1   ','ATGAATTGCGGCTTNNNNNNNATT');
INSERT INTO transcript VALUES ('gn00003A    ','gn00003     ','gn00003P1   ','ATGCCGTGATAGCTAGCGTA');
INSERT INTO transcript VALUES ('gn00003B    ','gn00003     ','gn00003P2   ','ATGCCGTGATAGTTTTGGGGGG');
INSERT INTO transcript VALUES ('gn00004A    ','gn00004     ','gn00004P1   ','GTGATGTGGCTGNNNNNTTGTTAA');
INSERT INTO exon VALUES ('gn00002X1   ',1,100,1,'2L');
INSERT INTO exon VALUES ('gn00002X2   ',201,200,1,'2L');
INSERT INTO exon VALUES ('gn00003X2   ',2001,2200,1,'X ');
INSERT INTO exon VALUES ('gn00003X1   ',1001,1200,1,'X ');
INSERT INTO exon VALUES ('gn00003X3   ',1401,1600,1,'X ');
INSERT INTO exon VALUES ('gn00004X1   ',4389,4418,1,'3L');
INSERT INTO exon VALUES ('gn00004X2   ',4890,5101,1,'3L');
INSERT INTO exon VALUES ('gn00004X3   ',5812,6043,1,'3L');
INSERT INTO exon_to_transcript VALUES ('gn00002X1   ','gn00002A    ',1);
INSERT INTO exon_to_transcript VALUES ('gn00002X2   ','gn00002A    ',2);
INSERT INTO exon_to_transcript VALUES ('gn00003X1   ','gn00003A    ',1);
INSERT INTO exon_to_transcript VALUES ('gn00003X2   ','gn00003A    ',2);
INSERT INTO exon_to_transcript VALUES ('gn00003X1   ','gn00003B    ',1);
INSERT INTO exon_to_transcript VALUES ('gn00003X3   ','gn00003B    ',2);
INSERT INTO exon_to_transcript VALUES ('gn00004X1   ','gn00004A    ',1);
INSERT INTO exon_to_transcript VALUES ('gn00004X2   ','gn00004A    ',2);
INSERT INTO exon_to_transcript VALUES ('gn00004X3   ','gn00004A    ',3);