Company questionsGroundworkDatabase management systems

Fundamentals · Database management systems

Database management systems: the questions candidates report

  • 18 questions
  • 5 themes
  • 15 companies
  • 16 with candidate reports

Database theory questions candidates report from campus and lateral rounds, from normalisation and keys to transactions and indexing. Read the answer, then say it aloud with your own example.

Schema design and normalisation

  1. What are the normal forms from 1NF to BCNF, and how would you normalise a given table?CoreSeveral reports

    Normalisation organises tables so each fact is stored once, which removes update, insert and delete anomalies. It works by removing dependencies that should not exist.

    • 1NF: every cell holds one atomic value and there are no repeating groups.
    • 2NF: 1NF, and no non-key column depends on only part of a composite key.
    • 3NF: 2NF, and no non-key column depends on another non-key column (no transitive dependency).
    • BCNF: for every non-trivial dependency X determines Y, X is a superkey.

    Example: enrolment(student_id, course_id, student_name, course_title) breaks 2NF because the names depend on only half of the key. Split it into student, course and enrolment. The usual follow-up is when you would denormalise on purpose.

  2. Design a relational schema for a scenario such as students and courses, and justify the keys.CoreSeveral reports

    Start from the entities and how they relate, then turn each into a table with a key.

    1. List the entities (student, course, instructor) and the facts each carries.
    2. Decide the cardinality of each relationship: one-to-many becomes a foreign key on the many side, and many-to-many becomes a junction table holding both keys.
    3. Pick primary keys and add constraints: NOT NULL, UNIQUE, CHECK, and foreign keys with a delete rule.
    4. Normalise to 3NF, then add indexes for the queries you expect.

    Example: students enrol in many courses and a course has many students, so use enrolment(student_id, course_id, grade) with a composite primary key on the two ids. State assumptions aloud, such as whether a course can have several instructors. Follow-up: write a query on the schema you just drew.

  3. When and why would you denormalise a schema?CoreSeveral reports

    Denormalisation deliberately stores redundant data, such as a copy of a customer name on each order or a precomputed counter, to avoid joins and speed up reads. Normalisation optimises for write correctness; denormalisation optimises for read latency.

    • When: read-heavy paths, reporting and analytics tables, sharded systems where cross-shard joins are expensive, and caches or materialised views.
    • Cost: duplicates can drift apart, writes get heavier, and you need code, triggers or jobs to keep copies in sync.

    Example: an order keeps a snapshot of the shipping address and item price at purchase time, which is also the correct business behaviour. Normalise first, measure, then denormalise the specific hot query. Follow-up: how you would keep the copies consistent.

  4. What is the ER model, and how do you map an ER diagram to tables?CoreSeveral reports

    The entity-relationship model describes data before any tables exist. An ER diagram shows entities (things like Student), attributes (name, roll number) and relationships (enrols in), with cardinality such as one-to-one, one-to-many and many-to-many, and whether participation is mandatory.

    • A strong entity has its own key; a weak entity depends on an owner entity and is identified only together with it.
    • Attributes can be simple, composite, multivalued or derived.

    Mapping to tables: each entity becomes a table, a one-to-many relationship becomes a foreign key on the many side, a many-to-many relationship becomes a junction table, and a multivalued attribute becomes its own table. Example: an order line is a weak entity of an order. Follow-up: turn a described scenario into a diagram and then into tables.

Keys and the relational model

  1. How do primary, candidate, super, unique and foreign keys differ?CoreSeveral reports

    A super key is any set of columns that uniquely identifies a row. A candidate key is a minimal super key, meaning no column can be dropped. The primary key is the candidate key you choose as the main identifier; it cannot be NULL and there is one per table. A unique key is a uniqueness constraint on another candidate key; it may allow NULL (how many NULLs depends on the engine) and a table can have many. A foreign key is a column whose values must match a key in another table (or be NULL), which enforces referential integrity. A composite key uses several columns together.

    Example: in users(id, email, phone), both id and email can be candidate keys; id is the primary key and email gets a unique constraint. Follow-up: natural versus surrogate keys.

  2. How does a DBMS differ from an RDBMS, and what are the levels of data abstraction?CoreSeveral reports

    A DBMS is software that stores, retrieves and manages data and handles concurrency, security and recovery. An RDBMS is a DBMS based on the relational model: data in tables of rows and columns, keys and foreign keys, SQL, and ACID transactions. Simpler DBMSs may store data as files, hierarchies or documents without enforced relations.

    Three levels of data abstraction give data independence:

    • Physical: how data sits on disk (files, pages, indexes).
    • Logical: tables, columns, relationships and constraints.
    • View: the part of the data that a particular user or application sees.

    Example: adding an index changes the physical level without touching any query, which is physical data independence. Follow-up: logical versus physical data independence.

    DBMS notes →

SQL behaviour

  1. What kinds of joins exist, and when does each one apply?CoreSeveral reports

    A join combines rows from two tables using a condition.

    • INNER: only rows that match on both sides.
    • LEFT (or RIGHT): all rows from one side, with NULLs where the other side has no match.
    • FULL: all rows from both sides, NULL-filled where unmatched (MySQL has no FULL join).
    • CROSS: every pair of rows, so m times n rows.
    • Self join: a table joined to itself under aliases, for example employees to their managers.

    Pitfalls: NULL never equals NULL in a join condition, so rows with NULL keys do not match, and a WHERE filter on the right table usually turns a left join into an inner join. With 3 left rows and 2 right rows, a cross join gives 6. Follow-up: how the engine executes it (nested loop, hash or merge join).

  2. How do GROUP BY and aggregates work, and what is the difference between WHERE and HAVING?CoreSeveral reports

    GROUP BY collapses rows that share the same values in the grouping columns into one row each, so aggregates such as COUNT, SUM, AVG, MIN and MAX run per group. WHERE filters individual rows before grouping; HAVING filters groups after aggregation, so only HAVING can use an aggregate.

    Logical order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. Example: to find departments with more than five well-paid employees, put the salary test in WHERE, group by department, and put the count test in HAVING. Pitfalls: in standard SQL every selected column must be grouped or aggregated, COUNT(*) counts rows with NULLs while COUNT(col) skips them, and other aggregates ignore NULLs. Follow-up: why a non-aggregate condition belongs in WHERE.

  3. What is the difference between RANK, DENSE_RANK and ROW_NUMBER?CoreSeveral reports

    All three number rows inside a partition ordered by a key, without collapsing rows. They differ on ties. For scores 90, 90, 80:

    • ROW_NUMBER gives 1, 2, 3: always unique, with ties broken arbitrarily unless you add a tiebreaker.
    • RANK gives 1, 1, 3: ties share a rank and the following ranks are skipped.
    • DENSE_RANK gives 1, 1, 2: ties share a rank and no gap follows.

    Syntax: RANK() OVER (PARTITION BY dept ORDER BY salary DESC). Use DENSE_RANK for the Nth highest salary, and ROW_NUMBER to keep one row per group or to remove duplicates. You cannot filter on a window function in WHERE; wrap it in a subquery or CTE. Follow-up: the top two salaries per department.

  4. What is the difference between DELETE, TRUNCATE and DROP?CoreSeveral reports
    • DELETE is DML: it removes the rows matching an optional WHERE, logs each row, fires row triggers and can be rolled back in a transaction.
    • TRUNCATE removes all rows, takes no WHERE, and is much faster because it deallocates data pages instead of logging each row; in many engines it resets identity counters and is classed as DDL.
    • DROP removes the table itself, with its data, indexes and, in most engines, its permissions.

    Rollback depends on the engine: PostgreSQL and SQL Server allow TRUNCATE inside a transaction, while MySQL and Oracle commit implicitly. SQLite has no TRUNCATE, so you use DELETE FROM t without a WHERE. TRUNCATE usually cannot run on a table that a foreign key references. Follow-up: DDL versus DML versus DCL.

  5. What are views, triggers and stored procedures, and when would you use each?CorePrep guides
    • A view is a stored query that behaves like a virtual table. It hides complexity, restricts which columns or rows users see, and keeps one definition of a join. A materialised view stores the result and must be refreshed.
    • A trigger is code that runs automatically before or after an insert, update or delete on a table. Typical uses are audit logs and enforcing rules, but it hides logic and can slow writes.
    • A stored procedure is named SQL logic stored in the database and called with parameters. It cuts round trips and centralises rules, but is harder to version, test and port between engines.

    Example: a view active_orders filters out cancelled rows so that every report uses the same definition. Follow-up: can you update through a view? Usually only simple views over one table allow it.

Transactions and concurrency

  1. What are the ACID properties, and how does a database maintain each one?CoreSeveral reports

    ACID describes the guarantees of a transaction.

    • Atomicity: all of its changes happen or none do; the engine rolls back using an undo log.
    • Consistency: a transaction moves the database from one valid state to another, respecting constraints and rules.
    • Isolation: concurrent transactions behave as if they ran in some serial order, enforced by locking or MVCC.
    • Durability: once committed, data survives a crash, achieved by flushing a write-ahead log to disk before acknowledging the commit.

    Example: a transfer debits one account and credits another, and a crash between the two statements must not lose money. Consistency is partly the application's job, since the database only enforces the constraints you declared. Follow-up: how isolation levels weaken the I part.

  2. Explain isolation levels, locking and two-phase commit.DeepSeveral reports

    Isolation levels trade correctness for concurrency by choosing which anomalies are allowed.

    • Read uncommitted: dirty reads are possible.
    • Read committed: no dirty reads, but a repeated read can change (non-repeatable read).
    • Repeatable read: a row read twice stays the same; the standard still allows phantom rows, though some engines prevent them.
    • Serializable: results match some serial order.

    Engines implement this with locks (shared and exclusive, row or table) or with MVCC snapshots, where readers do not block writers.

    Two-phase commit coordinates one transaction across several databases: the coordinator asks every participant to prepare, and only if all vote yes does it send commit. It blocks if the coordinator fails after the prepare phase. Follow-up: sagas as an alternative.

  3. What is a deadlock in a database, and how is it prevented or handled?CoreSeveral reports

    A database deadlock occurs when two or more transactions each hold a lock that the other needs, so none can proceed. Example: T1 locks row A and then wants row B, while T2 has locked row B and wants row A.

    Four conditions must all hold: mutual exclusion, hold and wait, no preemption, and circular wait. Engines usually detect deadlocks with a wait-for graph and abort one victim, which the application must retry; some rely on lock timeouts instead.

    • Acquire locks in a consistent order across code paths.
    • Keep transactions short and never wait for user input inside one.
    • Index the columns you update, so fewer rows are locked.
    • Retry the transaction when a deadlock error comes back.

    Follow-up: how wait-die and wound-wait schemes avoid cycles.

Indexing, scaling and choosing a store

  1. How do you choose between a SQL and a NoSQL database, for example for a payments system?CoreSeveral reports

    Relational databases give a fixed schema, joins and multi-row ACID transactions. NoSQL stores (document, key-value, wide-column, graph) trade some of that for a flexible schema and easier horizontal scale, usually by partitioning on a key.

    Choose by access pattern, not fashion:

    • Payments, ledgers and inventory suit relational, because correctness across several rows matters more than raw write volume.
    • Huge key-based lookups, feeds, sessions and catalogues with varying attributes suit a document or key-value store.
    • Ad hoc queries and reporting favour relational; known, narrow access paths favour NoSQL.

    Avoid absolute claims such as NoSQL has no transactions or SQL cannot scale, because many engines blur both lines. The follow-up is usually how you would shard the relational store or keep the NoSQL one consistent.

  2. What is a database index, which data structure backs it, and what are the trade-offs?CoreSeveral reports

    An index is a separate structure that maps column values to row locations, so a lookup avoids scanning the whole table. Most relational engines use a B+ tree: a balanced, shallow tree whose leaves are linked in sorted order, so both point lookups and range scans cost about O(log n) page reads.

    • Clustered: the table rows are stored in index order, so there is at most one per table (in MySQL InnoDB it is the primary key).
    • Non-clustered: a separate tree whose leaves point to the rows or to the primary key.

    Cost: every insert, update and delete must maintain each index, and indexes take space. A composite index on (a, b) usually helps filters on a, or on a and b, but generally not on b alone. Follow-up: when does the planner ignore an index (low selectivity, a function on the column, a leading wildcard)?

  3. How do sharding, partitioning and replication differ, and what does the CAP theorem say?DeepSeveral reports

    Partitioning splits one table into pieces; sharding places those pieces on different servers by a shard key (range, hash or directory). Replication keeps copies of the same data on several servers for availability and read scale.

    • Sharding scales writes and storage but makes cross-shard joins and transactions hard, and a poor key creates hot shards.
    • Leader-follower replication scales reads, but followers can lag, so reads may be stale.

    CAP says that when a network partition happens, a distributed store must choose between consistency (every read sees the latest write) and availability (every request gets an answer). Without a partition you can have both, so the choice only bites during one. Example: a personalisation store may pick availability and accept eventual consistency, while a ledger picks consistency. Follow-up: how to reshard without downtime.

  4. How do you read an execution plan and fix a slow query?DeepPrep guides

    The optimizer picks a plan using statistics about table sizes and value distribution. Read it with EXPLAIN, or EXPLAIN ANALYZE (PostgreSQL and MySQL 8), which also runs the query and shows real times and row counts.

    1. Look for full table scans on large tables, and for estimated row counts far from the actual ones, which usually mean stale statistics.
    2. Check the join order and join type (nested loop, hash, merge), and any sort or temporary table spilling to disk.
    3. Fix the cause: add or reshape an index to match the filter and sort columns, select only the columns you need, avoid wrapping an indexed column in a function, and page with the last-seen key instead of a large OFFSET.

    Example: WHERE DATE(created_at) equal to a day usually cannot use an index on created_at, but a range on the column can. Follow-up: when more indexes make things worse.

Keep going

Companies do not publish their questions and rotate them. These come from public candidate write-ups and prep guides, so treat the labels as one person's account or one list's tag. The answers are written for this site.

Now make it stick

Run a mock interviewBack to all questions