IGS Database Work


The following combines multiple separate requests for improvements to the Informatics Graduate School Database into one project for efficiency. The requests covered are Tracking Applications, Recording Disability, Recording Institute Actions, Refining Status and User Reports and are outlined in detail in the Proposal section.


The Informatics Graduate School, specifically Andrew Finnie and Anna Hobbs.


Some of the changes required are mandatory and if not done in the database would need to be done separately. The changes will reduce duplication of effort because there will be less information recorded outside of the database. The new reports produced are as requested and needed by academic staff for processing applications. While EUCLID is intended to be in place for the 2007 application round these changes are considered worth the effort because of the improvements to application processing efficiency and feedback to academic staff. The other changes requested are not included in this project as the required additional effort was not considered justifiable.


The necessary changes to the Applicant/Student tables and the Applicant/Student custom forms and additional reports as outlined in the Plan section below.


To be in place ideally for beginning of December but early January at latest as this is when the bulk of applications need to be entered.


This is an edited summary of the initial submission as adjusted to suit the scope of this project.

Tracking Applications

Add the ability to record the date an application was received and the date acknowledged and, if possible, the date of the last update/action.

Recording Disability

Add the ability to record the University two digit disability code on an application.

Recording Institute Actions

Add the ability to record for each institute (three maximum) on an application the date passed for assessment, the date the assessment was returned and the recommendation by the institute (either reject, further consideration, or admit).

Replace the existing PhD web reports with a report generating a web page for each institute showing applications by recommendation and date.

Refining Status

Modify the current status fields on an application so that the values are refined for consistency.

User Reports

Provide a report that provides all the application information in a structured format for the IGS to process separately as needed.


RAE work has priority over this (needed for mid-October) and session rollover is imminent (early September). There is additional ITO work required (minimal) in the same time window.


This project has no dependencies and no other projects depend on it.


Suggest that this project falls under the Research and Teaching Unit and is consequently managed by Tim Colles.


All the necessary expertise required is within the Unit.


Estimate two full time weeks planning to start in late October or early November.


This would have priority once session rollover and RAE work is completed.


This is a detailed technical plan for the work. Note that all the changes below should be carried out on the test database service so as to check they work and so as to refine the list of steps to be taken.


Add a virtual institute for the DTC so that DTC applicants can be processed separately from IANC applicants (and reported on separately).

Data Model

Make the following changes to the applicant table and update the Data Dictionary as appropriate.

  • Add a Date Received and Date Acknowleged field for tracking applications.

  • Add a Date of Last Update/Action and Owner of Last Update/Action (UUN) field for tracking record changes. These fields will be automatically maintained.

  • For each institute@ field, add an additional three fields which are a Date (date passed for assessment), a Date (date assessment returned) and an Option (String) recommendation by institute (values: reject, further consideration, admin and pending).

  • Change acceptable values for AppStatus and Status as below:

    AppStatus - Received, Rejected, Offered, Accepted, Started, Withdrew
    Status - Incomplete, Pending, Closed, Transferred, Unlikely, Deferred

    Check for Orphaned values and confirm with Andrew what they should be changed to from the list above.

Make the following changes to the student table and update the Data Dictionary as appropriate.

  • Add a "Disablity Code" field (integer1).

Procedural Model

Make the following procedural additions.

  • Add a rule triggered on insert/update(*) of applicant table which auto fills date/owner of last update. See ? for an example of how to do this.

  • Adjust rule that on Start being set copies Applicant entries into the Student record so that it includes copying the Disablility field value.

Custom Forms

Modify the Applicant form as below.

  • Drop the Unichoice* fields (Andrew to check with Judith first).

  • Add an additional tab to the tabs at the bottom for Institute and move the institute@* fields onto that tab and add fields for the new attributes for each institute, layed out like:

    Institute1@ Date-Out Date-Back Recommendation
    Institute2@ Date-Out Date-Back Recommendation
    Institute3@ Date-Out Date-Back Recommendation

    The Recommendation should be an Option pull down.

  • The above changes create space for new fields on the main body of the form so in an appropriate place add the Date Received/Acknowledged fields and as yellow/text type (to show auto/nonedit) add the Last Update/Owner fields.

  • Add the Disability code field.

  • Rename AppStatus as Outcome.

  • Add green firstname/lastname lookup fields from the Person table for the Supervisor (only the first one is ever used at application stage so the additional Supervisor* fields can be removed from the form, after checking for Orphaned values).

Modify the Student (RPG) form as below:

  • Add the Disability code field.


Add the following reports.

  • Create a Web report (to generate one page per-institute) on the IGSDB web section. The report lists applicants by reccommendation order, so Pending first, then for consideration, then admit and then reject with the same information as on other web reports. Note that because an applicant can be considered by multiple institutes they will appear on multiple pages.

  • Almost all of the existing PhD reports can be dropped, details to be checked by Andrew and confirmed before proceeding.

  • Create a genrep report producing Application data as XML. This was done previously as an email to Henry Thompson (although possibly for MScs rather than PhDs) so its just a variation of that.


Provide a two hour training session for Andrew on making queries using the existing Database interface (including manual queries directly on raw tables rather than custom forms).

Andrew and Judith will supply a list of how current status values should map to new status values after which we should run a simple hand crafted bit of SQL to automatically update all records according to this mapping.

Topic revision: r1 - 15 Aug 2006 - 16:11:36 - TimColles
This site is powered by the TWiki collaboration platformCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback
This Wiki uses Cookies