Database Concepts

10 Essential Database Concepts that All Beginners MUST Learn

July 31, 2024 by Ian

When I first started building websites 25 years ago, everything was static. Our primary building block was HTML, with a bit of CSS and JavaScript sprinkled in for good measure. Our content would be incorporated into the HTML documents, and so there was no separation of functionality and content.

Regardless, I was just happy to be able to build these amazing things that I could publish for the world to see.

But within a few years I had quickly learned about the power of databases and how they could completely transform the websites we were building at the time.

What is a Composite Primary Key?

August 9, 2024March 11, 2024 by Ian

Primary keys are a crucial part of relational database management systems (RDBMSs). Most of the time we create a primary key constraint against a single column that is the unique identifier for the table.

But we can also create primary key constraints against more than one column. When we do this, we call it a composite primary key.

Composite keys can be handy when we don’t have a single column that contains unique values, but multiple columns can be combined to create a unique value.

Understanding INFORMATION_SCHEMA in SQL

June 28, 2023 by Ian

Behind every well-structured and organised SQL database lies a treasure trove of information called metadata. Metadata is data that provides information about other data. In relational database management systems, this metadata can typically include information like the names of databases, tables, columns, the data type of a column, access privileges, and much more.

The SQL standard provides a collection of tables/views that allow us to view metadata about the objects in the database. The SQL standard refers to this as the information schema and calls it INFORMATION_SCHEMA.

What is CRUD?

March 4, 2023 by Ian

In the world of database management systems (DBMSs) and computer programming, CRUD is an acronym that stands for Create, Read, Update, and Delete. These are considered to be the four basic operations of persistent storage.

Let’s take a look at how CRUD fits into database management systems and programming in general.

What is TCL?

November 11, 2025February 21, 2023 by Ian

When using relational database management systems (RDBMSs) we often hear terms like DDL, DML, DQL, DCL, and TCL. But what exactly are they?

In this article we’ll look at what TCL stands for in the context of SQL, and what it does.

What is DCL?

February 21, 2023February 19, 2023 by Ian

When using relational database management systems (RDBMSs) we often hear terms like DDL, DML, DQL, TCL, and DCL. But what exactly are they?

In this article we’ll look at what DCL stands for in the context of SQL, and what it does.

What is DQL?

February 21, 2023February 18, 2023 by Ian

We often hear terms like DDL, DML, DQL, TCL, and DCL when using relational database management systems (RDBMSs). But what exactly are they?

In this article we’ll look at what DQL stands for, and what it does.

What is DML?

February 21, 2023February 17, 2023 by Ian

We often hear terms like DDL, DML, DQL, and DCL when using relational database management systems (RDBMSs). But what exactly are they?

In this article we’ll look at what DML stands for, and what it does.

What is DDL?

February 21, 2023February 16, 2023 by Ian

We often encounter terms like DDL, DML, DQL, DCL, and TCL when using relational database management systems (RDBMSs). But what do they mean?

In this article we’ll look at what DDL stands for and what it does.

Signed vs Unsigned Integers

February 14, 2023February 9, 2023 by Ian

The integer data type is probably one of the more common data types when working with database management systems (and with computing in general). The integer is a numeric data type that allows us to store certain kinds of numbers.

More specifically, an integer is the number zero (0), a positive natural number (e.g. 1, 2, 3, …) or a negative integer with a minus sign (e.g. −1, −2, −3, …). Integers contain no decimal or fractional part.

However, many computing environments distinguish between signed integers and unsigned integers.

Let’s take a look at the difference between signed integers and unsigned integers.