Databases
Databases asks about the relational model, SQL, normalisation and transactions. Expect one question on relational algebra or SQL semantics, one on functional dependencies and normal forms, and one on serializability or concurrency control.
Relational model and SQL
- Core operators: selection σ, projection π, join ⋈, Cartesian product ×, renaming ρ and division.
- NULL comparisons yield UNKNOWN, so
col = NULLnever matches; use IS NULL. - COUNT(*) counts rows; COUNT(col) skips NULLs. WHERE filters rows before grouping, HAVING filters groups.
Normalisation
- Compute attribute closures with Armstrong's reflexivity, augmentation and transitivity.
- 2NF: no partial dependency on part of a candidate key. 3NF: for every X → A, X is a superkey or A is prime. BCNF: every non-trivial determinant is a superkey.
- A decomposition of R into R1, R2 is lossless iff R1 ∩ R2 → R1 or R1 ∩ R2 → R2.
Transactions
- ACID: atomicity, consistency, isolation, durability.
- Conflict serializability is tested by a precedence graph; a cycle means not serializable. Two-phase locking guarantees conflict serializability; strict 2PL additionally prevents cascading rollback.
Worked example
R(A,B,C,D) with A → B, B → C, C → D. The closure A⁺ = ABCD, so A is the only candidate key. Since A is a single attribute there is no partial dependency, so R is in 2NF. But B → C has a non-superkey determinant and C is not prime, so 3NF fails. Highest normal form: 2NF.
Traps that cost marks
- Confusing 3NF with BCNF; a BCNF decomposition may not preserve dependencies.
- Assuming view serializability and conflict serializability coincide - conflict serializable implies view serializable, not the reverse.