Data Modeling & Storage

A relational database stores data as tables of columns and rows with defined relationships. A primary key is the minimal unique attribute that identifies a row; a foreign key references another table's primary key, enforcing referential integrity.

ACID names four transaction guarantees: atomicity applies all statements or none, consistency moves the base between valid states, isolation stops concurrent transactions interfering, and durability keeps committed changes after a crash.

SQL databases use a fixed schema and a standard query language, giving strong consistency, integrity and rich joins. NoSQL uses flexible models for unstructured data and scales horizontally. Pick SQL for relations and transactions, NoSQL for scale.

An index is a database object that speeds up search by keeping an ordered structure over one or more columns, so the engine locates rows without scanning the whole table. It trades storage and slower writes for much faster reads.

A normal form is a property of a relation describing its redundancy and structure. Normalization removes duplication and the insert, update and delete anomalies it causes by splitting tables. Denormalization adds redundancy back for reads.

Constraints enforce integrity at schema level: PRIMARY KEY identifies a row, FOREIGN KEY holds referential integrity, UNIQUE bars duplicates, NOT NULL requires a value, CHECK tests a condition. Multi-column uniqueness needs a composite UNIQUE index.

Vertical scaling adds CPU and RAM to one server — simple but bounded. Replication copies data to replicas (synchronous is consistent but slower, asynchronous faster but lagging) and scales reads. Sharding splits rows by shard key, scaling writes.

Four NoSQL families: key-value gives fast lookups for caches and sessions; document stores JSON-like nested entities; column-family suits huge analytical writes; graph stores nodes and edges for connected data. Pick by the dominant access pattern.

A transaction is one or more SQL statements as a single logical unit — an atomic action either committed in full or rolled back. It ends with COMMIT or ROLLBACK. A single statement runs in an implicit transaction, so every operation is transactional.

An index slows INSERT/UPDATE/DELETE because it must be maintained, and costs storage. A unique index also forbids duplicate key values. An index on gender is nearly useless — two values means low selectivity; id is far more selective.

Partitioning splits one large logical table into smaller physical sections by a key such as a date range, while queries still see a single table. Only relevant partitions are scanned, and a whole partition can be dropped for maintenance.

Concurrency anomalies: lost update (transactions overwrite each other), dirty read (reading uncommitted data), non-repeatable read (different values on re-read) and phantom (more or fewer rows on re-run). Isolation via locking or MVCC prevents them.

Index a column driving frequent searches or joins on a large read-heavy table; confirm from query plans. Index foreign keys and JOIN/WHERE columns, prefer composite indexes, and limit indexes on write-heavy tables. The usual structure is a B-tree.

A conceptual model names business entities and their relationships, free of technology. A logical model adds attributes, keys and relationship types but stays DBMS-independent. A physical model maps it to a concrete DBMS — tables, types, indexes.