TABLE alias
===========
alias_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
alias_name Required. Unique.
VARCHAR2(10)
date_created Required.
On insert, set to SYSDATE.
On update, keep original date.
created_by Required.
On insert, set to USER
Other rules:
------------
1. If an alias row is deleted, delete the corresponding row in the
locus_alias table (but do not delete the locus row in the locus
table).
2. Delete the row in the alias table if it is not used by either the
locus or feature tables. ***Checked by nightly script***
TABLE clone
===========
clone_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
chromosome Required.
Foreign key to the chromosome table.
NUMBER(2)
start_coord Required.
NUMBER(10)
Must be less than the chromosome physical_length.
stop_coord Required.
NUMBER(10)
Must be less than the chromosome physical_length.
atcc_name Optional.
VARCHAR2(10)
wash_u_name Optional.
VARCHAR2(10)
curator_note_no Optional.
Foreign key to the curator_note table.
NUMBER(10)
olson_restrict_file Optional.
VARCHAR2(100)
Filename or pathname.
date_created Required.
On insert, set to SYSDATE.
On update, keep original date.
created_by Required.
On insert, set to USER
Other rules:
------------
1. When a clone record is deleted, delete the corresponding
curator_note record. ***Checked by nightly script***
TABLE feat_alias
=============
feature_no Required. Part of Primary key.
Foreign key to the feature table.
Cascade delete.
Cannot be updated.
NUMBER(10)
alias_no Required. Part of Primary key.
Foreign key to the alias table.
Cascade delete.
Cannot be updated.
NUMBER(10)
TABLE feat_cn
=============
feature_no Required. Part of Primary key.
Foreign key to the feature table.
Cascade delete.
Cannot be updated.
NUMBER(10)
curator_note_no Required. Part of Primary key.
Foreign key to the curator_note table.
Cascade delete.
Cannot be updated.
NUMBER(10)
Other Rules:
------------
1. When a feature is deleted, delete it from the feat_cn table.
TABLE feat_pheno
=================
feature_no Required. Part of Primary key.
Foreign key to the feature table.
Cascade delete.
Cannot be updated.
NUMBER(10)
phenotype_no Required. Part of Primary key.
Foreign key to the phenotype table.
Cascade delete.
Cannot be updated.
NUMBER(10)
phenotype_type Required. Part of Primary key.
Cascade delete.
Cannot be updated.
Coded. On insert, check value against code table.
VARCHAR2(40)
sentence Optional.
VARCHAR2(720)
date_created Required.
On insert, set to SYSDATE.
On update, keep original date.
created_by Required.
On insert, set to USER
Other rules:
-----------
1. If a row in the feat_pheno table is deleted, do not delete the
corresponding row in the feature table.
TABLE feature
=============
feature_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
feature_name Required. Unique.
VARCHAR2(20)
chromosome Optional.
Foreign key to the chromosome table.
NUMBER(2)
start_coord Optional.
NUMBER(10)
-If start_coordinate > stop_coordinate
then strand = 'C'
-If there's a FK to the chromosome table,
start_coord must be less than the chromosome
physical_length.
stop_coord Optional.
NUMBER(10)
-If start_coordinate > stop_coordinate
then strand = 'C'
-If there's a FK to the chromosome table,
start_coord must be less than the chromosome
physical_length.
strand Optional.
Allowable values: W or C
is_on_pmap Required.
Allowable values: Y, N
On initial data load, set all to Y.
-If 'Y' then there must be a FK to the
chromosome table
locus_no Optional.
Foreign key to the locus table.
NUMBER(10)
sgdid Optional. Uppercase.
VARCHAR2(20)
date_modified Required.
On insert, set to SYSDATE.
On update, set to SYSDATE.
date_created Required.
On insert, set to SYSDATE.
On update, keep original date.
created_by Required.
On insert, set to USER
brief_id Optional.
VARCHAR2(960)
contigs Optional.
VARCHAR2(120)
Other Rules:
-------------
1. If a feature has a FK to the locus table, then is_on_pmap = y.
2. If a row in the feature table is deleted and that row isn't referenced
by any other row in the database, then cascade delete for the
following tables:
alias curator_note phenotype
3. **DO NOT** use cascade delete for the following tables:
locus chromosome sage_tag_location
4. Columns is_pseudo and is_mutant were dropped. The columns mutant
and pseudo were added as feature_type coded values. Also added as
feature_type codes are: Ty ORF and Questionable.
5. Some features/ORFs that are not yet named (ie. do not have associated
rows in the locus table) will have phenotypes and GO annotations.
So--we need the following rule to avoid denormalization problems:
Periodically check to see if any rows in the feature table that have
corresponding rows in either the go_feat_goev or feat_pheno tables now
are associated with a locus row. For eg. unnamed ORF YCR001W has been
annotated to GO and has a phenotype. Since then, the ORF has now been
named, and thus is now associated with a row in the locus table.
Curators need to be alerted about this so that they can remove this
association from the YCR001W feature with GO and phenotype and
associate it with the row in the locus table instead. ***CHECKED BY
PERIODIC CHECKING SCRIPT***
6. Need to link features with the following external URLs:
YPD | MIPS | SwissProt | PIR | Entrez | Entrez Related Seqs | EC | Kyoto
TABLE feature_type
==================
feature_no Required. Part of Primary key.
Foreign key to the feature table.
Cascade delete.
Cannot be updated.
NUMBER(10)
feature_type Required. Part of Primary key.
Cannot be updated.
Coded.
On insert or update, check if value is in code table.
VARCHAR2(40)
date_created Required.
On insert, set to SYSDATE.
On update, keep original date.
created_by Required.
On insert, set to USER
Other rules:
-----------
If a row in the feature_type table is deleted, do not delete the
corresponding row in the feature table.
TABLE sgdid_other
=================
sgdid_other_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
sgdid_other Required. Uppercase.
VARCHAR2(10)
sgdid_type Required. Coded.
On insert, check value against code table.
VARCHAR2(40)
locus_no Optional.
Foreign key to the locus table.
NUMBER(10)
feature_no Optional.
Foreign key to the feature table.
NUMBER(10)
curator_note_no Optional.
Foreign key to the curator_note table.
NUMBER(10)
date_created Required.
On insert, set to SYSDATE.
On update, keep original date.
created_by Required.
On insert, set to USER.
Other rules:
------------
1. In general, rows should never be deleted from sgdid_other, since this
will be used for archival purposes ie. to store deleted AspGDID's and
AspGDID_Secondaries. If there is some reason to delete a row in
sgdid_other (duplicate problem or something), no other row in other
main tables should be deleted, and vice versa: eg. if a row in the
locus table is deleted, the row in the sgdid_other table should
remain.
2. When a sgdid_other record is deleted, delete the corresponding
curator_note record.
TABLE subfeature
=================
subfeature_no Required.
Oracle sequence number.
Primary key.
Cannot be updated.
NUMBER(10)
start_coord Required.
NUMBER(10)
stop_coord Required.
NUMBER(10)
date_created Required.
On insert, set to SYSDATE.
On update, keep original date.
created_by Required.
On insert, set to USER
Other notes/rules:
------------------
This table is used for storing sub-features, such as introns,
exons, promoters, etc.
TABLE subfeature_type
==================
subfeature_no Required. Part of Primary key.
Foreign key to the feature table.
Cascade delete.
Cannot be updated.
NUMBER(10)
subfeature_type Required. Part of Primary key.
Cannot be updated.
Coded.
On insert or update, check if value is in code table.
VARCHAR2(40)
date_created Required.
On insert, set to SYSDATE.
On update, keep original date.
created_by Required.
On insert, set to USER
Other rules:
-----------
Cascade delete from subfeature ie. if a row in the subfeature table is
deleted, delete the associated row in the subfeature_type table.