The process of Database normalization refers to the technique used for organising data/info in a database. It’s a systematic approach that helps us decompose tables for eliminating the repetition or redundancy of data in the database. It also helps avoid all the undesirable characteristics in a database, such as anomalies with Deletion, Update, and Insertion. Normalization follows a multi-step process in which data will be put in a tabular form. Thus, it removes the duplicated data out of the relation tables.
In this article, we will take a look at the Normal Form in DBMS and its uses according to the GATE Syllabus for CSE (Computer Science Engineering). Read ahead to learn more.
Table of Contents
- What is Normal Form in DBMS?
- Anomalies in a Database
- Uses of Normal Forms in DBMS
- Types of Normal Forms in DBMS
- Boyce-Codd Normal Form in DBMS or BCNF
- Practice Problems on Normal Forms in DBMS
- FAQs
What is Normal Form in DBMS?
The process of organising information/data in a database is known as normalization. This process helps us minimise the redundancy of data from a set of relations or a single relation. It also helps us eliminate several undesirable characteristics, such as Update, Insertion, and Deleting of Anomalies.
Anomalies in a Database
When the design of a database isn’t perfect, it may consist of anomalies. These anomalies can be a nightmare for database administrators. It is next to impossible to manage any database that has these anomalies. Let us discuss a few of these:
- Update Anomalies: When the data elements/ items are scattered and aren’t really linked properly to each other, it may lead to some strange situations. For instance, if we try updating a data element with its copies scattered all over various places, then only a few instances will be updated properly. On the other hand, the rest others will be left with their old values. These types of instances will always leave a database in a state that’s pretty inconsistent.
- Deletion Anomalies: When we try to delete any record, a few parts of it are left undeleted due to unawareness, then the info/ data will get stored at some separate place.
- Insert Anomalies: It happens when we try inserting data in some non-existent record.
The normalization methods help us remove all such anomalies and ultimately bring a database to a very consistent and streamlined state.
Uses of Normal Forms in DBMS
The process of normalization helps us divide a larger table in the database into various smaller tables and then link their using relationships. Normal forms are basically useful for reducing the overall redundancy (repeating data) from the tables present in a database, so as to ensure logical storage.
Types of Normal Forms in DBMS
Normal forms are of four major forms: 1NF, 2NF, 3NF, and BCNF.
A majority of the database systems have their databases normalized up to the 3NF in DBMS. But here are the normal forms that are used in DBMS:
1NF: We can say that a relation is in 1NF when it consists of an atomic value. Read more about the First Normal Form in DBMSÂ .
2NF: We can say that a relation is in 2NF when it is already in 1NF, but all the non-key attributes fully and functionally depend on their primary keys. Read more about the Second Normal Form in DBMS.
3NF: We can say that a relation is in 3NF when it is already in 2NF, but it does not consist of any transition dependencies. Read more about the Third Normal Form in DBMS.
4NF: We can say that a relation is in 4NF when it is in BCNF or Boyce-Codd Normal Form, but it does not have any multi-valued dependencies.
5NF: We can say that a relation is in the 5NF when it is already in 4NF, but it does not consist of the join dependencies. Also, the joining must be lossless here.
Boyce-Codd Normal Form in DBMS or BCNF
BCNF is nothing but an extension to the Third Normal Form on very strict terms. Thus, it is known as the 3.5 Normal Form. The BCNF entails that:
- In the case of any functional dependency Z -> P that is non-trivial, Z has to be a super key.
When you look at the image here, Cand_ID acts as a super-key in the case of the relation Candidate_Detail, while Pin acts as the super-key in the PinCodes relation. Thus, here:
Cand_ID -> Cand_Name, Pin
and also,
Pin -> City
Thus, it confirms that both of these relations here are in BCNF.
Frequently Asked Questions on Normal Forms in DBMS
What are the 5 normal forms?
Answer – Normal forms are of four major forms: INF, 2NF, 3NF, and BCNF. A majority of the database systems have their databases normalized up to the 3NF in DBMS. But here are the normal forms that are used in DBMS:
1NF
2NF
3NF
4NF
5NF
What are the normal forms in DBMS with examples?
The process of organising information/data in a database is known as normalization. This process helps us minimise the redundancy of data from a set of relations or a single relation. It also helps us eliminate several undesirable characteristics, such as Update, Insertion, and Deleting of Anomalies.
Which normal form is considered adequate for normal relational database design?
3NF is sufficient to design a normal relational database since a majority of third normal form tables stay free from anomalies of deletion, updating, and insertion. Added to this, 3NF is bound to ensure losslessness and preserve functional dependencies.
What is BCNF normal form in DBMS?
Answer – BCNF is nothing but an extension to the Third Normal Form on very strict terms. Thus, it is known as the 3.5 Normal Form. The BCNF entails that:
- In the case of any functional dependency Z -> P that is non-trivial, Z has to be a super key.
Keep learning and stay tuned to get the latest updates on GATE Exam along with GATE Eligibility Criteria, GATE 2023, GATE Admit Card, GATE Syllabus for CSE (Computer Science Engineering), GATE CSE Notes, GATE CSE Question Paper, and more.
Also Explore,
Comments