Normalization of Database

What is Database Normalization?

A database design technique called normalization reduces data duplication and gets rid of undesirable traits like Insertion, Update, and Deletion Anomalies. Using relationships, normalization rules break up larger tables into smaller tables. SQL normalization serves the dual purpose of removing unnecessary (repetitive) data and ensuring logical data storage.

Why is normalization necessary?

• Getting rid of these anomalies is the main justification for normalizing the relationships.
• When anomalies are not removed, data redundancy results, which can compromise data integrity and result in other issues as the database expands.
• A set of guidelines called “normalization” can help you create a solid database structure.

Problems Without Normalization:

Three types of data modification anomalies can be distinguished:

• Insertion anomaly: When a new tuple cannot be inserted into a relationship because there is insufficient data, this is referred to as an insertion anomaly.
• Deletion anomaly: The term “deletion anomaly” describes a situation in which some important data is unintentionally lost when some other data is deleted.
• Update Anomaly: An update anomaly occurs when changing a single data value requires changing multiple rows of data.

Think of a database that contains data on students and their courses as an illustration. The database could be normalized into two tables: one for student information and another for course information, as opposed to having a single table with columns for both student information and course information. Each student’s primary key could be in the student table, and each course’s primary key could be in the course table. A foreign key, such as a student ID in the course table to identify which student is enrolled in which course, could be used to connect the two tables.

Normalization Rule:

The following normal forms are used to classify normalization rules:

1. First Normal Form
2. Second Normal Form
3. Third Normal Form
4. BCNF
5. Fourth Normal Form

First Normal Form (1NF):

• A relation violates the first normal form if it contains composite or multivalued attributes, or
• It is in first normal form if neither of the composite or multivalued attributes are present.
• If all the attributes in a relation are single-valued, the relation is said to be in first normal form.

Second Normal Form (2NF):

• A relation must be in first normal form and be devoid of any partial dependencies to be in second normal form.
• If a relation has No Partial Dependency, which means that no non-prime attribute—i.e., an attribute that is not included in any candidate key—is dependent on any proper subset of any candidate key of the table, then the relation is in 2NF.

Third Normal Form (3NF):

• If a relation is both in second normal form and third normal form if there is no transitive dependency for non-prime attributes.
• A relation is in 3NF if at least one of the following conditions holds in every non-trivial function dependency X –> Y
  1. X is a super key.
  2. Y is a prime attribute (each element of Y is part of some candidate key).

Boyce and Codd Normal Form (BCNF):

Boyce and Codd’s Normal Form is a stricter version of the Third Normal form. This form deals with a specific kind of anomaly that 3NF does not handle. A 3NF table is referred to as being in BCNF if it does not contain any candidate keys that overlap. A table must meet the following requirements in order to be in the BCNF:

• R must be in 3rd Normal Form and,
• for each functional dependency ( X → Y ), X should be a super Key.

Fourth Normal Form (4NF):

• Fourth Normal Form (4NF) is a level of database normalization that builds on the third normal form (3NF)
• The primary objective of 4NF is to get rid of particular kinds of multivalued dependencies that can result in redundant and inconsistent data.

Note: also read about Introduction to keys in DBMS

Follow Me

Please follow me to read my latest post on programming and technology if you like my post.

https://www.instagram.com/coderz.py/

https://www.facebook.com/coderz.py