Functional Dependencies

Chapter 7 · a functional dependency (FD) describes a relationship between attributes of a relation: the value of one attribute (or set of attributes) uniquely determines the value of another attribute. Everything in normalization is built on FDs.

Formal definition

X → Y

This means: if two rows have the same value of X, then they must also have the same value of Y.

X is called the determinant attribute
Y is called the dependent attribute

However, this dependency does not mean Y → X. Functional dependency is one-directional.

Example

Student_ID	Student_Name	Program
101	Rahim	CSE
102	Karim	EEE
103	Rahim	CSE

Holds:
Student_ID → Student_Name
because each Student_ID determines exactly one Student_Name.

Student_ID → Program
because each Student_ID determines exactly one Program.

So we can write: Student_ID → Student_Name, Program

Does NOT hold:
Student_Name → Student_ID

This may not be true, because two students can have the same name — look at rows 101 and 103: both are called Rahim but they are different students.

Practice — find the FDs

FD1smallFor the given table, what are the functional dependencies?
Instructor(instructor_id, name, dept, salary)

Possible functional dependencies are:

instructor_id → name
instructor_id → dept
instructor_id → salary

Therefore,

instructor_id → name, dept, salary

Because one instructor_id identifies one instructor and determines that instructor’s name, department and salary.

Any other determinant attribute in this table? Only if a natural attribute happens to be unique — e.g. if every instructor had a unique email, then email → instructor_id, name, dept, salary as well, giving a second candidate key. dept → salary does not hold, because many instructors in a department earn different salaries.

Why functional dependency is important

Functional dependency is used in database normalization. It helps to:

remove data redundancy;
avoid update, insert and delete anomalies;
identify candidate keys;
decompose tables into better designs.

Vocabulary you will need in the next pages

Term	Meaning
Determinant	the left-hand side X of X → Y
Super key	any attribute set that determines every attribute of the relation
Candidate key	a minimal super key
Prime attribute	an attribute that is part of any candidate key
Non-prime attribute	an attribute that belongs to no candidate key
Partial dependency	a non-key attribute depends on only part of a composite key → breaks 2NF
Transitive dependency	a non-key attribute depends on another non-key attribute → breaks 3NF

Next: Why normalize? →