CREATE TABLE mutant
(
	mutant_id		number(11) not null		CONSTRAINT pk_mutant PRIMARY KEY,
	mutant_name		varchar2(128) not null,
	mutant_symbol		varchar2(16) not null,
	mutant_description 	varchar2(4000) not null,
	UNIQUE(mutant_name)
);
--- UNIQUE(mutant_name, mutant_description)

CREATE TABLE rough_mutant_data
(
	mutant_id		number(11) not null	REFERENCES mutant,
	rough_mutant_data	varchar2(4000) not null,
	UNIQUE(mutant_id)
);

----------------------------------------------------------
--- trivia tables start
----------------------------------------------------------
CREATE TABLE object_to_evidence_code
(
	object_to_evidence_code_id     	number(11) not null	CONSTRAINT pk_object_to_evidence_code_id PRIMARY KEY,
    	object_table             	varchar2(128) not null,
    	object_id                     	number(11) not null,
    	evidence_code			varchar2(64) not null,
	UNIQUE(object_table, object_id, evidence_code)
);

CREATE TABLE mutant_synonym
(
	mutant_id	number(11) not null		REFERENCES mutant,
	mutant_synonym	varchar2(128) not null,
	UNIQUE(mutant_id, mutant_synonym)
);

CREATE TABLE mutagenesis
(
	mutagenesis_id		number(11) not null		CONSTRAINT pk_mutagenesis_type PRIMARY KEY,
	mutagen			varchar2(128) not null,
	mutagenesis_method	varchar2(128) not null,
	UNIQUE(mutagen, mutagenesis_method)
);

CREATE TABLE developmental_stage
(
	developmental_stage_id	number(11) not null	CONSTRAINT pk_developmental_stage PRIMARY KEY,
	description		varchar2(32) not null,
	UNIQUE(description)
);

CREATE TABLE geographical_location
(
	geographical_location_id	number(11) not null	CONSTRAINT pk_geographical_location PRIMARY KEY,
	location_name			varchar2(256) not null,
	latitude_degree			number(2),
	latitude_minute			number(2),
	is_north			number(1) 		CHECK(is_north = 0 OR is_north = 1),
	longitude_degree		number(2),
	longitude_minute		number(2),
	is_west				number(1)		CHECK(is_west = 0 OR is_west = 1),
	aptitude			number,
	UNIQUE(location_name)
);
---	UNIQUE(location_name, latitude_degree,latitude_minute, is_north, longitude_degree, longitude_minute, is_west)

CREATE TABLE image
(
	image_id		number(11) not null		CONSTRAINT pk_image PRIMARY KEY,
	file_name		varchar2(128)	not null,
	image_comment		varchar2(1024),
	UNIQUE(file_name)	
);

CREATE TABLE object_to_image
(
	object_to_image_id	number(11) not null		CONSTRAINT pk_object_to__image PRIMARY KEY,
	object_table		varchar2(128) not null,
	object_id		number(11) not null,
	image_id		number(11) not null	REFERENCES image,
	UNIQUE(object_table, object_id, image_id)
);
COMMENT ON TABLE object_to_image is
'object_id is the same as row_id';

CREATE TABLE object_to_linkout
(
	object_to_linkout_id	number(11) not null		CONSTRAINT pk_object_to_linkout PRIMARY KEY,
	object_table		varchar2(128) not null,
	object_id		number(11) not null,
	url_link		varchar2(128) not null,
	UNIQUE(object_table, object_id,  url_link)
);

CREATE TABLE object_to_gene_identity
(
	object_to_gene_identity_id	number(11) not null	CONSTRAINT pk_object_to_gene_identity PRIMARY KEY,
	object_table		varchar2(128) not null,
	object_id		number(11) not null,
	gene_identity_accession	varchar2(32)
);
COMMENT on column object_to_gene_identity.gene_identity_accession is
'GI (Genbank Index)';

CREATE TABLE object_to_gene_product
(
	object_to_gene_product_id	number(11) not null	CONSTRAINT pk_object_to_gene_product PRIMARY KEY,
	object_table		varchar2(128) not null,
	object_id		number(11) not null,	
	gene_product_accession	varchar2(32)
);
COMMENT on column object_to_gene_product.gene_product_accession is
'swiss-prot / trembl accession';

CREATE TABLE object_to_modification
(
	object_to_modification		number(11) not null	CONSTRAINT pk_object_to_modification PRIMARY KEY,
	object_table		varchar2(128) not null,
	object_id		number(11) not null,
	last_update		date	not null,	
---	curator_id		number(11) not null		REFERENCES ontology.contributor(contributor_id),
	curator_id		number(11) not null,
	style_checker		varchar2(16),
	UNIQUE(object_table, object_id, curator_id, last_update)	
);

----------------------------------------------------------
--- trivia tables end
----------------------------------------------------------

CREATE TABLE phenotype_expression
(
	phenotype_expression_id	number(11) not null		CONSTRAINT pk_phenotype_expression PRIMARY KEY,
	to_id			varchar2(11) not null,
	developmental_stage_id	varchar2(11) not null,
	anatomy_id		varchar2(64) not null,
	go_id			number(11) not null,
	trait_score		varchar(16) not null,
	UNIQUE(to_id, developmental_stage_id, anatomy_id, go_id, trait_score)
);
---	to_id			number(11) not null,
---	developmental_stage_id	number(11) not null,


CREATE TABLE object_to_phenotype_expression
(
	ot_phenotype_expression_id	number(11) not null	CONSTRAINT pk_ot_phenotype PRIMARY KEY,
	object_table		varchar2(128) not null,
	object_id		number(11) not null,
	phenotype_expression_id	number(11) not null		REFERENCES phenotype_expression,
	UNIQUE(object_table, object_id, phenotype_expression_id)
);

CREATE TABLE germplasm_info
(
	germplasm_info_id	number(11) not null		CONSTRAINT pk_germplasm_info PRIMARY KEY,
	accession		varchar2(128) not null,
	geographical_location_id	number(11) not null	REFERENCES geographical_location,
	mutagenesis_id		number(11) not null		REFERENCES mutagenesis,
	wild_type		varchar2(128),
	genus			varchar2(128),
	species_id		number(11),
	subspecies_id		number(11),
---	species_id		number(11)			REFERENCES ontology.species(species_id),
---	subspecies_id		number(11) 			REFERENCES ontology.subspecies(subspecies_id),
	UNIQUE(accession)
);

CREATE TABLE object_to_germplasm_info
(
	object_to_germplasm_info_id	number(11) not null 	CONSTRAINT pk_ot_germplasm_info PRIMARY KEY,
	object_table		varchar2(128) not null,
	object_id		number(11) not null,	
	germplasm_info_id	number(11) not null		REFERENCES germplasm_info,
	UNIQUE(object_table, object_id, germplasm_info_id)
);

CREATE TABLE study
(
	study_id		number(11) not null		CONSTRAINT pk_study_id	PRIMARY KEY,
	study_name		varchar2(64) not null,
	year			number(11) not null,
	season			varchar2(64) not null,
	geographical_location_id	number(11) not null	REFERENCES geographical_location, 	
	study_type		number(11) not null		CHECK(study_type = 1 OR study_type = 2 OR study_type =3 OR study_type =4),
	environmental_factors	varchar2(1024),
	UNIQUE(study_name)
);
COMMENT on TABLE study is
'no evidence - connect with reference using REFERENCE_TO_OBJECT table';
COMMENT on column study.study_type is
'field study or greenhouse or chamber';

CREATE TABLE object_to_study
(
	object_to_study_id	number(11) not null 	CONSTRAINT pk_object_to_study PRIMARY KEY,
	object_table		varchar2(128) not null,
	object_id		number(11) not null,	
	study_id		number(11) not null	REFERENCES study,
	UNIQUE(object_table, object_id, study_id)	
);

CREATE TABLE allele
(
	allele_id		number(11) not null		CONSTRAINT pk_allele PRIMARY KEY,
	allele_designation	varchar2(64) not null,
	allele_description	varchar2(2048) not null,
	allele_interaction_description	varchar2(2048),
	UNIQUE(allele_designation)
);

CREATE TABLE object_to_allele
(
	object_to_allele_id	number(11) not null		CONSTRAINT pk_object_to_allele PRIMARY KEY,
	object_table		varchar2(128) not null,
	object_id		number(11) not null,
	allele_id		number(11) not null		REFERENCES allele,
	UNIQUE(object_table, object_id, allele_id)
);

--- CREATE OR REPLACE VIEW local_map
--- as
--- select map_id, map_name
--- from cmap.map;
--- grant references on map to ...

CREATE TABLE map_position
(
	map_position_id		number(11) not null	CONSTRAINT pk_map_position	PRIMARY KEY,
	map_type		number(11),	
	chromosome_number	number(11),	
	closed_linked_markers	varchar2(128),
	curator_id		number(11) not null,
	curator_comment		varchar2(4000),
	start_position		number(9,2),
	end_position		number(9,2),
	UNIQUE(map_type, chromosome_number, start_position, end_position)
);

---	map_id			number(11) 		REFERENCES cmap.map,
---	curator_id		number(11) not null	REFERENCES ontology.contributor(contributor_id),

CREATE TABLE object_to_map_position
(
	object_to_map_position_id	number(11) not null 	CONSTRAINT pk_object_to_map_position PRIMARY KEY,
	object_table		varchar2(128) not null,
	object_id		number(11) not null,	
	map_position_id		number(11) not null	REFERENCES map_position,
	UNIQUE(object_table, object_id, map_position_id)	
);
--- clone_accession references gene_product
--- curator_comment, for example, tells how to get start_position and end_position for JRGP maps

CREATE VIEW temp AS
SELECT O.object_id, MIN(M.chromosome_number) AS chromosome_number	
FROM map_position M, object_to_map_position O
WHERE M.map_position_id = O.map_position_id
GROUP BY O.object_id;

CREATE VIEW mutant_profile AS
SELECT mutant.mutant_id, mutant.mutant_name, mutant.mutant_symbol, mutant_synonym.mutant_synonym, Temp.chromosome_number 
FROM mutant, mutant_synonym, temp
WHERE mutant.mutant_id = mutant_synonym.mutant_id(+)
  AND mutant.mutant_id = Temp.object_id(+);

select * from tab;