DBMS - Normalization|1NF,2NF,3NF and BCNF normal form

 

Normalization in dbms

 (1NF,2NF,3NF and BCNF normal form)

In database design, normalization is the process of organizing a database in such a way that it meets certain desirable properties for the purpose of reducing redundancy and dependency.

There are several normal forms that can be used to normalize a database. Each normal form has a set of rules that, when followed, result in a database that is more flexible, scalable, and easier to maintain. Here is a brief overview of the different normal forms, starting from the first normal form and moving up to the BCNF normal form:

Normalization (normal forms) in dbms SKILLS ARENA

What is normalization in dbms

Normalization is the process of organizing a database in a way that reduces redundancy and dependency, and allows the database to function more efficiently. Normalization is typically applied to relational databases, and involves breaking down large tables into smaller, more atomic ones, and establishing relationships between them.

Advantages of normalization:

There are six main advantages to normalization:

Reduces redundancy: 

Normalization helps to eliminate data redundancy by breaking down large tables into smaller, more atomic ones, and establishing relationships between them. This can help reduce the amount of duplicated data in the database and make it more efficient.

Improves data integrity: 

Normalization helps to improve data integrity by enforcing rules that ensure data is consistent and accurate. This can help prevent errors and inconsistencies in the data.

Increases data security: 

Normalization can help increase data security by separating sensitive data into separate tables, making it more difficult for unauthorized users to access.

Improves data flexibility: 

Normalization makes it easier to make changes to the database structure without affecting the entire database.

Increases data independence:

 Normalization helps to increase data independence by separating data into smaller, more atomic tables, which makes it easier to modify the data without affecting the entire database.

Improves query performance:

 Normalization can help improve query performance by reducing the amount of data that needs to be accessed and processed.

Disadvantages of normalization:

There are also four main disadvantages to normalization:

Increases complexity: 

Normalization can make the database more complex by creating more tables and relationships, which can be difficult to understand and manage.

Increases the number of tables:

 Normalization can increase the number of tables in the database, which can make it more difficult to maintain and update.

Requires more storage: 

Normalization can require more storage space, as it involves creating additional tables and relationships.

Decreases query performance: 

In some cases, normalization can decrease query performance, as it may require more joins to retrieve data from multiple tables.

1NF,2NF,3NF and BCNF normal form

1. First Normal Form (1NF)

• A database is in 1NF if it meets the following criteria:

  • It is a collection of tables, each with a unique name.
  • Each table has a primary key, which is a field (or combination of fields) that uniquely identifies each row in the table.
  • There are no repeating groups of data. That is, each column in a table contains a single value for each row.

• Example:

  EmployeeID	EmployeeName	Address
  1	John Smith	123 Main St
  2	Jane Doe	456 Main St
  3	Bob Johnson	789 Main St

• In this example, the table is in 1NF because it has a unique primary key (EmployeeID), and each column contains a single value for each row.

2. Second Normal Form (2NF)

• A database is in 2NF if it is already in 1NF and meets the following criteria:

  • There are no partial dependencies. That is, no non-key column is dependent on only a part of the primary key.
  • All non-key columns are fully dependent on the primary key.

• Example:

  EmployeeID	EmployeeName	Address	Department
  1	John Smith	123 Main St	Sales
  2	Jane Doe	456 Main St	Sales
  3	Bob Johnson	789 Main St	Marketing

• In this example, the table is in 2NF because it is already in 1NF and there are no partial dependencies. The non-key column (Department) is fully dependent on the primary key (EmployeeID).

3. Third Normal Form (3NF)

• A database is in 3NF if it is already in 2NF and meets the following criteria:

  • There are no transitive dependencies. That is, no non-key column is dependent on another non-key column.

• Example:

  EmployeeID	EmployeeName	Address	Department	Manager
  1	John Smith	123 Main St	Sales	5
  2	Jane Doe	456 Main St	Sales	5
  3	Bob Johnson	789 Main St	Marketing	6
  4	Mike Williams	321 Main St	Accounting	6
  5	Samantha Brown	654 Main St	Sales
  6	Chris Johnson	912 Main St	Marketing

• In this example, the table is in 3NF because it is already in 2NF and there are no transitive dependencies.

Boyce-Codd Normal Form (BCNF)

BCNF is a normal form that is used to ensure the functional dependencies in a database are in a certain form. It is a higher level of normalization than the third normal form (3NF) and is designed to eliminate certain types of anomalies that can occur in databases.

A database is in BCNF if and only if every determinant (attribute that determines the value of another attribute) is a candidate key (a set of attributes that uniquely identifies a row in a table). This means that if an attribute (A) determines the value of another attribute (B), then attribute (A) must be a candidate key.

For example, consider a table that stores information about employees in a company. If the table includes the attributes "Employee ID", "Name", and "Department", the functional dependency "Employee ID -> Name" holds in the table. This means that the value of the "Name" attribute is determined by the value of the "Employee ID" attribute. In this case, the "Employee ID" attribute is a determinant and a candidate key, so the table is in BCNF.

Fifth normal form normalization in dbms with examples denormalization in dbms normalization in dbms javatpoint decomposition normalization in dbms normal form in dbms normalization in dbms in hindi normalization in sql normalization in dbms

______________________________________

Please share this post and blog link with your friends.For more programs use this blog.

If you have any problem, please comment in comment box, subscribe this blog for notifications of new post on your email and follow this blog.If you have any method of this program or want to give any suggestion send email on hc78326@gmail.com

Created by-- HARSH CHAUHAN