Databases and Information Management
Databases and Information Management tests how structured data is modeled, queried, and protected. Expect relational keys, SQL statements, normalization, transactions, and the difference between logical design and physical storage.
Relational model
A relation is a table with rows (tuples) and columns (attributes). A primary key uniquely identifies a row and cannot be NULL. A foreign key references a primary key in another table and enforces referential integrity. Candidate keys are minimal unique identifiers; one is chosen as primary. NULL means unknown or missing, not zero or empty string.
SQL and normalization
SELECT retrieves columns; WHERE filters rows; ORDER BY sorts; GROUP BY groups rows for aggregate functions; HAVING filters groups. INNER JOIN returns matching rows; LEFT JOIN keeps all left-table rows. Normalization reduces redundancy: 1NF requires atomic values; 2NF removes partial dependencies on a composite key; 3NF removes transitive dependencies.
Transactions and indexing
ACID means atomicity (all or nothing), consistency (valid state), isolation (concurrent transactions do not interfere), and durability (committed changes persist). An index speeds reads on indexed columns but adds storage and slows writes.
Worked example
Table Student(id, name, course). Query: SELECT course, COUNT() FROM Student GROUP BY course HAVING COUNT() > 1. This lists courses with more than one student. WHERE cannot filter COUNT(*) because aggregation happens after row filtering; HAVING filters groups.
Traps
Do not use WHERE for aggregate conditions. COUNT(*) counts rows; COUNT(column) ignores NULLs. A foreign key may allow NULL unless declared NOT NULL. JOINs can duplicate rows when relationships are one-to-many. Normalization is not always desirable for read-heavy reporting, but exam questions usually reward reducing redundancy.