DBMS & NoSQL
ACID, normalization, indexing, isolation levels, and the SQL vs NoSQL decision — including the NoSQL data-modeling questions SDE 2 interviews love.
1. ACID vs BASE
- · Atomicity — transaction is all-or-nothing
- · Consistency — DB moves between valid states only
- · Isolation — concurrent transactions don't interfere
- · Durability — committed data survives crashes
- · Basically Available — system responds, always
- · Soft state — state may change without input (replication lag)
- · Eventually consistent — replicas converge over time, not instantly
2. Normalization
| Form | Rule | Removes |
|---|---|---|
| 1NF | Each column holds atomic (indivisible) values; no repeating groups. | Multi-valued columns, e.g. a 'phone_numbers' CSV string in one cell. |
| 2NF | 1NF + every non-key column depends on the whole primary key (not part of a composite key). | Partial dependency on a composite key. |
| 3NF | 2NF + no non-key column depends on another non-key column. | Transitive dependency, e.g. storing city AND zip_code -> city depends on zip, not the key. |
| BCNF | Every determinant (column that determines another) is a candidate key. | Edge cases 3NF misses with overlapping candidate keys. |
Trade-off: Normalization removes redundancy and update anomalies but requires JOINs to reassemble data (slower reads). Denormalization (common in NoSQL) duplicates data for fast reads at the cost of write complexity and potential inconsistency.
3. Indexes
Table rows are physically stored in index order. Only one per table (usually the primary key). Fast for range scans on that key.
Separate structure storing a pointer back to the actual row. Can have many per table. Extra lookup hop ("bookmark lookup") to fetch full row data.
Cost of indexes: Every index speeds up reads but slows down writes (INSERT/UPDATE/DELETE must update every index too) and takes extra storage. Don't over-index write-heavy tables.
4. Transaction Isolation Levels
| Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible |
| Read Committed | Prevented | Possible | Possible |
| Repeatable Read | Prevented | Prevented | Possible (MySQL InnoDB mostly prevents via gap locks) |
| Serializable | Prevented | Prevented | Prevented |
Dirty read: reading another transaction's uncommitted change. Non-repeatable read: re-reading the same row gives a different value because another transaction committed a change in between. Phantom read: re-running the same query returns a different set of rows because another transaction inserted/deleted matching rows.
5. SQL vs NoSQL
| Aspect | SQL (RDBMS) | NoSQL |
|---|---|---|
| Schema | Fixed schema, defined upfront | Flexible / schema-less |
| Scaling | Vertical (harder to shard, though modern SQL can scale-out) | Horizontal by design (sharding built in) |
| Consistency | Strong (ACID) | Usually eventual (BASE), tunable in some (e.g. Cassandra, DynamoDB) |
| Relationships | JOINs handle relational data naturally | Denormalized — relationships embedded or resolved in app code |
| Query flexibility | Rich ad-hoc queries (SQL) | Query patterns often need to be known upfront (data modeled around access patterns) |
| Best for | Financial systems, inventory, anything needing strong consistency + complex queries | High write throughput, huge scale, flexible/evolving schema, simple access patterns |
Simple key -> opaque value (blob).
→ Caching, session storage, feature flags — O(1) lookups by key.
Key -> JSON/BSON document with nested fields.
→ Catalogs, CMS, user profiles — flexible, evolving schemas per record.
Rows grouped by partition key; columns can vary per row.
→ High write throughput, time-series, huge scale (e.g. Twitter tweets, IoT data).
Nodes + edges with properties.
→ Social graphs, recommendation engines, fraud detection — relationship-heavy queries.
6. NoSQL Data Modeling: Design for Access Patterns
Rule of thumb: in NoSQL, duplicate data across tables/collections to match each read pattern with a single-partition lookup. The cost moves from "expensive JOIN at read time" to "extra writes to keep copies in sync" — worth it when reads vastly outnumber writes.
7. NoSQL & DBMS Interview Questions
A: MongoDB when the access patterns are still evolving and you need rich queries (secondary indexes, aggregation pipeline) on moderate write volume. Cassandra when you have very high, roughly linear-scaling write throughput (e.g. IoT/event ingestion, time-series) and your query patterns are fixed and known upfront (partition + clustering key design).
A: Writes go to an in-memory memtable + append-only commit log (sequential disk write, no random I/O), then get flushed to immutable SSTables on disk asynchronously. No read-before-write and no locking — this write path is why Cassandra scales writes near-linearly with more nodes.
A: MongoDB (single primary per replica set) leans CP — writes go through a primary, so it can be strongly consistent, but is unavailable for writes during primary election. Cassandra is tunably AP/CP via consistency levels (e.g. ONE, QUORUM, ALL) — you trade off latency for consistency per query. DynamoDB is AP by default (eventually consistent reads) but supports opt-in strongly consistent reads at higher latency/cost.
A: Either embed an array of course IDs in the student document (and vice versa) if the lists are small and bounded, or create a separate 'enrollment' collection with student_id + course_id if either side can grow unbounded (avoids the MongoDB 16MB document size limit and unbounded array growth issues).
A: When a single partition key receives disproportionate traffic (e.g. one viral restaurant_id, or a naive date-only partition key that funnels all of today's writes to one partition) it becomes a throughput/storage bottleneck on that one node. Fix: choose higher-cardinality partition keys or add a random/hashed suffix ('sharding key') to spread writes, then fan-out reads across the shards.
A: Data is deliberately partitioned/sharded across nodes for horizontal scale — a JOIN would require expensive cross-node data movement, defeating the scalability goal. Instead: denormalize (duplicate data to match access patterns), embed related data in the same document, or do the 'join' in application code with multiple targeted lookups (usually still cheaper than a distributed JOIN).
A: A dirty read (SQL isolation concept) is reading another transaction's uncommitted, possibly-to-be-rolled-back data within the same system. Eventual consistency (distributed systems concept) is reading committed but stale data from a replica that hasn't yet received a recent write — the data was valid and committed, just not yet propagated.