Relational Databases
Data as relations (tables), queried with algebra/calculus, constrained by keys.
Level: beginner
What is it?
A relational database models data as relations, commonly presented as tables with rows and columns. Keys and integrity constraints express identity and relationships; relational algebra provides operators such as selection, projection, and join that underpin query reasoning.
Why does it matter?
The model separates logical meaning from physical representation. Applications can ask for a relation satisfying conditions without specifying which pages or index nodes the engine must read.
Mental model
Think of each table as a set-like relation with a defined heading (attributes) and a body (tuples). SQL often uses bag semantics, so duplicate rows can appear unless a query or constraint removes them.
How it works
- A relation's schema defines attributes and domains; each tuple supplies values for those attributes.
- Primary and candidate keys identify tuples; foreign keys express references that must remain valid.
- Selection filters rows, projection chooses columns, and join combines related tuples under a predicate.
- Normalization reduces certain update anomalies by decomposing relations according to dependencies.
- SQL nulls and duplicate-row behavior mean SQL's practical semantics are not identical to pure set-based relational algebra.
Steps
- Store students in Students(student_id, name) and courses in Courses(course_id, title).
- Store enrollment in Enrollments(student_id, course_id) rather than embedding an unbounded list in each student row.
- Join Students to Enrollments on student_id to retrieve enrolled learners.
- Use foreign keys and uniqueness constraints to enforce the relationship at the database boundary.
Real-world usage
- Designing schemas and explaining table relationships.
- Translating business requirements into keys and constraints.
- Reasoning about joins, normalization, and query plans.
Common mistakes
- Treating row order as guaranteed without ORDER BY.
- Assuming a table with a duplicate-looking row cannot contain duplicates unless a constraint prevents them.
- Using free-form text fields for relationships that should be key-based.
Interview questions
What is a candidate key?
A minimal set of attributes that uniquely identifies each tuple in a relation.
Why does row order not belong to a relational table's logical meaning?
A relation is unordered. If an application needs a particular order, it must request that order explicitly, typically with ORDER BY.