n-ary Relations and Named Columns | Databases

From binary to n-ary

A binary relation involves two domains. Database tables rarely have only two columns. The generalisation is straightforward: if we have $n$ sets (domains) $S_1, S_2, \ldots, S_n$ , then an n-ary relation $R$ is:

$R \subseteq S_1 \times S_2 \times \cdots \times S_n = \{(s_1, s_2, \ldots, s_n) \mid s_i \in S_i\}$

Each element of $R$ is an n-tuple, a sequence of $n$ values drawn from the respective domains.

Tabular presentation

N-ary relations are naturally displayed as tables:

name	sid	age
Alice	abc123	20
Bob	def456	21
Carol	ghi789	20

Here $n = 3$ . Each row is a tuple (Alice, abc123, 20). The three columns correspond to the three domains: string, string, integer.

The problem with positional columns

In the mathematical definition, columns are identified by position: the first column corresponds to $S_1$ , the second to $S_2$ , and so on. In a query language, writing “select column 3” is error-prone and unreadable.

Named columns and attribute names

Database relations use named columns: we associate a name $A_i$ (called an attribute name) with each domain $S_i$ .

Instead of tuples $(s_1, s_2, \ldots, s_n)$ , we use records: sets of pairs each associating an attribute name $A_i$ with a value in domain $S_i$ . A record is:

$\{(A_1, s_1), (A_2, s_2), \ldots, (A_n, s_n)\}$

where $s_i \in S_i$ .

Column order does not matter

In a mathematical tuple, the order of components matters: $(A, 3) \neq (3, A)$ . In a record with named attributes, the order of the pairs does not matter:

$\{(\text{name}, \text{Alice}), (\text{age}, 20)\} = \{(\text{age}, 20), (\text{name}, \text{Alice})\}$

This matches how SQL works: SELECT name, age FROM Students and SELECT age, name FROM Students both produce valid results; the column order in the output is determined by the SELECT clause, not by any underlying ordering.

Schema notation

A database relation $R$ is therefore defined as a finite set:

$R \subseteq \{\{(A_1, s_1), (A_2, s_2), \ldots, (A_n, s_n)\} \mid s_i \in S_i\}$

The schema of $R$ is the set of attribute-name/domain pairs. Schema notation:

$R(A_1 : S_1,\; A_2 : S_2,\; \ldots,\; A_n : S_n)$

Example schema.

$\text{Students}(\text{name} : \text{string},\; \text{sid} : \text{string},\; \text{age} : \text{integer})$

Row order

Just as column order does not matter, row order does not matter in the relational model. A relation is a set of records. SQL results are unordered unless you explicitly include ORDER BY.

In practice, rows are stored in some physical order on disk (often a B-tree on the primary key), but the relational model does not expose or rely on that ordering.

Summary

An n-ary relation generalises binary relations to $n$ domains.
Database relations use named columns (attribute names) rather than positional columns.
Column order and row order do not matter at the logical level.
A schema declares the attribute names and their types.