You are here

IGS Database Work

Project ID: 
20
Current stage: 
Manager: 
Unit: 
What: 

Description: 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.

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

Why: 

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

Case statement: 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.

When: 

Status:

Timescales: 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.

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

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

How: 

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

Resources: All the necessary expertise required is within the Unit.

Plan: 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.

Data

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.

Reports

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.

Miscellaneous

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.

Other: 

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

Risks: 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.

URL:https://wiki.inf.ed.ac.uk/DICE/DevelopmentMeetingIGSDatabaseProject

Milestones

Proposed date Achieved date Name Description
2006-12-08 2006-11-17 1 Data Model Updates
2006-12-08 2006-12-01 2 Custom form updates
2007-04-12 2007-04-25 3 Reports
2007-04-12 2007-05-02 4 Finishing