CREATE TABLE CHROMOSOME ( 
	CHROMOSOME_ID NUMERIC(10) NOT NULL,
	CHROMOSOME_NUMBER VARCHAR(50) NOT NULL,
	TAXON_ID NUMERIC(10) NOT NULL,
	PRIMARY KEY(CHROMOSOME_ID)
) 
;

CREATE TABLE CLONE ( 
	CLONE_ID NUMERIC(10) NOT NULL,
	INSERT_SIZE NUMERIC(10),
	CLONE_NAME VARCHAR(50),
	VERSION VARCHAR(100),
	ACCESSION_NUMBER VARCHAR(15),
	CLONE_STRAIN VARCHAR(50),
	LIBRARY_ID NUMERIC(10),
	VERIFIED BOOL DEFAULT 0 NOT NULL,
	PRIMARY KEY(CLONE_ID)
) 
;

CREATE TABLE GENE ( 
	GENE_ID NUMERIC(10) NOT NULL,
	CLUSTER_ID NUMERIC(10),
	GENE_TITLE VARCHAR(50),
	LOCUS_LINK_ID VARCHAR(50),
	GENE_SYMBOL VARCHAR(50),
	OMIM_ID VARCHAR(50),
	LOCUS_LINK_SUMMARY TEXT,
	CHROMOSOME_ID NUMERIC(10),
	TAXON_ID NUMERIC(10),
	PRIMARY KEY(GENE_ID)
) 
;

CREATE TABLE GENE_SEQUENCE ( 
	GENE_ID NUMERIC(10) NOT NULL,
	SEQUENCE_ID NUMERIC(10) NOT NULL,
	PRIMARY KEY(GENE_ID, SEQUENCE_ID)
) 
;

CREATE TABLE GENE_TARGET ( 
	GENE_ID NUMERIC(10) NOT NULL,
	TARGET_ID NUMERIC(10) NOT NULL,
	PRIMARY KEY(GENE_ID, TARGET_ID)
) 
;

CREATE TABLE LIBRARY ( 
	LIBRARY_ID NUMERIC(10) NOT NULL,
	LIBRARY_TYPE VARCHAR(50),
	LIBRARY_NAME VARCHAR(50),
	DESCRIPTION VARCHAR(50),
	KEYWORD VARCHAR(50),
	R_SITE_1 VARCHAR(50),
	R_SITE_2 VARCHAR(50),
	SEQUENCES_TO_DATE NUMERIC(10),
	CLONES_TO_DATE NUMERIC(10),
	UNIGENE_ID NUMERIC(10),
	CREATE_DATE DATE,
	LAB_HOST VARCHAR(50),
	PRIMARY KEY(LIBRARY_ID)
) 
;

CREATE TABLE LIBRARY_GENE ( 
	GENE_ID NUMERIC(10) NOT NULL,
	LIBRARY_ID NUMERIC(10) NOT NULL,
	PRIMARY KEY(GENE_ID, LIBRARY_ID)
) 
;

CREATE TABLE MONKEY ( 
	MONKEY_ID NUMERIC(10) NOT NULL,
	NAME VARCHAR(50),
	TARGET_ID NUMERIC(10),
	PRIMARY KEY(MONKEY_ID)
) 
;

CREATE TABLE SEQUENCE ( 
	SEQUENCE_ID NUMERIC(10) NOT NULL,
	ACCESSION_NUMBER VARCHAR(50),
	IS_REF_SEQUENCE BOOL DEFAULT 0 NOT NULL,
	DESCRIPTION VARCHAR(4),
	ASCII_STRING TEXT,
	LENGTH NUMERIC(10),
	SEQUENCE_TYPE NUMERIC(10),
	VERSION VARCHAR(10),
	CLONE_ID NUMERIC(10),
	PRIMARY KEY(SEQUENCE_ID)
) 
;

CREATE TABLE TARGET ( 
	TARGET_ID NUMERIC(10) NOT NULL,
	TARGET_NAME VARCHAR(50),
	TARGET_TYPE CHAR(50),
	PRIMARY KEY(TARGET_ID)
) 
;

CREATE TABLE TAXON ( 
	TAXON_ID NUMERIC(10) NOT NULL,
	SCIENTIFIC_NAME VARCHAR(50),
	STRAIN_OR_ETHNICITY VARCHAR(50),
	COMMON_NAME VARCHAR(50),
	PREFERRED BOOL DEFAULT 0 NOT NULL,
	ABBREVIATION VARCHAR(50),
	PRIMARY KEY(TAXON_ID)
) 
;



CREATE INDEX idx_TAXON_ID ON CHROMOSOME(TAXON_ID)
;
CREATE INDEX idx_LIBRARY_ID ON CLONE(LIBRARY_ID)
;
CREATE INDEX idx_CHROMOSOME_ID ON GENE(CHROMOSOME_ID)
;
CREATE INDEX idx_TAXON_ID ON GENE(TAXON_ID)
;
CREATE INDEX idx_GENE_ID ON GENE_SEQUENCE(GENE_ID)
;
CREATE INDEX idx_SEQUENCE_ID ON GENE_SEQUENCE(SEQUENCE_ID)
;
CREATE INDEX idx_GENE_ID ON GENE_TARGET(GENE_ID)
;
CREATE INDEX idx_TARGET_ID ON GENE_TARGET(TARGET_ID)
;
CREATE INDEX idx_GENE_ID ON LIBRARY_GENE(GENE_ID)
;
CREATE INDEX idx_LIBRARY_ID ON LIBRARY_GENE(LIBRARY_ID)
;
CREATE INDEX idx_TARGET_ID ON MONKEY(TARGET_ID)
;
CREATE INDEX idx_CLONE_ID ON SEQUENCE(CLONE_ID)
;


ALTER TABLE CHROMOSOME ADD CONSTRAINT FK_CHROMOSOME_TAXON 
FOREIGN KEY (TAXON_ID) REFERENCES TAXON (TAXON_ID)
;

ALTER TABLE CLONE ADD CONSTRAINT FK_CLONE_LIBRARY 
FOREIGN KEY (LIBRARY_ID) REFERENCES LIBRARY (LIBRARY_ID)
;

ALTER TABLE GENE ADD CONSTRAINT FK_GENE_TAXON 
FOREIGN KEY (TAXON_ID) REFERENCES TAXON (TAXON_ID)
;

ALTER TABLE GENE_SEQUENCE ADD CONSTRAINT FK_GENE_SEQUENCE_GENE 
FOREIGN KEY (GENE_ID) REFERENCES GENE (GENE_ID)
;

ALTER TABLE GENE_SEQUENCE ADD CONSTRAINT FK_GENE_SEQUENCE_SEQUENCE 
FOREIGN KEY (SEQUENCE_ID) REFERENCES SEQUENCE (SEQUENCE_ID)
;

ALTER TABLE GENE_TARGET ADD CONSTRAINT FK_GENE_TARGET_GENE 
FOREIGN KEY (GENE_ID) REFERENCES GENE (GENE_ID)
;

ALTER TABLE GENE_TARGET ADD CONSTRAINT FK_GENE_TARGET_TARGET 
FOREIGN KEY (TARGET_ID) REFERENCES TARGET (TARGET_ID)
;

ALTER TABLE LIBRARY_GENE ADD CONSTRAINT FK_LIBRARY_GENE_GENE 
FOREIGN KEY (GENE_ID) REFERENCES GENE (GENE_ID)
;

ALTER TABLE LIBRARY_GENE ADD CONSTRAINT FK_LIBRARY_GENE_LIBRARY 
FOREIGN KEY (LIBRARY_ID) REFERENCES LIBRARY (LIBRARY_ID)
;

ALTER TABLE MONKEY ADD CONSTRAINT monkey 
FOREIGN KEY (TARGET_ID) REFERENCES TARGET (TARGET_ID)
;

ALTER TABLE SEQUENCE ADD CONSTRAINT FK_SEQUENCE_CLONE 
FOREIGN KEY (CLONE_ID) REFERENCES CLONE (CLONE_ID)
;
