Data, Databases, and Information Retrieval
What to know
Data questions move from raw facts to organized, queryable information. In the relational model, tables have rows (records) and columns (attributes); keys enforce identity and relationships. A primary key is unique and non-null; a foreign key references a primary key and enforces referential integrity. Know candidate, composite, and surrogate keys. SQL essentials: SELECT, FROM, WHERE, JOIN, GROUP BY, HAVING, ORDER BY, and aggregate functions. Normalization reduces redundancy and update anomalies: 1NF atomic values, 2NF no partial dependency on part of a composite key, 3NF no transitive dependency. Transactions follow ACID-atomicity, consistency, isolation, durability. Indexes speed reads but cost write time and space. Information retrieval adds inverted indexes, Boolean retrieval, ranking, precision, and recall.
Traps
WHERE filters rows before grouping; HAVING filters groups after aggregation. COUNT(*) counts rows, while COUNT(column) ignores NULLs. NULL comparisons use IS NULL, not = NULL. INNER JOIN drops unmatched rows; LEFT JOIN keeps all left-side rows. Precision is retrieved relevant items divided by retrieved items; recall is retrieved relevant divided by all relevant items. Do not assume more normalization is always better for read-heavy reporting.
Worked example
Students(id, name) and Enrollments(student_id, course). Query: SELECT s.name, COUNT(e.course) FROM Students s LEFT JOIN Enrollments e ON s.id = e.student_id GROUP BY s.name. A student with no enrollments appears with COUNT(e.course) = 0 because the left join preserves the row and the NULL course is not counted. Rule of thumb: choose the join first, then aggregate, then filter groups with HAVING.