Database Terminology for Beginners

by Agnieszka Kozubek-Krycuń

Beginners are often overwhelmed by the different terms they encounter when they first start out with databasing — database, table, row, server, SQL, PostgreSQL, MySQL. The list goes on. I’ll explain these (and other) terms in this article.

Data is not a database

One problem with the word “database” is that IT professionals and other people use the term differently. And it can get confusing.

In everyday language, people often use the term “database” to mean “data” or “information”. You’ll hear stories of sales representatives who left their companies and took their “client databases” with them. Spammers offer to sell you “email databases”. And hey — a friend of mine even has a “database” of his favorite soccer team’s fan clubs.

In each of these examples, the term “database” is taken to mean “information” or “data”: a sales representative took his customers’ business cards with him, a spammer wants to sell you a list of email addresses, and my friend just has a random Excel file listing fan clubs and the cities where they’re located.

While this is a legitimate usage of the term “database”, it’s not what IT professionals think of when they use the term.

What is a database?

For an IT professional, a database is a computer program that knows how to store and process large amounts of data. Of course, lots of computer programs can already do that. For example, Excel and your email program store and process data regularly.

But a database can process much larger amounts of data. While regular programs can typically only handle megabytes of data, a database can process gigabytes or even terabytes of data.

A database provides additional features for handling data. For one, it allows multiple users to access and modify data at the same time (known as concurrent access). It also provides a security layer: you can control who has permission to see or modify which data. A database can search the data quickly and efficiently, and it can quickly perform complex computations on data. Data in a database are also structured. A correctly structured database can even check the correctness or completeness of your data.

There are many different kinds of databases. The most common databases nowadays are relational databases. Other kinds of databases include graph databases, document databases, NoSQL databases, and others. These are used less frequently, generally only in special cases. I’ll focus on relational databases in this article.

Big Data Technologies
Note that there are also technologies that can process even larger amounts of data. They are called big data technologies. This name is used to describe technologies that can process petabytes of data, or data that change very quickly. These technologies (e.g., Hadoop, Spark, Hive, or Presto) are typically not called databases, as their underlying principles differ from those of databases.

The language of databases

A relational database stores data in tables. Every table consists of columns and rows. You can think of a table as a named spreadsheet in Excel — that’s not entirely accurate, but it’s good enough for starters.

A database lets you search, filter, and process your data. The language you use to query a database is called SQL (Structured Query Language).

With SQL you’re not limited to predefined search criteria — you can specify your own. Interested in finding your company’s female clients between the ages of 38 and 42 who live in rural Wyoming? f you have access to the right data and know how to write the proper query, SQL will list everyone matching these criteria in just a few milliseconds.

SQL can also perform simple computations on your results. For example, SQL can calculate how much money your Wyoming-based clients spent on your products in each week of 2017. If you’re interested in learning more about SQL, check our SQL Basics course to get a better understanding of writing basic SQL queries.

You can also use SQL to insert, modify, or remove data when working with a database. The term Data Manipulation Language (or DML) is used to describe any language that modifies data. Applications use DML to insert information about new purchase into your database, update your client’s delivery address, or remove a certain client’s information when they close their account. At its core, SQL is a DML. You can learn more about modifying data with SQL in our free Operating on Data in SQL course.

One more abbreviation you’ll encounter is DDL (Data Definition Language). This term refers to a language that describes how data are structured. Of course, SQL is also partly a DDL, since it allows you to create your own tables and define how they are structured. DDL operations are primarily used by software architects and software developers who design a database’s structure. A good introduction to DDL is learning how to create tables in SQL.

The process of designing tables in a database and specifying their relationships is known as database modeling. SQL DDL gives you the syntax — the language — needed to create new tables, but there are also some rules that help you create useful and error-proof tables. Database modeling is often taught in academic database courses.

Database engines: PostgreSQL, MySQL, Oracle, SQL Server

You may have heard terms like PostgreSQL, MySQL, Oracle, and SQL Server. What are these? They’re database engines. Think about various spreadsheet software. There’s Microsoft Excel, Apple’s Numbers, and the open-source LibreOffice and OpenOffice, among others. These are all spreadsheet software. Their general goal is the same: to store data in tabular form and allow users to interact with and process those data.

It’s similar with relational databases. PostgreSQL (often just called Postgres for short), MySQL, Oracle, Microsoft SQL Server, IBM DB2, SQLite, and others are all different database software. Their general goal is the same: to store and process large amounts of data.

The general principles are the same, but the details of how database engines work internally will differ from one to another. Some database engines are free, like MySQL, PostgreSQL, and SQLite. Other database engines, like Oracle, Microsoft SQL Server, and IBM DB2, are paid (though they often have free or community versions).

SQL in database engines

How does SQL fit into all of this? How is SQL used in different database engines? Well, each database engine understands SQL, but each database engine also uses a slightly different variant of SQL. It’s like English. If you speak English, you can go the UK, the US, Canada, or Australia, and you’ll be able to communicate with people. Still, some idioms or expressions may only be understood locally, and you may have trouble using them with the wrong people.

ISO (International Organization for Standardization) and ANSI (American National Standards Institute) are two standard-setting organizations that release an SQL standard every few years. The latest SQL standard was released in 2016.

Here’s the great thing about standard SQL: once you understand it, you’ll be able to easily pick up any popular database engine you want. For common SQL constructions, the differences in SQL syntax between different database engines are generally minor. The problem with the standard is that none of the major database engines completely comply with it.

Vertabelo Academy courses adhere to standard SQL when possible to make the content accessible and universal. In other words, whatever you learn in our course will transfer over to all popular databases engines. And we usually make a note if a certain database engine uses a different syntax for a particular feature.

SQL in other technologies

SQL is a very popular language for processing data. Many technologies other than relational databases use SQL. For example, you can use SQL’s dialect in Hive (big data technology) or, ironically, even in NoSQL databases.

Summary

A database is a computer program that stores and processes large amounts of structured data. In a relational database, data are structured in tables, which consist of columns and rows. You use SQL to access or modify data in a database. Knowledge of standard SQL — the one we teach at Vertabelo Academy — will allow you to quickly get started with popular database engines and other data-processing technologies.

Next steps

Nowadays, SQL is the de facto standard language for accessing data. If you want to work with data, knowledge of SQL is an absolute must — and SQL is an in-demand skill that many employers are looking for.

The best way to learn SQL is by doing — because you can’t expect to write great queries if you never apply your knowledge. Our hands-on SQL courses help you master SQL by writing over 100 of your own SQL queries.