This post would be about the usage of H2 database for the purpose of using it as an educational tool for Database Systems lessons and classes.
Recently, I have seen institutions that offers Database courses and they would use Oracle as their DBMS for educating students in Computer Science and Information Technology courses. In fact, many schools and institutions uses Oracle. The sad thing is few of these institutions are creative or innovation enough to find a good tool other than Oracle to teach SQL and Database subjects.
The most recent encounter with a school / institution (which I shall not name) uses Oracle as well. The students would either use SSH or Oracle SQLDeveloper to access the school's educational Oracle Database.
Because of the problems of allowing student's CREATE, INSERT, UPDATE and DELETE statements that may affect the Database as a whole, all of the students only had 'SELECT' privileges enabled. When the students had to test out their hands on the CREATE, INSERT, UPDATE and DELETE statements, there would be the usual error for the lack of privilege. How is a student going to test out his or her CREATE, INSERT, UPDATE and DELETE statements on the Oracle database if they couldn't do anything except SELECT ?
Mind you, not even view creation is allowed. That is how restricted the privileges are.
Some schools would advocate the students to install the free Oracle (version 9, 10, 11)G of the free database and it would be gigabytes of space wasted and hours long of installation. It would be tedious and we all know setting up Oracle database is a pain in the ass. The good thing is that since the database is setup on the student's side, they have the freedom to test out their CREATE, INSERT, UPDATE and DELETE statements. They would be given SQL scripts to copy into their databases to setup the database if anything goes wrong.
This is where H2 database steps in to save the day for students and teachers.
H2 database is only slightly more than 1 MB. How big can that be ? All you need is to unzip the zipped H2 compressed file to the Desktop or somewhere you want to keep it and then go to the '/bin' file and a double-click on the 'H2-(version).jar' file and it would simply load up the H2 web based console with NO HASSLE at all.
Now, this, as I have described above, is so easy. How difficult can using H2 be ?
Many schools and institutions may not want to give student SQL scripts because they include SQL statements which the students may reference for their assignments and even copy them wholesale. This is not a problem with H2.
The strategy I would propose is for the teacher to hand out an SQL script file that handles the creation of tables and setting of constraints and also a CSV file that contains the sample data for the tables. The SQL file and CSV file would be placed next to each other. The student simply needs to open the SQL file and edit the location of the CSV file within the SQL statement that reads the CSV file to populate the database tables and when all is done, the student uses the H2 command 'RUNSCRIPT FROM (filepath of SQL file);' and H2 would run the SQL script file and automatically create the tables and populate the data.
Below is a set of SQL commands on H2 database to prove my point. Use the H2 Console (or double click the H2-.jar file).
-- Comment: Create the test table and insert values --
CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));
INSERT INTO TEST VALUES (1, 'JOHN');
INSERT INTO TEST VALUES (2, 'MENA');
-- Comment: Script out the test table to a CSV file --
CALL CSVWRITE('/home/linux_user/Desktop/test.csv', 'SELECT * FROM TEST');
-- Comment: Go to the location and check if the CSV file is successfully made --
-- Comment: Delete table for the demo --
DROP TABLE TEST;
-- Comment: Creating tables using your own SQL statement and then reading the data from CSV file to populate table--
CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))
AS SELECT * FROM CSVREAD('/home/linux_user/Desktop/test.csv');
As you can see, the H2 database provides nifty functions as above to setup the database and tables without exposing INSERT, UPDATE, DELETE statements that students may copy directly into their assignment sheets for submission.
Most databases have very horrendous interactive consoles or methods of interaction that really put people off but H2 database provides a very user friendly web based GUI for interaction with the databases. If using of GUI is too 'noobie', then H2 also provide terminal consoles too.
Lastly, H2 database is FREE as in you can modify the source codes, the source codes are freely available and you don't have to pay a single cent to get the source codes or the executable files.
The forum for H2 database
(http://groups.google.com/group/h2-database?pli=1) is a very friendly place and answers are quickly answered and handled. Thomas Mueller, the original and lead developer of H2 database is always hard at work trying to improve H2 database and it's user experience.
Do consider using H2 database as an alternative teaching tool for Database related courses and subjects in educational institutions and schools. I am sure it would not only improve the user experience for the students but for the teachers too.
