Database Design

History

Allan, Sandy and Allan's administrative assistant for Graduate Studies, Christine Kerr, met on August 8 to discuss preliminary specifications for the database.

After an initial design of the database, this same group and Sheetal Pole, a CIS graduate student who will do the Acess work, met again on August 10 to discuss and finalize the database design. The diagrams below have been modified to capture the decisions made at this meeting.

Additional modifications have been made to the diagrams on August 21 to reflect changes resulting from an August 18th meeting with Allan and Christine.

• We decided that the database would be implemented in Access because Access is the most widely used personal relational database package. One advantage of this choise is the relative ease of training for those who will input and retrieve data from the database. A constraint resulting from this choice is that the database will not be WWW-accessible because Access is not well-suited to support web-based activity.

• At the highest level, the database is comprised of people: students and faculty in the College of Medicine. Student data is organized conceptually into four categories: Pre-admission, Pre-candidacy, Pre-Ph.D., and Alum Status.

• In the diagrams below, a relationship is annoted by a straight line. If the line ends in a three-pronged fork, then the relationship between tables is one-to-many. For example, A student may have only one GPA record, but many records for publications and presentations.

Upgrade to COMPH IS

In October, 2000 Allan asked Sandy to upgrade the IGBP IS so that it would store and allow retrieval of all data in the 22 tables that are required for NIH training grants. Sandy analyzed the tables and modified the IBGP database diagrams so that all the relevant data are stored.

In the figures below, am italicized font is used for these additions. Deletions are indicated by XXXX over the respective field.

At the end of March, 2001, Sandy and Sukhdeep Sidhu discussed the semantics and specification of the queries that are needed to generate the NIH reports from the database. During that discussion we discovered several more data fields and one additional table that are required to retrieve or calculate the data in the tables.

Sukhdeep added these fields and the table to the database and Sandy updated the online documentation provided below. These fields are entered in a third distinctive font.

On July 13, 2001, Allan asked Sandy to add to the COMPH IS the capability to track the progress of IGBP students through certain milestones in that program. The completion of most of these milestones is marked by the filing of meeting minutes with Chris. These tables are added below, just before the lookup tables.

On August 7 Sandy met with Allan and Cindy Fox to discuss Cindy's input activity for the faculty subysystem. As a result of that meeting several more changes were made to the database and these are all included in the diagrams below. In addition, Chris provided us values for the ResearchKeywords lookup table and the Courses lookup table. We decided for now to put only core courses in the Courses lookup.

• At the highest level, data are stored for faculty and students.

  

• Students may have a pre-admission status. Admission may be with respect to the Gradate School, as a Trainee or as a Postdoctoral Fellow.

  

• Students also may have a pre-candidacy or pre-PhD status.

  

  

• Finally, students may have an alum/postdoc status.

  

• Faculty may have committee and teaching responsibilities.

  

• Both Students and Faculty can have Publications.

  

• An additional set of tables must be created to support the extended application.

  

• An additonal set of tables was created to support the tracking of students through the IGBP program as they complete certain milestones in the program. The Table Milestones has three fields, PersonID (for the student), MeetingID (see new lookup table) and MinutesReceivedDate. The meeting values are taken from the 'Checklist for Meetings with Students' document. The meeting deadlines were agreed upon with Allan on 7/13 and will be used in the report generation stage.

  

• We also generated a list of fields for the lookup tables. The Committees, Courses and ResearchKeywords tables are incomplete.