Normalization Introduction

Normalization Introduction, Non loss decomposition and functional dependencies,

First, Second, and third normal forms dependency preservation, Boyce/Codd normal
form. Higher Normal Forms Introduction, Multivalued dependencies and Fourth
normal form, Join dependencies and Fifth normal form.

Normalization Introduction

 Normalization is a process of organizing the data in database to avoid data

redundancy, insertion anomaly, update anomaly and deletion anomaly.
 Normalization is a database design technique which organizes tables in a manner
that reduces redundancy and dependency of data.
 It divides larger tables to smaller tables and links them using relationships.
Normalization is also the process of simplifying the design of a database

Anomalies in DBMS

There are three types of anomalies that occur when the database is not normalized.

1. Insertion Anomaly

2. Update Anomaly

3. Deletion Anomaly

Let us assume we have Employee table as given below

Akhila N,dept of MCA

Page 1
 Normalization Introduction

Insert anomaly:

Insert anomaly is something when we are not able to insert data into tables due to
some constraints. Suppose a new employee joins the company, who is under training
and currently not assigned to any department then we would not be able to insert data
into the table if Emp_Dept field doesn’t allow nulls.

Update anomaly: Update anomaly is something when we are trying to update

some records in table, and that update is causing data inconsistency.
For example, in the above table we have two records for EmpId 100 as he belongs
to two departments of the company.
If we want to update the address of Rock then we have to update the same in two
rows or the data will become inconsistent. If somehow, the correct address gets
updated in one department but not in other then as per the database, Rock would
be having two different addresses, which is not correct and would lead to
inconsistent data.

Delete anomaly: Delete anomaly is something when we delete some data from
the table, and due to that delete operation we loss some other useful data.
For example, if at a point of time the company closes the department 103 then
deleting the rows that are having Emp_Dept as 103 would also delete the
information of employee Peter since she is assigned only to this department.
Normalization is a method to remove all these anomalies and bring the database
to a consistent state.

Akhila N,dept of MCA

Page 2
 Normalization Introduction

ADVANTAGES OF NORMALIZATION

Here we can see why normalization is an attractive prospect in RDBMS

concepts.

1) A smaller database can be maintained as normalization eliminates the

duplicate data. Overall size of the database is reduced as a result.

2) Better performance is ensured which can be linked to the above point. As

databases become lesser in size, the passes through the data becomes faster
and shorter thereby improving response time and speed.

3) Narrower tables are possible as normalized tables will be fine-tuned and

will have lesser columns which allows for more data records per page.

4) Fewer indexes per table ensures faster maintenance tasks (index rebuilds).

5) Also realizes the option of joining only the tables that are needed.

DISADVANTAGES OF NORMALIZATION

1) More tables to join as by spreading out data into more tables, the need to
join table’s increases and the task becomes more tedious. The database
becomes harder to realize as well.

2) Tables will contain codes rather than real data as the repeated data will be
stored as lines of codes rather than the true data. Therefore, there is always a
need to go to the lookup table.

3) Data model becomes extremely difficult to query against as the data model
is optimized for applications, not for ad hoc querying. (Ad hoc query is a query

Akhila N,dept of MCA

Page 3
 Normalization Introduction

that cannot be determined before the issuance of the query. It consists of an

SQL that is constructed dynamically and is usually constructed by desktop
friendly query tools.). Hence it is hard to model the database without knowing
what the customer desires.

4) As the normal form type progresses, the performance becomes slower and
slower.

5) Proper knowledge is required on the various normal forms to execute the

normalization process efficiently. Careless use may lead to terrible design
filled with major anomalies and data inconsistency.

Functional Dependencies (FDs)

Definition

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

X→Y
If two tuples have the same value for X, they must have the same value for Y.

Here:

 X = Determinant
 Y = Dependent attribute

Example

Student Table

USN Name Dept

 USN → Name
 USN → Dept
Akhila N,dept of MCA
Page 4
 Normalization Introduction

Meaning: Each USN uniquely determines Name and Dept.

Types of Functional Dependencies

1. Trivial FD
o Y⊆X
o Example: (USN, Name) → USN
2. Non-Trivial FD
o Y⊄X
o Example: USN → Name
3. Completely Non-Trivial FD
o X∩Y=Ø
o Example: USN → Dept

Lossless Decomposition in DBMS

The decomposition of a given relation X is known as a lossless decomposition when the
X decomposes into two relations X1 and X2 in a way that the natural joining of X1 and
X2 gives us the original relation X in return.

Uses of Lossless Decomposition in DBMS

There are two types of decompositions in DBMS, lossless and lossy decomposition. The
process of lossless decomposition helps in the removal of data redundancy from a
database while still preserving the initial/original data.

In the case of lossless decomposition, one selects the common attribute. Here, the
criteria used for the selection of a common attribute as this attribute has to be a super
key or a candidate key in either relation X1, relation X2, or either of them.

Conditions Required for Lossless Decomposition in DBMS

Let us consider a relation X. In case we decompose this relation into relation X1 and
relation X2 sub-parts. This decomposition will be referred to as a lossless
decomposition in case it satisfies these statements:

Akhila N,dept of MCA

Page 5
 Normalization Introduction

 If we union the sub relations X1 and X2, then it should consist of all the attributes
available before the decomposition in the original relation X.
 The intersections of X1 and X2 can never be Null. There must be a common
attribute in the sub relation. This common attribute must consist of some unique
data/information.
 Here, the common attribute needs to be the super key of the sub relations, either
X1 or X2.

 In this case,

 X = (P, Q, R)

 X1 = (P, Q)

 X2 = (Q, W)

 The relation X here consists of three attributes P, Q, and R. The relation X here
decomposes into two separate relations X1 and X2. Thus, each of these X1 and X2
both have two attributes. The common attribute among each of these is Q.

 Remember that the value present in column Q has to be unique. In case it consists
of a duplicate value, then a lossless-join decomposition would not be possible
here.

Lossless Decomposition in DBMS Example

Example 1
Draw a table with the relation X that has raw data:

X (P, Q, R)
P Q R

37 25 16

Akhila N,dept of MCA

Page 6
 Normalization Introduction

29 18 35

16 39 28

This relation would decompose into the following sub relations, X1 and X2:

X1 (P, Q)
P Q

37 25

29 18

16 39

X2 (Q, R)
Q R

25 16

18 35

39 28

Let us now check the first condition that satisfies the lossless-join decomposition. Here,
the union of the sub relations X1 and X2 generate the same results as the relation X.

X1 ∩ X2 = X

Akhila N,dept of MCA

Page 7
 Normalization Introduction

Here, we will get the result as follows:

X (P, Q, R)
P Q R

37 25 16

29 18 35

16 39 28

This relation is similar to the original relation X. Thus, this decomposition can be
considered as the lossless join decomposition in DBMS.

Database normalization rules

Database normalization process is divided into following the normal form:

First Normal Form (1NF)

1NF (First Normal Form) Rules

 Each table cell should contain a single value.

 Each record needs to be unique.

Akhila N,dept of MCA

Page 8
 Normalization Introduction

Example:

Sample Employee table, it displays employees are working with multiple

departments.

Employe Age Department

e
Melvin 32 Marketing, Sales

Edward 45 Quality
Assurance
Alex 36 Human Resource

Employee table following 1NF:

Employe Age Department

e
Melvin 32 Marketing
Melvin 32 Sales
Edward 45 Quality
Assurance
Alex 36 Human
Resource

Akhila N,dept of MCA

Page 9
 Normalization Introduction

Second Normal Form (2NF)

A table is said to be in Second Normal Form (2NF) if:

1. It is already in First Normal Form (1NF), and

2. No partial dependency exists — that means non-key attributes must
depend on the whole primary key, not just part of it.

What is Partial Dependency?

When a table has a composite primary key (more than one column)
and a non-key attribute depends on only one part of that key, it is
called partial dependency.

Example (Not in 2NF)

Student Table

StudentID
CourseID StudentName CourseName

S1 C1 Anu
DBMS

S2 C2 Ramesh OS

Primary Key: (StudentID, CourseID)

 StudentName depends only on StudentID

 CourseName depends only on CourseID

 Partial dependency exists ❌

 Table is not in 2NF

Converting to 2NF

Split the table into smaller tables:

Akhila N,dept of MCA

Page 10
 Normalization Introduction

Student Table

StudentID StudentName
S1 Anu
S2 Ramesh

Course Table

CourseID CourseName
C1 DBMS
C2 OS

Enrollment Table

StudentID CourseID
S1 C1
S2 C2

 Now all non-key attributes depend on the entire primary key

Tables are in Second Normal Form

Third Normal Form (3NF)

A table is said to be in Third Normal Form (3NF) if:

1. It is already in Second Normal Form (2NF), and

2. No transitive dependency exists.

This means non-key attributes should depend only on the primary key and
not on other non-key attributes.

What is Transitive Dependency?

A transitive dependency occurs when:

 A non-key attribute depends on another non-key attribute, and

 That non-key attribute depends on the primary key.

Akhila N,dept of MCA

Page 11
 Normalization Introduction

In short:
Primary Key → Non-key Attribute → Another Non-key Attribute ❌

Example (Table NOT in 3NF)

Student Table

StudentID StudentName DeptID DeptName

S1 Anu D1 MCA

S2 Ramesh D2 MBA

Primary Key: StudentID

Dependencies:

 StudentID → StudentName
 StudentID → DeptID
 DeptID → DeptName

DeptName depends on DeptID, not directly on StudentID

This is a transitive dependency ❌
Table is not in 3NF

Converting to Third Normal Form

Split the table to remove transitive dependency:

Student Table

StudentID StudentName DeptID

S1 Anu D1

S2 Ramesh D2

Akhila N,dept of MCA

Page 12
 Normalization Introduction

Department Table

DeptID DeptName

D1 MCA

D2 MBA

Now:

 All non-key attributes depend only on the primary key

 No transitive dependency exists
 Tables are in Third Normal Form (3NF)

Advantages of 3NF

 Eliminates data redundancy

 Avoids update, insert, and delete anomalies
 Improves data consistency
 Makes database design more efficient.

Boyce–Codd Normal Form (BCNF)

Definition of BCNF

 A relation is in Boyce–Codd Normal Form (BCNF) if:

 For every functional dependency X → Y, X must be a super key.
 BCNF is a stronger version of Third Normal Form (3NF).

Why BCNF is Needed?

Even after converting tables to 3NF, some anomalies may still exist when:

 There are multiple candidate keys

 Dependencies overlap
 BCNF removes these remaining anomalies.


Akhila N,dept of MCA

Page 13
 Normalization Introduction

3NF BCNF
Allows dependency if RHS is a Does not allow any
prime attribute exception
Less strict More strict
May still have anomalies Fully removes anomalies

Example (Table in 3NF but NOT in BCNF)

Student_Course Table

Student Course Instructor

S1 DBMS Prof A

S2 OS Prof B

Functional Dependencies:

 (Student, Course) → Instructor

 Instructor → Course

Candidate Keys:

 (Student, Course)

Instructor is not a super key, but determines Course ,Violates BCNF ❌

Table is not in BCNF

Converting to BCNF

Decompose the table:

Akhila N,dept of MCA

Page 14
 Normalization Introduction

Instructor_Course Table

Instructor Course

Prof A DBMS

Prof B OS

Student_Instructor Table

Student Instructor

S1 Prof A

S2 Prof B

Now in both tables:

 Left side of every dependency is a super key

 Tables satisfy BCNF

Advantages of BCNF

 Eliminates all redundancy caused by functional dependencies

 Prevents update, insert, and delete anomalies
 Produces a more stable and consistent database design.

Multivalued dependencies and Fourth normal form,

Multivalued Dependency (MVD)

Definition

Akhila N,dept of MCA

Page 15
 Normalization Introduction

A Multivalued Dependency (MVD) exists when, in a relation, one attribute

uniquely determines a set of values of another attribute independent of
other attributes.

It is written as:

A →→ B

Meaning:
For each value of A, there is a set of values of B associated with it,
independent of other attributes.

example of MVD

Consider a table:

STUDENT_COURSE_HOBBY

Student Course Hobby

Ravi Java Cricket
Ravi Java Music
Ravi Python Cricket
Ravi Python Music

Here:

 Ravi studies multiple courses → Java, Python

 Ravi has multiple hobbies → Cricket, Music
 Course and Hobby are independent of each other

Fourth Normal Form (4NF)

Definition

A relation is in 4NF if:

1. It is already in BCNF, and

Akhila N,dept of MCA

Page 16
 Normalization Introduction

2. It has no non-trivial Multivalued Dependencies.

Rule for 4NF

If a relation has:

A →→ B

Then A must be a super key.

If not, decompose the table.

3. Decomposition into 4NF

Original Table

STUDENT_COURSE_HOBBY
(Student, Course, Hobby)

MVDs:

 Student →→ Course
 Student →→ Hobby

Decompose into two tables:

Table 1: STUDENT_COURSE
Student Course

Ravi Java

Ravi Python

Table 2: STUDENT_HOBBY
Student Hobby

Ravi Cricket

Ravi Music

Akhila N,dept of MCA

Page 17
 Normalization Introduction

Now:

 No redundancy
 No anomalies
 Tables are in 4NF

Join Dependency (JD)

Definition

A Join Dependency (JD) exists when a table can be reconstructed exactly by

joining two or more of its projections without any loss of information.

It is denoted as:

JD (R1, R2, R3 … Rn)

where R1, R2, R3 are projections of relation R.

Fifth Normal Form (5NF / PJNF)

Definition

A relation is in Fifth Normal Form (5NF) if:

1. It is already in 4NF, and

2. It cannot be decomposed further without loss using join dependency.

OR

A relation is in 5NF if every join dependency in it is implied by its candidate

keys.

Why 5NF is Needed?

Even after 4NF:

 Some tables still have redundancy

 Due to complex many-to-many relationships
 That involve more than two attributes

5NF removes this type of redundancy.

Akhila N,dept of MCA

Page 18
 Normalization Introduction

Consider Relation:

SUPPLY

Supplier Part Project

S1 P1 J1

S1 P2 J1

S2 P1 J2

S2 P2 J2

 Supplier supplies Parts

 Parts are used in Projects
 Supplier works for Projects

This relation contains a cyclic relationship.

Decompose into 3 tables

1. SUPPLIER_PART
Supplier Part

S1 P1

S1 P2

S2 P1

S2 P2

2. PART_PROJECT
Part Project

Akhila N,dept of MCA

Page 19
 Normalization Introduction

Part Project

P1 J1

P2 J1

P1 J2

P2 J2

3. SUPPLIER_PROJECT
Supplier Project

S1 J1

S2 J2

Difference Between 4NF and 5NF

4NF 5NF

Based on Multivalued Dependency Based on Join Dependency

Decomposition into 2 tables Decomposition into 3 or more tables

Removes MVD redundancy Removes cyclic redundancy

Akhila N,dept of MCA

Page 20