# Relations, Keys and Integrity Constraints — Database Fundamentals

Source: https://www.skillbyai.com/en/database-fundamentals/r-model

> Define relations formally and distinguish super, candidate, primary and foreign keys.

## The vocabulary of the relational model

A **relation** is a set of **tuples** over a set of **attributes**, each attribute drawing values from a **domain** (all valid values, such as integers from 0 to 100 for marks). The number of attributes is the **degree**; the number of tuples is the **cardinality**. Because a relation is a set, tuples have no inherent order and no duplicates. Keys identify tuples. A **super key** is any set of attributes whose values uniquely identify a tuple. A **candidate key** is a **minimal** super key, with no unnecessary attributes. The **primary key** is the candidate key chosen as the main identifier; the others are **alternate keys**. A **foreign key** is a set of attributes in one relation that refers to the primary key of another. **Integrity constraints** keep data valid: **domain constraints** (type, range, NOT NULL), **entity integrity** (primary key values are unique and never null) and **referential integrity** (foreign key values must match an existing key or be null), with actions such as `CASCADE`, `SET NULL` or `RESTRICT` on deletes and updates.

## Keys within keys

Every candidate key is a super key; one candidate key is chosen as the primary key.

![Three nested ovals: the outermost broad, a middle one holding two small key icons, and an inner one holding a single highlighted key icon.](assets/figures/database-fundamentals/section-3-map.svg) — Figure 3.1 — Super keys, candidate keys and the primary key.

## Finding the keys of a relation

STUDENT(roll_no, email, aadhaar, name, dept)

```text
assume roll_no, email and aadhaar are each unique; name and dept are not

super keys (examples):   {roll_no}, {email}, {aadhaar}, {roll_no, name},
                         {email, dept}, {roll_no, email, aadhaar, name, dept}
candidate keys:          {roll_no}, {email}, {aadhaar}      (minimal)
primary key (chosen):    {roll_no}
alternate keys:          {email}, {aadhaar}
foreign key elsewhere:   ENROLMENT.roll_no REFERENCES STUDENT.roll_no
```

## Minimal means no attribute can be removed

{roll_no, name} is a super key but not a candidate key, because removing name still leaves a unique identifier. Exam questions often test this distinction.

**Quiz:** Which statement about candidate keys is true?

- [x] A candidate key is a minimal super key
- [ ] A relation can have only one candidate key
- [ ] Candidate keys may contain null values freely
- [ ] A foreign key is always a candidate key of its own relation

*Answer:* A candidate key is a minimal super key. Candidate keys are super keys with no redundant attributes; a relation may have several.