What Is a Relational Database (RDBMS)? Tables, Keys and SQL Explained

A relational database stores data in tables: rows and columns, like a set of linked spreadsheets with strict rules. Tables are connected through shared values called keys, so a question that needs several tables can be answered by joining them. You read and change the data with SQL. The software that runs such a database is a relational database management system (RDBMS), for example PostgreSQL, MySQL, SQL Server, Oracle or SQLite.

The model was described by Edgar F. Codd at IBM in 1970. It is still the most widely used way to store business data, and every data engineer works with it daily.

The vocabulary

Term	Plain meaning	Formal name
Table	A set of rows with the same columns, about one kind of thing	Relation
Row	One record, such as one employee	Tuple
Column	One fact recorded for every row, with a data type	Attribute
Primary key	Column(s) whose value identifies exactly one row
Foreign key	Column that stores another table’s primary key, to link rows
Schema	The definition of tables, columns, types and rules

The word “relational” comes from the mathematical term relation, meaning a table. It does not mean “relationships between tables”, although keys provide those too.

Tables linked by keys

Here are two tables. Each employee row stores the dept_id of its department, which links the two:

-- Two related tables. The key in one table is stored in the other to link them.
CREATE TABLE department (
    dept_id   int PRIMARY KEY,
    name      text NOT NULL UNIQUE
);
CREATE TABLE employee (
    emp_id    int PRIMARY KEY,
    full_name text NOT NULL,
    dept_id   int NOT NULL REFERENCES department (dept_id),
    salary    numeric(10,2) CHECK (salary > 0)
);
INSERT INTO department VALUES (1, 'Data'), (2, 'Finance');
INSERT INTO employee VALUES (101, 'Nadia Khan', 1, 72000), (102, 'Leo Park', 1, 65000), (103, 'Ines Silva', 2, 58000);

-- Ask a question that needs both tables: the join follows the key.
SELECT d.name AS department, count(*) AS people, avg(e.salary)::numeric(10,0) AS avg_salary
FROM employee e
JOIN department d ON d.dept_id = e.dept_id
GROUP BY d.name
ORDER BY d.name;

 department | people | avg_salary
------------+--------+------------
 Data       |      2 |      68500
 Finance    |      1 |      58000

The department name is stored once, in department. If it changes, you update one row, and every employee query sees the new name. Storing each fact once is the idea behind normalisation.

Rules the database enforces

An RDBMS does not just store data; it refuses data that breaks the rules you declared:

• PRIMARY KEY: no two rows with the same id, and never empty.
• NOT NULL: the value must be present.
• UNIQUE: no duplicates, such as two departments called “Data”.
• CHECK: a condition must hold, such as a positive salary.
• REFERENCES (foreign key): the linked row must exist.

-- The database protects the relationship: an employee cannot join a department that does not exist.
INSERT INTO employee VALUES (104, 'Omar Diaz', 9, 61000);

ERROR:  insert or update on table "employee" violates foreign key constraint "employee_dept_id_fkey"
DETAIL:  Key (dept_id)=(9) is not present in table "department".

These rules are why relational databases are trusted for orders, payments and accounts: bad data is stopped at the door, whichever application writes it.

Transactions

A transaction groups changes so that they all happen or none do. If anything fails before COMMIT, the database rolls everything back:

-- A transaction: both changes happen, or neither does.
BEGIN;
UPDATE department SET name = 'Data Platform' WHERE dept_id = 1;
UPDATE employee SET salary = salary * 1.05 WHERE dept_id = 1;
COMMIT;
SELECT e.full_name, d.name, e.salary FROM employee e JOIN department d USING (dept_id) ORDER BY e.emp_id;

 full_name  |     name      |  salary
------------+---------------+----------
 Nadia Khan | Data Platform | 75600.00
 Leo Park   | Data Platform | 68250.00
 Ines Silva | Finance       | 58000.00

Relational databases guarantee ACID transactions: atomic, consistent, isolated and durable. The transactions lesson explains each property and what happens when many users write at once.

What an RDBMS does for you

• Stores data safely on disk, with a log so that committed changes survive a crash.
• Runs SQL: a query planner works out the fastest way to answer each query, using indexes where they help.
• Handles many users at once without them overwriting each other’s work.
• Controls access with users, roles and permissions.
• Backs up and replicates data to other machines.

Relational vs NoSQL databases

“NoSQL” covers databases that do not use the table model, usually to scale out across many machines or to store flexible data:

Type	Stores data as	Good for	Examples
Relational	Tables with a fixed schema, joined by keys	Business data with clear rules, transactions, reporting	PostgreSQL, MySQL, SQL Server, Oracle
Document	JSON-like documents	Data whose shape varies, content and catalogues	MongoDB, Couchbase
Key-value	A value looked up by a key	Caches, sessions, very fast simple lookups	Redis, Amazon DynamoDB
Wide-column	Rows with flexible columns, spread over many machines	Huge write volumes, time series	Apache Cassandra, HBase
Graph	Nodes and edges	Highly connected data: networks, recommendations	Neo4j

The line has blurred. PostgreSQL stores and queries JSON well, and several NoSQL databases now offer SQL-like queries and transactions. Many systems use both: a relational database for orders and payments, and Redis as a cache in front of it. Trade-offs for databases spread over many machines are covered in CAP, PACELC and BASE.

Where data engineers meet relational databases

• As sources. Most business applications store their data in an RDBMS. Pipelines read from them with queries or change data capture.
• As warehouses. Snowflake, BigQuery and Redshift use tables and SQL too, but are built for large scans rather than many small transactions. See OLTP vs OLAP.
• As metadata stores. Tools such as Airflow keep their own state in PostgreSQL or MySQL.

Common mistakes

1. Calling any database with tables “relational”. The defining features are keys, declared rules and SQL over related tables.
2. Leaving out foreign keys “for speed” in transactional systems. Orphan rows are much more expensive to clean up later.
3. Storing lists in one column, such as "Data, Finance". Use a separate table instead.
4. Choosing NoSQL by default for scale before checking whether one well-indexed relational database would do the job.

What interviewers look for

• A clear definition: tables, keys, SQL, enforced rules and transactions.
• Primary key vs foreign key, with an example.
• When you would choose a relational database and when a NoSQL type.
• Bonus: normalisation and ACID in one sentence each.

Key takeaway

A relational database stores data as tables linked by keys, enforces rules such as primary keys, foreign keys and checks, and runs SQL with ACID transactions. An RDBMS is the software that manages it. NoSQL databases trade some of those guarantees for flexible data or easier scaling, and many systems use both. Next: SQL fundamentals.