Database Systems
Database Systems tests relational design, SQL semantics, transaction correctness, and physical access paths. Expect normalization, indexing, and isolation-level reasoning.
Core rules
- Relations are sets; a candidate key uniquely identifies tuples; a primary key is the chosen candidate.
- Normalization: 1NF atomic values; 2NF no partial dependency on a composite key; 3NF no transitive dependency; BCNF every determinant is a superkey.
- SQL logical order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. WHERE filters rows before grouping; HAVING filters groups.
- ACID: atomicity, consistency, isolation, durability. Isolation levels trade anomalies against concurrency.
- B+ tree indexes support range scans and equality; hash indexes support equality only. Composite index column order matters.
- A query planner chooses access paths using statistics and cost.
Worked example
Table Orders(order_id, customer_id, customer_city, product_id, qty). If customer_city depends on customer_id, not on the whole key (order_id, product_id), the table violates 2NF. Decompose into Customers(customer_id, customer_city) and OrderLines(order_id, product_id, qty).
SQL example: SELECT customer_id, COUNT() FROM Orders WHERE qty > 0 GROUP BY customer_id HAVING COUNT() > 5. WHERE removes nonpositive quantities before grouping; HAVING keeps only customers with more than five remaining orders.
Isolation: under READ COMMITTED, a transaction can see different committed values on repeated reads (nonrepeatable read). REPEATABLE READ prevents that but may allow phantoms unless the engine adds gap locking or snapshot isolation.
Traps
- Filtering aggregate results in WHERE instead of HAVING.
- Assuming 3NF always removes all redundancy; BCNF may be needed.
- Using an index on a low-selectivity column and expecting a scan improvement.
- Confusing durability with replication.