Primary Key

Used to uniquely identify each tuple (row) in a table. Example: a student's roll number or registration number is a typical primary key for a student table.

Candidate Key

The minimal set of attributes that can uniquely identify a record. A table can have multiple candidate keys; one of them is chosen as the primary key.

Candidate key properties :

1. Must not contain NULL values (for identification).
2. Values must be unique.
3. May consist of multiple attributes (composite).
4. Should be the minimal set of attributes necessary for uniqueness.

Super Key

A set of one or more attributes that can uniquely identify a tuple. A super key may include extra attributes that aren't necessary for uniqueness (so every candidate key is a super key, but not every super key is a candidate key).

Composite Key

A key composed of two or more attributes that together uniquely identify a row. Note: a composite key can be a candidate key or a primary key when minimum attributes together form uniqueness.

Foreign Key

Used to define a relationship between tables. A foreign key in a child table references the primary (or unique) key in a parent table, enforcing referential integrity.

Example — Department table:

Professor table:

Here Department_ID is the primary key for Department, and Professor_ID is primary for Professor.

If we add Department_ID to the Professor table as a column (for example Department_ID), we can create a foreign key relationship to link professors to their department. This helps answer questions like "which professor belongs to which department".

New (joined) table example: