Database Management Systems and SQL
Two database topics come up in almost every fresher interview: normalisation, because it tests whether you can reason about data rather than memorise forms, and indexing, because it is where a correct query becomes a slow one. Both reward understanding the mechanism.
▶ Start with NormalisationLessons in order
What you will be able to do
- Normalisation exists to remove insert, update and delete anomalies — not for tidiness.
- A B-tree index turns a linear scan into a logarithmic lookup, at the cost of slower writes.
Quiz yourself on DBMS & SQL
Reading is not recall. Take a timed quiz on this topic solo, or share a room code and battle friends on it.
⚡ Start the DBMS & SQL quizFrequently asked questions
What is normalisation in DBMS?
Organising a database so each fact is stored once, by splitting tables to remove dependencies that cause insert, update and delete anomalies.
What is the difference between 2NF and 3NF?
2NF removes partial dependencies, where a non-key column depends on only part of a composite key. 3NF removes transitive dependencies, where a non-key column depends on another non-key column.
Is 3NF always the right target?
For transactional systems, usually yes. Read-heavy and analytical systems often denormalise deliberately to avoid expensive joins, accepting duplicated data in exchange for query speed.
How does a database index make queries faster?
It keeps a sorted B-tree of the indexed column with pointers to the rows, so the database can navigate to matching rows in logarithmic time instead of scanning every row.
Why is my query not using the index?
Most often the column is wrapped in a function, the LIKE pattern starts with a wildcard, the types on either side of the comparison do not match, the predicate skips the index's leftmost column, or the query would return so many rows that a scan is genuinely cheaper.
What is the leftmost prefix rule?
A composite index on (a, b, c) can serve predicates on a, on a and b, or on all three — but not on b or c alone, because the index is sorted by a first.