Date:   Wed Dec 20 07:49:53 MST 2000
To: ADASS Web Site Working Group (WSWG)
From:   Irene Barg

Re: Brief description of the 'adass' database.

The 'adass' database consists of three table (schemas attached):

participant - table representing conference participants;
program - table representing all contributions (BoFs, Oral, Poster and Demo);
invited - table representing invited speakers.

The tables map logically to the way the information was presented at past
Conference host sites. Relationships between the three tables can be 
created using common keys, however there are no dependencies between them. 
The source of  data came from online public materials such as detailed 
programs and participant lists,from past host sites. The amount of 
information and the way it was presented varied from site to site and 
year to year.  The data was parsed from these online HTML documents using 
Perl and Python programs.  For most Conference years, no massaging of data 
was done, with the exception for years where 'paper id' was not provided, 
one was created using the format adopted by ADASS IX.  

Table descriptions follow:

mysql> describe participant;
+-------------+---------------+------+-----+---------+--------------+
| Field       | Type          | Null | Key | Default | Extra        |
+-------------+---------------+------+-----+---------+--------------+
| adassnum    | year(4)       |      |     | 0000    | ADASS year   |
| name_l      | varchar(30)   |      | MUL |         | Last name    |
| name_f      | varchar(20)   | YES  |     | NULL    | First name   |
| name_m      | varchar(20)   | YES  |     | NULL    | Middle name  |
| affiliation | varchar(60)   | YES  |     | NULL    | Institution  |
| update_date | timestamp(14) | YES  |     | NULL    |              |
| update_by   | varchar(30)   | YES  |     | NULL    |              |
+-------------+---------------+------+-----+---------+--------------+
7 rows in set (0.00 sec)

mysql> describe program;
+-------------+---------------+------+-----+---------+-------------------+
| Field       | Type          | Null | Key | Default | Extra             |
+-------------+---------------+------+-----+---------+-------------------+
| adassnum    | year(4)       |      | PRI | 0000    | ADASS year        |
| id          | varchar(10)   |      | PRI |         | Paper ID          |
| title       | varchar(136)  |      |     |         | Abstract title    |
| name_l      | varchar(30)   |      |     |         | Last name         |
| name_f      | varchar(20)   | YES  |     | NULL    | First name        |
| name_m      | varchar(20)   | YES  |     | NULL    | Middle name       |
| refurl      | varchar(136)  | YES  |     | NULL    | Journal url       |
| refname     | varchar(60)   | YES  |     | NULL    | Publisher         |
| type        | varchar(10)   | YES  |     | NULL    | ORAL, POSTER, BOF |
| category    | varchar(120)  | YES  |     | NULL    | Program category  |
| refyear     | year(4)       | YES  |     | NULL    | Journal year      |
| refvol      | smallint(6)   | YES  |     | NULL    | Journal volume    |
| refpgs      | varchar(10)   | YES  |     | NULL    | Journal pages     |
| status      | varchar(10)   | YES  |     | NULL    |                   |
| update_date | timestamp(14) | YES  |     | NULL    |                   |
| update_by   | varchar(30)   | YES  |     | NULL    |                   |
+-------------+---------------+------+-----+---------+-------------------+
16 rows in set (0.41 sec)

Fields in the 'invited' table are found in both tables above, and should 
be a view.

mysql> describe invited;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| adassnum      | year(4)       |      |     | 0000    |       |
| name_l        | varchar(30)   |      |     |         |       |
| name_f        | varchar(20)   | YES  |     | NULL    |       |
| name_m        | varchar(20)   | YES  |     | NULL    |       |
| affiliation   | varchar(60)   | YES  |     | NULL    |       |
| title         | varchar(136)  |      | MUL |         |       |
| special_topic | varchar(60)   | YES  |     | NULL    |       |
| update_date   | timestamp(14) | YES  |     | NULL    |       |
| update_by     | varchar(30)   | YES  |     | NULL    |       |
+---------------+---------------+------+-----+---------+-------+
9 rows in set (0.00 sec)