Normalization in DBMS,(1NF,2NF,3NF,BCNF,4NF,5NF)

                    

Normalization in DBMS

Handwritten Notes- Click Here


Normalization divides a large table into smaller, logically related tables and connects them using appropriate keys.

The main purpose of normalization is to reduce unnecessary duplication of data and prevent problems that may occur when data is inserted, updated, or deleted.


1. First Normal Form (1NF)

A relation is said to be in First Normal Form (1NF) when every attribute contains a single, indivisible value.

Conditions of  1NF

  • Each cell must contain only one value.
  • Multiple values should not be stored in the same field.

·        Example

Customer

Items Purchased

Gowsi

Rice, Dal

Tamil

Soap
· Here, the Items Purchased column contains more than one value for Gowsi.
· Therefore, the table is not in 1NF.Converting it into 1NF.

Customer

Item Purchased

Gowsi

Rice

Gowsi

Dal

Tamil

Soap




2. Second Normal Form (2NF)

A relation is in Second Normal Form (2NF) when:

  • It is already in 1NF.
  • It does not contain partial dependency(Non-Primary key depends on only part of a composite primary key).
  • Every non-key attribute must depend on the whole primary key.

Example

Customer

(Primary key)

Product

(Primary key)

Price

(Non-Primary key)

Gowsi

Laptop

50,000

Tamil

Mouse

300

Primary Key = (Customer, Product)
The price depends on the Product, not on the Customer.(Product → Price).
Therefore, Price has a partial dependency 


Decomposition

The table can be divided into two relations.

Customer-Product

Customer

Product

Gowsi

Laptop

Tamil

Mouse

Product-Price

Product

Price

Laptop

50,000

Mouse

300

This removes the partial dependency.



3. Third Normal Form (3NF)

A relation is in Third Normal Form (3NF) when:

  • It is already in 2NF.
  • It does not contain transitive dependency.(A → B,B → C,A → C)
  • A non-key attribute should not depend on another non-key attribute.

Example

Product Name

(Primary key)

Category

(Non-Primary key)

Tax Rate

(Non-Primary key)

Bread

Bakery

0%

Apple Juice

Beverages

8%

Cake

Bakery

0%

Orange Soda

Beverages

8%
Product Name → Category → Tax Rate. This is a transitive dependency.

Decomposition
Separate the information into two tables.

Product Table

Product Name

Category

Bread

Bakery

Apple Juice

Beverages

Cake

Bakery

Orange Soda

Beverages

Category Table

Category

Tax Rate

Bakery

0%

Beverages

8%
This removes the transitive dependency.



4. Boyce-Codd Normal Form (BCNF)

Boyce-Codd Normal Form (BCNF) is a stronger version of 3NF.

A relation is in BCNF when

Every determinant in the relation is a candidate key(If one attribute(Column) or a group of attributes determines another attribute, should be capable of uniquely identifying a record.)

Example

Student

Book Title

Author

Gowsi

Harry Potter

J.K. Rowling

Jeji

The Hobbit

J.R.R. Tolkien

Tamil

Harry Potter

J.K. Rowling

Book Title → Author. because a particular book title determines its author.

However, Book Title alone cannot uniquely identify a record, because the same book can be borrowed by different students.

Therefore, the original relation may violate BCNF.

Decomposition
Separate the information into two tables.

Student-Book

Student

Book Title

Gowsi

Harry Potter

Jegi

The Hobbit

Tamil

Harry Potter

Book-Author

Book Title

Author

Harry Potter

J.K. Rowling

The Hobbit

J.R.R. Tolkien


5. Fourth Normal Form (4NF)

A relation is in Fourth Normal Form (4NF) when:

  • It is already in BCNF.
  • It does not contain an unwanted multi-valued dependency(one attribute is associated independently with multiple values of another attribute.)

Example

Person

Hobby

Language

Gowsi

Music

Tamil

Gowsi

Cricket

English
Here, a person can have multiple hobbies and multiple languages.

The two sets of information are independent:
Person →→ Hobby
Person →→ Language

Keeping both independent multi-valued attributes in one table can create unnecessary combinations.

Decomposition
Separate the independent information.

Person-Hobby

Person

Hobby

Gowsi

Music

Gowsi

Cricket


Person-Language

Person

Language

Gowsi

Tamil

Gowsi

English

This removes the multi-valued dependency from the original relation.



5. Fifth Normal Form (5NF)

Fifth Normal Form (5NF) is also called Project-Join Normal Form (PJ/NF).

A relation is in 5NF when it cannot be further decomposed into smaller relations without losing the ability to reconstruct the original information correctly.

It mainly deals with join dependencies.

Example

Supplier

Product

Shop

S1

Pen

ShopA

S1

Pencil

ShopA

S2

Pen

ShopB

This table represents relationships among:

  • Supplier and Product
  • Product and Shop
  • Supplier and Shop

When several independent relationships exist together, storing everything in one relation may result in unnecessary combinations.

Decomposition

The information can be represented using smaller relations:

Supplier-Product

Supplier

Product

S1

Pen

S1

Pencil

S2

Pen

Product-Shop

Product

Shop

Pen

ShopA

Pen

ShopB

Pencil

ShopA

Supplier-Shop

Supplier

Shop

S1

ShopA

S2

ShopB
The purpose is to represent the relationships separately and reconstruct the original relation through joins when the required conditions are satisfied.


Video Explanation



Comments

Post a Comment

Popular posts from this blog

Queue ADT

Image
  Queue ADT Handwritten Notes- Click Here A  Queue  is a linear data structure in which elements are inserted at one end and removed from the other end.( Insertion → Rear,  Deletion → Front )                                   ↓ FRONT                                 ↓ REAR A B C D E    A queue follows the FIFO (First In, First Out) principle. This means that the element inserted first is removed first. A  Queue can be implemented using: Array Linked List Basic  Queue  Operations: 1. Enqueue - used to insert  a new element into the queue. 2. Dequeue - used to remove an element from the queue. 3. Peek - used to view the first element without removing it. 4. isEmpty - checks whether the queue contains any elements. 5...
Read more

Entity-Relationship(ER) Model

Image
Entity-Relationship(ER) Model Handwritten Notes - Click Here The Entity-Relationship (ER) Model is a conceptual data model. It helps database designers understand the requirements of a system before creating the actual database tables . It describes the important objects in a system, their properties , and the relationships between them. The three basic concepts of the ER model are Entity Attribute Relationship Entity An entity can be any real-world object, person, place, or class about which we want to store information in a database. In a traditional ER diagram, an entity is represented by a rectangle .                        Example: Employee and Department An employee works for a department. Here, Employee and Department are entities , and Works For is the relationship between them. Weak Entity A weak entity is an entity that depends on another entity for its identification of existence. A weak entity is represen...
Read more

Different types of Data Models in DBMS

Image
             Data Models Handwritten Notes- Click Here A Data Model defines how data is organized, stored, related, and accessed in a database. Types of Data Models in DBMS Hierarchical Data Model Network Data Model Relational Data Model Entity-Relationship (ER) Model Object-Oriented Data Model Object-Relational Data Model 1. Hierarchical Data Model   The Hierarchical Data Model organizes data in the form of a tree structure . Each child record has only one parent record . A parent record can have multiple child records . It mainly represents a one-to-many relationship . Data retrieval is very fast in the hierarchical model.                                   Company                   |            Department             /      ...
Read more