Database Concepts

What is the Year 2038 Problem?

January 28, 2021June 19, 2019 by Ian

The Year 2038 problem (also referred to as the Y2K38 bug) refers to a problem that some computer systems might encounter when dealing with times past 2038-01-19 03:14:07.

Many computer systems, such as Unix and Unix-based systems, don’t calculate time using the Gregorian calendar. They calculate time as the number of seconds since 1 January 1970. Therefore, in these systems, time is represented as a big number (i.e. the number of seconds passed since 1970-01-01 00:00:00). This is typically referred to as Epoch time, Unix time, Unix Epoch time, or POSIX time. As I write this, Unix time is 1560913841. And as I write this next line, Unix time has incremented to 1560913879.

What is Collation in Databases?

February 14, 2023April 23, 2018 by Ian

In database systems, Collation specifies how data is sorted and compared in a database. Collation provides the sorting rules, case, and accent sensitivity properties for the data in the database.

For example, when you run a query using the ORDER BY clause, collation determines whether or not uppercase letters and lowercase letters are treated the same.

Collation is also used to determine how accents are treated, as well as character width and Japanese kana characters. Collation can also be used to distinguish between various ideographic variation selectors in certain collations (such as the Japanese_Bushu_Kakusu_140 and Japanese_XJIS_140 collations that were introduced in SQL Server 2017).

Different database management systems will provide different collation options. Depending on the DBMS, collation can be specified at the server level, the database level, the table level, and the column level. Collations can also be specified at the expression level (so you can specify which collation to use when you run a query), and at the identifier level.

“This Data” vs “These Data” – Which is Correct?

February 14, 2023November 19, 2017 by Ian

If you happen to read a lot of data-related material, you might occasionally find the word “data” being treated in different ways. In some cases you’ll see “this data is…” and in other cases “these data are…”. You might even think “they obviously made a mistake with their grammar”.

Not so fast!

What is OLAP?

January 21, 2026October 11, 2017 by Ian

OLAP (Online Analytical Processing) is a category of database processing that facilitates business intelligence.

OLAP provides analysts, managers, and executives with the information they need to make effective decisions about an organization’s strategic directions. OLAP can provide valuable insights into how their business is performing, as well as how they can make improvements.

What is OLTP?

October 23, 2025October 11, 2017 by Ian

OLTP (Online Transactional Processing) is a category of data processing that is focused on transaction-oriented tasks. OLTP typically involves inserting, updating, and/or deleting small amounts of data in a database.

OLTP mainly deals with large numbers of transactions by a large number of users.

What is Normalization?

February 20, 2026October 6, 2017 by Ian

Normalization is the process of organizing a database to reduce redundancy and improve data integrity.

Normalization also simplifies the database design so that it achieves the optimal structure composed of atomic elements (i.e. elements that cannot be broken down into smaller parts).

Also referred to as database normalization or data normalization, normalization is an important part of relational database design, as it helps with the speed, accuracy, and efficiency of the database.

What is the Third Manifesto?

January 21, 2026October 3, 2017 by Ian

The Third Manifesto is a detailed proposal for the future direction of data and database management systems (DBMSs).

Written by C.J. Date and Hugh Darwen, The Third Manifesto can be viewed as a blueprint for the design of future DBMSs, as well as any language designed to interface with them.

Codd’s 12 Rules

November 11, 2025October 2, 2017 by Ian

Codd’s 12 rules is a set of rules that a database management system (DBMS) must satisfy if it’s to be considered relational (i.e. a relational DBMS).

The rules were proposed by Edgar F. Codd, who is considered a pioneer of the relational database model.

Codd’s 12 rules is actually a set of thirteen rules, numbered from zero to twelve. The twelve rules are based on a single foundation rule — Rule Zero.

RDBMS vs DBMS – What’s the Difference?

February 14, 2023September 29, 2017 by Ian

There’s a lot of confusion regarding the difference between an RDBMSs and a DBMS. I’ve even seen “RDBMS vs DBMS” forum posts where the accepted answer outlines the differences between RDBMSs and DBMSs, as though they were two distinct and different things.

However, this can be misleading.

The fact is, an RDBMS is a DBMS. But a DBMS is not always an RDBMS (but it often is).

So, is there a difference between an RDBMS and a DBMS or not? Is “RDBMS vs DBMS” the right way of looking at it, or is there more to it?

NoSQL Database Types

June 24, 2016 by Ian

NoSQL databases are often categorised under four main types. Some databases are a mix between different types, but in general, they fit under the following main categories.