Normalization in DBMS

Normalization in a DBMS is a fundamental concept that helps organize data efficiently. It reduces redundancy, minimizes data anomalies, and ensures data integrity. In this guide, we’ll explore normalization and its various forms, and provide real-world examples to make the concept easy to understand.

Why is Normalization Important?

• Reduces Data Redundancy: Normalization ensures that each piece of data is stored only once.
• Improves Data Integrity: Normalization reduces the chances of data anomalies by organizing data into logical structures.
• Enhances Query Performance: Properly normalized databases can improve the speed and efficiency of data retrieval.
• Designing is easy and simple: Easy to desing and maintain a database.
• Maintance is easy: For manageable table for easy to add, delete, update record in database.

in briefly can say normalization helps to improve data quality, increase database efficiency, and simplify database design and maintenance.

Key Normal Forms in DBMS

1.First Normal Form(1NF)

2.Second Normal Form(2NF)

3.Third Normal Form(3NF)

4.BCNF

5.Fourth Normal Form(4NF)

6.Fifth Normal Form(5NF)

1.First Normal Form(1NF):

Eliminate duplicate columns and ensure that all entries in a column are atomic, it means relation does not contains composite ,multi-valued attributes and their combination. every attribute in that relation is singled valued attribute.There are no duplicate row. for example

Real World Example:

Let’s understand normalization with a real-world example from a school database that stores student data, including subjects, teachers, and classes

Initial Table (Unnormalized Form)

Problems:

Data redundancy: John’s data is repeated for each subject.
Update anomalies: If Mr. Smith’s name changes, we must update it in every instance.

In First Normal Form, we ensure that each column has atomic values and that there are no duplicate rows.

2.Second Normal Form (2NF)

A table is in Second Normal Form (2NF) if it meets the requirements of First Normal Form (1NF) and ensures that no non-prime attribute (attributes not part of any candidate key) is partially dependent on any part of a candidate key. This means that each non-prime attribute must rely entirely on the whole candidate key, not just a portion of it.

In simpler terms, a functional dependency, where one set of attributes (Y) depends on another set (X), is considered a partial dependency if Y can be determined by just a part of X. In 2NF, while prime attributes can still have partial dependencies, all non-prime attributes must be fully dependent on the complete candidate key.

To achieve 2NF, we remove subsets of data that apply to multiple rows and create separate tables. In this form can found transitive dependency.  it is full functional dependency

Real World Example:

Student Table

Subject Table

Teacher Table

Enrollment Table

3. Third Normal Form(3NF):

To achieve 3NF, remove columns that are not dependent on the primary key. it means all non-key attributes are not dependent on other non-key attributes (no transitive dependencies). In simple terms, every non-key attribute should only depend on the primary key. This 3NF reduce data duplicatation and provide data integrity in database.

Real World Example:

Student Table

Subject Table

Teacher Table

this example removes the transitive dependency, as all non-key attributes now depend only on their respective primary keys.

Boyce-Codd Normal Form (BCNF)

A table is in Boyce-Codd Normal Form (BCNF) if it is in 3NF and every determinant is a candidate key. BCNF addresses cases where 3NF might still allow anomalies. this is the advanced version of 3NF.
A table is in BCNF if every functional dependency X → Y,(where X is the super key of the table).
The table should be in 3NF for BCNF, and LHS is super key for each FD.

Real World Example

Convert in BCNF

Subject Table

Room Table

Sub_ID −>Teacher Name
Sub_ID −> {Room No}

Conceptual Database Design
Introduction of ER Model
Relational Data Model In DBMS

Click here for more online video project