Interview Q&A / SQL & DBMS

1. What is the difference between a clustered and a non-clustered index?

   A clustered index decides the physical order in which table rows are stored, so the table itself is the index and there can be only one. A non-clustered index is a separate structure holding the indexed values plus a pointer back to the row, and a table can have many.

   AdvancedIn-depth4 min read
2. What is BCNF and how is it stricter than 3NF?

   A table is in BCNF if, for every non-trivial dependency X -> Y, X is a superkey. 3NF also lets Y be a prime attribute, which BCNF does not allow, so BCNF removes a few anomalies that 3NF misses.

   AdvancedQuick answer1 min read
3. What is MVCC in databases?

   MVCC keeps multiple versions of a row so readers see a consistent snapshot while writers create new versions. This lets reads and writes proceed without blocking each other. PostgreSQL, MySQL InnoDB and Oracle use it.

   AdvancedQuick answer1 min read