14. Databases

Programming is the art of manipulating data. Thus far, we’ve only manipulated in-memory data, meaning that the data we’ve used in our pseudo-programs, was created when the programs ran, and destroyed when the programs terminated. A significant proportion of programming deals with persistent data. In programming terms, persistence refers to the fact that the data needs to be saved somewhere for later use, even once the computer has been shut down. When you write a letter using your word processing program, you save the file to disk, USB stick or other storage media so that you can access it later on. Let’s examine how we apply persistence to programming.

Why Save Data

Before we explore this topic, let’s establish why it is so important that programs can save (persist) data. Imagine you’ve created an app in which your users can register with a username and password. In order for your users to be able to login to your app in the future, they need to be able to use the login credentials that they entered upon registration, without your app asking them to register again. This means that somewhere, somehow, usernames and passwords need to be stored for later use.

Another illustration would be an app that lets users post articles to their personal blogs. It’s logical that these articles must be persisted somewhere, right?

Where? And how? By using a database of course!

So What, Exactly, Is A Database?

A database is a set of organized data that lies within a specific type of software called a Relational Database Management System (RDBMS for short). RDBMS’s come in many flavors and there are numerous RDBMS vendors in existence. The most well known RDBMS’s include MySQL, Oracle, Microsoft SQL Server, PostgreSQL and SQLite. Notice that several of their names include the letters SQL? The reason for this will soon become apparent!

The sole job of a RDBMS is to store data in a consistent and structured fashion. In and of themselves, RDBMS’s don’t do much, however their innate power is unleashed when connected to an app; your app!

Tables

Tables are the central components of all databases. Tables aren’t the only important parts of databases, but for now they will be our primary focus. In time we’ll examine the other components to complete the picture.

All making sense so far? Great! Let’s look at an example:

Assume that you want to save a list of cities. You’ve probably used a spreadsheet program such as Microsoft Excel or Apple Numbers and if you were to use one to store your list of cities, it would probably look something like this:

In a database, a table is similar to an Excel sheet. If you were to save the list of cities in a database, you’d create a table titled City (it’s worth noting that programmers generally title tables using the singular form, as they do with classes), with one column titled Name, populated with city names. Here’s how the table would look using Microsoft SQL Server:

In a database table, a row is called a record.

Relations

The true power of using a database over simply saving your data in a plain text file is that you can use relationships to group, arrange and sort data. Remember what the R in RDBMS stands for? Relational. Let’s say you also want to store each city’s matching country in your city names spreadsheet. How would you go about it? You’d probably create a second sheet and list your countries in a column like this:

What if you now wanted to display a list containing two columns: City and Country? Using Excel, you’d probably manually type/paste the country names into fields on the city name sheet (or vice versa).

Using a database, we’d approach the task differently. We’d create a Country table with a column titled Name. We’d then add a column titled Id to uniquely identify each row. This is termed a primary key. The Country table would now look like this:

We’d then update the City table to include a primary key:

To pair cities with their matching countries, we’d need to add one more column named CountryId to the City table. By placing the country’s primary key in the City table within the Id column, we will have created a relation. This CountryId column is called a foreign key and our City table would now look like this:

If, for example, we wanted to know which country the 3rd city belongs to, we’d look at its CountryId value, go to the Country table and look for that value in the CountryId column.

As we’ll soon discover, a database can do this matching automatically!

Structured Query Language

When we need to browse a database, join matching data, or filter and sort that data, we would typically use a dedicated programming language specially designed for this job. This language is known by its acronym – SQL – which stands for Structured Query Language and is pronounced “sequel”. SQL allows us to issue statements such as Find all the cities in the USA and sort them in alphabetical order. Curious to see what the actual SQL statement looks like?

SELECT City.Name, Country.Name
  FROM City
  INNER JOIN Country
  ON City.CountryId = Countries.Id
    WHERE Country.Name = 'USA'
  ORDER BY Cities.Name

This snippet of code is called a query and below we can see how it’d appear in Microsoft SQL Server, and the result that it returns.

This is all new to you so it probably looks complicated, but don’t be intimidated! Its quite straightforward and we’ll make sure to take you through the details in simple terms when we start working with data in our apps.

A Note On Database Programming

Despite being an interesting and closely related topic, designing and programming databases lies outside the scope of an app programming course. Database programming and Database administration are specialised professions and highly sought after skillsets in the financial, scientific and statistics sectors. SQL consistently ranks highly in the TIOBE index – an index of the most commonly used programming languages.

We will dedicate ample time and attention to teaching you the requisite SQL skills that you’ll need to develop apps that manipulate persistent data. In later lessons, we’ll examine SQL keywords such as INSERTSELECTDELETEUPDATE and the queries you might write with them.

Summary

You’re now aware that data can be persisted in a database using a special kind of software known as a relational database management system, often abbreviated to RDBMS. You issue queries to a database using a language known as SQL which stands for structured query language and is pronounced sequel. To join and group associated data together, you make use of relationshipsprimary keys and foreign keys that uniquely identify records.

Important Words

  • Data persistence
  • Database
  • RDBMS
  • Table
  • Record
  • Primary key
  • Foreign key
  • Relationship
  • SQL
Exercise

Using a primary key and a foreign key, create two tables that have a relationship. Choose any topic you want but if you’re not in a creative mood, create tables for pets and pet owners. Detail how you’d store the two sets of related data and share your handiwork with the community, using the comments below!

Share to friends