Database Management Systems
Database management systems tests the relational model, SQL, normalization, transactions, concurrency control, indexing, and recovery. Questions often require deriving keys from functional dependencies or predicting SQL results with NULLs.
Core rules and concepts
- A superkey uniquely identifies tuples; a candidate key is a minimal superkey; the primary key is the chosen candidate key.
- Functional dependency X→Y means equal X values imply equal Y values. Armstrong’s axioms are reflexivity, augmentation, and transitivity.
- 2NF removes partial dependencies on a candidate key; 3NF removes transitive dependencies; BCNF requires every nontrivial FD’s determinant to be a superkey.
- ACID means atomicity, consistency, isolation, durability. Isolation levels trade consistency for concurrency and permit phenomena such as dirty reads, nonrepeatable reads, and phantoms.
- B+ tree indexes store data only in leaves and keep leaves linked for range scans; hashing is better for equality but not ranges.
Worked example
R(A,B,C) with FDs A→B and B→C. A determines B and C, so A is a candidate key. The FD B→C is transitive through A→B, so R is not in 3NF. Decompose into R1(A,B) and R2(B,C). Both are in BCNF: in R1, A is a key; in R2, B is a key. The decomposition is lossless because the common attribute B is a key of R2, and dependency preservation holds because both FDs are represented.
Traps that cost marks
Treating a primary key as the only possible key; assuming NULL = NULL is true (it yields UNKNOWN); forgetting that COUNT(*) counts rows while COUNT(col) skips NULLs; and thinking 3NF always removes all redundancy-BCNF may be needed. In schedules, distinguish recoverable from strict schedules.