Data and Information Management
The Data and Information Management domain tests how data is structured, stored and queried. Expect entity-relationship modelling, normalisation, SQL, and questions about integrity and validation.
Relational fundamentals
A relation (table) has attributes (columns) and tuples (rows). A primary key uniquely identifies a row; a foreign key references a primary key in another table and enforces referential integrity. Entities are things, relationships are associations, and cardinality (1:1, 1:M, M:N) describes how many instances participate. A many-to-many relationship needs a linking table.
Normalisation
1NF: atomic values and no repeating groups. 2NF: 1NF plus no non-key attribute depending on only part of a composite key. 3NF: 2NF plus no transitive dependency, where a non-key attribute depends on another non-key attribute.
Worked example
Student(StudentID, StudentName, CourseID, CourseName). The key is (StudentID, CourseID). StudentName depends only on StudentID, and CourseName only on CourseID, so both are partial dependencies and the table violates 2NF. Split into Student(StudentID, StudentName) and Course(CourseID, CourseName), with an Enrolment(StudentID, CourseID) table to represent the many-to-many link.
SQL
SELECT CourseID, COUNT() FROM Student GROUP BY CourseID HAVING COUNT() > 10; WHERE filters rows before grouping, while HAVING filters groups after aggregation.
Traps
- Using = NULL instead of IS NULL.
- Putting an aggregate condition in WHERE instead of HAVING.
- Assuming COUNT(column) counts NULLs, which it does not.
- Deleting a parent row without handling child rows.
- Treating validation (data is reasonable) as verification (data was transferred accurately).