Rules for the Reference Tables
last update: 2001-05-29 Gail
TABLE abstract
=================
reference_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
abstract Required.
VARCHAR2(4000)
Other rules:
-----------
TABLE author
=================
author_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
author_name Required. Unique.
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:
-----------
TABLE author_editor
=================
author_no Required. Part of Primary key.
Foreign key to the author table.
Cascade delete.
Cannot be updated.
NUMBER(10)
reference_no Required. Part of Primary key.
Foreign key to the reference table.
Cascade delete.
Cannot be updated.
NUMBER(10)
author_type Required. Coded.
On insert, check value against code table.
VARCHAR2(40)
author_order Required.
NUMBER(4)
Other rules:
-----------
TABLE book
=================
book_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
title Required.
VARCHAR2(400)
volume_title Optional.
VARCHAR2(400)
isbn Optional.
VARCHAR2(20)
total_pages Optional.
NUMBER(5)
publisher Optional.
VARCHAR2(100)
date_created Required.
On insert, set to SYSDATE.
On update, keep original date.
created_by Required.
On insert, set to USER
Other rules:
-----------
TABLE journal
=================
journal_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
full_name Required. Unique.
VARCHAR2(200)
abbreviation Optional.
VARCHAR2(140)
issn Optional.
VARCHAR2(20)
publisher Optional.
VARCHAR2(100)
url_no Optional.
Foreign key to the url 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:
-----------
TABLE publication_type
=================
reference_no Required. Part of Primary key.
Foreign key to the reference table.
Cascade delete.
Cannot be updated.
NUMBER(10)
pub_type Required. Part of Primary key.
Coded.
On insert, check value against code table.
VARCHAR2(40)
Other rules:
-----------
TABLE reference
=================
reference_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
ref_source Required. Coded.
On insert, check value against code table.
VARCHAR2(40)
status Required. Coded.
On insert, check value against code table.
VARCHAR2(40)
citation Required. Unique.
VARCHAR2(480)
year Required.
NUMBER(4)
pubmed Optional.
NUMBER(10)
date_published Optional.
VARCHAR2(20)
date_revised Optional.
NUMBER(8)
issue Optional.
VARCHAR2(40)
page Optional.
VARCHAR2(40)
volume Optional.
VARCHAR2(40)
title Optional.
VARCHAR2(400)
journal_no Optional.
Foreign key to the journal table.
NUMBER(10)
book_no Optional.
Foreign key to the book 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. If journal_no is not null and pubmed is not null and status =
'Published', the columns page, volume, and title should also be not
null.
2. If journal_no is not null, book_no should be null. Similarly, if
book_no is not null, then journal_no should be null.
TABLE reflink
=================
relink_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
reference_no Required.
NUMBER(10)
tab_name Required. Uppercase.
VARCHAR2(20)
primary_key Required.
VARCHAR2(40)
primary_key_col Required. Uppercase.
VARCHAR2(100)
col_name Optional. Uppercase.
VARCHAR2(30)
date_created Required.
On insert, set to SYSDATE.
On update, keep original date.
created_by Required.
On insert, set to USER
Other rules:
-----------
1. Composite primary keys have column names separated by double colons (::).
2. Columns tab_name, primary_key, primary_key_col, reference_no,
col_name together are unique.
TABLE ref_bad
=================
pubmed Primary key.
Cannot be updated.
Required.
NUMBER(10)
date_created Required.
On insert, set to SYSDATE.
On update, keep original date.
created_by Required.
On insert, set to USER
TABLE ref_unlink
=================
ref_unlink_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
pubmed Required.
NUMBER(10)
tab_name Required. Uppercase.
VARCHAR2(20)
primary_key Required.
VARCHAR2(40)
reference_no Optional.
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. Columns pubmed, tab_name, primary_key together are unique.
TABLE ref_url
=================
reference_no Required. Part of Primary key.
Foreign key to the reference table.
Cascade delete.
Cannot be updated.
NUMBER(10)
url_no Required. Part of Primary key.
Foreign key to the url table.
Cascade delete.
Cannot be updated.
NUMBER(10)
Other rules:
-----------
TABLE related_ref
=================
reference_no Required. Part of Primary key.
Foreign key to the reference table.
Cascade delete.
Cannot be updated.
NUMBER(10)
related_ref_no Required. Part of Primary key.
Foreign key to the reference table.
Cascade delete.
Cannot be updated.
NUMBER(10)
pub_type Required. Coded.
On insert, check value against code table.
VARCHAR2(40)
description Optional.
VARCHAR2(240)
Other rules:
-----------
TABLE feat_gene_info
=================
feature_no Required. Part of Primary key.
Foreign key to the feature table.
Cascade delete.
Cannot be updated.
NUMBER(10)
reference_no Required. Part of Primary key.
Foreign key to the reference table.
Cascade delete.
Cannot be updated.
NUMBER(10)
literature_topic Required. Part of Primary key.
Coded.
On insert, check value against code table.
VARCHAR2(40)
last_curated Required.
On insert, set to SYSDATE.
On update, keep original date.
created_by Required.
On insert, set to USER
Other rules:
-----------
TABLE locus_gene_info
=================
locus_no Required. Part of Primary key.
Foreign key to the locus table.
Cascade delete.
Cannot be updated.
NUMBER(10)
reference_no Required. Part of Primary key.
Foreign key to the reference table.
Cascade delete.
Cannot be updated.
NUMBER(10)
literature_topic Required. Part of Primary key.
Coded.
On insert, check value against code table.
VARCHAR2(40)
last_curated Required.
On insert, set to SYSDATE.
On update, keep original date.
created_by Required.
On insert, set to USER
Other rules:
-----------