DBMS Interview Questions and Answers

DBMS interviews test foundational database theory that every application engineer should know—even if daily work hides it behind an ORM.

Campus and experienced loops both expect you to explain normalization, keys, and ACID with examples—not only memorize definitions.

A strong answer is:

"DBMS interviews test whether I understand how data is modeled, constrained, queried, and kept correct under concurrent access. I should be able to explain keys and normalization, reason about transactions and isolation, and justify an index or database design with examples."

What interviewers are testing: Whether you know RDBMS is a relational DBMS with tables and SQL—not a separate unrelated category.

A DBMS (Database Management System) is software that lets users define, store, retrieve, and manage data with controlled access. The term is broad—it can include hierarchical, network, object-oriented, or document systems.

An RDBMS (Relational DBMS) uses the relational model and provides mechanisms such as keys, constraints, joins, transactions, and SQL to maintain relational integrity—not every deployment declares every constraint, but the engine supports them.

Every RDBMS is a DBMS; not every DBMS is relational. In interviews, say RDBMS when discussing joins, normalization, and ACID on tabular schemas; say DBMS when comparing storage paradigms broadly.

A strong answer is:

A DBMS is any database management software; an RDBMS uses the relational model with tables, keys, joins, and SQL—I say RDBMS when discussing normalization and ACID on tabular schemas.

What interviewers are testing: Whether you know which theory depth developers need vs what DBAs own in production.

Basic dbms interview questions skew toward theory (normal forms, keys). Senior backend loops add isolation anomalies and index trade-offs. DBA tracks go deeper on recovery and replication—see PostgreSQL interview questions for engine-specific depth.

A strong answer is:

Developers need ER, normalization, transactions, and index basics in code; DBAs own backup drills, replication lag, capacity, and recovery—I know which side of the table my answer targets.

The exact interview structure varies by company and role.

Campus rounds often ask draw ER diagram for university registration; product companies add transaction isolation and index choice for backend roles.

A strong answer is:

I expect fundamentals, live coding or scenarios, and depth follow-ups—so I prepare stories and small examples for each major topic.

Practice 10 SQL problems weekly from SQL technical interview questions. Run transactions locally on PostgreSQL to see COMMIT / ROLLBACK behavior—the SQL transactions guide walks through BEGIN, COMMIT, and rollback with practical examples.

A strong answer is:

I'd build hands-on drills each week, explain answers aloud, and close every technical card with one sentence I could say in the room.

What interviewers are testing: Whether you distinguish raw facts from processed meaning with an example—not interchangeable buzzwords.

Data are raw facts—numbers, strings, timestamps—without context. Information is data processed into a meaningful form for decisions.

A DBMS stores data; applications and reports turn it into information via constraints, relationships, and queries. Metadata (schema, statistics) describes the data itself.

A strong answer is:

Data are raw values; information is data with context and meaning—a DBMS stores data; queries and business rules produce information for decisions.

What interviewers are testing: Whether you name concurrency, integrity, and query wins over flat files—not just 'databases are faster'.

Flat files work for simple scripts; production systems need structured concurrent access.

Files remain fine for logs, CSV exports, and static assets; OLTP and shared mutable state belong in a DBMS.

A strong answer is:

Files lack concurrent safe updates, declarative integrity, and efficient indexed queries—a DBMS gives transactions, constraints, and recovery that files force every app to reinvent.

What interviewers are testing: Whether you separate schema (design), instance (snapshot), and database (container) without conflating structure with data.

Analogy: schema is the blueprint of a building; instance is the furniture and occupants inside.

One DBMS server can host multiple databases (e.g. postgres, myapp_prod). The terms database, catalog, and schema are product-specific—for example, PostgreSQL databases can contain multiple schemas, while in MySQL SCHEMA is effectively synonymous with DATABASE.

A strong answer is:

Schema is the logical design—tables and constraints; instance is the live data at a point in time; I know database/catalog/schema naming differs by engine.

What interviewers are testing: Whether you can walk external → conceptual → internal views and say what each layer hides.

ANSI/SPARC three-level architecture separates concerns so storage changes do not break applications.

Mapping layers translate between levels. Logical data independence: change conceptual schema with minimal external view changes. Physical data independence: move indexes or storage without rewriting apps.

A strong answer is:

"Three-schema architecture separates user views, the logical database design, and physical storage. For example, the DBMS can change indexes or storage layout without requiring application queries to change, while views can help shield applications from some logical-schema changes."

What interviewers are testing: Whether you name query processor, storage manager, transaction manager roles—not a vague 'DBMS stores data'.

Understanding the pipeline helps in interviews: "Why is this query slow?" → parse OK, bad plan or missing index or lock wait.

A strong answer is:

I name the query processor, storage manager, transaction manager, and recovery manager—then tie slow queries to optimizer plans and lock waits, not mysticism.

What interviewers are testing: Whether you match relational, document, graph, and key-value models to real workload shapes.

Relational dominates OLTP with strong consistency needs. Document fits flexible schemas; graph fits relationship-heavy traversals. Choice depends on access patterns, not hype.

A strong answer is:

Relational for tabular integrity and joins; document for flexible nested records; graph for deep relationship traversals—I pick by query pattern, not buzzwords.

What interviewers are testing: Whether you classify DDL, DML, DCL, and TCL statements and give one example of each.

SQL command categories:

Some interview material classifies SELECT separately as DQL (Data Query Language), while broader SQL classifications include it under DML; mention the convention your interviewer is using rather than arguing terminology.

TRUNCATE usually removes all rows using bulk/storage-level mechanisms rather than ordinary row-by-row DELETE semantics. Transaction, logging, trigger, foreign-key, and identity/sequence behavior is DBMS-specific—for example PostgreSQL TRUNCATE can roll back inside a transaction, while other engines may treat it differently.

BEGIN;
INSERT INTO accounts (id, balance) VALUES (1, 100);
ROLLBACK; -- inserted row is rolled back

A strong answer is:

DDL defines schema; DML changes rows; DCL grants access; TCL wraps units of work—I use COMMIT/ROLLBACK so partial updates never leave inconsistent balances.

What interviewers are testing: Whether you draw entities, attributes, relationships and explain why ER precedes relational tables.

The Entity-Relationship (ER) model is a conceptual diagram notation for database design before tables exist.

Weak entity depends on another entity for identity (OrderLine depends on Order). Derived attribute computed from others (age from birthdate)—often not stored.

ER diagrams communicate with stakeholders before migration scripts. Mapping rules convert ER → relational tables.

A strong answer is:

ER model is conceptual—entities, attributes, relationships, cardinality—I draw it first with stakeholders, then map to tables with keys before writing DDL.

What interviewers are testing: Whether you place foreign keys correctly for 1:1, 1:N, and M:N—not just label the diagram.

Example M:N: Students enroll in Courses → enrollments(student_id, course_id, grade).

Participation: total (every entity must participate) vs partial. Optional relationship → nullable FK.

A strong answer is:

1:N puts the foreign key on the many side; M:N needs a junction table with both keys—I state cardinality before writing CREATE TABLE.

What interviewers are testing: Whether you decompose M:N into junction tables and preserve keys through the mapping rules.

Example — university:

• students(id, name)
• courses(id, title)
• enrollments(student_id, course_id, semester) PK (student_id, course_id)

Avoid NULL-heavy designs when relationship attributes exist—junction table carries enrollment date, grade, etc.

A strong answer is:

Each entity becomes a table; 1:N is a foreign key on the many side; M:N is a junction table—I move relationship attributes onto the table that represents the association.

What interviewers are testing: Whether you distinguish entity sets from relationship sets and their keys in the relational mapping.

An entity set is a collection of entities of the same type—all Employee rows share attributes like emp_id, name.

A relationship set is a collection of relationship instances linking entities—Works_In(emp, dept) pairs employees with departments.

In relational terms:

• Entity set → table
• Relationship set → FK columns or junction table

Relationship attributes (e.g. since_date on Works_In) belong on the relationship representation—not duplicated on both entity tables.

A strong answer is:

Entity set is a type of object; relationship set is associations between them—in SQL, relationships become foreign keys or a bridge table with their own attributes.

What interviewers are testing: Whether you explain identifying relationships and partial keys for weak entities—not optional attributes.

A weak entity cannot be uniquely identified by its own attributes alone—it depends on a owner entity.

Example: InvoiceLine identified by (invoice_id, line_no)—line_no is unique only per invoice.

DDL: composite primary key (invoice_id, line_no) with invoice_id FK → invoices(id) ON DELETE CASCADE often appropriate.

A strong answer is:

Weak entities need the owner's key in their primary key—InvoiceLine uses (invoice_id, line_no) because line numbers repeat across invoices.

What interviewers are testing: Whether you rank super, candidate, primary, and alternate keys without calling every column a primary key.

Example employees:

• Super keys: {emp_id}, {emp_id, name}, {email} (if unique)
• Candidate keys: {emp_id}, {email}
• Primary key: emp_id
• Alternate key: email UNIQUE

A strong answer is:

Candidate keys are minimal unique identifiers; primary key is the main one; alternate keys stay unique—foreign keys reference parent primary or unique keys.

What interviewers are testing: Whether you choose natural composite keys vs surrogate integers with trade-offs for joins and migrations.

Composite key: primary key spans multiple columns—natural for junction tables (order_id, product_id).

Surrogate key: artificial identifier (SERIAL, UUID) with no business meaning.

Use surrogate when natural keys are long, composite, or unstable (username changes). Keep natural unique constraints where business requires (email UNIQUE).

A strong answer is:

Composite keys fit junction tables; surrogate keys simplify joins when natural keys are wide or unstable—I still enforce business uniqueness with alternate constraints.

What interviewers are testing: Whether you explain referential actions (CASCADE, RESTRICT, SET NULL) and when each is dangerous.

A foreign key enforces referential integrity: child values must exist in parent (or be NULL if allowed).

CREATE TABLE orders (
  id         int PRIMARY KEY,
  customer_id int NOT NULL REFERENCES customers(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

RESTRICT and NO ACTION often behave similarly in simple cases, but their check timing can differ by engine.

A strong answer is:

Foreign keys keep child rows honest—I default to ON DELETE RESTRICT for safety and CASCADE only when child rows have no meaning without the parent.

What interviewers are testing: Whether you cover entity, referential, domain, and user-defined integrity—not only PRIMARY KEY.

CREATE TABLE products (
  id    int PRIMARY KEY,
  price numeric CHECK (price >= 0),
  sku   text UNIQUE NOT NULL
);

ORMs map these to validators—but database constraints are the last line of defense against buggy deploys.

A strong answer is:

Entity, referential, and domain integrity—PKs, foreign keys, and CHECK constraints—I put non-negotiable rules in the database, not only in application code.

What interviewers are testing: Whether you know three-valued logic and why NULL is not equal to NULL or zero.

NULL means unknown or not applicable—not zero, not empty string.

SQL represents missing/unknown values with NULL, while three-valued logic itself uses TRUE, FALSE, and UNKNOWN. Use IS NULL / IS NOT NULL. Rows that evaluate to UNKNOWN in a WHERE filter do not match.

Design tip: use NULL for optional fields; avoid NULL in PKs; consider NOT NULL DEFAULT when a sentinel is clearer.

A strong answer is:

NULL is unknown, not zero—comparisons use IS NULL; I avoid NULL primary keys and explain three-valued logic when debugging surprising WHERE results.

What interviewers are testing: Whether you cite update anomalies and redundancy as the driver—not vague 'cleaner tables'.

Normalization decomposes tables to reduce redundancy and update anomalies.

Example denormalized order_lines(customer_name, customer_city, product, qty):

• Update customer city in every row they ordered
• Delete last order line loses customer address

Normalization splits into customers, products, orders, order_lines.

Trade-off: more joins at read time—sometimes denormalize deliberately for reporting (see Q28).

A strong answer is:

Normalization removes redundancy so updates happen in one place—I explain insert/update/delete anomalies on a messy table, then decompose it.

What interviewers are testing: Whether you spot repeating groups and atomic columns that violate first normal form.

In interview terms, 1NF means each row represents one record, repeating groups are removed, and each attribute contains a single value from its defined domain rather than multiple independent values packed into one field.

Violations:

• phone_numbers: "555-1, 555-2" → split to customer_phones table
• Multiple course1, course2 columns → row per enrollment

A strong answer is:

"1NF means one value per attribute and no repeating groups. I move things like comma-separated phone numbers or course1, course2 columns into separate rows or related tables."

What interviewers are testing: Whether you find partial dependencies on a composite key—the classic 2NF violation pattern.

A relation is in 2NF when it is in 1NF and no non-prime attribute is partially dependent on a candidate key. The issue matters when a candidate key is composite: an attribute must not depend on only part of that key.

Example violation:

enrollments(student_id, course_id, student_name, course_title, grade)

• student_name depends only on student_id (partial)
• course_title depends only on course_id (partial)

Fix: move student_name → students; course_title → courses; keep grade on enrollments.

A strong answer is:

"2NF removes partial dependencies on composite candidate keys. If an attribute depends on only part of a key, I move that fact to the relation where it belongs."

What interviewers are testing: Whether you eliminate transitive dependencies where non-keys depend on other non-keys.

A table is in 3NF when:

1. It is in 2NF
2. No non-key attribute depends on another non-key attribute (transitive dependency)

Example violation:

employees(emp_id, dept_id, dept_name, dept_location)

• dept_name and dept_location depend on dept_id, not directly on emp_id

Fix: departments(dept_id, dept_name, dept_location) + employees(emp_id, dept_id, ...).

Most OLTP schemas target 3NF unless reporting needs dictate otherwise.

A strong answer is:

3NF eliminates transitive dependencies—I pull attributes that depend on non-key columns into separate tables like departments separate from employees.

What interviewers are testing: Whether you understand why BCNF is stricter than 3NF—specifically that every determinant in a non-trivial functional dependency must be a superkey.

BCNF (Boyce-Codd Normal Form) strengthens 3NF:

For every non-trivial functional dependency X → Y, X must be a superkey.

3NF but not BCNF example (classic):

student_advisor(student, subject, advisor) where each student has one advisor per subject, and each advisor teaches one subject → decompose further.

In practice 3NF ≈ BCNF for most business schemas. BCNF matters in academic questions and odd dependency patterns.

A strong answer is:

"BCNF is stricter than 3NF: for every non-trivial functional dependency, the determinant must be a superkey. It matters when overlapping candidate keys create dependencies that 3NF still permits."

What interviewers are testing: Whether you justify read-performance trade-offs with measured need—not blanket denormalization.

Denormalization intentionally adds redundancy for read performance or simpler queries.

Costs: update anomalies return—you must sync denormalized copies (triggers, batch jobs, CDC).

Rule: normalize first, measure slow queries, denormalize with a documented consistency strategy.

A strong answer is:

I normalize OLTP to 3NF first, then denormalize only with metrics—star schemas for analytics or cached counts with a clear update path, not upfront duplication.

What interviewers are testing: Whether you write FD notation (X → Y) and use it to justify decomposition steps.

A functional dependency X → Y means: if two rows agree on X, they must agree on Y.

Notation: student_id → student_name

Normalization algorithms decompose tables using FDs. Interviewers may ask you to list FDs from a table definition.

A strong answer is:

Functional dependencies describe what determines what—I list them from a schema, then use them to justify 2NF and 3NF decomposition steps.

What interviewers are testing: Your normalization walkthrough on a messy table—name the anomaly, split tables, show keys.

Given (denormalized):

orders(order_id, customer_name, customer_email, product_name, qty, unit_price)

Problems:

• Customer data repeats per line → update anomaly on email change
• Product name repeats → rename product touches many rows
• Cannot list products without an order (insert anomaly)

3NF decomposition:

customers(customer_id PK, name, email)
products(product_id PK, name, current_price)
orders(order_id PK, customer_id FK, order_date)
order_lines(order_id FK, product_id FK, qty, unit_price)

order_lines.unit_price stores the price charged at purchase time. line_total can normally be derived as qty * unit_price unless there is a business reason to persist it.

A strong answer is:

I separate customer, product, order, and order-line facts into their own tables so each fact is stored in the right place and updates such as changing a customer's email happen once.

What interviewers are testing: Whether you name all four ACID properties and tie each to a failure when it is violated.

ACID guarantees reliable transactions:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If second UPDATE fails, ROLLBACK undoes the debit—atomicity. Consistency is partly app + constraint responsibility; engine provides mechanisms. Durability typically relies on writing to a transaction log or other durable storage—for example WAL in PostgreSQL.

A strong answer is:

"ACID: transfer example for atomicity, constraints for consistency, isolation levels for concurrent reads, durable transaction logging for durability—I tie each letter to a concrete failure mode."

What interviewers are testing: Whether you narrate active → partially committed → committed/rolled back without skipping states.

BEGIN starts active state. COMMIT → committed → terminated. Error or ROLLBACK → aborted → terminated.

Savepoints allow partial rollback inside a transaction: SAVEPOINT sp1; ... ROLLBACK TO sp1;

A strong answer is:

Active until COMMIT or ROLLBACK—failed transactions abort; I mention savepoints when a long transaction needs partial undo without full rollback.

What interviewers are testing: Whether you map isolation levels to dirty read, non-repeatable read, and phantom phenomena.

SQL standard isolation levels (lowest to highest):

*PostgreSQL REPEATABLE READ also prevents phantoms, but serialization anomalies can still occur. MySQL InnoDB behavior varies—know your engine.

Default on PostgreSQL is READ COMMITTED—good balance for most OLTP.

A strong answer is:

I compare isolation levels with the dirty/non-repeatable/phantom table—READ COMMITTED for most apps, stronger isolation such as SERIALIZABLE when invariants require serial execution semantics and retries are acceptable—not merely because the domain is financial.

What interviewers are testing: Whether you contrast locking, MVCC, and optimistic control with when each fits.

Lost update without control: two txs read balance 100, both write 90—should be 80.

Fix: row-level lock, atomic UPDATE balance = balance - 10, or optimistic concurrency with a version column:

UPDATE account
SET balance = ?, version = version + 1
WHERE id = ? AND version = ?;

If zero rows are updated, another transaction changed the row—retry or reject.

A strong answer is:

"DBMSs use techniques such as locking and MVCC to coordinate concurrent transactions. At the application level, I may use explicit row locks for contested updates or optimistic version checks when conflicts are relatively rare and can be retried."

What interviewers are testing: Whether you understand how deadlocks form, how a DBMS resolves them, and how application design reduces and safely retries them.

Deadlock: cycle of transactions each waiting for a lock held by another.

Tx A: lock row 1 → wait row 2
Tx B: lock row 2 → wait row 1

Detection: the DBMS detects a wait cycle and aborts one transaction as the victim so the remaining transactions can proceed. Victim-selection policy is DBMS-specific.

Prevention:

• Acquire locks in a consistent order (always id ascending)
• Keep transactions short
• Lock only what is needed
• Retry aborted transactions appropriately—on deadlock (40P01 in PostgreSQL). PostgreSQL uses 40001 for serialization failures; applications may need retry logic for both depending on the transaction design.

BEGIN;
SELECT * FROM accounts WHERE id IN (1,2) ORDER BY id FOR UPDATE;
COMMIT;

Retry the transaction when the DBMS reports a deadlock failure.

A strong answer is:

"Deadlock is a cycle where transactions wait on locks held by each other. The DBMS detects the cycle and aborts one transaction; I reduce the risk with consistent lock ordering and short transactions, and the application retries appropriate deadlock failures."

What interviewers are testing: Your safe money-transfer narrative—atomic debit/credit, isolation choice, and failure rollback.

Requirements: debit and credit must be atomic; balance never negative; concurrent transfers safe.

BEGIN;

SELECT id, balance
FROM accounts
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;

-- Verify the source account has sufficient funds.
-- If not, ROLLBACK and do not execute the credit.

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

COMMIT;

Lock both account rows in a consistent order by account ID—not source-first/destination-second—so opposite-direction transfers between the same two accounts cannot deadlock.

Steps to mention:

1. BEGIN explicit transaction
2. Lock both rows (FOR UPDATE) in deterministic ID order
3. Verify funds before debit; do not credit if debit would fail
4. COMMIT or ROLLBACK on any failure
5. Idempotent client token for retries (avoid double transfer)

Isolation: READ COMMITTED plus explicit row locking can be sufficient for this design; use stronger isolation such as SERIALIZABLE when the application's invariants require serial execution semantics and the application can retry serialization failures.

A strong answer is:

BEGIN, lock both accounts in id order with FOR UPDATE, check balance, debit and credit in one transaction, COMMIT or ROLLBACK—I add idempotency keys for client retries.

What interviewers are testing: Whether you pick B-tree, hash, bitmap, and full-text indexes by access pattern—not habit.

Index implementations and terminology vary by DBMS, but these are common index patterns interviewers expect you to recognize:

Some engines support non-key included columns for covering-index designs.

Clustered vs secondary indexes are engine-specific; for example, InnoDB organizes row data using its clustered index, normally the primary key. See Q38 for the engine differences.

Indexes can speed row lookup for SELECT, UPDATE, DELETE, and joins, but every additional index consumes storage and adds maintenance cost when indexed data changes.

A strong answer is:

B-tree for most OLTP equality and range; composite B-tree indexes are most naturally useful from the leading indexed columns, but actual usefulness depends on predicates, ordering, selectivity, and the DBMS optimizer—I mention write overhead and avoid indexing every column.

What interviewers are testing: Whether you know clustered and non-clustered indexes are engine-specific—not one universal implementation.

Clustered and non-clustered indexes are engine-specific concepts. A clustered index determines or closely controls the physical organization of table rows, so only one clustering order is possible. Secondary/non-clustered indexes are separate access structures.

Choose clustered keys carefully when the engine supports them—often narrow, monotonic id columns.

A strong answer is:

"A clustered index affects how table rows are physically organized, while secondary indexes are separate structures. The exact implementation differs by engine, so I wouldn't assume that every primary key is automatically a clustered index."

What interviewers are testing: Whether you outline parse → optimize → execute and where indexes change the plan.

Stages when you run SELECT:

1. Parser — syntax check, build parse tree
2. Validator — tables/columns exist; type check
3. Query rewriter — views expanded, rule-based transforms
4. Optimizer — cost-based plan (seq scan vs index scan, join order)
5. Execution engine — runs plan, returns rows

Statistics (row counts, histograms) feed the optimizer—stale stats → bad plans.

Interview link: PostgreSQL EXPLAIN for engine-specific plan reading.

A strong answer is:

Parse, validate, optimize, execute—I say the optimizer picks plans from statistics, so stale stats cause slow queries even with indexes present.

What interviewers are testing: Whether you justify an index choice from predicates, sort order, and selectivity—not guess.

Query:

SELECT * FROM orders
WHERE customer_id = 42 AND status = 'open'
ORDER BY created_at DESC
LIMIT 20;

Reasoning:

PostgreSQL partial-index example if status = 'open' is the important hot subset:

CREATE INDEX idx_open_customer ON orders (customer_id, created_at DESC)
WHERE status = 'open';

Mention write amplification—only add indexes queries prove they need.

A strong answer is:

Composite index on (customer_id, status, created_at DESC) matches filter and ORDER BY—a partial index on open orders if that is the hot path; I verify with EXPLAIN, not guessing.

What interviewers are testing: Whether you choose SQL vs NoSQL from consistency, schema, and query patterns—not hype.

Choose SQL when: complex relationships, strong consistency, reporting joins, financial correctness.

Choose a NoSQL model when: the document, key-value, graph, or wide-column access pattern fits the workload better, schema flexibility is valuable, or the selected system's distribution model matches the scale and consistency requirements (see MongoDB interview questions).

Many systems use both—Postgres for source of truth, Redis for cache, Elasticsearch for search.

A strong answer is:

"I choose relational databases when relationships, constraints, transactions, and ad hoc SQL fit the workload. I choose a document, key-value, graph, or other NoSQL model when its access pattern and distribution characteristics fit better—the decision comes from workload and consistency requirements, not the label."

What interviewers are testing: Whether you explain partition tolerance as unavoidable and the real pick between C and A.

CAP: in a network partition, you choose between Consistency and Availability (Partition tolerance is mandatory in distributed systems).

Consistency in CAP means linearizable/single-copy consistency—not generic ACID "C."

Not "pick two of three" in normal operation—only during partition.

Examples:

• Traditional RDBMS primary: often CP-ish for writes
• Cassandra: tunable; AP leaning
• Redis Cluster: configuration-dependent

PACELC extension: else (no partition), choose Latency vs Consistency.

A strong answer is:

During a partition you trade consistency for availability or vice versa—I explain CP vs AP with a concrete system, not as a buzzword triangle.

What interviewers are testing: Whether you contrast sync vs async replication and backup/recovery approaches—not treat PITR as just another backup file type.

Replication — copies data for read scale or HA:

Backup and recovery approaches:

RPO (how much data loss) and RTO (downtime) drive strategy. A backup strategy is incomplete until restore procedures are tested.

A strong answer is:

"Replication is for availability, not a substitute for backup. I design backups around RPO and RTO, preserve transaction logs when point-in-time recovery is required, and test restores regularly."

What interviewers are testing: Whether you know views as virtual tables and when triggers hide logic you should not.

Views simplify security (GRANT on view not base table) and encapsulate joins.

Triggers for audit logs work; avoid heavy business logic that belongs in application services.

Materialized view stores results—refresh on schedule for dashboards.

A strong answer is:

Views encapsulate queries and permissions; procedures bundle DB-side logic; triggers for audit—I avoid trigger spaghetti that duplicates app business rules.

What interviewers are testing: Whether you explain version columns or timestamps that detect lost updates without long locks.

When two users update the same row without holding long locks, use a version column or timestamp:

UPDATE accounts
SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = 5;

Check the affected row count; retry or fail if zero rows were updated.

A strong answer is:

I use optimistic concurrency with a version column for low-contention updates—if zero rows are affected, I reload and retry instead of silently losing an update.

What interviewers are testing: Whether you can self-audit gaps across ER, normalization, transactions, and indexing before the loop.

• DBMS vs RDBMS crisp definition
• Draw ER diagram with cardinality
• 1NF → 3NF with anomalies explained
• Keys: PK, FK, candidate, surrogate
• ACID with transfer example
• Isolation levels anomaly table
• Deadlock prevention + retry
• B-tree index rationale
• SQL vs NoSQL trade-offs
• CAP during partition
• One normalization scenario on whiteboard
• SQL technical interviews for live coding
• PostgreSQL if engine-specific loop
• MongoDB for document contrast

A strong answer is:

I rehearse ER + normalization weekly, explain ACID on a transfer, and pair theory here with SQL practice from the SQL guide—not campus notes without queries.