Database Normalization

Normalization is the process of removing redundant data from your tables in order to
improve storage efficiency, data integrity and scalability. This improvement is balanced against
an increase in complexity and potential performance losses from the joining of the normalized
tables at query-time. There are two goals of the normalization process: eliminating redundant
data (for example, storing the same data in more than one table) and ensuring data dependencies
make sense (only storing related data in a table).

Why We Need Normalization?

• Minimize data redundancy i.e. no unnecessarily duplication of data.

• To make database structure flexible i.e. it should be possible to add new data values and rows
without reorganizing the database structure.

• Data should be consistent throughout the database i.e. it should not suffer from following
anomalies.

The Problem of redundancy in Database:

Redundancy means having multiple copies of same data in the database. This problem arises
when a database is not normalized. Suppose a table of student details attributes are: student Id,
student name, college name, college rank, course opted.

As it can be observed that values of attribute college name, college rank, course is being repeated
which can lead to problems. Problems caused due to redundancy are: Insertion anomaly,
Deletion anomaly, and Updation anomaly.

• Insert Anomaly – Due to lack of data i.e., all the data available for insertion such that null
values in keys should be avoided. This kind of anomaly can seriously damage a database

• Update Anomaly – It is due to data redundancy i.e. multiple occurrences of same values in a
column. This can lead to inefficiency.
• Deletion Anomaly – It leads to loss of data for rows that are not stored elsewhere. It could
result in loss of vital data.

• Complex queries required by the user should be easy to handle.

Normalization

 Normalization is the process of organizing the data in the database.

 Normalization is used to minimize the redundancy from a relation or set of relations. It is
also used to eliminate the undesirable characteristics like Insertion, Update and Deletion
Anomalies.
 Normalization divides the larger table into the smaller table and links them using
relationship.
 The normal form is used to reduce redundancy from the database table.

Types of Normal Forms

There are the four types of normal forms:

Normal Form Description

First Normal Form A relation is in 1NF if it contains an atomic value.

( 1NF)

Second Normal Form (2NF) A relation will be in 2NF if it is in 1NF and all non-key attributes are fully
functional dependent on the primary key.

Third Normal Form (3NF) A relation will be in 3NF if it is in 2NF and no transition dependency exists.

Boyce Codd Normal Form A relation will be in 4NF if it is in Boyce Codd normal form and has no multi-
(BCNF) valued dependency.
First Normal Form (1NF)

o A relation will be 1NF if it contains an atomic value.

o It states that an attribute of a table cannot hold multiple values. It must hold only single-
valued attribute.
o First normal form disallows the multi-valued attribute, composite attribute, and their
combinations.

Example: Relation EMPLOYEE is not in 1NF because of multi-valued attribute EMP_PHONE.

EMPLOYEE table:

EMP_ID EMP_NAME EMP_PHONE EMP_STATE

14 John 7272826385, UP
9064738238

20 Harry 8574783832 Bihar

12 Sam 7390372389, Punjab

8589830302

The decomposition of the EMPLOYEE table into 1NF has been shown below:

EMP_ID EMP_NAME EMP_PHONE EMP_STATE

14 John 7272826385 UP

14 John 9064738238 UP

20 Harry 8574783832 Bihar

12 Sam 7390372389 Punjab

12 Sam 8589830302 Punjab

Second Normal Form (2NF):

o In the 2NF, relational must be in 1NF.

o In the second normal form, all non-key attributes are fully functional dependent on the
primary key
Example: Let's assume, a school can store the data of teachers and the subjects they teach. In a
school, a teacher can teach more than one subject.

TEACHER table

TEACHER_ID SUBJECT TEACHER_AGE

25 Chemistry 30

25 Biology 30

47 English 35

83 Math 38

83 Computer 38

In the given table, non-prime attribute TEACHER_AGE is dependent on TEACHER_ID which

is a proper subset of a candidate key. That's why it violates the rule for 2NF.

To convert the given table into 2NF, we decompose it into two tables:

TEACHER_DETAIL table:

TEACHER_ID TEACHER_AGE

25 30

47 35

83 38

TEACHER_SUBJECT table:

TEACHER_ID SUBJECT

25 Chemistry

25 Biology

47 English

83 Math
 83 Computer

Third Normal Form (3NF):

o A relation will be in 3NF if it is in 2NF and not contain any transitive partial dependency.
o 3NF is used to reduce the data duplication. It is also used to achieve the data integrity.
o If there is no transitive dependency for non-prime attributes, then the relation must be in
third normal form.

A relation is in third normal form if it holds atleast one of the following conditions for every
non-trivial function dependency X → Y.

1. X is a super key.
2. Y is a prime attribute, i.e., each element of Y is part of some candidate key.

Example:

EMP_ID EMP_NAME EMP_ZIP EMP_STATE EMP_CITY

222 Harry 201010 UP Noida

333 Stephan 02228 US Boston

444 Lan 60007 US Chicago

555 Katharine 06389 UK Norwich

666 John 462007 MP Bhopal

EMPLOYEE_DETAIL table:

Super key in the table above:

1. {EMP_ID}, {EMP_ID, EMP_NAME}, {EMP_ID, EMP_NAME, EMP_ZIP}....so on

Candidate key: {EMP_ID}

Non-prime attributes: In the given table, all attributes except EMP_ID are non-prime.
 Here, EMP_STATE & EMP_CITY dependent on EMP_ZIP and EMP_ZIP dependent on
EMP_ID. The non-prime attributes (EMP_STATE, EMP_CITY) transitively dependent
on super key(EMP_ID). It violates the rule of third normal form.

That's why we need to move the EMP_CITY and EMP_STATE to the new
<EMPLOYEE_ZIP> table, with EMP_ZIP as a Primary key.

EMP_ID EMP_NAME EMP_ZIP

222 Harry 201010

333 Stephan 02228

444 Lan 60007

555 Katharine 06389

666 John 462007

EMPLOYEE table:

EMPLOYEE_ZIP table:

EMP_ZIP EMP_STATE EMP_CITY

201010 UP Noida

02228 US Boston

60007 US Chicago

06389 UK Norwich

462007 MP Bhopal

Boyce Codd normal form (BCNF):

o BCNF is the advance version of 3NF. It is stricter than 3NF.

o A table is in BCNF if every functional dependency X → Y, X is the super key of the
table.
 o For BCNF, the table should be in 3NF, and for every FD, LHS is super key.

Example: Let's assume there is a company where employees work in more than one department.

EMPLOYEE table:

EMP_ID EMP_COUNTRY EMP_DEPT DEPT_TYPE EMP_DEPT_NO

264 India Designing D394 283

264 India Testing D394 300

364 UK Stores D283 232

364 UK Developing D283 549

In the above table Functional dependencies are as follows:

1. EMP_ID → EMP_COUNTRY
2. EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}

Candidate key: {EMP-ID, EMP-DEPT}

The table is not in BCNF because neither EMP_DEPT nor EMP_ID alone are keys.

To convert the given table into BCNF, we decompose it into three tables:

EMP_COUNTRY table:

EMP_ID EMP_COUNTRY

264 India

264 India

EMP_DEPT table:

EMP_DEPT DEPT_TYPE EMP_DEPT_NO

Designing D394 283

Testing D394 300

 Stores D283 232

Developing D283 549

EMP_DEPT_MAPPING table:

EMP_ID EMP_DEPT

D394 283

D394 300

D283 232

D283 549

Functional dependencies:

1. EMP_ID → EMP_COUNTRY
2. EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}

Candidate keys:

For the first table: EMP_ID

For the second table: EMP_DEPT
For the third table: {EMP_ID, EMP_DEPT}

Now, this is in BCNF because left side part of both the functional dependencies is a key.

Lossless Join decomposition:

On decomposition of a relation into smaller relations with fewer attributes on normalization the
resulting relations whenever joined must result in the same relation without any extra rows. The
join operations can be performed in any order. This is known as Lossless Join decomposition.

The resulting relations (tables) obtained on normalization should possess the properties such as
each row must be identified by a unique key, no repeating groups, homogeneous columns, each
column is assigned a unique name etc.

Advantages of Normalization

The following are the advantages of the normalization.

• More efficient data structure.

• Avoid redundant fields or columns.

• More flexible data structure i.e. we should be able to add new rows and data values easily

• Better understanding of data.

• Ensures that distinct tables exist when necessary.

• Easier to maintain data structure i.e. it is easy to perform operations and complex queries can
be easily handled.

• Minimizes data duplication.

• Close modeling of real world entities, processes and their relationships.

Disadvantages of Normalization

The following are disadvantages of normalization.

• You cannot start building the database before you know what the user needs.

• On normalizing the relations to higher normal forms i.e. 4NF, 5NF the performance degrades.

• It is very time consuming and difficult process in normalizing relations of higher degree.

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