[Gmod-schema] Re: genetic module

[Linda S. <Lin...@cg...>]

Hi Pinglei,

This schema looks good (it solves the unique observable_id problem, and=20=

much more).  But, I still have two questions (maybe it is one=20
question):

Where do you put genetic interactions (suppresion, enhancement, etc.)?

What is the purpose of the phenotype_comparison table?

Thank you,
Linda




On Feb 23, 2005, at 4:45 PM, Pinglei Zhou wrote:

> Hi Linda,
>
> Not sure if I mentioned before, genetic module is still under=20
> development, we (FlyBase) are testing on a new
> design which is much different from the one in cvs. Here test I means=20=

> 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=20
> cascade,
> 	genotype_id int not null,
> 	foreign key (genotype_id) references genotype (genotype_id) on =
delete=20
> cascade,
> 	chromosome_id int,
> 	foreign key (chromosome_id) references feature (feature_id) on =
delete=20
> 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=20=

> 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)=20
> on delete cascade,
> 	cvterm_id int not null,
> 	foreign key (cvterm_id) references cvterm (cvterm_id) on delete=20=

> 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=20
> cascade,
> 	attr_id int,
> 	foreign key (attr_id) references cvterm (cvterm_id) on delete =
set=20
> null,
> 	value text,
> 	cvalue_id int,
> 	foreign key (cvalue_id) references cvterm (cvterm_id) on delete =
set=20
> null,
> 	assay_id int,
> 	foreign key (assay_id) references cvterm (cvterm_id) on delete =
set=20
> null,
> 	unique( uniquename )=09
> );
> 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=20
> delete cascade,
> 	cvterm_id int not null,
> 	foreign key (cvterm_id) references cvterm (cvterm_id) on delete=20=

> cascade,
> 	unique(phenotype_id, cvterm_id)
> );
> =09
> 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=20
> cascade,
> 	environment_id int,
> 	foreign key (environment_id) references environment =
(environment_id)=20
> on delete cascade,
> 	phenotype_id int not null,
> 	foreign key (phenotype_id) references phenotype (phenotype_id) =
on=20
> 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)=09
> );
> 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=20
> delete cascade,
>        phenotype_id int not null,
>        foreign key (phenotype_id) references phenotype (phenotype_id)=20=

> 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=20
> (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=20=

> delete cascade,
> 	environment_id int;
>         foreign key (environment_id) references environment (=20
> 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)=20=

> on delete cascade,
> 	environment1_id int,
>         foreign key (environment1_id) references environment=20
> (environment_id) on delete cascade,
> 	genotype2_id int not null,
>         foreign key (genotype2_id) references genotype (genotype_id)=20=

> on delete cascade,
> 	environment2_id int,
>         foreign key (environment2_id) references environment=20
> (environment_id) on delete cascade,
> 	phenotype1_id int not null,
>         foreign key (phenotype1_id) references phenotype=20
> (phenotype_id) on delete cascade,
> 	phenotype2_id int,
>         foreign key (phenotype2_id) references phenotype=20
> (phenotype_id) on delete cascade,
> 	type_id int not null,
>         foreign key (type_id) references cvterm (cvterm_id) on delete=20=

> 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,=20
> phenotype1_id, phenotype2_id,
> type_id,
> pub_id)
> )
>
Linda Sperling
Centre de G=E9n=E9tique Mol=E9culaire
CNRS
Avenue de la Terrasse
91198 Gif-sur-Yvette CEDEX
FRANCE

spe...@cg...
+33 (0)1 69 82 32 09 	(telephone)
+33 (0)1 69 82 31 50	(fax)
http://paramecium.cgm.cnrs-gif.fr

SAUVONS LA RECHERCHE
http://recherche-en-danger.apinc.org