What is a relational database?

Why am I writing about databases?

I am currently working on a number of projects that require a fairly decent database under the hood. In this case, it is an Oracle database, but I suppose it could have been another product by another company, but for what I am working on, it is an Oracle Database.

My role doesn’t actually require me to know the database all that well, we have Database Analysts or DBA’s as they are often known. However, they are a protective bunch and they don’t like the other IT folks poking about too much in case they break something. Which I sort of understand, because making it work and keeping it working is certainly something that requires effort.

I am more interested in something called Fusion Middleware which I often find myself decribing as a web front-end to a database. Which might sound like I am missing the point here as most websites are basically the front-end to a database, however, hear me out.

Most websites are the front to a database right?

If I wanted to build a PHP web application, there is very high chance that I will use an SQL database in the background. The difference in this scenario is that, to me at least, it feels like the focus is on the User Experience (UX) and the User Interface (UI). The database in this scenario is just a place we dump things and call again when required.

Fusion Middleware (FMW) feels different, it feels like the database is the most essential part of the system and the front-end was a secondary thought. Of course, there is a lot more to FMW that just an Oracle Database. It can be used as a middle man between several datasources, hence the term middleware. However, all of those datasources are also just databases if you drill down far enough.

The reason this differentiation is important is because, it means that the User Experience is second to the data source, whereas most public facing websites, a database in the background, whilst important, is secondary to the functioning of the website.

The Java influence

Fusion Middleware is further (select own word from the following) [ complicated / compromised / complimented ] by the fact that it is built with Java. Not JavaScript, Java. The thing that sounds like it might be connected, but it isn’t, JavaScript is nothing like Java, they only named JavaScript as JavaScript because Java was a hot new technology at about the same time as people were working on what would become JavaScript, in fact, it is a bone of contention as Oracle now own the trademark to JavaScript even though they never created the language and the language isn’t actually based on Java. I digress.

I was first introduced to Java nearly 25 years ago. By that point I had already been exposed to a number of programming languages, I wasn’t especially good at any of them, except maybe simple things like BASIC and Turbo Pascal, but Java came at around the same time as we were learning C++, which is a lot more similar to Java than JavaScript will ever be. As a result, I found it quite difficult to get on top of Java because of its similarities and differences when something else was still so new to me. As soon as the module (at university) requiring Java finished, I dropped it like a rock and shuddered at any future mention of the language. Yet here I am, 25 years later, working on a product that was built entirely in Java and as I am quickly finding here, when you really want to customise a Fusion Middleware application, playing with Java seems to be inevitable.

What is a database?

Oh yeah, there was a point to this blog post.

Databases were originally conceived as ways of storing digital data, there have been a few different approaches to storing this digital data over the years. At some point in the 1960s, it was understood that data storage was going to be more important as we moved forward and at the time, that data storage may have been organised, but there were no common standards, meaning that data on one computer, or even in one department, could not necessariliy be managed in same fashion. Therefore some common frameworks were developed.

We had something that linked data in a sort of network topology as follows:

To be frank, I don’t really understand how this worked, it seems a bit too theoretical to me. There was another proposal for more of a hierachical structure. This one makes a bit more sense to me and was indeed taken up by industry, becoming the dominant database structure over the next 20 or so years:

Relational Databases

For me, this is the structure that makes most sense. This relies on the concept of a two-dimensional table consisting of rows and columns, just like a spreadsheet. Unlike previous models, there is no predetermined link between content in different tables. Each table has what is called a primary key, this is a unique element, kind of like the address for each row in the table. If you want to link to data in another table, then you might add a foreign key, that key is the glue that holds two distinct tables together:

Oracle was one of the first companies to embrace the Relational Database design, initially under the name Relational Software Incorporated and with a product called Oracle v.2, obviously that product went on to be such a phenominal success that they changed the company name to reflect the success of the product, and from there, they went on to release several more products.

Newer databases

There are newer ways of storing data in a database, I won’t go into them here, but they do exist I promise.

Structured Query Language (SQL)

The relational approach to data was a driver behind what is now called SQL. It was developed by Big Blue (IBM) but it was Big Red (Oracle) that brought it to market.

The basics of SQL are quite simple to learn, although it is definitely one of those things that is simple to learn, but difficult to master:

SQL: Data Query Language (DQL)

We’ve probably all seen the SELECT statement:

SELECT account_name, status FROM dba_users;

SQL: Data Definition Language (DDL)

These define the structure of a database, as an example:

CREATE TABLE omd_table (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(150),
  anotherColumn INT);

SQL: Data Manipulation Language (DML)

Commands that manipulate the actual data within a table, as an example:

INSERT INTO my_table (name, anotherColumn) VALUES ('OMD', 42);

SQL: TCL & DCL

Data Control Language and Transaction Control Language are there as well, but they are a bit beyond this post, because they are a bit beyond me. But these are where we might give users access privileges to the actual database or where we might rollback the database to a previously committed state for example.

Fusion Middleware

Oracle grew, their database products became some of the most important products in their sector and at some point they started building other things, or buying companies that had built other things on top of those database products.

Fusion Middleware isn’t, or perhaps, wasn’t a product in its own right, it is a collection of products that have mostly been aquired by Oracle over the years. I think, and I may be mistaken here, but a lot of what I work with, was once called WebLogic and I believe that may have belonged to BAE Systems at some point.

Back to Oracle Database…

Oracle release a new version of their most famous product every 3-5 years. There is an unusual theme based naming sceheme, such as Oracle Database 9i which represented internet, or Oracle Database 19c related to Cloud functionality.

One of the interesting things about the Oracle Database product, at least I think it is interesting, is that the user space is pretty much unique no matter which OS it is installed on. Of course there are Operating System level optimisations, but how a user or a database administrator interacts with the actual user side of the product is the same is the product is installed on Windows, Oracle Linux or some cloud based service provider etc.