Re: [Gmod-schema] Re: genetic module

[Chris M. <cj...@fr...>]

On Wed, 23 Feb 2005, Pinglei Zhou wrote:

>
> >Hi Pinglei,
> >
> >This schema looks good (it solves the unique observable_id problem, and
> >much more).  But, I still have two questions (maybe it is one
> >question):
> >
> >Where do you put genetic interactions (suppresion, enhancement, etc.)?
>
> it should involve phenstatement/phenotype_comparison tables.
>
> >What is the purpose of the phenotype_comparison table?
>
> phenotype_comparison will replace the old
> interaction/interactionobject/interaction/subject table

I don't think this is an adequate explanation! The
interaction/interactionobject/interaction/subject tables were removed from
the genetic module at the end of 2003.

I was under the impression that the version of the genetic module that was
in cvs was the latest one, and relatively stable. This is the version
based around 'gcontext's, and reasonably well commented. It seems that
this has been abandoned and there is now a new version. It would therefore
seem like a good idea to commit this new one to cvs and to add some
comments to it

Cheers
Chris

> Pinglei
>
>
> >
> >On Feb 23, 2005, at 4:45 PM, Pinglei Zhou wrote:
> >
> >> 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 (
> >> =09feature_genotype_id serial not null,
> >> =09primary key (feature_genotype_id),
> >> =09feature_id int not null,
> >> =09foreign key (feature_id) references feature (feature_id) on delete
> >> cascade,
> >> =09genotype_id int not null,
> >> =09foreign key (genotype_id) references genotype (genotype_id) on dele=
te
> >> cascade,
> >> =09chromosome_id int,
> >> =09foreign key (chromosome_id) references feature (feature_id) on dele=
te
> >> set null,
> >> =09rank int not null,
> >> =09cgroup =09int not null,
> >> =09cvterm_id int not null,
> >> =09foreign key (cvterm_id) references cvterm (cvterm_id) on delete
> >> cascade,
> >> =09unique(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 (
> >> =09environment_id serial not NULL,
> >> =09primary key  (environment_id),
> >>         uniquename text not null,
> >> =09description =09text,
> >> =09unique(uniquename)
> >> );
> >> create index environment_idx1 on environment(uniquename);
> >>
> >>
> >> create table env_cvterm (
> >> =09env_cvterm_id serial not null,
> >> =09primary key  (env_cvterm_id),
> >> =09environment_id =09int not null,
> >> =09foreign key (environment_id) references environment (environment_id=
)
> >> on delete cascade,
> >> =09cvterm_id int not null,
> >> =09foreign key (cvterm_id) references cvterm (cvterm_id) on delete
> >> cascade,
> >>
> >> =09unique(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 (
> >> =09phenotype_id serial not null,
> >> =09primary key (phenotype_id),
> >>         uniquename text not null,
> >> =09observable_id int,
> >> =09foreign key (observable_id) references cvterm (cvterm_id) on delete
> >> cascade,
> >> =09attr_id int,
> >> =09foreign key (attr_id) references cvterm (cvterm_id) on delete set
> >> null,
> >> =09value text,
> >> =09cvalue_id int,
> >> =09foreign key (cvalue_id) references cvterm (cvterm_id) on delete set
> >> null,
> >> =09assay_id int,
> >> =09foreign key (assay_id) references cvterm (cvterm_id) on delete set
> >> null,
> >> =09unique( 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 (
> >> =09phenotype_cvterm_id serial not null,
> >> =09primary key (phenotype_cvterm_id),
> >> =09phenotype_id int not null,
> >> =09foreign key (phenotype_id) references phenotype (phenotype_id) on
> >> delete cascade,
> >> =09cvterm_id int not null,
> >> =09foreign key (cvterm_id) references cvterm (cvterm_id) on delete
> >> cascade,
> >> =09unique(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 (
> >> =09phenstatement_id serial not null,
> >> =09primary key (phenstatement_id),
> >> =09genotype_id int not null,
> >> =09foreign key (genotype_id) references genotype (genotype_id) on dele=
te
> >> cascade,
> >> =09environment_id int,
> >> =09foreign key (environment_id) references environment (environment_id=
)
> >> on delete cascade,
> >> =09phenotype_id int not null,
> >> =09foreign key (phenotype_id) references phenotype (phenotype_id) on
> >> delete cascade,
> >> =09type_id int,
> >> =09foreign key (type_id) references cvterm (cvterm_id) on delete casca=
de,
> >> =09pub_id int not null,
> >> =09foreign key (pub_id) references pub (pub_id) on delete cascade,
> >> =09unique(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 (
> >> =09phendesc_id serial not null,
> >> =09primary key (phendesc_id),
> >> =09genotype_id int not null;
> >>         foreign key (genotype_id) references genotype (genotype_id) on
> >> delete cascade,
> >> =09environment_id int;
> >>         foreign key (environment_id) references environment (
> >> environment_id) on delete cascade,
> >> =09description text not null,
> >> =09pub_id int not null,
> >> =09foreign key (pub_id) references pub (pub_id) on delete cascade,
> >>
> >> =09unique(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 (
> >> =09phenotype_comparison_id serial not null,
> >> =09primary key (phenotype_comparison_id),
> >> =09genotype1_id int not null,
> >>         foreign key (genotype1_id) references genotype (genotype_id)
> >> on delete cascade,
> >> =09environment1_id int,
> >>         foreign key (environment1_id) references environment
> >> (environment_id) on delete cascade,
> >> =09genotype2_id int not null,
> >>         foreign key (genotype2_id) references genotype (genotype_id)
> >> on delete cascade,
> >> =09environment2_id int,
> >>         foreign key (environment2_id) references environment
> >> (environment_id) on delete cascade,
> >> =09phenotype1_id int not null,
> >>         foreign key (phenotype1_id) references phenotype
> >> (phenotype_id) on delete cascade,
> >> =09phenotype2_id int,
> >>         foreign key (phenotype2_id) references phenotype
> >> (phenotype_id) on delete cascade,
> >> =09type_id int not null,
> >>         foreign key (type_id) references cvterm (cvterm_id) on delete
> >> cascade,
> >> =09pub_id =09int not null,
> >>         foreign key (pub_id) references pub (pub_id) on delete cascade=
,
> >> =09unique(genotype1_id, environment1_id, genotype2_id, environment2_id=
,
> >> 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 =09(telephone)
> >+33 (0)1 69 82 31 50=09(fax)
> >http://paramecium.cgm.cnrs-gif.fr
> >
> >SAUVONS LA RECHERCHE
> >http://recherche-en-danger.apinc.org
> >
>
>
>
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real users.
> Discover which products truly live up to the hype. Start reading now.
> http://ads.osdn.com/?ad_ide95&alloc_id=14396&op=3Dclick
> _______________________________________________
> Gmod-schema mailing list
> Gmo...@li...
> https://lists.sourceforge.net/lists/listinfo/gmod-schema
>