Data Management, Databases and Information Systems
Data are raw facts; information is data processed into something meaningful. The exam tests database terminology, keys, relationships, normalisation, and validation and verification.
Terminology
A field (attribute) is one item of data, a record (tuple) is one row, and a table (relation) is a collection of records of the same type. A primary key uniquely identifies each record and cannot be null. A candidate key is any field that could serve as the primary key; a composite key uses two or more fields. A foreign key is a field in one table that refers to the primary key of another and enforces referential integrity.
Relationships
One-to-one, one-to-many, and many-to-many. A many-to-many relationship cannot be stored directly; it needs a junction (link) table holding the keys of both entities.
Normalisation
- 1NF: atomic values only, no repeating groups.
- 2NF: 1NF plus no partial dependency - no non-key field depends on only part of a composite key.
- 3NF: 2NF plus no transitive dependency - no non-key field depends on another non-key field.
Validation and verification
Validation checks data against rules: presence, range, type, length, format, lookup list. Verification checks that data were copied accurately: double entry, visual check, parity. Validation cannot stop a plausible but wrong value.
Worked example
Membership(StudentID, StudentName, ClubID, ClubName, Advisor) with primary key (StudentID, ClubID). ClubName and Advisor depend only on ClubID, so there is a partial dependency - the table is not in 2NF. Split into Student(StudentID, StudentName), Club(ClubID, ClubName, Advisor) and Membership(StudentID, ClubID).
Traps that cost marks
- Confusing a primary key with a foreign key.
- Saying validation guarantees correct data.
- Claiming 3NF without first checking 1NF and 2NF.
- Storing calculated totals, which duplicate data and can become inconsistent.