Database Concepts

Organising tables so each fact is stored once, to stop update anomalies.

If a customer address is copied into every order, changing it means finding every row - and missing one leaves the database contradicting itself. Normalisation moves the address into a customers table referenced by id, so there is one place to change. The goal is consistency, not tidiness.

What it is really testing. Whether you name the anomaly. "To remove redundancy" is the recited answer; "so one update cannot leave two different addresses" is the understood one.

First: no repeating groups, one value per cell. Second: no non-key column depending on part of a composite key. Third: no non-key column depending on another non-key column.

The informal version is worth having too: every non-key column depends on the key, the whole key, and nothing but the key. 2NF only ever bites when the primary key is composite - with a single-column key you get it for free, which is worth saying because it shows you know why the rule exists.

What it is really testing. Whether you can apply them to a table rather than recite them. Expect a messy example table and "which form is this in?"

When a read that matters is too slow, and you can accept maintaining the duplicate.

Reporting and analytics are the usual cases: a dashboard joining eight tables on every page load may be better served by one wide table rebuilt nightly. The cost is real - you now have two places a fact can live, and they can drift. Denormalising before measuring is the mistake.

What it is really testing. Whether you treat normalisation as a default rather than a law. A candidate who says it should never be broken has not worked on a slow report.

A table has one primary key and it cannot be null; it can have many unique keys and they usually can be null.

Both enforce uniqueness. The primary key is the row's identity, which is what other tables point at with foreign keys. A unique constraint on email says "no two accounts share an address" without making the email the identity - which matters, because people change their email.

What it is really testing. Whether you mention nullability and identity. "Both are unique" is where most answers stop, and it is the least useful half.

That the value exists in the referenced table - and it controls what happens when the referenced row is deleted.

It stops orphans: no order pointing at a customer who was never created. The second half is the part people forget - ON DELETE CASCADE removes the children, RESTRICT blocks the delete, SET NULL detaches them. Choosing wrongly is how a routine cleanup deletes half a database.

What it is really testing. Whether you know about cascade behaviour. The enforcement half is easy; the delete half is where real damage happens.

Disk space, and time on every insert, update and delete, because the index has to be maintained too.

An index is a sorted structure kept alongside the table, so reads can find rows without scanning. Every write now updates both. On a write-heavy table that is a real tax, and an index nobody queries is pure cost - which is why "add an index" is not automatically the right answer.

What it is really testing. Whether you volunteer the cost. The question is phrased to invite "it makes queries faster", and stopping there is the trap.

Atomicity, Consistency, Isolation, Durability.

Atomicity: all of a transaction happens or none of it does. Consistency: it leaves the database obeying its own constraints. Isolation: concurrent transactions do not see each other half-done. Durability: once committed it survives a crash. Have the bank-transfer example ready - debit and credit must be atomic.

What it is really testing. Whether you can give an example per letter. The acronym alone is the answer everyone has; isolation in particular is the one people cannot illustrate.

A group of statements that succeed or fail as one unit.

Moving money is the standard example: the debit and the credit must both happen or neither. You open with BEGIN, and end with COMMIT to make it permanent or ROLLBACK to undo it. Without one, a crash between the two statements leaves money that has left one account and arrived nowhere.

What it is really testing. Whether you can name the failure it prevents. The definition is easy; the crash-in-the-middle scenario is the answer.

Two transactions each holding a lock the other needs - avoided by always taking locks in the same order.

Transaction A locks row 1 then wants row 2; B locks row 2 then wants row 1. Neither can proceed. Databases detect this and kill one, so your application must be able to retry. The practical fix is a consistent ordering - every code path touching both tables touches them in the same sequence.

What it is really testing. Whether you know the application has to retry. Many candidates describe the deadlock correctly and assume the database simply resolves it for them.

A clustered index is the physical order of the table itself, so there can only be one; non-clustered indexes are separate structures pointing back at rows.

Because the clustered index is the table, range queries along it are very cheap - the rows are already next to each other. A non-clustered index finds the row and then has to go and fetch it, unless every column needed is already in the index, which is what a covering index is.

What it is really testing. Whether you know why there is only one. "The table can only be sorted one way" is the sentence being looked for.

A stored query that behaves like a table - useful for hiding complexity and restricting access.

It holds no data of its own; it runs underneath every time. That makes it good for giving a team a simplified, permission-limited window onto a messy schema, and bad as a performance fix, since the work still happens. A materialised view does store results, and then you own the refresh.

What it is really testing. Whether you know a plain view is not a cache. Expecting a view to be faster is a common and costly misunderstanding.

When the shape of the data genuinely does not fit tables, or you need horizontal scale more than you need joins and transactions.

Document stores suit varied, nested records; key-value stores suit caches and sessions; graph databases suit relationship-heavy queries. The honest answer includes the cost: you usually give up joins and strong transactional guarantees, and you move that work into your application.

What it is really testing. Whether you can say no. The expected weak answer is "NoSQL scales better"; the strong one names what you lose.