1.3Fundamentals of the relational model

The word "table" is used casually every day, but in relational-model theory it is defined as a mathematically rigorous concept called a relation. This section is not about memorizing terminology; it builds understanding toward the practical judgment of why relational-model terms sometimes diverge from the intuitive image of a table, and which key to choose as the primary key when multiple candidate keys exist.

1.3.1Relation, tuple, attribute, and domain

• A relation is the basic structure that holds data in the relational model, corresponding to a table. Mathematically, it is defined as a set of tuples. A tuple is a single record making up a relation, corresponding to one row of a table. An attribute is an individual value item making up a tuple, corresponding to one column (column name) of a table.
• A domain is the set of values an attribute may take. For example, if the domain of a "gender" attribute is {male, female, other}, no value outside that set may be stored in it. A domain can express a semantically stricter constraint than a mere data type (e.g., an integer type further restricted to the domain "months 1 through 12").
• The degree of a relation is the number of attributes composing it (i.e., the number of columns in the table). The cardinality is the number of tuples composing it (i.e., the number of rows). Degree is a relatively static value fixed at design time, whereas cardinality is a dynamic value that changes as data is added or removed—a key difference in character between the two.

1.3.2Candidate keys, primary keys, and foreign keys

• A candidate key is an attribute (or set of attributes) that can uniquely identify a tuple within a relation and satisfies minimality—removing any further attribute would break that uniqueness. A relation can have multiple candidate keys (e.g., both an employee number and an email address could each uniquely identify an employee).
• A primary key is the one candidate key selected from among the possibly multiple candidate keys to represent the relation's identifier. A candidate key not chosen as the primary key is called an alternate key. A foreign key is an attribute that references the primary key (or a candidate key) of another relation, implementing an association (referential constraint) between relations.

Suppose you are the database designer responsible for the logical design of a human-resources system and are designing an "employee" relation. You determine that three attributes—employee number, national ID number (My Number), and internal email address—each satisfy uniqueness as candidate keys. It would be dangerous to jump to the conclusion that "the national ID number is legally unique and the most reliable identifier, so it should be the primary key." The national ID number is sensitive personal information, and treating it as a primary key referenced by foreign keys from many tables increases the burden of access control and auditing, and widens the blast radius if the data is ever leaked—a real design risk. The alternative of "make the internal email address the primary key" also carries risk, since the email address can change due to organizational changes or a name change after marriage, which would cascade updates through every foreign key value embedded across many tables. The most stable design, therefore, is to choose the employee number—an artificial key with no business meaning, issued once and never changed (effectively a surrogate key)—as the primary key. The judgment expected of a level-4 designer is to keep the national ID number and internal email address as alternate keys with a uniqueness (UNIQUE) constraint, while not using them as the target of foreign-key references.

1.3.3Section summary

• Relation = a set of tuples (table), tuple = row, attribute = column, degree = column count (static), cardinality = row count (dynamic)
• Multiple candidate keys can exist, one of which is chosen as the primary key; an unchosen candidate key is an alternate key
• Primary-key selection is judged by resistance to business-driven change and avoiding sensitive information (keep sensitive data like a national ID number as an alternate key, not the primary key)