Databases and BI glossary

This databases and Business Intelligence glossary defines, in one sentence, the key terms you will meet in our courses: the relational model, SQL, data warehouses, analytics and BI. Each definition is self-contained and links, where relevant, to the related course.

Relational database

A relational database organises data into tables linked by keys, queried in SQL (SQL Server, Oracle, PostgreSQL…).

DBMS

A DBMS (Database Management System) is the software that stores, secures and lets you query a database.

SQL

SQL (Structured Query Language) is the standard language for querying and manipulating relational databases: SELECT, INSERT, UPDATE, DELETE, joins… See our SQL language course.

Table

A table is a structure of rows and columns storing a set of data of the same kind in a relational database.

Primary key

A primary key is a column (or set of columns) that uniquely identifies each row of a table.

Foreign key

A foreign key is a column that references the primary key of another table, materialising a relationship between the two.

Join

A join combines rows from several tables on a condition, usually the equality between a foreign key and a primary key.

Index

An index is a structure that speeds up searching data in a table, at a small cost on writes.

Transaction (ACID)

A transaction groups several operations into an all-or-nothing unit, guaranteeing the ACID properties: atomicity, consistency, isolation, durability.

Stored procedure

A stored procedure is an SQL program saved in the database, executable on demand to automate processing.

View (SQL)

A view is a saved query presented as a virtual table, simplifying access to complex data.

Normalisation

Normalisation organises tables to avoid redundancy and inconsistency in data.

NoSQL

NoSQL refers to non-relational databases (document, key-value, column, graph) suited to large volumes or loosely structured data.

Data warehouse

A data warehouse centralises and historises data from several sources for analysis and reporting.

Data lake

A data lake stores large volumes of raw data (structured or not) at low cost, for later analytical processing.

ETL / ELT

ETL (Extract, Transform, Load) and ELT are the processes of extracting, transforming and loading data into a warehouse or a lake.

Business Intelligence (BI)

Business Intelligence covers the tools and methods that turn data into dashboards and indicators for decision-making.

OLAP

OLAP (online analytical processing) lets you quickly explore large volumes of data across several dimensions (analysis cube).

Data engineering

Data engineering designs and industrialises the pipelines that collect, transform and make data usable for analysis.

Apache Spark

Apache Spark is a distributed processing engine for analysing very large data volumes in parallel; at the heart of Azure Databricks. See our Azure Databricks course.

Snowflake

Snowflake is a cloud data warehouse that separates storage and compute for elastic, pay-as-you-go analytics. See our Snowflake course.

Power BI

Power BI is Microsoft’s Business Intelligence tool for building interactive reports and dashboards from your data.