Database Management Systems
Core concepts
Understand the three-schema architecture (external, conceptual, internal), data independence, and the relational model: relations, tuples, attributes, keys (super, candidate, primary, foreign), and integrity constraints (entity and referential). Master relational algebra basics (select, project, join) and SQL: DDL (CREATE, ALTER, DROP), DML (INSERT, UPDATE, DELETE), DQL (SELECT with WHERE, GROUP BY, HAVING, ORDER BY), joins, subqueries, views, and triggers.
Normalization is heavily tested: 1NF (atomic values), 2NF (no partial dependency on a composite key), 3NF (no transitive dependency), BCNF. Know functional dependencies and Armstrong's axioms. Also study transactions and ACID properties, concurrency control (locking, two-phase locking), and recovery via logs and checkpoints. Understand indexing (B-tree, hash), and the difference between a DBMS and a file system.
Traps to watch
- Confusing WHERE (filters rows before grouping) with HAVING (filters groups after aggregation).
- Assuming 3NF automatically implies BCNF; it does not.
- Forgetting that NULL comparisons use IS NULL, not = NULL.
- Mixing candidate keys with primary keys; a table may have several candidate keys but one primary key.
Worked example
Given R(A,B,C) with A -> B and B -> C, the key is A. Since B -> C is a transitive dependency on a non-key attribute, R is in 2NF but not 3NF. Decompose into R1(A,B) and R2(B,C). Rule of thumb: every non-key attribute must depend on the key, the whole key, and nothing but the key.