Databases and Information Management
What the exam tests
Databases store structured data for reliable retrieval. The exam tests the relational model, keys, relationships, normalisation, SQL basics, transaction properties and data integrity.
Core rules
- A relation is a table; rows are records/tuples and columns are attributes. A primary key uniquely identifies each row and cannot be null.
- A foreign key references a primary key in another table and enforces referential integrity. It may allow duplicates and, depending on design, nulls.
- Normalisation reduces redundancy and update anomalies. First normal form removes repeating groups; second removes partial dependencies; third removes transitive dependencies.
- ACID: atomicity, consistency, isolation, durability. A transaction must complete fully or not at all.
Worked example
A table Marks(StudentID, StudentName, CourseCode, CourseName, Mark) stores course names repeatedly. StudentName depends on StudentID, and CourseName depends on CourseCode, so both are partial dependencies in a composite key. Split into Student(StudentID, StudentName), Course(CourseCode, CourseName) and Marks(StudentID, CourseCode, Mark). Reasoning: each non-key attribute now depends on the whole key of its table, reducing duplication and update anomalies.
Traps that cost marks
Do not say a foreign key must be unique. Do not confuse DELETE (removes rows) with DROP (removes a table). In SQL, NULL is not equal to zero or an empty string; use IS NULL. Do not assume every table needs a single-column primary key-composite keys are valid.
Exam focus
Expect key identification, normalisation to 3NF, simple SELECT queries and ACID scenario questions.