Fundamentals of Database Systems

Chapter Eight:Functional Dependency and Normalization

Ewnetu Endalamaw (MSC)

March 4, 2021

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 1 / 28

Contents

Functional dependency
Definition & purpose of normalization
Normalization types
Anomalies

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 2 / 28

Functional Dependency

Functional dependency and its representation

A functional dependency is a constraint between two sets of
attributes of entities in a database.

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 3 / 28

...Cont’d

The essence of functional dependency is that;

If we say for the existence of something, call it B, as primary
condition A must exist, and have a certain value, then we say that ”B
is functionally dependent on A”.
We also often express this idea by saying that:
I A determines B, or
I B is a function of A, or
I A functionally governs B.

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 4 / 28

Types of Functional Dependency

Partial Dependency:
If an attribute which is not a member of the key is dependent on
some part of the key (if we have composite/candidate key) then that
attribute is partially functionally dependent on the key.
Let {A,B} is the Key and C is no key attribute.
Then if {A,B} → C and [B → C or A → C] holds
We say→ C is partially functionally dependent on { A, B }
Note: Partial dependency occurs when a non-prime attribute is
functionally dependent on part of a candidate/composite key.

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 5 / 28

...Cont’d

Full Dependency:
An attribute is fully functional dependent on another attribute, if it is
functionally dependent on that attribute and not on any of its proper
subset.
For example, an attribute Q is fully functionally dependent on another
attribute P, if it is functionally dependent on P and not on any of the
proper subset of P.
Let {A,B} is the Key and C is no key attribute.
Then if {A,B} → C and [B→ C & A → C] doesn’t hold
I (i.e; if A can’t determine C and B can’t determine C), then C fully
functionally dependent on { A, B }

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 6 / 28

...Cont’d

Transitive Dependency :
I In mathematics and logic, a transitive relationship is a relationship of
the following form:
I ”If A → B, and if also B → C, then A → C.”
I Example: If Mr X >Y and Y >Z then X >Z.
Generalized way of describing transitive dependency is that:
If A functionally governs B, AND
If B functionally governs C THEN
A functionally governs C ,Provided that neither C nor B determines A

In the normal notation:

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 7 / 28

Definition of normalization and its purpose

What is Normalization?
Normalization: is a database design technique/concept that
eliminates undesirable characteristics like (Insertion, Update,
Redundancy and Deletion anomalies).
Normalization is the transformation of complex user views and data
stores to a set of smaller, stable, and easily maintainable data
structures.
Normalization divides larger tables into smaller tables and links them
using relationships.

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 8 / 28

Normalization

Purpose: the purpose of normalization in SQL is to eliminate

redundant (repetitive) data and ensure data is stored logically.
In relational databases, normalization is a process that helps to
I Eliminate redundancy,
I Organize data efficiently,
I Reduce the potential for anomalies during different operations on data,
and
I Improve data consistency.

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 9 / 28

..Cont’d

Normal Form Types

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 10 / 28

First Normal Form (1NF)

A relation (table) R is in 1NF if and only if (iff) all underlying domains

of attributes contain only atomic (simple or indivisible) values.
I i.e. The value of any attribute in a tuple (row) must be a single value
from the domain of their attribute.
It is defined to disallow multivalued attributes, composite attributes,
and their combinations.
In other words, 1NF disallows relations within relations or relations as
attribute values within tuples.
Remove repeating groups (values in a single field).
The primary key with repeating group attributes are moved into a
new table.
When a relation contains no repeating groups, it is in first normal
form(1NF).

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 11 / 28

..Cont’d

Example1(unnormalized)

Normalized(1st NF)

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 12 / 28

..cont’d

Example2

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 13 / 28

..cont’d

Example2(unnormalized)

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 14 / 28

...Cont’d

The above table is not in 1NF as the rule says “each attribute of a
table must have atomic (single) values”, the emp-mobile values for
employees Jon & Lester violates that rule.
To make the table complies with 1NF we should have the data like
this:
Example2(1NF)

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 15 / 28

Second Normal Form (2NF)

A relation schema R is in 2NF if it is in 1NF and every non-prime

attribute A in R is fully functionally dependent on the primary key.
(i.e. not partially dependent on candidate/primary key).
Remove any partially dependent attributes and place them in another
relation.
A partial dependency is when the data are dependent on a part of a
key.

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 16 / 28

...Cont’d

Example1: Consider a relation schemas for Employees and Teams in a

single relation as follows
Emp-Teams(EmpId, Name, BDate, Gender, TeamId, Project,
TeamName)
Then upon decomposition we will have
EmpId → {Name, BDate, Gender}
TeamId → {Project, TeamName}
Employees(EmpId, Name, BDate, Gender)
Teams(TeamId, Project, TeamName)
Emp-Teams(EmpId, TeamId)

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 17 / 28

...Cont’d

Example2:

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 18 / 28

Third Normal Form (3NF)

Defn.→A relation schema R is in 3NF if it satisfies 2NF and no

nonprime attribute of R is transitively dependent on the primary key.
Alternative defn.→ A relation schema R is in 3NF if every nonprime
attribute of R meets both of the following conditions:
It is fully functionally dependent on every key of R.
It is nontransitively dependent on every key of R.

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 19 / 28

...Cont’d

Example: Suppose a company wants to store the complete address of

each employee, and the company manager creates a table named
employee-details that looks like this:

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 20 / 28

...Cont’d

Analyzing the table

I Super keys: {{emp-id}, {emp-id, emp-name}, {emp-id, emp-name,
emp-zip}}....so on
I Candidate Keys: {emp-id}
I Non-prime attributes: all attributes except empid are non-prime as they
aren’t part of any candidate keys.
I Here, {emp-state, emp-city & emp-district} are dependent on emp-zip,
and emp-zip is dependent on emp-id that makes non-prime attributes
(emp-state, emp-city & emp-district) transitively dependent on key
(emp-id).
I This violates the rule of 3NF.
I To make this table complies with 3NF we have to break the table into
two tables to remove the transitive dependency.

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 21 / 28

...Cont’d

After normalization

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 22 / 28

...Cont’d

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 23 / 28

Normalization Summary

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 24 / 28

...Cont’d

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 25 / 28

Anomalies

Data redundancy
Insert anomaly
Deletion anomaly
Update anomaly

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 26 / 28

...Cont’d

Data Redundancy→ occures when the same data is stored in more

than one place in the database.
I Solved by creating tables that are in third normal form
Insert Anomaly→ occurs when the entire primary key is not known
and the database cannot insert a new record properly, which would
violate entity integrity.
I Can be avoided by using a sequence number for the primary key
Deletion Anomaly→happens when a record is deleted that results in
the loss of other related data.
I Can be avoided by normalizing tables in a database to minimize their
dependency.
Update Anomaly→occures when a change to one attribute value
causes the database to either contain inconsistent data or causes
multiple records to need changing.
I May be prevented by making sure tables are in third normal form

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 27 / 28

Thank you!!!

Ewnetu Endalamaw Fundamentals of Database March 4, 2021 28 / 28