Databases: Relational Spreadsheets

Databases are found in everything we do. Banking, chatting, even watching a video involves a database. But what is it exactly? How does it work? And why would anyone use this thing they’ve barely heard of?

Database: Excel but more

So to talk about databases, we should have this nice metaphor so you can follow along at home. Think of a database like… a wardrobe.

It’s one of them fancy ones with the sliding mirrors.

Why a wardrobe? Because it’s the closest thing that came to mind when I was thinking this up, and I blanked on anything else.

So this wardrobe. What does it have, and what can we do with it?

• You can put socks in the drawers
• You can hang clothes off the hangers
• You can put folded shirts in the open shelves
• You can put scuba gear up top in the space you might forget about

We can do this, and much more with a wardrobe. So if we consider the database as a wardrobe, the clothes can be considered as information. At the very least, we can consider it as such once we put it in. Before it becomes information (the useful kind), it’s merely data:

What a bargain! It hasn’t been organised yet.

There are many ways of organising data. Databases aren’t the only things we can use:

• A text document: Not your first choice, but you can do it if you have nothing else handy
• Spreadsheets: Probably what you’re thinking of. You can insert data and process it. You can make charts from it, and calculate things from it.
• Database Management System (DBMS): The one we’re talking about. You can create relationships between information, and do really interesting things with it.

What sort of interesting things can a DBMS do? Well, they can help you define a structure so that you can insert data into it. This structure, or formatting can be used to organise, save, protect, and retrieve data.

By utilising these features of a DBMS, you can tell your wardrobe to pull out your best suit, or find your not-yet-ironed shirts. You can tell your wardrobe to only hold socks as pairs, or that the kitchen utensils have no place in the drawers.

These drawers are known as tables, except that they’re not tables you sit down and have a meal at. Rather, these tables are structures defined with columns (or fields)―which can hold any content we want it to. Unlike spreadsheets, a database can apply rules, or constraints, to ensure only certain data is inserted into the table.

You can stick your socks with your shirts, or something.

When you want to pull them out again, you can query the database for what you want, using criteria to get an entry (or row). Using what you know about an item, you can find it again. Let’s get a nice shirt for the day using something called Structured Query Language (SQL). I’ve formatted it so it’s easy to read:

SELECT * FROM drawer
 WHERE folded = TRUE
   AND ironed = TRUE
   AND colour = 'orange';

First of all, the select statement. SELECT * FROM drawer means in human terms, to select all the columns (details) from the table called drawer.

WHERE folded = TRUE is a clause that tells the DBMS to only get records where it has been folded.

AND lets us add additional criteria so that we only get ironed clothing that is orange. You can substitute this with OR, but for the purposes of getting clothing, it’s much less useful.

SQL lets you define the structure, enter data, and retrieve the data. Do you want something that isn’t orange? Just add a NOT to the last statement:

AND NOT colour = 'orange'

and there you go, no shirts that are orange!

These features are why so many people use databases. Without them, we’d be stuck with a filing cabinet only available from that one place 18 hours away, and you don’t want to go there.

User Login: You can relate to this

A login system by far is one of the easiest examples to look at when accessing a database, since we’re doing it all the time. For example, let’s take a banking organisation called VX:

VX stands for five ten, at least if we interpreted them as Roman numerals. What it means? I dunno.

This bank is pretty new, and the fact it shakes up an oligopoly appeals to you. No ATM fees, good interest in savings accounts, and it hasn’t been hacked… yet. A representative has handed you some credentials to demo how you can bank from anywhere, including your phone.

You enter the credentials, and… you’re logged in! That’s one place where you’ve just interacted with the database. The backend of the banking server has done all the hard work, but in some more detail, the system queries the database to:

• check that the user you’ve specified exists
• check that the password you’ve provided (the hash and salt) matches that of the database
• check that your account is not suspended for any reason

And then it logs you in. Then the database does some more work, now you’re authenticated:

• It writes a session entry so you stay logged in (with the help of cookies)
• It gets the financial accounts you own, and how much money is stored
• It gets other account details so it can personalise your experience (for example it can greet you by your name, rather than your account number)

That’s not all. If you have to update your details, you’ll notice you can do things only according to a certain pattern. Your phone number can’t yet be 12 numerics and a letter. The postcode must be 4 numbers. The e-mail address has to be valid.

In all those cases, the application handles the logic. But in case it doesn’t, the database will prevent you from updating those details incorrectly.

And with that concludes this segment on databases!