NORMALIZATION

Normalization is the process of splitting relations into well-structured relations that allow users
to insert, delete, and update tuples without introducing database inconsistencies. The purpose of
Normalization is to eliminate redundant (useless) data and ensure data is stored logically.

The process of normalization was first developed by E.F codd (1972b). Normalization is often
performed as a series of tests on a relation to determine whether it satisfies or violates the
requirements of a given normal form. Three normal forms were initially proposed called first
(1NF), Second (2NF) and third (3NF) normal forms.

Subsequently, R. Boyce and E.F. codd introduced a stronger definition of third normal form
called Boyce-Codd normal form (BCNF) (codd,1974). All these normal forms are based on
functional dependencies among attributes of a relation (maier, 1983). Higher normal forms that
go beyond BCNF were introduced later such as fourth (4NF) and fifth (5NF0 normal forms
(Fagin, 1977,1979). However this later forms deal with situations that are very rare.

DATA REDUNDANCY AND UPDATE ANOMALIES

Database normalization is a series of steps followed to obtain a database design that allows for
consistent storage and efficient access of data in a relational database. These steps reduce data
redundancy and the risk of data becoming inconsistent.

A major aim of relational database design is to group attributes into relations to minimize
redundancy and thereby reduce the file storage space required by the implemented base relations.
Redundancy in relation may cause insertion, deletion and update [Link] problems
associated with data redundancy are illustrated by comparing the following relations. Staff and
Branch with branch relations

Staff

Staff no Name Position Salary Branch no

SL21 John white Manager 30000 B005

SG37 Ann beech Assistants 12000 B003

Notes Prepared by Peninah J. Limo Page 1

 SG14 David ford Supervisor 18000 B003

SA9 Mary itowe Assistant 9000 B007

SG5 Susan brand Manager 24000 B003

SL41 Julie leo Assistant 9000 B005

Branch

BranchNo bAddress

B005 22 door road, London

B007 16 argyllst, abardeen
B003 163 main st, glesgow

StaffBranch

StaffNo sName Position Salary BranchNo bAddress

SL21 John white Manager 30000 B005 22 Deer rd, london

SG37 Ann beech Assistants 12000 B003 163 main st, glasglow

SG14 David ford Supervisor 18000 B003 163 main st, glasglow

SA9 Mary Howe Assistant 9000 B007 16 argyll st, Aberdeen

SG5 Susan brand Manager 24000 B003 163 main st, Glasgow

SL41 Julie leo Assistant 9000 B005 22 deer rd, london

Staff branch relation is an alternative format of the staff and branch relation above

Notes Prepared by Peninah J. Limo Page 2

Staff (StaffNo, Sname, position, Salary, branchNo)

Branch (branchNo, baddress)

StaffBranch (staffNo, sName, position, salary, branchNo, baddress)

The schema of the above relation are shown with the primary key for each relation underlined.

In the staff Branch relation these is redundant data; the details of a branch are repeated for every
member of staff located at that branch. In constrast, 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. Relations that have redundant data
may have problems called update anomalies, which are classified as:

 Insertion anomalies
 Deletion anomalies
 Modification anomalies

1. INSERTION ANOMALIES

Insert anomalies occur when certain attributes cannot be inserted into the database due to
missing additional data. For example, if a new employee is not assigned a department, their data
cannot be inserted into the table if the department field does not allow null values. For example
Lets illustrate using the previous staffBranch relation.

a. To insert the details of new members of staff into the staff Branch relation, we must
include the details of the branch at which the staff are to be located. For example, to
insert, the details of new staff located branch number B007, we must enter the correct
details of branch number B007 so that the branch details are consistent with values for
branch B007 in other tuples of the staffBranch relation.
b. To insert details of 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 the staffNo violates entity integrity and is not allowed. We therefore

Notes Prepared by Peninah J. Limo Page 3

 cannot enter a tuple for a new branch into the staffBranch relation with a null for the
staffNo.

2. DELETION ANOMALIES

Deletion anomalies occurs when deleting one part of the data deletes the other necessary
information from the database. For example If we delete a tuple from the StaffBranch
relation that represents the last member of the staff located at a branch, the details about that
branch are also lost from the database. Consider if we delete the tuple for staff number SA9
(mary Howe) from the staffBranch relation, the details relating to branch number B007 are
lost from the database.

Another example of deletion anomaly is If we delete the tuple for the staff number SA9 from
the Staff relation, the details on branch number B007 are lost from the database.

3. MODIFICATION ANOMALIES

modification anomalies: occurs when the same data items are repeated with the same values
and are not linked to each other. For example consider relations above 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 must update the tuples of all staff located at that branch. If
this modification is not carried on all the appropriate tuples of the staffBranch relation, the
database will become inconsistent. In this example, branch number B003 may appear to have
different addresses in the different staff tuples.

FUNCTIONAL DEPENDENCIES
One of the main concepts associated with normalization is functional dependency. The
functional dependency describes a relationship that exists between two attributes. Functional
Dependency is represented by → (arrow sign).To understand this concept ,Let us assume X is a
relation with attributes A and B . Then the following function dependency between attributes can
be represent by :
A→B

Notes Prepared by Peninah J. Limo Page 4

attributes 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. The dependency between attributes A and B can be
represented diagramatically as shown below:

When a functional dependency exists, the attribute or group of attributes on the left- hand side of
the arrow is called the determinant. The consequent of a functional dependency is the attribute or
group of attributes on the right-hand side of the arrow. In the figure above, A is the determinant
of B and B is the consequent of A.
Identifying A Functional Dependency
Example
Consider the attributes staff No and position of the previous staff relation. For a specific staff No,
we can determine the position of that member of staff as manager. In other words, the position
attribute is functionally dependent on staff No

Staff numberSL21 Manager

The opposite is not true as staffNo is not true as StaffNo is not functionally dependent on
position. A member of staff holds one position, however, there may be several members of staff
with the same position.

Notes Prepared by Peninah J. Limo Page 5

 Staff number SL21
Manager
Staff Number SG5

The relationship between staffNo and Position is one-to-one (1:1): for each staff number there is
only one position. On the other hand, the relationship between position and staffNo is one-to-
many(1:*): there are several staff numbers associated with a given position. In this example,
staffNo is the determinant of this functional dependency. For the purposes of normalization we
are interested in identifying dependencies between attributes of a relation that have a one-to-one
relationship.

In summary, the main characteristics of functional dependencies that we use in normalization:

 Have a one-to-one relationship between attributes(s) on the left and right-hand of a

dependency;
 Hold for all time;
 Are non-trivial

TYPES OF FUNCTIONAL DEPENDENCY

There are mainly four types of Functional Dependency in DBMS. Following are the types of
Functional Dependencies in DBMS:

 Non-trivial Functional dependency

 Trivial Functional dependency
 Transitive Dependency
 Multivalued dependency

1. Non-trivial functional dependency

Non-trivial functional dependency is a dependency that have one to one(1:1) relationship that
hold all the time.
Example

Notes Prepared by Peninah J. Limo Page 6

Consider the values shown in StaffNo and sName attributes of the Staff relation. We see that for
a specific staffNo, for example SL21, we can determine the name of that member of staff as John
White. Furthermore it appears that for a specific sName, for example, John White, we can
determine the staff number for that member of staff as SL21. For StaffNo and Sname attributes
the following functional dependencies hold:

StaffNo sName

For example, the purpose of the values held in the staffNo attribute is to uniquely identify each
members of staff. Clearly, the statement that if we know the staff number (StaffNo) of a member
of staff we can determine the name of the member of staff (sName) remains [Link] relationship
between StaffNo and sName is one-to-one (1:1) for each staff number there is only is one name.

2. Trivial Functional dependencies

A dependency is trivial if it is impossible for it not be satisfied. Furthermore, dependency is
trivial, if and only if, the right-hand side is a subset (not necessarily a proper subset) of the left-
hand side (determinant).
Example

StaffNo, sName sName

StaffNo, sName StaffNo

{StaffNo, sName} -> Sname is a trivial functional dependency as sName is a subset of

{StaffNo, sName }.

{ StaffNo, sName } -> StaffNo is a trivial functional dependency as StaffNo is a subset of

{StaffNo, sName }.

Non Trivial Functional Dependency can be categorized into:

 Complete Non Trivial Functional Dependency – A Functional Dependency is completely

nontrivial if none of the RHS attributes are part of the LHS attributes.

Notes Prepared by Peninah J. Limo Page 7

 Semi Non-Trivial Functional Dependencies – A Functional Dependency is semi non-trivial if
one of the RHS attributes are not part of the LHS attributes.

3. Transitive Dependency

A functional dependency is said to be transitive if it is indirectly formed by two functional

dependencies.

If P -> Q and Q -> R is true, then P-> R is a transitive dependency.

4. Multivalued Dependency

Multivalued Functional Dependency takes place in the conditions when there is more than one
independent attribute with multiple values in the same relation.

The Multivalued Dependency case is a complete limitation between two sets of attributes in the
relationship of Functional Dependency. It requires that certain row values can be present as a
functional dependency connection. This can be represented as,

X→Y
X→Z
X → A,

Where X, Y, Z, A are attributes of the same relation, X being the primary key and Y, Z, A is
non- key attributes. Here Y, Z, A are functionally dependent on X, and not dependent on each
other.

Properties of Functional Dependency(Armstrong’s Axioms)

William Armstrong in 1974 suggested a few rules related to functional dependency. They are
called RAT rules.

1. Reflexivity: If A is a set of attributes and B is a subset of A, then the functional

dependency A → B holds true.

o For example, { Employee_Id, Name } → Name is valid.

Notes Prepared by Peninah J. Limo Page 8

 2. Augmentation: If a functional dependency A → B holds true, then appending any
number of the attribute to both sides of dependency doesn't affect the dependency. It
remains true.

o For example, X → Y holds true then, ZX → ZY also holds true.

o For example, if { Employee_Id, Name } → { Name } holds true
then, { Employee_Id, Name, Age } → { Name, Age }

3. Transitivity: If two functional dependencies X → Y and Y → Z hold true, then X →

Z also holds true by the rule of Transitivity.

o For example, if { Employee_Id } → { Name } holds true and { Name } →

{ Department } holds true, then { Employee_Id } → { Department } also holds
true.

Assignment:
Write short notes on Minimal set of functional dependency

PROCESS OF NORMALIZATION

Normalization is a formal technique for analyzing relations based on their primary key
(candidate keys) and functional dependencies. The technique involves a series of rules that can
be used to test individual relations so that a database can be normalized to any degree. When a
requirements is not met, the relation violating the requirements must be decomposed into relation
that individually meet the requirements of normalization.

Normalization is often executed as a series of steps. Each step corresponds to a specific normal
form that has known properties. As normalization proceeds, the relations becomes progressively
more restricted (stronger) in format, and also less vulnerable to update anomalies. For relational
model, it is important to recognize that it is only first normal form (INF) that is critical in
creating relations. All subsequent normal forms are optional. However, to avoid the update
anomalies it is recommended normally to normalize to 3NF.

Notes Prepared by Peninah J. Limo Page 9

UN NORMALIZES FORM (UNF OR 0 NF)

A table that contains one or more repeating groups

FIRST NORMAL FORM (INF)

A relation in which the intersection of each row or column contains one and only one value.

To transform the unnormalized table to first normal form (INF), we identify and remove
repeating groups within the table. A repeating group is an attribute, or group attributes, within a
table that occurs with multiple values for a single occurrence of the nominated key attributes for
that table. Note that in this context, the term’ key’ refers to the attributes that uniquely identify
each row within the unnormalized table. There are two common approaches to removing
repeating groups from unnormalized tables:

a) In the first approach, we remove the repeating groups by entering appropriate data in the
empty columns of rows containing the repeating data. In other words we fill in the blanks by
duplicating the non repeating data where required. This approach is commonly referred to as
‘flattering’ the table. The resulting table, now referred to as a relation, contains atomic (or
single) values at the intersection of each row and column, and is therefore in first normal
form. With this approach, redundancy is introduced into the resulting relation, which is
subsequently removed during the normalization process.
b) In the second approach, we remove the repeating group by placing the repeating data, along
with a copy of hung key attributes, in a separate relation. A primary key is identified for the
new relation. Sometimes the unnormalized table may contain more than one repeating group,
or presents groups with repeating groups. In such cases, this approach is applied until no
repeating groups remain. A set of relations is in 1NF if they contain no repeating groups.

SECOND NORMAL FORM 2NF

A relation is said to be in 2NF if it is already in 1NF and every non-primary key attribute is fully
functionally dependent on the primary key.

Process Of Converting 1NF TO 2NF

Notes Prepared by Peninah J. Limo Page 10

  Identify primary key for the 1NF relation.
 Identify functional dependencies in the relation.
 If partial dependencies exist on the primary key remove them by placing them in a new
relation along with copy of their determinant.

THIRD NORMAL FORM

A relation is said to be in 3NF, if it is clearly in 2NF there exists no transitive dependency in that
relation.

Transitive dependency is a concept based on the following examples if A, B and C are attributes
of a relation such that if A B (B depends on A) and B C (C depend on B) then C is
transitively dependent on A through B

Process Of Converting 2NF TO 3NF

 Identify the primary key in the 2NF relation.

 Identify functional dependencies in the relation.
 If transitive dependencies exist on the primary key remove them by placing them in a
new relation closing with copy of their determinant.

NORMALISATION EXAMPLE

Mosspark Community College Classlist

Notes Prepared by Peninah J. Limo Page 11

This represents a college's record of all the courses it offers - one document for each course.
Students may take several courses, and tutors may be in charge of more than one course.

You will notice that certain data repeats more than once (student no, student name, date of birth,
gender and last attendance date). This is therefore a complex entity, since different courses will
have different numbers of students. The structure needs to be converted into its simple form.

Un-Normalised Form (UNF)

 list the attributes of the entity

 identify the main key (course code) - this must be unique
 identify the repeating group of attributes
 identify the key from the repeating group

Convert to First Normal Form (1NF)

 Remove the repeating group of attributes to form a new entity

 Add to it the original key

Notes Prepared by Peninah J. Limo Page 12

Convert to Second Normal Form (2NF)

 Examine tables with a composite key (a key made up of two parts)

 For each non-key attribute, determine if its key is the first part, or the second part, or, if
neither, then the answer is both parts
 Remove the partial key and its dependants to form new table

Note that in our example student name, date of birth and gender are referenced by student no,
while last attendance date is referenced by course code and student number (because it is the date
for that student on that particular course).

Notes Prepared by Peninah J. Limo Page 13

Convert to Third Normal Form (3NF)

 Identify any dependencies between non-key attributes in each table

 Remove them to form a new table
 Promote one of the attributes to be the key of the new table
 This becomes the foreign key link in the original table (shown with a *).

Notes Prepared by Peninah J. Limo Page 14

Advantages of Normalization

1. More efficient data structure.

2. Avoid redundant fields or columns.
3. More flexible data structure i.e. we should be able to add new rows and data values easily
4. Better understanding of data.
5. Ensures that distinct tables exist when necessary.
6. Easier to maintain data structure i.e. it is easy to perform operations and complex queries
can be easily handled.
7. Minimizes data duplication.
8. Close modeling of real world entities, processes and their relationships.

Disadvantages of Normalization

1. You cannot start building the database before you know what the user needs.
2. On Normalizing the relations to higher normal forms i.e. 4NF, 5NF the performance
degrades.
3. It is very time consuming and difficult process in normalizing relations of higher degree.

Notes Prepared by Peninah J. Limo Page 15

4. Careless decomposition may leads to bad design of database which may leads to serious
problems.

Notes Prepared by Peninah J. Limo Page 16