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.
Model relationships and trace relational algebra
An entity model describes relevant objects, attributes and relationship cardinalities before physical implementation. A many-to-many relationship usually requires a linking relation with foreign keys to the participating entities; a one-to-many design places the reference on the many side under the chosen representation. State optional participation separately from maximum cardinality. A key constraint must follow the business requirements, not a convenient name.
In classical relational algebra, selection chooses rows satisfying a predicate, while projection retains named attributes and eliminates duplicate tuples under set semantics. SQL SELECT usually retains duplicates unless DISTINCT is requested, so do not silently substitute bag behavior for set behavior. A Cartesian product pairs every tuple of its operands. A join retains pairs satisfying its condition; a natural join additionally matches identically named attributes, which can be surprising after a schema change. For the relation Enroll(student, course), division by a relation of required courses expresses students related to every required course. Do not project away course before division, because the necessary pairing information would already be lost. Check the schemas of both operands, then trace a tiny relation that includes a missing enrollment.