Data Modeling & Storage
Data is the longest-lived part of a system: the code will be rewritten, but the tables and relations remain. So an analyst must think about storage deliberately, not "however it turns out": which entities, which keys, which model — relational or NoSQL — and what guarantees the DBMS gives under load.
This block gathers everything asked about storage: from the relational model and design levels to indexes, normalization, transactions, ACID, and concurrency anomalies. Common traps are confusing an index with a primary key, indexing a low-selectivity column, treating ACID as a set of "performance features," and swapping the SQL and NoSQL schemas. The full map lives in the layers below.
Topic map
- Relational model and keys — tables, rows, primary and foreign key, referential integrity.
- Data model levels — conceptual, logical, and physical levels and what each adds.
- Normalization — normal forms, redundancy anomalies, and deliberate denormalization.
- Constraints — PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and composite uniqueness.
- Indexes — what an index is, why it is needed, and how a B-tree works.
- Index tradeoffs and selectivity — write cost, the unique index, selectivity, and design guidelines.
- Transactions — a single logical unit of work, COMMIT and ROLLBACK.
- ACID guarantees — atomicity, consistency, isolation, durability.
- Concurrency anomalies — dirty read, phantoms, and defense through isolation levels.
- SQL vs NoSQL — fixed vs flexible schema, the NoSQL families, and when to choose which.
- Partitioning — splitting a large table into sections and how it differs from sharding.
Common traps
| Mistake | Consequence |
|---|---|
| Confusing an index with a primary key | Wrong storage model; extra or missing indexes |
| Indexing a low-selectivity column (gender) | The index does not narrow the search, only slows writes |
| Treating ACID as "performance features" | You explain the transaction guarantees wrong — a gross failure |
| Swapping the SQL and NoSQL schemas | You pick the model for the task incorrectly |
| Building the physical model before the conceptual one | DBMS details leak into the business level; the model is unreadable |
| Thinking normalization merges tables | The opposite — it splits them, removing anomalies |
Interview relevance
Data storage is the heaviest technical section of an analyst interview. It checks whether you understand the mechanics, not just the terms: how a foreign key differs from a primary key, why an index on gender is useless, what exactly each letter of ACID guarantees.
Typical checks:
- The relational model, primary and foreign key, referential integrity.
- What an index is, its trade-off, and why selectivity is decisive.
- ACID and concurrency anomalies; how isolation levels defend against them.
- The difference between SQL and NoSQL, the NoSQL families, and the data-model levels/normalization.
Common wrong answer: "an index is so data is not lost" or "ACID is about speed." In fact an index trades storage and write speed for read speed, and ACID is four reliability guarantees of a transaction, not performance features.