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 constraint references an eligible unique parent key and enforces referential integrity; permitted nulls depend on the declared constraints. 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: 1 NF atomic values, 2 NF no partial dependency on part of a composite key, 3 NF 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.id, 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.
Trace representations and queries separately
A bit pattern acquires meaning from its stated encoding. In unsigned binary, each position contributes a power of two, so 1011 represents eight plus two plus one, or eleven. Signed integers and floating-point values require an explicit representation convention; the same bits do not imply the same numeric value under every convention. Text also requires an encoding, and a byte count is not automatically a character count.
For a database query, construct a tiny example containing two people with the same name, a missing child row and a null field. Group by stable student identity as well as the displayed name, so distinct students are not accidentally merged. A left join preserves an unmatched parent, but a later WHERE condition on a child column can remove that row. Decide which relation and multiplicity are intended before choosing syntax. Test the result against the example rather than relying on the query’s appearance.