Database Management Systems
What this domain tests
DBMS questions test the relational model, keys and integrity constraints, normal forms, SQL query semantics, joins, transactions and indexing.
Keys and integrity
A super key uniquely identifies a tuple; a candidate key is a minimal super key; the primary key is the chosen candidate key and can never be NULL; alternate keys are the remaining candidates; a foreign key enforces referential integrity by pointing to a primary key in another relation.
Normalisation in order
- 1NF: every attribute holds atomic values, no repeating groups.
- 2NF: in 1NF and no non-prime attribute depends on part of a composite candidate key (no partial dependency).
- 3NF: in 2NF and no transitive dependency, that is no X to Y and Y to Z with Y not a key.
- BCNF: every determinant is a candidate key; stricter than 3NF.
SQL semantics
WHERE filters rows before grouping; HAVING filters groups after GROUP BY. COUNT(*) counts rows including NULLs, while COUNT(column) skips NULLs. ACID stands for atomicity, consistency, isolation and durability.
Worked example
Relation Enrol(StudentID, CourseID, StudentName, CourseName, Instructor) with StudentID determining StudentName, and CourseID determining CourseName and Instructor. The key is the pair (StudentID, CourseID), so StudentName depends on part of the key and the relation is in 1NF but not 2NF. Decompose into Student(StudentID, StudentName), Course(CourseID, CourseName, Instructor) and Enrol(StudentID, CourseID). Each partial dependency is removed and the schema reaches 3NF.
Traps that cost marks
- Declaring a relation in 3NF as BCNF without checking every determinant.
- Writing col = NULL instead of col IS NULL, which never matches.
- Confusing primary key with UNIQUE, which permits NULL in many systems.
- Mixing DELETE (removes rows, can be rolled back) with TRUNCATE and DROP.