What is Normalization? | How to apply Normalization up to the 5th Normal Form?

“image: Freepik.com”. This cover has been designed using resources from Freepik.com

Introduction

  • What is Normalization?
  • What is data redundancy?
  • What are data anomalies?
  • 1st Normal Form
  • 2nd Normal Form & Partial Dependency
  • 3rd Normal Form & Transitive Dependency
  • Boyce Codd Normal Form
  • 4th Normal Form & Multi-valued Dependency
  • 5th Normal Form & Join Dependency
  • Wrapping up

What is Normalization?

Fig: 1

What are data anomalies?

  1. Update Anomaly:
    It happens when we try to update the record in our table. In the employee table, we have a project manager for each project. Now, most probably each project has more than one employee, which means more than one record in our table. For each record, we are repeating the project code, project name, and project manager. Now If we have to change the project manager, let’s say for the GLO project from Qadir Shaikh to Hamza Imran. We have to change it for every record where the project is GLO. There is a chance we might miss one or two entries leaving the database in an inconsistent state. Where in some records project manager is Qadir Shaikh and for others, it is Hamza Imran.
  2. Insertion Anomaly:
    Let’s say we want to add a new employee who has been hired recently and currently is in training so hasn’t been assigned any project yet. We can’t add employees without the project details or we have to fill the project fields with null values.
    OR let’s say we want to add a new project in our database that we have received from the client but we haven’t started working on it yet, so currently, there is no employee assigned to this project. But we can’t store the project details without any employee since employee_id (primary key) is linked with the employee.
  3. Deletion Anomaly
    Let’s say our project NJAS is completed and we want to remove it from our table so we will remove all the entries of the table where the project is NJAS. But If we do so we will also remove the record of all the employees (Umer shaikh all others) who were working on this project

1st Normal Form

  • Each field (cell) in our table should have a single atomic value.
  • There should not be any repeating groups
  • Each attribute(column) should contain the value of the same type.
Fig: 2
Fig: 3
Fig: 4
Fig: 5

2nd Normal Form

  • The table should be in first normal form
  • There should be no partial dependency between non-prime attributes and the prime attributes.
Fig: 6

3rd Normal Form

  • The table should be in 2nd normal form
  • There should be no transitive dependency between prime attributes and non-prime attributes
Fig 7: Transitive dependency
Fig 8: New table for the projects
Fig: 9

Boyce and Codd Normal Form (BCNF)

  • The table should be in the 3rd normal form
  • And, for any dependency A → B, A should be a super key.
  • The 2nd normal form says that any non-prime attribute should be functionally dependent on the whole primary key and not partial key.
  • The 3rd normal form says that any non-prime attribute should not be dependent on any other non-prime attribute.
  • BCNF says that any prime attribute should also not be dependent on the non-prime attributes.
Fig: 10
  • This table satisfies the first normal form because each cell has a single value and there are no repeating groups.
  • This table also satisfies the 2nd normal form because there is no partial dependency between prime attributes and non-prime attributes.
  • This table also satisfies the 3rd normal form since there is no transitive dependency between prime attributes and non-prime attributes.
Fig: 11

4th Normal Form

  • The table should be in Boyce Codd normal form (BCNF)
  • There should be no multi-valued dependency
  • In the relation A->B, for each value of A, there should be more than one value of B
  • There should be at least three attributes
  • And for the table with A, B, and C columns, B and C should be independent of each other. And both should be dependent on A.
Fig: 12
  • There are 3 attributes
  • For each value, of course, there is more than one value of the Professor and more than one value of the Book.
  • The Professor and Book attributes are independent of each other but both are dependent on the Course attribute.
Fig: 13

5th Normal Form (Project join normal form)

  • The table should be in 4th normal form
  • It should not have the join dependency

Wrapping Up

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store