DROP DATABASE IF EXISTS trii;
CREATE DATABASE trii;
USE trii;

DROP TABLE IF EXISTS organism;
CREATE TABLE organism
(
 		organism_id		INTEGER     NOT NULL    AUTO_INCREMENT,
     	PRIMARY KEY (organism_id),
     	taxonomy_id			INTEGER  			NOT NULL,
     	common_name			VARCHAR(35)			NOT NULL,
     	species				VARCHAR(35)			NOT NULL,
     	UNIQUE (taxonomy_id)
) TYPE=INNODB;  # enforces foreign key constraints

DROP TABLE IF EXISTS gene;
CREATE TABLE gene
(
  		gene_id   INTEGER  NOT NULL  AUTO_INCREMENT,
  		PRIMARY KEY (gene_id),
  		ensembl_gene_id		VARCHAR(50) 		NOT NULL,
  		organism_id			INTEGER  			NULL,
  		name				VARCHAR(35)  		NOT NULL,
  		locuslink			VARCHAR(10) 		NULL,
  		chromosome			TINYINT   			NULL,
  		chromo_start		INTEGER   			NULL,
  		chromo_end			INTEGER   			NULL,
  		description			VARCHAR(255) 		NULL,
  		UNIQUE (ensembl_gene_id),
  		FOREIGN KEY (organism_id) REFERENCES organism(organism_id),
  		INDEX org_indx (organism_id)
) TYPE=INNODB;  # enforces foreign key constraints

# populate organism table
DELETE FROM organism;
INSERT INTO organism (organism_id, taxonomy_id, common_name, species) VALUES ('null', 9606, 'Human', 'Homo sapiens');
INSERT INTO organism (organism_id, taxonomy_id, common_name, species) VALUES ('null', 10090, 'Mouse', 'Mus musculus');
INSERT INTO organism (organism_id, taxonomy_id, common_name, species) VALUES ('null', 10116, 'Rat', 'Rattus norvegicus');

# populate gene table
DELETE FROM gene;
INSERT INTO gene (gene_id, ensembl_gene_id, organism_id, name, locuslink, chromosome, chromo_start, chromo_end, description)
VALUES ('null','ENSG00000186891.3',1,'TNFRSF18',8784,1,1044947,1048147,'Tumor necrosis factor receptor superfamily member 18 precursor (Glucocorticoid-induced TNFR-related protein) (Activation-inducible TNFR family receptor).');

INSERT INTO gene (gene_id, ensembl_gene_id, organism_id, name, locuslink, chromosome, chromo_start, chromo_end, description)
VALUES ('null','ENSG00000078808.4',1,'CAB45',51150,1,1058370,1073469,'45 kDa calcium-binding protein precursor (Cab45) (Stromal cell-derived factor 4) (SDF-4).');

INSERT INTO gene (gene_id, ensembl_gene_id, organism_id, name, locuslink, chromosome, chromo_start, chromo_end, description)
VALUES ('null','ENSG00000176022.1',1,'B3GALT6',126792,1,1073703,1076476,'UDP-Gal:betaGal beta 1,3-galactosyltransferase polypeptide 6; beta-1,3-galactosyltransferase-6; UDP-Gal:betaGlcNAc beta 1,3-galactosyltransferase, polypeptide 6.');

INSERT INTO gene (gene_id, ensembl_gene_id, organism_id, name, locuslink, chromosome, chromo_start, chromo_end, description)
VALUES ('null','ENSG00000160087.5',1,'UBE2J2',118424,1,1095352,1115292,'ubiquitin conjugating enzyme E2, J2 isoform 2; ubiquitin conjugating enzyme 6; yeast UBC6 homolog.');

INSERT INTO gene (gene_id, ensembl_gene_id, organism_id, name, locuslink, chromosome, chromo_start, chromo_end, description)
VALUES ('null','ENSG00000162572.4',1,'SCNN1D',6339,1,1123634,1133467,'Amiloride-sensitive sodium channel delta-subunit (Epithelial Na+ channel delta subunit) (Delta ENaC) (Nonvoltage-gated sodium channel 1 delta subunit) (SCNED) (Delta NaCH).');

INSERT INTO gene (gene_id, ensembl_gene_id, organism_id, name, locuslink, chromosome, chromo_start, chromo_end, description)
VALUES ('null','ENSG00000162576.4',1,'MGC3047',84308,1,1194130,1199973,'limitrin');

INSERT INTO gene (gene_id, ensembl_gene_id, organism_id, name, locuslink, chromosome, chromo_start, chromo_end, description)
VALUES ('null','ENSG00000175756.3',1,'AKIP',54998,1,1215168,1216641,'Aurora-A kinase interacting protein.');

INSERT INTO gene (gene_id, ensembl_gene_id, organism_id, name, locuslink, chromosome, chromo_start, chromo_end, description)
VALUES ('null','ENSG00000131586.2',1,'MRPL20',55052,1,1288703,1294063,'mitochondrial ribosomal protein L20.');

INSERT INTO gene (gene_id, ensembl_gene_id, organism_id, name, locuslink, chromosome, chromo_start, chromo_end, description)
VALUES ('null','ENSG00000179403.2',1,'WARP',64856,1,1322311,1327547,'von Willebrand factor A domain-related protein isoform 1.');

INSERT INTO gene (gene_id, ensembl_gene_id, organism_id, name, locuslink, chromosome, chromo_start, chromo_end, description)
VALUES ('null','ENSG00000160072.5',1,'ATAD3B',83858,1,1358611,1396091,'AAA-ATPase TOB3; AAA-ATPase TOB3.');

INSERT INTO gene (gene_id, ensembl_gene_id, organism_id, name, locuslink, chromosome, chromo_start, chromo_end, description)
VALUES ('null','ENSG00000008128.5',1,'CDC2L2',985,1,1582617,1604060,'PITSLRE serine-threonine kinase CDC2L2 (EC 2.7.1.37) (Galactosyltransferase associated protein kinase p58/GTA) (Cell division cycle 2-like 2) (CDK11).');

INSERT INTO gene (gene_id, ensembl_gene_id, organism_id, name, locuslink, chromosome, chromo_start, chromo_end, description)
VALUES ('null','ENSG00000169911.4',1,'SLC35E2',9906,1,1611978,1625728,'solute carrier family 35, member E2');

INSERT INTO gene (gene_id, ensembl_gene_id, organism_id, name, locuslink, chromosome, chromo_start, chromo_end, description)
VALUES ('null','ENSG00000008130.3',1,'FLJ13052',65220,1,1630975,1659805,'Putative inorganic polyphosphate/ATP-NAD kinase (EC 2.7.1.23) (Poly(P)/ATP NAD kinase).');

INSERT INTO gene (gene_id, ensembl_gene_id, organism_id, name, locuslink, chromosome, chromo_start, chromo_end, description)
VALUES ('null','ENSG00000078369.3',1,'GNB1',2782,1,1665027,1770792, 'Guanine nucleotide-binding protein G(I)/G(S)/G(T) beta subunit 1 (Transducin beta chain 1)');

INSERT INTO gene (gene_id, ensembl_gene_id, organism_id, name, locuslink, chromosome, chromo_start, chromo_end, description)
VALUES ('null','ENSMUSG00000041954.1',2,'TNFRSF18',21936,4,154139702,154142251,'Tumor necrosis factor receptor superfamily member 18 precursor (Glucocorticoid-induced TNFR-related protein) (Activation-inducible TNFR family receptor).'); 

INSERT INTO gene (gene_id, ensembl_gene_id, organism_id, name, locuslink, chromosome, chromo_start, chromo_end, description)
VALUES ('null','ENSMUSG00000023286.1',2,'UBE2J2',140499,4,154057210,154072964,'ubiquitin conjugating enzyme E2, J2 isoform 2; ubiquitin conjugating enzyme 6; yeast UBC6 homolog.');

INSERT INTO gene (gene_id, ensembl_gene_id, organism_id, name, locuslink, chromosome, chromo_start, chromo_end, description)
VALUES ('null','ENSRNOG00000012136.2',3,'FGFA_RAT','null',2,50438931,50445674,'null');

INSERT INTO gene (gene_id, ensembl_gene_id, organism_id, name, locuslink, chromosome, chromo_start, chromo_end, description)
VALUES ('null','ENSRNOG00000016552.2',3,'HMCS_RAT',29637,2,51674090,51681200,'null');