Home / PROFESSOR EXAM / UGC NET / Normalization

Normalization

 

The biggest problem needed to be solved in the database is data redundancy.
Why is data redundancy the problem? Because it causes:

Insert Anomaly
Update Anomaly
Delete Anomaly

Teacher Subject Degree Phone
Ram Kumar Database Master’s 012666777
Van Sokhen Database Bachelor’s 017678678
Sok San E-Commerce Master’s 012666777

Normalization is the process of removing redundant data from your tables to improve storage efficiency, data integrity, and scalability.
Normalization generally involves splitting existing tables into multiple ones, which must be re-joined or linked each time a query is issued.

Why normalization?

The relation derived from the user view or data store will most likely be unnormalized.
The problem usually happens when an existing system uses an unstructured file, e.g. in MS Excel.

Steps of Normalization

First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Boyce-Codd Normal Form (BCNF)
Fourth Normal Form (4NF)
Fifth Normal Form (5NF)

In practice, 1NF, 2NF, and 3NF are enough for the database.

First Normal Form (1NF)

The official qualifications for 1NF are:

  1. Each attribute name must be unique.
  1. Each attribute value must be single.
  1. Each row must be unique.
  1. There is no repeating groups.

Additional:

Choose a primary key.

Reminder:

A primary key is unique, not null, unchanged. A primary key can be either an attribute or combined attributes.

Example of a table not in 1NF 

Group Topic Student Score
Group A Intro MongoDB Sok San 18 marks
Sao Ry 17 marks
Group B Intro MySQL Chan Tina 19 marks
Tith Sophea 16 marks

It violates the 1NF because:
Attribute values are not single.
Repeating groups exist.

1nf
1nf

Now it is in 1NF.

However, it might still violate 2NF and so on.

Functional Dependencies

We say an attribute, B, has a functional dependency on another attribute, A, if for any two records, which have the same value for A, then the values for B in these two records must be the same. We illustrate this as:

2nf
2nf

employee name email address

funcational dependancy
functional dependency

If EmpNum is the PK then the FDs:
EmpNum, EmpEmail, EmpFname, EmpLname must exist.

Functional Dependency
EmpNum –> EmpEmail
Attribute on the left-hand side is known as the
determinant
• EmpNum is a determinant of EmpEmail

Second Normal Form (2NF)

The official qualifications for 2NF are:

  1. A table is already in 1NF.
  1. All nonkey attributes are fully dependent on the primary key.

All partial dependencies are removed to place in another table.

second nf
second nf

The Course Name depends on only CourseID, a part of the primary key, not the whole primary {CourseID, SemesterID}.It’s called partial dependency.

Solution: Remove CourseID and Course Name together to create a new table.

Third Normal Form (3NF)

The official qualifications for 3NF are:
1. A table is already in 2NF.
2. Nonprimary key attributes do not depend on other
nonprimary key attributes
(i.e. no transitive dependencies)
All transitive dependencies are removed to place in
another table.


Solution:
Remove Teacher Name and Teacher Tel together to create a new table.

Boyce Codd Normal Form (BCNF) – 3.5NF

The official qualifications for BCNF are:

  1. A table is already in 3NF.
  1. All determinants must be superkeys.

All determinants that are not superkeys are removed to place in another table.

Key: {Student, Course}

Functional Dependency:

{Student, Course} –> Teacher

Teacher–> Course

Problem: Teacher is not a superkey but determines Course.


Fourth Normal Form (4NF)

The official qualifications for 4NF are:
1. A table is already in BCNF.
2. A table contains no multi-valued dependencies.
* Multi-valued dependency: MVDs occur when two
or more independent multi valued facts about the
same attribute occur within the same table.
A =>=> B (B multi-valued depends on A)

Fifth Normal Form (5NF)

The official qualifications for 5NF are:
1. A table is already in 4NF.
2. The attributes of multi-valued dependencies are related.

5th nf
5th noraml form

About admin

Leave a Reply

Your email address will not be published. Required fields are marked *