Database Design – Denormalization, Codd and the Copa America

Deb’s quite keen on the story of Goldilocks and the Three Bears ( although my version does end up with Goldilocks being charged with breaking and entering and criminal damage). How is this fairy tail linked to Database design ? Well, a good database should ideally be to Goldilock’s taste – not too fre-form and ad-hoc, not too rigid Third Normal Form, but just right.
To demonstrate this, we’re going to do a quick tour through the first three normal forms, give an airing to an ancient Geek joke, and relive past footballing glory. At this point Scottish readers will be relieved that I plan to demonstrate the concept of denormalization without referring to 1966. Instead, I will take as my example, the apogee of Columbian Footballing achievemnt, the 2001 Copa America.

Installing Ubuntu on an EMachines E350 netbook

I am now the proud owner of an EMachines E350 netbook. Well, proud is probably overstating it. It’s about the same spec as most of the other netbooks out there and it was cheap.
This particular model boasts 1GB RAM, a 160GB HDD, and an Intel Atom N450 processor.
It also comes with Windows 7 Starter as the OS. Hmmm, not sure about that last bit.

My purpose in obtaining this machine is to replace the venerable Laptop I’ve been carting around on the train for the last several months. Like the Emachines, it also has 1GB RAM. Unlike the netbook, it’s running Ubuntu 10.04.2 LTS.
So, all I need to do now is to wipe away Windows and install Ubuntu. Oh, and make sure the wireless network card still works ( which has often been a problem with this kind of exercise).

So, what follows is the steps taken to install and configure Ubuntu on my netbook, with all the mistakes and associated cursing removed so I can pretend I really do know what I’m doing.

If you want to follow along, you will need :

• a machine to experiment on ( or your new netbook)
• a machine running Ubuntu already
• a wired network connection
• a USB stick with at least 2GB capacity
• a bit of time (such as a Bank Holiday)

By the way, I’m going to ruin the suspense here and say that I’ve typed this on and posted it from the netbook in question, using OpenOffice Writer and a wireless connection. You can probably tell by the fact the typing is a bit slower and there are probably some amusing spelling mistakes. I put this down to getting used to the new keyboard.

A word of warning here – apart from the wireless card, I’m really not too bothered about the other peripherals on this machine. So, I’m not worried about the web cam, for example and have not covered this here.

Custom Reports in PL/SQL Developer

Having played around with PL/SQL Developer, one of the frustrations I’ve found is that the extensibility of the tool relies on you being able to create dlls.
Not being a Microsofty, I’ve found it a bit limiting when compared with SQLDeveloper, which allows some fairly significant add-ons by the simple application of a bit of XML.

Don’t get me wrong, I quite like PL/SQL Developer, not least because it allows you to run SQL*Plus scripts pretty much unedited.

Now, I wanted a way of displaying the information held in my CRUD application without having to go to the trouble of typing the statement in each time.
The answer to my problem – the PL/SQL Developer custom report.

Creating the Report

In PL/SQL Developer, go to File / New/ Report Window.
This is where you type in your SQL statement.

As with SQL*Plus, any runtime parameters are prefixed by an ‘&’.
The way you name and define these parameters is a little different however.
In my case, I want to give my variables a name that will show up when the user is prompted to enter them at runtime. Both of the variables are mandatory, and both should be converted to uppercase.

The end result is a query that looks like this :

SELECT object_owner, object_name, object_type,
       create_flag, read_flag, update_flag, delete_flag
FROM crud_owner.db_crud
WHERE table_owner = '&<name="Table Owner" required="yes" uppercase="yes">'
AND table_name = '&<name="Table Name" required="yes" uppercase="yes">'
ORDER BY 1,2,3

There are a (bewildering) number of configuration options for the report, but I’m quite happy with the default output so I simply have to save the report in a file with a .rep extension.

Adding the Report to the Menu

Back at the main menu, select Tools / Configure Reports…
In the Configure Reports dialog box, click on the yellow folder icon and navigate to where you saved your .rep file.
Make sure that the Report as main menu item checkbox is checked then click OK.

When you next open the Reports menu, you should see your new report at the bottom of the list.

I don’t think I’ll ever learn to love PL/SQL Developer ( or any other Oracle IDE for that matter), but at least this sort of thing makes life a little more bearable.

The gedit bug with Shared Folders in VirtualBox – a Workaround

The trouble with a Virtual Machine is that it’s, well, virtual. Sooner or later, you’re going to want to transfer some files back to the host.

Unfortunately, gedit seems to have a bit of a problem getting it’s head around this process. When you do try to change a file in the shared directory, gedit complains :

Could not save file path to file on shared folder
Unexpected error: Error renaming temporary file : Text file busy

All is not lost however. Yes, this does appear to be a bug, but there is a solution that will enable you to continue using your favourite Gnome editor inside Virtual land.
For this you will need :

• a local folder on the Guest OS
• a shell script
• an alias in .bashrc
• some sticky-back plastic

OK, so I made that last one up.

Top-N Queries in Oracle – SQL at the sharp-end of the season

It’s coming up to that time of year once more. Games are running out, permutations are being permutated, bottoms are beginning to squeak.

The question on everyone’s lips ( well, Simon’s actually) is, who will fill the Play-Off places in the Conference ?

For those of you not acquainted with the lower echelons of English Professional Football ( or Soccer, if you really must), The Conference is the fifth tier of English football and the pinnacle of non-League. Each year, the winners gain automatic promotion to the promised land of the Football League with those finishing second to fifth playing-off for the right to join them.

All of which gives me the perfect opportunity to forsake my old habits and bring my SQL bang up-to-date.

SQLDeveloper 3.0 and the Missing Imported Connections in Ubuntu

SQLDeveloper 3.0 production has finally been released.

Like a kid on Christmas morning, I ripped off the wrapping paper and plunged straight into the installation on my Ubuntu laptop.
All was going according to plan…until I fired it up and found that it hadn’t imported my connections from SQLDeveloper 3.0 EA4 ( 3.0.3.97).
It seems that the production version has got a bit sniffy about it’s Early Adopter sibling although, oddly, it does still import any extensions you have installed.
Anyway, to save you the trouble of setting up all your connections again…

Oracle Instant Client on Ubuntu…with added Aliens

“This is the voice of the Mysterons…have you got any Lemsip ?”
Yep, I’ve caught Deb’s cold and now sound like the alien menace from Captain Scarlet.
This provides a somewhat tenuous link to the subject at hand – namely installing Oracle Instant Client on Ubuntu.
I think I’d better explain. As you probably know, Ubuntu – being a Debian based Distro – uses the Debian packaging mechanism. Oracle, on the other hand, provides Instant Client for Linux in rpm ( RPM Package Manager) format. In order to bridge this divide, we’re going to need to use the alien utility. Look, I did say it was tenuous OK.

I’m doing this on a 32-bit Ubuntu installation ( 10.04, since you ask). If you’re running 64-bit, you’ll need to download the appropriate equivalent files.

More Oracle Dating Tips – High Days and Holidays

Following on from my post about Dates the other week, I’ve been looking around (afer all, there’s no harm in looking). There’s a fair number of clever date manipulation routines out there, calculating a business week, the tax year etc.
Work, work, work. What would be really useful is something that can work out when the Public Holidays are this year.

Installing Ubuntu in VirtualBox on a Windows 7 Host

It’s Sunday afternoon and Deb is feeling poorly…but not so poorly that she’s not offering considerable editorial input into this post. In between the sniffles and requests for lemsip etc…she’s throwing in various comments relating to various icons from the sci-fi genre. To be fair, I have borrowed her laptop as it’s the only one in the house running Windows 7.
I will attempt to minimize the in-jokes and references but I thought I’d better go on record and report the fact that I am under some duress here. Honestly, some people are soooo geeky !

Reverse Engineering a Crud Matrix complete with SQLDeveloper Extension – Version 2

A couple of years ago, I wrote an application to reverse-engineer a CRUD matrix for tables in an Oracle database.
I’ve since used it quite a lot for impact analysis and have refined it a fair amount. I’m now happy enough with the new version to let it take it’s first steps into the wider world….where doubtless people will be able to find some of the bugs that I’ve missed.
At this point, if you’re wondering what a CRUD matrix is, you can have a look at the original post here.