LECTURE OUTLINE:

NORMALIZATION
▪ Objectives
▪ Functional Dependency
▪ Inference Rules
▪ First Normal Form
▪ Full Functional Dependency
▪ Second Normal Form
▪ Transitive Dependency
▪ Third Normal Form
▪ Boyce-Codd Normal Form
1
 ▪Normalization is a database design technique that begins
by examining the relationships (called functional
Normalization dependencies) between attributes.
▪Normalization uses a series of tests (described as normal
forms) to help identify the optimal grouping for attributes
to ultimately identify a set of suitable relations that
supports the data requirements of the enterprise.
▪Normalization: A technique for producing a set of
relations with desirable properties, given the data
requirements of an enterprise.

2
 ▪The purpose of normalization is to identify a suitable set
of relations that support the data requirements of an
enterprise.
Purpose of
Normalization ▪The characteristics of a suitable set of relations include the
… following:
▪ The minimal number of attributes necessary to support the
data requirements of the enterprise;
▪ Attributes with a close logical relationship (described as
functional dependency) are found in the same relation;
▪ Minimal redundancy, with each attribute represented only
once, with the important exception of attributes that form all or
part of foreign keys, which are essential for the joining of
related relations.

3
 ▪Database normalization is the process of organizing the fields
and tables of a relational database to minimize redundancy.

Normalization ▪Normalization usually involves dividing large tables into

smaller (and less redundant) tables and defining relationships
(In Simple between them.
Words) ▪The objective is to separate data so that additions, deletions,
and modifications of a field can be made in just one table and
then propagated through the rest of the database using the
defined relationships.
▪Normalized design makes the maintenance of database easier.

4
 ▪A major aim of relational database design is to group
attributes into relations to minimize data redundancy.
Data ▪Potential benefits for the implemented database include
Redundancy and the following:
Update ▪ Updates to the data stored in the database are achieved with a
Anomalies minimal number of operations, thus reducing the opportunities
for data inconsistencies occurring in the database;
▪ Reduction in the file storage space required by the base relations
thus minimizing costs.

5
 ▪ Relational databases also rely on the existence of a certain amount
of data redundancy.

Data ▪ This redundancy is in the form of copies of primary keys (or

Redundancy and candidate keys) acting as foreign keys in related relations to enable
the modeling of relationships between data.
Update
Anomalies
…
▪Problems associated with unwanted data redundancy

6
 ▪The StaffBranch relation is an alternative format of the Staff
and Branch relations.

Data
Redundancy and
Update
Anomalies ▪In the StaffBranch relation there is redundant data; the details
… of a branch are repeated for every member of staff located at
that branch.
▪In contrast, the branch details appear only once for each branch
in the Branch relation, and only the branch number (branchNo)
is repeated in the Staff relation to represent where each
member of staff is located.

7
 Data
Redundancy and
Update
Anomalies
…

8
 ▪Relations that have redundant data may have
problems called update anomalies.
Data
Redundancy and ▪Anomaly: A state of database that can lead
Update to redundancy, inconsistency or
Anomalies incorrectness.
…
▪Update Anomalies are classified as:
▪Insertion Anomalies
▪Deletion Anomalies
▪Modification anomalies
9
Redundancy
Example

10
 ▪There are two main types of insertion anomaly.
▪To insert the details of new members of staff into the
Insertion StaffBranch relation, we must include the details of the
branch at which the staff are to be located.
Anomalies
▪To insert details of a new branch that currently has no
members of staff into the StaffBranch relation, it is
necessary to enter NULLs into the attributes for staff, such
as staffNo.
▪ However, as staffNo is the primary key for the StaffBranch
relation, attempting to enter nulls for staffNo violates entity
integrity, is not allowed.

11
 ▪ Suppose for a new admission, we have a Student id(SID-105), name
and address of a student but if student has not registered for any
subjects yet then we have to insert NULL there, leading to Insertion
Insertion anomaly.

Anomalies
…

12
 ▪If we delete a tuple from the StaffBranch relation that
represents the last member of staff located at a branch, the
details about that branch are also lost from the database.
Deletion
▪For example, if we delete the tuple for staff number SA9
Anomalies
(Mary Howe) from the StaffBranch relation, the details
relating to branch number B007 are lost from the
database.
▪If we delete the tuple for staff number SA9 from the Staff
relation, the details on branch number B007 remain
unaffected in the Branch relation.

13
 ▪If (SID-105) has only one subject and temporarily he
drops it, when we delete that row, entire student record
will be deleted along with it.
Deletion
Anomalies
…

14
 ▪If we want to change the value of one of the attributes of a
particular branch in the StaffBranch relation
▪For example, the address for branch number B003—we
Modification/U
must update the tuples of all staff located at that branch.
pdation
Anomalies ▪If this modification is not carried out on all the appropriate
tuples of the StaffBranch relation, the database will
become inconsistent.
▪This demonstrates that although the StaffBranch relation
is subject to update anomalies, we can avoid these
anomalies by decomposing the original relation into the
Staff and Branch relations.

15
 ▪To update address of a student who occurs twice or more
than twice in a table, we will have to update Address
column in all the rows, else data will become inconsistent.
Modification/U
pdation
Anomalies
…

16
 ▪There are two important properties associated with
decomposition of a larger relation into smaller relations:

Modification
Anomalies ▪Lossless-join: The lossless-join property ensures that any
… instance of the original relation can be identified from
corresponding instances in the smaller relations.
▪Dependency Preservation: The dependency preservation
property ensures that a constraint on the original relation
can be maintained by simply enforcing some constraint on
each of the smaller relations.

17
 ▪Functional dependency: Describes the relationship
between attributes in a relation.
▪For example, if A and B are attributes of relation R, B is
Functional
functionally dependent on A (denoted A ® B), if each
Dependencies value of A is associated with exactly one value of B. (A
and B may each consist of one or more attributes.)

18
 ▪When a functional dependency is present, the dependency is
specified as a constraint between the attributes.
▪“A functionally determines B.”
Functional ▪Determinant: Refers to the attribute, or group of attributes, on
Dependencies the left-hand side of the arrow of a functional dependency.
… ▪Consider a relation with attributes A and B, where attribute B
is functionally dependent on attribute A. If we know the value
of A and we examine the relation that holds this dependency,
we find only one value of B in all the tuples that have a given
value of A, at any moment in time. Thus, when two tuples
have the same value of A, they also have the same value of B.
However, for a given value of B, there may be several different
values of A.

19
 ▪Consider the attributes staffNo and position of the Staff
relation.
▪A member of staff holds one position; however, there may
Example of be several members of staff with the same position.
Functional
Dependency ▪The relationship between staffNo and position is
one-to-one (1:1).
▪The relationship between position and staffNo is
one-to-many (1:*)
▪In this example, staffNo is the determinant of this
functional dependency.

20
 ▪ When identifying functional dependencies between attributes in a
relation, it is important to distinguish clearly between the values
held by an attribute at a given point in time and the set of all
possible values that an attribute may hold at different times.
Example of
Functional
Dependency
…

21
 ▪ Consider the values shown in staffNo and sName attributes of the Staff
relation.
▪ Can we therefore conclude that the staffNo attribute functionally
determines the sName attribute and/or that the sName attribute
functionally determines the staffNo attribute?
Example of a
▪ If the values shown in the Staff relation represent the set of all possible
functional values for staffNo and sName attributes, then the following functional
dependency dependencies hold:
▪ staffNo ® sName
that holds for ▪ sName ® staffNo
all time ▪ However, if the values shown in the Staff relation simply represent a set
of values for staffNo and sName attributes at a given moment in time,
then?
▪ One approach to identifying the set of all possible values for attributes in
a relation is to more clearly understand the purpose of each attribute in
that relation.
▪ staffNo ® sName
22
 ▪ Determinants should have the minimal number of attributes
necessary to maintain the functional dependency with the
attribute(s) on the righthand side.
▪ Full Functional Dependency: Indicates that if A and B are
Full attributes of a relation, B is fully functionally dependent on A if B is
Functional functionally dependent on A, but not on any proper subset of A.
Dependency ▪ A functional dependency A ® B is a full functional dependency if
removal of any attribute from A results in the dependency no longer
existing.
▪ A functional dependency A ® B is a partial dependency if there is
some attribute that can be removed from A and yet the dependency
still holds.

23
 ▪ staffNo, sName ® branchNo
▪It is correct to say that each value of (staffNo, sName) is
Example of associated with a single value of branchNo.
Full ▪However, it is not a full functional dependency, because
Functional branchNo is also functionally dependent on a subset of
(staffNo, sName), namely staffNo. (partial dependency)
Dependency
▪ staffNo ® branchNo (Full functional dependency)

24
 ▪The functional dependencies that we use in normalization
have the following characteristics:
▪ There is a one-to-one relationship between the attribute(s) on the
Characteristics left-hand side (determinant) and those on the right-hand side of a
of Full functional dependency. (Note that the relationship in the opposite
Functional direction—that is, from the righthand to the left-hand side
Dependency attributes—can be a one-to-one relationship or one-to- many
relationship.)
▪ They hold for all time.
▪ The determinant has the minimal number of attributes necessary to
maintain the dependency with the attribute(s) on the right-hand side.
In other words, there must be a full functional dependency between
the attribute(s) on the left-hand and right-hand sides of the
dependency.
25
 ▪A condition where A, B, and C are attributes of a relation
such that if A ® B and B ® C, then C is transitively
dependent on A via B (provided that A is not functionally
dependent on B or C).
Transitive ▪ Consider the following functional dependencies within the StaffBranch
Dependency relation:
▪ staffNo ® sName, position, salary, branchNo, bAddress
▪ branchNo ® bAddress

▪ The transitive dependency branchNo ® bAddress exists on staffNo via

branchNo.
▪ In other words, the staffNo attribute functionally determines the bAddress
via the branchNo attribute and neither branchNo nor bAddress
functionally determines staffNo.

26
 ▪ Identifying all functional dependencies between a set of attributes
should be quite simple if the meaning of each attribute and the
relationships between the attributes are well understood.
▪ Identifying a set of functional dependencies for the StaffBranch
Identifying relation
▪ staffNo ® sName, position, salary, branchNo, bAddress
Functional ▪ branchNo ® bAddress
Dependencies ▪ bAddress ® branchNo
▪ branchNo, position ® salary
▪ bAddress, position ® salary

▪All the attributes on the right-hand side are functionally

dependent on the determinant on the left-hand side.

27
 ▪ The main purpose of identifying a set of functional dependencies for a
relation is to specify the set of integrity constraints that must hold on a
relation.
Identifying the ▪ An important integrity constraint to consider first is the identification of candidate keys,
Primary Key one of which is selected to be the primary key for the relation.

for a Relation ▪ Identifying the primary key for the StaffBranch relation
Using ▪ The identification of five functional dependencies for the StaffBranch relation

Functional ▪ The determinants for these functional dependencies are staffNo, branchNo,
bAddress, (branchNo, position), and (bAddress, position).
Dependencies ▪ To identify the candidate key(s) for the StaffBranch relation, we must identify the
attribute (or group of attributes) that uniquely identifies each tuple in this relation.
▪ All attributes that are not part of the primary key (non-primary-key attributes) should
be functionally dependent on the key.
▪ The only candidate key of the StaffBranch relation, and therefore the primary key, is
staffNo, as all other attributes of the relation are functionally dependent on staffNo.

28
 ▪Different forms or levels of normalization.
▪Called first, second, third, fourth, fifth forms .
▪Each form has got certain conditions.
Process of
Normalization ▪If a table fulfils the condition(s) for a normal form then
the table is in that normal form.

29
 ▪A relation is in first normal form if every attribute in
every row can contain only one single (atomic) value.

1NF ▪A university uses the following relation:

First Normal
Form Student(Surname, Name, Skills)

The attribute Skills can contain multiple values and

therefore the relation is not in the first normal form.

But the attributes Name and Surname are atomic attributes

that can contain only one value.
30
 1NF
First Normal
Form
…

31
 ▪A database is in second normal form if it satisfies the
following conditions:

2NF ▪It is in first normal form

Second ▪All non-key attributes are fully functional
Normal dependent on the primary key.
Form

32
 2NF
Second
Normal
Form
…

33
 ▪It is in second normal form.
▪There is no transitive functional dependency (A is
functionally dependent on B, and B is functionally
3NF dependent on C. In this case, C is transitively dependent
Third on A via B).
Normal
Form

34
 3NF
Third
Normal
Form
…

35
 ▪Three most commonly used normal forms: 1NF, 2NF, and
3NF.

More on Normal ▪However, R. Boyce and E.F. Codd identified a weakness

Forms with 3NF and introduced a stronger definition of 3NF,
Boyce-Codd Normal Form (BCNF) (Codd, 1974).

▪Higher normal forms that go beyond BCNF were

CHAPTER-15 introduced later, such as Fourth (4NF) and Normal
Forms (Fagin, 1977, 1979). Fifth (5NF)
▪ However, these later normal forms deal with situations that are
very rare.

36
 ▪ A relation is in BCNF if and only if every determinant is a
candidate key.
▪ Identify all the determinants and make sure that they are candidate
Boyce-Codd keys.
Normal Form ▪ Recall that a determinant is an attribute, or a group of attributes, on
(BCNF) which some other attribute is fully functionally dependent.
▪ Every relation in BCNF is also in 3NF.
▪ However, a relation in 3NF is not necessarily in BCNF.

37
Boyce-Codd
Normal Form
(BCNF)
…

38
 ▪ Functional dependencies fdl, fd2, and fd3 are all candidate keys for
this relation.
▪ The only functional dependency that requires discussion is (staffNo,
Boyce-Codd interviewDate) roomNo (represented as fd4).
Normal Form ▪ Even though (staffNo, interviewDate) is not a candidate key for the
(BCNF) Clientlnterview relation this functional dependency is allowed in
… 3NF because roomNo is a primary-key attribute being part of the
candidate key (roomNo, interviewDate, interviewTime).
▪ As there are no partial or transitive dependencies on the primary key
(clientNo, interviewDate), and functional dependency fd4 is
allowed, the Clientlnterview relation is in 3NF.
▪ However, this relation is not in BCNF (a stronger normal form of
3NF) due to the presence of the (staffNo, interviewDate)
determinant, which is not a candidate key for the relation.
39
 ▪ To transform the Clientlnterview relation to BCNF, we must
remove the violating functional dependency by creating two new
relations called Interview and StaffRoom,
Boyce-Codd
Normal Form
(BCNF)
…

40
 ▪One of the main concepts associated with normalization is
functional dependency, which describes the relationship
between attributes.
Inference Rules
for Functional ▪Functional dependencies A B and B C in a relation
Dependencies implies that the functional dependency A C also holds
in that relation. A C is an example of a transitive
functional dependency.

41
 ▪ A set of inference rules, called Armstrong’s axioms, specifies how new
functional dependencies can be inferred from given ones.
▪ Let A, B, and C be subsets of the attributes of the relation R. Armstrong’s
axioms are as follows:
Inference Rules
for Functional (1) Reflexivity: If B is a subset of A, then A B
Dependencies (2) Augmentation: If A B, then A,C B,C
… (3) Transitivity: If A B and B C, then A C
▪ Let D be another subset of the attributes of relation R.
(4) Self-determination: A A
(5) Decomposition: If A B,C, then A B and A C
(6) Union: If A B and A C, then A B,C
(7) Composition: If A B and C D then A,C B,D

42
 ▪Rule 1 (Reflexivity) and Rule 4 (Self-determination) state
that a set of attributes always determines any of its subsets
or itself.
Inference Rules ▪ If B is a subset of A, then A B
for Functional ▪A A
Dependencies
… ▪Rule 2 (Augmentation) states that adding the same set of
attributes to both the left-hand and right-hand sides of a
dependency results in another valid dependency.
▪ If A B, then A,C B,C

▪Rule 3 (Transitivity) states that functional dependencies

are transitive.
▪ If A B and B C, then A C
43
 ▪Rule 5 (Decomposition) states that we can remove attributes
from the right-hand side of a dependency.
▪ If A B,C, then A B and A C
Inference Rules ▪ Applying this rule repeatedly, we can decompose A B, C, D
functional dependency into the set of dependencies A B, A C, and
for Functional A D.
Dependencies
… ▪Rule 6 (Union) states that we can do the opposite:
▪ If A B and A C, then A B,C
▪ we can combine a set of dependencies A B, A C, and A D into a
single functional dependency A B, C, D.

▪Rule 7 (Composition) is more general than Rule 6 and states

that we can combine a set of nonoverlapping dependencies to
form another valid dependency.
▪ If A B and C D then A,C B,D

44
THANK YOU
Q&A