Database Management Systems

Relations, keys, and integrity

A relational schema describes attributes, domains, and constraints; a database instance contains the current rows. A candidate key minimally identifies a tuple. A foreign key restricts a relationship between relations, but it does not automatically establish every business rule. Separate logical design from physical storage. An enrollment relation might use (student_id, course_id, term) as its key, because the same student can take a course in different terms. Omitting term would encode a different, possibly unintended rule.

References: PostgreSQL official relational and SQL tutorial

Query reasoning and SQL's edge cases

Relational algebra builds queries from selection, projection, joins, and set operations. SQL often uses bag semantics, so duplicates require deliberate handling. A join can multiply rows when a matching key is not unique. NULL represents missing or unknown information and creates three-valued logic: comparing a column to NULL with equality does not test missingness. Reason about the rows produced before aggregation, then groups, then the filtering of groups. WHERE and HAVING act at different stages.

References: PostgreSQL official relational and SQL tutorial

Worked aggregate query

Suppose Enrollment(student_id, course_id, grade) has one row for each enrollment. To count successful enrollments by course, first filter grades, then group by course. This counts enrollments, not distinct students across all courses. If a separate section table is joined first and has multiple matching rows per course, the count can be inflated. Check cardinalities with a tiny hand-built dataset before trusting a plausible-looking aggregate.

SELECT course_id, COUNT(*) AS successful_enrollments
FROM Enrollment
WHERE grade >= 50
GROUP BY course_id
HAVING COUNT(*) >= 3;

References: PostgreSQL official relational and SQL tutorial

Functional dependencies and normalization

A functional dependency X -> Y states that equal X values force equal Y values in every valid instance, not merely in the current sample. Compute an attribute closure by repeatedly applying dependencies until no new attributes appear. Normalization reduces redundancy and update anomalies. If student_id determines student_name, storing the name in every enrollment repeats a fact. A decomposition should be lossless; otherwise joining its pieces can create spurious rows. Dependency preservation is a separate design property.

References: PostgreSQL official relational and SQL tutorial

Indexes and query execution

An index trades storage and write work for a faster access path. A B+ tree supports ordered lookup and ranges, while hashing is naturally suited to equality. The best choice depends on selectivity, data distribution, ordering, and workload. A query optimizer compares plans using estimated cardinalities and costs; a syntactically shorter query is not inherently cheaper. Nested-loop, hash, and merge joins have different requirements. Inspect an execution plan and compare estimates with observed work before adding indexes indiscriminately.

References: PostgreSQL official relational and SQL tutorial

Transactions and different data models

Transactions group operations into a unit with a defined correctness and failure contract. Isolation determines which concurrent effects are visible; it is separate from durable storage. Parameterized queries keep user values separate from SQL structure. Document, property-graph, RDF, and vector databases emphasize different representations and access patterns. Vector similarity search ranks candidates by a chosen metric; approximate indexes may trade recall for speed. Similarity does not provide a foreign-key guarantee. Choose a model by the questions and invariants the application must support, not by novelty.

References: PostgreSQL official relational and SQL tutorial

Practice & worked answers

Original exercises for this guide.

Public notes & references

Course sources establish the scope above. Reference materials support the explanations; linked lecture notes and books remain with their authors.

• Carleton COMP 3005 — Fall 2026 public outline ↗Course scope · Fall 2026
• PostgreSQL official relational and SQL tutorial ↗Official documentation
• PostgreSQL SQL language reference ↗Official documentation