Databases and Information Systems: Model, Normalisation and SQL
This domain tests the relational model end to end: designing a schema, proving which normal form it satisfies, writing a query that returns exactly the requested rows, and reasoning about transactions and integrity. Marks are lost on small semantic details rather than on big conceptual errors.
Core rules
- Relational vocabulary: relation, tuple, attribute, degree, cardinality. Keys: superkey, candidate key, primary key, foreign key.
- Integrity: entity integrity (primary key never null) and referential integrity (a foreign key matches an existing key or is null).
- Functional dependencies drive normalisation. 1NF: atomic values. 2NF: no partial dependency on part of a composite key. 3NF: no transitive dependency on a non-key attribute. BCNF: every determinant is a candidate key.
- SQL logical evaluation order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. That order explains why a SELECT alias cannot be used in WHERE.
- Joins: inner, left, right, full outer. Unmatched rows produce NULLs.
- ACID properties protect transactions; isolation levels trade consistency against concurrency and permit anomalies such as dirty reads and lost updates.
- Indexes (typically B-trees) accelerate filtered lookups and joins but slow inserts and updates.
Worked example
R(StudentID, CourseID, StudentName, Instructor) with StudentID → StudentName and CourseID → Instructor. The candidate key is (StudentID, CourseID). StudentID → StudentName is a partial dependency, violating 2NF, so decompose into R1(StudentID, StudentName) and R2(StudentID, CourseID, Instructor). In R2 the dependency CourseID → Instructor makes the key transitively determine a non-key attribute, so decompose again into R3(StudentID, CourseID) and R4(CourseID, Instructor). Query: SELECT dept, COUNT(*) FROM emp WHERE salary > 1000 GROUP BY dept HAVING COUNT(*) > 2; filters rows before grouping and departments after grouping.
Traps that cost marks
Putting a group condition in WHERE instead of HAVING; using COUNT(column) when NULLs should be counted - only COUNT(*) counts rows; assuming NULL = NULL is true; forgetting that NOT IN with a NULL in the list returns no rows; and listing a non-aggregated column in SELECT that is absent from GROUP BY.