I just completed a post about database basics and at the end I showed how students (and faculty members) can download Oracle or MS SQL Server, install it on their PC and create a database to match the book they are using in database class. Then I thought about the fact that I haven’t really used Oracle since 2008 and maybe, just maybe I’m out of practice. There’s nothing worse than creating a post containing untested code or information that might not be valid, so I went out and downloaded Oracle 11g XE (express). First, I noticed that there was only a windows x32 version and a warning that it doesn’t work on x64. Being a skillful developer, I ignored that warning and tried it for myself. There was some sort of error during the installation but it was more of a warning that something was missing. This did not affect the capabilities of the installed program from what I’ve used already. I’m sure it involves stored procedures or maybe some capability that I’m not going to use for an entry level class. So I dragged out my “Database Systems: principles, design, & implementation” book from the early 1800’s (OK, it’s copyrighted in 1990).
Here’s the starting sample database in the book:
and here’s the matching tables with data:
I installed Oracle (which took a very long time), then I jumped right into the control panel, which is all new. In fact, it’s a web interface now instead of a java application. No problem, I clicked around the menu options until I saw a table designer interface (I’ll leave it up to the student to read up and figure out how to create a table, it’s easy). So I created all three tables, two foreign key constraints, some not-null constraints and primary keys for the student and class tables. Yes, I was cringing as I typed the data in and my mind was just screaming (that should be broken out in a separate table!). But that’s the purpose of this exercise in the book is to start with a somewhat functional but incorrectly designed database and work through the normalization rules to make this into a properly designed database.
Here are the three final tables and their data in Oracle:
As you can see, learning Oracle is like riding a bicycle. Technically, all databases function similarly. They all have tables, fields, foreign key constraints, etc. There are some nuances in the way different databases handle cascade deletes and triggers and other capabilities, but the basic functions are nearly identical.
Any student should be able to download the express edition of Oracle and create the three tables above in an afternoon. It only took me two hours (one and a half of that was install time). Warning: If you’re an MS SQL Server user like me, try not to hit the F5 key to execute your query in Oracle. It just refreshes the browser and erases the query you typed in.
Now you should be able to join the student table to the enrollment table to get a list of students and their grades:
Now this is learning!