Data Management: Databases, Keys, and Integrity
Data management covers how organisations store, retrieve, protect, and govern data. The exam tests relational concepts, SQL fundamentals, normalisation, transaction properties, and backup practice. Questions commonly present a table design with a flaw, or a failure scenario, and ask for the correct fix or recovery step.
Relational fundamentals
- A relational database stores data in tables of rows and columns; each row should be uniquely identified by a primary key.
- A foreign key references a primary key in another table and enforces referential integrity.
- A candidate key is any column set that could serve as primary key; a composite key uses two or more columns.
- NULL means unknown or absent, not zero or blank.
Normalisation and design
- First normal form removes repeating groups and enforces atomic values.
- Second normal form removes partial dependencies on part of a composite key.
- Third normal form removes transitive dependencies between non-key columns.
- Normalisation reduces redundancy and update anomalies; reporting systems sometimes denormalise deliberately for speed.
SQL and transactions
- DDL defines structures (CREATE, ALTER, DROP); DML manipulates rows (SELECT, INSERT, UPDATE, DELETE).
- ACID means atomicity, consistency, isolation, and durability; transactions either complete fully or roll back.
- Indexes speed reads but slow writes and consume space.
Backup and recovery
- Full backups copy everything; incremental backups copy changes since the last backup of any kind; differential backups copy changes since the last full backup.
- Restoring from incremental sets needs the last full plus every increment in order.
- Archiving moves inactive data for retention; it is not a substitute for backup.
Worked example
A customer table has columns CustomerID, Name, Phone1, Phone2, and Phone3. Reasoning: multiple phone columns store repeating groups of the same fact, violating first normal form. The fix is a separate Phones table with PhoneNumber and CustomerID as a foreign key, allowing any number of numbers per customer. That change also removes the empty-column waste when a customer has one phone and eliminates the anomaly where adding a fourth number requires redesigning the table.
Traps that cost marks
- Confusing primary and foreign keys, or assuming a table may have several primary keys.
- Mixing up DELETE (removes rows), TRUNCATE (empties a table quickly), and DROP (removes the table definition).
- Treating an archive as a backup, or forgetting that a differential restore needs only the last full plus the latest differential.
- Assuming third normal form is always the goal; analytical workloads may denormalise on purpose.