Keys and functional dependencies

Normal forms are defined with a handful of terms. Learn these and the rules become short.

Relations, tuples and attributes

• A relation is a table (Student).
• A tuple is a row.
• An attribute is a column (StudentID, StudentName, Department).
• A domain is the set of allowed values for an attribute.

In theory a relation is a set of tuples, so it can't hold duplicate rows. A SQL table can, unless a key or unique constraint stops it.

Superkeys, candidate keys and primary keys

Take Student(StudentID, Email, StudentName), where every student has a unique ID and a unique email.

• A superkey is any set of columns that identifies each row: {StudentID}, {Email}, {StudentID, StudentName}.
• A candidate key is a minimal superkey: drop any column and it stops being unique. {StudentID} and {Email} are candidate keys. {StudentID, StudentName} isn't, because StudentID alone is enough.
• The primary key is the candidate key you pick as the main identifier. The others are alternate keys.

An attribute is prime if it's part of at least one candidate key, and non-prime otherwise. Here StudentID and Email are prime; StudentName is non-prime. The formal 2NF and 3NF rules depend on this distinction.