Databases and Information Systems
Domain 6 - Databases and information systems
Tests relational design, keys, normalisation, SQL, transactions and data integrity.
Core rules
- A DBMS controls storage, concurrent access, backup and security; a database reduces redundancy but does not automatically eliminate it.
- Relation = table; tuple = row; attribute = column; primary key uniquely identifies; foreign key references another relation.
- Normalisation: 1NF atomic values; 2NF no partial dependency on part of a composite key; 3NF no transitive dependency.
- SQL: SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY; INSERT/UPDATE/DELETE change data.
- ACID: atomicity, consistency, isolation, durability.
Worked example
Enrolment(student_id, student_name, course_code, course_name) has primary key (student_id, course_code). student_name depends only on student_id, and course_name only on course_code - partial dependencies, so it is not 2NF. Decompose into Student(student_id, student_name), Course(course_code, course_name) and Enrolment(student_id, course_code). Reasoning: each non-key attribute now depends on the whole key of its relation. This removes update anomalies: renaming a course is one update, not many.
Traps
Do not confuse primary and foreign keys. NULL is unknown, not zero. WHERE filters rows before grouping; HAVING filters groups. COUNT(*) counts rows including NULLs; COUNT(column) ignores NULLs. Normalisation reduces anomalies but may require joins.