General concepts

Junior PRO

Essence

A relational database stores data in tables (in theory, "relations"): rows are records, columns are typed attributes. Links between tables are expressed through keys, not through nesting as in JSON.

The basic vocabulary an interviewer expects:

• Primary key — a row's unique identifier. Foreign key — a reference to another table's primary key; that's how 1:N and, via a junction table, N:M relationships are modelled.
• Index — a separate structure (usually a B-tree) that speeds up lookups on a column at the cost of space and slower writes. Without an index, WHERE email = ? is a full table scan.
• JOIN — combining tables by keys in a single query.
• Transaction — an "all or nothing" group of operations. The ACID properties: Atomicity, Consistency, Isolation, Durability.
• Normalisation — removing data duplication (an author's name lives once in the author table, not in every book).

SQL (Structured Query Language) is a declarative language for describing a schema (DDL: CREATE TABLE) and for changing and querying data (DML: INSERT, UPDATE, DELETE, SELECT).

ORM (Object-Relational Mapping) is a layer that maps table rows to language objects and generates SQL for you. The upside is less hand-written code; the downside is hidden expensive queries (the classic N+1 problem).