Databases and Information Management
Databases and Information Management tests the relational model, normalization, SQL behavior, transactions, and indexing. Expect a dependency analysis, an isolation-level question, or an index trade-off.
Core rules
A candidate key uniquely determines every attribute. 1NF requires atomic values; 2NF removes partial dependencies on part of a composite key; 3NF removes transitive dependencies on non-superkeys; BCNF requires every determinant to be a superkey. ACID means atomicity, consistency, isolation, and durability. Isolation levels prevent anomalies such as dirty reads, non-repeatable reads, and phantom reads, but higher isolation usually costs concurrency. B+ tree indexes speed reads and range scans but add write and storage overhead.
Worked example
Relation R(A,B,C) has key A and dependencies A→B, A→C, and B→C. Because A is a single-attribute key, there is no partial dependency, so R is in 2NF. However, B→C is a transitive dependency: B is not a superkey and C is non-prime. R violates 3NF. Decompose into R1(A,B) and R2(B,C), which preserves the dependencies and removes the transitive dependency.
Traps
3NF and BCNF are not interchangeable. NULL comparisons use IS NULL, not = NULL, and NULL affects aggregate results. A dirty read reads uncommitted data; a non-repeatable read sees a row change after the first read. Adding an index is not automatically beneficial for write-heavy workloads.