Last Update: 2002-08-26 Kara
TABLE associate
===============
colleague_no Required. Part of primary key.
Foreign key to colleague table.
Cascade delete.
Cannot be updated.
NUMBER(10)
associate_no Required. Part of primary key.
Foreign key to colleague table.
Cascade delete.
Cannot be updated.
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. When person1 identifies person2 as an associate, employee, or PI,
populate the associate table like this:
colleague_no associate_no
person1 person2
person2 person1
2. When person1 is deleted from the colleague table, delete all rows
mentioning person1 from associate table.
3. When person1 deletes the association with person2, delete both rows:
colleague_no associate_no
person1 person2
person2 person1
4. Submission Form: Correctly identifying the full name of an associate
is very difficult. If the incorrect full name is put in the database,
we can get multiple entries for a single person. All data entry
interfaces need to help the colleague search for and identify the
correct associate.
5. Display: If two associates are in the PI table, the display may be
affected. See information in the rules for TABLE pi.
TABLE colleague
===============
colleague_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
last_name Required.
VARCHAR2(40)
first_name Required.
VARCHAR2(40)
suffix Optional. Coded.
On insert, check if value is in the code table.
VARCHAR2(40)
other_name Optional.
VARCHAR2(40)
profession Optional.
VARCHAR2(100)
job_title Optional.
VARCHAR2(100)
insitution Optional.
VARCHAR2(100)
address1 Optional.
VARCHAR2(60)
address2 Optional.
Must have address1 to insert address2.
VARCHAR2(60)
address3 Optional.
Must have address2 to insert address3.
VARCHAR2(60)
address4 Optional.
Must have address3 to insert address4.
VARCHAR2(60)
address5 Optional.
Must have address4 to insert address5.
VARCHAR2(60)
city Optional.
VARCHAR2(100)
state Optional. Coded.
On insert, check if value is in the state table.
VARCHAR2(40)
region Optional.
Used for all countries, except USA and Canada.
VARCHAR2(40)
country Optional.
VARCHAR2(40)
postal_code Optional. Uppercase.
VARCHAR2(40)
source Required. Coded.
On insert, check if value is in the code table.
VARCHAR2(40)
is_sgd_contact Required.
VARCHAR2(1)
Valid values are 'Y' or 'N'.
date_modified Required.
On insert, if NULL, 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:
------------
A new column was suggested on 2002-08-26 to store which colleagues are
CGD contacts ie. are willing to receive an CGD newsletter.
1. A colleague_no is a foreign key in many tables. When a colleague
entry is deleted, records for that colleague should be deleted
(cascade delete) from these tables:
colleague_remark coll_url
associate coll_phone
pi coll_email
coll_vocab coll_locus
coll_locus coll_cn
2. Some data associated with a colleague is not unique to the
colleague entry. When a colleague entry is deleted, delete the
corresponding records from these tables only if no other colleague
references that data:
url email
phone curator_notes
3. Updates to colleague information is displayed to CGD users only when
is_public in the update_log table = 'Y'.
4. If state column is NOT NULL, country must be either USA or Canada.
5. If country is NULL, state and region must both be NULL.
6. Submission form: make Last name, First name, Country input boxes
manditory.
7. Initial data load: date_modified = ACEDB last_update, otherwise, if
NULL = SYSDATE
Table colleague_remark
======================
remark_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
remark Required.
VARCHAR2(1500)
remark_type Required. Coded.
On insert, check if value is in code table.
VARCHAR2(40)
remark_date Required.
On insert, if NULL, set to SYSDATE.
colleague_no Required.
Foreign key to colleague table.
Cascade delete.
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. Initial data load: remark_date = colleague.date_modified
TABLE coll_cn
=============
curator_note_no Required. Part of primary key.
Foreign key to curator_note table.
Cascade delete.
Cannot be upated.
NUMBER(10)
colleague_no Required. Part of primary key.
Foreign key to colleague table.
Cascade delete.
Cannot be updated
NUMBER(10)
Other Rules:
------------
1. When a colleague is deleted, delete it from the coll_cn table.
TABLE coll_email
================
colleague_no Required. Part of primary key.
Foreign key to colleague table.
Cascade delete.
Cannot be updated.
NUMBER(10)
email_no Required. Part of primary key.
Foreign key to email table.
Cannot be updated.
NUMBER(10)
Other Rules:
------------
1. When a colleague is deleted, delete it from the coll_email table.
TABLE coll_locus
================
colleague_no Required. Part of primary key.
Foreign key to colleague 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 it from the coll_locus table.
2. When a colleague is deleted, delete it from the coll_locus table.
TABLE coll_phone
================
colleague_no Required. Part of primary key.
Foreign key to colleague table.
Cascade delete.
Cannot be updated
NUMBER(10)
phone_no Required. Part of primary key.
Foreign key to phone table.
Cannot be updated.
NUMBER(10)
Other Rules:
------------
1. When a colleague is deleted, delete it from the coll_phone table.
TABLE coll_url
==============
colleague_no Required. Part of primary key.
Foreign key to colleague table.
Cascade delete.
Cannot be updated.
NUMBER(10)
url_no Required. Part of primary key.
Foreign key to url table.
Cannot be updated
NUMBER(10)
Other Rules:
------------
1. When a colleague is deleted, delete it from the coll_url table.
TABLE coll_vocab
================
colleague_no Required. Part of primary key.
Foreign key to colleague table.
Cascade delete.
Cannot be updated.
NUMBER(10)
vocabulary_no Required. Part of primary key.
Foreign key to vocabulary table.
Cascade delete.
Cannot be updated.
NUMBER(10)
Other Rules:
------------
1. When a colleague is deleted, delete it from the coll_vocab table.
TABLE email
===========
email_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
email Required. Unique
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:
------------
1. When a colleague is deleted, delete its email record if it is not
used by any other colleague. ***Checked by nightly script***
TABLE phone
===========
phone_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
phone_num Required.
VARCHAR2(40)
phone_type Required. Coded.
On insert, check if value is in the code table.
VARCHAR2(40)
phone_location Required. Coded.
On insert, check if value is in the 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:
------------
1. If a colleague is deleted, delete its related row in the phone
table if there are not any other colleagues using it. ***Checked by
nightly script***
2. Phone_num, phone_type, and phone_location together are unique.
TABLE pi
========
colleague_no Required. Part of primary key.
Foreign key to colleague table.
Cascade delete.
Cannot be updated.
NUMBER(10)
pi_no Required. Part of primary key.
Foreign key to colleague table.
Cascade delete.
Cannot be updated.
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.When person1 identifies person2 as his/her pi, populate the pi table
like this:
colleague_no pi_no
person1 person2
2. When person1 claims to be a pi AND identifies person2 as an
employee, populate the pi table like this:
colleague_no pi_no
person2 person1
3. When the colleague entry for a pi or employee is deleted, delete
all rows in the pi_table that mention that person.
4. When a employee (person1 in the example) OR pi(person2) says that
the pi-employee relationship is ended, delete the row that mentions
both of them:
colleague_no pi_no
person1 person2
5. Submission form: There needs to be a graceful, yet obvious way for
users to input the PI-employee relationship. Employees are really
students, post-docs, technicians, etc.
TABLE url
=========
url_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
url Required. Unique.
VARCHAR2(240)
url_type Required. Coded.
On insert, check if value is in the code table.
VARCHAR2(40)
www_name 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:
------------
1. When a colleague is deleted, delete its corresponding row in the
url table if it is not referred to by any other colleague. ***Checked
by nightly script***
TABLE keyword
================
Keyword_no Required.
Primary key. Oracle sequence number.
Cannot be updated.
NUMBER(10)
keyword Required. Unique.
VARCHAR2(100)
source Required. Coded.
On insert, check if value is in the 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:
------------
1. Delete the Keyword term if no other table refers to it
(coll_keyword at present; I guess we may eventually use this for other
tables).
TABLE coll_keyword
=======================
colleague_no Required.
Part of primary key, Foreign key to colleague
table.
Oracle sequence number.
Cannot be updated.
NUMBER(10)
keyword_no Required.
Part of primary key, Foreign key to keyword
table.
Oracle sequence number.
Cannot be updated.
NUMBER(10)