In system design and senior backend interviews, they check two things: can you turn a business problem into tables, and can you keep that database correct and fast as it grows. This set covers ER diagrams to tables, picking keys, planned denormalisation, partitioning, sharding, replication, isolation anomalies, MVCC and avoiding deadlocks.
How to practise: read each question and tap the option you think is correct. You will see right away whether you got it, along with a short explanation, and your score is tracked at the bottom of the screen. This set has 15 questions.
Topics covered in this set
- Mapping ER relationships to tables
- Candidate, alternate and surrogate keys
- Denormalised counters and partitioning
- Sharding and shard key choice
- Replication: synchronous vs asynchronous
- Isolation anomalies, MVCC, row locks and deadlocks
SQL MCQs with answers and explanations
Q1. How is a many-to-many relationship between students and courses implemented in a relational schema?
a) Merging both tables into one
b) A junction table such as enrollments(student_id, course_id) with a foreign key to each table and a composite primary key or unique constraint
c) A course_ids array column in students
d) A foreign key from students to courses only
Q2. In a one-to-many relationship between customers and orders, where does the foreign key go?
a) In orders, referencing customers
b) In a separate junction table always
c) No foreign key is needed
d) In customers, referencing orders
Q3. A users table has two candidate keys, id and email, and id is chosen as the primary key. What is email called?
a) An alternate key
b) A super key only
c) A foreign key
d) A composite key
Q4. What is a surrogate key?
a) A key that allows duplicates
b) A system-generated identifier with no business meaning, such as an identity column or UUID
c) A key made of business data such as a PAN or email
d) A key that references another table
Q5. You add a comment_count column to posts to avoid counting comments on every page view. What must you do to keep it correct?
a) Update it in the same transaction as each comment insert or delete (or with a trigger), and be ready to recompute it
b) Index it
c) Nothing; the database updates it automatically
d) Store it only in the browser
Q6. An events table is range-partitioned by month on created_at. What is the main benefit for WHERE created_at >= '2026-10-01'?
a) Every query reads all partitions in parallel
b) Partition pruning: the planner skips partitions that cannot contain matching rows, and old months can be dropped cheaply
c) It encrypts each month separately
d) Partitioning removes the need for indexes
Q7. What is the key difference between partitioning and sharding?
a) Partitioning only works on numbers
b) Partitioning splits a table within one database server; sharding spreads data across multiple independent servers
c) Sharding is done inside one table on one server
d) They are the same thing
Q8. Which shard key is the poorest choice for a write-heavy orders system using range-based sharding?
a) tenant_id for many similar-sized tenants
b) customer_id with many distinct values
c) The order creation timestamp
d) A hash of order_id
Q9. A user updates their profile on the primary, the next page reads from an asynchronous replica, and the old name is shown. What is happening?
a) Index corruption
b) A deadlock
c) Replication lag; the replica has not yet applied the change. Read-your-writes can be ensured by reading from the primary for that user for a short time
d) A phantom read on the primary
Q10. What is the main trade-off of synchronous replication compared with asynchronous replication?
a) Commits wait for a replica to confirm, which adds write latency, but committed data is not lost if the primary fails
b) It makes reads slower on the primary only
c) It loses data on failover
d) It cannot be used with PostgreSQL
Q11. A transaction runs SELECT COUNT(*) FROM orders WHERE status = 'new' twice and gets a different count because another transaction inserted and committed a new order in between. Which anomaly is this?
a) Dirty read
b) Non-repeatable read of a single row
c) Lost update
d) Phantom read
Q12. Two doctors are on call. Each transaction checks that at least two doctors are on call, then takes itself off call, and both commit. Which isolation level is guaranteed to prevent this write skew?
a) SERIALIZABLE
b) REPEATABLE READ in every database
c) READ COMMITTED
d) READ UNCOMMITTED
Q13. How does MVCC (multi-version concurrency control), as used in PostgreSQL, let readers and writers work together?
a) Every query runs one at a time
b) Readers lock the whole table
c) Data is copied to a replica for every read
d) Updates create new row versions, and each transaction reads from a snapshot, so readers do not block writers and writers do not block readers
Q14. What does SELECT ... FOR UPDATE do inside a transaction?
a) Creates a new row version immediately
b) Updates every selected row
c) Locks the selected rows until the transaction ends, so other transactions that try to update or lock them must wait
d) Makes the query read-only
Q15. Transaction 1 updates account 7 then account 3; transaction 2 updates account 3 then account 7, and they sometimes deadlock. What is the most reliable fix?
a) Increase the connection pool size
b) Remove the transaction
c) Use READ UNCOMMITTED
d) Always lock or update the accounts in the same order, for example ascending id
Quick revision notes
- Many-to-many needs a junction table. In one-to-many, the foreign key goes on the many side.
- An alternate key is a candidate key you didn't pick as primary. A surrogate key is a generated id with no business meaning.
- Partitioning splits a table inside one server (pruning, cheap drops). Sharding spreads data across servers. With range sharding, avoid keys that only go up, like timestamps.
- Async replicas lag, so a user may not see their own write. Sync replication is slower but does not lose committed data.
- A phantom is a new row matching your WHERE. SERIALIZABLE stops write skew. MVCC uses snapshots and VACUUM cleans up. Always lock rows in the same order.
Frequently asked questions
When should I shard a database?
Only when a bigger machine, indexes, caching, read replicas and partitioning are no longer enough. Sharding makes both operations and app code harder, and it is hard to undo.
Does MySQL InnoDB also use MVCC?
Yes. InnoDB keeps old row versions in undo logs to give each transaction a consistent snapshot, and purges them once no transaction needs them.
Should I use UUIDs or integers as primary keys?
Integers are small and fast. UUIDs can be created anywhere, which helps when you shard or merge data. Time-ordered UUIDs like UUIDv7 keep the index less fragmented.
Comments
Post a Comment