RDBMS Basics: SQL Database Fundamentals

Let's talk about RDBMS basics. If you’ve ever dipped your toes in the warm but deep waters of programming, then you’ve probably wanted to store data at some point or another. Whether it’s user data, scientific results or anything else that makes your program dynamic. SQL is one of the most popular tools to do exactly that. It has been around for decades, yet it is still used today to manage databases of different sizes with great success.

It may seem daunting, but hopefully, we will be able to answer some of your biggest questions here. Let’s dive right in!

What is SQL?

SQL stands for Structured Query Language and is generally pronounced as “sequel”. It’s a language used to manage databases and offers features to create, read, update and delete data in a way that makes it easy to make connections or relationships between different bits of data.

There are multiple versions of the SQL language, each with slight differences in features and syntax. However, they all support most of the major features and commands. Even if you learn one version, your knowledge should transfer fairly easily to any of the others. The most popular at the moment are Oracle, MySQL, Microsoft SQL Server, and PostgreSQL.

Another feature of SQL is that its basic syntax is relatively easy to understand, as it somewhat follows a sentence structure. It uses keywords like SELECT, INSERT, WHERE, etc. For example, to retrieve all names and emails from the Person table, where the person’s name is “Nick”, you could write the following:

SELECT Name, Email FROM Person WHERE Name = ‘Nick’

What is a RDBMS? How does it relate to SQL?

A relational database management system (RDBMS) is an application on a computer that can manage large amounts of data quickly and efficiently.

The main property that sets relational databases apart from other databases is that data in separate tables can be linked, meaning you can specify a relationship between different bits of data. For example, one table could specify a list of people, while another could list each person’s address. The tables could then be linked closely together by having each row from the Address table reference a particular row in the People table.

One of the major alternatives to relational database are object based databases, which store data like a tree: a single parent with many branches, which each can have sub branches, like this:

{
  1: {
    name: Nick,
    email: nick@thatemailofyours.com,
    address: {
      street: Sesame Street,
      number: 42
  }
}

Next, we’ll discuss what a SQL database looks like.

What is a table?

Data is generally stored as tables with columns and rows, very similar to an Excel spreadsheet. Attributes like Name and Email Address are stored in columns, with each separate record being a row in the table. Unlike the example above, the data would look like this:

Person

ID	Name	Email
1	Nick	nick@thatemailofyours.com

Address

ID	PersonID	Street	Number
1	1	Sesame Street	42

Note that the PersonID in the Address table relates to the ID column in the Person table.

While it may seem like more work to organise your data like this instead of the simple object model above, the real power of it comes into play when you have a lot of data with multiple relationships. In the simple object model, you will probably end up with multiple versions of the same data, structured in different ways.

For example, using the example above, if you wanted to find everyone living at the same address:

• In the object model, you’d have to go through each record in order to access their address and then compare it. You can imagine that you could have much more data stored underneath each Person node (like purchase history, loyalty points, phone numbers, you name it), so you’d be accessing a lot of data that you don’t need.
• In the relational model, you can simply go through the Address table only and find out people living at the same address by looking at the PersonIDs that you now have!

What is a tuple, record or row? What about an attribute?

As we mentioned above, tables consist of columns and rows. Each row represents a single item in the database, where each column is an attribute of that item. Rows are also called tuples or records.

In the example above, a single item in the Person table is a row (or tuple/record), whereas Name and Email are attributes.

How do you organize a database?

As you may have noticed in the example above, tables have a very obvious structure, with predefined columns. Each row has to adhere to those columns and have a value for each (even if it’s a NULL value, meaning no data). To keep track of what goes where we use a database schema.

A schema lists the name of each table, what columns it has and what type each column is. For example, the Name column in the Person table would likely be of type varchar, meaning a string of characters. However, for a date of birth field, you would probably use a date or datetime type.

There are a great number of different data types you can use in SQL, and, as with most programming languages, some are more useful than others. For more information, see the Data Types section further down.

What’s all this about keys?

Primary keys

Now that we know we can associate bits of data together (e.g. a person’s address by referring to the PersonID from the Address table), let’s talk about keys. They are a feature of SQL that lets you define what makes a row in a table unique. For example, in a table of users with various fields (username, email, first name, last name, date of birth), you could make the username or email address the primary key, meaning you can only have a single row in your table with any one particular value in that column. This makes sense as you generally can’t have multiple accounts with the same email address or username.

Candidate keys

You can also use a combination of columns as your primary key, called a candidate key. For example, in a table that contains a list of playing cards with a column for the suit and a column for the number, you could make the combination of suit and number the unique identifier, as you can have four Jacks but only one of each suit, for example.

Foreign keys

When you have attributes of a particular thing in your database (e.g. a Person with an Address and OrderHistory, all as separate tables) you can link these tables together using foreign keys. In the Address table example above, we had a column that referred to the Person the address belongs to by referring to the ID of that person in the Person table. Without creating any constraints, we could put any old number in there, even if there is no row in the Person table with that ID.

However, by adding a foreign key to this table, we can link the PersonID column on the Address table to the ID column on the Person table. If you then try to add a row to the Address table with a PersonID that doesn’t exist, it won’t let you.

You might think that’s unnecessarily strict, but it’s a great way to ensure you’ve got consistent data so you can query your database with confidence, especially once your database starts to grow very large!

For more information about keys, see this great write up.

Data types

The list below describes some of the most common and useful data types you can use in SQL. There are more of course, so if you would like to dive deeper, check out this documentation from Microsoft.

Note: the following data types are from Transact-SQL, the version used in SQL Server. Other versions of SQL might provide different data types, but most will be very similar.

Type	Description	Examples
int / bigint / smallint	Integers (whole numbers) with various maximum values, can be negative. Primary keys generally are of type int.	0, 1, -512, 764,994
tinyint	Like int but can only be between 0 and 255.	0, 1, 2, …, 255
float	A number with support for decimals. Values are approximate.	3.14, 24.75579, 1.0
money / smallmoney	Like float but more accurate. As the name suggests, they’re useful to store monetary values.	45.0123, 35.99, -680,550.78
date / datetime / time	Describes date and/or time values.	2018-07-24, 2002-08-29 08:29:55, 16:19:59
char / varchar / nvarchar	Various types to store text or strings of characters. char stores a fixed length of characters, varchar stores a variable length of characters and nvarchar also allows Unicode characters. When using these, you need to define the maximum number of characters per entry you want to support.	’Hello World!’
uniqueidentifier	Globally unique identifier (GUID). These are unique across your whole database.	0E984725-C51C-4BF4-9960-E1C80E27ABA0
xml	Stored data in the XML format. Certain versions of SQL will allow you to query into these values, e.g. to find a particular attribute inside the XML.	<OrderHistory> <Order Username=“nick” Total=“39.99” /> </OrderHistory>
json	Similar to XML but a little more modern. It is less strict and uses fewer characters.	{ orderHistory: [{ username: “nick”, total: 39.99 }] }

SQL Constraints

We can impose constraints on our data in a number of ways. We’ve already seen two, primary keys and foreign keys, but there are a few more:

Constraint	Description
NOT NULL	Ensures a column cannot be NULL (basically meaning “no data”)
UNIQUE	Every value in that column needs to be unique. Primary keys are a combination of NOT NULL and UNIQUE constraints.
CHECK	Every value in that column needs to satisfy a provided expression (e.g. value needs to be between 200 and 500)
DEFAULT	If no value is set, it sets a default value instead.
INDEX	This is used to store the table in such a way that it’s fast to retrieve data when this column is used as an input value (e.g. an index on the date of birth column will make it fast to search by a particular date of birth). Crucial when dealing with large tables.

Thanks for reading!

We hope we were able to answer some of your most burning questions about SQL and relational database management systems, though this was just scratching the surface. If you have any questions or would like to provide feedback, please post a comment below!

If you would like to learn more about SQL check out intro to SQL or compliment your knowledge of big data with Power BI, and Power Query courses.

Learn in-demand skills

Take your career to the next level with GoSkills coding courses

Start free trial