[Gmod-schema] Re: genetic module

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

=20
>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.)?

it should involve phenstatement/phenotype_comparison tables.

>What is the purpose of the phenotype_comparison table?

phenotype_comparison will replace the old=20
interaction/interactionobject/interaction/subject table


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=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 (
>> =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=20
>> cascade,
>> =09genotype_id int not null,
>> =09foreign key (genotype_id) references genotype (genotype_id) on delete=
=20
>> cascade,
>> =09chromosome_id int,
>> =09foreign key (chromosome_id) references feature (feature_id) on delete=
=20
>> 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=20
>> 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)=
=20
>> on delete cascade,
>> =09cvterm_id int not null,
>> =09foreign key (cvterm_id) references cvterm (cvterm_id) on delete=20
>> 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=
=20
>> cascade,
>> =09attr_id int,
>> =09foreign key (attr_id) references cvterm (cvterm_id) on delete set=20
>> null,
>> =09value text,
>> =09cvalue_id int,
>> =09foreign key (cvalue_id) references cvterm (cvterm_id) on delete set=
=20
>> null,
>> =09assay_id int,
>> =09foreign key (assay_id) references cvterm (cvterm_id) on delete set=20
>> null,
>> =09unique( 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 (
>> =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=20
>> delete cascade,
>> =09cvterm_id int not null,
>> =09foreign key (cvterm_id) references cvterm (cvterm_id) on delete=20
>> cascade,
>> =09unique(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 (
>> =09phenstatement_id serial not null,
>> =09primary key (phenstatement_id),
>> =09genotype_id int not null,
>> =09foreign key (genotype_id) references genotype (genotype_id) on delete=
=20
>> cascade,
>> =09environment_id int,
>> =09foreign key (environment_id) references environment (environment_id)=
=20
>> on delete cascade,
>> =09phenotype_id int not null,
>> =09foreign key (phenotype_id) references phenotype (phenotype_id) on=20
>> delete cascade,
>> =09type_id int,
>> =09foreign key (type_id) references cvterm (cvterm_id) on delete cascade=
,
>> =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)=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 (
>> =09phendesc_id serial not null,
>> =09primary key (phendesc_id),
>> =09genotype_id int not null;
>>         foreign key (genotype_id) references genotype (genotype_id) on=
=20
>> delete cascade,
>> =09environment_id int;
>>         foreign key (environment_id) references environment (=20
>> 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)=20
>> on delete cascade,
>> =09environment1_id int,
>>         foreign key (environment1_id) references environment=20
>> (environment_id) on delete cascade,
>> =09genotype2_id int not null,
>>         foreign key (genotype2_id) references genotype (genotype_id)=20
>> on delete cascade,
>> =09environment2_id int,
>>         foreign key (environment2_id) references environment=20
>> (environment_id) on delete cascade,
>> =09phenotype1_id int not null,
>>         foreign key (phenotype1_id) references phenotype=20
>> (phenotype_id) on delete cascade,
>> =09phenotype2_id int,
>>         foreign key (phenotype2_id) references phenotype=20
>> (phenotype_id) on delete cascade,
>> =09type_id int not null,
>>         foreign key (type_id) references cvterm (cvterm_id) on delete=20
>> 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,=
=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 =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
>