DBMS Interview Questions and Answers

A DBMS (database management system) is software that stores data and lets many users define, query, update and protect it through one interface, like MySQL or MongoDB. Over plain files it adds a query language, enforced constraints, safe concurrent access, crash recovery and permissions. The database is the data; the DBMS is the software that manages it (what a database is).

Files leave every rule to the application: copies of the same data drift out of sync, each new report needs new code, two writers at once can corrupt a file, and a crash mid-write leaves half an update. A DBMS fixes these with normalized tables, SQL, constraints, locking and transaction logs. Files still suit logs, media and bulk data written once.

An RDBMS is a DBMS built on the relational model: tables linked by primary and foreign keys, queried with SQL, with constraints and normalization. DBMS is the wider term that also covers hierarchical (IBM IMS), document (MongoDB) and key-value (Redis) systems. Every RDBMS is a DBMS. Avoid the myth that a DBMS serves one user and an RDBMS many.

The ANSI/SPARC architecture has three levels: internal (how data sits on disk: files, pages, indexes), conceptual (the whole database as tables, keys and constraints) and external (what each user or app sees, usually through views). The mappings between levels give data independence: adding an index changes no query.

Data independence is changing the schema at one level without rewriting the level above. Physical: change storage, such as adding an index, without touching the logical schema or any query. Logical: change tables, such as splitting one, without breaking applications, usually by keeping a view with the old shape. Physical independence is easier to achieve than logical.

A schema is the design of the database (tables, columns, types, constraints) and rarely changes; an instance is the data in it at one moment and changes with every write, like a class versus its current objects. "Schema" also means a namespace inside a database (PostgreSQL's public), so say which meaning you are answering.

SQL commands are grouped by what they act on:

• DDL (structure): CREATE, ALTER, DROP, TRUNCATE
• DML (data): INSERT, UPDATE, DELETE, plus SELECT, which some books call DQL
• DCL (permissions): GRANT, REVOKE
• TCL (transactions): COMMIT, ROLLBACK, SAVEPOINT

CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT NOT NULL);      -- DDL
INSERT INTO courses (title) VALUES ('DBMS'), ('Operating Systems');      -- DML
UPDATE courses SET title = 'Database Systems' WHERE id = 1;              -- DML
SELECT id, title FROM courses;                                           -- DQL

The SQL cheat sheet lists the syntax.

DELETE removes chosen rows (DML: WHERE, triggers, logs each row). TRUNCATE removes all rows by deallocating pages (DDL: no WHERE, no DELETE triggers). DROP removes the table itself with its indexes and constraints. TRUNCATE and DROP roll back in PostgreSQL and SQL Server but commit implicitly in MySQL and Oracle. SQLite has no TRUNCATE. A DELETE inside a transaction can be undone; here all 3 rows come back:

CREATE TABLE logs (id INTEGER PRIMARY KEY, msg TEXT);
BEGIN;
INSERT INTO logs (msg) VALUES ('boot'), ('login'), ('logout');
COMMIT;

BEGIN;
DELETE FROM logs;
ROLLBACK;

SELECT count(*) AS rows_left FROM logs;

They are the relational model's names for the parts of a table: a relation is a table, a tuple a row, an attribute a column, a domain the set of values a column allows, the degree the number of columns and the cardinality the number of rows. A relation is a set, so in theory it has no duplicate rows and no order; SQL tables allow both, hence DISTINCT and ORDER BY.

A join combines rows of two tables on a condition, usually a foreign key matching a primary key. INNER keeps only matches; LEFT and RIGHT keep every row of one side, with NULLs where nothing matches; FULL keeps both sides; CROSS pairs every row with every row; a self join joins a table to itself (employee and manager).

CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE employees (name TEXT, dept_id INTEGER);
INSERT INTO departments VALUES (1, 'Sales'), (2, 'HR');
INSERT INTO employees VALUES ('Asha', 1), ('Ravi', 1), ('Meera', NULL);

SELECT e.name, d.name AS dept
FROM employees e LEFT JOIN departments d ON d.id = e.dept_id
ORDER BY e.name;

Meera has no department, so an inner join would drop her. MySQL has no FULL JOIN; use a UNION of a left and a right join. See left joins.

The six basic operations and their SQL: selection σ (WHERE), projection π (SELECT DISTINCT columns), union ∪ (UNION), set difference − (EXCEPT, MINUS in Oracle), Cartesian product × (CROSS JOIN) and rename ρ (AS). Join, intersection and division are built from them. SQL has no division operator ("students who took every course"), so you write a double NOT EXISTS.

A key is a set of columns that identifies a row. In students(roll_no, email, name, dept_id):

• Super key: any identifying set, such as {roll_no, name}.
• Candidate key: a minimal super key, such as {roll_no} or {email}.
• Primary key: the chosen candidate key; unique and never NULL.
• Alternate key: a candidate key that was not chosen.
• Foreign key: points to another table's key (dept_id).
• Composite key: two or more columns.
• Surrogate key: a generated id with no business meaning.

See primary keys.

Both reject duplicates, but a table has one primary key and it is never NULL, while it can have many unique keys and those accept NULL.

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    name  TEXT
);
INSERT OR IGNORE INTO users VALUES (1, 'asha@mail.com', 'Asha');
INSERT OR IGNORE INTO users VALUES (1, 'other@mail.com', 'Duplicate id');
INSERT OR IGNORE INTO users VALUES (2, 'asha@mail.com', 'Duplicate email');
INSERT OR IGNORE INTO users VALUES (3, NULL, 'No email');
SELECT id, email, name FROM users;

INSERT OR IGNORE skips rows that break a constraint, so both duplicates are dropped and the NULL email is kept. SQLite quirk: a PRIMARY KEY other than INTEGER PRIMARY KEY accepts NULL unless you add NOT NULL or use a STRICT or WITHOUT ROWID table; see the SQLite CREATE TABLE docs. See unique constraints.

A super key is any attribute set that identifies a row; a candidate key is a minimal one, where removing any attribute breaks uniqueness. In employees(emp_id, email, name), {emp_id, name} is a super key but not a candidate key, because {emp_id} alone is unique. Attributes that belong to some candidate key are prime attributes, which the normal forms refer to.

A composite key spans two or more columns, used when no single column is unique but the combination is, typically in a many-to-many junction table.

CREATE TABLE enrollments (
    student_id INTEGER,
    course_id  INTEGER,
    grade      TEXT,
    PRIMARY KEY (student_id, course_id)
);
INSERT OR IGNORE INTO enrollments VALUES (1, 101, 'A');
INSERT OR IGNORE INTO enrollments VALUES (1, 102, 'B');
INSERT OR IGNORE INTO enrollments VALUES (2, 101, 'A');
INSERT OR IGNORE INTO enrollments VALUES (1, 101, 'C');
SELECT student_id, course_id, grade FROM enrollments;

The pair (1, 101) can appear once, so the fourth insert is skipped. Column order matters: this index serves "courses of a student", and "students in a course" needs its own index on course_id.

Rules the DBMS checks on every write: domain (type, CHECK, NOT NULL), entity integrity (the primary key is unique and never NULL), key (UNIQUE) and referential integrity (a foreign key value exists in the parent, or is NULL). Rules that span rows or tables need triggers, since the major engines do not implement CREATE ASSERTION. Enforce them in the database, because there is always a second writer.

Use a generated surrogate key as the primary key and keep the natural key (email, ISBN) as UNIQUE. Natural keys change, and a changed primary key must be updated in every referencing table; they are often wide, and InnoDB copies the primary key into every secondary index. Natural keys suit stable short codes like 'IN', and junction tables.

Normalization splits tables so each fact is stored once, removing redundancy and the insert, update and delete anomalies it causes. You decompose by functional dependencies until each table reaches the target form, usually 3NF or BCNF, at the cost of more joins. The mnemonic: every non-key column depends on "the key, the whole key, and nothing but the key".

They are the three ways a table that repeats a fact goes wrong. In enrollments(student_id, student_name, course_id, course_fee), you cannot add a course's fee until someone enrolls (insertion); changing a fee means updating every row, and missing one leaves two fees (update); deleting a course's last student loses its fee (deletion). The fix stores each fact once: students, courses and enrollments.

A table is in 1NF when every column holds one atomic value and there are no repeating groups such as phone1, phone2.

-- not 1NF: skills is a list inside one column
CREATE TABLE dev_bad (name TEXT, skills TEXT);
INSERT INTO dev_bad VALUES ('Asha', 'sql,python'), ('Ravi', 'java,sql');

-- 1NF: one row per value
CREATE TABLE dev_skills (name TEXT, skill TEXT, PRIMARY KEY (name, skill));
INSERT INTO dev_skills VALUES ('Asha', 'sql'), ('Asha', 'python'),
                              ('Ravi', 'java'), ('Ravi', 'sql');

SELECT skill, count(*) AS devs FROM dev_skills GROUP BY skill ORDER BY skill;

With a list column, "who knows SQL" needs LIKE '%sql%', which also matches mysql and can use neither an index nor a foreign key. With one row per value it is WHERE skill = 'sql'.

A table is in 2NF when it is in 1NF and no non-prime column depends on only part of a candidate key (a partial dependency). In enrollments(student_id, course_id, student_name, grade), student_name depends on student_id alone, so it repeats for every course; move it to students. A table whose candidate keys are all single columns is automatically in 2NF.

A table is in 3NF when it is in 2NF and no non-prime column depends on a key through another non-key column (a transitive dependency, A → B → C). In employees(emp_id, dept_id, dept_name), dept_name depends on emp_id only through dept_id; move it to departments. Formally, for every non-trivial X → A, X is a super key or A is prime.

BCNF requires every non-trivial FD X → Y to have a super key on the left. 3NF also allows it when Y is prime, so every BCNF table is in 3NF but not the reverse.

Example: teaching(student, course, instructor), where each instructor teaches one course, so instructor → course. All columns are prime, so 3NF holds, but instructor is not a super key. Splitting into (instructor, course) and (student, instructor) is lossless but loses {student, course} → instructor. A lossless, dependency-preserving 3NF decomposition always exists; a BCNF one may not.

X → Y means rows with the same X have the same Y: emp_id → name holds, while name → emp_id fails if names repeat. Armstrong's axioms derive every implied FD:

1. Reflexivity: if Y ⊆ X, then X → Y.
2. Augmentation: if X → Y, then XZ → YZ.
3. Transitivity: if X → Y and Y → Z, then X → Z.

They are sound and complete; union and decomposition follow from them.

Compute closures: X is a super key when X+ holds every attribute, and a candidate key when no subset of it does. An attribute missing from every right side is in every key. For R(A, B, C, D, E) with A → B, B → C, CD → E, those are A and D, and AD+ = ABCDE, so AD is the only key:

from itertools import combinations

attrs = "ABCDE"
fds = [("A", "B"), ("B", "C"), ("CD", "E")]

def closure(xs):
    result = set(xs)
    changed = True
    while changed:
        changed = False
        for left, right in fds:
            if set(left) <= result and not set(right) <= result:
                result |= set(right)
                changed = True
    return "".join(sorted(result))

print("A+ =", closure("A"))
print("AD+ =", closure("AD"))

keys = []
for size in range(1, len(attrs) + 1):
    for combo in combinations(attrs, size):
        if closure(combo) == attrs and not any(set(k) <= set(combo) for k in keys):
            keys.append("".join(combo))
print("candidate keys:", keys)

A → B is a partial dependency on AD, so R is not in 2NF.

A decomposition is lossless when joining the parts gives back exactly the original rows. The test: the shared attributes are a super key of at least one part. Splitting works(emp, dept, city) on dept, which does not determine city, is lossy:

CREATE TABLE works (emp TEXT, dept TEXT, city TEXT);
INSERT INTO works VALUES ('Asha', 'Sales', 'Pune'), ('Ravi', 'Sales', 'Delhi');

-- decompose on dept, which is not a key of either part
CREATE TABLE emp_dept AS SELECT emp, dept FROM works;
CREATE TABLE dept_city AS SELECT DISTINCT dept, city FROM works;

SELECT e.emp, d.city
FROM emp_dept e JOIN dept_city d ON e.dept = d.dept
ORDER BY e.emp, d.city;

Four rows come back from two; Asha in Delhi never existed. Splitting on emp is lossless. Dependency preservation is the second check: desirable, though BCNF cannot always keep it.

4NF removes multivalued dependencies and 5NF join dependencies; both are about independent facts in one table. In teacher_info(teacher, subject, language), subjects and languages are unrelated lists, so 2 subjects and 3 languages need 6 rows. The table is in BCNF (its key is all three columns) yet redundant, and 4NF splits it into teacher_subjects and teacher_languages. 5NF says a table cannot be split into three or more parts that join back losslessly unless the keys imply the split (the supplier, part, project case). 5NF problems are rare.

Denormalization adds redundancy on purpose, such as a stored order_total or a reporting star schema, to make reads faster, at the cost of keeping copies in sync on writes. Update the copy in the same transaction, with a trigger, or rebuild it on a schedule (a materialized view). Normalize first; denormalize specific read paths after measuring.

A transaction is a group of operations the database treats as one unit: all commit or none do. A transfer is two updates that must not be separated:

CREATE TABLE accounts (
    owner   TEXT PRIMARY KEY,
    balance INTEGER NOT NULL CHECK (balance >= 0)
);
BEGIN;
INSERT INTO accounts VALUES ('Asha', 1000), ('Ravi', 200);
COMMIT;

BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE owner = 'Asha';
UPDATE accounts SET balance = balance + 300 WHERE owner = 'Ravi';
COMMIT;

SELECT owner, balance FROM accounts ORDER BY owner;

Without BEGIN, MySQL, PostgreSQL, SQL Server and SQLite autocommit each statement; Oracle keeps a transaction open from the first DML until COMMIT. See transactions.

Atomicity: all or nothing (undo log). Consistency: each transaction moves between valid states (constraints, app logic). Isolation: no one sees another transaction's partial work (locks or MVCC). Durability: committed work survives a crash (write-ahead log). Two traps: most engines default to read committed, not serializable, and the database checks only declared rules, so a transfer that debits 300 and credits 30 commits happily.

Active (running), partially committed (last statement done, changes not yet durable), committed (commit record in the log), failed (an error, deadlock or crash stopped it) and aborted (changes rolled back). The paths are active → partially committed → committed, or active → failed → aborted. After an abort the DBMS restarts the transaction (after a deadlock) or kills it (after a logic error).

It depends on the engine. PostgreSQL aborts the whole transaction, and every later statement fails until you roll back. MySQL InnoDB rolls back only the statement on a duplicate key or lock wait timeout, but the whole transaction on a deadlock (see the InnoDB error handling docs). SQLite usually undoes only the statement, though a disk full or I/O error can roll back the transaction (SQLite transaction docs). SQL Server depends on the error, unless SET XACT_ABORT ON. So roll back explicitly on any error and decide whether to retry.

A schedule is serializable when its effect equals some serial order of its transactions. To test conflict serializability, draw a precedence graph with an edge Ti → Tj for each pair of operations on the same item, at least one a write, where Ti acts first. No cycle means serializable, and a topological order gives the equivalent serial order. R1(A) W2(A) W1(A) gives T1 → T2 → T1, a cycle: the lost update. View serializability accepts more schedules but is NP-complete to test.

With a write-ahead log: a change's log record reaches disk before the changed page, and a transaction commits once its commit record is flushed, which costs one sequential fsync. After a crash, the DBMS redoes committed changes missing from the data files (durability) and undoes uncommitted changes that already reached them (atomicity); engines may write uncommitted pages early, so they need both. ARIES does this in three passes (analysis, redo, undo), and checkpoints bound the replay.

• Dirty read: reading an uncommitted write that then rolls back.
• Lost update: two transactions read and write back one value; one write is lost.
• Non-repeatable read: a row read twice changes in between.
• Phantom read: a repeated query returns newly inserted rows.
• Write skew: two transactions read overlapping data and update different rows, together breaking a rule (two doctors both go off call).

The standard's isolation levels cover only dirty, non-repeatable and phantom reads. PostgreSQL's snapshot isolation stops lost updates, MySQL's repeatable read does not, and write skew survives snapshot isolation everywhere.

The standard's four levels, by the anomalies they allow:

Serializable also rules out write skew. Defaults: read committed in PostgreSQL, Oracle and SQL Server; repeatable read in MySQL InnoDB. PostgreSQL's repeatable read and Oracle's serializable are snapshot isolation (no phantoms, but write skew), and PostgreSQL never allows dirty reads (see the PostgreSQL isolation docs). SQLite has one writer at a time, so it is serializable.

A shared (S) lock is for reading and many transactions can hold it at once; an exclusive (X) lock is for writing and conflicts with every other lock. Two transactions that each hold S and upgrade to X deadlock, which is why SELECT ... FOR UPDATE takes X up front. Under MVCC (PostgreSQL, InnoDB, Oracle), plain reads take no S locks and read a snapshot, except in InnoDB at serializable with autocommit off. SQLite locks the whole file; see WAL mode and concurrency.

Under 2PL a transaction takes all its locks before releasing any (a growing, then a shrinking phase), which makes every schedule conflict serializable. Basic 2PL may release an X lock before commit, so others read a value that can still roll back (cascading aborts). Strict 2PL holds X locks until the end; lock-based databases use it or rigorous 2PL, which holds all locks. Conservative 2PL takes every lock up front and cannot deadlock; the others can. It is not two-phase commit, which commits across nodes.

A deadlock is transactions each holding a lock another needs: T1 holds A and waits for B, T2 holds B and waits for A. It needs mutual exclusion, hold and wait, no preemption and circular wait. Databases find the cycle in the wait-for graph and roll back one victim. The search is a depth-first search:

waits_for = {"T1": ["T2"], "T2": ["T3"], "T3": ["T1"], "T4": ["T1"]}

def find_cycle(graph):
    state = {}  # 1 = on the current path, 2 = done
    path = []

    def dfs(node):
        state[node] = 1
        path.append(node)
        for nxt in graph.get(node, []):
            if state.get(nxt) == 1:
                return path[path.index(nxt):] + [nxt]
            if nxt not in state:
                found = dfs(nxt)
                if found:
                    return found
        state[node] = 2
        path.pop()
        return None

    for node in graph:
        if node not in state:
            found = dfs(node)
            if found:
                return found
    return None

print("deadlock:", " -> ".join(find_cycle(waits_for)))

T4 waits but is outside the cycle. Lock rows in a consistent order, keep transactions short, and retry victims.

Both give each transaction a start timestamp and let waits go in only one direction of age, so no cycle forms. In wait-die, an older requester waits and a younger one dies (rolls back). In wound-wait, an older requester wounds (rolls back) the younger holder, and a younger requester waits. Either way the younger one is rolled back; it restarts with its original timestamp, so it ages into a winner and never starves. Wound-wait usually causes fewer rollbacks.

Conflicting operations must run in the order of the transactions' start timestamps; there are no locks, so no deadlocks. Each item keeps R-ts(X) and W-ts(X), the largest timestamps that read and wrote it. T may read X only if ts(T) >= W-ts(X), and write it only if ts(T) is at least both; otherwise T rolls back and restarts with a new timestamp. Thomas's write rule ignores an obsolete write (ts(T) < W-ts(X) but ts(T) >= R-ts(X)) instead of rolling back, which allows schedules that are view serializable but not conflict serializable.

Read and write in one conditional statement, so there is no gap for another request to slip into:

CREATE TABLE stock (item TEXT PRIMARY KEY, qty INTEGER NOT NULL);
INSERT INTO stock VALUES ('ticket', 1);

-- request 1
UPDATE stock SET qty = qty - 1 WHERE item = 'ticket' AND qty >= 1;
-- request 2 (same statement)
UPDATE stock SET qty = qty - 1 WHERE item = 'ticket' AND qty >= 1;
SELECT changes() AS rows_updated_by_request_2, qty FROM stock;

The second update finds qty >= 1 false and changes 0 rows, so the app knows the ticket is gone. In PostgreSQL, MySQL and SQL Server the second update waits for the first's row lock and re-checks the committed value, so this is safe at read committed. Otherwise use SELECT ... FOR UPDATE or a version column.

MVCC keeps several versions of each row. An update writes a new version tagged with its transaction id, and a read takes the newest version committed before its snapshot (taken per statement at read committed, per transaction at repeatable read). Readers and writers never block each other; two writers of one row still do. PostgreSQL keeps old versions in the table for VACUUM to remove; InnoDB and Oracle rebuild them from the undo log. The cost is bloat, worst with long transactions that pin old versions.

Granularity is what a lock covers: a row, page, table or database. Fine locks allow more concurrency but cost memory; coarse ones are cheap but make transactions wait. An intention lock (IS, IX) on the table before row locks lets a table lock request see at once that rows are in use. Lock escalation swaps many row locks for one table lock: SQL Server does it when a statement holds many, so a big UPDATE can block the whole table. PostgreSQL and InnoDB do not escalate.

An index is a separate sorted structure, usually a B+ tree, that maps column values to rows, so a lookup takes about log(n) steps instead of a full scan.

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, city TEXT);
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'a@b.com';
CREATE INDEX idx_users_email ON users(email);
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'a@b.com';

The last plan is SEARCH users USING INDEX idx_users_email (email=?); before the index the query was SCAN users. Indexes help WHERE, JOIN, ORDER BY and GROUP BY. See indexes.

Each index takes space and slows writes, since inserts, deletes and most updates must change it too. Low-selectivity columns like is_active rarely help, because the optimizer prefers a scan. Index columns in frequent WHERE, JOIN and ORDER BY clauses, plus foreign keys, which most engines other than InnoDB leave unindexed.

A B tree keeps keys and data in every node; a B+ tree keeps data only in the leaves, which are linked, and uses internal nodes for routing. Databases prefer B+ trees: internal nodes hold only keys, so hundreds fit per page and three or four levels index hundreds of millions of rows, and a range scan finds its start and walks the leaf chain. A B tree's one edge: a lookup can stop above the leaves. Unlike a binary search tree, both are many-way.

A clustered index stores the rows themselves in key order, so a table has one; a non-clustered index is separate, its leaves point to rows, and each match costs an extra lookup unless the index covers the query. In InnoDB the primary key is always clustered and secondary indexes store it, so a lookup is two searches. SQL Server lets you choose. PostgreSQL tables are heaps, so every index is non-clustered. SQLite clusters on rowid, or on the primary key in a WITHOUT ROWID table.

Yes. An index on (a, b) is sorted by a, then by b, so it serves conditions on a or on a and b, but not on b alone: the leftmost prefix rule.

CREATE TABLE people (id INTEGER PRIMARY KEY, city TEXT, age INTEGER, name TEXT);
CREATE INDEX idx_city_age ON people(city, age);

EXPLAIN QUERY PLAN SELECT * FROM people WHERE city = 'Pune';
EXPLAIN QUERY PLAN SELECT * FROM people WHERE city = 'Pune' AND age > 30;
EXPLAIN QUERY PLAN SELECT * FROM people WHERE age > 30;

The last plan is SCAN people; the first two search idx_city_age. Put equality columns first and the range or ORDER BY column last. See composite indexes.

The condition is not a plain comparison on the stored column, or a scan looks cheaper. Typical causes: a function on the column (lower(email) = ?), a leading wildcard (LIKE '%gmail.com'), a type mismatch, low selectivity, OR across columns, or stale statistics (run ANALYZE). Rewrite the condition, such as dates as ranges, or index the expression:

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT);
CREATE INDEX idx_email ON users(email);
EXPLAIN QUERY PLAN SELECT id FROM users WHERE lower(email) = 'asha@mail.com';
CREATE INDEX idx_email_lower ON users(lower(email));
EXPLAIN QUERY PLAN SELECT id FROM users WHERE lower(email) = 'asha@mail.com';

The last plan searches idx_email_lower; before it existed, the query scanned.

A covering index holds every column a query needs, so the database answers from the index and skips the table lookup for each row.

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, status TEXT, total INTEGER, notes TEXT);
CREATE INDEX idx_cust_status_total ON orders(customer_id, status, total);
EXPLAIN QUERY PLAN SELECT status, total FROM orders WHERE customer_id = 42;

The plan says USING COVERING INDEX; add notes to the SELECT and each row needs a table lookup. PostgreSQL 11+ and SQL Server can add non-key columns with INCLUDE.

A hash index answers = in about O(1) but cannot do ranges, ordering or prefixes; a B+ tree keeps keys sorted and handles all of those in O(log n). Hash indexes appear in PostgreSQL USING HASH (crash-safe since version 10), MySQL's MEMORY engine and InnoDB's adaptive hash index (off by default since MySQL 8.4). B+ trees are the default since they cover every case. See hash tables.

A dense index has an entry per key value; a sparse index has one per data block, so it is smaller but needs the file sorted on that key. A primary index is on the sorted key and can be sparse (a clustering index if that field is not unique). A secondary index is on any other field, so it must be dense. A multilevel index indexes the index, the idea behind B+ trees.

The entity-relationship model designs a database before the tables: the entities, their attributes and the relationships between them. In Chen notation a rectangle is an entity (double for weak), an ellipse an attribute (underlined for the key, double for multivalued, dashed for derived), a diamond a relationship (double for identifying), and a double line total participation.

Three independent splits: simple or composite (roll_no vs address with street and city; a composite gets one column per part), single-valued or multivalued (date_of_birth vs phone_numbers, which needs its own table) and stored or derived (date_of_birth vs age, computed on read). Store the birth date, not the age: age changes every year without a write.

Cardinality is how many entities on one side relate to one on the other: 1:1 (person and passport), 1:N (department and employees), M:N (students and courses). Participation is total (double line: every employee works in a department) or partial (single line: not every employee manages one). In tables, total participation on the many side becomes a NOT NULL foreign key, and partial a nullable one.

A weak entity cannot be identified by its own attributes; it needs its owner's key plus a partial key, and it cannot exist without the owner. An order line is one: line 1 appears in every order.

PRAGMA foreign_keys = ON;
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer TEXT);
CREATE TABLE order_lines (
    order_id INTEGER REFERENCES orders(order_id) ON DELETE CASCADE,
    line_no  INTEGER,
    product  TEXT,
    qty      INTEGER,
    PRIMARY KEY (order_id, line_no)
);
INSERT INTO orders VALUES (1, 'Asha'), (2, 'Ravi');
INSERT INTO order_lines VALUES (1, 1, 'pen', 2), (1, 2, 'book', 1), (2, 1, 'pen', 5);
DELETE FROM orders WHERE order_id = 1;
SELECT order_id, line_no, product FROM order_lines;

The key is (order_id, line_no), and ON DELETE CASCADE encodes "no line without its order", so deleting order 1 removes its two lines. Chen notation draws it as a double rectangle.

Each strong entity becomes a table. A 1:N relationship becomes a foreign key on the N side, a 1:1 a foreign key with UNIQUE, and an M:N a junction table keyed by both foreign keys. A multivalued attribute gets its own table, a composite one a column per part, and a weak entity a key of owner key plus partial key.

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE courses  (id INTEGER PRIMARY KEY, title TEXT);
CREATE TABLE enrolls (
    student_id INTEGER REFERENCES students(id),
    course_id  INTEGER REFERENCES courses(id),
    enrolled_on TEXT,
    PRIMARY KEY (student_id, course_id)
);
INSERT INTO students VALUES (1, 'Asha'), (2, 'Ravi');
INSERT INTO courses  VALUES (10, 'DBMS'), (20, 'OS');
INSERT INTO enrolls VALUES (1, 10, '2026-07-01'), (1, 20, '2026-07-02'), (2, 10, '2026-07-03');

SELECT c.title, count(*) AS students
FROM enrolls e JOIN courses c ON c.id = e.course_id
GROUP BY c.title ORDER BY c.title;

Attributes of the relationship, like enrolled_on, go in the junction table because they describe the pair.

Specialization splits an entity into subtypes (Employee into Engineer and Manager); generalization merges entities into a supertype (Car and Truck into Vehicle). A hierarchy is disjoint or overlapping, and total or partial. Aggregation treats a relationship as an entity, so a manager can monitor one works_on assignment. To map a hierarchy, use one table with a type column (many NULLs), a table per type sharing the key (one join per read), or a table per concrete subclass (UNION for "all employees").

Relational databases keep tables with an enforced schema, joins in SQL and ACID transactions across tables. NoSQL stores use documents, key-value pairs, wide columns or graphs, usually with a flexible schema, few joins and per-document transactions, and are often built to scale out. The line is blurry: PostgreSQL has jsonb, and MongoDB has had multi-document transactions since 4.0.

Four families: key-value (Redis, DynamoDB) for caches and sessions, document (MongoDB, Firestore) for nested records, wide-column (Cassandra, HBase) for huge write volumes read by key and time range, and graph (Neo4j) for relationships of variable depth. Pick from the queries: a profile by id is key-value or document, friends of friends is a graph, and joins across many entities with strong consistency point back to SQL.

During a network partition, a distributed store must give up either consistency (every read sees the latest write; linearizability, not ACID's C) or availability (every live node answers). Partitions always happen, so the real choice is CP, which refuses requests to stay consistent (ZooKeeper, etcd, HBase), or AP, which answers from any replica and reconciles later (Cassandra, DynamoDB by default). Follow-up: what is PACELC? Without a partition you still trade latency against consistency.

BASE (Basically Available, Soft state, Eventually consistent) systems keep answering and let replicas disagree for a while; ACID transactions are all or nothing, and reads see committed data. Under BASE a read after your write may return the old value. Many stores let you choose per request: with N replicas, R + W > N makes every read overlap the latest write.

Choose NoSQL for a simple, known access pattern at a scale or shape that suits it: writes beyond one node (Cassandra), access by key only (sessions, caches), nested data read whole, or deep relationship queries (a graph). Default to relational when data is related, queries will change, or correctness across entities matters. "No schema" only moves the schema into your code. Many systems use both.

Yes. PostgreSQL (jsonb), MySQL (JSON), SQL Server and SQLite store JSON and query inside it:

CREATE TABLE products (id INTEGER PRIMARY KEY, doc TEXT);
INSERT INTO products VALUES
  (1, '{"name": "Laptop", "price": 55000, "tags": ["electronics", "work"]}'),
  (2, '{"name": "Desk",   "price": 8000,  "tags": ["furniture", "work"]}');

SELECT p.id, json_extract(p.doc, '$.name') AS name, t.value AS tag
FROM products p, json_each(p.doc, '$.tags') t
WHERE json_extract(p.doc, '$.price') > 10000;

json_extract reads a field and json_each turns an array into rows. Use JSON for variable attributes and normal columns for what you filter, join or constrain on; core data in JSON loses types, foreign keys and simple indexes. See JSON support.

Replication copies all the data to several servers for availability and read scaling; sharding splits the data across servers for write scaling and size. The shard key should spread load and keep most queries on one shard (tenant_id); a bad one makes hot shards. Range sharding keeps range scans but heats recent keys; hash sharding spreads evenly but loses them. Cross-shard joins and transactions become app work, and uniqueness holds only within a shard.

OFFSET 100000 reads and discards 100,000 rows first, so deep pages get slower. Keyset pagination continues after the previous page's last row with an indexed WHERE, so every page costs the same:

CREATE TABLE posts (id INTEGER PRIMARY KEY, created_at TEXT, title TEXT);
CREATE INDEX idx_posts_created ON posts(created_at, id);
INSERT INTO posts (created_at, title) VALUES
 ('2026-10-01', 'a'), ('2026-10-02', 'b'), ('2026-10-02', 'c'),
 ('2026-10-03', 'd'), ('2026-10-04', 'e');

-- the previous page ended at ('2026-10-02', 3); fetch the next 2
SELECT id, created_at, title FROM posts
WHERE (created_at, id) > ('2026-10-02', 3)
ORDER BY created_at, id
LIMIT 2;

Order by a unique combination (created_at, id), or rows with equal timestamps get skipped or repeated. You give up jumping straight to page 50.

Nested loop, hash join and sort-merge, chosen from row estimates, indexes and memory. A nested loop looks up inner matches for each outer row, ideal for a small outer side and an indexed join column. A hash join builds a hash table on the smaller input and probes it, O(n + m), for large equality joins without an index (MySQL only since 8.0.18, see the MySQL hash join docs). A sort-merge join walks two inputs sorted on the key, best when they arrive sorted. A wrong pick usually comes from a bad row estimate.

Measure, read the plan, fix the expensive step. Find the query with the slow query log or pg_stat_statements, ranked by total time. In EXPLAIN ANALYZE, look for full scans of big tables, sorts spilling to disk, and estimates far from actual rows. Usual causes: a missing index, a function on an indexed column, stale statistics, N+1 queries or lock waits.

CREATE TABLE customers (id INTEGER PRIMARY KEY, city TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);
CREATE INDEX idx_orders_customer ON orders(customer_id);
EXPLAIN QUERY PLAN
SELECT c.city, sum(o.total)
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Pune'
GROUP BY c.city;

SCAN c reads every customer to filter by city, then SEARCH o uses the index; an index on customers(city) removes the scan. See EXPLAIN QUERY PLAN.

N+1 is one query for a list plus one per item for related data: 1 query for 50 orders, then 50 for their customers. ORM lazy loading in a loop usually causes it. Fetch the related rows in one query with a join or IN:

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ravi');
INSERT INTO orders VALUES (10, 1, 500), (11, 2, 300), (12, 1, 200);

-- one query instead of 1 + N
SELECT o.id, o.total, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
ORDER BY o.id;

ORMs have it built in: Django select_related, JPA JOIN FETCH, Rails includes. A query count that grows with page size gives it away.

SQL injection is user input pasted into SQL text, where it can change the statement. Parameterized queries fix it: the SQL and the values travel separately, so a value never becomes code.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE users (name TEXT, password TEXT)")
db.executemany("INSERT INTO users VALUES (?, ?)", [("asha", "s3cret"), ("ravi", "hunter2")])

name = "nobody"
password = "' OR '1'='1"

unsafe = f"SELECT name FROM users WHERE name = '{name}' AND password = '{password}'"
print("string building:", db.execute(unsafe).fetchall())

safe = "SELECT name FROM users WHERE name = ? AND password = ?"
print("parameters:     ", db.execute(safe, (name, password)).fetchall())

The built string ends password = '' OR '1'='1', and since AND binds tighter than OR, every user matches; the parameterized query matches nothing. Table and column names cannot be parameters, so check them against an allow list. See preventing SQL injection.

A stored procedure is named code you call (CALL, EXEC) that can change data and manage transactions; a function returns a value inside queries; a trigger runs automatically on INSERT, UPDATE or DELETE. SQLite has only triggers:

CREATE TABLE salaries (emp TEXT PRIMARY KEY, amount INTEGER);
CREATE TABLE salary_audit (emp TEXT, old_amount INTEGER, new_amount INTEGER);
CREATE TRIGGER log_salary_change AFTER UPDATE OF amount ON salaries
BEGIN
    INSERT INTO salary_audit VALUES (OLD.emp, OLD.amount, NEW.amount);
END;
INSERT INTO salaries VALUES ('Asha', 50000);
UPDATE salaries SET amount = 55000 WHERE emp = 'Asha';
SELECT emp, old_amount, new_amount FROM salary_audit;

Keep triggers small, like audit logs, since logic outside the application code is easy to miss when debugging. See triggers.

Replication lag is the delay before a primary's commit shows on a replica. With asynchronous replication, the MySQL and PostgreSQL default, a user who saves a profile and reads from a replica may see the old one. Fixes: send a user's reads to the primary briefly after they write, pin a user to one replica so data never goes backwards, or read only from a replica past the write's log position (LSN, GTID).

Expand and contract: each deploy is backward compatible, so old and new code both work. To rename fullname to display_name: add the new column, write both, backfill in small batches, switch reads, stop writing the old column, then drop it in a later deploy. Traps: some ALTER TABLE forms rewrite or lock the table, so check your engine version or use gh-ost; in PostgreSQL, build indexes with CREATE INDEX CONCURRENTLY and set a short lock_timeout so a blocked ALTER fails fast instead of queuing every query behind it.

Auto-increment integers are 4 or 8 bytes and insert at the end of the index, the fastest choice on one database; UUIDs can be generated anywhere and do not leak row counts. Random UUIDv4 keys hurt clustered indexes (InnoDB, SQL Server): inserts land on random pages, causing splits and cache misses. UUIDv7 (RFC 9562) puts a timestamp first, so inserts are nearly sequential, though it reveals creation time. A common compromise is a BIGINT primary key plus a random public id.