[Gmod-schema] genetic module

[Pinglei Z. <zh...@mo...>]

Hi Linda,

Not sure if I mentioned before, genetic module is still under development, we (FlyBase) are testing on a new 
design which is much different from the one in cvs. Here test I means we try to feed Drosophila genetic to see 
if it will break the schema. welcome to give any suggestions.


Pinglei

 
 
 
 
genetic module currently tested by FlyBase 
 
create table genotype (
       genotype_id serial not null,
       primary key (genotype_id),
       uniquename text not null,      
       description varchar(255),
       unique(uniquename)
);
create index genotype_idx1 on genotype(uniquename);
GRANT ALL on genotype_genotype_id_seq to PUBLIC;
GRANT ALL on genotype to PUBLIC;



create table feature_genotype (
	feature_genotype_id serial not null,
	primary key (feature_genotype_id),
	feature_id int not null,
	foreign key (feature_id) references feature (feature_id) on delete cascade,
	genotype_id int not null,
	foreign key (genotype_id) references genotype (genotype_id) on delete cascade,
	chromosome_id int,
	foreign key (chromosome_id) references feature (feature_id) on delete set null,
	rank int not null,
	cgroup 	int not null,
	cvterm_id int not null,
	foreign key (cvterm_id) references cvterm (cvterm_id) on delete cascade,
	unique(feature_id, genotype_id, cvterm_id)
);
create index feature_genotype_idx1 on feature_genotype (feature_id);
GRANT ALL on feature_genotype to PUBLIC;

create index feature_genotype_idx2 on feature_genotype (genotype_id);
GRANT ALL on feature_genotype to PUBLIC;

create table environment (
	environment_id serial not NULL,
	primary key  (environment_id),
        uniquename text not null,
	description 	text,
	unique(uniquename)
);
create index environment_idx1 on environment(uniquename);


create table env_cvterm (
	env_cvterm_id serial not null,
	primary key  (env_cvterm_id),
	environment_id 	int not null,
	foreign key (environment_id) references environment (environment_id) on delete cascade,
	cvterm_id int not null,
	foreign key (cvterm_id) references cvterm (cvterm_id) on delete cascade,

	unique(environment_id, cvterm_id)
);
create index env_cvterm_idx1 on env_cvterm (environment_id);
create index env_cvterm_idx2 on env_cvterm (cvterm_id);


create table phenotype (
	phenotype_id serial not null,
	primary key (phenotype_id),
        uniquename text not null,  
	observable_id int,
	foreign key (observable_id) references cvterm (cvterm_id) on delete cascade,
	attr_id int,
	foreign key (attr_id) references cvterm (cvterm_id) on delete set null,
	value text,
	cvalue_id int,
	foreign key (cvalue_id) references cvterm (cvterm_id) on delete set null,
	assay_id int,
	foreign key (assay_id) references cvterm (cvterm_id) on delete set null,
	unique( uniquename )	
);
create index phenotype_idx1 on phenotype (cvalue_id);
create index phenotype_idx2 on phenotype (observable_id);
create index phenotype_idx3 on phenotype (attr_id);

GRANT ALL on phenotype_phenotype_id_seq to PUBLIC;
GRANT ALL on phenotype to PUBLIC;

create index phenotype_idx1 on phenotype (type_id);
GRANT ALL on phenotype to PUBLIC;

create index phenotype_idx2 on phenotype (pub_id);
GRANT ALL on phenotype to PUBLIC;

create index phenotype_idx3 on phenotype (background_genotype_id);
GRANT ALL on phenotype to PUBLIC;

create table phenotype_cvterm (
	phenotype_cvterm_id serial not null,
	primary key (phenotype_cvterm_id),
	phenotype_id int not null,
	foreign key (phenotype_id) references phenotype (phenotype_id) on delete cascade,
	cvterm_id int not null,
	foreign key (cvterm_id) references cvterm (cvterm_id) on delete cascade,
	unique(phenotype_id, cvterm_id)
);
	
GRANT ALL on phenotype_cvt_phenotype_cvt_seq to PUBLIC;
GRANT ALL on phenotype_cvterm to PUBLIC;

create index phenotype_cvterm_idx1 on phenotype_cvterm (phenotype_id);
GRANT ALL on phenotype_cvterm to PUBLIC;

create index phenotype_cvterm_idx2 on phenotype_cvterm (cvterm_id);
GRANT ALL on phenotype_cvterm to PUBLIC;



create table phenstatement (
	phenstatement_id serial not null,
	primary key (phenstatement_id),
	genotype_id int not null,
	foreign key (genotype_id) references genotype (genotype_id) on delete cascade,
	environment_id int,
	foreign key (environment_id) references environment (environment_id) on delete cascade,
	phenotype_id int not null,
	foreign key (phenotype_id) references phenotype (phenotype_id) on delete cascade,
	type_id int,
	foreign key (type_id) references cvterm (cvterm_id) on delete cascade,
	pub_id int not null,
	foreign key (pub_id) references pub (pub_id) on delete cascade,
	unique(genotype_id, environment_id, phenotype_id, type_id, pub_id)	
);
create index phenstatement_idx1 on phenstatement (genotype_env_id);
create index phenstatement_idx2 on phenstatement (phenotype_id);



create table feature_phenotype (
       feature_phenotype_id serial not null,
       primary key (feature_phenotype_id),
       feature_id int not null,
       foreign key (feature_id) references feature (feature_id) on delete cascade,
       phenotype_id int not null,
       foreign key (phenotype_id) references phenotype (phenotype_id) on delete cascade,
       unique(feature_id,phenotype_id)       
);
GRANT ALL on feature_pheno_feature_pheno_seq to PUBLIC;
GRANT ALL on feature_phenotype to PUBLIC;

create index feature_phenotype_idx1 on feature_phenotype (feature_id);
GRANT ALL on feature_phenotype to PUBLIC;

create index feature_phenotype_idx2 on feature_phenotype (phenotype_id);
GRANT ALL on feature_phenotype to PUBLIC;


create table phendesc (
	phendesc_id serial not null,
	primary key (phendesc_id),
	genotype_id int not null;
        foreign key (genotype_id) references genotype (genotype_id) on delete cascade,
	environment_id int;
        foreign key (environment_id) references environment ( environment_id) on delete cascade,
	description text not null,
	pub_id int not null,
	foreign key (pub_id) references pub (pub_id) on delete cascade,

	unique(genotype_id, environment_id, pub_id)
);
create index phendesc_idx1 on phendesc (genotype_id);
create index phendesc_idx2 on phendesc (environment_id);
create index phendesc_idx3 on phendesc (pub_id);



create table phenotype_comparison (
	phenotype_comparison_id serial not null,
	primary key (phenotype_comparison_id),
	genotype1_id int not null,
        foreign key (genotype1_id) references genotype (genotype_id) on delete cascade,
	environment1_id int,
        foreign key (environment1_id) references environment (environment_id) on delete cascade,
	genotype2_id int not null,
        foreign key (genotype2_id) references genotype (genotype_id) on delete cascade,
	environment2_id int,
        foreign key (environment2_id) references environment (environment_id) on delete cascade,
	phenotype1_id int not null,
        foreign key (phenotype1_id) references phenotype (phenotype_id) on delete cascade,
	phenotype2_id int,
        foreign key (phenotype2_id) references phenotype (phenotype_id) on delete cascade,
	type_id int not null,
        foreign key (type_id) references cvterm (cvterm_id) on delete cascade,
	pub_id 	int not null,
        foreign key (pub_id) references pub (pub_id) on delete cascade,
	unique(genotype1_id, environment1_id, genotype2_id, environment2_id, phenotype1_id, phenotype2_id, 
type_id, 
pub_id)
)