Relations, tuples, and keys
The relational model represents all data as relations: tables of rows and columns with precise mathematical meaning. Keys identify rows and connect tables.
Relations and attributes
A relation is a set of tuples sharing the same attributes. In table terms, the relation is the table, tuples are rows, and attributes are columns. Each attribute has a domain, the set of allowed values, such as integers, dates, or strings up to 50 characters. The relation schema is written like Student(id, name, birth_date, major).
Properties of relations
Because a relation is a set, its tuples have no inherent order and, in pure theory, no duplicates; the order of attributes does not matter either, since columns are referred to by name. Values are atomic, single indivisible values, in the original model. SQL tables relax some of this: they may contain duplicate rows unless constraints forbid them.
Superkeys and candidate keys
A superkey is any set of attributes whose values uniquely identify each tuple. A candidate key is a minimal superkey: removing any attribute would lose uniqueness. A Student relation might have candidate keys {id} and {email}; {id, name} is a superkey but not minimal.
Primary keys
The designer chooses one candidate key as the primary key, used as the main way to identify tuples. Primary key values must be unique and not null. Natural keys come from the real world, such as a national ID or ISBN; surrogate keys are artificial, such as auto-incrementing integers or UUIDs, chosen because natural values can change, be missing, or be sensitive.
Foreign keys
A foreign key is an attribute, or set of attributes, in one relation that refers to the primary key of another. Enrolment(student_id, course_id, grade) has student_id referring to Student.id and course_id referring to Course.id. Foreign keys express relationships and let the DBMS enforce that references point to rows that actually exist.
Composite keys
A key may need several attributes together. In Enrolment, neither student_id nor course_id alone is unique, since students take many courses and courses have many students, but the pair (student_id, course_id) is, assuming one enrolment per student per course. Composite primary keys are common in linking tables.
NULL values
NULL represents a missing or inapplicable value, such as an unknown phone number. It is not zero or an empty string. Comparisons with NULL produce 'unknown' rather than true or false, so WHERE phone = NULL never matches; SQL uses IS NULL. This three-valued logic surprises many people and is a frequent source of query bugs.
Worked example
Relations: Author(author_id, name, country), Book(isbn, title, year, author_id). In Book, isbn is a natural primary key, and author_id is a foreign key referencing Author. If Author has rows 1 'Ada' and 2 'Ben', inserting a book with author_id 3 violates the foreign key, because no author 3 exists. Deleting author 1 while books still refer to that author must be blocked, cascaded, or handled explicitly.
Key takeaways
Relations are sets of tuples over attributes with domains; tables, rows, and columns are their practical forms. Pure relations have unordered, distinct tuples with atomic values; SQL tables may allow duplicates. Superkeys identify tuples uniquely; candidate keys are minimal superkeys; the chosen one is the primary key, unique and non-null. Natural keys come from data; surrogate keys are artificial. Foreign keys reference other relations' keys. Composite keys combine attributes. NULL means missing and creates three-valued logic, tested with IS NULL.
Flashcard
Set of tuples with the same attributes
Check your understanding
In Enrolment(student_id, course_id, grade), why is (student_id, course_id) a better primary key than student_id alone?