Information Systems and Databases
Domain scope
Information systems collect, process, store and disseminate data to support decisions. The exam tests relational database concepts, ER modelling, keys, normalisation, SQL and transaction integrity.
Core rules
- A relation is a table with atomic values; each tuple is a row and each attribute is a column. A primary key uniquely identifies a row and cannot be NULL.
- A foreign key in one relation references a primary key in another and enforces referential integrity.
- 1NF removes repeating groups; 2NF removes partial dependencies on part of a composite key; 3NF removes transitive dependencies, where non-key attributes depend on other non-key attributes.
- ACID: Atomicity (all or nothing), Consistency (valid states), Isolation (concurrent transactions do not interfere) and Durability (committed changes survive failure).
Worked example
Table Enrollment(StudentID, CourseID, StudentName, CourseTitle, Lecturer) has primary key (StudentID, CourseID). StudentName depends on StudentID only; CourseTitle and Lecturer depend on CourseID only. This violates 2NF. Decompose into Student(StudentID, StudentName), Course(CourseID, CourseTitle, Lecturer) and Enrollment(StudentID, CourseID). Reasoning: after decomposition, each non-key attribute depends on the whole key of its table, and no transitive dependency remains.
Traps
- Calling a candidate key a primary key when the table has several candidate keys.
- Assuming NULL equals zero or an empty string; comparisons with NULL need IS NULL.
- Using SELECT * when the question asks for specific columns.
- Forgetting that 3NF requires 2NF first.