Revision and Exam Questions

Cheat sheet

DBMS vs files: redundancy, inconsistency, integrity, atomicity, concurrency and security problems solved by a DBMS. Architecture: external, conceptual, internal levels; physical and logical data independence; schema vs instance. ER model: entities, attribute types, relationships, cardinality (1:1, 1:N, M:N), total/partial participation, weak entities, ISA, aggregation; mapping rules (M:N → junction table). Relational model: relation, tuple, attribute, domain, degree, cardinality; super, candidate, primary, alternate, foreign keys; entity and referential integrity. Relational algebra: σ, π, ρ, ∪, −, ∩, ×, ⋈, outer joins, ÷. SQL: DDL, DML, DCL, TCL; logical order FROM-WHERE-GROUP BY-HAVING-SELECT-ORDER BY; NULL semantics. Normalisation: FDs, Armstrong's axioms, closures, candidate keys; 1NF, 2NF (no partial), 3NF (no transitive), BCNF (every determinant a super key); lossless join test; dependency preservation. Transactions: ACID, states, schedules, conflict serializability via precedence graphs, recoverable and cascadeless schedules; anomalies and isolation levels. Concurrency: S/X locks, 2PL, strict 2PL, deadlocks, wait-die, wound-wait, timestamps, optimistic control, MVCC. Recovery: WAL, checkpoints, undo/redo, ARIES. Storage: pages, buffer pool, B+ trees, hash indexes, clustered vs secondary, composite and covering indexes; query optimisation and join algorithms. Distributed: CAP, BASE, replication, sharding, 2PC.

Common exam and interview questions

Practise answering each with a definition and an example.

1. Explain the three-schema architecture and the two kinds of data independence.
2. Differentiate super key, candidate key, primary key and foreign key with an example.
3. Convert an ER diagram with a weak entity and an M:N relationship into tables.
4. Write relational algebra and SQL for a "for all" query using division.
5. Given R(A,B,C,D) and a set of FDs, find all candidate keys and the highest normal form.
6. Decompose a relation into BCNF and check whether it is lossless and dependency preserving.
7. Explain ACID with a bank transfer example.
8. Test a schedule for conflict serializability using a precedence graph.
9. Compare 2PL, timestamp ordering and MVCC; explain wait-die and wound-wait.
10. Explain WAL and how a DBMS recovers after a crash.
11. Why are B+ trees preferred for database indexes?
12. State the CAP theorem and give examples of CP and AP systems.

Show your working

In normalisation and serializability questions, examiners award marks for the steps: list the FDs, compute closures, draw the precedence graph. A correct final answer with no working often scores less than clear steps with a small slip.

त्वरित जाँच: A relation is in 3NF but not in BCNF. What must be true?

• It has repeating groups
• It has no candidate keys
• It contains a partial dependency of a non-prime attribute
• Some FD X → A has a non-super-key X where A is a prime attribute