Project: STU186 Student Migrations

There's been some time between now and the last update, ill explain this as we go.

At the start of this week I was given the task of migrating client data across databases as part of a the Project STU186, this is the upgrade of the Student Counselling system.

STU186 has come about as a knock on effect of the EUCLID implimentation, as EUCLID serves as the central IT system for the university a lot of services provided here are coupled with this system. As a result, when the old system (DACS) fully comes of line there will need to be provisions made for dependant systems so their associated department or service can carry on functioning.

And of course downtime is always an excuse to upgrade.

I was given the week to migrate the data, I've not done any major database work for some time now so it was exciting to have a task which would test what I know and teach me something new.

The new database consists of eleven tables (versus the original db's 10), the new tables are looking to simplify the storage and referencing of the data meaning that the data is being rearranged according to the new db structure.

I wrote SQL scripts (these can be opened with notepad) which handle the migration of all data from table to table, there are copies of these scripts in the STU186 evidence folder. As part of the development process, I have written these scripts, tested them and used them to transfer the data in question. At this point the core task of data migration was complete.

Additionally to this another interesting task came up, since the old Student counselling system was taken down for development there are still new applicants to the counselling service, current UoE students have been stored on a table for non UoE clients using a second forename field to store their matriculation number.

In the table these new clients are going to the matriculation number becomes the client ID.

The problem this poses is how do we extract this data (matriculation numbers) from the column its in and migrate the associated records to the new database, seperate from the non UoE clients.

The solution sort of extended past my experience but a golden opportunity to learn something.

Creating this script was the harder part of the task and it took several attempts to get it right, the finished script fits my interpretation of what the objective is and I really hope its up to the job that is expected of it.

This script is also in the STU186 evidence folder (Evidence/August/STU186).

What Did I learn?

• Used SQL developer for the first time
• Set up databse connections to support SQL developer usage
• Transfer of data between databases, not just between tables
• how to write regular expressions
  
• How to use regular expressions in SQL queries
• How to contribute to the implementation document