Database Concepts and Management Systems
What the domain covers
Database Concepts and Management Systems tests how data is organised, related, protected, and queried. Expect relational terminology, keys, integrity rules, basic SQL, normalisation, transactions, and the difference between a database and a DBMS.
Core rules and concepts
- A database is an organised collection of related data; a DBMS is the software that creates, controls, and queries it (for example MySQL, PostgreSQL, Oracle).
- In a relation (table), a tuple is a row, an attribute is a column, and the domain is the set of allowed values for an attribute.
- A primary key uniquely identifies each row and cannot be NULL. A candidate key is any minimal unique identifier; one is chosen as primary. A foreign key references a primary key in another table and enforces referential integrity.
- Entity integrity forbids NULL primary keys; referential integrity forbids orphan foreign-key values.
- SQL sub-languages: DDL (CREATE, ALTER, DROP), DML (SELECT, INSERT, UPDATE, DELETE), DCL (GRANT, REVOKE).
- Normalisation reduces redundancy and update anomalies: 1NF removes repeating groups, 2NF removes partial dependencies, 3NF removes transitive dependencies.
- Transactions follow ACID: atomicity, consistency, isolation, durability.
Worked example
A Student table has columns StudentID, Name, and Phone, but one row stores “98765, 91234” in Phone. This violates first normal form because the cell holds multiple values. The fix is a separate Phone table with columns StudentID and Phone, where StudentID is a foreign key to Student. Reason: 1NF requires atomic values. Splitting the repeating phone numbers also allows indexing and querying by phone without parsing text.
Traps that cost marks
A primary key may be composite (two or more columns), not necessarily a single ID. A foreign key may contain duplicates and may be NULL unless constrained. DELETE removes rows; DROP removes the table structure. SELECT does not change data. Normalisation beyond 3NF can add joins and complexity, so it is not an automatic goal for every reporting table.