Relations, Tuples & Attributes

## Relation is a math term, not 'relationship' The word comes from set theory. Given domains (sets of values) D1, D2, ... Dn, the Cartesian product D1 x D2 x ... x Dn is the set of every combination of one value from each. A relation is any subset of that product, and each element of that subset is a tuple. Nothing in that definition mentions files, disk pages or foreign keys. A frequent beginner error is to hear 'relational database' and assume the relations are the links between tables. The relations are the tables. ## Heading and body A relation has two parts. The **heading** is a fixed, unordered set of attributes. An attribute is a pair: a name and a domain, where the domain is the set of values the attribute may take together with the operations defined on those values. `{customer_id: INTEGER, email: TEXT, signup_date: DATE}` is a heading of three attributes. The heading is the relation's type: it says what shape a tuple must have. The **body** is a set of tuples. A tuple maps every attribute name in the heading to a value drawn from that attribute's domain. Every tuple in the body carries exactly the same heading; a relation cannot have a ragged tuple with one extra attribute. Two derived numbers fall out: **degree** is the number of attributes in the heading, **cardinality** is the number of tuples in the body. ## Tuples are addressed by name, not by position Because the heading is a set of named attributes, a tuple is a mapping, not a list. In the model there is no 'third column' - there is the attribute named `signup_date`. Two tuples are equal exactly when they agree on the value of every attribute. That is why the model can declare column order irrelevant. ## What 'set of tuples' implies Sets are unordered. So a relation's body has no first tuple and no row numbers; any ordering you observe is a property of how you asked, not of the relation. ## Mapping onto SQL, and where it leaks Everyday speech, which interviewers expect you to use: table = relation, row = tuple, column = attribute, column type = domain, column count = degree, row count = cardinality. The deviations you should be able to name when pushed: - An SQL table is a **bag (multiset)**: without a uniqueness constraint two identical rows can coexist, so it is not strictly a set of tuples. - SQL gives columns an **ordinal position**; `SELECT *` returns them in declared order and some constructs address them by number. Relations have no such notion. - SQL allows **NULL**, a marker meaning 'no value here' that belongs to no domain, so an SQL row is not always a total mapping from attribute to domain value. - A table is a named, persistent, physically stored object; a relation is a pure value. ## Why the distinction earns its keep The heading/body split is what licenses the engine's freedom. Because the body is an unordered set and attributes are named rather than positional, the engine may lay rows out however it likes, return them in whatever order a plan produces, and rewrite or reorder work, as long as the resulting set of tuples is the same. When application code depends on something the model never promised - insertion order, a column's position - it is depending on an accident of the current physical layout, and that dependency breaks silently when the layout changes. The definitions also set up the rest of the theory: a key is a subset of the heading that uniquely determines a tuple in any body, and relational operators take relations and return relations, which is what makes them composable.