Design & normalization
What is the ER model?
A design blueprint that represents data as entities (things), their attributes (properties), and the relationships between them - drawn before you translate it into actual tables.
Explain normal forms 1NF through BCNF.
Each form removes a kind of redundancy: 1NF requires atomic values, 2NF removes partial dependencies on part of a composite key, 3NF removes transitive dependencies, and BCNF requires every determinant to be a candidate key. Given a relation, be ready to say which form it violates and why.
What is a functional dependency and a candidate key?
A functional dependency A → B means A's value determines B's value. A candidate key is a minimal set of attributes that uniquely identifies each row - remove any attribute and it stops being unique.
When would you denormalise?
Deliberately, for read performance - adding controlled redundancy (e.g. in reporting or analytics tables) to avoid expensive joins, accepting more complex writes as the trade-off.
Transactions & concurrency
What are the ACID properties?
Atomicity (all operations succeed or none do), Consistency (a transaction moves the database from one valid state to another), Isolation (concurrent transactions don't interfere), and Durability (committed changes survive crashes).
What does serializability guarantee?
That a concurrent schedule of transactions produces the same result as some serial (one-after-another) execution of them - the correctness standard for concurrency control.
What is a deadlock and how is it handled?
Two transactions each waiting for a lock the other holds. Databases handle it by prevention (lock ordering), avoidance (Banker's algorithm), or detection - spotting the wait-for cycle and rolling one transaction back.
What do isolation levels control?
The trade-off between performance and anomalies. From Read Uncommitted up to Serializable, higher levels prevent more anomalies - dirty reads, non-repeatable reads and phantom reads - at the cost of concurrency.
Storage & indexing
How do indexes speed up queries, and why B+ trees?
An index lets the engine locate rows without scanning the whole table. B+ trees keep keys sorted with all data in the leaves, giving efficient equality and range lookups - which is why most database indexes use them.
Clustered vs non-clustered index?
A clustered index defines the physical order of rows in the table, so there's only one. A non-clustered index is a separate structure that stores keys with pointers to the rows.
SQL vs NoSQL - when do you choose each?
Relational (SQL) suits structured, related data needing strong consistency and complex queries. NoSQL/document stores suit flexible schemas, high write volume and horizontal scaling.
Practice this now