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 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) is a DBMS that stores data in tables (relations) with rows and columns, enforces relational integrity via keys and constraints, and uses SQL (or SQL-like languages) for access.

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.

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.

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

Practice 10 SQL problems weekly from SQL technical interview questions. Run transactions locally on PostgreSQL to see COMMIT / ROLLBACK behavior.

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.

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.

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). Each database has its own schema namespace. Catalog / system tables store metadata about schemas.

A strong answer is:

Schema is the logical design—tables and constraints; instance is the live data at a point in time; I do not confuse CREATE TABLE (schema) with INSERT (instance).

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, logical ER model, and physical storage—so I can add an index or split a table without rewriting every application query.

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.

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.

SQL command categories:

TRUNCATE is DDL-like—fast, often cannot roll back, resets identity. DELETE is DML—row-level, logged, transactional.

BEGIN;
INSERT INTO accounts (id, balance) VALUES (1, 100);
ROLLBACK;  -- row not visible after rollback

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.

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.

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.

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.

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.

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.

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.

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.

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 protects accidental orphan deletes in OLTP. CASCADE suits strict ownership (line items die with order).

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.

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.

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

Use IS NULL / IS NOT NULL. Three-valued logic (TRUE, FALSE, UNKNOWN) affects WHERE filters—rows with UNKNOWN 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.

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.

A table is in 1NF when:

1. Each column holds atomic (indivisible) values
2. Each row is unique (has a key)
3. No repeating groups in one column

Violations:

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

1NF is the baseline—almost all RDBMS tables satisfy it by design.

A strong answer is:

1NF means atomic values and no repeating groups—I split multi-valued columns into separate rows or tables instead of comma-separated lists.

A table is in 2NF when:

1. It is in 1NF
2. Every non-key attribute depends on the whole primary key—not a proper subset (no partial dependency)

Applies when PK is composite. 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 keys—I move attributes that depend on only part of the key into their own tables.

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.

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

Every determinant (attribute that determines others) must be a candidate key.

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 says every determinant is a candidate key—stricter than 3NF; I know the classic student-advisor example but most production schemas stop at 3NF.

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.

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.

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, unit_price)
orders(order_id PK, customer_id FK, order_date)
order_lines(order_id FK, product_id FK, qty, line_total)

line_total may be derived (qty * unit_price) or stored for historical pricing.

A strong answer is:

I split customers, products, orders, and order_lines—removing partial and transitive dependencies so customer email updates happen once in customers.

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.

A strong answer is:

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

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.

SQL standard isolation levels (lowest to highest):

*PostgreSQL REPEATABLE READ also prevents phantoms via MVCC snapshot; 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, SERIALIZABLE when financial correctness demands it despite retries.

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 retry.

A strong answer is:

Lock-based 2PL, MVCC snapshots, or optimistic versioning—I pick pessimistic locks for contested balances and MVCC for read-heavy Postgres workloads.

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: DBMS builds wait-for graph; cycle → abort victim (youngest tx, least work).

Prevention:

• Lock rows in consistent order (always id ascending)
• Keep transactions short
• Use lower isolation when safe
• Retry on deadlock error (40001 in PostgreSQL)

-- App pattern: retry up to N times on deadlock
BEGIN;
SELECT * FROM accounts WHERE id IN (1,2) ORDER BY id FOR UPDATE;
-- transfer logic
COMMIT;

A strong answer is:

Deadlock is a lock cycle—the DBMS kills a victim transaction; I prevent it by locking in consistent order, keeping txs short, and retrying on deadlock errors in app code.

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

BEGIN;

SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
SELECT balance FROM accounts WHERE id = 2 FOR UPDATE;

UPDATE accounts SET balance = balance - 100 WHERE id = 1 AND balance >= 100;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- verify row counts / balances; ROLLBACK if insufficient funds
COMMIT;

Steps to mention:

1. BEGIN explicit transaction
2. Lock both rows (FOR UPDATE) in deterministic order
3. Check balance before debit
4. COMMIT or ROLLBACK on any failure
5. Idempotent client token for retries (avoid double transfer)

Isolation: READ COMMITTED + row locks usually enough; SERIALIZABLE for strict audit systems with retry logic.

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.

Clustered (InnoDB PK): data rows stored in index order. Non-clustered: separate structure pointing to rows.

Indexes speed SELECT; slow INSERT/UPDATE/DELETE (maintain index).

A strong answer is:

B-tree for most OLTP equality and range; composite indexes match leftmost WHERE columns—I mention write overhead and avoid indexing every column.

PostgreSQL: table is heap; even PK index is non-clustered—CLUSTER command reorders physically (one-time).

Choose clustered key carefully—it is the physical order (often narrow, monotonic id).

A strong answer is:

Clustered index is the table order—one per table on SQL Server/InnoDB; PostgreSQL uses heap storage with separate indexes—I do not confuse logical PK with physical clustering.

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.

Query:

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

Reasoning:

Partial index if most queries want status = 'open':

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.

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

Choose NoSQL when: flexible schema, massive horizontal scale, simple access patterns (key lookup), document shape matches app (see MongoDB interview questions).

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

A strong answer is:

SQL for relational integrity and ad hoc joins; NoSQL for flexible schema and horizontal scale—I pick by access pattern and consistency needs, not religion.

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

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.

Replication — copies data for read scale or HA:

Backup:

RPO (how much data loss) and RTO (downtime) drive strategy. Test restore drills—backups untested are wishes.

A strong answer is:

Primary-replica for HA and read scale; full plus WAL for PITR—I define RPO/RTO and insist on tested restore, not just backup jobs running.

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.

• 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.