NORMALIZATION

Data Modeling is primarily the result of a thorough understanding of the information about an
enterprise. The aim of a relational database design is to produce a set of relation schemas that
represent the real-world situation that is being modeled.

Normalization entails organizing the columns (attributes) and tables (relations) of a database to ensure
that their dependencies are properly enforced by database integrity constraints. It is accomplished
by applying some formal rules either by a process of synthesis (creating a new database design) or
decomposition (improving an existing database design)".
Normalization is the process of decomposing relations with anomalies to produce well-structured
relations.
Through these well-structured relations, your database becomes:
• More efficient (using less storage)
• More accurate (reducing inconsistencies)
• Easier to maintain (simplifying updates)
• More adaptable to change (accommodating new requirements)

WHY DO WE NORMALIZE?
• Minimizing redundancy: By removing redundant information, normalization helps us make sure
that each piece of data is stored only once. This reduces storage requirements and prevents
inconsistencies.
• Avoiding the database modification anomalies: Eliminating redundant(useless) data, therefore
handling data integrity, because if data is repeated it increases the chances of inconsistent data.
o Insertion anomaly: It may not be possible to store some information unless some other
information is stored as well.
o Deletion anomaly: It may not be possible to delete some information without losing some
other information as well.
o Update anomaly: If one copy of repeated data is updated, an inconsistency is created
unless all copies are similarly updated.
Let us illustrate the above situations with an example.
Set - A
EMP(EMPNO, NAME, DESG, SALARY, DEPTNO, DNAME)

EMPNO NAME DESG SALARY DEPTNO DEPTNAME

E01 KOUSHIK GHOSH MANAGER 25000 D01 ADMINISTRATION
E05 PINAKI BOSE PROGRAMMER 15000 D03 PROJECT
E06 GOUTAM DEY FINANCE OFFICER 10000 D02 PERSONNEL
E07 RAJIB HALDER SYSTEM ANALYST 20000 D03 PROJECT
EMP
Set - B
EMP(EMPNO, NAME, DESG, SALARY, DEPTNO)
DEPT(DEPTNO, DEPTNAME)

EMPNO NAME DESG SALARY DEPTNO

E01 KOUSHIK GHOSH PROJECT MANAGER 25000 D01
E05 PINAKI BOSE PROGRAMMER 15000 D03
E06 GOUTAM DEY FINANCE OFFICER 10000 D02
E07 RAJIB HALDER SYSTEM ANALYST 20000 D03
EMP
1
 DEPTNO DEPTNAME
D01 ADMINISTRATION
D02 PERSONNEL
D03 PROJECT
DEPT
In set A, suppose that we want to insert the details for a new department say ‘D04’ but at this moment
any employee has not been assigned to it. There is no way in the EMP relation to represent this
information. Because, we cannot simply enter null values for the attributes belong to employee
information because one of these attributes (EMPNO) is the key of the relation and remember that no
key attribute may be assigned a null value. This is insertion anomaly. The relations in set B, however,
do not have this problem because the details for the departments and the employees are maintained
in separate relations.
If we want to delete information of employee whose employee code is E06 from EMP. In set A, we
would lose the information concerning the department D02. This is deletion anomaly. But, in set B, we
can safely delete E06’s information without losing the information concerning the D02 department.
Suppose that we want to change the name of a particular department. For example, suppose that we
want to change the name of the department D03. To achieve this, in set A, we have to update on
every tuple of the employees in the relation instance EMP. If this modification is not carried out on all
the appropriate tuples in the relation instance, the relation, and hence the database, will become
inconsistent. This is updation anomaly. Once again, however, in set B, updating the same requires the
update of only a single tuple in the DEPT relation.
Normalization is most often executed as a sequence of steps. Each step corresponds to a specific
normal form that has certain known properties. As normalization proceeds, the relations become
progressively more restricted (stronger) in format, and as a result less vulnerable to the aforesaid
anomalies.

NORMAL FORM (NF)

A relation is said to be in a particular normal form if it satisfies certain prescribed set of rules. Normal
forms provide database designers with:
o A formal framework for analyzing relation schemas based on their keys and on the data
dependencies or semantic constraints among their attributes.
o A series of tests that can be carried out on individual relation schema so that the relational
database can be normalized to any degree. When a test fails, the relation violating that
test must be decomposed into relations that individually meet the normalization tests.
In the early 1970s by E.F. Codd initially proposed three normal forms called first (1NF), second (2NF),
and third (3NF). Subsequently, R. Boyce and Codd together introduced a stronger definition for third
normal form called Boyce-Codd Normal Form (BCNF). All four of these normal forms are based upon
the concept of a functional dependency.
The standard relational data model requires a minimum normal form of 1NF. Typical thought is that
normalization should proceed through at least 3NF at the minimum.

NOTE: Later, a Fourth Normal Form (4NF) and a Fifth Normal Form (5NF) were proposed, based
on the concepts or multi-valued dependencies and join dependencies, respectively. In the late
1990s, C.J. Date, one of the foremost experts in database theory, proposed Sixth Normal Form(6NF),
particularly to handle situations in which there is temporal data.

2
FIRST NORMAL FORM (1NF)
• Formal Definition:
A relation is in First Normal Form (1NF) if and only if all underlying simple domains contain
atomic values only.
• Normalization Guideline:

A B C

x 1 {a, b}

z 2 c

A B B C A B C
A B C C’
x 1 1 a x 1 a
x 1 a b
z 2 1 b x 1 b
z 2 c NULL
2 c z 2 c
By adding new attributes
By splitting the relation By adding new tuples
in the same relation
into new relations in the same relation

• Practical Example:
Let us consider the following relation where one wishes to record the names and telephone numbers
of customers. There are customers with multiple telephone numbers. The simplest way of
representing the repeated occurrences of telephone numbers in a relation is as follows-
CUSTOMER
CustomerID CustomerName TelNo
389 Pradip Dey 23441212
23334534
405 Tirtha Ray 24126789
407 Manas Ghosh 25127645

The representation above is not in 1NF. The designer might attempt to get around this restriction by
defining multiple Telephone Number columns:
CUSTOMER
CustomerID CustomerName TelNo1 TelNo2
389 Pradip Dey 23441212 23334534
405 Tirtha Ray 24126789 NULL
407 Manas Ghosh 25127645 NULL

NOTE: This representation, however, makes use of nullable columns, and therefore does not
conform to Date's definition of 1NF. Even if the view is taken that nullable columns are allowed, the
design is not in keeping with the spirit of 1NF.

3
A design that is unambiguously in 1NF makes use of two relations: a CUSTOMER relation and a
CUSTOMER_TELEPHONE relation.
CUSTOMER
CustomerID CustomerName
389 Pradip Dey
405 Tirtha Ray
407 Manas Ghosh

CUSTOMER_TELEPHONE
CustomerID TelNo
389 23441212
389 23334534
405 24126789
407 25127645

With CustomerID as key fields, a "parent-child" or one-to-many (1:M) relationship exists between the
two relations, since a customer record (in the "parent" relation) can have many telephone number
records (in the "child" relation), but each telephone number usually has one, and only one customer.
The relationship can be implemented by defining CustomerID as a foreign key of
CUSTOMER_TELEPHONE relation referring the CustomerID of CUSTOMER relation.

SECOND NORMAL FORM (2NF)

Second normal form is based on the concept of fully functional dependency. A functional X → Y is a
fully functional dependency if removing any attribute, A from X causes the dependency to no longer
hold. That is, the dependent attribute depends on the entire candidate key, not just a part of it.
• Formal Definition
A relation is in second normal form (2NF) if and only if it is in 1NF and every non-key attribute
is fully functionally dependent on the entire primary key.
• Normalization Guideline:
Given a relation: R(A, B, C, D). The primary key of R is {A, B}. The functional dependencies that hold on
R are: (i) A, B → C (ii) A → D. The non-key attribute D is partially dependent on the key {AB}.
Decomposition: R1(A, B, C), R2(A, D).
• Practical Example:
Consider the following relation.
STUDENT_COURSES
SID Sname Phone CourseID CourseDesc CreditHours Grade
100 John 487 2454 IS380 Database Concepts 3 A
100 John 487 2454 IS416 Unix 3 B
200 Smith 671 8120 IS380 Database Concepts 3 B
200 Smith 671 8120 IS416 Unix 3 B
200 Smith 671 8120 IS420 Data Network 3 C
300 Russell 871 2356 IS417 System Analysis 3 A

Examination of the above STUDENT_COURSES relation reveals that SID does not uniquely identify a
row (tuple) in the relation hence cannot be the primary key. For the same reason CourseID cannot be
the primary key. However, the combination of SID and CourseID uniquely identifies a row in
STUDENT_COURSES, Therefore, (SID, CourseID) is the primary key of the above relation. The attributes

4
Sname, Phone, CourseDesc, CreditHours and Grade all are non-primary attributes because none of
them is a component of the primary key. But SID alone determines both Sname and Phone, and
attribute CourseID alone determines both CreditHours and CourseDesc attributes. The attribute
Grade is fully functionally dependent on the primary key (SID, CourseID).
The relation STUDENT_COURSES contains redundant data (Database Concepts as the course
description for IS380 appears in more than one place). Also, it suffers from:
o Insertion anomaly: We cannot add a new course such as IS247 with course description
programming techniques to the database unless we add a student who to take the
course.
o Update anomaly: If we change the course description for IS380 from Database Concepts
to Database Management System we have to make changes in more than one place or
else the database will be inconsistent. In other words, in some places the course
description will be Database Management System and, in any place, where we forgot to
make the changes, the description still will be Database Concepts.
o Deletion anomaly: If student Russell is deleted from the database, we also loose
information that we had on course IS417 with description System Analysis.
To convert STUDENT_COURSES to second normal relations we have to make all non-primary
attributes to be fully functionally dependent on the primary key.
Decomposition:
STUDENT (SID, Sname, Phone)
STUDENT_GRADE(SID, CourseID, Grade)
COURSES (CourseID, CourseDesc, CreditHours)

Following are these three relations and their contents:

STUDENT (SID, Sname, Phone)
SID Sname Phone
100 John 487 2454
200 Smith 671 8120
300 Russell 871 2356

COURSES (CourseID, CourseDesc)

CourseID CourseDesc CreditHours
IS380 Database Concepts 3
IS416 Unix Operating System 3
IS420 Data Net Work 3
IS417 System Analysis 3

STUDENT_GRADE(SID, CourseID, Grade)

SID CourseID Grade
100 IS380 A
100 IS416 B
200 IS380 B
200 IS416 B
200 IS420 C
300 IS417 A

All these three relations are in second normal form. Further these three sets are free from all
anomalies. Let us clarify this in more detail.
5
 • Insertion anomaly: Now a new Course with CourseID IS247 and CourseDesc can be inserted
to the relation COURSE. Equally we can add any new students to the database by adding their
id, name and phone to STUDENT relation. Therefore, our database, which made up of these
three relations does not suffer from insertion anomaly.
• Update anomaly: Since redundancy of the data was eliminated no update anomaly can occur.
To change the CourseDesc for IS380 only one change is needed in relation Courses.
• Deletion anomaly: the deletion of student Russell from the database is achieved by deleting
Russell's records from both Student and STUDENT_GRADE relations and this does not have
any side effect because the course IS417 untouched in the relation Courses.

THIRD NORMAL FORM (3NF)

Third normal form is based on the concept of transitive dependency. A functional dependency X→Y
in a relation is a transitive dependency if there is a set of attributes Z that is not a subset of any key of
the relation, and both X→Z and Z→Y hold.
• Formal Definition
A relation is in third normal form (3NF) if and only if it is in 2NF and every non-key attribute is
non-transitively dependent on the primary key.
• Normalization Guideline:
Case 1: Relation with non-Composite Primary key
Given a relation: R(A, B, C) with primary key is {A}. The functional dependencies that hold on R are:
(i) A → B (ii) B → C. The non-key attribute C is transitively dependent on the key attribute A.
Decomposition: R1(A, B), R2(B, C).
Case 2: Relation with non-Composite Primary key.
Given a relation: R(A, B, C, D) with primary key is {A, B}. The functional dependencies that hold on R
are: (i) A, B → C (ii) C → D. The non-key attribute is transitively dependent on the key {AB}.
Decomposition: R1(A, B, C), R2(C, D).
• Practical Example:
Consider the relation given in the following relation -
STUDENT(RollNo,Name,Department,Year,HostelName)
An instance of the above relation is as follows –
STUDENT
RollNo Name Department Year HostelName
1784 Ram Physics 1 Gandhi Bhavana
1648 Krishna Chemistry 1 Gandhi Bhavana
1768 Gopal Mathematics 2 Arabinda Bhavana
1848 Raja Botany 2 Arabinda Bhavana
1682 Maya Geology 3 Krishna
1485 Sima Zoology 4 Netaji Bhavana

A particular hostel is assigned to the students of a particular year.

Here, RollNo is the key and all the other attributes are functionally dependent on it. Thus, it is in 2NF.
But hostelName is dependent on the non-key attribute Year. This dependency is shown below.
RollNo → Name,Department,Year
Year → HostelName
The given relation is in 2NF but not in 3NF due to transitive dependency (Year → HostelName). This
dependency leads to duplication of data as is evident from the table. If it is decided to ask all first-year
students to move to Arabinda Bhavana, and all second-year students to Gandhi Bhavana, this change

6
should be made in many places in the table. Also, when a student's year of study changes his or her
change of hostel should also be noted in the relation. Also, when a student's year of study changes his
hostel change should also be noted in the relation.
To transform it to 3NF, we should introduce another relation which includes the functionally related
non-key attributes. This is shown below.
STUDENT(RollNo,Name,Department,Year)
HOSTEL(Year,HostelName)
It should be stressed again that dependency between attributes is a semantic property and has to be
stated in the problem specification. In this example the functional dependency between Year and
HostelName is clearly stated. In case, hostel allocated to students do not depend on their year in
college, then the original relation R is already in 3NF.

BOYCE-CODD NORMAL FORM (BCNF)

• Formal Definition
A relation is in Boyce/Codd normal form (BCNF) if and only if every determinant is a candidate key.
In other words, A relation R is said to be in BCNF if whenever X → A holds in R, and A is not in X,
then X is a candidate key for R.
• Normalization Guideline:
Consider the following example:
R = (A, B, C)
Candidate keys = {AB}
F = {AB → C, C → A}
This relation scheme is not in BCNF with respect to F since the functional dependency C → A holds
and C is not a candidate key of R. Notice that R is in 3NF because the functional dependency C → A
does not represent a transitive dependency on any candidate key because A is a prime attribute.
Decomposition: R1(C, A) and R2 (B, C).
• Practical Example:
Consider the following relation: ENROLMENT(Student_ID, Course_ID, Instructor)

Student_ID Course_ID Instructor

001 DB101 Prof. Rao
002 DB101 Prof. Rao
003 ML201 Prof. Sharma
The business rules are Each student can enroll in multiple courses, and each course is taught by one
instructor. Here, the functional dependencies are:
Student_ID → Course_ID
Course_ID → Instructor
At this point, neither Student_ID nor Course_ID alone can uniquely identify all attributes. Therefore {
Student_ID, Course_ID} is chosen as a candidate key. While it may seem, this table is in 3NF, it’s not in
BCNF because of the dependency
Course_ID → Instructor
And Course_ID is not a candidate key.

What Problems Can This Cause?

• Update Anomaly: If Prof. Ali is assigned to teach the course DB101 in place of Prof. Rao,
we have to update every row where Prof. Rao appears.
• Insertion Anomaly: We can’t record the fact that a new instructor Mehta is employed for
teaching the course DL202 unless some student enrolls.

7
 • Deletion Anomaly: If student 003 leaves the course ML201, the third row gets deleted.
Now information that Sharma teaches CN is also lost.
To satisfy BCNF, we decompose the relation into two following two:
STUDENT-COURSE
Student_ID Course_ID
001 DB101
002 DB101
003 ML201

COURSE-INSTRUCTOR
Course_ID Instructor
DB101 Prof. Rao
ML201 Prof. Sharma