Relations, Keys and Integrity Constraints

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.

Finding the keys of a relation

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

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.

Quick check: Which statement about candidate keys is true?

• 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