The fourth course on the HHS was another major course. This was the last course that I did, before summer vacation (Though that’s a bit ovcious, I guess) and in this course we did everything surrounding databases. This is one of the first Informatics courses, where programming did not play a big part in the entire project (I am not counting minor courses) and that took a little bit getting used too.

Summary
Course: I-5 Dataarchitecture
Completed: June 2010

Gained knowledge
Defining Datadictionairy
Desisn EER diagrams
Design class diagrams
Compose an advisory comparing MS SQL Server and Oracle
Compose a conversionplan for migration between MS Access DB and Excel sheet to Oracle
Working with Oracle DB
Working with SQL Server

The course I-5 already started off well, with the message that we would focus on the migration of a database and everything that’s surrounding that subject. The intent of this course is that there’s a company that uses a MS Access datbase and excel sheet to manage their data. They would really like to migrate their current structure to a better database environment. It’s up to us to map the current database structure and translate this to a better database structure in an Oracle or MS SQL Server environment. All the students were divided into groups of four at the first college. In this course I didn’t work together with the people I usually worked together with (Roy, Eise, Arjen, Mark, Quiriën etc) and I ended up working with three people, which ended up being really fun and good.

We started this new adventure with reading the old data dictionairy (from the MS Acccess database) and make a start with the new data dictionairy. It was clear from the start that we would migrate the MS Access DB, so we spend the first few weeks designing EER diagrams. This took a little getting used too. They have a lot of similarities to class diagrams, but the multiplicities at the associations are reversed with EER diagrams. If you have an employee that could have more expertises and an expertise can only have 1 employee, then you put 0..* at the side of expertise and 1 at the side of employee in class diagrams. This is the other way around in EER diagrams. The moment I figured that out, was the moment designing EER diagrams became really fun. Ours got really big, though!

After all the designs were.. well.. designed, we were meant to write an advisory that would compare MS SQL Server and Oracle, so we could see which database environment was the best for our fictional company. This was a pretty big job. We chose six criteria to compare both environments with: Data Import & Expoert, Security, Recovery, Concurrency, Managing Database Storage and DBMS Buffer Cache. Both environments were pretty much  matched with eachother, but Oracle worked better than SQL Server with Concurrency, so we chose to go with Oracle. I found that pretty exciting, because I never worked with Oracle before.

The datadictionairy for the new database was finished, the EER diagrams and other information for the new structure was known and weknew we were going to use Oracle. It was high time to build the structure in Oracle and to write the conversion plan. The conversionplan was, in our case, a report with excelsheets that explained where all the data went too and how the data was converted. It was a little bit annoying to make, because you were just messing around with the values in such a sheet, but eventually it was clear how all the data should be converted in the new database environment. The only thing that was left, wa sto do the actual conversion. We did this with help from another advisory, that was already written for us. This advisory would compare different migration tools for us to use. Several tools were compared on a few criteria, just like we did in our advisory, and Kettle from Pentaho was the big winner.

I have to say that migration tools, or ETL’s, are pretty simple. In hindsight I could’ve very easily written something similair in PhP and do the exact samework, but without all the ruckus that came with trying to operate such an existing tool. We had a bit of difficulty trying to get Kettle to work. We were bombarded with errors, values taht were invorrect and even if we did enter the correct values, we still couldn’t get it to work. Kettle would get everything from the excelsheet, but not from the Access database and vice versa, even though we clearly told the tool that we  needed both in the new database. Eventually we manually added all the information from the excelsheet into the access database, and transformed that one to the new database environment. That did work, thankfully.

After we did this, the only thing we had to do was to make a simple application that would show the data that was stored in the Oracle database.I took this job onto myself, since I already knew how I would create this. I made av ery simple PhP website, where I retrieved some information from the Oracle database and put them into sometables. I wrote it very quick-and-dirty, but since the application did not impact our grades whatsoever, I didn’t really waste any time showing off.

All things considered I really enjoyed this course, but it was a bit cumbersome at times. I did learn a lot, though, and really appreciate that.