Loading...
Loading...
Loading...
Core CS and SQL interview guide
Keys, normalisation, transactions, ACID, indexing and concurrency control. Every answer is short enough to say aloud, and every question links to the lesson that teaches it.
60 questionsLast updated
Move X and the attributes it determines into their own table with X as the key, and keep X in the original table as a foreign key, such as department(dept_id, dept_name).
Lesson: Normalisation: 3NF and BCNFBoyce-Codd normal form requires that for every non-trivial FD X → A, X is a super key. In other words, every determinant must be a candidate key or contain one.
Follow-up: Is every BCNF table in 3NF? Yes. BCNF drops the "or A is prime" exception, so any table that passes it also passes 3NF.
Lesson: Normalisation: 3NF and BCNF3NF allows X → A when A is a prime attribute even if X is not a super key; BCNF does not allow that exception. So BCNF is stricter, and every BCNF table is in 3NF.
Follow-up: Which one do teams use in practice? Most designs reach 3NF and BCNF together. Teams stay at 3NF when BCNF would force them to give up an FD they want enforced inside one table.
Common mistake: Saying "BCNF is just a stricter 3NF" with no example. Mention the prime-attribute exception and the teaches(student, course, instructor) case.
Lesson: Normalisation: 3NF and BCNFOnly when the table has two or more candidate keys that are composite and overlap, and some FD goes from a non-super-key into a prime attribute. With a single candidate key, 3NF and BCNF coincide.
Lesson: Normalisation: 3NF and BCNFBecause a BCNF decomposition may lose a functional dependency, which then can be enforced only with a join or application code. 3NF guarantees a lossless, dependency-preserving decomposition, so it is a common practical target.
Follow-up: What is the cost of going to BCNF? Some BCNF splits lose a functional dependency, so a rule that was one UNIQUE or CHECK in one table now needs a join or a trigger.
Lesson: Normalisation: 3NF and BCNFExtra, incorrect rows produced when tables from a lossy decomposition are joined back, because the common columns do not identify rows uniquely.
Lesson: Normalisation: 3NF and BCNFA decomposition preserves dependencies if every original FD can be checked inside one of the new tables. Otherwise, enforcing that rule needs a join on every insert or update.
Follow-up: Why does it matter? If an FD spans two tables, enforcing it needs a join on every write. Preserving FDs keeps each rule checkable inside one table.
Lesson: Normalisation: 3NF and BCNFYes, by repeatedly splitting on a violating FD X → Y into (X ∪ Y) and (R − Y). It may not preserve all dependencies, though.
Lesson: Normalisation: 3NF and BCNFYes. The 3NF synthesis algorithm builds one table per FD in a minimal cover and adds a table with a candidate key if none contains one, which guarantees both properties.
Follow-up: Which algorithm produces that 3NF decomposition? The 3NF synthesis algorithm: take a minimal cover, make a table for each FD, add a table holding a candidate key if none is covered, and drop redundant tables.
Lesson: Normalisation: 3NF and BCNF1NF, 2NF, 3NF, BCNF, then 4NF and 5NF. Each higher form includes all the conditions of the ones before it.
Lesson: Normalisation: 3NF and BCNF3NF. The 3NF condition is satisfied whenever the right side of every FD is prime, though BCNF may still fail.
Lesson: Normalisation: 3NF and BCNFYes. Any non-trivial FD in R(A, B) is A → B or B → A, and in either case the left side determines both columns, so it is a super key.
Lesson: Normalisation: 3NF and BCNFFor read-heavy paths measured to be slow, reporting and analytics tables, dashboards and caches. Only after normalising, and with a plan to keep the copies consistent.
Lesson: Normalisation: 3NF and BCNFCopies can become inconsistent, writes become slower and more complex, and more storage is used. Every write path must update every copy, usually in the same transaction or by a trigger or job.
Follow-up: How do you keep the copies consistent? Update the copy in the same transaction as the source, or refresh it with a trigger or scheduled job, and test that the two always match.
Lesson: Normalisation: 3NF and BCNFNo. It improves write consistency and reduces storage, but reads may need many joins. Most production schemas are mostly normalised with a few deliberate denormalised parts.
Lesson: Normalisation: 3NF and BCNFemp_id → name, dept_id and dept_id → dept_name, dept_head is transitive. Split into employee(emp_id, name, dept_id) and department(dept_id, dept_name, dept_head); both are in BCNF.
Common mistake: Drawing new tables before writing the FDs. State emp_id → name, dept_id; dept_id → dept_name, dept_head first, then split.
Lesson: Normalisation: 3NF and BCNFEach non-key attribute must depend on the key (1NF gives it a key), on the whole key, not part of it (2NF), and on nothing but the key, not on another non-key attribute (3NF).
Lesson: Normalisation: 3NF and BCNFA sequence of database operations treated as one logical unit of work that either completes fully or has no effect, such as a fund transfer's debit and credit.
Lesson: ACID properties, transactions and concurrencyAtomicity (all or nothing), Consistency (valid state to valid state), Isolation (concurrent transactions behave as if run one at a time) and Durability (committed changes survive failures).
Follow-up: Which property is the hardest to provide while many users work at once? Isolation. Perfect isolation (serializable) limits concurrency, so databases offer weaker levels as a trade-off between correctness and speed.
Common mistake: Listing the four words with no example. Give a one-line meaning for each and tie them to one story, such as the bank transfer.
Lesson: ACID properties, transactions and concurrencyEvery transaction must keep the database's rules true: balances never negative, foreign keys valid, total money unchanged by a transfer. The DBMS enforces declared constraints, and the application must write correct logic.
Follow-up: Who is responsible for consistency, the database or the programmer? Both. The database enforces constraints, but the transaction code must also be logically correct, for example crediting the same amount that was debited.
Common mistake: Saying "consistency means all replicas agree". In ACID it means the database moves from one valid state to another and its rules hold.
Lesson: ACID properties, transactions and concurrencyIf two people book the last seat at the same time, isolation makes the result the same as if one booking ran fully before the other, so only one succeeds and neither sees the other's half-done work.
Lesson: ACID properties, transactions and concurrencyOnce the bank shows "transfer successful" after COMMIT, the transfer survives a power cut. The DBMS writes the change to a log on disk before confirming the commit.
Follow-up: How does a committed change survive a crash? It is written to the redo (write-ahead) log on disk before COMMIT returns. After a crash, recovery replays the log.
Lesson: ACID properties, transactions and concurrencyThe rule that log records describing a change are written to stable storage before the changed data pages are. After a crash the DBMS redoes committed work and undoes uncommitted work from the log.
Lesson: ACID properties, transactions and concurrencyActive, partially committed, committed, failed and aborted. A successful transaction goes active → partially committed → committed; a failing one goes to failed and then aborted after rollback.
Lesson: ACID properties, transactions and concurrencyA named marker inside a transaction. ROLLBACK TO SAVEPOINT name undoes only the work after that marker while keeping the transaction open.
A mode, on by default in MySQL, where every statement is its own transaction and commits immediately. Multi-statement work needs START TRANSACTION or autocommit turned off.
Follow-up: What goes wrong with autocommit on a transfer? Each UPDATE commits alone, so a crash between the debit and the credit leaves Asha debited and Bhavna unpaid. Wrap both in START TRANSACTION and COMMIT.
Common mistake: Saying "with autocommit nothing can go wrong". Every statement commits at once, so a multi-step change is no longer atomic.
Lesson: ACID properties, transactions and concurrencyReading the same row twice in one transaction and getting different values, because another transaction updated and committed it in between.
Lesson: ACID properties, transactions and concurrencyRunning the same query with a condition twice in one transaction and getting a different set of rows, because another transaction inserted or deleted matching rows and committed.
Lesson: ACID properties, transactions and concurrencyA non-repeatable read is an existing row whose value changes. A phantom is a row that appears or disappears from a result set.
Common mistake: Mixing them up. Non-repeatable: the same row changed. Phantom: the set of rows changed because a row was inserted or deleted.
Lesson: ACID properties, transactions and concurrencyREAD UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE, from weakest to strongest. Each prevents more anomalies at the cost of concurrency.
Follow-up: Which level would you choose by default? The database default, usually READ COMMITTED or REPEATABLE READ, and SERIALIZABLE only for the few operations that need it, because stronger levels cost concurrency.
Lesson: ACID properties, transactions and concurrencyREAD COMMITTED prevents dirty reads. REPEATABLE READ also prevents non-repeatable reads. SERIALIZABLE also prevents phantoms. READ UNCOMMITTED prevents none of them.
Lesson: ACID properties, transactions and concurrencyIt reduces concurrency: more locking, waiting, deadlocks or aborted retries, which hurts throughput. Most applications choose a weaker level and handle specific cases with explicit locks.
Follow-up: What do you give up with SERIALIZABLE? Concurrency: transactions wait for each other or abort and retry, so throughput falls.
Lesson: ACID properties, transactions and concurrencyAn ordering of the operations of several concurrent transactions, keeping each transaction's own order. A serial schedule runs one transaction completely before the next.
Lesson: ACID properties, transactions and concurrencyA schedule is serializable if its effect equals that of some serial schedule of the same transactions. It is the formal meaning of isolation being correct.
Lesson: ACID properties, transactions and concurrencyWhen they belong to different transactions, access the same data item and at least one of them is a write. Read-read pairs never conflict.
Lesson: ACID properties, transactions and concurrencyA weaker condition where the schedule reads the same values and produces the same final writes as some serial schedule. Every conflict-serializable schedule is view-serializable, but testing view serializability is much harder.
Lesson: ACID properties, transactions and concurrencyA protocol where each transaction acquires all its locks in a growing phase and releases them in a shrinking phase, never acquiring after releasing. It guarantees conflict serializability but can deadlock.
Follow-up: Does 2PL prevent deadlocks? No. It guarantees serializability, but two transactions can still wait on each other, so deadlocks must be detected or avoided separately.
Lesson: ACID properties, transactions and concurrency2PL where exclusive locks are held until the transaction commits or aborts. It prevents other transactions from reading uncommitted data, avoiding dirty reads and cascading rollbacks.
Lesson: ACID properties, transactions and concurrencyMulti-version concurrency control keeps older versions of rows so readers see a consistent snapshot without blocking writers. InnoDB and PostgreSQL use it.
Follow-up: What is the benefit of MVCC? Readers do not block writers and writers do not block readers, because each reader sees a consistent snapshot.
Lesson: ACID properties, transactions and concurrencyMost detect them by finding cycles in a wait-for graph and roll back one transaction as the victim, returning an error such as MySQL's 1213. Others use timeouts or prevention schemes such as wait-die and wound-wait.
Follow-up: How should the application handle MySQL error 1213? Catch it, roll back and retry the transaction a few times. Locking rows in a fixed order reduces how often it happens.
Lesson: ACID properties, transactions and concurrencyTimestamp-based deadlock prevention. In wait-die, an older transaction may wait for a younger one, but a younger one requesting an older one's lock dies. In wound-wait, an older one wounds (aborts) the younger holder, and a younger one waits.
Lesson: ACID properties, transactions and concurrencyIt starts a database transaction before the method and commits after it, or rolls back on a runtime exception, so all repository calls inside share one transaction. Its isolation and propagation settings map to the database's transaction features.
Follow-up: Does a checked exception roll back a @Transactional method? Not by default. Spring rolls back on unchecked exceptions; use rollbackFor to roll back on a checked exception too.
Common mistake: Saying "@Transactional makes the database ACID". It only wraps the method in a database transaction; by default only unchecked exceptions roll back.
Lesson: ACID properties, transactions and concurrencyA separate data structure, usually a B+ tree, that keeps a column's values in sorted order with pointers to the rows. It lets the database find matching rows in about log n steps instead of scanning the whole table.
Follow-up: Does an index help SELECT * FROM student with no WHERE? No. Every row is needed, so the database reads the whole table anyway. Indexes help when only a small part of the rows is wanted.
Lesson: Indexing basicsLookups in WHERE, JOIN conditions, ORDER BY and GROUP BY on the indexed columns, MIN and MAX, and uniqueness checks.
Lesson: Indexing basicsReading every row of the table to answer a query. EXPLAIN shows it as type ALL. It is fine for small tables or queries that return most rows, but slow for selective lookups on large tables.
Lesson: Indexing basicsA separate structure with the indexed values and pointers to the rows; the table's physical order is unaffected. A table can have many. In InnoDB the pointer is the primary key value.
Lesson: Indexing basicsClustered decides row order, there is one per table, and its leaves contain the data. Non-clustered is separate, there can be many, and a lookup usually needs an extra step to fetch the row.
Lesson: Indexing basicsInnoDB uses the first UNIQUE NOT NULL index as the clustered index, and if there is none, it creates a hidden row id to cluster on. Defining a primary key explicitly is better.
Lesson: Indexing basicsAn index that contains every column a query needs, so the database answers from the index without reading the table rows. EXPLAIN shows "Using index".
Follow-up: How do you see a covering index in EXPLAIN? The Extra column says "Using index": MySQL answered from the index alone without reading the table rows.
Lesson: Indexing basicsAn index on several columns in a fixed order, such as (branch, cgpa). It sorts by the first column, then by the second within each first value.
Follow-up: When is a composite index better than two single-column indexes? When queries filter on both columns together. One composite index narrows the rows in one search, while MySQL usually uses only one single-column index per table.
Lesson: Indexing basicsPut columns used with equality first, then the column used for a range or sort. Among equality columns, put the most commonly filtered or most selective first.
Lesson: Indexing basicsA balanced search tree where each node holds many sorted keys and has many children, designed so each node fits in a disk page. All leaves are at the same depth, so search, insert and delete are O(log n).
Lesson: Indexing basicsA binary tree node has only two children, so the tree is deep and each level is a disk read. A B+ tree node holds hundreds of keys, so even a hundred million rows need only about four levels.
Lesson: Indexing basicsWith a fanout of about 100, three levels reach 100³ = 1,000,000 rows. Real fanouts are often larger, and the top levels are usually cached in memory.
Follow-up: How did you get that number? With about 100 keys per node, one level reaches 100 rows, two reach 10,000 and three reach a million, so about three or four page reads.
Lesson: Indexing basicsAn index that hashes the key to a bucket, giving O(1) average equality lookups. It cannot support range queries, ORDER BY or prefix searches. In MySQL, the MEMORY engine supports hash indexes; InnoDB uses B+ trees.
Lesson: Indexing basicsBecause a full scan is estimated to be cheaper, for example when the query matches a large share of rows, the table is small, or statistics are outdated. Functions on the column or type mismatches can also make the index unusable.
Follow-up: How do you find out why? Run EXPLAIN. Look at type (ALL means a full scan) and key (NULL means no index), then check for a function on the column, a leading wildcard or a missing leftmost column.
Lesson: Indexing basicsLIKE '%abc' not use an index while LIKE 'abc%' can?The index is sorted from the first character. A known prefix narrows the range to search, but a leading wildcard can match anywhere, so every entry must be checked.
Common mistake: Saying "LIKE never uses an index". A prefix pattern like 's42%' can; only a leading % forces a scan, because the sorted order no longer helps.
Lesson: Indexing basicsRun EXPLAIN before the query. Check type (ALL is a full scan; ref, range and const use an index), key (the index chosen) and rows (the estimate examined).
Yes. MySQL creates an index for every PRIMARY KEY and UNIQUE constraint, because it needs one to check uniqueness quickly. InnoDB also requires an index on foreign key columns and creates one if missing.
Lesson: Indexing basicsYes. Indexing the join column of the inner table lets each lookup be an index search instead of a scan, which turns a nested loop from O(n × m) into roughly O(n log m).
Lesson: Indexing basicsEach lesson has quizzes, practice and more interview questions on the same topic.
Open the first lesson