In a database, if the tables are unnormalised, then there are many problems like Insertion anomalies, Deletion anomalies, update anomalies, and data redundancy. So we need to normalise the data by using different types of normal forms.
The process of structuring the data in the database is called normalisation. To reduce duplication from a connection or set of relations, normalisation is utilised. It is also utilised to eliminate undesired traits, including deletion, update, and insertion-related anomalies.
The bigger table is split into smaller tables during normalisation, and these tables are connected through relationships. Redundancy in the database table is reduced by using the normal form.
In relational databases, normalization is a database design approach used to efficiently arrange data and minimize redundancy. Large tables are broken up into smaller, related tables, and keys are used to create relationships between them. This procedure facilitates database maintenance, enhances data integrity, and helps remove duplicate data.
A set of guidelines called Normal Forms (NF), which include the First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), and higher normal forms, are used to carry out normalization. By adhering to these guidelines, developers can ensure data consistency and reduce storage needs while creating well-structured databases that are simpler to maintain, update, and query.
Problem with an unnormalized database
There are four types of anomalies (problems) with the database, and to remove those anomalies, Normaliztion is happened.
Anomalies are-
- Insertion anomalies
- Deletion anomalies
- Updation anomalies
- Data redundancy
Insertion anomalies
When a database architecture makes it impossible to add new data without including extraneous or irrelevant information, this is known as an insertion anomaly. This issue typically occurs in tables with redundant data that are poorly designed. As a result, in order to create a new record, users might have to submit redundant or insufficient information. By grouping data into distinct, related tables, normalization helps remove insertion anomalies and improves the accuracy and efficiency of data entry.
When some characteristics cannot be entered into the database without the presence of other attributes, this is known as an insertion anomaly.
Example
Example to show Insertion Anomalies-
In the database, there are five fields: Course Number, Tutor, Room, Capacity of room (students), and Limit.
And there are two entries in the table; the first tutor is Achyut, and the other is Kirti, with room numbers 101 and 102 respectively.
If we try to insert room-103 in the database. The data is not inserted in the table because there is no assigned tutor or course Number along with that room number, so that is called an insertion anomaly.
Deletion anomalies
When deleting a record from a table inadvertently erases other important data, this is known as a deletion anomaly. When different kinds of data are kept in the same table in poorly constructed databases, this issue typically occurs. Therefore, removing one piece of information may result in the loss of linked information that ought to have been kept. By dividing data into related, well-structured tables, normalization aids in the prevention of deletion anomalies.
When certain attributes are lost as a result of the deletion of other attributes, this is known as a deletion anomaly.
Example
In the above table, we deleted Course number 2, so along with the course number, we lost the whole row; we lost the room size and limit. So, that is called insertion anomalies.
Updation anomalies
An update anomaly occurs when the same data is stored in numerous rows and must be updated several times. If all events are not updated regularly, the database may contain inaccurate or inconsistent data. This problem often arises in improperly created tables with redundant data. Normalization helps eliminate update anomalies by reducing data duplication and organizing data into related tables.
An updation anomaly exists when one or more than one instances of duplicated data are updated, but not all.
Example
In the above table, if we update the first row of the capacity of the room by 70, and do not update the 3rd row of the same room. then that is called an updating anomaly.
In the 3rd row, there is an update anomaly.
Data Redundancy
The needless duplication of the same data in several locations within a database is referred to as data redundancy. Excessive duplication raises storage needs and can result in inconsistencies, update failures, and data anomalies, even tho some redundancy may be purposeful for performance reasons. By grouping data into distinct, related tables, normalization ensures that each item of data is kept only once and is preserved effectively, hence reducing data redundancy.
Data redundancy is the storage of the identical piece of information or data more than once in a database.
Example
If we add more than one phone number of the same person, like achyut then we create one more row and repeat all the same; then that is called data redundancy.
Data Redundancy Problem
- Redundant data consumes a lot of additional disk space.
- It takes more time to update duplicate data, which might cause a serious performance problem.
Normal Forms
A set of guidelines called Normal Forms (NFs) is used in database normalization to effectively arrange data and cut down on repetition. Each normal form enhances a database's structure and deals with particular kinds of data abnormalities. First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF) are the most widely utilized normal forms; higher normal forms offer more optimization. Applying these guidelines helps develop well-structured databases that assure data integrity, consistency, and easy maintenance.
There are 6 normal forms defined-
- 1st Normal form (1NF)
- 2nd Normal form(2NF)
- 3rd Normal form(3NF)
- Boyce-Codd Normal form(BCNF)
- 4th Normal form(4NF)
- 5th Normal forn(5NF) All three anomalies (insertion anomaly, deletion anomaly, and update anomaly) are removed by the normal forms, but the 4th anomaly, which is data redundancy, is not completely removed by any of the normal forms; we can only minimize the data redundancy from the table, but not completely removed.
1st Normal form (1NF)
The normalization process begins with the First Normal Form (1NF). A table is said to be in 1NF if there are no repeating groups or multiple values stored in a single column, each row is unique, and each column only contains atomic (indivisible) values. Data organization is enhanced, querying is made easier, and higher normal forms like 2NF and 3NF are made possible by converting a table to 1NF.
If all the attributes of the relation are atomic(unique) in nature, and there is no multivalued or composite attribute in the relation, then that relation is in 1st normal form.
Example
This table has all the attribute is atomic(Unique) in nature, and there is no multivalued attribute in the table; then the table is in 1st normal form.
2nd Normal form (2NF)
The normalization process begins with the First Normal Form (1NF). A table is said to be in 1NF if there are no repeating groups or multiple values stored in a single column, each row is unique, and each column only contains atomic (indivisible) values. Data organization is enhanced, querying is made easier, and higher normal forms like 2NF and 3NF are made possible by converting a table to 1NF.
Certain non-key columns in a table that is not in 2NF might rely on a subset of the composite primary key instead of the entire key. In addition to producing redundant data, this may result in abnormalities in insertion, updating, and deletion. The database is made more efficient and well-organized by dividing partially dependent attributes into separate tables and creating appropriate associations.
By using 2NF, data redundancy is decreased, data integrity is enhanced, and database maintenance is made simpler. It guarantees that every non-key attribute fully characterizes the primary key, making the database design clearer and providing a solid basis for switching to Third Normal Form (3NF).
A relation is said to be in 2NF if there does not exist any partial functional dependency.
Partial Function Dependency- If a non-prime attribute of the relation depends upon the prime attribute of the relation, then a partial function dependency occurs.
3rd Normal form (3NF)
A relation is said to be in 3NF if all non-prime characteristics depend directly (non-transitively) on the relation's primary key.
If the Functional dependency is like (key -> non-prime attribute), then for Relation is satisfy for 3rd normal form; either x is the key or y is a prime attribute.
The third phase of database normalization is known as the Third Normal Form (3NF). If a table is already in 2NF and every non-key attribute depends only on the main key and not on any other non-key attribute, it is in 3NF. Transitive dependencies, in which one non-key column depends on another non-key column, are eliminated as a result.
Reducing data redundancy and preventing insertion, update, and deletion anomalies are the primary goals of 3NF. The database is made more structured, consistent, and manageable by dividing attributes that are not directly dependent on the primary key into the proper tables.
Boyce-Codd Normal Form (BCNF)
A relation is said to be in BCNF if the determinant is a key. BCNF is the strongest form of 3NF.
For example, if the Functional dependency is like (x -> y), then for Relation is satisfy BCNF, x must be a key.
Compared to the Third Normal Form (3NF), the Boyce-Codd Normal Form (BCNF) is a more stringent type of database normalization. If the determinant is a candidate key for each functional dependency, the table is in BCNF. To put it simply, any attribute that influences another property needs to be able to uniquely identify a record.
Even after applying 3NF, a table may still have some kinds of redundancy and abnormalities, which BCNF helps remove. Database designers can enhance data consistency, integrity, and overall database structure by breaking down tables that violate BCNF into smaller, linked tables.
4th Normal Form (4NF)
A relation with trivial multivalued dependency is called 4th normal form.
If (x->->y) is non-trivial, then x must be a key for having the 4th normal form.
Multivalued dependency - When two attributes in a table are independent of one another yet both depend on a third attribute, the situation is known as multivalued dependence.
An enhanced level of database normalization that addresses multivalued relationships is called the Fourth Normal Form (4NF). Unless the determinant is a superkey, a table is in 4NF if it is already in BCNF and has no non-trivial multivalued dependencies. To put it simply, 4NF makes sure that separate sets of multiple-valued data aren't needlessly kept in the same database.
Reducing data redundancy and preventing insertion, update, and deletion anomalies brought on by multivalued relationships are the primary goals of 4NF. 4NF produces a more organized and effective database structure by dividing independent multi-valued attributes into distinct tables.
5th Normal form (5NF)
A relation is in 5th normal form if it is in 4NF and cannot be further decomposed.If a relation is in 4NF, lacks join dependencies, and requires lossless joining, it is in 5NF.
The Fourth Normal Form (4NF) is an advanced level of database normalization that deals with multivalued dependencies. A table is in 4NF if it is already in BCNF and does not contain any non-trivial multivalued dependencies unless the determinant is a superkey. In simple terms, 4NF ensures that independent sets of multiple-valued information are not unnecessarily stored together in the same table.
The main purpose of 4NF is to reduce data redundancy and prevent insertion, update, and deletion anomalies caused by multivalued relationships. By separating independent multi-valued attributes into different tables, 4NF creates a cleaner and more efficient database structure.
Conclusion
In SQL, normalization is a crucial database design method that promotes effective data organization, minimizes redundancy, and preserves data integrity. Normalization reduces insertion, deletion, and update anomalies while simplifying database management and upkeep by organizing data into relevant and well-structured tables.
The main normal forms—1NF, 2NF, 3NF, BCNF, 4NF, and 5NF—were examined in this article. Each normal form offers a more structured database structure by addressing particular kinds of dependencies and redundancies. Developers can create dependable, consistent, and effective relational databases by comprehending these common forms.
Higher levels of normalization may necessitate more tables and joins even tho they can offer better data structure. As a result, the data structure, performance requirements, and application requirements should all be taken into consideration when choosing the proper amount of normalization.
Top comments (0)