Last Update: 2002-03-13 Kara
TABLE curator_note
==================
curator_note_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
note Required. Unique.
VARCHAR2(960)
is_public Required.
Allowable values: Y, N
On insert, if NULL, set to N.
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 the curator_note_no is not mentioned in the tables below, then
delete the entire row. ***Checked by nightly script***
locus_cn feat_cn sgdid_other
coll_cn clone
TABLE gene_product
==================
gene_product_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
gene_product Required. Unique.
VARCHAR2(480)
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 gene_product table is not associated with any row
in the locus table, then delete that row. ***Checked by nightly
script***
2. If a row in the gene_product table is deleted, delete rows in the
locus_gp table but do not delete rows in the locus table.
TABLE gene_reservation
======================
reservation_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
reservation_date Required.
On insert, if NULL, set to SYSDATE.
expiration_date Required.
On insert, if NULL, set to SYSDATE + 365 days
locus_no Required.
Foreign key to locus table.
Delete Cascade.
NUMBER(10)
is_standardized Required.
VARCHAR2(1)
Valid values are 'Y' or 'N'
date_standardized Required.
DATE
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 gene reservation is made, the corresponding colleague must
have an email address. If colleague_no is present in the
gene_reservation table, then it must also be present in the coll_email
table. (***CHECKED BY PERIODIC CHECKING SCRIPT***)
2. If the locus is deleted from the locus table, delete the related
rows from the gene_reservation table.
TABLE locus
===========
locus_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
locus_name Required. Unique.
Uppercase.
VARCHAR2(10)
chromosome Optional.
Foreign key to chromosome table.
NUMBER(2)
genetic_position Optional.
NUMBER(5,2)
sgdid Optional. Uppercase.
VARCHAR2(10)
enzyme Optional.
VARCHAR2(240)
description Optional.
VARCHAR2(240)
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.
Other Rules:
------------
1. If a locus row is deleted, delete the related rows from the following
tables:
gene_reservation coll_locus locus_alias go_locus_goev
locus_cn locus_twopt sacchdb_phenotype locus_gp
locus_pheno
If a locus row is deleted, *do not* delete the relevant row in the
chromosome table or in the feature table.
2. If Genetic_Position exists for a LOCUS row, chromosome_no is mandatory.
(***CHECKED BY PERIODIC CHECKING SCRIPT***)
3. Need to link loci with the following external urls:
YPD | MIPS | SwissProt | PIR | Entrez | Entrez Related Seqs | EC | Kyoto
TABLE locus_alias
=================
locus_no Required. Part of Primary key.
Foreign key to the locus 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)
Other Rules:
------------
1. When a locus is deleted, delete it from the locus_alias table
2. If the relevant row in the alias table is not used by any other row in
the locus_alias table, then delete that alias row.
TABLE locus_cn
==============
curator_note_no Required. Part of primary key.
Foreign key to curator_note table.
Cascade delete.
Cannot be updated.
NUMBER(10)
locus_no Required. Part of primary key.
Foreign key to locus table.
Cascade delete.
Cannot be updated.
NUMBER(10)
Other Rules:
------------
1. When a locus is deleted, delete its locus_cn record.
TABLE locus_gp
==============
locus_no Required. Part of primary key.
Foreign key to locus table.
Cascade delete.
Cannot be updated.
NUMBER(10)
gene_product_no Required. Part of primary key.
Foreign key to gene_product table.
Cascade delete.
Cannot be updated.
NUMBER(10)
Other Rules:
------------
1. When a locus is deleted, delete its locus_gp record.
TABLES locus_pheno
==================
locus_no Required. Part of primary key.
Foreign key to locus table.
Cascade delete.
Cannot be updated.
NUMBER(10)
phenotype_no Required. Part of primary key.
Foreign key to 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 locus_pheno table is deleted, do not delete the
corresponding row in the feature table.
2. If the corresponding row in the phenotype table is not used by any
other row in the database, then delete it (of course, if it is used by
another feature or locus row, then do not delete it). DO NOT IMPLEMENT YET
TABLE locus_twopt
=================
locus_no Required. Part of primary key.
Foreign key to locus table.
Cascade delete.
Cannot be updated.
NUMBER(10)
two_point_no Required. Part of primary key.
Foreign key to two_point table.
Cascade delete.
Cannot be updated.
NUMBER(10)
Other Rules:
------------
1. When a locus is deleted, delete its locus_twopt record.
TABLE phenotype
===============
phenotype_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
phenotype Required. Unique.
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 phenotype table is not associated with any other row
in the database, then delete that row. **NOTE** Do not invoke this
business rule until curators decide it is time. We are building
a controlled vocabulary for phenotype and need a set of standard
phenotypes (that may be initially unused) to work with.
2. If a row in the phenotype table is deleted, delete rows in the
linking tables (locus_pheno, feat_pheno) but do not delete rows in the
locus or feature tables.
3. See relevant rules under "TABLE FEATURE" and "TABLE SACCHDB_PHENOTYPE".
TABLE sacchdb_phenotype
========================
sacchdb_phenotype_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
sacchdb_phenotype Required.
VARCHAR2(960)
locus_no Optional.
Foreign key to locus table.
NUMBER(10)
feature_no Optional.
Foreign key to feature 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:
-----------
**Note** this table is temporary. As we gradually codify our
phenotypes using the phenotype table, we will gradually delete rows in
the SacchDB table (see rule below). When the sacchdb_phenotype table
is finally empty, we'll drop it.
For rows in the locus table with a corresponding row in the
sacchdb_phenotype table, check to see if these loci rows have a row in
the locus_pheno table. (ie. we want curators to be sure to delete the
sacchdb_phenotypes as the oracle phenotypes are entered) ***CHECKED BY
PERIODIC CHECKING SCRIPT***
TABLE interaction
========================
sgdid_1 Required.
Primary key (part of composite key).
Cannot be updated. Uppercase.
VARCHAR2(10)
sgdid_2 Required.
Primary key (part of composite key).
Cannot be updated. Uppercase.
VARCHAR2(10)
interaction_type Required.
Coded.
On insert, check value against code table.
VARCHAR2(40)
description Optional.
VARCHAR2(240)
date_created Required.
On insert, set to SYSDATE.
On update, keep original date.
created_by Required.
On insert, set to USER
Other Rules:
-----------
Check for the existence of the CGDIDs that are in the INTERACTION
table in LOCUS, FEATURE, or CGDID_Other. If non-primary CGDIDs are
used, then they will be replaced by the primary ones. This will be
reported to the log file. ***CHECKED BY PERIODIC CHECKING SCRIPT***